Находить уникальные значения в Excel — легко, если нужные столбцы идут друг за другом. Но как быть, если выбирать надо из разбросанных? С помощью новых функций вы буквально за пару минут соберёте идеальную выборку и забудете о лишних колонках.
Основы работы UNIQUE: быстро и просто
Функция UNIQUE появилась в Excel 2021, Microsoft 365, веб-версии Excel и мобильных приложениях. Вот как она работает:
UNIQUE относится к динамическим массивам — это значит, что достаточно ввести формулу лишь в одну ячейку, а все результаты сразу разливаются в соседние. Вокруг появится голубая рамка — так обозначается область массива. Если что-то мешает — появится ошибка #SPILL!.
6 функций, которые полностью изменили работу в Microsoft Excel
Динамические массивы — настоящая революция в Excel.
Как выбрать соседние столбцы
Допустим, я веду таблицу расходов — T_Expenses — и хочу получить список уникальных пар «Категория»+«Магазин».
Здесь всё просто: если колонки стоят рядом, их легко указать через двоеточие в аргументе array:
Почему не получается выбрать далеко стоящие столбцы одной формулой
А теперь задача: нужны уникальные пары «Категория» и «Способ оплаты» — а столбцы-то разбросаны.
Если попытаться указать эти колонки через запятую — получите ошибку #VALUE!. Excel считает второй столбец дополнительным параметром by_col, а ждет TRUE или FALSE — отсюда и срабатывает ошибка.
Есть два крутых способа обойти это ограничение.
Быстрое решение: CHOOSECOLS внутри UNIQUE
Чтобы получить только нужные столбцы, внутри UNIQUE используем CHOOSECOLS — она выбирает из диапазона ровно те колонки, которые нужны, по их номерам.
В T_Expenses «Категория» — второй столбец, «Способ оплаты» — четвёртый. Вот как выглядит формула с CHOOSECOLS:
CHOOSECOLS собирает из всей таблицы только 2-й и 4-й столбцы и передаёт этот мини-диапазон в UNIQUE, который возвращает уникальные пары.
Абсолютная гибкость: динамические заголовки через MATCH
Но есть тонкий момент: CHOOSECOLS выбирает колонки строго по номерам. Если кто-то вставит в таблицу новый столбец, номера съедут и формула начнёт работать неправильно.
Чтобы сделать подбор устойчивым к таким изменениям, подставляем вместо «жёстких» номеров функцию MATCH, которая находит порядковый номер по названию столбца:
Фишка: используйте Alt+Enter, чтобы разбивать длинные формулы на части прямо в строке формул — очень удобно для редактирования.
Теперь вместо 2 и 4 в формулу подставляются результаты MATCH: первая ищет «Категория» (название из G1), вторая — «Способ оплаты» из H1, определяя, какие это по счёту столбцы в вашей таблице.
Теперь формула достает нужные колонки даже после изменений структуры таблицы!
Почему не стоит жестко прописывать значения в Excel — используйте только ссылки и имена!
Ссылки и именованные диапазоны — всегда надёжнее и удобнее для работы.
Лайфхак: делаем выпадающие списки заголовков через INDIRECT
Ещё круче, если сделать в ячейках G1 и H1 выпадающие списки — никакая опечатка не испортит работу формулы.
Подпишитесь на рассылку и ловите новые Excel-хаки одним из первых!
Правда, стандартная проверка данных не даёт выбрать структурированные ссылки вроде T_Expenses[#Headers]. Но тут на помощь приходит функция INDIRECT — она превращает текст в настоящую ссылку, которую можно использовать в Data Validation.
Выделяю ячейки (например, G1 и H1), затем на вкладке «Данные» выбираю «Проверка данных».
В настройках выбираю значение «Список» и в поле «Источник» ввожу формулу с INDIRECT:
После ОК появляются стрелки — в ячейках раскрывается выпадающий список со всеми заголовками вашей таблицы.
Стоит выбрать другой столбец — MATCH моментально определяет его номер, CHOOSECOLS тут же формирует правильный диапазон, а UNIQUE мгновенно обновляет результаты! И что удобно — с помощью INDIRECT все появляющиеся новые столбцы тут же автоматически появляются и в выпадающем списке.
Как оформить выпадающий список из столбца в Excel
Выбор подходящего способа зависит от структуры ваших данных.
UNIQUE с динамическими массивами — намного гибче и надёжнее, чем привычное удаление дубликатов: исходные данные не исчезают, и вы всегда можете пересчитать уникальные значения по-своему.
Microsoft 365 Personal
Microsoft 365 даёт полный доступ к Word, Excel, PowerPoint на пяти устройствах, 1 ТБ в облаке OneDrive и многое другое.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru