Найти наиболее часто используемое значение

Question

Я знаю, что этот вопрос уже задавался, но мой вопрос требует более масштабируемого решения!

У меня есть столбец G с государствами, в этом столбце 10 000 государств.

Мне нужно выяснить, какое наиболее часто встречающееся состояние.

Обычно ответ: =INDEX($G:$G,MODE(MATCH($G:$G,$G:$G,0))) но в моем случае это недопустимо, это означает, что Excel нужно сделать 10 000 поисков на 10000 ячеек (100 000 000 операций), и это просто лучший случай (поскольку Excel не известен для определения правильного используемого диапазона).

Поскольку в моем столбце есть список состояний, я думаю, что я мог бы слегка изменить эту формулу =INDEX($G:$G,MODE(MATCH(state_list,$G:$G,0))) (где список_состояний - это массив названия штатов, что даст 500 000 операций, что в 200 раз лучше).

Однако, когда я пытаюсь вычислить это, я получаю ошибку #N/A

Я использовал F9 для проверки результата MATCH(state_list,$G:$G,0) : {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}

Поправьте меня, если я ошибаюсь, это доказывает, что я использую формулу массива?

Если так, что я делаю не так?

Как я могу получить реально масштабируемый способ нахождения этого наиболее общего значения (State) в столбце G:G

Благодарю вас

Answer 1

Ваша формула не работает, потому что вы ссылаетесь на весь столбец, а не на определенный диапазон. Это, естественно, найдет наиболее распространенное значение пустым.

Вы можете исправить это, используя эту (чрезвычайно изменчивую) функцию -

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

Я не рекомендую это.

С количеством предметов, которые вы упоминаете, я бы использовал сводную таблицу -

Выберите столбец, вставьте - сводную таблицу

Строки могут быть вашими состояниями со значениями, являющимися их количеством. Вы можете сортировать, чтобы найти наиболее распространенные.

нажмите для полного размера

Answer 2

Я на самом деле не пробовал это с 10000 строк, но попробуйте

=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))

где уникальные значения состояния находятся в S1:S50 .  (Естественно, это формула массива; поэтому вам нужно набрать Ctrl+Shift+Enter при ее вводе.)

• COUNTIF($G$1:$G$10000,$S$1:$S$50) подсчитывает, сколько раз каждое из состояний ( S1:S50 ) появляется в столбце G , получая виртуальный массив из 50 чисел, которые в среднем равны 200 (потому что они добавляют до 10000).  Обратите внимание, что это подвыражение появляется дважды, и, да, в худшем случае требуется 500 000 (50 × 10 000) сравнений, но в среднем требуется половина этого.
• MAX(COUNTIF($G$1:$G$10000,$S$1:$S$50)) сообщает о наибольшем числе в этом виртуальном массиве.  Это будет число ≥ 200, которое представляет частоту (число) наиболее часто встречающегося состояния.
• MATCH( max_value , array_of_counts, 0) находит местоположение в виртуальном массиве максимального значения.  Это будет число от 1 до 50, обозначающее наиболее часто встречающееся состояние.
• INDEX($S$1:$S$50, state_number ) дает название наиболее часто встречающегося состояния.