30 sept 20225 Min. de lectura

Resumen por empleado y grupo

En este ejemplo, tenemos una tabla llamada ventas, donde tenemos una fecha, un empleado, las ventas, y, grupo, que de momento no está relleno.

Tenemos otra tabla donde tenemos un grupo, un grupo general, una fecha de comienzo, y, de fin, y, un empleado.

El grupo nunca va a cambiar, pero en cada fecha de inicio, y, de fin, pueden cambiar los empleados.

En la primera tabla, tenemos una fecha y un empleado, que son dos valores de búsqueda, la fecha debo de compararla con la fecha de inicio, fin, y, el empleado de la tabla grupo.

En la tabla ventas, tenemos la columna grupo sin rellenar, pues, vamos a rellenarla, para ello, voy a usar la función FILTRAR, donde como argumento array, seleccionamos la columna grupo de la tabla grupo.

=FILTRAR(Grupo[Grupo];

Tenemos que poner tres condiciones, el empleado, la fecha de inicio, y, la fecha de fin, pues, abrimos un paréntesis para poner la primera condición, seleccionamos la columna de empleado de la tabla grupo, ponemos el signo igual, y, señalamos la primera celda de empleado de la tabla ventas, cerramos paréntesis.

=FILTRAR(Grupo[Grupo];(Grupo[Empleado]=[@Empleado])

Ponemos el símbolo de asterisco, abrimos otro paréntesis, la siguiente condición es que cada celda de la columna fecha debe de ser mayor o igual que la columna fecha de inicio, y, cerramos paréntesis.

=FILTRAR(Grupo[Grupo];(Grupo[Empleado]=[@Empleado])*([@Fecha]>=Grupo[Fecha comienzo])

Volvemos a poner el símbolo de asterisco, abrimos otro paréntesis, y, la siguiente condición es que cada fecha de la columna fecha, debe de ser menor o igual que la columna fecha de fin, cerramos paréntesis.

Cerramos paréntesis de la función, y, aceptamos.

=FILTRAR(Grupo[Grupo];(Grupo[Empleado]=[@Empleado])*([@Fecha]>=Grupo[Fecha comienzo])*([@Fecha]<=Grupo[Fecha fin]))

Ya tenemos el grupo al que pertenece cada empleado.

Lo que acabamos de hacer, nos permitirá resumir.

Tenemos un tercer modelo, que no esta en formato de tabla, donde debemos de resumir, es decir, calcular las ventas por grupo, miembro, fecha de comienzo, y, de fin, que son cuatro condiciones, usaremos la función SUMAR.SI.CONJUNTO.

Bien, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.

=SUMAR.SI.CONJUNTO(

Como argumento rango de suma, seleccionamos la columna ventas de la tabla ventas.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];

Como argumento rango de criterios 1, seleccionamos la columna empleado de la tabla ventas.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];

Como argumento criterio1, seleccionamos las celdas N6:N20, todos los empleados.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];N6:N20;

Como argumento rango de criterios 2, seleccionamos la columna fecha.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];N6:N20;Ventas[Fecha];

Como argumento criterio2, entre comillas dobles, ponemos mayor o igual, y, lo concatenamos con las celdas O4:Q4, es decir, las fechas de inicio.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];N6:N20;Ventas[Fecha];">="&O4:Q4;

Como argumento rango de criterios 3, volvemos a seleccionar la columna de fecha.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];N6:N20;Ventas[Fecha];">="&O4:Q4;Ventas[Fecha];

Como argumento criterio3, entre comillas dobles, ponemos menor o igual, y, lo concatenamos con las celdas O5:Q5, las fechas de fin.

Cerramos paréntesis y aceptamos.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];N6:N20;Ventas[Fecha];">="&O4:Q4;Ventas[Fecha];"<="&O5:Q5)

Tenemos una matriz desbordada con las ventas para cada empleado, fecha de inicio, y, fecha de fin.

Pero, falta un criterio, porque no hemos buscado el grupo de cada empleado, pues, añadimos esta última condición.

=SUMAR.SI.CONJUNTO(Ventas[Ventas];Ventas[Empleado];N6:N20;Ventas[Fecha];">="&O4:Q4;Ventas[Fecha];"<="&O5:Q5;Ventas[Grupo];M6:M20)

Vemos como algunas ventas han cambiado.

Ahora, tenemos la misma tabla, pero sin la columna de grupo.

Y tenemos otra tabla, donde tenemos el grupo, grupo general, tres empleados, una fecha de inicio, y, de fin.

Vemos que hay equipos que aparecen dos veces, en cada equipo, tenemos diferentes empleados, por ejemplo, para el mes de enero de tenemos tres empleados, Jaime, Claudia, y, Miguel.

Pero, para el mes de febrero a marzo, tenemos otros tres empleados, Jaime, Pilar, y, Francisco.

