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

Из исходной таблицы отберем только уникальные значения и выведем их в отдельный диапазон с сортировкой по возрастанию. Отбор и сортировку сделаем с помощью одной формулой массива. Формула работает как для текстовых (сортировка от А до Я), так и для числовых значений (сортировка от мин до макс).

Эта статья - продолжение статьи Отбор уникальных значений (убираем повторы из списка) в MS EXCEL .

В столбце А имеется список с повторяющимися значениями, например список с названиями городов.

Задача

В некоторых ячейках исходного списка имеются повторы - новый список уникальных значений не должен их содержать.

Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования .

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

Решение

Список уникальных значений создадим в столбце B с помощью формулы массива (см. файл примера ). Для этого введите следующую формулу в ячейку B6 :

=ИНДЕКС(Список_Текст;НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($B$5:B5;Список_Текст)=0;СЧЁТЕСЛИ(Список_Текст;"<"&Список_Текст)+1;"");1)=СЧЁТЕСЛИ(Список_Текст;"<"&Список_Текст)+1;СТРОКА(Список_Текст)-МИН(СТРОКА(Список_Текст))+1);1))

После ввода формулы вместо ENTER нужно нажать CTRL + SHIFT + ENTER . Затем нужно скопировать формулу вниз, например, с помощью Маркера заполнения . Чтобы все значения исходного списка были гарантировано отображены в списке уникальных значений, необходимо сделать размер списка уникальных значений равным размеру исходного списка (на тот случай, когда все значения исходного списка не повторяются). В случае наличия в исходном списке большого количества повторяющихся значений, список уникальных значений можно сделать меньшего размера, удалив лишние формулы, чтобы исключить ненужные вычисления, тормозящие пересчет листа.

Примечание : в формуле использован Динамический диапазон Список_текст . Значения ошибки скрыты с помощью Условного форматирования.

СОВЕТ: Список уникальных значений можно создать разными способами, например, с использованием Расширенного фильтра (см. статью Отбор уникальных строк с помощью Расширенного фильтра ), Сводных таблиц или через меню Данные/ Работа с данными/ Удалить дубликаты . У каждого способа есть свои преимущества и недостатки. Но, в этой статье нам требуется, чтобы при добавлении новых значений в исходный список, список уникальных значений должен автоматически обновляться, поэтому здесь построен список с использованием формул.