Удаление дубликатов с помощью UNIQUE

Функция UNIQUE возвращает список без дубликатов, оставляя только различные значения из диапазона. Результат разливается, поэтому одна формула создаёт аккуратный список, который всегда обновляется.

Это гораздо удобнее старой команды меню «Удалить дубликаты», которая создавала статическую копию, и её приходилось запускать заново при каждом изменении данных.

UNIQUE принимает один обязательный и два необязательных аргумента:

=UNIQUE(array, [by_col], [exactly_once])

• array — диапазон, в котором нужно удалить дубликаты.
• Если для by_col задано TRUE, сравниваются столбцы, а не строки (требуется редко).
• Если для exactly_once задано TRUE, возвращаются только значения, встречающиеся один раз.

В большинстве случаев достаточно указать только массив.

Предположим, A2:A12 содержит повторяющиеся названия регионов: East, West, East, North, West, East. Чтобы получить каждый регион один раз:

=UNIQUE(A2:A12)

Функция разливает три значения: East, West, North — в порядке их первого появления. Добавьте новый регион в исходные данные, и список автоматически расширится.

UNIQUE может удалять дубликаты целых строк. Если в A2:B12 указаны Region и Product, строка считается дубликатом только при совпадении обоих столбцов.

=UNIQUE(A2:B12)

Функция возвращает все уникальные сочетания Region и Product в виде небольшой разлитой таблицы из двух столбцов. Это удобно для создания подписей строк сводного отчёта.

Задайте для третьего аргумента значение TRUE, чтобы оставить только значения, встречающиеся один раз, и удалить все повторяющиеся.

=UNIQUE(A2:A12,,TRUE)

Обратите внимание на две запятые: аргумент by_col пропущен, а значение передано только для exactly_once. Если East встречается три раза, он удаляется полностью, а регион, встречающийся один раз, сохраняется.

Вложите UNIQUE в COUNTA или ROWS, чтобы подсчитать уникальные значения. Чтобы узнать, сколько существует разных регионов:

=ROWS(UNIQUE(A2:A12))

ROWS подсчитывает строки в разлитом списке. При появлении новых регионов количество обновляется автоматически. Это удобный способ узнать, сколько различных элементов содержится в данных.

UNIQUE сохраняет порядок первого появления. Чтобы представить список в алфавитном порядке, вложите функцию в SORT:

=SORT(UNIQUE(A2:A12))

Теперь уникальные регионы будут выведены в следующем порядке: East, North, West. Такое сочетание часто используют для создания аккуратных подписей в отчётах.

UNIQUE прекрасно сочетается с FILTER. Чтобы получить только уникальные товары, проданные в регионе East:

=UNIQUE(FILTER(B2:B12, A2:A12="East"))

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

Классический небольшой отчёт объединяет UNIQUE для подписей и SUMIF для итогов. Поместите уникальные регионы в столбец E с помощью =UNIQUE(A2:A12), а рядом с разлитым списком используйте:

=SUMIF(A2:A12, E2#, C2:C12)

Ссылка на разлив E2# передаёт в SUMIF каждый уникальный регион, возвращая один итог для каждого региона. Вся сводка обновляется по мере роста данных.

UNIQUE работает и в Excel 365, и в Google Таблицах, хотя есть небольшие различия. В Google Таблицах старая версия UNIQUE сравнивала целые строки и не имела аргумента exactly_once, поэтому при отличиях в результатах проверьте поведение функции.

В Таблицах также можно использовать UNIQUE внутри QUERY и функцию SORTN для более сложных случаев. Для обычных списков уникальных значений базовая формула =UNIQUE(range) работает одинаково на обеих платформах.

Список уникальных значений идеально подходит для раскрывающегося списка проверки данных. Поместите =UNIQUE(A2:A12) во вспомогательную область, а затем укажите для раскрывающегося списка диапазон разлива с помощью оператора #.

Если список начинается в H1, задайте источник проверки как =H1#. По мере появления новых регионов в данных варианты раскрывающегося списка будут автоматически расширяться без ручного обновления.

Проверьте, насколько хорошо Вы поняли UNIQUE.

Вы научились извлекать уникальные значения с помощью UNIQUE:

• Синтаксис: =UNIQUE(array, [by_col], [exactly_once]).
• Функция разливает уникальные значения в порядке их первого появления.
• Она может удалять дубликаты целых строк из нескольких столбцов.
• Задайте для exactly_once значение TRUE, чтобы оставить значения, встречающиеся только один раз.
• Объединяйте UNIQUE с SORT, FILTER, ROWS и SUMIF для создания аккуратных актуальных отчётов.

Далее Вы научитесь находить и устранять ошибку SPILL.