Fórmula genérica
=SUM(firstcell:INDEX(data,rows,cols))
Resumen
Para definir un rango basado en un valor en otra celda, puede usar la función INDICE. En el ejemplo que se muestra, la fórmula en J7 es:
=SUM(C5:INDEX(data,J5,J6))
donde "datos" es el rango con nombre B5: G9.
Explicación
Esta fórmula se basa en un comportamiento específico de INDEX; aunque parece que INDEX devuelve el valor en una ubicación en particular, en realidad devuelve la referencia a la ubicación. En la mayoría de las fórmulas, no notaría la diferencia: Excel simplemente evalúa la referencia y devuelve el valor. Esta fórmula utiliza esta función para construir un rango dinámico basado en la entrada de la hoja de trabajo.
Dentro de la función de suma, la primera referencia es simplemente la primera celda en el rango que cubre todas las celdas posibles:
=SUM(C5:
Para obtener la última celda, usamos INDICE. Aquí, le damos a INDEX el rango denominado "datos", que es el rango máximo posible de valores, y también los valores de J5 (filas) y J6 (columnas). ÍNDICE no devuelve un rango, solo devuelve una sola celda en esa ubicación, E9 en el ejemplo:
INDEX(data,J5,J6) // returns E9
La fórmula original se reduce a:
=SUM(C5:E9)
que devuelve 300, la suma de todos los valores en C5: E9.
La fórmula en J8 es casi la misma, pero usa PROMEDIO en lugar de SUMA para calcular un promedio. Cuando un usuario cambia los valores en J5 o J6, el rango se actualiza y se devuelven nuevos resultados.
Alternativa con OFFSET
Puede crear fórmulas similares con la función OFFSET, que se muestra a continuación:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET está diseñado para devolver un rango, por lo que las fórmulas son quizás más sencillas de entender. Sin embargo, OFFSET es una función volátil y puede causar problemas de rendimiento cuando se usa en hojas de trabajo más grandes y complejas.
