Tim tiene una hoja de trabajo en la que la celda B1 contiene la fórmula = SUMA (A1: A7).

Quiere copiar esta fórmula hacia abajo y aumentar el rango en 7 filas, de modo que la celda B2 contenga la fórmula = SUMA (A8: A14), la celda B3 contendría = SUMA (A15: A21), etc. El problema es que cuando lo copia, cada «extremo» del rango solo se incrementa en 1, donde debería incrementarse en 7 para satisfacer su necesidad. Se pregunta cómo puede hacer que Excel realice el incremento adecuado.

No puede hacer que Excel realice el incremento adecuado usando copiar y pegar; simplemente no lo hará. La razón es simple: hay ocasiones en las que incrementar en 1 tiene sentido desde una perspectiva de fórmulas. Dado que Excel no puede leer tu mente (al menos hasta la próxima versión:>)), asume que solo debería incrementarse en 1.

La solución es cambiar tu fórmula. Usando un par de funciones de la hoja de trabajo, puede hacer que Excel construya el rango deseado para la suma. Considere el siguiente ejemplo de una fórmula que proporcionará la suma deseada:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

Si pones esta fórmula en la celda B1, funciona porque echa un vistazo al número de fila (devuelto por la función FILA) de la fila en la que está contenida la fórmula. Como está en la fila 1, Excel evalúa la fórmula de esta manera:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

Lo que terminas en B1 es la suma que deseaste. (La función INDIRECTO usa el valor en la cadena como si fuera un rango real, que es lo que desea). Cuando copia la fórmula en la columna, a medida que aumenta el número de fila, la fórmula proporciona los incrementos adecuados de 7 en ambos extremos de la gama.

Hay otras variaciones de esta técnica que puede utilizar. La única diferencia es que las variaciones utilizan diferentes funciones de la hoja de trabajo para realizar la misma tarea. Por ejemplo, la siguiente variación todavía usa la función FILA, pero finalmente se basa en la función DESPLAZAMIENTO para calcular el rango deseado:

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

Un enfoque más corto que usa OFFSET es el siguiente:

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

Independientemente del enfoque, probablemente pueda decir que la idea es llegar a una fórmula que use la fila en la que aparece la fórmula para construir el rango que realmente desea. Cada uno de los ejemplos hasta ahora asume que está comenzando en la celda B1. Si desea comenzar en la celda B2, deberá modificar las fórmulas para tener en cuenta la fila en la que está comenzando. Para darle una idea de cómo funciona esto, si estuviera comenzando en la celda B2, en cambio, las tres fórmulas presentadas en este consejo se modificarían de la siguiente manera:

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

Comience en una ubicación diferente y deberá realizar más modificaciones en la fórmula que elija usar.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (8385) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posterior) aquí:

link: / excelribbon-Incrementing_References_by_Multiples_when_Copying_Formulas [Incrementar referencias por múltiplos al copiar fórmulas].