Excel

Definir rango según el valor de la celda

Define Range Based Cell Value

Fórmula de Excel: defina el rango según el valor de la celdaFórmula genérica
= SUM (firstcell: INDEX (data,rows,cols))
Resumen

Para definir un rango basado en un valor en otra celda, puede usar el Función INDICE . En el ejemplo que se muestra, la fórmula en J7 es:

|_+_|

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 particular, en realidad devuelve el 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.



creando una tabla dinámica en Excel

Dentro de la función de suma, la primera referencia es simplemente la primera celda en el rango que cubre todas las celdas posibles:

|_+_|

Para obtener la última celda, usamos INDICE. Aquí, le damos a INDEX el rango con nombre '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:



sobresalir si # n / a
|_+_|

La fórmula original se reduce a:

|_+_|

que devuelve 300, la suma de todos los valores en C5: E9.

crear una hoja de cálculo de presupuesto en Excel

La fórmula en J8 es casi la misma, pero usa PROMEDIO en vez 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 el Función OFFSET , mostrado a continuación:

|_+_|

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 un función volátil y puede causar problemas de rendimiento cuando se utiliza en hojas de trabajo más grandes y complejas.

Autor Dave Bruns


^