Функция UNIQUE в Excel — как извлечь уникальные значения

Быстрые ссылки

• The UNIQUE Function Syntax

• Using the UNIQUE Function to Extract Distinct Values

• Using the UNIQUE Function to Return Values Appearing Only Once

• Using the UNIQUE Function Alongside Other Excel Functions

• Problems You Might Encounter When Using UNIQUE in Excel

Microsoft Excel функция UNIQUE делает ровно то, что указано в её названии — она извлекает уникальные значения из массива. Проще говоря, в результате каждое значение появляется только один раз, даже если в исходных данных оно повторяется. Ниже — подробное объяснение синтаксиса, практические примеры, варианты сочетаний с другими функциями и рекомендации по устранению ошибок.

Функция UNIQUE доступна пользователям Excel в Microsoft 365, Excel для веба, мобильных и планшетных приложений Excel, а также в одноразовых версиях Excel, выпущенных в 2021 году и позже.

Microsoft 365 Personal

ОС

Windows, macOS, iPhone, iPad, Android

Бренд

Microsoft

Microsoft 365 включает доступ к Office-приложениям (Word, Excel, PowerPoint) на нескольких устройствах, 1 ТБ хранилища OneDrive и дополнительные сервисы.

$100 at Microsoft

The UNIQUE Function Syntax

Excel — мощная среда для работы с массивами, и UNIQUE имеет три аргумента — один обязательный и два необязательных:

=UNIQUE(a,b,c)

где

• a — массив (диапазон или структурированная ссылка), из которого нужно вернуть уникальные значения;
• b — логическое значение (TRUE или FALSE), определяющее, возвращать уникальные столбцы (TRUE) или строки (FALSE);
• c — логическое значение (TRUE или FALSE), определяющее, возвращать все уникальные значения (FALSE) или только значения, которые встречаются ровно один раз (TRUE).

Если аргументы b и/или c опущены, Excel предполагает FALSE по умолчанию. То есть, при вводе только аргумента a, возвращаются все уникальные строки (или уникальные значения в одномерном диапазоне).

Важно: UNIQUE — динамическая функция массива. Это значит, что она «разливается» (spills) в соседние ячейки, и результаты обновляются автоматически при изменении исходных данных.

Using the UNIQUE Function to Extract Distinct Values

По умолчанию UNIQUE удаляет дубликаты и возвращает набор уникальных значений в том порядке, в котором они впервые встречаются в исходных данных. Функцию можно применять к одномерным массивам (один столбец или одна строка) и к двумерным массивам (несколько столбцов или строк).

Чтобы удобнее работать с примерами в статье, скачайте демонстрационный рабочий файл и открывайте соответствующие вкладки для каждого примера.

Extracting a Single List of Unique Values

UNIQUE чаще всего используют для очистки одномерного списка с повторяющимися записями.

В таблице с именем T_PLWinners перечислены чемпионы Премьер-лиги за период с 2010 года.

Функция UNIQUE нечувствительна к регистру: Geek, geek и GEEK будут считаться одинаковыми строками.

Поскольку в исходных данных некоторые клубы повторяются, наша задача — сформировать список уникальных победителей. В ячейке D2 введите:

=UNIQUE(T_PLWinners[Winner])

или выберите диапазон вручную — при работе со структурированной таблицей наведите курсор на заголовок столбца и нажмите стрелку для выбора целого столбца.

Если список команд расположен в строке вместо столбца, укажите TRUE для аргумента b.

Примечания:

• Результат UNIQUE — динамический диапазон. Если вы добавите команды в исходную таблицу, список обновится автоматически.
• Чтобы превратить динамический результат в статические значения, скопируйте диапазон (Ctrl+C) и вставьте значения (Ctrl+Shift+V).

Extracting Unique Values From Two or More Columns or Rows

UNIQUE умеет работать с несколькими столбцами одновременно, возвращая уникальные сочетания значений.

