Baixe a apostila de exemplo
Este tutorial demonstrará como usar as funções SUMPRODUCT e SUMIFS para somar dados que atendam a certos critérios em várias planilhas do Excel e do Google Sheets.
Soma regular em várias folhas
Às vezes, seus dados podem se estender por várias planilhas em um arquivo Excel. Isso é comum para dados coletados periodicamente. Cada planilha em uma pasta de trabalho pode conter dados para um determinado período de tempo. Queremos uma fórmula que some os dados contidos em duas ou mais planilhas.
A função SUM permite somar facilmente os dados em várias planilhas usando um Referência 3D:
| 1 | = SUM (Folha1: Folha2! A1) |
No entanto, isso não é possível com a função SUMIFS. Em vez disso, devemos usar uma fórmula mais complicada.
Soma se em várias folhas
Este exemplo irá somar Número de entregas planejadas para cada Cliente em várias planilhas, cada uma contendo dados relativos a um mês diferente, usando as funções SUMIFS, SUMPRODUCT e INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Vamos examinar esta fórmula.
Etapa 1: Criar uma Fórmula SOMASE apenas para 1 Folha de Entrada:
Usamos a função SUMIFS para somar o Número de entregas planejadas por Cliente para uma única folha de dados de entrada:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Etapa 2: adicionar uma referência de planilha à fórmula
Mantemos o resultado da fórmula o mesmo, mas especificamos que os dados de entrada estão na planilha chamada 'Passo 2'
| 1 | = SUMIFS ('Etapa 2'! D3: D7, 'Etapa 2'! C3: C7, H3) |
Etapa 3: aninhar dentro de uma função SUMPRODUCT
Para preparar a fórmula para realizar cálculos SUMIFS em várias planilhas e, em seguida, somar os resultados, adicionamos uma Função SUMPRODUCT em torno da fórmula
| 1 | = SUMPRODUCT (SUMIFS ('Etapa 3'! D3: D7, 'Etapa 3'! C3: C7, H3)) |
Usar a função SUMIFS em uma folha produz um único valor. Em várias planilhas, a função SUMIFS produz uma matriz de valores (um para cada planilha). Usamos a função SUMPRODUCT para totalizar os valores neste array.
Etapa 4: Substitua a referência da folha por uma lista de nomes de folhas
Queremos substituir o Nome da Folha parte da fórmula com uma lista de dados contendo os valores: Jan, Fev, Mar, e Abr. Esta lista é armazenada nas células F3: F6.
A função INDIRETO para garantir que a lista de texto mostrando Nomes de planilhas é tratado como parte de uma referência de célula válida na função SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Nesta fórmula, a referência de intervalo escrita anteriormente:
| 1 | 'Etapa 3'! D3: D7 |
É substituído por:
| 1 | INDIRETO ("'" & F3: F6 & "'!" E "D3: D7") |
As aspas tornam a fórmula difícil de ler, então aqui ela é mostrada com espaços adicionados:
| 1 | INDIRETO ("'" & F3: F6 & "'!" E "D3: D7") |
Usar esta forma de referenciar uma lista de células também nos permite resumir os dados de várias planilhas que não seguem um estilo de lista numérica. Uma referência 3D padrão exigiria que os nomes das folhas estivessem no estilo: Entrada1, Entrada2, Entrada3, etc., mas o exemplo acima permite que você use uma lista de qualquer Nomes de planilhas e tê-los referenciados em uma célula separada.
Referências de Célula de Bloqueio
Para tornar nossas fórmulas mais fáceis de ler, mostramos as fórmulas sem referências de células bloqueadas:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Mas essas fórmulas não funcionarão corretamente quando copiadas e coladas em outro lugar do arquivo. Em vez disso, você deve usar referências de células bloqueadas como este:
| 1 | = SUMPRODUTO (SUMIFS (INDIRETOS ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRETOS ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Leia nosso artigo sobre Bloqueio de referências de células para saber mais.
Soma se em várias planilhas no Planilhas Google
No momento, não é possível usar a função INDIRETO para fazer referência a uma lista de planilhas em uma função SUMPRODUCT e SUMIFS no Planilhas Google.
Em vez disso, cálculos SOMASE separados podem ser feitos para cada folha de entrada e os resultados somados:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+ SUMIFS (fevereiro! D3: D7, fevereiro! C3: C7, H3)+ SUMIFS (março! D3: D7, março! C3: C7, H3)+ SUMIFS (abr! D3: D7, abr! C3: C7, H3) |
