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

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

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

Задача

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

Решение

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

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

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

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

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

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

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

Тестируем

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

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

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

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

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

СОВЕТ:

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

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

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

Комментарии

Muad (не проверено)

Для базы из 4000 телефонов, данный пример уже в течении 10 минут вычисляет повторяющиеся значения, причем у меня Core i7 и 8 ГБ ОЗУ

Creator

Все правильно, так и должно быть. Excel намекает, что с такие операции с большими массивами лучше производить в Access запросом. Можно пойти другим путем: 1. Выделить первые вхождения дубликатов (т.е. все кроме повторов) см. статью http://excel2.ru/articles/vydelenie-pervyh-vhozhdeniy-dublikatov 2. Напротив каждого из 4000 значений (пусть это ячейки А2:А4001) записать формулу =СЧЁТЕСЛИ($A$2:$A$4001;A2), результат - количество повторов каждого значения. Далее автофильтром можно отобрать все значения >1, скопировать отобранные значения в отдельный диапазон и удалить дубликаты с помощью инструмента Удалить дуликаты через меню Данные/ Работа с данными / Удалить дубликаты.