Função SOMASE no Excel e soma por várias condições

O Excel é um programa incrivelmente funcional. Mesmo o conjunto de recursos integrado é suficiente para concluir quase qualquer tarefa. E além dos padrões, conhecidos por muitos, também existem aqueles que poucos ouviram falar. Mas, ao mesmo tempo, eles não deixam de ser úteis. Eles têm uma especialização mais restrita e nem sempre há necessidade deles. Mas se você souber sobre eles, em um momento crítico, eles podem ser muito úteis.

Hoje falaremos sobre uma dessas funções - SUMMESLIMN.

Se o usuário se depara com a tarefa de somar vários valores, concentrando-se em determinados critérios, é necessário usar a função SUMMESLIMN. A fórmula que usa essa função toma essas condições como argumentos, depois soma os valores que as atendem e, em seguida, o valor encontrado é inserido na célula em que está escrito. 

Descrição detalhada da função SOMASES

Antes de considerar a função SUMMESLIMN, você deve primeiro entender o que é uma versão mais simples – SUMMESLI, pois é nele que se baseia a função que estamos considerando. Cada um de nós provavelmente já está familiarizado com duas funções que são frequentemente usadas – SOMA (realiza a soma dos valores) e E SE (testa um valor contra uma condição especificada).

Se você combiná-los, você obtém outra função – SUMMESLI, que verifica os dados em relação aos critérios especificados pelo usuário e soma apenas os números que atendem a esses critérios. Se falamos sobre a versão em inglês do Excel, essa função é chamada SUMIF. Em palavras simples, o nome do idioma é uma tradução direta do nome do idioma inglês. Esta função pode ser usada para uma variedade de propósitos. Em particular, pode ser usado como uma alternativa VPR, ou seja, escreva

A principal diferença entre a função SUMMESLIMN  da função normal SUMMESLI é que vários critérios são usados. Sua sintaxe é bastante complexa à primeira vista, mas após um exame mais detalhado, verifica-se que a lógica dessa função é muito simples. Primeiro você precisa selecionar o intervalo onde os dados serão verificados e, em seguida, definir as condições de conformidade com as quais a análise será realizada. E tal operação pode ser realizada para um número bastante grande de condições.

A sintaxe em si é:

SOMASES(sum_range, condition_range1, condition1, [condition_range2, condition2], …)

Em locais adequados é necessário colocar matrizes de células adequadas a um caso particular. 

Vejamos os argumentos com mais detalhes:

1. Sum_range. Esse argumento, bem como o intervalo da condição 1 e da condição 1, é necessário. É um conjunto de células que precisam ser somadas.
2. Condição_range1. Este é o intervalo onde a condição será verificada. Ele é emparelhado com o próximo argumento – Condition1. A soma dos valores correspondentes ao critério é realizada dentro das células especificadas no argumento anterior.
3. Condição1. Este argumento especifica os critérios a serem verificados. Pode ser definido, por exemplo, desta forma: “> 32”.
4. Faixa de condição 2, Condição 2… Aqui, as seguintes condições são definidas da mesma maneira. Se mais de algumas condições precisarem ser especificadas, os argumentos Intervalo de Condição 3 e Condição 3 serão adicionados. A sintaxe é a mesma para os argumentos a seguir.

A função permite o processamento máximo de até 127 pares de condições e faixas. 

Você pode usá-lo em várias áreas ao mesmo tempo (daremos apenas algumas, a lista é ainda maior):

1. Contabilidade. Por exemplo, é bom usar a função SUMMESLIMN para criar relatórios resumidos, por trimestre para gastos acima de um determinado valor, por exemplo. Ou crie um relatório sobre um produto de uma determinada categoria de preço.
2. Gerenciamento de vendas. Aqui também a função pode ser muito útil. Por exemplo, nos deparamos com a tarefa de resumir apenas o custo das mercadorias que foram vendidas a um determinado cliente em um determinado momento. E em tal situação, a função SUMMESLIMN pode ser muito útil.
3. Educação. Daremos exemplos mais práticos desta área hoje. Em particular, você pode usá-lo para obter um resumo das notas dos alunos. Você pode escolher por uma única disciplina ou por notas individuais. Uma pessoa pode definir imediatamente vários critérios pelos quais a avaliação será selecionada, o que é muito conveniente e pode economizar muito tempo.

Como você pode ver, a gama de aplicações para esta função é muito ampla. Mas este não é o seu único mérito. Vamos dar uma olhada em mais alguns benefícios que esse recurso tem:

1. Capacidade de definir vários critérios. Por que isso é uma vantagem? Você pode usar a função normal SUMMESLI! E tudo porque é conveniente. Não há necessidade de realizar cálculos separados para cada um dos critérios. Todas as ações podem ser programadas com antecedência, mesmo antes. como a tabela de dados será formada. Esta é uma grande economia de tempo.
2. Automação. A era moderna é a era da automação. Somente a pessoa que sabe automatizar adequadamente seu trabalho pode ganhar muito. É por isso que a capacidade de dominar o Excel e a função SUMMESLIMN em particular, é tão importante para qualquer pessoa que queira construir uma carreira. Conhecer uma função permite que você execute várias ações ao mesmo tempo, como uma só. E aqui passamos para o próximo benefício desse recurso.
3. Economizando tempo. Apenas devido ao fato de que uma função executa várias tarefas ao mesmo tempo.
4. Simplicidade. Apesar da sintaxe ser bastante pesada à primeira vista devido ao seu volume, na verdade, a lógica dessa função é muito simples. Primeiro, um intervalo de dados é selecionado, depois um intervalo de valores, que será verificado quanto à conformidade com uma determinada condição. E, claro, a própria condição também deve ser especificada. E assim várias vezes. Na verdade, esta função é baseada em apenas uma construção lógica, o que a torna mais simples do que a conhecida VPR apesar de poder ser utilizado para os mesmos fins, tendo também em conta um maior número de critérios. 

