Agruparpor, Pivotarpor, Porcentajede

GROUPBY (Agruparpor)

Devuelve una tabla con registros agrupados en funcion de los valores de una o varias columnas.

Sintaxis

·        Row_fieldas, es la columna por la que agrupar.

·        Values, rango que contiene los valores que se van a agrupar.

·        Function, funcion a usar para agrupar.

·        Field_headers, un numero entre 0 y 3, especifica si los datos tienen encabezados y si se debe de devolver dichos encabezados.

·        Total_depth, muestra los totales de los campos de fila.

o   0 – ninguno

o   1 – totales generales

o   2 – total general y subtotales

·        Sort_orden, columna por la que ordenar.

·        Filter_array, columna por la que filtrar.

Veamos un ejemplo, trabajamos con el modelo que solemos usar habitualmente.

Veamos un ejemplo, en la celda I2, ponemos la funcion AGRUPARPOR, como argumento row_fields, seleccionamos en rango C1:C25 (Centros).

=AGRUPARPOR(C1:C25

Como argumento values, seleccionamos el rango G1:G25 (Total).

=AGRUPARPOR(C1:C25;G1:G25

Como argumento Function, seleccionamos SUMA.

Cerramos paréntesis.

=AGRUPARPOR(C1:C25;G1:G25;SUMA)

Aceptamos, y, tenemos el total por centro.

Estas funciones que aparecen en el argumento Function, se llaman eta-lambda.

Cuando ponemos la funcion SUMA, realmente, es una abreviatura de escribir una LAMBDA para realizar la suma.

Volvemos a la expresión, en el argumento values, cambiamos G1:G25, por, F1:G25, con esto vamos a resumir por pecio y total.

=AGRUPARPOR(C1:C25;F1:G25;SUMA)

Podemos observar que la ultima fila son los totales.

Vamos a mostrar los encabezados, volvemos a la expresión, borramos el cierre de paréntesis, ponemos coma, vemos diferentes opciones.

Seleccionamos 3, es decir, si y mostrar.

=AGRUPARPOR(C1:C25;F1:G25;SUMA;3)

Tenemos los encabezados.

Volvemos a la expresión cambiamos el argumento row_fields por C1:D25, vamos a resumir por centro y producto.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3)

Volvemos a la expresión, borramos el cierre de paréntesis, ponemos punto y coma, se abre la ventana del argumento total_depth, donde podemos decidir que es lo que queremos ver como totales.

Si seleccionamos cero, no aparecen los totales.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3;0)

Si seleccionamos 1, aparecen los totales generales.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3;1)

Si seleccionamos 2, aparecen subtotales, y, totales.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3;2)

Si seleccionamos -1, aparecen los totales, pero en la parte superior, y, si seleccionamos -2, aparecen los totales y subtotales en la parte superior.

Volvemos a la expresión, el siguiente argumento es sort_order, es decir, por la columna que queremos ordenar, vamos a poner 3, para que ordene de menor a mayor por la columna de precio.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3;1;3)

Vemos que el modelo esta ordenado por la columna de precio, pero, ordena de menor a mayor por centros.

SI ponemos -3, se ordena de mayor a menor.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3;1;-3)

El ultimo argumento es Filter_array, donde podemos realizar un filtro, volvemos a la expresión, borramos el paréntesis de cierre, ponemos punto y coma, seleccionamos el rango C1:C25 e igualamos a C.C. Nervion.

=AGRUPARPOR(C1:D25;F1:G25;SUMA;3;1;-3;C1:C25=C2)

Tenemos el modelo solo por el centro comercial C.C. Nervion, ordenado por precio de mayor a menor.

PIVOTARPOR

Es igual que AGRUPARPOR pero tenemos la posibilidad de agregar columnas.

Veamos un ejemplo y vemos sus argumentos.

En la celda I11, ponemos PIVOTARPOR, el primer argumento row_fields, que es el rango que contiene los campos de fila, seleccionamos el rango C1:C25.

