Evitar que la fórmula de Excel cambie al insertar o borrar filas

Lo que hay que entender es que el carácter absoluto de las referencias absolutas, tal y como se especifica en el $ no es absolutamente absoluto ;-)

Ahora que el trabalenguas está fuera del camino, permítanme explicar.

La absolutización sólo se aplica al copiar-pegar o rellenar la fórmula. Insertar filas por encima, o columnas a la izquierda, de un rango referenciado de forma absoluta "desplazará" la dirección del rango para que el datos el rango señala sigue siendo el mismo.

Además, la inserción de filas o columnas en el medio del rango lo expandirá para abarcar las nuevas filas/columnas. Por lo tanto, para "añadir" una fila de datos a un rango (tabla) hay que insertarla después de la primera fila de datos.

La forma más sencilla de permitir añadir una fila de datos por encima de el rango de datos actual debe tener siempre una fila de cabecera, e incluir la fila de cabecera en el rango actual. Esta es exactamente la solución propuesta por cybernetic.nomad en este comentario .

Pero Todavía queda una cuestión más, y es la de añadir una fila de datos después del fin de la tabla. Escribir los nuevos datos en la fila posterior a la última fila de datos no funcionará. Tampoco lo hará insertar una fila antes de la fila después de la última fila.

La solución más sencilla para esto es utilizar una "última" fila especial, incluir esa fila en el rango de datos y añadir siempre nuevas filas insertando antes de esa fila especial.

Normalmente reduzco la altura de las filas y relleno las celdas con un color adecuado:

Para su ejemplo, la fórmula completa "más sencilla" sería, pues, la siguiente

=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)

Otra forma de conseguir el mismo objetivo es utilizar una fórmula dinámica que se ajuste automáticamente a la cantidad de datos de la tabla. Hay algunas variaciones diferentes de esto, dependiendo de las circunstancias exactas y precisamente lo que se va a permitir hacer a la tabla.

Si, como es el caso típico (su ejemplo, por ejemplo), la tabla comienza en la parte superior de la hoja de trabajo, tiene una cabecera de una fila, y los datos son contiguos sin espacios, una fórmula dinámica simple sería:

=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))

Esta es una solución mejor que utilizar INDIRECT() como

1. No es volátil y, por lo tanto, la hoja de cálculo es más rápida, y
2. No se romperá si insertas columnas a la izquierda de la tabla.

La técnica de las fórmulas dinámicas puede mejorarse aún más si se utiliza en un Fórmula de nombre .

Por supuesto, el mejor solución es convertir la tabla en una Tabla propia y utilizar referencias estructuradas .

Así que estás diciendo que, si insertas una nueva Fila 2 (entre la Fila 1 y la Fila 2 actuales), ¿quieres que la fórmula mire la nueva Fila 2?  Aquí hay un par de variaciones:

=SUMIF(INDIRECT("JUN!$G$2:$G$500"),"Utilities", INDIRECT("JUN!$D$2:$D$500"))

siempre mirará las filas 2 a 500, sin tener en cuenta las filas que se renumeran por inserciones (o eliminaciones).  Esto significa que, si inserta una fila, la fila original 500 será renumerada a 501 y será sacada del rango.  Si quiere ver la Fila 2 actual a través de la Fila 500 original, utilice

=SUMIF(INDIRECT("JUN!$G$2"):JUN!$G$500,"Utilities", INDIRECT("JUN!$D$2"):JUN!$D$500)

Por si no es obvio, INDIRECT() toma un argumento de cadena (texto) y lo interpreta como una dirección.  Le permite hacer direcciones invariables, porque las cadenas (que parecen direcciones) no se ajustarán cuando otras direcciones se ajusten debido a la inserción/borrado de filas/columnas.

Tenga en cuenta que el $ Los caracteres de las cadenas de direcciones son opcionales; no tienen ningún efecto.

La configuración de la compensación es tediosa, pero merece la pena si se recopilan datos de varios años, una semana o un mes a la vez.

para calcular la dirección de la última fila de datos de la columna D:
\=OFFSET(D$1,1,0)-D última fila actual +1
D última fila actual se incrementará cuando se inserte, el desplazamiento desde D$1 no

puedes usar esta fórmula para promedios, rangos, etc:
R2: =OFFSET(D$1,1,0)-D3180+1
Promediar todos los valores de la columna N, excepto los ceros
\=PROMEDIOIF(OFFSET(N$1,1,0):OFFSET(N$1,R2,0),"<>0")