Queremos crear una tabla dinámica, para crear un informe con las ventas para cada trabajador, y, como van cambiando las ventas, tenemos que convertir la tabla, para que tenga un identificador único para el empleado, y, grupo, para ello, vamos a usar Power Query.

Para ello, con una celda dentro de la primera tabla, vamos a la pestaña de datos, dentro del grupo obtener y transformar, hacemos clic en de una tabla o rango.

Cerramos y cargamos como una conexión, y, nos llevamos la segunda tabla.

Debemos de tomar el primer registro, y, repetirlo tres veces, una vez para cada empleado, para ello, seleccionamos las siguientes columnas:

Hacemos clic con botón alternativo de ratón, y, seleccionamos anulación de dinamización de otras columnas

Ya tenemos lo que queremos, un empleado para cada fecha.

La columna atributo, no la voy a necesitar, por lo que hago clic con botón alternativo de ratón sobre ella, y, elijo quitar.

A la columna valor, le cambio el nombre por idempleado.

Vamos a la tabla venta, donde tenemos que resumir las ventas por el grupo, por ejemplo, Jaime, aparece tres veces para el mes de enero, y, pertenece al Grupo A-1.

Debemos de agregar una columna adicional, y, buscar el nombre del grupo según fecha y empleado, quiere decir que para cada fecha y empleado de la tabla venta, debemos de buscarlo en la tabla grupo, entonces, voy a agregar un paso, donde voy a usar la función Table.Buffer, esta función almacena una tabla en memoria, y, la aisla de cambios, de esta manera, no tenemos que acudir a la tabla grupo.

Para insertar un paso, hacemos clic en el siguiente icono.

Ponemos la función, y, el nombre de la tabla grupo.

Ahora, tenemos la consulta grupo en la tabla venta como una tabla estática.

Le cambio el nombre al paso y le pongo grupos.

Ahora, es cuando debemos de agregar esa columna adicional, para ello, volvemos a insertar un paso, donde vamos a usar Table.Addcolumn, para añadir la columna.

El primer argumento es tabla, pues, seleccionamos el paso tipo cambiado, que es la tabla venta.

El siguiente argumento es nombre de columna, pues entre comillas dobles, ponemos nombre de grupo

Ahora, necesitamos la tabla de búsqueda para cada fila, entonces, voy a poner la palabra each (para cada), y, ponemos la tabla que hemos creado con Table.Buffer.

Cerramos paréntesis y aceptamos.

Tenemos esa columna adicional donde tenemos la tabla grupo.

En el siguiente registro, tenemos una fecha y un empleado, y, podemos acceder a cualquier columna de la columna nombre de rango.

Tenemos que comparar la fecha con la columna fecha de inicio, y, de fin, y, el empleado con idempleado.

De esa manera, podremos extraer el nombre del grupo, pero, debemos de acceder a tres columnas de la tabla grupo, por lo que vamos a tener que declarar tres variables, una para cada columna.

Volvemos a la función.

La palabra each, ya no nos vale, en este caso significa para cada fila de la tabla grupos, pero no estamos especificando columnas independientes, que es lo que debemos de hacer, por lo que borro la palabra each.

Para declarar una variable, debemos de ponerla entre paréntesis, seguido de =>, por ejemplo, declaro la siguiente variable.

Ahora, vamos a usar la función Table.SelectRows, que devuelve las filas de una tabla que cumplan con la condición, el primer argumento es tabla, que es el paso grupos.

El siguiente argumento es la condición, donde vamos a crear otra variable para acceder a las columnas de la tabla grupo.

Entonces, tenemos tres condiciones, la primera es comparar el empleado de la tabla venta con idempleado de la tabla grupo, como sigue:

Cerramos paréntesis de Table.SelectRows, y, de Tabla.AddColumn.

Aceptamos.

Si hacemos clic en la primera celda de la columna nombre de grupo, vemos en la parte inferior, todas las columnas de la tabla grupo que pertenecen a Guillermo.

Lo siguiente es comparar la fecha de inicio, con la fecha de fin, para ello, eliminamos los dos paréntesis de cierre, usamos el operador lógico AND, debemos de escribirlo en minúsculas

Ponemos la variable AA, y, entre corchetes, ponemos la columna fecha.

Ponemos mayor o igual a la variable BB, la columna fecha de inicio.

Volvemos a poner el operador lógico and, volvemos a poner Fecha de la tabla venta, y, ponemos menor o igual a la columna fecha fin.

Volemos a poner los paréntesis de cierre, y, aceptamos.

Si hacemos clic en la primera celda de la columna nombre de grupo, y, vemos en la parte inferior, podemos ver el registro coincidente.

Pero necesitamos el nombre del grupo, para ello, volvemos a la barra de fórmulas, donde debemos de usar un índice posicional, para ello, entre llaves, ponemos el numero de columna a mostrar, empezando desde cero, y, necesitamos recuperar la columna cero.