Например, у вас есть таблица T_Prizes с именами и фамилиями победителей, и вы хотите получить каждую уникальную пару «Имя — Фамилия»:

Формула — та же логика, но диапазон аргумента a включает оба столбца:

=UNIQUE(T_Prizes[[First]:[Last]])

Даже если в исходных данных два Тима (Tim) с разными фамилиями, каждая пара будет считаться отдельной уникальной записью.

Если в аргумент a вы передадите всю таблицу (например, T_Prizes), структурированная ссылка не будет перечислять заголовки: она будет просто указывать имя таблицы.

Using the UNIQUE Function to Return Values Appearing Only Once

Иногда нужно не все уникальные значения, а только те, которые встречаются ровно один раз. Для этого укажите TRUE в аргументе c:

=UNIQUE(T_Transactions[Customer],,TRUE)

Например, в таблице T_Transactions у вас список транзакций, и требуется выбрать клиентов с единственной покупкой.

Если указать TRUE и для аргумента b (то есть b=TRUE, c=TRUE), вы получите столбцы, уникальные значения которых встречаются ровно один раз.

Using the UNIQUE Function Alongside Other Excel Functions

UNIQUE становится особенно полезной в комбинации с другими функциями. Ниже — распространённые комбинации, их назначение и практические примеры.

Внимание: все примеры предполагают использование аргумента a по умолчанию; при необходимости вы можете комбинировать b и c с теми же приёмами.

COUNTA и UNIQUE: подсчёт уникальных значений

Чтобы посчитать количество уникальных значений, вложите UNIQUE внутрь COUNTA:

=COUNTA(UNIQUE(T_PremChamp[Winner]))

UNIQUE формирует гипотетический список уникальных команд, а COUNTA подсчитывает занимаемые им ячейки. Благодаря динамике результата число будет меняться при добавлении новых команд.

SORT и UNIQUE: извлечение и сортировка уникальных значений

UNIQUE возвращает значения в порядке их первого появления. Если нужно отсортировать результат, вложите UNIQUE в SORT:

=SORT(UNIQUE(T_PremW[Winner]))

Таким образом вы получаете альфавитный список уникальных значений.

UNIQUE и FILTER: уникальные значения по критерию

FILTER извлекает элементы по условию. Вложив FILTER в UNIQUE, вы получите уникальные значения, соответствующие критерию:

=UNIQUE(FILTER(T_Votes[Name],T_Votes[Vote%]>0.5))

В примере из исходного файла фамилия Patricia Naylor встречалась несколько раз в отфильтрованной выборке, поэтому применение UNIQUE устраняет дубликаты.

Если требуется и сортировка, оберните весь набор в SORT.

UNIQUE, FILTER и COUNTIF: извлечь значения, которые встречаются более одного раза

Чтобы найти имена, которые появляются как минимум дважды, используйте сочетание COUNTIF, FILTER и UNIQUE:

=UNIQUE(FILTER(T_PWin[Name],COUNTIF(T_PWin[Name],T_PWin[Name])>1))

FILTER+COUNTIF отберут элементы, встречающиеся более одного раза, а UNIQUE удалит повторные в результирующем списке. Для порогов >2, >3 и т.д. просто измените число в условии COUNTIF.

Problems You Might Encounter When Using UNIQUE in Excel

Ниже — распространённые ошибки и рекомендации по их устранению.

Дополнительные ситуации и заметки:

• Чувствительность к пробелам: лишние пробелы в ячейках (особенно в начале или конце) приведут к тому, что «строки» будут отличаться. Используйте TRIM для очистки: =UNIQUE(TRIM(A2:A100)).
• Локальные разделители аргументов: в некоторых локализациях Excel аргументы разделяются точкой с запятой (;), не запятой. Если формула не принимается, попробуйте другой разделитель.
• Формулы в объединённых ячейках: динамические массивы не могут корректно «выпадать» в объединённые ячейки — снимите объединение.

