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, creación de rangos dinámicos que crecen automáticamente junto con el conjunto de datos.
Estructura de COUNTA
=COUNTA(value1, [value2], ...)
value1: primer rango o valor que se cuenta;[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 inicial;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, escribir:
=COUNTA(Sales_Data[Revenue])
Confirmar que el resultado coincide con el número actual de filas de Revenue.
Escribir:
=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.
Agregar una nueva fila de transacción en Sales_Data.
Confirmar que el total de Revenue se actualiza automáticamente.
Abrir el Administrador de nombres y crear:
DynamicRevenue
Asignar la siguiente fórmula:
=OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1)
Reemplazar 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.
Crear otro rango con nombre:
DynamicProfit
Asignar:
=OFFSET(Sales_Data!$J$1,1,0,COUNTA(Sales_Data!$J:$J)-1,1)
Escribir:
=SUM(DynamicProfit)
Agregar filas adicionales en Sales_Data.
Confirmar 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