Recursos do uso da função SOMASES

Existem vários recursos de uso desta função que você precisa prestar atenção. Em primeiro lugar, esta função ignora intervalos com strings de texto ou nulos, pois esses tipos de dados não podem ser somados em um padrão aritmético, apenas concatenados como strings. Esta função não pode fazer isso. Você também precisa prestar atenção às seguintes condições:

1. Você pode usar esses tipos de valores como condições para selecionar células para adicionar ainda mais os valores contidos nelas: valores numéricos, expressões booleanas, referências de células e assim por diante. 
2. Se o texto, as expressões lógicas ou os sinais matemáticos estiverem sendo verificados, esses critérios serão especificados por meio de aspas.
3. Não é possível usar termos com mais de 255 caracteres.
4. É possível usar critérios aproximados para selecionar valores usando curingas. O ponto de interrogação é usado para substituir um único caractere e o sinal de multiplicação (asterisco) é necessário para substituir vários caracteres. 
5. Os valores booleanos que estão no intervalo de somatório são convertidos automaticamente em valores numéricos de acordo com seu tipo. Assim, o valor “TRUE” se transforma em um, e “FALSE” – em zero. 
6. Se um #VALOR! erro aparecer em uma célula, isso significa que o número de células nos intervalos de condição e soma é diferente. Você precisa ter certeza de que os tamanhos desses argumentos são os mesmos. 

Exemplos de uso da função SOMASES

função SUMMESLIMN não é tão complicado quanto parece à primeira vista, ao que parece. Mas para maior clareza, vejamos alguns exemplos práticos de como você pode usar a função SUMMESLIMN. Assim ficará muito mais fácil se aprofundar no assunto.

Faixa dinâmica de soma de condições

Então vamos começar com o primeiro exemplo. Digamos que temos uma tabela que contém informações sobre como os alunos lidam com o currículo de uma determinada disciplina. Há um conjunto de notas, o desempenho é avaliado em uma escala de 10 pontos. A tarefa é encontrar a nota para o exame daqueles alunos cujo sobrenome começa com a letra A e sua nota mínima é 5.

A mesa fica assim.

Para que possamos calcular a pontuação total com base nos critérios descritos acima, precisamos aplicar a seguinte fórmula.

Vamos descrever os argumentos com mais detalhes:

1. C3:C14 é o nosso intervalo de soma. No nosso caso, coincide com o intervalo de condição. A partir dele serão selecionados os pontos usados ​​para calcular o valor, mas apenas aqueles que se enquadram em nossos critérios.
2. “>5” é nossa primeira condição.
3. B3:B14 é o segundo intervalo de soma que é processado para corresponder ao segundo critério. Vemos que não há coincidência com o intervalo de somatório. A partir disso, concluímos que o alcance da soma e o alcance da condição podem ou não ser idênticos. 
4. “A*” é o segundo intervalo, que especifica a seleção de notas apenas para os alunos cujo sobrenome começa com A. No nosso caso, um asterisco significa qualquer número de caracteres. 

Após os cálculos, obtemos a seguinte tabela.

Como você pode ver, a fórmula resumiu os valores com base no intervalo dinâmico e com base nas condições especificadas pelo usuário.

Soma seletiva por condição no Excel

Agora suponha que queremos obter informações sobre quais mercadorias foram enviadas para quais países durante o último trimestre. Depois disso, encontre a receita total dos embarques de julho e agosto.

A tabela em si se parece com isso. 

Para determinar o resultado final, precisamos dessa fórmula.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Como resultado dos cálculos realizados por esta fórmula, obtemos o seguinte resultado.

Atenção! Essa fórmula parece bem grande, embora tenhamos usado apenas dois critérios. Se o intervalo de dados for o mesmo, você poderá reduzir significativamente o comprimento da fórmula, conforme mostrado abaixo.

Função SOMASES para somar valores em várias condições

Agora vamos dar outro exemplo para ilustrar. Nesse caso, a tabela permanece a mesma do caso anterior. 

Usamos a seguinte fórmula (mas escrevemos como uma fórmula de matriz, ou seja, inserimos através da combinação de teclas CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Depois da função SUMMESLIMN irá somar a matriz de valores com base nos critérios especificados na fórmula (ou seja, os países da China e Geórgia), a matriz resultante é somada pela função usual SOMA, que é escrito como uma fórmula de matriz.

Se as condições foram passadas como uma constante de matriz para mais de um par, a fórmula fornecerá um resultado incorreto.

Agora vamos dar uma olhada na tabela que contém os totais.

Como você pode ver, nós conseguimos. Com certeza você também terá sucesso. Grande sucesso neste campo. Esta é uma função muito simples que uma pessoa que acabou de colocar os pés no caminho do aprendizado do Excel pode entender. E já sabemos que a função SUMMESLIMN permite que você seja eficaz em qualquer campo de atividade, desde a contabilidade até a educação. Mesmo se você estiver construindo uma carreira em qualquer outra área que não tenha sido descrita acima, esse recurso ainda o ajudará a ganhar dinheiro. É por isso que ela é valiosa.

Mais importante ainda, permite que você economize tempo, que é, infelizmente, um recurso limitado. Parece que há alguns segundos para aplicar duas funções, mas quando você precisa executar um grande número de operações repetitivas, esses segundos somam horas que poderiam ser gastas em outra coisa. Portanto, recomendamos que você pratique o uso desse recurso. Além disso, é incrivelmente simples.