Como contar células filtradas com texto no Excel?
No Excel, contar células com texto é simples. Mas quando se trata de contar células filtradas com texto, as coisas ficam complicadas. Este tutorial introduzirá três maneiras de contar as células com texto em uma lista filtrada.
Contar células de texto filtradas usando uma coluna auxiliar
Contar células de texto filtradas usando as funções SOMARPRODUTO, SUBTOTAL, INDIRETO, LINHA e ISTEXT
Contar células de texto filtradas usando uma coluna auxiliar
Usando a função CONT.SE, juntamente com a ajuda de uma coluna auxiliar, podemos facilmente contar as células de texto filtradas. Por favor, faça o seguinte.
1. Por favor, copie a fórmula abaixo para a célula D2, depois pressione a tecla "Enter" para obter o primeiro resultado.
=SUBTOTAL (103, A2)
Nota: A coluna auxiliar com a fórmula SUBTOTAL serve para testar se a linha está filtrada ou não. E "103" representa a função "CONT.VALORES" no argumento "function_num".
2. Em seguida, arraste a alça de preenchimento para baixo até as células onde você deseja aplicar esta fórmula.
3. Por favor, copie a fórmula abaixo na célula "F2", depois pressione a tecla "Enter" para obter o resultado final.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Podemos ver que existem "4" células com texto nos dados filtrados.
Contar células de texto filtradas usando as funções SOMARPRODUTO, SUBTOTAL, INDIRETO, LINHA e ISTEXT
Outro método para contar células filtradas com texto é usar a "combinação" das funções "SOMARPRODUTO", "SUBTOTAL", "INDIRETO", "LINHA" e "ISTEXT". Por favor, faça o seguinte.
Por favor, copie a fórmula abaixo na célula "E2", depois pressione a tecla "Enter" para obter o resultado.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Explicação da Fórmula:
- "LIN(A2:A18)" retorna os respectivos números de linha do intervalo A2:A18.
- "INDIRETO("A"&LIN(A2:A18))" retorna referências válidas de células do intervalo fornecido.
- "SUBTOTAL(103, INDIRETO("A"&LIN(A2:A18)))" verifica se a linha está filtrada ou não, retornando 1 para as células visíveis, 0 para as células ocultas e vazias.
- "ISTEXT(A2:A18)" verifica se cada célula no intervalo A2:A18 contém texto, retornando Verdadeiro para as células com texto, Falso para as outras células. O operador unário duplo (--) converte os valores VERDADEIRO e FALSO em 1's e 0's.
- "SOMARPRODUTO(SUBTOTAL(103, INDIRETO("A"&LIN(A2:A18))), --(ISTEXT(A2:A18)))" pode ser visto como "SOMARPRODUTO({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})". Então, o SOMARPRODUTO multiplica os dois arrays juntos e retorna a soma dos valores, que é 4.
Contar células de texto filtradas usando as funções SOMARPRODUTO, SUBTOTAL, DESLOCAMENTO, MÍNIMO, LINHA e ISTEXT
O terceiro método para contar células com texto em dados filtrados é "concatenar" as funções "SOMARPRODUTO", "SUBTOTAL", "DESLOCAMENTO", "MÍNIMO", "LINHA" e "ISTEXT". Por favor, faça o seguinte.
Por favor, copie a fórmula abaixo na célula "E2", depois pressione a tecla "Enter" para obter o resultado.
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)))
Explicação da Fórmula:
- "DESLOCAMENTO(A2:A18, LIN(A2:A18)-2 -- MÍN(LIN(A2:A18)-2),,1)" retorna as referências individuais de células do intervalo A2:A18.
- "SUBTOTAL(103, DESLOCAMENTO(A2:A18, LIN(A2:A18)-2 -- MÍN(LIN(A2:A18)-2),,1))" verifica se a linha está filtrada ou não, retornando 1 para as células visíveis, 0 para as células ocultas e vazias.
- "ISTEXT(A2:A18)" verifica se cada célula no intervalo A2:A18 contém texto, retornando Verdadeiro para as células com texto, Falso para as outras células. O operador unário duplo (--) converte os valores VERDADEIRO e FALSO em 1's e 0's.
- "SOMARPRODUTO(SUBTOTAL(103, DESLOCAMENTO(A2:A18, LIN(A2:A18)-2 -- MÍN(LIN(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)))" pode ser visto como "SOMARPRODUTO({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})". Então, o SOMARPRODUTO multiplica os dois arrays juntos e retorna a soma dos valores, que é 4.
Outras Operações (Artigos)
Combine As Funções CONT.SE e ESQUERDA Para Contar Células Que Começam Com Um Determinado Caractere No Excel
Para contar o número de células que começam com um determinado caractere no Excel, você pode combinar as funções CONT.SE e ESQUERDA para fazer isso. Na verdade, há diferentes tipos de fórmulas que podem alcançá-lo. Este tutorial irá guiá-lo passo a passo.
Como Contar Células Que Contêm Números Ou Não No Excel?
Se houver um intervalo de células, algumas contendo números e outras contendo texto, como você pode contar rapidamente as células que contêm números ou não no Excel?
Como Contar Células Se Um Dos Vários Critérios For Atendido No Excel?
E se contar células que contenham um de vários critérios? Aqui eu vou compartilhar as maneiras de contar células que contenham X ou Y ou Z... etc. no Excel.
Como Contar Células Com Texto Específico E Cor De Preenchimento/Fonte No Excel?
Você sabe como contar células com várias condições? Por exemplo, conte o número de células que contêm tanto texto específico quanto cor de fonte/preenchimento. Este artigo mostrará a solução.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência sem igual. Kutools para Excel oferece mais de300 recursos avançados para aumentar sua produtividade e economizar tempo.Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface de abas para o Office e facilita muito seu trabalho
- Habilite a edição e leitura com abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas da mesma janela, em vez de novas janelas.
- Aumente sua produtividade em50% e reduza centenas de cliques do mouse todos os dias!