Rangos dinámicos en Excel: cuándo usar OFFSET y cuándo usar tablas
Actualiza totales y listas desplegables al añadir filas. Aprende a usar OFFSET y COUNTA, comprueba qué ocurre si falta un ID y configura una tabla de Excel.
Añades un producto a una lista de Excel, pero el total y las opciones del desplegable quedan igual. Si la referencia sigue siendo A2:A10, la nueva fila 11 queda fuera. Un rango dinámico permite a Excel averiguar dónde termina la lista y evita tener que ajustar las fórmulas cada vez.
Calcular la altura y la anchura con OFFSET
Si los datos son contiguos, cuenta los encabezados y los ID de producto para pasar la altura y la anchura a OFFSET. Crea la tabla en Sheet1 y pon la fórmula de suma en otra hoja. Conserva literalmente los encabezados en japonés para que funcionen las referencias estructuradas de más adelante.
| Celda | Columna A | Columna B | Columna C |
|---|---|---|---|
| Fila 1 | 商品ID (ID de producto) |
商品名 (nombre del producto) |
数量 (cantidad) |
| Fila 2 | A01 | Bolígrafo | 4 |
| Fila 3 | A02 | Cuaderno | 6 |
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))
Parte de A1 sin desplazar filas ni columnas. Usa el recuento de la columna A para la altura, y el de encabezados de la fila 1 para la anchura. Se obtiene A1:C3, con tres filas por tres columnas y un total de 10. Añade A03, Regla y 2 en la fila 4 y el rango pasa a A1:C4 con un total de 12. Los ID son texto y las zonas contadas deben estar libres de otros datos. Referencia de OFFSET de Microsoft
Ahora borra solo el ID de A3. Se cuentan tres celdas en la columna A: el encabezado A1 y los ID de A2 y A4. OFFSET devuelve las tres primeras filas, A1:C3. La cantidad 6 de la fila 3 sigue incluida, pero queda fuera el 2 de la fila 4. El total vuelve a 10.
Un encabezado vacío también puede dejar fuera las últimas columnas. COUNTA cuenta celdas con fórmulas que devuelven una cadena vacía (""). En un libro existente, mantén contiguos los encabezados y los ID y comprueba el límite del rango y las celdas vacías. Una cantidad vacía no cambia la altura si hay ID. Referencia de COUNTA de Microsoft
Si los encabezados empiezan en la segunda fila
Si la primera fila contiene el título del informe, empieza en A2 y cuenta también desde la fila 2 para dejar fuera el título. Al bajar una fila la tabla de ejemplo, el rango de encabezados y dos productos es A2:C4 y el total sigue siendo 10.
=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))
Usar una tabla que se amplía al añadir filas
Para una lista nueva, selecciona el rango con los encabezados y elige Insertar → Tabla. Ponle el nombre Items. La fórmula siguiente suma las cantidades desde una celda exterior. Al añadir filas, el rango de suma se amplía. Guía de Microsoft sobre referencias estructuradas
=SUM(Items[数量])
Sumar solo las filas con ID de producto
Si conviertes A1:C4 en la tabla Items, la fórmula anterior calcula 4 + 6 + 2 y devuelve 12, incluso con A3 vacía. Para sumar solo las filas con ID, usa SUMIF con la condición de que la columna de ID no esté vacía. En este ejemplo da 6 (4 + 2 de las filas 2 y 4). Referencia de SUMIF de Microsoft
=SUMIF(Items[商品ID],"<>",Items[数量])
Vincular las opciones de un desplegable a la tabla
Para mostrar los nombres de producto en otra hoja, en Excel de escritorio abre Fórmulas → Administrador de nombres. Crea ItemChoices con ámbito de libro y pon =Items[商品名] en «Se refiere a». En la celda de entrada, configura Validación de datos como Lista y escribe =ItemChoices en «Origen». El encabezado se excluye y las filas añadidas al final aportan opciones. Rellena el nombre al añadir cada fila para evitar opciones vacías. Guía de Microsoft para crear listas desplegables