la función SumProduct para hacer una suma condicional

Ya he hablado sobre cómo usar la suma de bruja condicional (suma condicional en francés) para realizar una suma condicional en varios criterios. También te mostré cómo hacerlo usando el Sumif (suma en francés). Hoy, me gustaría mostrarle otra alternativa, muy poderosa, ya sea el uso de la función SumProduct (Sommeprod en francés) para no hacer la suma de los campos multiplicados, sino para hacer una cantidad condicional de acuerdo con varios criterios. Una vez que haya dominado este método, ¡no podrá transmitirlo!

Demostración concreta del uso de la función SumPruct

en el siguiente ejemplo, tenemos un archivo con dos pestañas. Lo que queremos demostrar es el momento en que cada empleado realmente va a hacer un trabajo productivo vs el tiempo total en el que está disponible para trabajar.

La primera pestaña se titula «Lista principal». Contiene horario programado del trabajo (otorgado) por empleado en diferentes fechas durante el año 2011.

La segunda pestaña se titula «Ocupación Empleados «y se utiliza para calcular la tasa de ocupación real de los empleados frente a las horas de trabajo programadas (otorgadas). En esta pestaña, primero insertamos una tabla en la que hemos puesto el nombre de todos los empleados en una columna con celdas adicionales para agregar a los empleados según sea necesario. Ver columna B en la imagen de abajo.

Complete la tabla de datos

Luego asignamos a cada empleado un sector de actividad. Además, en la pestaña «Lista principal», la columna «Mains» no es una entrada, sino más bien un cálculo derivado de la tabla a continuación. Simplemente hemos utilizado la fórmula Vlookup (buscada en francés) para presentar el sector empresarial de cada empleado.

Posteriormente, agregamos una columna «Horas semanales» para indicar la cantidad de horas disponibles por semana por semana.

Finalmente, agregamos dos columnas: «Inicio» y «Fin» para indicar la fecha de contratación y la fecha de finalización de cada recurso.

Crear un menú desplegable Validación de datos

Luego, hemos creado un menú desplegable con todos los nombres de los empleados utilizando la función de validación de datos (validación de datos en francés). Por lo tanto, cuando hacemos clic en la celda B14 (vea la imagen a continuación), tenemos una caída -Menú -Down con la lista de empleados y debemos elegir a un empleado. En la celda B15, usamos un simple Vlookup (búsqueda de francés) para indicar el sector de la actividad.

en la celda B19, indicamos el Fecha del primer lunes del año 2. 011. En la celda B20, simplemente registramos: = B19 + 7 y copiamos la fórmula hasta que llegue al último lunes de 2011, el 26 de diciembre de 2011.

Calcule el número de horas programadas

En la columna C, utilizamos una fórmula especial para calcular el número de horas planificadas (otorgadas) por recurso por semana. En la célula C19, la fórmula de la función SumProduct se ve así:

= SUPUMPUT (‘Lista principal’! $ B $ 2: $ B $ 1901 = $ B $ 14) * (‘Lista principal’ ! $ C $ 2: $ C $ 1901 > = $ B19) * (‘Lista principal’! $ C $ 2: $ C $ 1901 < $ b20); ‘Lista principal’! $ D $ $ 2: $ d $ 1901)

Qué entender es que todos los argumentos separados por * son condiciones. Por lo tanto, en el presente ejemplo, primero se dice que el empleado debe ser igual a la celda B14 (donde se encuentra nuestra elección del menú desplegable). Luego, se dice que la fecha debe ser mayor o igual al 3 de enero de 2011 y debe ser inferior al 10 de enero de 2011. El último argumento precedido por uno; Especifica las células a abordarse, las horas programadas (otorgadas).

Calcule las horas disponibles

Para calcular las horas disponibles, en la celda D19, utilizamos la siguiente fórmula:

= IF (y (Vlookup ($ B $ 14, $ 4: $ F $ 11; 4, Falso) = $ B19; Vlookup ($ B $ 14, $ 4: $ F $ 11; 5; Falso) > = $ b19);
Vlookup ($ B $ 14, $ B $ 4: $ F $ 11; 3; Falso); 0)

En resumen, esta fórmula indica que si la fecha indicada en la columna B es mayor quea o igual a la fecha de contratación e igual o menor que el final del uso, se debe indicar el número de horas de trabajo disponible. Esta semana .

Calcule la diferencia

En conclusión, la diferencia es simplemente la diferencia entre las horas concedidas (planificadas) y las horas disponibles.

Dibuja un gráfico de horas otorgadas VS disponibles

Finalmente podemos dibujar un gráfico que Muestra las horas de trabajo otorgadas (planificadas) vs las horas de trabajo disponibles por el empleado, según su período de empleo.

quí Es un video corto que muestra el dinamismo de los gráficos así creados.

Además, notará que los títulos de los gráficos cambian automáticamente. Explicamos cómo hacer esto: Excel: crear títulos gráficos dinámicos.

Formación complementaria

¿Eres tan bueno como lo crees con Excel? ¡Prueba! Luego podrás seguir la capacitación de EXCEL – Actualización que puede servir como una actualización para profesionales que ya usan Excel como parte de sus funciones y quieren ir más lejos.

Aquí hay algunos comentarios de los alumnos que tienen Siguió la capacitación de Excel – actualización:

CFO-MASTORK_FORMATION-ES LINE_FB El CFO enmascarado ofrece una amplia gama de decisiones – Hacer un entrenamiento informático con Excel y Power BI, a través de un portal en línea y remoto en tiempo real, según un calendario. Si desea organizar cursos privados, simplemente envíenos un correo electrónico a [email protected] Los certificados adecuados para las normas de educación continua para las diversas órdenes ocupacionales de Quebec se ofrecen para toda la capacitación.

Dejar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *