Fórmula do Excel: lista os números que ocorrem com mais frequência -

Índice

Fórmula genérica

(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))

Resumo

Para listar os números que ocorrem com mais frequência em uma coluna (ou seja, mais comum, segundo mais comum, terceiro mais comum, etc), você pode uma fórmula de matriz baseada em quatro funções do Excel: IF, MODE, MATCH e ISNUMBER. No exemplo mostrado, a fórmula em D5 é:

(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))

onde "dados" é o intervalo nomeado B5: B16. A fórmula é então copiada para as linhas abaixo de D5 para gerar a lista desejada dos números mais frequentes.

Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.

Explicação

O núcleo desta fórmula é a função MODE, que retorna o número que ocorre com mais frequência em um intervalo ou matriz. O resto da fórmula apenas constrói uma matriz filtrada para MODE usar em cada linha. O intervalo de expansão $ D $ 4: D4 funciona para excluir números já produzidos em $ D $ 4: D4.

Trabalhando de dentro para fora:

  1. A função MATCH é usada primeiro, verifique todos os números no intervalo nomeado "dados" em relação aos números existentes no intervalo de expansão $ D $ 4: D4
  2. ISNUMBER converte os valores correspondentes em TRUE e os valores não correspondentes em FALSE
  3. 1-NUMBER inverte a matriz, e a operação matemática gera uns e zeros
  4. IF usa a saída da matriz de # 3 acima para filtrar a lista original de valores, excluindo os números já em $ D $ 4: D4
  5. A função MODE retorna o número mais frequente na saída do array na etapa 4

Na célula D5, nenhuma filtragem ocorre e a saída de cada etapa acima fica assim:

(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93

Na célula D6, com 93 já em D5, a saída fica assim:

(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95

Tratamento de erros

A função MODE retornará o erro # N / A quando não houver modo. Conforme você copia a fórmula nas linhas subsequentes, provavelmente encontrará o erro # N / A. Para interceptar esse erro e retornar uma string vazia (""), você pode usar IFERROR assim:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")

Artigos interessantes...