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

history

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


Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний.

Задача

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

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

Решение



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

Для создания Динамического диапазона :

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите: Исходный_список ;
  • в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
  • нажмите ОК.

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

=ЕСЛИОШИБКА(ИНДЕКС(Исходный_список; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B$4:B4;Исходный_список);0));"")

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

Разберем работу формулу подробнее:

  • Здесь использование функции СЧЁТЕСЛИ() не совсем обычно: в качестве критерия (второй аргумент) указано не одно значение, а целый массив Исходный_список , поэтому функция возвращает не одно значение, а целый массив нулей и единиц. Возвращается 0, если значение из исходного списка не найдено в диапазоне B4:B4 ( B4:B5 и т.д.), и 1 если найдено. Например, в ячейке B5 формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается массив {1:0:0:0:0:0:0:1:0:0:0:0:1:1:0}. Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» ( B5 ). Массив легко увидеть с помощью клавиши F9 (выделите в Строке формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список) , нажмите F9 : вместо формулы отобразится ее результат);
  • ПОИСКПОЗ() – возвращает позицию первого нуля в массиве из предыдущего шага. Первый нуль соответствует значению еще не найденному в исходном списке (т.е. значению "ОАО Уважаемая компания" для формулы в ячейке B5 );
  • ИНДЕКС() – восстанавливает значение по его позиции в диапазоне Исходный_список ;
  • ЕСЛИОШИБКА() подавляет ошибку, возникающую, когда функция ПОИСКПОЗ() пытается в массиве нулей и единиц, возвращенном СЧЁТЕСЛИ() , найти 0, которого нет (ситуация возникает в ячейке B12 , когда все уникальные значения уже извлечены из исходного списка).

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

Примечание . Функция ЕСЛИОШИБКА() будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() . В файле примера имеется лист Для 2003 , где эта функция не используется.

Решение для списков с пустыми ячейками

Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ): =ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19; ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);"";СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0) );"")

Решение без формул массива

Для отбора уникальных значений можно обойтись без использования формул массива . Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист "Без CSE" в файле примера ).

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

СОВЕТ2 : Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список , необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст "" , который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами ). Например, см. статью Динамический выпадающий список в MS EXCE L.

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


Комментарии

Только для авторизованных пользователей

Аноним, 11 июня 2017 г.
Доброго времени суток! Взял скопировал формулу, подправил. Получилось: =ЕСЛИОШИБКА(ИНДЕКС(qwe;ПОИСКПОЗ(0;СЧЁТЕСЛИ(B1:$B$1;qwe)+ЕСЛИ(СЧЁТЕСЛИ(qwe;qwe)>1;0;1);0));"") А список не формируется! Что не так исправил?! Диапазон в колонке A назвал "qwe". Заменил имя "Исходный_список" в формуле. Таблица с заголовком начинается с первой строки, потому поправил на "B1:$B$1". В чём хитрость?! Спасибо!
Михаил, 12 июня 2017 г.
Формулу нужно вводить как формулу массива, т.е. нажать CTRL+SHIFT+ENTER. Подробнее здесь http://excel2.ru/articles/formuly-massiva-v-ms-excel-znakomstvo
Аноним, 2 апреля 2020 г.
Приветствую! Как работает формула массива отбора уникальных значений без пустых ячеек понятно. Нельзя ли разъяснить, как работает формула когда есть пустые ячейки. А то моей гениальности не хватает.))
Михаил, 2 апреля 2020 г.
Формула очень похожа. ЕПУСТО проверяет является ли ячейка пустой. Если да, то она "заполняется" значением пустой текст "". Т.е. теперь в ПОИСКПОЗ будет поступать не массив нулей и единиц, а массив нулей, единиц и "". Поэтому ПОИСКПОЗ никогда не выберет пустую ячейку (ей никогда не будет соответствовать 0). и следовательно никогда не будет выведена.
Аноним, 2 апреля 2020 г.
[id295933596|Михаил], Спасибо Михаил! Уразумел. Поэкспериментировал с функцией вида ПОИСКПОЗ(0; {1:1:"":0:0:0}; 0) и все стало на свои места. Вернулась 4, и пустота была проигнорирована. Мнение: Функция ЕПУСТО(…), на ячейку содержащую пустоту вида "", выдает ЛОЖЬ. Я применяю ДЛСТР(…)=0.
Михаил, 3 апреля 2020 г.
[id115101963|Nemo], можно и так)
Аноним, 13 мая 2020 г.
Добрый день. Спасибо большое за вашу работу. Скажите, пожалуйста, в решении без формул массива можно ли сделать так, что бы в список уникальных значений попадали только уникальные значения ООО? Т.е. добавляется доп условие, что бы среди уникальных значений также выбирались только ООО, а не как сейчас все и ЗАО и ОАО
Михаил, 13 мая 2020 г.
Да, это возможно. Про это написано в статье https://excel2.ru/articles/otbor-unikalnyh-znacheniy-na-osnovanii-znacheniy-iz-sosednego-stolbca-v-ms-excel
Аноним, 2 июня 2020 г.
Спасибо ♥
Андрей, 12 января 2021 г.
Михаил, доброго времени суток. Подскажите, как мне доставить еще условие с дополнение к ЕПУСТО, помимо пустых строк еще хочу исключить строки с некоторыми значениями. Спасибо
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved