Слияние двух списков без дубликатов

Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):

Слияние списков без дубликатов

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

Способ 1. Удаление дубликатов

Можно решить задачу самым простым путем - руками скопировать элементы обоих списков в один и применить потом к полученному набору инструмент Удалить дубликаты с вкладки Данные (Data - Remove Duplicates):

Удаление дубликатов в общем списке

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

Способ 1а. Сводная таблица

Этот способ является, по сути, логическим продолжением предыдущего. Если списки не очень большого размера и заранее известно предельное количество элементов в них (например, не больше 10), то можно объединить две таблицы в одну прямыми ссылками, добавить справа столбец с единичками и построить по получившейся таблице сводную:

Объединение сводной таблицей

Как известно, сводная таблица игнорирует повторы, поэтому на выходе мы получим объединенный список без дубликатов. Вспомогательный столбец с 1 нужен только потому, что Excel умеет строить сводные по таблицам, содержащим, по крайней мере, два столбца.

При изменении исходных списков новые данные по прямым ссылкам попадут в объединенную таблицу, но сводную придется обновить уже вручную (правой кнопкой мыши - Обновить). Если не нужен пересчет "на лету", то лучше воспользоваться другими вариантами.

Способ 2. Формула массива

Можно решить проблему формулами. В этом случае пересчет и обновление результатов будет происходить автоматически и мгновенно, сразу после изменений в исходных списках. Для удобства и краткости давайте дадим нашим спискам имена Список1 и Список2, используя Диспетчер имен на вкладке Формулы (Formulas - Name Manager - Create):

Присвоение имен спискам

После именования, нужная нам формула будет выглядеть следующим образом:

Формула массива для слияния двух списков без дубликатов

На первый взгляд выглядит жутковато, но, на самом деле, все не так страшно. Давайте я разложу эту формулу на несколько строк, используя сочетание клавиш Alt+Enter и отступы пробелами, как мы делали, например тут:

Формула массива в разборе

Логика тут следующая:

  • Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает все уникальные элементы из первого списка. Как только они заканчиваются - начинает выдавать ошибку #Н/Д:

    Уникальные из списка1

  • Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным образом извлекает уникальные элементы из второго списка.
  • Вложенные друг в друга две функции ЕСЛИОШИБКА реализуют вывод сначала уникальных из списка-1, а потом из списка-2 друг за другом.

Обратите внимание, что это формула массива, т.е. после набора ее нужно ввести в ячейку не обычным Enter, а сочетанием клавиш Ctrl+Shift+Enter и затем скопировать (протянуть) вниз на нижестоящие ячейки с запасом.

В английской версии Excel эта формула выглядит как:

=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, Список2), 0))), "") 

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

Способ 3. Power Query

Если в ваших исходных списках большое количество элементов, например, по несколько сотен или тысяч, то вместо медленной формулы массива лучше использовать принципиально другой подход, а именно - инструменты надстройки Power Query. Эта надстройка по умолчанию встроена в Excel 2016. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (бесплатно).

Алгоритм действий следующий:

  1. Открываем отдельную вкладку установленной надстройки Power Query (если у вас Excel 2010-2013) или просто идем на вкладку Данные (если у вас Excel 2016).
  2. Выделяем первый список и жмем кнопку Из таблицы/диапазона (From Range/Table). На вопрос про создание из нашего списка "умной таблицы" - соглашаемся:

    Вводим список в Power Query

  3. Открывается окно редактора запросов, где будет видно загруженные данные и имя запроса Таблица1 (можно поменять на свое, если хотите).
  4. Делаем двойной щелчок в заголовок таблицы (слово Список1) и переименовываем на любой другой (например Люди). Каки именно назвать - не важно, но придуманное название нужно запомнить, т.к. его придется использовать потом еще раз при импорте второй таблицы. Объединить две таблицы в дальнейшем получится только если заголовки их столбцов совпадают.
  5. Разворачиваем выпадающий список в левом верхнем углу Закрыть и загрузить и выбираем Закрыть и загрузить в... (Close&Load to...):

    Сохраняем запрос

  6. В следующем диалоговом окне (оно может выглядеть немного по-другому - не пугайтесь) выбираем Только создать подключение (Only create connection):

    Создаем подключение

  7. Повторяем всю процедуру (пункты 2-6) для второго списка. При переименовании заголовка столбца важно использовать то же имя (Люди), что и в предыдущем запросе.
  8. В окне Excel на вкладке Данные (Data) или на вкладке Power Query выбираем Получить данные - Объединить запросы - Добавить (Get Data - Merge Queries - Append):

    Объединяем запросы

  9. В появившемся диалоговом окне выбираем наши запросы из выпадающих списков:

    Параметры слияния

  10. В итоге получим новый запрос, где два списка будут соединены друг под другом. Осталось удалить дубликаты кнопкой Удалить строки - Удалить дубликаты (Delete Rows - Delete Duplicates):

    Удаление дубликатов
  11. Готовый запрос можно переименовать справа на панели параметров, дав ему вменяемое имя (это будет имя таблицы-результата по факту) и все и можно все выгружать на лист командой Закрыть и загрузить (Close&Load):

    Результат

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

