Soma se em várias planilhas – Planilhas Excel e Google
Este tutorial demonstrará como usar as funções SOMAPRODUTO e SOMASES para somar dados que atendam a determinados critérios em várias planilhas do Excel e do Google.
Soma Regular em Várias Planilhas
Às vezes, seus dados podem abranger várias planilhas em um arquivo do Excel. Isso é comum para dados coletados periodicamente. Cada planilha em uma pasta de trabalho pode conter dados para um período de tempo definido. Queremos uma fórmula que some os dados contidos em duas ou mais planilhas.
A função SOMA permite que você some facilmente os dados de várias planilhas usando uma referência 3D:
=SOMA(Planilha1:Planilha2!A1)
No entanto, isso não é possível com a função SOMASE. Em vez disso, precisamos usar uma fórmula mais complicada.
Soma Se em Várias Planilhas
Este exemplo somará o 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 SOMASES, SOMARPRODUTO e INDIRETO:
=SOMARPRODUTO(SOMASES(INDIRETO("'"&F3:F6&"'!"&"D3:D7");INDIRETO("'"&F3:F6&"'!"&"C3:C7");H3))
Vamos examinar essa fórmula.
Passo 1: criar uma fórmula SOMASES para apenas uma planilha de entrada:
=SOMASES(D3:D7,C3:C7,H3)
Passo 2: Adicionar uma referência de planilha à fórmula
Mantemos o mesmo resultado da fórmula, mas especificamos que os dados de entrada estão na planilha chamada ‘Passo 2’.
=SOMASES('Passo 2'!D3:D7,'Passo 2'!C3:C7,H3)
Passo 3: Aninhar dentro de uma função SOMAPRODUTO
Para preparar a fórmula para executar cálculos SOMASES em várias planilhas e, em seguida, somar os resultados, adicionamos uma função SOMAPRODUTO em torno da fórmula
=SOMAPRODUTO(SOMASES('Passo 3'!D3:D7,'Passo 3'!C3:C7,H3))
O uso da função SOMASES em uma planilha produz um único valor. Em várias planilhas, a função SOMASES gera uma matriz de valores (uma para cada planilha). Usamos a função SOMAPRODUTO para totalizar os valores nessa matriz.
Passo 4: Substitua a referência da planilha por uma lista de nomes de planilhas
Desejamos substituir a parte Nome da planilha da fórmula por uma lista de dados contendo os valores: Jan, Fev, Mar e Abr. Essa lista está armazenada nas células F3:F6.
A função INDIRETO garante que a lista de texto que mostra os nomes das planilhas seja tratada como parte de uma referência de célula válida na função SOMASES.
=SOMAPRODUTO(SOMASES(INDIRETO("'"&F3:F6&"'!"&"D3:D7"),INDIRETO("'"&F3:F6&"'!"&"C3:C7"),H3))
Nessa fórmula, a referência do intervalo escrito anteriormente:
'Passo 3'!D3:D7
É substituído por:
INDIRETO("'"&F3:F6&"'!"&"D3:D7")
As aspas dificultam a leitura da fórmula, portanto, aqui ela é mostrada com espaços adicionais:
INDIRETO ( " ' " & F3:F6 & " ' ! " & "D3:D7" )
O uso dessa maneira de fazer referência a uma lista de células também nos permite resumir 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 planilhas estivessem no estilo: Entrada1, Entrada2, Entrada3, etc., mas o exemplo acima permite usar uma lista de quaisquer nomes de planilhas e referenciá-los em uma célula separada.
Bloqueio de Referências de Células
Para facilitar a leitura de nossas fórmulas, mostramos as fórmulas sem referências de células bloqueadas:
=SOMAPRODUTO(SOMASES(INDIRETO("'"&F3:F6&"'!"&"D3:D7"),INDIRETO("'"&F3:F6&"'!"&"C3:C7"),H3))
Mas essas fórmulas não funcionarão corretamente quando forem copiadas e coladas em outro local do arquivo. Em vez disso, você deve usar referências de células bloqueadas como esta:
=SOMAPRODUTO(SOMASES(INDIRETO("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIRETO("'"&$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 Google Sheets
Atualmente, não é possível usar a função INDIRETO para fazer referência a uma lista de planilhas em uma função SOMAPRODUTO e SOMASES no Google Sheets.
=SOMASES(Jan!D3:D7,Jan!C3:C7,H3)
+SOMASES(Fev!D3:D7,Fev!C3:C7,H3)
+SOMASES(Mar!D3:D7,Mar!C3:C7,H3)
+SOMASES(Abr!D3:D7,Abr!C3:C7,H3)
