Sabemos como obter o número que aparece com mais frequência no Excel. Usamos a função MODE. Mas se quisermos listar os números que aparecem com mais frequência, essa fórmula não funcionará. Precisaremos de uma combinação de funções do Excel para fazer essa tarefa.
Aqui está a fórmula genérica para listar os números que ocorrem com mais frequência:
| = MODE (IF (1-ISNUMBER (MATCH (números, intervalo de expansão, 0)), números)) |
números:Este é o intervalo ou matriz a partir do qual precisamos obter os números que aparecem com mais frequência.
gama_de_expansão:É um intervalo em expansão que começa em uma célula acima, onde você escreve esta fórmula. Por exemplo, se a fórmula do Excel estiver correta na célula B4, o intervalo de expansão será $ B $ 3: B3.
Agora, vamos ver essa fórmula em ação.
Exemplo: obtenha 3 avaliações que aparecem com frequência.
Portanto, o cenário é este. Temos alguns dados de uma pesquisa na qual os usuários avaliaram nossos serviços. Precisamos obter as 3 avaliações que aparecem com mais frequência.
Escreva esta fórmula em G3:
| = MODO (SE (1-ISNUMBER (CORRESPONDÊNCIA ($ D $ 3: $ D $ 11, $ G $ 2: G2,0)), $ D $ 3: $ D $ 11)) |
Esta é uma fórmula de matriz. Use CTRL + SHIFT + ENTER. Arraste esta fórmula em três células. E agora você tem seus 3 números de ocorrência mais frequente.
Agora que fizemos isso, vamos ver como está funcionando.
Como funciona?
Aqui, a ideia é excluir os números que aparecem com mais frequência já extraídos na próxima execução da função MODE. para fazer isso, usamos alguma ajuda extra de outras funções lógicas do Excel.
Para entender, vamos dar uma olhada na fórmula da célula G4. Qual é:
| = MODO (SE (1-ISNUMBER (CORRESPONDÊNCIA ($ D $ 3: $ D $ 11, $ G $ 2: G3,0)), $ D $ 3: $ D $ 11)) |
A fórmula começa com a função interna MATCH.
A função MATCH tem intervalo de pesquisa D3: D11 (os números). O valor de pesquisa também é um intervalo G2: G3 (expandido quando copiado para baixo). Agora, é claro, G2 contém um texto que não está no intervalo D3: D11. Em seguida, G3 contém 6. MATCH procura 6 no intervalo D3: D11 e retorna uma matriz de erro # N / A e posições de 6 no intervalo, que é {# N / A; 2; # N / A; 2; 2; # N / A; # N / A; # N / A; # N / A}.
Agora, esse array é alimentado para a função ISNUMBER. ISNUMBER retorna TRUE para números na matriz. Portanto, obtemos outro array como {FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE}.
Em seguida, este array é subtraído de 1. Como sabemos FALSE é tratado como 0 e TRUE como 1. Conforme subtraímos este array de 1, obtemos um array invertido como{1;0;1;0;0;1;1;1;1}. Esta é a imagem espelhada exata da matriz retornada pela função ISNUMBER.
Agora, para cada 1 (VERDADEIRO) MODO, a função é executada. A função MODE obtém um array{2; FALSO; 2; FALSO; FALSO; 5; 4; 4; 5}.E, finalmente, obtemos o número que aparece com mais frequência na matriz como 2. (Observe que 5 e 4 estão ocorrendo 2 vezes. Mas, como 2 apareceu primeiro, obtemos 2.
Em G3, a função ISNUMBER retorna um array de FALSE apenas. Portanto, toda a gama é fornecida para a função MODE. Em seguida, a função de modo retorna o número que aparece com mais frequência no intervalo como 6.
Outra Fórmula
Outra maneira é usar a função NOT para inverter o retorno do array pela função ISNUMBER.
| {= MODO (SE (NÃO (É NÚMERO (CORRESPONDÊNCIA ($ D $ 3: $ D $ 11, $ G $ 2: G3,0)))), $ D $ 3: $ D $ 11))} |
Então, sim pessoal, é assim que você pode recuperar os números que aparecem com mais frequência no Excel. Espero ter sido suficientemente explicativo. Se você tiver alguma dúvida sobre alguma função utilizada nesta fórmula, basta clicar sobre ela. Isso o levará à explicação dessa função. Se você tiver alguma dúvida sobre este artigo ou qualquer outra função do Excel ou VBA, pergunte-me na seção de comentários abaixo.
Como encontrar números que ocorrem com frequência | Os números que aparecem com mais frequência podem ser calculados usando a função MODE do Excel.
Obtenha o texto que aparece com mais frequência no Excel | Aprenda como obter texto que aparece com frequência no Excel.
Pesquisar texto que aparece com frequência com critérios no Excel | Para obter o texto que aparece com mais frequência no Excel com os critérios, você só precisa colocar uma condição if antes da função match.
Recuperando o primeiro valor em uma lista que é maior / menor que um valor especificado | Aprenda a pesquisar primeiro o valor maior do que um valor especificado. A fórmula genérica para pesquisar é …
Como recuperar toda a linha de um valor correspondente | É fácil pesquisar e recuperar a linha inteira de uma tabela. Vamos ver como podemos fazer essa pesquisa.
Como recuperar o preço mais recente no Excel. Para consultar o preço mais recente de uma faixa, você deve ter seus dados classificados. Depois de classificado, use esta fórmula …
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
COUNTIF no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel. Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.