=PIVOTARPOR(C1:C25

El siguiente argumento es col_fields, que contiene los campos de columna, seleccionamos el rango D2:D25 (Producto).

=PIVOTARPOR(C1:C25;D1:D25

El siguiente argumento es values, seleccionamos el rango G1:G25.

=PIVOTARPOR(C1:C25;D1:D25;G1:G25

Como argumento Function, seleccionamos SUMA.

Cerramos paréntesis.

=PIVOTARPOR(C1:C25;D1:D25;G1:G25;SUMA)

Aceptamos, y, tenemos el total por centro y producto, junto con el total para filas y columnas.

Si volvemos a la expresión, y, cambiamos el argumento row_fields, por B1:C25.

=PIVOTARPOR(B1:C25;D1:D25;G1:G25;SUMA)

Tenemos el total para provincia y centro, y, por productos.

Ponemos los encabezados, en el argumento fields_headers, ponemos 3.

=PIVOTARPOR(B1:C25;D1:D25;G1:G25;SUMA;3)

El siguiente argumento es row_total_depth, es decir, si queremos ver los totales y subtotales por filas, voy a seleccionar cero, es decir, no mostrarlos.

=PIVOTARPOR(B1:C25;D1:D25;G1:G25;SUMA;3;0)

El siguiente argumento es row_sort_order, es decir, la fila por la que ordenar, voy a poner -1, es decir, ordena por provincia de mayor a menor.

=PIVOTARPOR(B1:C25;D1:D25;G1:G25;SUMA;3;0;-1)

Los siguientes argumentos son col_total_depth, col_sort_order, que es si queremos ver el total por columnas, y ordenar por columna, el ultimo argumento es filter_array, que funciona igual que para la funcion AGRUPARPOR.

Como argumento col_total_depth voy a poner 0, es decir, quitar totales.

PERCENTOF

Esta funcion calcula el porcentaje de un conjunto de datos.

Solo tiene dos argumentos, el primero de ellos es el subconjunto de datos, el siguiente argumento es el conjunto de datos.

Volviendo al ejemplo de AGRUPARPOR, ponemos la funcion PERCENTOF, como argumento data_subject, seleccionamos la celda L3, que es el primer total, como argumento data_all, seleccionamos el rango L3:L6, que son todos los totales, y, fijamos.

=PERCENTOF(L3;$L$3:$L$6)

Obtenemos la diferencia porcentual del primer total respecto al total general.

Arrastramos y tenemos cada porcentaje.

Esta funcion la tenemos dentro de AGRUPARPOR, ponemos la funcion, como argumento row_fields, seleccionamos el rango C1:C25 (Centro), como argumento values, seleccionamos el rango G1:G25, como argumento Function, vemos aparece dicha funcion.

La seleccionamos y tenemos la diferencia porcentual de cada total respecto al total general.

=AGRUPARPOR(C1:C25;G1:G25;PERCENTOF)

También, podemos usar dos funciones o más, por ejemplo, vamos a agrupar por centro comercial, y, que nos devuelva la suma, y, el promedio.

Ponemos AGRUPARPOR, como argumento row_fields, seleccionamos el rango C2:C25, como argumento value, seleccionamos el rango E2:E25, punto y coma, ponemos APILARH, como argumento matriz1, ponemos SUMA, como argumento matriz2, ponemos PROMEDIO, cerramos paréntesis.

=AGRUPARPOR(C2:C25;E2:E25;APILARH(SUMA;PROMEDIO))

Tenemos la suma y promedio por centros comerciales.

ETA-LAMBDA reemplazar LAMBDA

Vamos a ver cómo podemos usar algunas funciones ETA-LAMBDA para reemplazar LAMBDA.

Veamos algunos ejemplos.

Tenemos el total vendido por centro comercial, y, producto.

Vamos a calcular el total por producto, para ello, en la celda C8, usamos BYCOL, como argumento array, seleccionamos el rango C2:G7, ponemos punto y coma, vemos que se abre una ventana con las funciones que podemos usar.

Seleccionamos SUMA, cerramos paréntesis.

=BYCOL(C2:G7;SUMA)

Aceptamos, y, tenemos el total por producto.

Si usamos la funcion BYCOL antes de ETA.LAMBDA, la sintaxis seria:

=BYCOL(C2:G7;LAMBDA(x;SUMA(x)))

Vemos que la sintaxis es algo más larga.

Vamos a calcular el total por centro comercial, para ello, usamos BYROW, como argumento array, seleccionamos el rango C2:G7, ponemos punto y coma, y, seleccionamos SUMA.

=BYROW(C2:G7;SUMA)

Tenemos el total por centro comercial.

Si usamos BYROW antes de ETA-LAMBDA, vemos, igual que antes, que la sintaxis es algo mar larga.

=BYROW(C2:G7;LAMBDA(x;SUMA(x)))

Vamos a crear un acumulado del total por centro comercial, para ello, ponemos SCAN, ignoramos el argumento valor inicial, como argumento array, seleccionamos los totales por centros comerciales, como argumento funcion, ponemos SUMA.

=SCAN(;H2#;SUMA)

Ya tenemos el acumulado.

Veamos otro ejemplo, tenemos una columna con una zona, y, otra columna con las ventas de cada zona.

Debemos de crear un informe donde aparezca el valor máximo y mínimo por zona.

En la celda D2, usamos AGRUPARPOR, como argumento row_fields, seleccionamos el rango A1:A20, como argumento values, seleccionamos el rango B1:B20, como argumento Function, ponemos APILARH, como argumento matriz1, ponemos MAX, como argumento matriz2, ponemos MIN, omitimos el argumento field_headers, como argumento total_depth, seleccionamos 0, para no mostrar los totales, vamos a obtener una agrupación por zonas, por el valor máximo y mínimo.

=AGRUPARPOR(A1:A20;B1:B20;APILARH(MAX;MIN);;0)

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco