Contar o número de células que não contêm muitos valores
Normalmente, é fácil contar células que não contêm um valor específico com a função CONT.SE. Esta postagem explicará como contar o número de células que não contêm muitos valores em um intervalo especificado no Excel.
Como contar células que não contêm muitos valores?
Conforme mostrado na captura de tela abaixo, para contar células em B3: B11 que não contêm valores listados em D3: D4, você pode fazer o seguinte.
Fórmula Genérica
{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}
Argumentos
Variação (obrigatório): o intervalo do qual você deseja contar as células que não contêm muitos valores.
Intervalo_de_critérios (obrigatório): o intervalo contém os valores que você deseja excluir ao contar células.
Observação: Esta fórmula deve ser inserida como uma fórmula de matriz. Depois de aplicar a fórmula, se houver colchetes ao redor da fórmula, uma fórmula de matriz é criada com êxito.
Como usar essa fórmula?
1. .Selecione uma célula em branco para produzir o resultado.
2. Insira a fórmula abaixo e pressione o botão Ctrl + Shift + Entrar simultaneamente para obter o resultado.
=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))
Como essas fórmulas funcionam?
=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))
1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):
- TRANSPOSE(D3:D4): A função TRANSPOSE gira a orientação de D3: D4 e retorna {“Contar”, ”em branco”};
- SEARCH({“count”,”blank”},B3:B11): A função SEARCH aqui encontra a posição da substring “contagem” e “em branco” do intervalo B3: B11, e retorna uma matriz como {#VALOR!, # VALOR!; # VALOR!, # VALOR!; 1, # VALOR!; # VALOR!, 8; 1, # VALOR!; # VALOR!, # VALOR!; # VALOR!, # VALOR! ; 1,
#VALUE!; 1,7}. - Nesse caso, cada célula em B3: B11 será pesquisada duas vezes, pois há dois valores que você deseja excluir ao contar células, portanto, você obterá 18 valores na matriz. Cada número na matriz indica a posição do primeiro caractere de “contagem” ou “espaço em branco” em cada célula de B3: B11.
- ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
#VALUE!;1,#VALUE!;1,7}: A função ISNUMBER retorna TRUE se encontrar números na matriz e retorna FALSE se encontrar erros. Aqui retorna o resultado como {FALSE, FALSE; FALSE, FALSE; TRUE, FALSE; FALSE, TRUE; TRUE, FALSE; FALSE, FALSE; FALSE, FALSE; TRUE, FALSE;
VERDADE VERDADE}. - --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
FALSE;TRUE,TRUE}): Estes dois sinais de menos convertem “VERDADEIRO” em 1 e convertem “FALSO” em 0. Aqui você obterá uma nova matriz como {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.
2) ROW(D3:D4)^0: A função ROW retorna os números das linhas da referência de célula: {3; 4} e, em seguida, o operador de exponenciação (^) retorna o resultado do número 3 e 4 elevado à potência 0 e, finalmente, retorna o resultado como {1; 1}.
3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}):A função MMULT retorna o produto da matriz dessas duas matrizes: {0; 0; 1; 1; 1; 0; 0; 1; 2; XNUMX} para atender aos dados originais. Qualquer número diferente de zero na matriz representa um valor em que pelo menos uma das strings excluídas foi encontrada e zero indica que nenhuma string excluída foi encontrada.
4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):
- {0;0;1;1;1;0;0;1;2}>0: Aqui, verifique se cada número na matriz é maior que 0. Se um número for maior que 0, retorna VERDADEIRO; caso contrário, retorna FALSO. Então você obterá uma nova matriz como {FALSO; FALSO; VERDADEIRO; VERDADEIRO; VERDADEIRO; FALSO, FALSO, VERDADEIRO; VERDADEIRO}.
- 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Como precisamos contar apenas as células que não contêm os valores especificados, precisamos inverter esses valores na matriz subtraindo-os de 1. Aqui, o operador matemático converte automaticamente os valores VERDADEIRO e FALSO em 1s e 0s e, finalmente, retorna o resultado como {1;1;0;0;0;1;1;0;0}.
- SUM{1;1;0;0;0;1;1;0;0}: A função SUM soma todos os números da matriz e retorna o resultado final como 4.
Funções relacionadas
Função SUM do Excel
A função SUM do Excel adiciona valores.
Função Excel MMULT
A função Excel MMULT retorna o produto da matriz de duas matrizes.
Função ISNUMBER do Excel
A função ISNUMBER do Excel retorna TRUE quando uma célula contém um número e FALSE se não.
Função Excel TRANSPOSE
A função TRANSPOSE do Excel gira a orientação de um intervalo ou matriz.
Função Excel ROW
A função ROW do Excel retorna o número da linha de uma referência.
Fórmulas Relacionadas
Contar células que não contêm erros
Este tutorial fornece etapas detalhadas para ajudá-lo a contar o número de células que não contêm erros em um intervalo especificado no Excel.
Contar células que não contêm texto específico
Você pode usar a função CONT.SE com um caractere curinga para contar o número de células que contêm texto específico em um intervalo. Pelo contrário, também é fácil usar a função CONT.SE para contar células que não contêm texto específico. Este tutorial fornece etapas detalhadas para ajudar a resolver o problema.
Contar o número de células de data em um intervalo por dia da semana
O Excel fornece a combinação das funções SUMPRODUCT e WEEKDAY para nos ajudar a contar facilmente o número de dias da semana especificados dentro de um intervalo. Este tutorial fornece um guia passo a passo para ajudá-lo a superar isso.
Conte o número de células de texto
Para contar o número de células que contêm texto em um intervalo especificado, a função CONT.SE pode ajudar a fazer isso facilmente. Aqui, este artigo apresentará a fórmula em detalhes para ajudá-lo a resolver o problema.
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.
