Baixe a apostila de exemplo
Este tutorial demonstrará como usar a função SUMIFS para somar dados relacionados a células não em branco ou não vazias no Excel e no Planilhas Google.
Soma se não estiver em branco
Primeiro, vamos demonstrar como somar dados relacionados a células não vazias.
Podemos usar a função SUMIFS para somar todos Pontuações para Jogadoras com nomes não vazios.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Para somar linhas com células não vazias, excluímos Pontuações com falta Jogador nomes. Usamos o critério “diferente de espaço em branco” (“”) dentro da função SUMIFS.
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Para tratar uma célula contendo apenas espaços como se fosse uma célula em branco, podemos adicionar uma coluna auxiliar usando as funções LEN e TRIM para identificar Jogadoras com nomes.
A Função TRIM remove os espaços extras no início e no final do valor de cada célula e a Função LEN então conta o número de caracteres restantes. Se o resultado da função LEN for 0, então o Jogador o nome deve estar em branco ou apenas com espaços:
| 1 | = LEN (TRIM (B3)) |
Aplicamos a função SUMIFS à coluna auxiliar (Soma se for maior que 0) e agora calcula a soma com precisão.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
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 for necessário tratar quaisquer células que contenham apenas espaços da mesma forma como se estivessem em branco, mas usar uma coluna auxiliar não é apropriado, então podemos utilizar a função SUMPRODUCT em combinação com as funções LEN e TRIM para somar os dados relacionados às células contendo não em branco Jogador nomes:
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9))> 0), D3: D9) |
Neste exemplo, usamos a função SUMPRODUCT para realizar cálculos complicados de “soma se”. Vamos examinar a fórmula.
Esta é a nossa fórmula final:
| 1 | = SUMPRODUCT (- (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 Jogador nomes com mais de 0 caracteres são alterados para TRUE:
| 1 | = SUMPRODUTO (- ({VERDADEIRO; VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO}), {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 ({1; 1; 0; 1; 0; 1; 0}, {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 não estão em branco ou não são feitos apenas de espaços:
| 1 | = SUMPRODUTO ({25; 10; 0; 5; 0; 17; 0) |
Finalmente, os números na matriz são somados
| 1 | =57 |
Mais detalhes sobre o uso de declarações booleanas e o comando “-” em uma função SUMPRODUCT podem ser encontrados aqui
Some if not blank in Google Sheets
Essas fórmulas funcionam exatamente da mesma forma no Planilhas Google e no Excel.
