Функция УНИК динамических массивов в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Функция УНИК() является частью коллекции формул динамического массива. Она возвращает неповторяющиеся значения массива. Синтаксис функции УНИК():

Рис. 27. Синтаксис функции УНИК()

Скачать заметку в формате Word или pdf, примеры в формате Excel

Аргумент по_столбцам может принимать значения: ИСТИНА = возвращаются столбцы массива, ЛОЖЬ или опущен = возвращаются строки массива (почти всегда используется именно это значение). Аргумент только_один_раз может принимать значения: ИСТИНА = возвращаются элементы которые в исходном массиве представлены единожды, ЛОЖЬ или опущен = возвращаются все элементы массива; при этом дубли исключаются (это значение используется чаще). Наличие третьего необязательного аргумента только_один_раз подводит меня к небольшой тираде.

Как понимать уникальное и различное

Рассмотрим список: Яблоко, Яблоко, Банан, Вишня, Вишня. Что бы вы сказали об уникальных элементах в этом списке? Если вы не специалист по базам данных, вы бы сказали, что правильный ответ: Яблоко, Банан, Вишня. Но некоторые функции в Excel говорят, что уникальный элемент списка – Банан. Только он не имеет дубликатов.

Рис. 28. Возвращает список элементов, которые встречаются ровно один раз

Например, если вы выберите диапазон А2:А6 и пройдете по меню Главная –> Условное форматирование –> Правила выделения ячеек –> Повторяющиеся значения –> Уникальные, Excel выдели только ячейку А4 – Банан.

Рис. 29. Условное форматирование уникальным значением считает только Банан

Специалисты по базам данных говорят, что Яблоко, Банан, Вишня – это список различных значений, а Банан – это единственный уникальный элемент.

На наш взгляд, такое определение уникальности является бесполезным. Возможно, разработчики функции УНИК() с нами согласны, поэтому используемое по умолчанию значение третьего аргумента (ЛОЖЬ или опущен) дает интуитивно ожидаемый список всех продуктов без повторений:

Рис. 30. Истинный уникальный список задается предельно простой формулой

Глава книги Майка Гирвина Ctrl + Shift + Enter о получении уникальных списков превращается в простейшую формулу:

Рис. 31. Извлечение списка уникальных продуктов с сортировкой по алфавиту

Извлечение уникальных пар

Что делать, если вам нужно извлечь уникальные пары Name/Product? Используйте массив из двух столбцов: =УНИК(B4:C29).

Рис. 32. Уникальные пары значений Name/Product

Получить уникальную комбинацию Team/ Product несколько сложнее: =УНИК(ВЫБОР({1;2};A4:A29;C4:C29)). Подробнее о работе формулы см. YouTube.

Комбинирование функций динамических массивов

Совместное использование СОРТ() и УНИК() было представлено выше (см. рис. 31), и не должно вызывать затруднений. Чуть более сложный вариант: вывод уникальных имен игроков команды Blue, отсортированных по алфавиту: =СОРТ(УНИК(ФИЛЬТР(B4:B29;A4:A29="Blue")))

Рис. 33. Использование трех функций динамических массивов