Neste artigo, aprenderemos como usar a função IF em vez das funções SUMPRODUCT e SUMIFS no Excel.
Cenário:
Em palavras simples, ao trabalhar com um conjunto de dados muito disperso, às vezes precisamos encontrar a soma dos números com alguns critérios sobre ela. Por exemplo, encontrar a soma dos salários em um determinado departamento ou ter vários critérios sobre data, nomes, departamento ou pode até mesmo numerar dados como salários abaixo do valor ou quantidade acima do valor. Para isso, você geralmente usa a função SUMPRODUCT ou SUMIFS. Mas você não acreditaria, você executa a mesma função com a função básica do Excel função IF.
Como resolver o problema?
Você deve estar pensando como isso é possível, para realizar operações lógicas em matrizes de tabela usando a função IF. A função IF no Excel é muito útil, ela o ajudará em algumas tarefas difíceis no Excel ou em qualquer outra linguagem de codificação. A função IF testa as condições na matriz correspondente aos valores necessários e retorna o resultado como uma matriz correspondente às condições True como 1 e False como 0.
Para este problema, usaremos as seguintes funções:
- Função SUM
- Função IF
Estaremos exigindo as funções acima e algum senso básico de operação de dados. condições lógicas em matrizes podem ser aplicadas usando operadores lógicos. Esses operadores lógicos funcionam com texto e números. Abaixo aqui está a fórmula genérica. { } As chaves são a ferramenta mágica para realizar fórmulas de array com a função IF.
Fórmula genérica:
| { = SUM (IF ((lógico_1) * (lógico_2) *… * (lógico_n), soma_matriz)) } |
Nota: Para colchetes ( { } ) Usar Ctrl + Shift + Enter ao trabalhar com matrizes ou intervalos no Excel. Isso gerará colchetes ondulados na fórmula por padrão. NÃO tente codificar os caracteres das chaves.
Lógico 1: testa a condição 1 na matriz 1
Lógico 2: testa a condição 2 na matriz 2 e assim por diante
sum_array: array, operação soma é realizada
Exemplo :
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo. Aqui temos dados de produtos entregues em diferentes cidades, juntamente com os campos de categoria e quantidades correspondentes. Aqui temos os dados e precisamos encontrar a quantidade de cookies enviados para Boston onde a quantidade seja maior que 40.
A tabela de dados e a tabela de critérios são mostradas na imagem acima. Para fins de compreensão, usamos intervalos nomeados para os arrays usados. Os intervalos nomeados estão listados abaixo.
Aqui :
Cidade definida para a matriz A2: A17.
Categoria definida para a matriz B2: A17.
Quantidade definida para a matriz C2: C17.
Agora você está pronto para obter o resultado desejado usando a fórmula abaixo.
Use a fórmula:
| { = SOMA (SE ((Cidade = "Boston") * (Categoria = "Cookies") * (Quantidade> 40), Quantidade)) } |
Explicação:
- City = "Boston": verifica os valores no intervalo da cidade para corresponder a "Boston".
- Categoria = "Cookies": verifica os valores no intervalo da categoria para corresponder a "Cookies".
- Quantidade> 40: verifica os valores no intervalo de quantidade para ma
- Quantidade é a matriz em que soma é necessária.
- A função IF verifica todos os critérios e o asterisco char (*) multiplica todos os resultados da matriz.
= SOMA (SE ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Agora a função IF retorna apenas as quantidades correspondentes a 1s e os restantes são ignorados.
- A função SUM retorna a SUM.
Agora, a quantidade correspondente a 1 só se soma para obter o resultado.
Como você pode ver, a quantidade 43 é retornada, mas há três pedidos de biscoitos entregues em "Boston" com as quantidades 38, 36 e 43. Precisávamos de uma soma da quantidade em que a quantidade estivesse acima de 40. Portanto, a fórmula retorna apenas 43. Agora use outros critérios para obter a Quantidade SUM para a cidade: "Los Angeles" e Categoria: "Barras" e Quantidade menor que 50.
Use a fórmula
| { = SOMA (SE ((Cidade = "Los Angeles") * (Categoria = "Barras") * (Quantidade <50), Quantidade)) } |
Como você pode ver, a fórmula retorna os valores 86 como resultado. Qual é a soma de 2 pedidos que satisfazem as condições com as quantidades 44 e 42. Este artigo ilustra como substituir uma fórmula IF aninhada por um único IF em uma fórmula de matriz. Isso pode ser usado para reduzir a complexidade em fórmulas complexas. No entanto, este problema específico pode ser facilmente resolvido com a função SUMIFS ou SUMPRODUCT.
Uso da função SUMPRODUCT:
A função SUMPRODUCT retorna a soma dos valores correspondentes na matriz. Portanto, faremos com que os arrays retornem 1s para os valores de instrução True e 0s para os valores de instrução False. Portanto, a última soma corresponderá onde todas as afirmações forem True.
Use a fórmula:
| = SUMPRODUCT (- (Cidade = "Boston"), - (Categoria = "Cookies"), - (Quantidade> 40), Quantidade) |
-: operação usada para converter todos os TRUEs para 1s e False para 0.
A função SUMPRODUCT verifica novamente a SUM da quantidade retornada pelas funções SUM e IF explicadas acima.
Da mesma forma, para o segundo exemplo, o resultado permanece o mesmo.
Como você pode ver, a função SUMPRODUCT pode realizar a mesma tarefa.
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas:
- O sum_array na fórmula só funciona com números.
- Se a fórmula retornar o erro #VALUE, verifique se as chaves devem estar presentes na fórmula conforme mostrado nos exemplos do artigo.
- Negation (-) char altera os valores, TRUEs ou 1s para FALSEs ou 0s e FALSEs ou 0s para TRUEs ou 1s.
- Operações como igual a ( = ), menor que igual a ( <= ), Maior que ( > ) ou diferente de () pode ser executado dentro de uma fórmula aplicada, apenas com números.
Espero que este artigo sobre como usar a função IF em vez das funções SUMPRODUCT e SUMIFS no Excel seja explicativo. Encontre mais artigos sobre fórmulas de soma aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
SUM se a data estiver entre : Retorna a SOMA dos valores entre datas ou períodos fornecidos no Excel.
Soma se a data for maior que a data fornecida: Retorna a soma dos valores após a data ou período especificado no Excel.
2 maneiras de somar por mês no Excel: Retorna a SOMA dos valores em um determinado mês específico no Excel.
Como somar várias colunas com condição: Retorna a soma dos valores em várias colunas com condição no Excel.
Artigos populares :
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.