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. Este post mostrará 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?

Como mostra a 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

Nota: Esta fórmula deve ser inserida como uma fórmula de matriz. Após aplicar a fórmula, se houver chaves envolvendo a fórmula, uma fórmula de matriz foi criada com sucesso.

Como usar esta fórmula?

1. Selecione uma célula em branco para exibir o resultado.

2. Insira a fórmula abaixo nela e pressione simultaneamente as teclas Ctrl + Shift + Enter para obter o resultado.

=SOMA(1-(MMULT(--(ÉNÚM(PROCURAR(TRANSPOR(D3:D4);B3:B11));LIN(D3:D4)^0)>0))

Como essas fórmulas funcionam?

=SOMA(1-(MMULT(--(ÉNÚM(PROCURAR(TRANSPOR(D3:D4);B3:B11));LIN(D3:D4)^0)>0))

1) --(ÉNÚM(PROCURAR(TRANSPOR(D3:D4);B3:B11))):

• TRANSPOR(D3:D4): A função TRANSPOR gira a orientação de D3:D4 e retorna {“contar”,”em branco”};
• PROCURAR({“contar”,”em branco”};B3:B11): A função PROCURAR aqui encontra a posição da substring “contar” e “em branco” no intervalo B3:B11 e retorna uma matriz como {#VALOR!,#VALOR!;#VALOR!,#VALOR!;1,#VALOR!;#VALOR!,8;1,#VALOR!;#VALOR!,#VALOR!;#VALOR!,#VALOR!;1,
  #VALOR!;1,7}.
• Neste caso, cada célula em B3:B11 será pesquisada duas vezes, pois há dois valores que você deseja excluir ao contar células, então você obterá 18 valores na matriz. Cada número na matriz indica a posição do primeiro caractere de “contar” ou “em branco” em cada célula de B3:B11.
• ÉNÚM{#VALOR!,#VALOR!;#VALOR!,#VALOR!;1,#VALOR!;#VALOR!,8;1,#VALOR!;#VALOR!,#VALOR!;#VALOR!,
  #VALOR!;1,#VALOR!;1,7}: A função ÉNÚM retorna VERDADEIRO se encontrar números na matriz e retorna FALSO se encontrar erros. Aqui retorna o resultado como {FALSO,FALSO;FALSO,FALSO;VERDADEIRO,FALSO;FALSO,VERDADEIRO;VERDADEIRO,FALSO;FALSO,FALSO;FALSO,FALSO;VERDADEIRO,FALSO;
  VERDADEIRO,VERDADEIRO}.
• --({FALSO,FALSO;FALSO,FALSO;VERDADEIRO,FALSO;FALSO,VERDADEIRO;VERDADEIRO,FALSO;FALSO,FALSO;FALSO,FALSO;VERDADEIRO,
  FALSO;VERDADEIRO,VERDADEIRO}): Esses 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) LIN(D3:D4)^0: A função LIN retorna os números das linhas da referência de célula: {3;4}, e depois o operador de exponenciação (^) retorna o resultado dos números 3 e 4 elevados à 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 matricial dessas duas matrizes: {0;0;1;1;1;0;0;1;2} para atender aos dados originais. Qualquer número diferente de zero na matriz representa um valor onde pelo menos uma das strings excluídas foi encontrada, e zero indica que nenhuma string excluída foi encontrada.

4) SOMA(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Aqui verifica se cada número na matriz é maior que 0. Se um número for maior que 0, retorna VERDADEIRO, se não, retorna FALSO. Então você obterá uma nova matriz como {FALSO;FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO,FALSO,VERDADEIRO;VERDADEIRO}.
• 1-{FALSO;FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO,FALSO,VERDADEIRO;VERDADEIRO}: 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}.
• SOMA{1;1;0;0;0;1;1;0;0}: A função SOMA soma todos os números na matriz e retorna o resultado final como 4.

Funções relacionadas

Fórmulas relacionadas