Expansión Automática de Modelos
Desliza para mostrar el menú
Las tablas de Excel expanden automáticamente las referencias estructuradas, pero algunos cálculos aún dependen de rangos estándar. En este capítulo, se construyen rangos dinámicos que crecen automáticamente junto con el conjunto de datos.
Estructura de COUNTA
=COUNTA(value1, [value2], ...)
value1: primer rango o valor a contar;[value2]: rangos o valores adicionales opcionales.
COUNTA cuenta todas las celdas no vacías.
Fórmula utilizada en este capítulo:
=COUNTA(Sales_Data[Revenue])
Esto devuelve el número actual de filas de Revenue pobladas.
Estructura de OFFSET
=OFFSET(reference, rows, cols, [height], [width])
reference: celda de inicio;rows: desplazamiento vertical;cols: desplazamiento horizontal;[height]: altura del rango devuelto;[width]: ancho del rango devuelto.
OFFSET construye un rango dinámicamente en relación con una posición inicial.
Fórmula dinámica con OFFSET
=SUM(OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1))
Sales_Data!$H$1: referencia inicial;1: omite la fila de encabezado;COUNTA(...) - 1: altura dinámica del conjunto de datos;1: ancho del rango devuelto.
La fórmula se ajusta automáticamente a medida que se agregan nuevas filas.
Dentro de la hoja Summary, escribe:
=COUNTA(Sales_Data[Revenue])
Confirma que el resultado coincide con el número actual de filas de Revenue.
Escribe:
=SUM(OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1))
El cálculo del total de Revenue ahora se expande dinámicamente con el conjunto de datos.
Agrega una nueva fila de transacción en Sales_Data.
Confirma que el total de Revenue se actualiza automáticamente.
Abre el Administrador de nombres y crea:
DynamicRevenue
Asigna la siguiente fórmula:
=OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1)
Reemplaza la fórmula anterior por:
=SUM(DynamicRevenue)
La lógica sigue siendo dinámica, pero la fórmula es más fácil de leer y mantener.
Crea otro rango con nombre:
DynamicProfit
Asigna:
=OFFSET(Sales_Data!$J$1,1,0,COUNTA(Sales_Data!$J:$J)-1,1)
Escribe:
=SUM(DynamicProfit)
Agrega filas adicionales en Sales_Data.
Confirma que:
- Los totales de Revenue se actualizan automáticamente;
- Los totales de Profit se actualizan automáticamente;
- Los rangos con nombre continúan expandiéndose dinámicamente.
1. ¿Cuál es el propósito principal de combinar COUNTA con OFFSET?
2. ¿Por qué son útiles los Rangos Nombrados al trabajar con OFFSET?
3. ¿Qué significa que OFFSET es una función volátil?
¡Gracias por tus comentarios!
Pregunte a AI
Pregunte a AI
Pregunte lo que quiera o pruebe una de las preguntas sugeridas para comenzar nuestra charla
