a função sumproduct para fazer uma soma condicional

Eu já falei sobre como usar a suma bruxa condicional (soma condicional em francês) para realizar uma soma condicional em vários critérios. Eu também mostrei como fazer isso usando o sumif (soma em francês). Hoje, gostaria de mostrar outra alternativa, muito poderosa, seja o uso da função Sumproduct (sommeprod em francês) para não fazer a soma de campos multiplicados, mas para fazer uma quantidade condicional de acordo com vários critérios. Depois de ter dominado este método, você não poderá passar!

Demonstração concreta do uso da função SUMPROCT

no exemplo abaixo, temos um arquivo com dois guias. O que queremos demonstrar é o tempo que todo funcionário realmente vai fazer um trabalho produtivo vs o tempo total em que está disponível para funcionar.

A primeira guia é intitulada “Lista principal”. Contém horas agendadas de trabalho (concedido) por funcionário em datas diferentes durante o ano de 2011.

A segunda guia é intitulada “Ocupação Funcionários “e é usado para calcular a taxa de ocupação real dos funcionários vs as horas agendadas de trabalho (concedido). Nesta guia, primeiro inserimos uma tabela em que colocamos o nome de todos os funcionários em uma coluna com células adicionais para adicionar funcionários conforme necessário. Veja a coluna B na imagem abaixo.

Complete a tabela de dados

Nós atribuímos cada funcionário um setor de atividades. Além disso, na guia “lista principal”, a coluna “Mains” não é uma entrada, mas sim um cálculo derivado da tabela abaixo. Simplesmente usamos a fórmula Vluokup (pesquisada por francês) para apresentar o setor de negócios de cada funcionário.

depois, adicionamos uma coluna “horas semanais” para indicar o número de horas disponíveis por semana por funcionário.

Finalmente, adicionamos duas colunas: “Começando” e “End” para indicar a data de contratação e a data final de cada recurso.

Criar um menu suspenso de validação de dados

Então criamos um menu suspenso com todos os nomes dos funcionários usando o recurso de validação de dados (validação de dados em francês). Assim, quando clicamos na célula B14 (veja a imagem abaixo), temos uma queda menu ultrapassado com a lista de funcionários e devemos escolher um funcionário. Na célula B15, usamos um simples vlookup (busca por francês) para indicar o setor de atividade.

na célula B19, indicamos o Data da primeira segunda-feira do ano 2 011. Na célula B20, nós simplesmente registramos: = B19 + 7 e copiou a fórmula até chegar à última segunda-feira de 2011, em 26 de dezembro de 2011.

Calcule o número de horas agendadas

Na coluna C, usamos uma fórmula especial para calcular o número de horas planejadas (concedidas) por recurso por semana. Na célula C19, a fórmula da função Sumproduct parece esta:

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

O que entender é que todos os argumentos separados por * são condições. Então, no presente exemplo, é dito primeiro que o funcionário deve ser igual a célula B14 (onde a nossa escolha do menu suspenso está localizada). É então dito que a data deve ser maior que ou igual a 3 de janeiro de 2011 e deve ser inferior a 10 de janeiro de 2011. O último argumento precedido por um; Especifica as células a serem abordadas, as horas agendadas (concedidas).

Calcule as horas disponíveis

para calcular as horas disponíveis, na célula D19, usamos a seguinte fórmula:

= if (e (vlookup ($ B $ 14, US $ 4: $ F $ 11; 4, falso) = $ B19; vlookup ($ B $ 14, US $ 4: $ F $ 11; 5; Falso) > = $ B19); Vlookup ($ B $ 14, $ B $ 4: $ F $ 11; 3; falso); 0)

Em resumo, esta fórmula indica que, se a data indicada na coluna B for maior ou igual à data de contratação e igual ou menor que o final de uso, o número de horas de trabalho disponíveis deve ser indicado. Esta semana .

Calcule a diferença

em conclusão, a diferença é simplesmente a diferença entre as horas concedidas (planejadas) e as horas disponíveis.

Desenhe um gráfico de horas concedido vs disponível

Finalmente somos capazes de desenhar um gráfico que mostra as horas de trabalho concedidas (planejadas) vs as horas de trabalho disponíveis por funcionário, de acordo com seu período de emprego.

qui é um pequeno vídeo mostrando o dinamismo dos gráficos criados assim.

Além disso, você notará que os títulos dos gráficos alteram automaticamente. Explicamos como fazer isso: Excel: criar títulos gráficos dinâmicos.

treinamento complementar

Você é tão bom quanto você acha que com o Excel? Teste! Você será capaz de seguir o treinamento do Excel – Atualização que pode servir como upgrade para profissionais que já usam o Excel como parte de suas funções e querem ir mais longe.

Aqui estão alguns comentários de alunos que têm Seguido Treinamento do Excel – atualização:

cfo-mask_formation-en line_fb O CFO mascarado oferece uma ampla gama de decisões Treinamento de computação com o Excel e Power BI, através de um portal on-line e remoto em tempo real, de acordo com um calendário. Se você quiser organizar campos privados, envie-nos um e-mail para [email protected] Certificados adequados para os padrões de educação continuada para as várias ordens de trabalho do Quebec são oferecidos para todo o treinamento.

Leave a comment

O seu endereço de email não será publicado. Campos obrigatórios marcados com *