Функция УНИК в Excel (эксель): уникальные значения списка

=УНИК(B2:B8) возвращает каждое значение B2:B8 один раз, в порядке первого появления. Это функция УНИК (по-английски UNIQUE). Ниже North встречается трижды, а South дважды, но в результате каждый регион указан один раз. Результат разливается вниз от E2 и обновляется при изменении данных: введите West в B5, и East исчезнет из результата, потому что у Cara была единственная строка с ним. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой: =СОРТ(УНИК(B2:B8);;-1).

ФормулыСбросить

E2fx

=UNIQUE(B2:B8)

	A	B	C	D	E
1	Rep	Region	Sales		Regions
2	Ann	North	120		North
3	Ben	South	80		South
4	Ann	North	90		East
5	Cara	East	150		West
6	Ben	South	80
7	Dan	North	60
8	Eve	West	95

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =УНИК(B2:B8)

УНИК не меняет исходный список. Чтобы удалить повторяющиеся строки из самой таблицы, используйте кнопку «Удалить дубликаты» на вкладке «Данные» (на странице удалить дубликаты показаны оба способа).

Синтаксис УНИК

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

• array (массив) это диапазон или массив, из которого берутся значения.
• by_col (по_столбцу) равен ЛОЖЬ (по умолчанию), чтобы сравнивать строки, и ИСТИНА, чтобы сравнивать столбцы.
• exactly_once (только_один_раз) равен ЛОЖЬ (по умолчанию), чтобы вернуть все различные значения, и ИСТИНА, чтобы вернуть только значения, которые встречаются один раз.

УНИК требует Excel 2021, Excel 2024 или Microsoft 365. В старых версиях она показывает #ИМЯ? (по-английски #NAME?). В Google Таблицах есть UNIQUE с теми же тремя аргументами.

Для списка, который идёт по строке, а не вниз по столбцу, задайте by_col значение ИСТИНА: =УНИК(B1:H1;ИСТИНА) возвращает различные значения строки в виде строки.

Уникальные строки по нескольким столбцам

Если передать УНИК несколько столбцов, она сравнивает строки целиком: строка считается дубликатом, только когда все её столбцы совпадают с другой строкой. Здесь пары продавца и региона:

ФормулыСбросить

E2fx

=UNIQUE(A2:B8)

	A	B	C	D	E	F
1	Rep	Region	Sales		Rep	Region
2	Ann	North	120		Ann	North
3	Ben	South	80		Ben	South
4	Ann	North	90		Cara	East
5	Cara	East	150		Dan	North
6	Ben	South	80		Eve	West
7	Dan	North	60
8	Eve	West	95

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =УНИК(A2:B8)

Ann и North встречаются дважды, как и Ben и South, поэтому из 7 строк остаётся 5. Dan тоже из North, но Dan и North это другая пара, поэтому она остаётся. Со всеми тремя столбцами =УНИК(A2:C8) оставляет обе строки Ann (120 и 90 различаются) и убирает только вторую строку Ben, South, 80.

Значения, которые встречаются ровно один раз

Третий аргумент меняет вопрос с «какие значения есть» на «какие значения не повторяются»:

ФормулыСбросить

F2fx

=UNIQUE(A2:A8,,TRUE)

	A	B	C	D	E	F
1	Rep	Region	Sales		Distinct	Exactly once
2	Ann	North	120		Ann	Cara
3	Ben	South	80		Ben	Dan
4	Ann	North	90		Cara	Eve
5	Cara	East	150		Dan
6	Ben	South	80		Eve
7	Dan	North	60
8	Eve	West	95

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =УНИК(A2:A8;;ИСТИНА)

E2 перечисляет всех пятерых продавцов. F2 перечисляет только Cara, Dan и Eve, потому что Ann и Ben встречаются по два раза. Два разделителя подряд пропускают by_col, и он сохраняет значение по умолчанию.

Отсортированный список уникальных значений

УНИК сохраняет порядок первого появления. Для списка по алфавиту оберните её в СОРТ. Рядом СЧЁТЗ считает, сколько всего различных значений:

ФормулыСбросить

E2fx

=SORT(UNIQUE(B2:B8))

	A	B	C	D	E	F
