Início / coding / Perguntas / 79340735
Accepted
Mark S.
Asked: 2025-01-09 04:55:22 +0800 CST

Fórmula de divisão para somas por linha e coluna somente para meses relevantes

  • 772

Tenho uma tabela com formatação solta que tem Pessoas no topo, tarefas nas linhas e uma coluna para data de início e uma coluna para Data de término. O usuário inserirá as horas esperadas nas subtarefas fornecidas e elas serão acumuladas na própria tarefa. Consegui obter uma soma por linha dividida pelo número de meses sendo pesquisados, mas não consegui que o valor fosse distribuído corretamente pelos meses relevantes.

É assim que os dados são estruturados.

Amostra

A tarefa é produzir uma tabela que descreva as tarefas e subtarefas e faça uma média linear das horas gastas em uma determinada tarefa nos meses relevantes. (Total / # meses -> os meses entre as datas de início e término).

Por exemplo, aqui você vê que estou filtrando as subtarefas do total de tarefas e distribuindo suas respectivas horas totais pelos meses em que a tarefa deve ocorrer.

Resultado Esperado

Gostaria de me concentrar na tabela inferior por enquanto, já que a tabela superior é composta por isso, acho que posso ajustar o resto adequadamente.

Agora mesmo, eu pego um sumifs do total por tarefa/mês # para aquela tarefa/dia do cabeçalho apenas para que ele se espalhe pelas colunas. No entanto, isso está espalhando o mesmo valor por todos os meses para as tarefas, não apenas os meses relevantes. Então, eu tentei um If para verificar se a data está entre a data de início e término, mas então ele não se espalha. E se eu fizer isso sozinho, ele não valida corretamente a lógica.

Também tentei usar uma instrução LET para definir a data de início e a data de término, depois verificar o intervalo de vazamento do cabeçalho do mês para ver se esse valor está entre essas datas e, se estiver, multiplicar o valor por 1 ou 0 para tentar zerar os valores irrelevantes.

Isso é o que está acontecendo atualmente quando tento verificar as colunas em relação aos resultados do xlookup das linhas e aplicá-los às interseções.

O que está acontecendo

Aqui está uma tabela de markdown para você entender o básico.

Minha fórmula de filtro para as tarefas:=FILTER(A5:A16,MOD(A5:A16,1)<>0)

Minha fórmula para os cabeçalhos de data:=DATE(YEAR(MIN(B5:B16)),SEQUENCE(1,DATEDIF(MIN(B5:B16),MAX(C5:C16),"M")+1,MONTH(MIN(B5:B16)),1),1)

Pessoa 1 Pessoa 2 Pessoa 3 Pessoa 4 Pessoa 5
$ 100 $ 150 $ 200 $ 175 $ 95
Tarefa Começar Fim Meses Total
1 01/01/2024 01/08/2024 8 5 15 25 12 10 67
1.1 01/03/2024 01/04/2024 2 5 5 10 20
1.2 01/05/2024 01/07/2024 3 15 12 27
1.3 01/05/2024 01/08/2024 4 20 20
2 01/07/2024 01/10/2024 4 20 20 30 - 60 130
2.1 01/07/2024 01/09/2024 3 20 20 40
2.2 01/09/2024 01/10/2024 2 30 60 90
3 01/06/2024 01/10/2024 5 20 - 45 55 30 150
3.1 01/06/2024 01/07/2024 2 10 10
3.2 01/09/2024 01/10/2024 2 10 15 25
3.3 01/09/2024 01/10/2024 2 25 25
3.4 01/09/2024 01/10/2024 2 30 30 30 90
excel

2 respostas

  1. Aqui está uma maneira de obter a saída desejada usando LAMBDA()a função auxiliar MAKEARRAY():

    insira a descrição da imagem aqui

    =LET(
     a, FILTER(A4:A15,MOD(A4:A15,1)>0),
     b, MIN(B4:B15),
     c, MAX(C4:C15),
     d, DATEVALUE(UNIQUE(TEXT(SEQUENCE(,c-b+1,b),"mm/e"),1)),
     e, MAKEARRAY(ROWS(a),COLUMNS(d),LAMBDA(x,y,
        LET(z,FILTER(B4:I15,INDEX(a,x)=A4:A15,0),
        IF((INDEX(d,y)>=TAKE(z,,1))*(INDEX(d,y)<=INDEX(z,,2)),SUM(DROP(z,,3))/INDEX(z,,3),0)))),
     HSTACK(VSTACK("Task",a),VSTACK(d,ROUND(e,2)),VSTACK("Total",BYROW(e,SUM))))
    • 1
  2. Best Answer

    Com as funções mais antigas do Excel,
    a fórmula está na célula N9 e arraste para a direita e depois para baixo.

    =IF(AND(N$9>=VLOOKUP($M10,$A$5:$J$16,2,FALSE),N$9<=VLOOKUP($M10,$A$5:$J$16,3,FALSE)),VLOOKUP($M10,$A$5:$J$16,10,FALSE)/VLOOKUP($M10,$A$5:$J$16,4,FALSE),"")

    insira a descrição da imagem aqui

    Depois de esclarecer: A sua fórmula
    modSPILL

    =LET(start,XLOOKUP($M10#,$A$5:$A$16,$B$5:$B$16),
end,XLOOKUP($M$10#,$A$5:$A$16,$C$5:$C$16),
logic, IF((start<=N$9#)*(N$9#<=end),1,0),
rates,XLOOKUP($M10#,$A$5:$A$16,$J$5:$J$16)/XLOOKUP($M10#,$A$5:$A$16,$D$5:$D$16),
logic*rates)

    O resultado é a captura de tela acima. Células vazias são preenchidas com 0.

    • 1

relate perguntas