Las tablas dinámicas han sido durante mucho tiempo la aparejo estereotipado para resumir y analizar datos en Excel, y funcionan aceptablemente para muchas tareas. Pero si alguna vez se ha incompatible haciendo clic en múltiples menús solo para ajustar una agrupación o poner al día sus datos, puede apreciar un enfoque más tratable. Las funciones Groupby y Pivotby de Excel ofrecen una forma diferente de pegar y organizar datos, uno que es más flexible y transparente que la interfaz de la tabla de pivote.

Utilizo estas funciones para resumir los datos con fórmulas en superficie de cuadros de diálogo. Eso hace que sea más tratable ver exactamente lo que está sucediendo y ajustar el investigación sobre la marcha. Si incluso se siente cómodo con las fórmulas de Excel, Groupby y Pivotby pueden aliviar su flujo de trabajo y brindarle más control sobre cómo se estructuran sus datos.

Groupby es la función de remisión para una simple agregación de datos

Simplifica la creación de informes sumarios

Crédito: Yasir Mahmood / Sweetnesseof

La función Groupby hace exactamente cómo sugiere su nombre. Agrupe filas de datos basadas en una o más columnas y calcula títulos resumidos para cada congregación. Lo uso para mostrar ventas totales por región o ingresos promedio por producto sin configurar una tabla de pivote.

Supongamos que tiene un conjunto de datos de ventas transacciones de seguimiento en diferentes regiones y productos. Con Groupby, puede cambiar de tablas de pivote, permitiendo que su investigación se actualice y obtenga sus totales en una sola fórmula.

Utiliza la venidero sintaxis:

=GROUPBY(row_fields, values, function, (field_headers), (total_depth), (sort_order), (filter_array), (field_relationship))

Los parámetros se descomponen así:

• ROW_FIELDS: La columna o columnas con la que desea agrupar. Esta podría ser una sola columna, como la región o múltiples columnas si necesita agrupación anidada, por ejemplo, región y producto juntos.
• títulos: Los datos que desea pegar. Esta es típicamente una columna de números que desea sumar, promediar o contar.
• función: El cálculo para realizar en cada congregación. Las opciones comunes incluyen suma, promedio, recuento, max y min. Puede usar cualquier función que funcione con matrices.

Los siguientes son los parámetros opcionales:

• Field_headers: Establezca esto en 1 para incluir encabezados de columna en la salida, o 0 para excluirlos. Si omite esto, Excel incluye encabezados por defecto.
• Total_depth: Esto agrega grandes filas totales a sus resultados. Contáctalo en 1 para un solo total de gran, 2 para subtotales y grandes totales, y así sucesivamente.
• sort_order: Controla cómo se clasifican los grupos. Use 1 para Ascender, -1 para descender u omitirlo para evitar el pedido flamante de sus datos.
• Filter_array: Una matriz verdadera/falsa que filtra que fila incluir antaño de agruparse. Esto es útil cuando solo desea pegar un subconjunto de sus datos.
• field_relationship: Controla cómo Excel interpreta la relación entre múltiples Row_fields Al agrupar. Establecer en 0 (u evitar) para tratar cada combinación de títulos como un congregación único. Establecer en 1 para crear una relación jerárquica donde el segundo campo se anida bajo el primero.

En la hoja de cálculo de ventas, encontrará registros de transacciones con columnas de región, producto, representante de ventas, término y cantidad. Para calcular las ventas totales por región, usaría:

=GROUPBY(A2:A50, E2:E50, SUM)

Esto agrupa todas las filas por la región (columna A) y suma los títulos de cantidad correspondientes (columna E). El resultado es una tabla de dos columnas que muestra cada región y sus ventas totales, no se requiere una tabla de pivote.

Crédito: Yasir Mahmood / Sweetnesseof

Si desea ver las ventas desglosadas por la región y el producto, puede producirse varias columnas al Row_fields parámetro:

=GROUPBY(A2:B50, E2:E50, SUM)

Crea una agrupación anidada donde cada región se desglosa por el producto, lo que le brinda una visión más detallada de dónde provienen sus ingresos.

Crédito: Yasir Mahmood / Sweetnesseof

Las actualizaciones de salida automáticamente cada vez que cambia los datos de su origen. No necesita refrescarse o recalcular manualmente; la fórmula siempre refleja sus datos actuales.

Por ejemplo, los siguientes grupos de fórmula por región y concatena todos los nombres de representantes de ventas en cada congregación.

=GROUPBY(A2:A50, C2:C50, LAMBDA(x, TEXTJOIN(", ", TRUE, x)))

