Ediciones ENI Editorial | líder en informática
Las funciones de cálculo como alternativa a las tablas dinámicas
Introducción
En este capítulo, exploraremos dos funciones recientes de Excel: AGRUPARPOR y PIVOTARPOR.
Estas funciones reproducen las capacidades de las tablas dinámicas y constituyen una alternativa basada en fórmulas.
Las funciones AGRUPARPOR y PIVOTARPOR pertenecen a la familia de funciones matriciales dinámicas (Dynamic Arrays en inglés).
Se trata de un tipo de fórmula que no devuelve un único valor, sino un conjunto de valores en forma de tabla. Esta tabla se ajusta automáticamente al tamaño de los datos, lo que evita tener que copiar y pegar fórmulas o redimensionar manualmente las tablas.
La función AGRUPARPOR
AGRUPARPOR es una función que permite realizar agregaciones de datos sin utilizar tablas dinámicas. Con AGRUPARPOR, se pueden agrupar datos según una o varias columnas y efectuar cálculos de agregación como la suma, el promedio, la mediana, etc.
La sintaxis de la función AGRUPARPOR es la siguiente:
=AGRUPARPOR(row_fields;values;function;[field_headers];[total_treatment];[sort_order];[filter_array])
-
row_fields: las columnas a utilizar para agrupar las filas.
-
values: los valores a agregar.
-
function: la función de agregación a aplicar (SUMA, PROMEDIO, CONTAR, etc.)
-
field_headers (opcional): los encabezados de los campos resultantes.
-
total_treatment (opcional): la profundidad de agrupamiento.
-
sort_order (opcional): el orden de clasificación de los resultados.
-
filter_array (opcional): los criterios de filtrado de datos.
Para ilustrar esta función, utilizaremos el siguiente conjunto de datos:
Encontrará esta tabla en el archivo AGRUPAR_PIVOTAR.xlsx.
Inicialmente, queremos agrupar las ventas por ciudades.
En la hoja AGRUPARPOR, escriba =AGRUPARPOR en la celda F4.
Primer argumento row_fields: se trata de las filas agrupadas, seleccione la columna que contiene las ciudades, es decir, Ventas[[#Todo];[Ciudad]], luego escriba ;.
Segundo argumento values: se trata de los valores, seleccione la columna que contiene los importes, es decir, Ventas[[#Todo];[Importe]] y escriba...
La función PIVOTARPOR
Esta función funciona de manera similar a AGRUPARPOR, pero permite realizar agrupaciones por columnas.
La sintaxis de la función PIVOTARPOR es la siguiente:
= PIVOTARPOR (row_fields; col_fields; values; function; [filed_headers]; [row_total_depth]; [col_total_depth]; [col_sort_order]; [filter_array])
En comparación con la función AGRUPARPOR, la función PIVOTARPOR contiene el argumento col_fields, es decir, el campo que se utilizará para agrupar las columnas.
Para este ejemplo, deseamos crear un informe de ventas por ciudad (en filas) y por producto (en columnas).
En la hoja PIVOTARPOR, en la celda F3, escriba =PIVOTARPOR(.
Primer argumento row_fields: corresponde a los agrupamientos por filas. Seleccione la columna de las ciudades, es decir, Ventas[[#Todo];[Ciudad]] y luego escriba ;.
Segundo argumento col_fields: corresponde a los agrupamientos por columnas. Seleccione la columna de los artículos, es decir, Ventas[[#Todo];[Artículo]] y luego escriba ;.
Tercer argumento values: seleccione la columna que contiene los importes, es decir, Ventas[[#Todo];[Importe]] y luego escriba ;.
Cuarto argumento función: en este ejemplo, será SUMA.
La fórmula es:
=PIVOTARPOR(Ventas_[[#Todo];[Ciudad]];Ventas_[[#Todo];[Artículo]];Ventas_[[#Todo];[Importe]];SUMA)
Se obtendrá el siguiente resultado:
Este resultado podría haberse alcanzado...
Comparación entre las funciones AGRUPARPOR, PIVOTARPOR y las tablas dinámicas
AGRUPARPOR y PIVOTARPOR
-
Enfoque basado en fórmulas: las funciones AGRUPARPOR y PIVOTARPOR permiten una flexibilidad y un control precisos gracias al uso de fórmulas. Los usuarios pueden personalizar los cálculos y análisis directamente desde las celdas.
-
Resultados dinámicos: las fórmulas AGRUPARPOR y PIVOTARPOR se actualizan automáticamente cuando cambian los datos fuente, lo que permite un análisis en tiempo real sin tener que actualizar manualmente las tablas.
Inconvenientes:
-
Falta de funcionalidades interactivas: a diferencia de las tablas dinámicas, las funciones AGRUPARPOR y PIVOTARPOR no disponen de funcionalidades interactivas como el filtrado, el ordenamiento o los segmentos.
-
Complejidad de las fórmulas: el uso de AGRUPARPOR y PIVOTARPOR puede requerir un mayor dominio de las funciones y fórmulas de Excel, lo que puede ser intimidante para los usuarios menos experimentados.
Tablas dinámicas
-
Facilidad de uso: crear tablas dinámicas es a menudo más intuitivo para los usuarios, gracias a la interfaz amigable y las opciones de personalización disponibles.
-
Tamaño y complejidad de los datos: para conjuntos de datos muy voluminosos, las tablas dinámicas ofrecen un rendimiento superior y una síntesis más rápida de los datos. ...