A função do SUMMESLE, bem como as SUMMESLES por dois critérios

  1. Pesquisar por tags
Truques 11 de junho de 2011 Dmitry 243582 visualizações

Imagine uma tabela na qual os nomes dos departamentos (ou contas, ou qualquer outra coisa) são listados em linhas em uma linha.

Soma as células por critério
É necessário calcular o valor total de cada departamento. Muitos fazem isso com um filtro e escrevem com canetas nas células.
Embora isso possa ser feito facilmente e simplesmente com apenas uma função - SUMMESLI .
SUMMESLES (SUMIF) - Aumenta as células que satisfazem uma determinada condição (apenas uma condição pode ser especificada). Esta função também pode ser usada se a tabela estiver dividida em colunas por períodos (mensalmente, em cada mês, três colunas - Receita | Despesa | Diferença) e você precisará calcular o valor total para todos os períodos apenas por Renda, Despesa e Diferença.

Existem três argumentos no total para o SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Intervalo (A1: A20000) - indica o intervalo com os critérios. Ou seja A coluna na qual procurar o valor indicado pelo argumento Criterion .
  • O critério (A1) é o valor (texto ou numérico, bem como a data) que deve ser encontrado no intervalo . Pode conter os caracteres curinga "*" e "?". Ou seja especificando o Critério "* massa *" para resumir os valores em que a palavra "massa" ocorre. Ao mesmo tempo, a palavra “massa” pode ocorrer em qualquer parte do texto, ou pode haver apenas uma palavra em uma célula. E especificando "massa *", todos os valores que começam com "massa" serão somados. "?" - substitui apenas um caractere, ou seja especificando "mas? a" você pode somar as linhas com o valor "massa" e o valor "máscara", etc.
    Se o critério for gravado em uma célula e você ainda precisar usar caracteres curinga, poderá criar um link para essa célula adicionando o necessário. Suponha que você precise somar os valores que contêm a palavra "total". A palavra "total" é escrita na célula A1, enquanto na coluna A pode haver vários valores ortográficos contendo a palavra "total": "totais para junho", "totais para julho", "totais para março". A fórmula deve ficar assim:
    = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - o sinal & (comercial) combina vários valores em um. Ou seja o resultado será "* resultado *".
    Para entender melhor o princípio de como as fórmulas funcionam, é melhor usar a ferramenta Calculate Formula : Como visualizar as etapas para calcular fórmulas
    Todos os critérios e critérios textuais com sinais lógicos e matemáticos devem ser colocados entre aspas duplas (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Se o critério for um número, as cotações não serão necessárias. Se você deseja encontrar um ponto de interrogação ou um asterisco diretamente, é necessário colocar um til (~) na frente dele.
    Sobre o til e seus recursos podem ser encontrados neste artigo: Como substituir / remover / localizar asterisco?
  • Sum_Range (B1: B20000) (argumento opcional) - especifica o intervalo de somas ou valores numéricos a serem somados.

Como funciona: a função pesquisa o Intervalo pelo valor especificado pelo argumento Criterion e, quando uma correspondência é encontrada, soma os dados indicados pelo argumento Range_Amount. Ou seja se tivermos um nome de departamento na coluna A e um valor na coluna B, a especificação do Departamento de Desenvolvimento como critério resultará na soma de todos os valores da coluna B, oposta à qual o Departamento de Desenvolvimento se encontra na coluna A. Na verdade, o SumArrangement pode não ter o mesmo tamanho do argumento Range e isso não causará um erro da função em si. No entanto, ao definir células para soma, a célula superior esquerda do argumento Range_Amount será usada como a célula inicial para soma e, em seguida, as células correspondentes em tamanho e forma ao argumento Range serão somadas.

Alguns recursos
O último argumento da função (Sum_And_Band: B1: B20000) é opcional. Isso significa que não pode ser especificado. Se você não especificá-lo, a função adicionará os valores especificados pelo argumento Range . O que é isso? Por exemplo, você precisa obter a soma apenas dos números maiores que zero. Na coluna A do valor. Então a função ficará assim:
= SUMMERS (A1: A20000; "> 0")

O que deve ser considerado: o range_summing e o intervalo devem ser iguais no número de linhas. Caso contrário, você pode obter o resultado errado. Idealmente, se for semelhante nas fórmulas que dei: o intervalo e o intervalo de somatórios começam de uma linha e têm o mesmo número de linhas: A1: A20000; B1: B20000

Soma sobre dois ou mais critérios
Mas o que fazer quando os critérios para o somatório 2 e mais? Suponha que você precise somar apenas os valores que pertencem a um departamento e apenas para uma determinada data. Os proprietários felizes das versões office 2007 e posteriores podem usar a função SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. O primeiro argumento especifica o intervalo de células que contém as quantias que serão coletadas em uma.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Especifica o intervalo de células em que você deseja procurar uma correspondência por critério.
$ I $ 3, $ H8 - critério. Aqui, como em SUMMESLI, os caracteres curinga * e ? São permitidos . e eles funcionam da mesma maneira.

Especificidades da especificação de argumentos: primeiro, o intervalo de critério é especificado (eles são numerados), então o valor (critério) é indicado diretamente no ponto e vírgula, que nesse intervalo deve ser encontrado - $ A $ 2: $ A $ 50; $ I $ 3. E mais nada. Você não deve tentar primeiro especificar todos os intervalos e, em seguida, os critérios para eles - a função dará um erro ou não resumirá o que é necessário.

Todas as condições são comparadas de acordo com o princípio I. Isso significa que se todas as condições listadas forem cumpridas. Se pelo menos uma condição não for atendida, a função pula a linha e não adiciona nada.
Quanto aos SUMMERS, os intervalos de soma e critérios devem ser iguais no número de linhas.

Porque SUMMESLIMN apareceu apenas em versões do Excel, a partir de 2007, então como usuários infelizes de versões anteriores podem estar em tais casos? Muito simples: use outra função - SUMPRODUCT. Eu não vou pintar os argumentos, porque Existem muitos deles e todos são conjuntos de valores. Esta função multiplica as matrizes indicadas pelos argumentos. Tentarei descrever o princípio geral de usar essa função para resumir dados sobre várias condições.
Para resolver o problema da soma por vários critérios, a função ficará assim:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - intervalo de datas. $ I $ 3 é a data do critério para o qual é necessário somar os dados.
$ B $ 2: $ B $ 50 - os nomes dos departamentos. H5 - o nome do departamento, cujos dados devem ser somados.
$ C $ 2: $ C $ 50 - faixa com valores.

Nós analisamos a lógica, porque para muitos, não será totalmente claro apenas olhando para esta função. Se apenas porque na ajuda esta aplicação não é descrita. Para maior legibilidade, reduza o tamanho dos intervalos:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Portanto, a expressão ($ A $ 2: $ A $ 5 = $ I $ 3) e ($ B $ 2: $ B $ 5 = H5) são lógicas e retornam matrizes de lógica FALSE e TRUE. VERDADEIRO se a célula do intervalo $ A $ 2: $ A $ 5 for igual ao valor da célula $ I $ 3 e a célula do intervalo $ B $ 2: $ B $ 5 for igual ao valor da célula H5. Ou seja nós temos o seguinte:
= SUMPRODUCT ({FALSE; VERDADE; VERDADEIRO; VERDADEIRO; FALSO} * {FALSO; FALSO; VERDADEIRO; FALSO}; $ C $ 2: $ C $ 50)
Como você pode ver, na primeira matriz há duas correspondências para a condição e na segunda. Além disso, essas duas matrizes são multiplicadas (o sinal de multiplicação (*) é responsável por isso). Quando a multiplicação ocorre, a conversão implícita de matrizes FALSE e TRUE para constantes numéricas 0 e 1, respectivamente ({0; 1; 1; 0} * {0; 0; 1; 0}) ocorre. Como você sabe, quando multiplicado por zero, obtemos zero. E o resultado é um único array:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Em seguida, a matriz {0; 0; 1; 0} é multiplicada por uma matriz de números no intervalo $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
E como resultado, obtemos 30. O que precisávamos - obtemos apenas o valor que atende ao critério. Se houver mais de uma soma satisfazendo o critério, elas serão somadas.

Vantagem do SUMMYROIZV
Se os argumentos tiverem o sinal de mais em vez do sinal de multiplicação:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
então as condições serão comparadas de acordo com o princípio da OR: ou seja, somas totais serão somadas se pelo menos uma condição for preenchida: $ A $ 2: $ A $ 5 é igual ao valor da célula $ I $ 3 ou célula do intervalo $ B $ 2: $ B $ 5 é igual ao valor da célula H5.
Essa é a vantagem do SUMMPRODUCT sobre SUMMESLIMN. SUMMESLIMN não pode somar valores de acordo com o princípio OR, somente de acordo com o princípio AND (todas as condições devem ser atendidas).

Desvantagens
O SUMPRODUCT não pode usar curingas * e? É possível usar com mais precisão, mas eles serão percebidos não como caracteres especiais, mas como um asterisco e um ponto de interrogação. Eu acho que isso é uma desvantagem significativa. E embora isso possa ser contornado, eu uso outras funções dentro do SUMPRODUCT - ainda seria ótimo se a função pudesse de algum modo usar curingas.

No exemplo, você encontrará alguns exemplos de funções para entender melhor o que está escrito acima.

Baixe um exemplo

Quantidade por vários critérios (41,5 KiB, 10,477 downloads)

Veja também:
Somando células por cor de preenchimento
Soma das células por cor da fonte
Somando células por formato de célula
Calcular a quantidade de células por cor de preenchimento
Calcular a quantidade de células por cor de fonte
Como somar dados de várias planilhas, inclusive por condição

Artigo ajudou? Compartilhe o link com seus amigos! Tutoriais em vídeo

{"Barra inferior": {"textstyle": "estático", "textpositionstatic": "bottom", "textautohide": verdadeiro, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" esquerda "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": falso, "texteffect1": "slide", "texteffectslidedirection1": "direito", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "direita", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: bloco; preenchimento: 12px; alinhamento de texto: esquerdo; "," textbgcss ":" display: bloco; posição: absoluto; início: 0px; esquerda: 0px; largura: 100%; altura: 100% ; cor de fundo: # 333333; opacidade: 0.6; filtro: a lpha (opacidade = 60); "," titlecss ":" display: bloco; posição: relativa; fonte: negrito 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sem serifa, Arial; color: #fff; "," descriptioncss ":" display: bloco; posição: relativa; fonte: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sem serifa, Arial; cor: #fff; margin-top: 8px; "," buttoncss ":" display: bloco; posição: relativa; margin-top: 8px; "," texteffectresponsive ": verdadeiro," texteffespesponsivesize ": 640," titlecssresponsive ":" tamanho da fonte: 12px; "," descriçãocssesponsiva ":" display: nenhum: importante; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Pesquisar por tags

Ir Acesso relógio de maçã Multex Outlook Power Query e Power BI Trabalho de VBA no editor Gerenciamento de código VBA Suplementos gratuitos Data e hora Tabelas e gráficos Anotações Proteção de dados A internet Fotos e objetos Folhas e livros Macros e VBA Complementos Personalização Impressão Dados de pesquisa Política de Privacidade Mail Programas Trabalhar com aplicativos Trabalhar com arquivos Desenvolvimento de aplicativos Tabelas Resumidas Listas Treinamentos e webinars Financeiro Formatação Fórmulas e funções Funções do Excel Funções VBA Células e intervalos Ações MulTEx análise de dados bugs e falhas no Excel ligações Pode conter os caracteres curinga "*" e "?
Quot;?
Quot; - substitui apenas um caractere, ou seja especificando "mas?
O que é isso?
Porque SUMMESLIMN apareceu apenas em versões do Excel, a partir de 2007, então como usuários infelizes de versões anteriores podem estar em tais casos?
IRC (Internet Relay Chat)