Las tablas dinámicas no pueden hacer esto: están limitados a contar o mostrar títulos de texto individuales, no combinarlos en una sola salida.

Pivotby crea un diseño de pivote ascendiente sin la molestia

Sí, una mesa clásica de dos vías con una sola fórmula

Crédito: Yasir Mahmood / Sweetnesseof

Pivotby lleva a la agregación de datos un paso más allá al crear un sumario bidimensional: frases y columnas que trabajan juntas. La diferencia es que está escribiendo una fórmula en superficie de hacer clic en los cuadros de diálogo.

Esta función es útil cuando necesita ver cómo se cruzan dos categorías. Por ejemplo, si desea ver las ventas por región en diferentes productos, Pivotby organiza todo en un formato de cuadrícula que sea tratable de escanear.

Tiene una sintaxis larga con múltiples parámetros opcionales:

=PIVOTBY(row_fields, col_fields, values, function, (field_headers), (row_total_depth), (row_sort_order), (col_total_depth), (col_sort_order), (filter_array), (relative_to))

Los parámetros funcionan así:

• ROW_FIELDS: La columna o columnas que definen sus filas. Esto es lo que aparece en el banda izquierdo de su tabla de salida. Puede usar una sola columna o múltiples columnas para agrupaciones de fila anidada.
• col_fields: La columna o columnas que definen sus columnas. Estos títulos aparecen en la parte superior de su tabla de salida, creando la dimensión horizontal de su sumario.
• títulos: Los datos que está agregando. Esta es típicamente una columna numérica que desea sumar, promediar o contar para cada combinación de títulos de fila y columna.
• función: El cálculo para aplicar a cada intersección de grupos de fila y columna. Las opciones comunes incluyen suma, promedio, recuento, max y min.

Los siguientes son parámetros opcionales:

• Field_headers: Establecer en 1 para incluir encabezados en la salida o 0 para excluirlos. Si omite esto, Excel incluye encabezados por defecto.
• ROW_TOTAL_DEPTH: Controla si se debe pegar totales de fila y subtotales. Establecido en 1 para totales grandes, 2 para subtotales y totales grandes, y así sucesivamente.
• Row_sort_order: Determina cómo se clasifican los grupos de fila en la salida. Use 1 para orden pino, -1 para orden descendente u omitirlo para perseverar el orden flamante de sus datos de origen.
• col_total_depth: Lo mismo que row_total_depthpero para totales de columna. Esto agrega columnas de sumario a la derecha de su salida.
• col_sort_order: Controla la clasificación de grupos de columnas en su tabla de salida. Use 1 para ascender, -1 para descender, o déjelo para preservar la secuencia flamante.
• Filter_array: Una matriz verdadera/falsa que determina qué filas de los datos de su origen deben incluirse en el cálculo de pivote.
• relativo_to: Cambia cómo se calculan los totales y los porcentajes cuando se usan ciertas funciones de agregación. Establecer en 0 (u evitar) para totales estereotipado. Establecer en 1 para calcular los títulos como un porcentaje de totales de fila, o 2 para porcentajes de totales de columna.

Usando la hoja de cálculo de datos de ventas, suponga que desea ver las ventas totales para cada combinación de región y producto. Escribirías:

=PIVOTBY(A2:A50, B2:B50, E2:E50, SUM)

Este congregación se filra por región (columna A), crea columnas para cada producto (columna B) y suma los títulos de cantidad (columna E) para cada intersección. La salida es una cuadrícula que muestra exactamente cuánto se vende cada producto en cada región. Y la fórmula se actualiza automáticamente a medida que cambia sus datos.

Estas funciones son dinámicas, transparentes y más fáciles de integrar

Tanto Groupby como Pivotby, siendo fórmulas, juegan muy aceptablemente con el resto de su hoja de cálculo. Puede hacer remisión a su salida en otros cálculos, envolverlos adentro si las declaraciones o combinarlas con filtro para crear resúmenes condicionales.

La transparencia es una delantera maduro de lo que parece. Cuando alguno abre su hoja de cálculo, puede hacer clic en la celda y ver con precisión cómo se construyó el sumario.

No hay cachés de mesa de pivote ocultos o configuraciones de campo para decodificar, lo que facilita la colaboración y reduce el “¿Cómo obtuviste este número?” preguntas. Puede crear paneles que se actualicen por su cuenta. Ese tipo de flujo de trabajo es más complicado de configurar con tablas de pivote, que requieren actualizaciones manuales.