В данном обзоре рассмотрим, как с помощью пользовательской функции получить список уникальных значений из любого диапазона данных, независимо от версии Excel.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Встроенная функция УНИК
Функция УНИК является одной из поисковых функций, предназначенных для возврата списка уникальных значений из заданного диапазона:
🔔 К сожалению, неприятным ограничением является то, что функция недоступна для пользователей Excel 2019 и более ранних версий.
Решить проблему поможет представленная ниже пользовательская функция, которая позволяет получить уникальные значения из любого диапазона данных, независимо от версии.
▶️ Альтернатива - пользовательская функция
Используя объект Scripting.Dictionary для проверки уникальности значений можно вернуть массив уникальных значений из указанного диапазона:
▶️ Описание функции
🔘 Параметры: rng (Range) — диапазон ячеек, из которого нужно извлечь уникальные значения.
🔘 Возвращаемое значение: массив уникальных значений из указанного диапазона.
🔘 Используемые объекты и методы:
- Scripting.Dictionary — объект, используемый для хранения уникальных значений.
- CreateObject("Scripting.Dictionary") — создание экземпляра объекта Dictionary.
- dict.exists(cell.Value) — проверка, существует ли значение в словаре.
- dict.Add cell.Value, Nothing — добавление нового уникального значения в словарь.
- Application.Transpose(dict.keys) — преобразование ключей словаря в массив.
➡️ Результат
Чтобы получить уникальные значения из диапазона, введите формулу:
📝 Примечание автора:
- Убедитесь, что данные не содержат ошибок или пустых значений, так как это может повлиять на результат.
- Для работы функции может потребоваться установка библиотеки Microsoft Scripting Runtime. Это можно сделать в редакторе VBA, выбрав Tools -> References и отметив Microsoft Scripting Runtime:
Если функция работает без установки библиотеки Microsoft Scripting Runtime, то подключение библиотеки не требуется.
▶️ Библиотека Microsoft Scripting Runtime
Библиотека Microsoft Scripting Runtime может быть полезной или необходимой в следующих случаях:
- Расширенные функции словаря (Dictionary): если используете дополнительные методы и свойства объекта Dictionary, которые не поддерживаются без явного подключения библиотеки, потребуется подключить ее. Однако для базовых операций, таких как добавление и проверка существования ключей, этого не требуется.
- Общая стабильность и совместимость: в некоторых средах и конфигурациях Excel может возникнуть необходимость в явном подключении библиотеки для обеспечения стабильной работы функций, использующих объекты из библиотеки Scripting Runtime.
- Сложные сценарии и большие проекты: в более сложных проектах, где используется множество функций и объектов из библиотеки, явное подключение может помочь избежать потенциальных проблем и конфликтов.
▶️ Выпадающий список уникальных значений
Полученный результат можно использовать для создания выпадающего списка, например,
- Способ 1. В качестве источника укажем весь диапазон столбца А:
- Способ 2. Учтем высоту диапазона используя функцию СМЕЩ:
где
- старт - именованная ячейка от которой начинается смещение
- Число - именованная ячейка, определяющая высоту диапазона:
➡️ Результат
Теперь у вас есть удобная функция для получения уникальных значений из любого диапазона данных в Excel!😎
🔶 Узнать о других способах создания выпадающих списков можно в наших предыдущих обзорах 🔽
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