Contar o número de células que não contêm vários valores

Normalmente, é fácil contar células que não contêm um valor específico com a função CONTAR.SE. Este artigo explica, passo a passo, como contar o número de células que não contêm vários valores num intervalo limitado no Excel.

Como contar células que não contêm determinados valores?

Conforme ilustrado na imagem seguinte, para contar as células em B3:B11 que não contêm os valores listados em D3:D4, siga estes passos.

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 introduzida como uma fórmula de matriz. Após a sua aplicação, se aparecerem chavetas a envolver a fórmula, significa que a fórmula de matriz foi criada com sucesso.

Como utilizar esta fórmula?

1. Selecione uma célula vazia para exibir o resultado.

2. Introduza a seguinte fórmula na célula e, em seguida, prima simultaneamente as teclas Ctrl+Shift+Enter para obter o resultado.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>,0))

Como funcionam estas fórmulas?

=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 TRANSPOR devolve a orientação de D3:D4 e retorna {«count»,«blank»};
• SEARCH({“count”,”blank”},B3:B11): A função PESQUISAR localiza aqui a posição das substrings «count» e «blank» no intervalo B3:B11 e devolve um conjunto 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, uma vez que existem dois valores que pretende excluir ao contar células, obtendo assim 18 valores no conjunto. Cada número no conjunto indica a posição do primeiro carácter de «count» ou «blank» 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 É.NÚMERO devolve VERDADEIRO se encontrar números no conjunto e devolve FALSO se encontrar erros. Aqui devolve o resultado como {FALSO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;
  VERDADEIRO;VERDADEIRO}.
• --({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 obterá um novo conjunto 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 LIN devolve o conjunto de números de linha da referência de célula: {3;4}. De seguida, o operador de exponenciação (^) eleva os números 3 e 4 à potência 0, devolvendo o resultado final 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 devolve o produto matricial destes dois conjuntos: {0;0;1;1;1;0;0;1;2}, correspondendo aos dados originais. Qualquer valor diferente de zero neste conjunto indica que pelo menos um dos textos excluídos foi encontrado, enquanto o zero significa que nenhum dos textos excluídos foi detetado.

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

• {0;0;1;1;1;0;0;1;2}>0: Aqui verifica-se se cada número no conjunto é maior do que 0. Se for, devolve VERDADEIRO; caso contrário, devolve FALSO — obtendo assim um novo conjunto como {FALSO;FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;VERDADEIRO}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Como só queremos contar as células que não contêm os valores especificados, temos de inverter estes valores no conjunto, subtraindo-os a 1. Neste caso, o operador matemático converte automaticamente os valores VERDADEIRO e FALSO em 1 e 0, devolvendo, assim, 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 SOMA adiciona todos os números no conjunto e devolve o resultado final: 4.

Funções relacionadas

Fórmulas relacionadas