☰

Somar valores dentro de um período de tempo

tag-icon • tag-icon microsoft-excel-2010
Somar valores dentro de um período de tempo

insira a descrição da imagem aquiTenho 550 linhas de dados que representam tempos de voo diários. Cada voo, dependendo do tipo de aeronave, exige a participação de um determinado número de pessoas. Essa tarefa leva 45 minutos. Portanto, em qualquer horário de voo, haverá pessoas ocupadas com esse voo e quaisquer voos nos 45 minutos anteriores. Quero obter uma contagem agregada do total de pessoas necessárias em qualquer horário de voo. Também quero tratar os 45 minutos como uma variável de entrada para ver como as alterações neste tempo de tarefa afetam a demanda de mão de obra em cada tempo de voo.

Aqui está uma maquete da aparência dos dados:

     A            B         C.        D
1   2:30          4        0:45       4
2   2:45          3.                  7
3   3:15          2.                  9
4   3:30          4.                  9
5   3:35          5.                 11

A coluna A contém os tempos de voo. A coluna B contém o número de pessoas necessárias para aquele voo. A célula C1 contém o valor do tempo da tarefa. Quero preencher a coluna D com a contagem agregada do pessoal necessário em cada voo. O exemplo é mostrado com os resultados preenchidos.

Um cálculo de amostra ficaria assim. Use a linha 4 como exemplo. O horário do vôo é 15h30. Com base em um tempo de tarefa de 45 minutos, todos os voos a partir das 2h45 exigiriam pessoas ativas simultaneamente. Existem três desses voos, 2h45, 3h15 e 3h30. A equipe da coluna B para esses voos é 3, 2 e 4, então a necessidade total de pessoal no voo das 3h30 é 9. Isso é o que acontece em D4.

Se eu mudasse o tempo da tarefa em C1 para 30 minutos, apenas os voos de 3h15 e 3h30 seriam qualificados, então o total em D4 seria 6.

Eu tentei SUMIFe SUMIFS. Adicionei uma linha com os dados (tempo) menos C1e tentei somar tudo entre Besse intervalo de tempo. Não consigo fazer com que a Bcoluna adicione os dados.

tentei

    =sumif(A:A,"<=A4",B:B)-sumif(A:A,"<=A4-C1",B:B)

Responder1

Aqui está uma solução. Esta é a fórmula para D1. Insira-o e copie para outras células em D:

    =SUM(INDIRECT("b"&IF(ISNA(MATCH(A1-C$1,A$1:A1,0)),IF(ISNA(MATCH(A1-C$1,A$1:A1,1)),1,MATCH(A1-C$1,A$1:A1,1)+1),MATCH(A1-C$1,A$1:A1,0))):B1)

Esta é basicamente a lógica. Ele encontra a primeira linha que atende aos seus critérios e então soma os valores em B entre ela e a linha atual. A maneira como ele encontra a primeira linha é usando a função MATCH para comparar o tempo na coluna A menos o tempo em C1 com os valores de tempo nas linhas até e incluindo a linha atual. MATCH não possui uma pesquisa GE para valores em ordem crescente, portanto, usa uma combinação de LE e EQ.

  • Se não houver nada LT, isso significa que a linha 1 é GE, então essa é a primeira linha.
  • Se uma linha for EQ, essa será a linha inicial.
  • Se uma linha for LE, mas não EQ, isso significa que a próxima linha é a primeira GE, portanto essa é a primeira linha.

informação relacionada