Encontre o valor mais utilizado

Question

Eu sei que essa pergunta já foi feita antes, mas minha pergunta precisa de uma solução mais escalonável!

Eu tenho uma coluna G com os estados, há 10.000 estados nessa coluna.

Eu preciso descobrir qual é o estado mais freqüentemente encontrado.

Normalmente a resposta é: =INDEX($G:$G,MODE(MATCH($G:$G,$G:$G,0))) mas no meu caso isso é inaceitável, isso significaria que o Excel precisa fazer 10.000 consultas, em 10.000 células (100.000.000 de operações) e esse é apenas o melhor caso (porque o Excel não é conhecido para descobrir a faixa correta usada).

Como minha coluna tem uma lista de estados, estou pensando em modificar um pouco essa fórmula   =INDEX($G:$G,MODE(MATCH(state_list,$G:$G,0))) (onde state_list é uma matriz de nomes de estados, que renderia 500.000 operações, o que representa uma melhoria de 200 vezes).

No entanto, quando tento calcular isso, recebo #N/A error.

Eu usei o F9 para verificar o MATCH(state_list,$G:$G,0) result: {197,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A}

Corrija-me se estiver errado, isso prova que estou usando uma fórmula de matriz?

Se sim, o que estou fazendo errado?

Como posso obter uma maneira real e escalável para encontrar o valor mais comum (State) na coluna G:G

Obrigado

    

Answer 1

Eu realmente não tentei isso com 10.000 linhas, mas tente

=INDEX($S$1:$S$50, MATCH(MAX(COUNTIF($G$1:$G$10000,$S$1:$S$50)), COUNTIF($G$1:$G$10000,$S$1:$S$50), 0))

em que os valores de estado exclusivos estão em S1:S50 . (Naturalmente, essa é uma fórmula de matriz; então você precisa digitar Ctrl + Deslocar + Enter quando entrar nele.)

• COUNTIF($G$1:$G$10000,$S$1:$S$50) conta quantas vezes cada um dos estados ( S1:S50 ) aparece na coluna G , produzindo uma matriz virtual de 50 números que, em média, 200 (porque eles somam 10.000). Note que esta sub-expressão aparece duas vezes, e, sim, na pior das hipóteses, requer comparações de 500.000 (50 × 10.000) - mas, em média, requer metade disso.
• MAX(COUNTIF($G$1:$G$10000,$S$1:$S$50)) informa o maior número neste array virtual. Será um número ≥ 200 que representa a frequência (contagem) do estado mais frequentemente encontrado.
• %código% localiza a localização no array virtual do valor máximo. Este será um número entre 1 e 50, identificando o estado mais freqüentemente encontrado.
• %código% dá-lhe o nome do estado mais frequentemente encontrado.

Answer 2

Sua fórmula está falhando porque você está fazendo referência a uma coluna inteira em vez de um intervalo específico. Isso, naturalmente, encontrará o valor mais comum para ficar em branco ..

Você pode corrigir isso usando essa função (extremamente volátil) -

=INDEX(INDIRECT("G1:G"&COUNTA(G:G)),MODE(MATCH(INDIRECT("G1:G"&COUNTA(G:G)),INDIRECT("G1:G"&COUNTA(G:G)),0)))

Eu não recomendo isso.

Com o número de itens que você mencionou, eu usaria uma tabela dinâmica -

Selecione sua coluna, insert - tabela dinâmica

As linhas podem ser seus estados com valores sendo suas contagem . Você pode classificar para encontrar o mais comum.

clique para ampliar