Это перевод главы книги 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. Использование трех функций динамических массивов