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

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

Традиционно рассмотрим несколько способов решения столь распространенной проблемы – от примитивного «в лоб» до более сложного, но изящного.

Способ 1: удалить дубликаты

Решить проблему можно самым простым способом — вручную скопировать элементы обоих списков в один, а затем применить инструмент к полученному набору. Удалить дубликаты на вкладке Данные (Данные — Удалить дубликаты):

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

Метод 1а. сводная таблица

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

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

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

Метод 2: формула массива

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

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

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

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

• Формула INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) выбирает все уникальные элементы из первого списка. Как только они заканчиваются, начинает выдавать ошибку #N/A:

  

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

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

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

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Список1, СООТВЕТСТВИЕ(0, СЧЁТЕСЛИ($E$1:E1, Список1), 0)), ИНДЕКС(Список2, СООТВЕТСТВИЕ(0, СЧЁТЕСЛИ($E$1:E1, Список2), 0)) ), «») 

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

Способ 3. Power Query

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

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

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

   

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

   

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

   

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

   

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

   

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

    

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

    

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

• Как собрать несколько таблиц из разных файлов с помощью Power Query
• Извлечение уникальных элементов из списка
• Как сравнить два списка друг с другом на предмет совпадений и различий