Извлекаем список уникальных значений из необработанных данных - Различные инструменты Excel - Excel - Каталог статей

Главная » Статьи » Excel » Различные инструменты Excel

Извлекаем список уникальных значений из необработанных данных

Я недавно много писал про то, как организовать каскадные взаимозависимые списки. Спору нет - списки полезная и нужная штука. Однако, списки строятся на основе неких справочников. Хорошо, когда эти справочники статичны. Вы единожды занесёте в них информацию, а далее будете только пользоваться. Но что делать, когда к вам поступает на входе таблица, на основе которой вам надо динамически извлечь информацию для построения справочника, который затем уже вы используете в выпадающем списке? Давайте смодулируем простую, но предельно жизненную ситуацию, когда на отдельном листе Excel у вас есть некая таблица, которая совершенно не статична, а обновляется с течением времени. К примеру, таблица пополняется информацией о продажах за истекшую неделю. Предположим, там есть столбец с названием продукта, на основе которого на соседнем листе вы хотите иметь выпадающий список. Вы ведь захотите, чтобы выпадающий список автоматически подхватывал новые продукты, появляющиеся на листе с продажами? Захотите. Факт. Давайте разбираться, как это можно реализовать.

Описание задачи

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

Два пути

В начале я стал решать эту задачу при помощи формул рабочего листа. Решил и очень обрадовался. Однако, суровая реальность не оставила на этом решении камня на камне. Фундаментальный недостаток - огромное количество формул, которые губят всё, когда у вас громоздкие таблицы (а в реальной жизни они именно такие). Да, я понимаю, что там есть возможности для оптимизации, что формулы можно написать иначе и считаться они будут, например, в 2 раза быстрее. Но это не спасёт, когда в вашей таблице будет 50 000 строк, а формулы будут пересчитываться 5 минут. Мне абсолютно не легче, если вместо 5 минут, это будет обсчитано за 2. Меня устроит только 1 секунда и меньше. Есть и другие мелкие неприятности, о которых нет смысла упоминать, так как решение идёт в корзину, даже если всё остальное было бы идеально. У формул одно преимущество - не нужны макросы. Можно рекомендовать этот путь только для небольшого количества данных - до 3000 строк это всё считается довольно шустро и незаметно. Чисто из академического интереса я выкладываю формульное решение тоже.

Решение, которое я смело могу вам рекомендовать, основано на сводных таблицах и буквально одной строчке VBA кода.

Файлы примеров

Решение на основе сводной таблицы

Формульное решение

Решение на основе сводной таблицы

На листе Data расположены наши исходные данные, оформленные в виде умной таблицы tblData, что очень важно
На листе PT располагается наша сводная таблица ptCars. У неё отключены вывод Общих итогов и включена сортировка по алфавиту. Сводную таблицу важно строить на основе tblData
Именованный диапазон CARS_Sorted ссылается на сводную таблицу! Я уже описывал способ формирования ИД при помощи формулы ИНДЕКС.
ИД CARS_Sorted используется в качестве источника строк для нашего выпадающего списка на листе Result
То, ради чего всё затевалось.
А это элементарный VBA код, который мы подвесили на событие Activate рабочего листа Result. Таким образом, каждый раз, когда вы заходите на лист Result, данный макрос рефрешит сводную таблицу, в ней появляются все новые значения, которые тут же фотографирует ИД CARS_Sorted и к моменту, когда вы увидите выпадающий список, он уже будет содержать полный список значений моделей автомобилей.