Получить условный режим эффективно в Excel

Я ищу, чтобы найти режим (наиболее частое) значение одного столбца, учитывая другой столбец.

Я знаю, как это сделать, но расчет длится несколько минут и может сделать Excel невосприимчивым. Поэтому я ищу лучший способ.


Предположим, что мои данные выглядят так

group | Level 1 D 1 A 1.1 B 1 C 1 A 1 E 

Затем я хочу, чтобы результат выглядел следующим образом:

 group | LevelMode 1 A 1.1 B 

Предполагая, что данные находятся в верхнем левом углу рабочего листа, теперь я использую эту формулу, которую я перетаскиваю по столбцу C:

 =COUNTIFS(A:A;A2;B:B;B2)/COUNTIF(A:A;A2) 

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

Эта формула делает именно то, что я хочу, но для примерно 50000 строк она просто требует слишком много ресурсов. Я ожидал найти это в опциях сводной таблицы или после быстрого поиска в Интернете, но я не нашел никакого способа достичь своей цели.

У меня есть достойное оборудование, довольно недавняя версия Excel и предпочитаю делать это без макросов. Однако, если для этого требуются макросы, то так и должно быть.

Попробуй это:

Шаг 1.

Создайте сводную таблицу и разместите ее в группе «RowLabels» и под ней «Уровень». Теперь поместите «Уровень» в качестве значения в поле «Значения».

Шаг 2.

См. Рисунок ниже. Нажмите на черную стрелку в правой части «Уровень» (вы не видите ее на картинке, но она должна находиться внутри красного круга). Выберите «Values ​​Filters», затем «Top 10», а затем установите номер 1 (вверху 1). Готово!!

Внимание: вам нужно сделать шаг 2 для «Уровень». Если вы сделаете то же самое с «Группой», это не сработает.

введите описание изображения здесь

Изменить: это результат и то, как должна выглядеть сводная таблица.

введите описание изображения здесь

Теперь, чтобы сформировать вывод в желаемый формат:

  1. В разделе «Инструменты сводной таблицы»> «Дизайн» установите макет отчета в табличную форму
  2. Отключить итоги и промежуточные итоги

Предполагая настройки по умолчанию, вы теперь можете легко скопировать список групп и LevelModes

Interesting Posts

Рабочий лист в массиве автоматически очищается?

Matlab: Хранение больших номеров

Как вставить данные в Excel, если имя столбца содержит «#» в c #

Формулы Excel, дающие #VALUE! ошибка при портировании на Mac

Как получить ячейку с наибольшим числом, работать с ней, получить следующий максимум и так далее в excel?

excel заменить значения нескольких строк

Справка Excel: применение условного форматирования к номерам в формате процента

Excel VBA добавляет и называет несколько листов в соответствии со списком имен

Переименуйте файл excel и сохраните его в относительном пути с помощью VBA

XML ERROR в стиле REASON: отсутствующий тег

C # Excel Dependent Picklist с Validation & Indirect

как показать последние 12 месяцев, когда я ввожу дату / год для Excel

IF Повторяющееся значение в столбце B & C возвращает большую цену в G

Метод Диапазон объекта Глобальный не удалось

Копировать таблицу выпадающего списка из Интернета VBA

Давайте будем гением компьютера.