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

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

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

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

Задача

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

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

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

Решение

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

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

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

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4 (2 оценок)

Комментарии

MCH

Формула массива для B6, и копируем вниз
=ИНДЕКС(A$6:A$31;ПОИСКПОЗ(МАКС(ЕСЛИ(СЧЁТЕСЛИ(B$5:B5;A$6:A$31)=0; СЧЁТЕСЛИ(A$6:A$31;">="&A$6:A$31)));ЕСЛИ(СЧЁТЕСЛИ(B$5:B5;A$6:A$31)=0; СЧЁТЕСЛИ(A$6:A$31;">="&A$6:A$31));))&""

Яндекс.Метрика