Дополнительные приёмы и рекомендации (практический справочник)

Ниже — расширенная подборка практических советов, шаблонов и чек-листов для аналитиков и администраторов, которые используют UNIQUE в реальной работе.

Мини-методика: как быстро подготовить уникальный список из сырого диапазона

1. Скопируйте исходные данные в новую вкладку, чтобы не повредить оригинал.
2. Удалите пустые строки и лишние пробелы: используйте TRIM и очистку формата.
3. Примените =UNIQUE(диапазон).
4. При необходимости примените =SORT(…) или =FILTER(…) для критериев.
5. Если нужен статичный список, вставьте значения (Paste Values).

Шаблон формул (cheat sheet)

• Уникальные значения из столбца:

=UNIQUE(A2:A100)

• Уникальные записи по строкам (несколько столбцов):

=UNIQUE(A2:C100)

• Только значения, которые встречаются ровно один раз:

=UNIQUE(A2:A100,,TRUE)

• Уникальные и отсортированные значения:

=SORT(UNIQUE(A2:A100))

• Количество уникальных значений:

=COUNTA(UNIQUE(A2:A100))

• Уникальные значения по критерию:

=UNIQUE(FILTER(A2:A100,B2:B100>0.5))

• Значения, которые встречаются больше N раз (например, >1):

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>N))

Чек-листы по ролям

Аналитик:

• Проверить наличие скрытых пробелов и одинаковых, но визуально похожих значений (use TRIM и CLEAN).
• Убедиться, что диапазон не содержит ссылок на закрытые рабочие книги.
• Сохранить динамический список для дальнейших сводных расчётов.

Менеджер данных:

• Контроль версий: если Excel старых выпусков, убедиться, что пользователи знают о ограничениях функции.
• Политика форматов: стандартизировать регистр, форматы дат и валют в исходных данных.

Разработчик отчётов:

• Использовать SORT и UNIQUE внутри виджетов дашборда для минимизации ручной работы.
• При публикации в Power BI или других инструментах убедиться, что хостинг поддерживает динамические массивы.

Когда UNIQUE не подходит (ограничения и альтернативы)

• Ограничение версии Excel: если часть команды использует старые версии (Excel 2016 и ниже), UNIQUE недоступна — используйте комбинации INDEX+MATCH+FREQUENCY или Power Query для извлечения уникальных значений.
• Не подходит для сложных трансформаций: если требуется агрегация по ключам, лучше применять сводные таблицы (PivotTable) или Power Query.
• Большие наборы данных: при очень больших объёмах данных (десятки тысяч строк) Power Query или база данных будут работать быстрее и потребляют меньше оперативной памяти.

Альтернативы:

• Power Query: загрузка, удаление дубликатов и трансформации на этапе ETL.
• Сводная таблица: для подсчёта и агрегаций по уникальным ключам.
• Формулы на старых версиях Excel: сочетание INDEX, MATCH и COUNTIF/FREQUENCY.

Ментальные модели и эвристики

• Модель «фильтр → уникальность → сортировка»: сначала отбросьте ненужные строки (FILTER), затем снимите дубликаты (UNIQUE), и затем при необходимости отсортируйте (SORT).
• Эвристика «малое изменение — динамический список»: если вы ожидаете частые изменения в исходных данных, держите результат динамическим (не вставляйте значения как статичные).
• «Проверка на чистоту данных» перед UNIQUE: очистите пробелы и нормализуйте регистр, чтобы избежать ложных дублей.

Сценарии отказа и что делать

