Neste artigo, aprenderemos a Como contar linhas se corresponderem a pelo menos n números no Excel.
Cenário:
Em palavras simples, ao trabalhar com dados de números. Precisamos extrair a contagem de linhas com pelo menos, no máximo ou exatamente n valores da linha particular que satisfaça os critérios mencionados no Excel. Por exemplo, se estiver calculando o número de alunos que estão com desempenho abaixo da média nos testes. Podemos realizar esse tipo de problema de contagem em uma longa lista usando a fórmula do Excel explicada a seguir.
Funções de fórmula usadas e sintaxe da fórmula:
A seguinte função estará em uso. Portanto, conheça um pouco antes de usá-lo.
- Função SUM
- Função MMULT
- Função TRANSPOSE
- Função COLUMN
Vamos construir uma fórmula a partir dessas funções declaradas.
Use a fórmula:
| { = SOMA (- (MMULT (- ((dados) <crítico), TRANSPOSE (COLUNA (dados) 0)) = n)) } |
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.
dados: monte de números como dados.
= n: número exato de ocorrências (use <= por no máximo ou >= para pelo menos ocorrências)
crit: critérios para combinar com
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo. Aqui, temos os dados de vendas para o ID de vendas específico em uma linha. Precisamos encontrar a contagem de ID de vendas em que o vendedor não concluiu as vendas de 40 por exatamente 2 vezes. Aqui, o critério é <40 e a ocorrência é 2 (= 2 para a ocorrência exata)
Fórmula:
| { = SOMA (- (MMULT (- ((C4: H15) <40), TRANSPOSE (COLUNA (C4: H15) 0)) = 2)) |
Explicação:
- A função COLUMN retorna a matriz do número da coluna da primeira célula na matriz até a última célula na matriz. {3; 4; 5; 7; 8}
- 0, esta operação com qualquer número de matriz retorna uma matriz de 1s.
- A função TRANSPOSE converte as linhas da matriz da matriz em colunas e as colunas em linhas. Aqui, a matriz de 1s em linha é convertida em uma matriz de 6 (1s) em colunas. {1; 1; 1; 1; 1; 1}
- - ((C4: H15) <40), esta operação retorna os valores como 1s como valores VERDADEIROS (critérios correspondentes) e 0s como valores falsos.
- A função MMULT leva a multiplicação da matriz dos 2 arrays e retorna valores conforme mostrado abaixo.
= SOMA (- ({1; 0; 1; 0; 2; 1; 0; 0; 1; 2; 3; 3} = 2))
- A função SUM conta os valores na matriz que corresponde a 2 conforme a ocorrência é necessária.
- Use Ctrl + Shift + Enter no lugar de para obter o resultado. Como esta é uma fórmula de matriz.
Aqui, os arrays para a função são fornecidos como referência de array. Use double (-) manualmente Pressione Enter para obter o resultado.
Como você pode ver na imagem acima, existem 2 vendedores que não concluíram nem mesmo 40 vendas. Esta é a contagem para as linhas correspondentes nos dados onde 2 números são menores que 40. Você pode personalizar a ocorrência de contagem e os critérios a serem verificados nas linhas da tabela. Segue abaixo o exemplo para obter o Count com diferentes critérios e diferentes ocorrências.
Conte as linhas que correspondem às vendas acima de 100 com 3 como ocorrência exata:
Agora, gostaríamos de saber quantos vendedores estão tendo um desempenho moderadamente bom em vendas. Para isso, precisamos obter a contagem de vendedores que realizaram vendas acima de 100 com exatamente 3 vezes.
Use a fórmula:
| = SOMA (- (MMULT (- ((C4: H15)> 100), TRANSPORAR (COLUNA (C4: H15) 0)) = 3)) |
> 100: o número deve ser maior que 100.
= 3: a ocorrência seja igual a 3
Aqui, os arrays para a função são fornecidos como referência de array. Use double (-) manualmente Pressione Enter para obter o resultado.
Como você pode ver, há 3 vendedores que estão moderadamente bem. Esta é a contagem para as linhas correspondentes nos dados onde 3 vendas são maiores que 100.
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas:
- A fórmula só funciona com números.
- As funções MMULT e TRANSPOSE são a função de matriz, portanto, inspecione antes de usar.
- 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 contar linhas se n valores correspondentes no Excel seja explicativo. Encontre mais artigos sobre fórmulas de contagem 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
Encontre a última linha de dados com números no Excel : Em uma faixa de valores de texto, encontre o último dado no Excel.
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 dependentes de outros valores de células no Excel.
COUNTIFS Correspondência de dois critérios : Conte as células que correspondem a dois critérios diferentes na lista do Excel.
COUNTIFS com OU para vários critérios : Conte as células com correspondência de vários critérios usando a função OU.
A função COUNTIFS no Excel : Contar células dependentes de outros valores de células.
Como usar Countif em VBA no Microsoft Excel : Conte células usando o código do Visual Basic for Applications.
Como usar curingas no excel : Conte células que combinam frases usando os curingas 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.