Отбор повторяющихся значений в EXCEL

history

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


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

Задача

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

Решение



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

Введем в ячейку B5 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0) );"")

Вместо ENTER нужно нажать CTRL + SHIFT + ENTER .

ИсхСписок - это Динамический диапазон (ссылка на исходный список в столбце А ).

Скопируйте формулу вниз с помощью Маркера заполнения (размерность списка значений имеющих повторы должна совпадать с размерностью исходного списка).

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

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

Тестируем

1. Добавьте в исходный список название новой компании (в ячейку А20 введите ООО Кристалл)

2. Список неповторяющихся значений (столбец С) автоматически будет обновлен, чтобы включить новое название

3. Добавьте в исходный список название новой компании еще раз (в ячейку А21 снова введите ООО Кристалл)

4. Список неповторяющихся значений автоматически будет обновлен, новое название будет исключено

5. Список повторяющихся значений (столбец B) автоматически будет обновлен, чтобы включить новое название.

СОВЕТ:

Созданный список повторяющихся значений является динамическим, т.е. при добавлении новых значений в исходный список, новый список будет автоматически обновляться. Это очень ресурсоемкая задача и годится для небольших списков 50-100 значений. Если динамический список не нужен, то можно пойти другим путем: см. статью Отбор повторяющихся значений с помощью фильтра .


Комментарии

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

Аноним, 9 ноября 2018 г.
Я согласен и на час работы скрипта, но только вот не хочет данный пример обрабатывать более 24 строк. Вроде с экселем дружу, но тут что-то не вижу, как расширить диапазон выборки. Можете помочь?
Михаил, 14 ноября 2018 г.
В файле примера использован именной диапазон: Формулы/ Диспетчер имен. Не забыли его обновить?
Irakli, 11 марта 2021 г.
А как можно модифицировать формулу, чтобы она работала с первой строки? Я так понимаю, на данный момент это невозможно из-за этой части формулы: СЧЁТЕСЛИ(B4:$B$4....
Михаил, 12 марта 2021 г.
удалите первые 3 строки )))
Irakli, 13 марта 2021 г.
Прикольно, конечно. Но останется одна строка. Она-то и не нужна. Впрочем, я уже нашел решение. =ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$15;АГРЕГАТ(15;6;(СТРОКА($A$1:$A$15))/(ЧАСТОТА(ПОИСКПОЗ($A$1:$A$15;$A$1:$A$15;0);СТРОКА($A$1:$A$15)-1)>1);ЧСТРОК($C$1:C1)));"")
Irakli, 13 марта 2021 г.
СТРОКА($A$1:$A$15)-1 минус 1 лишний.
(только для авторизованных пользователей)

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