Contar o número de linhas que contêm valores específicos no Excel
Pode ser fácil para nós contar o número de células com valor específico em uma planilha do Excel. No entanto, obter o número de linhas que contêm valores específicos pode ser bastante complicado. Neste caso, uma fórmula mais complexa baseada nas funções SUM, MMULT, TRANSPOSE e COLUMN pode lhe ajudar. Este tutorial vai falar sobre como criar essa fórmula para lidar com esse trabalho no Excel.
Contar o número de linhas que contêm valores específicos
Por exemplo, você tem um intervalo de valores em uma planilha e agora, você precisa contar o número de linhas com um determinado valor "300", conforme a captura de tela mostrada abaixo:
Para obter o número de linhas que contêm valores específicos, a sintaxe genérica é:
Array formula, should press Ctrl + Shift + Enter keys together.
- data: O intervalo de células a verificar se contém o valor específico;
- X: O valor específico que você usa para contar as linhas.
1. Insira ou copie a fórmula abaixo em uma célula em branco onde deseja colocar o resultado:
2. Então aperte Ctrl + Shift + Enter juntas para obter o resultado correto, veja a captura de tela:
Explicação da fórmula:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- - $ A $ 2: $ C $ 12 = 300: Esta expressão verifica se o valor “300” existe no intervalo A2: C12 e irá gerar um resultado de matriz TRUE e False. O sinal duplo negativo é usado para converter TRUEs em 1s e FALSEs em 0s. Então, você obterá o resultado como este: {0,0,0; 1,0,0; 0,0,0; 0,1,1; 0,0,0; 0,1,0; 0,0,0 , 1,0,0; 0,0,1; 0,0,0; 1,1,1; 11}. A matriz que consiste em 3 linhas e 1 colunas funcionará como a matrizXNUMX na função MMULT.
- TRANSPOSE (COLUNA ($ A $ 2: $ C $ 12)): A função COLUMN aqui é usada para obter o número da coluna do intervalo A2: C12, ela retorna uma matriz de 3 colunas como esta: {1,2,3}. E então a função TRANSPOSE troca esta matriz para uma matriz de 3 linhas {1; 2; 3}, funcionando como a matriz2 dentro da função MMULT.
- MMULT (- ($ A $ 2: $ C $ 12 = "Joanna"), TRANSPOSE (COLUNA ($ A $ 2: $ C $ 12))): Esta função MMULT retorna o produto da matriz das duas matrizes acima, você obterá o resultado como este: {0; 1; 0; 5; 0; 2; 0; 1; 3; 0; 6}.
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Primeiro, verifique os valores na matriz maiores que 0: Se um valor for maior que 0, um VERDADEIRO é exibido; se menor que 0, um FALSE é exibido. E então o sinal duplo negativo força os TRUEs e FALSEs a serem 1s e 0s, então você obterá isto: SUM ({0; 1; 0; 1; 0; 1; 0; 1; 1; 0; 1}). Finalmente, a função SUM soma os valores na matriz para retornar o resultado: 6.
Dicas:
Se você precisar contar o número de linhas que contêm um texto específico em uma planilha, aplique a fórmula abaixo e lembre-se de pressionar o Ctrl + Shift + Enter chaves juntas para obter a contagem total:
Função relativa usada:
- SOMA:
- A função SUM do Excel retorna a soma dos valores fornecidos.
- MULTO:
- A função Excel MMULT retorna o produto da matriz de duas matrizes.
- TRANSPOR:
- A função TRANSPOSE retornará uma matriz em uma nova orientação com base em um determinado intervalo de células.
- COLUNA:
- A função COLUMN retorna o número da coluna cuja fórmula aparece ou retorna o número da coluna da referência fornecida.
Mais artigos:
- Contar linhas se atender aos critérios internos
- Suponha que você tenha um relatório de vendas de produtos deste ano e do ano passado, e agora, você pode precisar contar os produtos em que as vendas neste ano são maiores do que no ano passado, ou as vendas neste ano são menores do que no ano passado, conforme abaixo captura de tela mostrada. Normalmente, você pode adicionar uma coluna auxiliar para calcular a diferença de venda entre os dois anos e, em seguida, usar CONT.SE para obter o resultado. Mas, neste artigo, apresentarei a função SUMPRODUCT para obter o resultado diretamente, sem nenhuma coluna auxiliar.
- Contar linhas se atenderem a vários critérios
- Conte o número de linhas em um intervalo com base em vários critérios, alguns dos quais dependem dos testes lógicos que funcionam no nível de linha. A função SUMPRODUCT no Excel pode lhe fazer um favor.
- Contar o número de células é igual a um de muitos valores
- Supondo que eu tenha uma lista de produtos na coluna A, agora, desejo obter o número total de produtos específicos Maçã, Uva e Limão listados no intervalo C4: C6 da coluna A, conforme a captura de tela abaixo. Normalmente, no Excel, as funções CONT.SE e CONT.SE não funcionarão neste cenário. Neste artigo, vou falar sobre como resolver esse trabalho de forma rápida e fácil com a combinação das funções SUMPRODUCT e COUNTIF.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.
