Excel

Soma de colunas e linhas correspondentes

Sum Matching Columns

Fórmula do Excel: soma de colunas e linhas correspondentesFórmula genérica
= SUMPRODUCT (data*(range1=criteria1)*(range2=criteria2))
Resumo

Para somar valores em colunas e linhas correspondentes, você pode usar o Função SUMPRODUCT . No exemplo mostrado, a fórmula em J6 é:

 
= SUMPRODUCT (data*(codes=J4)*(days=J5))

Onde dados (C5: G14), dias (B5: B14), e códigos (C4: G4) são intervalos nomeados .



Explicação

A função SUMPRODUCT pode lidar com matrizes nativamente, sem exigir a entrada do controle de deslocamento.



Nesse caso, estamos multiplicando todos os valores nos dados do intervalo nomeado por duas expressões que filtram valores não de interesse. A primeira expressão aplica um filtro baseado em códigos:

excel se, então, declaração com texto
 
(codes=J4)

Como J4 contém 'A002', a expressão cria uma matriz de valores TRUE FALSE como este:



 
{FALSE,TRUE,FALSE,FALSE,FALSE}

A segunda expressão filtra no dia:

como somar valores no excel
 
(days=J5)

Como J4 contém 'Quarta', a expressão cria uma matriz de valores TRUE FALSE como este:

 
{FALSEFALSETRUEFALSEFALSEFALSEFALSETRUEFALSEFALSE}

No Excel, os valores TRUE FALSE são automaticamente forçados a valores 1 e 0 por qualquer operação matemática, então a operação de multiplicação coage as matrizes acima para uns e zeros e cria uma matriz 2D com as mesmas dimensões dos dados originais. O processo pode ser visualizado conforme mostrado abaixo:



Multiplicação de matriz dentro de SUMPRODUCT

Por fim, SUMPRODUCT retorna a soma de todos os elementos na matriz final, 9.

Contar em vez de somar

Se quiser contar os valores correspondentes em vez de somar, você pode encurtar a fórmula para:

como destacar células diferentes no excel
 
= SUMPRODUCT ((codes=J4)*(days=J5)) // count only

Observe que essa contagem incluirá células vazias.

Notas

  1. Embora o exemplo mostre apenas uma coluna correspondida, esta fórmula somará corretamente várias colunas correspondidas.
  2. Se você só precisa combinar colunas (não linhas), você pode usar um fórmula como esta .
  3. Para combinar apenas linhas, você pode usar o Função COUNTIFS .
Autor Dave Bruns


^