Ссылки по теме



23.06.2017 12:20:05
Можно воспользоваться простейшим способом - стандартной сводной таблицей
25.06.2017 14:26:01
Не назвал бы его "простейшим", но, да - тоже вариант. Дописал про него в статью.
26.06.2017 15:15:52
Для данной задачи можно воспользоваться расширенным фильтром с вкладки данные (там есть галочка "только уникальные записи").
27.06.2017 12:32:35
Можно, но много ручных действий будет.
04.07.2017 17:37:33
Согласен, что потребуется много ручных действий. Правда могу сказать, что попробовав все эти методы на больших списках я понял, что Advanced Filter (Расширенный фильтр) работает лучше. Правда количество строк у меня более 250 тысяч. Я создаю один список из нескольких диапазонов, выделяю, включаю расширенный фильтра, щелкая галку "Unique records only". Получившиеся уникальные значения подкрашиваю цветом и симаю расширенный фильтр шоткатом Ctrl+Shit+L  и сортирую по цвету. Неокрашенные ячейки подлежат удалению. Считаю, что на больших списках расширенный фильтр лучший (сводные не пробовал так как у меня специфика другая), но могу ошибаться.
29.06.2017 11:17:30
Ещё плюс консолидация, там можно к тому же подсчитать количество повторов.
29.06.2017 17:14:50
Вот, кстати, да - тоже вариант. Спасибо!
Здравствуйте Николай, давно у Вас на сайте, много новому научился, спасибо за Ваши труды.
Не знал в каком разделе написать свой вопрос, может направите по итогам рассмотрения?
Есть несколько таблиц - в таблицах есть ячейки, данные в которых должны быть идентичны, если нет - то цифра введенная в такие ячейки окрашивается красным цветом автоматически и будет гореть красным, пока во всех ячейках эта цифра не будет прописана.
Или же, цифра в одной ячейке таблицы должна быть равна сумме или разнице чисел в ячейке из другой таблицы - при этом пока все цифры не приведут в соответствие, все они будут окрашены красным цветом.
Все это происходит в одной книге эксель, но в разных листах (таблицы).
Вопрос - как сделать такое подкрашивание красным цветом?
Благодарю заранее за ответ (гугл не помог в этом случае никак :)).
15.07.2017 20:54:52
Эмиль, посмотрите раздел Форматирование и, в частности, урок по условному форматированию - там как раз про такие вещи.
13.07.2017 14:45:58
Спасибо, Николай.
14.07.2017 09:46:26
Николай, в п.6 "В следующем диалоговом окне (оно может выглядеть немного по-другому - не пугайтесь) выбираем Только создать подключение "
а где вы нашли такой формат окна? у вас другая версия эксель?
15.07.2017 20:53:17
Это окно немного внешне изменилось после недавнего обновления Power Query.
22.07.2017 22:52:50

Спасибо!
27.07.2017 13:41:10
Добрый день! Отличный способ "Формула массива". Но есть один недочет. Вопрос - как исправить? Если диапазон списка1 шире чем количество значений в списке, то в результате после значений из списка1 ставиться "0", а потом продолжаются значения из Списка2. Да, все уникальные отобраны, но "0" мешается. Конечно, можно убрать его и в ручную, но может можно как-нибудь сделать, чтобы его не было. Спасибо.

П.С.: естественно решение чтобы вместо "0" была просто пустая ячейка, но разделяющая уникальный список не катит. Еще раз спасибо!
Здравствуйте. Подскажите а как объединить 2 списка в которых больше чем 1 столбец? Поясню, на разных листах есть таблицы одинаковые по структуре (количество и название столбцов совпадает), но разные по наполнению, надо объединить так чтобы дубликаты были исключены (признаком дубля считается совпадение записи в столбце наименование изделия)