a función Sumproduct para facer unha suma condicional

Xa falaches sobre como usar a suma de bruxa condicional (suma condicional en francés) para realizar unha suma condicional en varios criterios. Tamén me mostrei como facelo usando o Sumif (suma en francés). Hoxe, gustaríame mostrarlle outra alternativa, moi poderosa, xa sexa o uso da función Sumproduct (Sommeprod en francés) para non facer a suma de campos multiplicados senón para facer unha cantidade condicional de acordo con varios criterios. Unha vez que domine este método, non poderás superalo.

Demostración de formigón do uso da función Sumpruct

No exemplo seguinte, temos un ficheiro con dous Tabs. O que queremos demostrar é o momento en que cada empregado realmente vai facer un emprego produtivo contra o tempo total onde está dispoñible para traballar.

A primeira pestana titúlase “Lista principal”. Contén horas programadas de traballo (concedido) por empregado en diferentes datas durante o ano 2011.

A segunda pestana ten dereito “Ocupación Empregados “e úsase para calcular a taxa de ocupación real dos empregados contra as horas de traballo programadas (concedidas). Nesta pestana, primeiro inserimos unha táboa na que poñemos o nome de todos os empregados nunha columna con células adicionais para engadir empregados segundo sexa necesario. Vexa a columna B na imaxe de abaixo.

Complete a táboa de datos

A continuación, asignamos a cada empregado un sector de actividade. Ademais, na pestana “Lista principal”, a columna “Mains” non é unha entrada senón un cálculo derivado da táboa a continuación. Simplemente usamos a fórmula VLOOLUP (buscada francés) para presentar o sector empresarial de cada empregado.

Despois engadiamos unha columna “Horas semanais” para indicar o número de horas dispoñibles por semana por empregado.

Finalmente, engadimos dúas columnas: “Inicio” e “End” para indicar a data de contratación e a data de finalización de cada recurso.

Crear un menú despregable de validación de datos

Entón creamos un menú despregable con todos os nomes dos empregados utilizando a función de validación de datos (validación de datos en francés). Así, cando facemos clic na cela B14 (consulte a imaxe a continuación), temos unha caída -down menú coa lista de empregados e debemos escoller un empregado. Na cela B15, usamos un simple VLOOKUP (busca francés) para indicar o sector da actividade.

Na cela B19, indicamos a Data do primeiro luns do ano 2 011. Na cela B20, simplemente rexistramos: = B19 + 7 e copiou a fórmula ata chegar ao último luns de 2011, o 26 de decembro de 2011.

Calcula o número de horas programadas

Na columna C, usamos unha fórmula especial para calcular o número de horas planificadas (concedidas) por recurso por semana. Na célula C19, a fórmula da función Sumproduct parece así:

= Suppoduct (‘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)

Que comprender é que todos os argumentos separados por * son condicións. Así, no presente exemplo, primeiro dixo que o empregado debe ser igual á célula B14 (onde se atopa a nosa elección do menú despregable). Díxase entón que a data debe ser maior ou igual ao 3 de xaneiro de 2011 e debe ser inferior a 10 de xaneiro de 2011. O último argumento precedido por un; Especifica as células a abordar, as horas programadas (concedidas).

Calcule as horas dispoñibles

Para calcular as horas dispoñibles, na cela D19, usamos a seguinte fórmula:

= se (e (VLOOKUP ($ B $ 14, $ 4: $ f $ 11; 4, false) = $ B19; VLOOKUP ($ B $ 14, $ 4: $ f $ 11; 5; False) > = $ b19);
VLOOKUP ($ B $ 14, $ b $ 4: $ f $ 11; 3; false); 0)

En resumo, esta fórmula indica que se a data indicada na columna B é maior ou igual á data de contratación e igual a ou inferior ao final do uso, debe indicarse o número de horas de traballo dispoñibles. Esta semana .

Calcular a diferenza

En conclusión, a diferenza é simplemente a diferenza entre as horas concedidas (planificadas) e as horas dispoñibles.

Debuxar un gráfico de horas premiadas vs dispoñibles

Estamos finalmente capaces de debuxar un gráfico que mostra as horas de traballo concedidas (planificadas) contra as horas de traballo dispoñibles polo empregado, segundo o seu período de emprego.

quí é un vídeo curto que mostra o dinamismo dos gráficos así creado.

Ademais, notarás que os títulos dos gráficos cambian automaticamente. Explicamos como facelo: Excel: Crear títulos gráficos dinámicos.

Formación complementaria

¿Es tan bo como o pensas con Excel? Proba! Entón poderás seguir o adestramento de actualización de Excel que pode servir como unha actualización para profesionais que xa usan Excel como parte das súas funcións e queren ir máis lonxe.

Aquí tes algúns comentarios de estudantes que teñen seguido de adestramento de Excel – Actualización:

CFO-Mask_formation-en LINE_FB O CFO enmascarado ofrece unha gran variedade de decisión – Formación informática con Excel e Power BI, a través dun portal en liña e remoto en tempo real, segundo un calendario. Se desexa organizar cursos privados, simplemente envíanos un correo electrónico a [email protected] Os certificados axeitados para os estándares de educación continua para as diversas ordes de traballo de Quebec ofrécense para toda a formación.

Leave a comment

O teu enderezo electrónico non se publicará Os campos obrigatorios están marcados con *