Neste artigo, aprenderemos Contar vários intervalos com um critério no Microsoft Excel.
Cenário:
Em palavras simples, ao trabalhar com tabelas de dados, às vezes precisamos contar as células onde mais de dois intervalos atendem aos critérios. No Excel, você pode realizar tarefas como operações em vários intervalos usando a fórmula explicada a seguir. Os critérios podem ser aplicados sobre texto, número ou qualquer correspondência parcial no Excel. Critérios dentro da fórmula executada usando os operadores. Operadores como é igual a ( = ), menor que igual a ( <= ), Maior que ( > ) ou não é igual a ().
Como resolver o problema?
Para este problema, seremos obrigados a usar a função SUMPRODUCT. Agora faremos uma fórmula da função. Aqui, temos dois intervalos de dados e precisamos contar as linhas que atendem a 3 critérios. A função SUMPRODUCT retorna a SOMA dos valores TRUE correspondentes (como 1) e ignora os valores correspondentes aos valores FALSE (como 0) na matriz retornada
Fórmula genérica:
| = SUMPRODUCT ((rng_1 op_1 crit_1) + 0, (rng_2 op_2 crit_1) + 0, rng_2 op_2 crit_1) + 0) |
rng: intervalo a procurar
crit: critérios a serem aplicados
op: operador de critérios, condição fornecida como operador entre intervalo e critérios
+0: converte valores booleanos em binários (0 e 1).
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo. Aqui, precisamos encontrar a contagem de linhas listadas no intervalo com 3 condições. Aqui temos uma lista das reuniões diplomáticas realizadas entre a Índia e os EUA a partir de 2014. A tabela mostra o Presidente / PM com a etiqueta do país e o ano. A tabela também está dividida em partes representando o país de origem e a lista de países visitantes.
Condições listadas abaixo:
O presidente dos EUA "Barack Obama visitou a Índia com menos de 2 problemas.
Use a fórmula:
| = SUMPRODUTO ((C4: C10 = "Barack Obama") + 0, (F4: F10 = "Índia") + 0, (G4: G10 <2) + 0)) |
C4: C10 = "Barack Obama": Presidente que corresponde a "Barack Obama" na lista de visitantes.
F4: F10 = "Índia": país anfitrião que corresponde a "Índia".
G4: G10 <2: questões menores que dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, apenas uma vez o presidente dos EUA, Barack Obama, visitou a Índia, o que aconteceu em 2015. Isso mostra que a fórmula extrai a contagem de vezes correspondida na matriz correspondente. Como também houve uma única vez em que o presidente dos Estados Unidos "Barack Obama" visitou a Índia, onde as questões também são iguais a 1, que é menor que 2.
Com igual aos critérios:
O exemplo acima foi fácil. Então, para torná-lo interessante, contaremos quantas vezes os EUA hospedaram a Índia a partir de 2014 como dados.
Condições listadas abaixo:
Os EUA hospedaram a Índia com problemas é igual a 2.
Use a fórmula:
| = SUMPRODUTO ((F4: F10 = "EUA") + 0, (D4: D10 = "Índia") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "US": país anfitrião que corresponde a "US".
D4: D10 = "Índia": país visitante que corresponde a "Índia".
G4: G10 = 2: questões são iguais a dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, houve 2 ocasiões em que os EUA sediaram a Índia e os problemas foram iguais a dois. Isso mostra que a fórmula extrai a contagem de vezes correspondidas na matriz correspondente. Como houve 5 vezes em que os EUA sediaram a Índia, mas os problemas foram 1 ou 3, mas aqui precisamos que os problemas sejam correspondidos com 2.
Com critérios maiores que:
Aqui, para torná-lo interessante, contaremos quantas vezes o presidente dos EUA "Donald Trump" hospedou o primeiro-ministro indiano a partir de 2014 como dados.
Condições listadas abaixo:
O presidente dos EUA, "Donald Trump", hospedou a Índia com problemas maiores que 1.
Use a fórmula:
| = SUMPRODUTO ((C4: C10 = "Donald Trump") + 0, (F4: F10 = "Índia") + 0, (G4: G10> 1) + 0) |
F4: F10 = "EUA": presidente anfitrião correspondendo a "Donald Trump".
D4: D10 = "Índia": país visitante que corresponde a "Índia".
G4: G10 = 2: questões são iguais a dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, uma vez em que o presidente dos EUA "Donald Trump" hospedou a Índia e questões maiores do que dois. Isso mostra que a fórmula extrai a contagem de vezes correspondidas na matriz correspondente. Como houve 2 vezes em que o presidente dos EUA "Donald Trump" hospedou a Índia, mas os problemas foram 1 ou 3, mas aqui precisamos que os problemas sejam maiores do que 1, que é 3 está no ano de 2019.
Com questões não consideradas nos critérios:
Aqui, para tornar a compreensão mais fácil e conveniente, contaremos quantas vezes no total o presidente dos EUA visitou a Índia a partir de 2014 como dados.
Condições listadas abaixo:
O presidente dos EUA visitou a Índia no total desde 2014.
Use a fórmula:
| = SUMPRODUCT ((F4: F10 = "Índia") + 0, (D4: D10 = "EUA") + 0) |
F4: F10 = "US": país anfitrião que corresponde a "US".
D4: D10 = "Índia": país visitante que corresponde a "Índia".
G4: G10 = 2: questões são iguais a dois.
+0: converte valores booleanos em binários (0 e 1).
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a contagem.
Como você pode ver, 2 vezes em que os EUA visitaram a Índia e em edições superiores a duas. Isso mostra que a fórmula extrai a contagem de vezes correspondidas na matriz correspondente. Como houve uma vez em que o presidente dos Estados Unidos "Barack Obama" visitou a Índia em 2015 e uma vez em que o presidente dos Estados Unidos "Donald Trump" visitou a Índia no ano de 2020.
Você também pode executar intervalos como critérios. Conte as células em que 2 intervalos atendem aos critérios. Saiba mais sobre Countif com SUMPRODUCT no Excel aqui.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula só funciona com números.
- As matrizes na fórmula devem ter comprimento igual, pois a fórmula retorna erro quando não.
- A função SUMPRODUCT considera os valores não numéricos como 0s.
- A função SUMPRODUCT considera o valor lógico TRUE como 1 e False como 0.
- A matriz do argumento deve ter o mesmo tamanho, caso contrário, a função retornará um erro.
- A função SUMPRODUCT retorna a soma depois de obter produtos individuais na matriz correspondente.
Espero que este artigo sobre Contagem de vários intervalos com um critério no Microsoft Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas 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 para nós no site de e-mail.
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 SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
COUNTIFS com intervalo de critérios dinâmicos : Contar células selecionando os critérios da lista de opções na célula de critérios no Excel usando a ferramenta de validação de dados.
COUNTIFS Correspondência de dois critérios : correspondência de vários critérios em listas diferentes na tabela usando a função COUNTIFS no Excel
COUNTIFS com OU para vários critérios : corresponde a dois ou mais nomes na mesma lista usando os critérios OU aplicados na lista no Excel.
Como usar Countif em VBA no Microsoft Excel : Conte células com critérios usando o código do Visual Basic for Applications em macros do Excel.
Como usar curingas no excel : Conte células que correspondem a frases em listas de texto usando os curingas ( * , ? , ~ ) no excel
Artigos populares :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV 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 SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
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.