1	Rep	Region	Sales		Regions A to Z	Count
2	Ann	North	120		East	4
3	Ben	South	80		North
4	Ann	North	90		South
5	Cara	East	150		West
6	Ben	South	80
7	Dan	North	60
8	Eve	West	95

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СОРТ(УНИК(B2:B8))

E2 возвращает East, North, South, West, а F2 показывает 4. Для обратного порядка используйте =СОРТ(УНИК(B2:B8);;-1). Чтобы посчитать без списка, =ЧСТРОК(УНИК(B2:B8)) даёт те же 4; на странице подсчёт уникальных значений описаны формулы для версий Excel без УНИК.

УНИК с условием

Чтобы взять различные значения только из строк, которые подходят под условие, сначала отфильтруйте их и передайте результат в УНИК. Продавцы, у которых есть хотя бы одна продажа на 90 или больше:

ФормулыСбросить

E2fx

=UNIQUE(FILTER(A2:A8,C2:C8>=90))

	A	B	C	D	E
1	Rep	Region	Sales		Reps
2	Ann	North	120		Ann
3	Ben	South	80		Cara
4	Ann	North	90		Eve
5	Cara	East	150
6	Ben	South	80
7	Dan	North	60
8	Eve	West	95

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =УНИК(ФИЛЬТР(A2:A8;C2:C8>=90))

У Ann две такие продажи (120 и 90), и она указана один раз. Результат: Ann, Cara и Eve. Замените C3 на 100, и в список попадёт Ben. Здесь работает любое условие из ФИЛЬТР, в том числе несколько условий, перемноженных друг на друга.

УНИК как источник выпадающего списка

Выпадающий список на основе УНИК никогда не показывает один пункт дважды и подхватывает новые значения без правки списка. Щёлкните F1 и откройте список: его пункты берутся из результата УНИК в столбце E. Введите Central в B6, и в списке появится пятый пункт.

ФормулыСбросить

F1fx

	A	B	C	D	E	F
1	Rep	Region	Sales		Regions	North▾NorthSouthEastWest
2	Ann	North	120		North	270
3	Ben	South	80		South
4	Ann	North	90		East
5	Cara	East	150		West
6	Ben	South	80
7	Dan	North	60
8	Eve	West	95

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

F2 суммирует продажи выбранного региона: 270 для North. В Excel настройте список через Данные > Проверка данных > Список и в поле «Источник» введите первую ячейку результата со знаком #: =$E$2#. # означает «весь разлитый результат», поэтому список растёт и сжимается вместе с УНИК. Фиксированный диапазон вроде =$E$2:$E$5 пропустит пятый регион.

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

ФормулыСбросить

E2fx

	A	B	C	D	E
1	Rep	Region	Sales		Regions
2	Ann	North	120
3	Ben	South	80
4	Ann	North	90
5	Cara	East	150
6	Ben	South	80
7	Dan	North	60
8	Eve	West	95

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

Ваша очередь: В E2 перечислите каждый регион один раз, по алфавиту от A до Z.

ПроверитьПоказать ответ

Почему УНИК оставляет значения, которые выглядят одинаково

УНИК сравнивает значения точно, без учёта только прописных и строчных букв. Если значение, которое вы ждали один раз, появилось дважды, ячейки различаются чем-то невидимым:

• Пробел в конце. "North " и "North" это разные значения. Очистите источник функцией СЖПРОБЕЛЫ или уберите пробелы прямо в формуле: =УНИК(СЖПРОБЕЛЫ(B2:B8)).
• Число, сохранённое как текст. '101 и 101 похожи, но одно из них текст, поэтому остаются оба. Преобразуйте столбец функцией ЗНАЧЕН или через Данные > Текст по столбцам.
• Регистр тут ни при чём. УНИК не учитывает регистр, поэтому apple и Apple считаются одним значением, и в результате остаётся написание, встреченное первым.

=UNIQUE({"apple";"Apple";"North ";"North"})

В Excel эта формула (здесь в английской форме, потому что разделители внутри фигурных скобок зависят от региональных настроек) возвращает три значения: apple, "North " с пробелом в конце и North.