Baixe a apostila de exemplo
Este tutorial demonstrará como usar a função SUMIFS para somar dados correspondentes a células em branco ou vazias no Excel e nas planilhas do Google.
Soma se estiver em branco
Primeiro, vamos demonstrar como somar linhas com células em branco.
A função SUMIFS soma os dados que atendem a certos critérios.
Podemos usar a função SUMIFS para somar todos Pontuações para Jogadoras sem nomes no exemplo abaixo.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Usamos aspas duplas (“”) para representar uma célula em branco no Excel. Nosso exemplo ignora Jogadoras A, B, C e D e soma as pontuações para desconhecido Jogadoras.
Tratamento de espaços como células em branco - com coluna auxiliar
Você precisa ter cuidado ao interagir com células em branco no Excel. As células podem parecer em branco para você, mas o Excel não as tratará como em branco. Isso pode ocorrer se a célula contiver espaços, quebras de linha ou outros caracteres invisíveis. Este é um problema comum ao importar dados para o Excel de outras fontes.
Se precisarmos tratar quaisquer células que contenham apenas espaços da mesma forma como se estivessem em branco, a fórmula do exemplo anterior não funcionará. Observe como a Fórmula SUMIFS não considera a célula B9 abaixo (”“) em branco:
Para tratar uma célula que contém apenas espaços como se fosse uma célula em branco, podemos adicionar uma coluna auxiliar com a função TRIM para remover os espaços extras do valor de cada célula:
| 1 | = TRIM (B3) |
Aplicamos a função SUMIFS à coluna auxiliar e agora ela calcula a soma com precisão.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
A coluna auxiliar é fácil de criar e ler, mas você pode desejar ter uma única fórmula para realizar a tarefa. Isso é abordado na próxima seção.
Tratando espaços como células em branco - sem coluna auxiliar
Se uma coluna auxiliar não for apropriada para suas necessidades, você pode usar a função SUMPRODUCT em combinação com as funções LEN e TRIM para somar as linhas em branco.
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9)) = 0), D3: D9) |
Neste exemplo, usamos a função SUMPRODUCT para realizar um cálculo complicado de “soma se”. Vamos examinar a fórmula.
Esta é a nossa fórmula final:
| 1 | = SUMPRODUTO (- (LEN (TRIM (B3: B9)) = 0), D3: D9) |
Primeiro, a função SUMPRODUCT lista a matriz de valores dos dois intervalos de células:
| 1 | = SUMPRODUCT (- (LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Em seguida, a função TRIM remove os espaços à esquerda e à direita de Jogador nomes:
| 1 | = SUMPRODUTO (- (LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
A função LEN calcula os comprimentos dos Jogador nomes:
| 1 | = SUMPRODUTO (- ({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Com o teste lógico (= 0), qualquer aparado Jogador nomes com 0 caracteres são alterados para TRUE:
| 1 | = SUMPRODUTO (- ({FALSO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO}), {25; 10; 15; 5; 8; 17; 50) |
Em seguida, os travessões duplos (-) convertem os valores VERDADEIRO e FALSO em 1s e 0s:
| 1 | = SUMPRODUTO ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
A função SUMPRODUCT então multiplica cada par de entradas nas matrizes para produzir uma matriz de Pontuações apenas para Jogador nomes que estão em branco ou apenas com espaços:
| 1 | = SUMPRODUTO ({0; 0; 15; 0; 8; 0; 50) |
Finalmente, os números na matriz são somados:
| 1 | =73 |
Mais detalhes sobre o uso de instruções booleanas e o comando “-” em uma função SUMPRODUCT podem ser encontrados aqui.
Soma se estiver em branco nas planilhas do Google
Essas fórmulas funcionam exatamente da mesma forma no Planilhas Google e no Excel.