• Если появляется #SPILL!, найдите и удалите блокирующие ячейки или снимите объединение.
• Если формула не распознаётся (#NAME?), проверьте локаль Excel и версию продукта.
• Если данные ссылаются на закрытые книги (#REF?), откройте все связанные книги.

Сравнение: UNIQUE vs другие функции Excel для извлечения данных

• UNIQUE: быстрое получение уникальных значений и комбинаций с динамическим обновлением.
• Power Query: лучше для ETL и больших объёмов, даёт нечувствительность к версиям Excel на этапе подготовки данных.
• PivotTable (GETPIVOTDATA): отличный для агрегации и представления данных в сводной форме.
• Комбинации формул (INDEX/ MATCH/COUNTIF): совместимы со старыми версиями, но сложнее в поддержке.

Примеры использования в реальных задачах

1. Маркетинг: список уникальных клиентов за квартал для рассылки.
2. HR: уникальные сотрудники, получившие повышение в году.
3. Финансы: перечень контрагентов, с которыми была совершена операция только один раз.
4. Продажи: список продуктов, которые были проданы более N раз.

Практические тест-кейсы для проверки формулы UNIQUE

Тест-кейсы для проверки корректности:

• Введите список с очевидными дубликатами и убедитесь, что каждое значение появляется ровно один раз.
• Проверьте регистр: добавьте вариации в разных регистрах (Aa, aa) и убедитесь, что они считаются одинаковыми.
• Добавьте пустые строки в середину диапазона и проверьте, как они влияют на результат.
• Протестируйте поведение при добавлении новых строк в конец таблицы — результат должен обновиться автоматически.

Критерии приёмки:

• Результат содержит ровно те значения, которые уникальны (по выбранному правилу).
• Динамический диапазон корректно «выпадает» и не вызывает #SPILL!.
• Формула корректно работает при сохранении файла и при закрытии/открытии ссылочных книг (если ссылки есть).

Советы по локализации и совместимости

• Разделитель аргументов: в русской локали Excel часто используется точка с запятой (;) вместо запятой. Если формула не принимается, замените запятые на точки с запятой.
• Формат дат и чисел: перед применением UNIQUE нормализуйте форматы дат/чисел — ячейки с разными форматами могут вести к неожиданным «уникальным» значениям.

Быстрый обзор версий и доступности (факт-бокс)

• UNIQUE доступна в Excel для Microsoft 365, Excel для веба, мобильных приложениях Excel и одноразовых версиях Excel, выпущенных в 2021 году и новее.
• В старых локальных версиях (до Excel 2019) функция отсутствует — используйте Power Query или классические формулы.

Короткая инструкция для внедрения в шаблон отчёта (SOP)

1. Создайте рабочую вкладку «Сырые данные» и загрузите/вставьте исходный набор.
2. Очистите данные: TRIM, удаление лишних пробелов, проверка пустых ячеек.
3. На вкладке отчёта вставьте формулу =UNIQUE(Сырые!A2:A100).
4. При необходимости добавьте =SORT или =FILTER.
5. Зафиксируйте формат результата (например, примените условное форматирование для подсветки ошибок).
6. Документируйте шаги в описании вкладки.

Короткие заметки по безопасности и приватности

• UNIQUE работает локально в файле Excel и не отправляет данные в сторонние сервисы. При использовании Excel в облаке (Excel для веба), следите за политиками организации по хранению персональных данных.

Итог

Функция UNIQUE — универсальный инструмент для быстрого получения списка уникальных значений и уникальных комбинаций. Она особенно удобна в сочетании с FILTER, SORT и COUNTIF. Если ваша версия Excel поддерживает динамические массивы, UNIQUE значительно упростит подготовку данных и автоматизацию отчётов.

Important: перед применением UNIQUE всегда проверяйте чистоту данных (пробелы, регистр, формат) и совместимость версии Excel у пользователей, с которыми вы делите файл.

Краткое резюме:

• UNIQUE удаляет дубликаты и возвращает динамический список уникальных значений.
• Параметр b управляет направлением (строки/столбцы), параметр c — выбором значений, встречающихся ровно один раз.
• Совместное использование с SORT, FILTER и COUNTIF расширяет кейсы применения.

Конец руководства.