Нередко для формирования отчета требуется получить список уникальных значений из исходной таблицы и вот пять способов сделать это.
Удаление дубликатов
Если нужно получить, так скажем, статичный список уникальных значений (то есть когда у вас есть данные, которые не будут обновляться, но на базе которых нужно создать отдельный отчет), то проще всего воспользоваться стандартным инструментом удаления дубликатов.
Делаем копию всего интересующего нас диапазона значений и через вкладку Данные удаляем из него дубликаты.
Теперь на базе полученных значений можно формировать отчет.
Расширенный фильтр
Еще одной возможностью получить список неповторяющихся значений будет использование расширенного фильтра.
Через вкладку Данные (1) обращаемся к группе Сортировка и фильтр. Открываем расширенный фильтр (2) и указываем диапазон значений, из которого необходимо выделить уникальные значения (3). Устанавливаем соответствующую галочку (4) и в итоге получаем список уникальных значений, который можно скопировать и перенести в свой отчет.
В таком виде работает именно фильтрация, то есть остальные данные просто скрываются и мы всегда можем снять фильтр, чтобы их вновь отобразить.
Сводная таблица
Также для решения этой задачи подходят и сводные таблицы. На базе исходной таблицы создаем новую сводную и закидываем в область строк интересующее нас поле.
В итоге также получим список неповторяющихся значений, который можно скопировать в будущий отчет.
Функция УНИК
Самым простым вариантом получения списка уникальных значений будет применение функции УНИК, которая относится к функциям динамических массивов. Нужно лишь указать диапазон, из которого необходимо получить уникальные значения и на выходе мы его получим.
Итоговый диапазон значений будет динамическим, то есть при добавлении новых данных в исходной таблице он будет автоматически обновляться.
У функции УНИК есть только один существенный минус - она доступна лишь пользователям Excel 365 и коробочкой версии 2021. В более старых версиях либо придется обходиться ранее рассмотренными способами, либо попробовать создать свою формулу, которая также решит эту задачу.
Формула массива
Так как мы должны обработать массив значений, в котором многократно проверим элементы массива на уникальность, то без формул массива, к сожалению, обойтись не получится.
Ранее в своих публикациях я уже использовал формулы массива с подробным изложением сути их работы и, как показывает этот опыт, подавляющему большинству пользователей нужен рабочий инструмент, а не понимание того, как он работает. Поэтому не буду углубляться в дебри вычислений и предлагаю вашему вниманию готовую формулу в формате «как есть». Вы можете ее скопировать и использовать в своих таблицах. Я лишь прокомментирую основные аргументы, которые нужно будет скорректировать под ваши задачи.
Итак, эта формула является аналогом функции ВПР, то есть применена связка функций ИНДЕКС и ПОИСКОПЗ. В качестве массива функции ИНДЕКС выступает диапазон, из которого вы хотите получить уникальные значения (в рассматриваемом примере A2:A25). Также этот массив необходимо указать в качестве критерия функции СЧЁТЕСЛИ. Ну а в качестве диапазона поиска этой функции указываем «плавающий диапазон» столбца, в который нужно уникальные значения вывести($H$1:$H1). Обратите внимание на то, как этот диапазон указан - первая ссылка в нем полностью зафиксирована, а во второй зафиксирован лишь столбец. При этом формулу мы вводим во второй строке, а первая является заголовком данного столбца и именно она фигурирует в формуле.
После ввода формулы нажимаем сочетание клавиш Ctrl + Shift + Enter, чтобы сделать формулу формулой массива и затем "протягиваем" ее вниз до тех пор, пока не появятся пустые ячейки.
Макрос на VBA
И в качестве бонусного варианта хочу предложить простой макрос. Код макроса приложу в отдельном текстовом файле. Вам достаточно будет его открыть, скопировать код и затем вставить его в свой документ. Для этого в Excel нажимаем сочетание клавиш Alt+F11, чтобы запустить редактор кода.
Я вставлю макрос только в текущий документ, поэтому выбираю его в окне проектов и создаю новый модуль, через контекстное меню.
В модуль вставляю ранее скопированный код.
Теперь переключаюсь в Эксель, укажу диапазон, значения которого нужно очистить от дубликатов и нажимаю Alt+F8, чтобы открыть окно макросов. Выбираю макрос и запускаю его.
Будет предложено выбрать ячейку, начиная с которой будет вставлен список уникальных значений.
В итоге получаю список, который никак не связан с исходным диапазоном, то есть это своеобразный аналог стандартного инструмента очистки дубликатов, но без удаления исходных данных.
Макросам и их применению в Эксель я посвятил отдельный подробный видеокурс. Если тема вам интересна, то буду рад, если обратите на него свое внимание.
В курсе на постепенно усложняющихся примерах разбираются различные конструкции языка программирования VBA и инструментарий стандартного редактора кода.
В конце курса вас ждет курсовая работа, подводящая итоги по всему пройденному материалу. Ознакомиться с подробным содержанием курса можно, перейдя по этой ссылке
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
