Alternativa ao SUMPRODUCT

Question

Estou tentando calcular duas colunas de outra planilha do excel. Digamos que a primeira folha seja RAW DATA com a primeira coluna como Data, a segunda como Total, a terceira como Duração e a última como meu contador (para verificar duplicatas). Antes que eu possa calcular para a duração total que deve ser colocada em TALLY SHEET, ela deve atender aos seguintes critérios: A data deve ser igual à data no TALLY SHEET e a coluna do contador deve ser igual a 1.

O TALLY SHEET possui as seguintes colunas (em ordem): Data e Duração total.

Na minha coluna Duração Total em TALLY SHEET, eu uso a fórmula

=SUMPRODUCT(SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1),
            SUMIFS('RAW DATA'!C:C,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))

No entanto, essa fórmula apenas soma a duração e não executa a função SUMPRODUCT() . Se eu fizer isso na planilha RAW DATA com =SUMPRODUCT(B:B,C:C)/SUM(B:B) , isso funciona.

    

Answer 1

Essa não é a maneira correta de modificar a fórmula =SUMPRODUCT(B:B,C:C)/SUM(B:B) para verificar as condições especificadas.

Sua fórmula não produzirá o produto cruzado esperado, pois você está somando dentro de SUMPRODUCT() .

SUMPRODUCT(SUMIFS(«Total»),SUMIFS(«Duration»)/SUMIFS(«Total») é o mesmo que SUMIFS(«Total»)*SUMIFS(«Duration»)/SUMIFS(«Total») , o que é, obviamente, o mesmo que SUMIFS(«Duration»)

Você precisa usar um estilo diferente de fórmula, um que produz matrizes dentro do SUMPRODUCT() . O estilo a seguir é adequado, desde que haja apenas valores convertíveis em números nas colunas B e C . (Uma string vazia retornada por uma fórmula não é um número conversível. Também não são erros.)

Insira esta fórmula em B2 e ctrl-enter / copy-paste / fill-down no restante da coluna:

=SUMPRODUCT('RAW DATA'!$B$2:$B$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),
            'RAW DATA'!$C$2:$C$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)

Note que para este estilo de fórmula, não é recomendado o uso de colunas inteiras dentro do SUMPRODUCT() , já que isso atrasa a execução da fórmula em um lote .

Lembre-se de substituir os intervalos por números de linhas apropriados ou torná-los dinâmicos para que eles se ajustem automaticamente de acordo com os dados presentes.

Explicação:

A parte ('RAW DATA'!$A$2:$A$10=A2) verifica se a data corresponde e a parte ('RAW DATA'!$D$2:$D$10=1) verifica se o contador é igual a 1 .

Quando essas matrizes são multiplicadas juntas, TRUE torna-se 1 e FALSE a 0 . Portanto, o resultado é 1 apenas se ambos forem verdadeiros.

Multiplicar por 'RAW DATA'!$B$2:$B$10 (ou 'RAW DATA'!$C$2:$C$10 ) resulta no valor total (ou duração) apropriado, se e somente se a data corresponder e o contador for um 1 .

A cruz de função SUMPRODUCT() multiplica as duas matrizes, e então as soma.

É claro que a fórmula acima pode ser refatorada / simplificada para o seguinte, pois as condições verificadas são as mesmas para o total e a duração:

=SUMPRODUCT('RAW DATA'!$B$2:$B$10*'RAW DATA'!$C$2:$C$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)

EDITAR:

Se os valores nas colunas B e C forem gerados por fórmulas e as fórmulas puderem retornar sequências vazias, quando isso ocorrer, a fórmula acima resultará em um erro #VALUE! . O seguinte corrigirá isso tratando as strings vazias como zeros:

=SUMPRODUCT(("0"&'RAW DATA'!$B$2:$B$10)*("0"&'RAW DATA'!$C$2:$C$10)*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)

Uma fórmula alternativa pode ser construída usando SUM() e IF() .

Matriz entra ( Ctrl + Desloca + Enter ) a seguinte fórmula em B2 e copia-cola / fill-down no resto da coluna (não esqueça de remover { e } ):

{=SUM(IF(('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),'RAW DATA'!$B$2:$B$10*'RAW DATA'!$C$2:$C$10,0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)}

Observe que SUM pode ser substituído por SUMPRODUCT e a fórmula funcionará da mesma forma.

EDITAR:

O mesmo problema com as sequências vazias nas colunas B e C , conforme acima, também se aplica a essa fórmula. O seguinte corrige isso:

=SUM(IF(('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),("0"&'RAW DATA'!$B$2:$B$10)*("0"&'RAW DATA'!$C$2:$C$10),0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)