Fórmula do complexo coutif

Question

Eu tenho um conjunto de dados que nos cabeçalhos tem: (nome do funcionário da cadeia, dia da seqüência, horário de check-in, horário de check-out).

O que eu quero fazer é obter uma tabela com rótulos de linha de segunda a sexta-feira e, como títulos de coluna, horas de 00:00 a 23:00. Em cada célula dessa tabela, eu deveria ter o número de funcionários que estão trabalhando naquele dia naquela hora (ou seja, a contagem de funcionários cujo turno inclui essa hora e não estão em seu dia de folga).

Não há horas parciais, todos os funcionários trabalham horas inteiras. Todos os funcionários têm um único dia de folga.

Eu já tentei com fórmulas de array, countifs, etc. Mas parece que não estou me aproximando de uma solução real.

    

Answer 1

OK, eu fiz isso. Aqui está como -

Primeiro, eu estendi seu conjunto de dados para facilitar a vida. Então, depois das primeiras 4 colunas, adicione colunas (começando por E1) "00:00", "01:00" ... "23:00". Para o primeiro funcionário em "00:00" (célula E2), coloque a fórmula

=IF(AND(E$1>=$C2, E$1<=$D2),1,0)

Isso deve mostrar um 1 se a pessoa estiver trabalhando naquela hora e um zero se não estiver, comparando o cabeçalho da coluna com os horários de entrada / saída. Copie-preencha a fórmula para as outras horas e depois para baixo para todos os funcionários.

Agora, para tornar a vida mais fácil para mim, fiz a tabela de totais (ou seja, o que você está procurando), com as horas diretamente alinhadas sob essas novas colunas na mesma planilha. Em meus dados de amostra, fui para a linha 5 para os dados do funcionário, portanto, é necessário ajustar isso para quantas linhas você tiver. Na linha total de segunda-feira, para célula na coluna E (0:00), use a fórmula

=SUMIFS(E$2:E$5,$B$2:$B$5,"<>Monday")

Isso deve totalizar os 1s acima somente se eles não estiverem funcionando na segunda-feira às 0:00. Copie-preencha a fórmula mais 4 células, e simplesmente mude o dia no final da fórmula para terça, quarta, quinta e sexta-feira. Adicione os rótulos de linha de segunda a sexta à esquerda dessas células.

Por fim, copie e preencha essa fórmula (as 5 células) para preencher toda a tabela total (ou seja, para a coluna 23:00). No final, não é muito complexo e fácil de verificar se está fazendo o que você espera eu acho. Tudo é dinâmico, mude o horário de entrada / saída ou o dia de folga e a tabela total refletirá isso.