1. Pesquisar por tags

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

{"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