Note: The other languages of the website are Google-translated. Back to English

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 é:

{=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))}
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:

=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

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:

=SUM(--(MMULT(--(ISNUMBER(SEARCH("Joanna",A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))


Função relativa usada:

  • SOMA:
  • A função SUM do Excel retorna a soma dos valores fornecidos.
  • MMULT:
  • 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 for Excel - ajuda você a se destacar da multidão

Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.

  • Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
  • Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
  • Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
  • Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Faixa de opções do Excel (com Kutools para Excel instalado)

Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo 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 (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
Captura de tela do Excel (com guia Office instalado)
Comentários (0)
Ainda não há classificações. Seja o primeiro a avaliar!
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL