Найти уникальные значения в диапазоне, которые встречаются минимальное количество раз

Question

Я пытаюсь сократить ручную работу с помощью функций Excel. Каждый день у меня есть список статистических данных, которые мне нужно проанализировать, и все значения так или иначе меняются. Каждая электронная таблица содержит около 10 столбцов и более 10000 строк данных. Вот что мне нужно сделать:

Найти все уникальные значения в одном столбце, которые встречаются минимальное количество раз (исключая пустые ячейки). Затем мне нужно, чтобы эти данные были отсортированы от наиболее часто встречающихся к наименее встречающимся.

Например, если предположить, что минимальная частота, необходимая для отображения в списке, равна 2:

Blue
Blue
<empty cell>
Green
Green
Green
Green
<empty cell>
Red
Red
Red
Yellow

Должен отображаться как:

Green
Red
Blue

Причина, по которой мне нужно это сделать с помощью функций, заключается в том, что я буду создавать эти списки для 5-6 столбцов, которые отображают наиболее частые значения из каждого столбца отдельно друг от друга. Использование фильтров и сортировка данных вручную занимает по часу для каждого листа, в то время как наличие набора функций позволяет анализировать все данные одновременно, и я могу тратить свое время более эффективно.

Если это имеет какое-то значение, я использую Microsoft Excel 2011 для Mac.

Answer 1

да, вы можете легко сделать это, используя Tools-> Subtotals and Tools-> PivotTables

сослаться на это - http://office.microsoft.com/en-in/excel-help/outline-group-data-in-a-worksheet-HA010095247.aspx

Answer 2

Сортированный список уникальных значений, которые встречаются заданное количество раз

1. Установите строку заголовка. Это нужно позже для правильной фильтрации

2. Введите две формулы в B2 и C2 и заполните обе, чтобы отобразить только те значения, которые встречаются два или более раз. Но все еще с пустыми ячейками и несортированными

   B2: =IF(COUNTA(A1:$A$100000,A2)=2,A2,"")
   C2: =COUNTIF(A:A,A2)
   

   • Измените =2 если вы хотите значения с более высоким вхождением

   • Если ваш столбец данных содержит более 100000 записей, вам нужно установить большее число.
     Я выбрал 100000 только для простоты формулы

3. Сортировка таблицы: выберите таблицу без строки заголовка и перейдите к
   Data → Sort → Column C → Largest to smallest

4. Скрыть пустые строки: выберите столбец B и выберите « Data → Filter → untick "empty"