google spreadsheet - trucos - Suma de hoja de cálculo de Google que siempre termina en la celda de arriba

porcentaje google sheets (7)

¿Cómo crear una suma de hoja de cálculo de Google () que siempre termina en la celda anterior, incluso cuando se agregan nuevas celdas? Tengo que hacer varios cálculos de este tipo en cada columna para que soluciones como esta no ayuden.

Ejemplo:

En la columna B, tengo varios rangos dinámicos que deben sumarse. B1..B9 se debe sumar en B10, y B11..B19 se debe sumar en B20. Tengo decenas de tales cálculos para hacer. De vez en cuando, agrego filas debajo de la última fila sumada, y quiero que se agreguen a la suma. Agrego una nueva fila (llámela 9.1) antes de la fila 10, y una nueva bruta (llamémosla 19.1) antes de la fila 20. Quiero que B10 contenga la suma de B1 a B9.1 y B20 que contenga la suma de B11: B19.1.

En Excel, tengo la función de desplazamiento que lo hace como encanto. ¿Pero cómo hacerlo con la hoja de cálculo de google? Intenté usar fórmulas como esta:

=SUM(B1:INDIRECT(address(row()-1,column(),false))) # Formula on B10 =SUM(B11:INDIRECT(address(row()-1,column(),false))) # Formula on B20

Pero en Google Spreadsheet, todo lo que da es un error #name.

Perdí horas tratando de encontrar una solución, tal vez alguien pueda llamar. por favor avise

Amnon

Esto se basa en @PsychoFish, aquí está la solución:

=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"3:"&ADDRESS(ROW()-1,COLUMN(),4)))

Simplemente reemplace "3:" para que la fila comience a sumar.

@PsychoFish es correcto pero no puede ser arrastrado ni copiado ya que la columna es literal y está codificada, y @Druvision estaba en la dirección correcta pero estaba equivocada ... básicamente terminó con el mismo problema de tener que volver a ingresar los rangos y luego Deslizando las fórmulas una y otra vez.

La respuesta de @PsychoFish me llevó de la manera correcta. El único problema que tuve que volver a escribir la fórmula de cada columna y cada suma. Así que aquí está la fórmula mejorada, que suma las 9 celdas anteriores en la misma columna, sin codificar los números de columna o fila:

=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))

El único problema es que tuve que volver a escribir las fórmulas si alguien agrega o elimina una fila. En este caso debería cambiar de 9 a 10 u 8 de forma correspondiente.

Me gusta cómo @abernier describe la solución general. Hasta ahora solo se está utilizando la notación A1 basada en el alfabeto (A es la primera columna, 1 es la primera fila). Me sigue confundiendo, especialmente cuando pienso en columnas a la izquierda de otras columnas. Me gusta mucho más la notación R1C1 basada en números. Para usar la notación R1C1 para INDIRECT , debe pasar FALSO así:

=SUM(INDIRECT("R1C"&COLUMN()&":R"&(ROW()-1)&"C"&COLUMN(), FALSE))

Espero que te resulte útil también.

Probablemente estés buscando una fórmula como:

=SUM(INDIRECT("B1:"&ADDRESS(ROW()-1,COLUMN(),4)))

Google Spreadsheet INDIRECT devuelve la referencia a una celda o área , mientras que, por lo que recuerdo, Excel INDIRECT devuelve siempre la referencia a una celda. Dado que, de hecho, INDIRECTO de Google tiene dificultades cuando intenta utilizarlo dentro de SUM como referencia de celda, lo que desea es alimentar a SUM con todo el rango para resumirlo, por ejemplo, en la notación a1: "B1: BX".

Obtiene la dirección que desea de la misma manera que en EXCEL (note "4" aquí para la fila / columna relativa, por defecto, Google INDIRECT devuelve absoluto):

ADDRESS(ROW()-1,COLUMN(),4)

y luego usarlo para preparar la cadena de rango para la función SUMA concatenando con la celda inicial.

"B1:"&

y envuélvelo con INDIRECTO, que devolverá el área a resumir.

REFERIENDO A ABAJO UNA RESPUESTA de Druvision (no puedo comentar aún, no quería multiplicar las respuestas)

En lugar de consumir mucho tiempo correcciones de fórmulas cada vez que se inserta / elimina una fila para que todo se vea como:

=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))

Puede guardar una columna en una hoja separada para mantener las variables (llamémosla "def"), digamos Z, para definir puntos de partida, por ejemplo, en Z1 escriba "B1" en Z2 escriba "B11", etc. y luego utilícelo como variable en su suma utilizando INDEX:

SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sumas de B1 a la fila calculada, ya que en Z1 tenemos " B1 "(el 1,1 en INDEX(...,1,1) )

SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sumas de B11 a la fila calculada, ya que en Z2 tenemos " B11 "(el 2,1 en INDEX(...,2,1) )

tenga en cuenta:

Hoja separada llamada ''def'': no desea que la inserción / eliminación de filas influya en esos datos, por lo tanto, manténgalos en el lado. Útil para agregar algunas listas de validación, otras cosas que necesita en sus fórmulas.
Notación "Z: Z" - toda la columna. Usted dijo que tenía muchas de esas fórmulas;)

Por lo tanto, conserva la flexibilidad de definir la celda de inicio para cada una de sus fórmulas, que no está influenciada por los cambios en la hoja de cálculo.

Por cierto, ¿no sería más fácil escribir una función / script personalizado que resuma todas las filas por encima de la celda? Si tienes ganas de javascripting, por lo que recuerdo, la hoja de cálculo de Google ahora tiene un buen editor de scripts. Puede crear una función llamada, por ejemplo, sumRowsAboveMe () y usarla en su hoja como =sumRowsAboveMe() en la celda de la hoja.

Nota: puede que tenga que reemplazar comas por punto y coma

Sintaxis general:

=SUM(INDIRECT(cell_reference_as_string1 &":"& cell_reference_as_string2)

con por ejemplo:

cell_reference_as_string1 = ADDRESS(ROW(),COLUMN(),4) cell_reference_as_string2 = ADDRESS(ROW()-1,COLUMN(),4)

Ustedes están haciendo esto más difícil de lo que tienen que hacer. Solo dejo un par de filas vacías arriba por la fila "suma" (puede formatearlas para que se llenen de color o algo para evitar que se usen inadvertidamente), luego agregue sus nuevas filas justo encima de esas filas especiales.

NOTA Después de probar esta respuesta, solo funcionará si la suma está en una columna diferente debido a un error de dependencia circular. De lo contrario, la solución es válida.

Es un poco de álgebra, pero podemos aprovechar el arrastre de la esquina inferior derecha de las Hojas de cálculo.

=SUM(X:X) - SUM(X2:X)

Donde X es la columna con la que está trabajando y X2 es su punto final . Arrastre la fórmula hacia abajo y Sheets incrementará el X2, cambiando así el punto final.

* Mencionaste que tenías decenas de tales cálculos para hacer. Por lo tanto, para satisfacer su necesidad exacta, le restamos su última suma para obtener el rango "medio" que queríamos.

p.ej

B1..B9 se debe sumar en B10, y B11..B19 se debe sumar en B20

Debido al error de dependencia circular mencionado anteriormente, no puedo resolverlo exactamente y colocar la suma en la misma línea, pero esto podría funcionar en otros casos donde la suma debe almacenarse en una columna diferente.

=SUM(B:B) - SUM(B9:B) //Formula on C10 (Sum of B1..B9) =SUM(B:B) - SUM(B19:B) - B10 // Formula on C20 (Sum of B11..B19)