Как объединить два списка в один в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  

• Формула ИНДЕКС(Список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). На вопрос про создание из нашего списка «умной таблицы» — соглашаемся:

   

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):

    

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

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

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

Иногда для удобства требуется объединить два списка значений в один.

Пусть имеется 2 списка: первый список находится в столбце

А

, второй в столбце

В

.

Задача

Объединим 2 списка, содержащие, текстовые значения.

Решение

Эта задача решается простой для понимания формулой (см.

Файл примера

):

=ЕСЛИ( СТРОКА()-СТРОКА($C$4)>СЧЁТЗ(Список1); ИНДЕКС(Список2;СТРОКА(C5)-СЧЁТЗ(Список1)-СТРОКА($B$4)); A5)

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

Удаляем пропуски в списке

.

Имена

Список1 и Список2 представляют собой 2

динамических диапазона

.

Наличие

динамических диапазонов

позволяет добавлять новые значения в исходные списки. При этом, новые значения автоматически попадут в Объединенный список.

СОВЕТ:

Если списков больше, то потребуется подход, приведенный в статье

Объединение 3-х и более списков

.

На вашем листе два списка, и некоторые значения в списках дублируются, и вы хотите объединить эти два списка и просто оставить уникальные значения, как показано на скриншотах ниже, как вы можете это сделать?

Объединяйте списки без дубликатов с помощью функции удаления дубликатов в Excel

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

Объединить списки без дубликатов с Kutools for Excel

Объединяйте списки без дубликатов с помощью функции удаления дубликатов в Excel

Чтобы объединить два списка и удалить повторяющиеся значения в Excel, вы можете сделать следующее:

1. Скопируйте один из двух списков и вставьте его в конец другого списка, см. Снимок экрана:

2. Выберите список и щелкните Данные > Удалить дубликаты, см. снимок экрана:

3. в Удалить дубликаты диалоговое окно, если у вашего столбца нет заголовка, снимите флажок У моих данных есть заголовки, А затем нажмите OK. Смотрите скриншот:

4. Затем на экране отобразится диалоговое окно, в котором сообщается, что дубликаты были удалены, щелкните OK. Смотрите скриншот:

Вы можете увидеть результат:

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

В Excel VBA также может помочь вам объединить два списка без дубликатов.

1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули, и скопируйте VBA в модуль.

VBA: объединять списки без дубликатов

Sub FindUniques()
'Updateby20140313
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set dic = CreateObject("Scripting.Dictionary")
For j = 1 To InputRng.Columns.Count
    For i = 1 To InputRng.Rows.Count
        xValue = InputRng.Cells(i, j).Value
        If xValue <> "" And Not dic.Exists(xValue) Then
            OutRng.Value = xValue
            dic(xValue) = ""
            Set OutRng = OutRng.Offset(1, 0)
        End If
    Next
Next
End Sub

3. Нажмите Run или нажмите F5 для запуска VBA.

4. На экране появится диалоговое окно, в котором вы можете выбрать диапазон, который хотите объединить. Смотрите скриншот:

5. Нажмите OK, затем появилось другое диалоговое окно, в котором вы можете выбрать ячейку для вывода комбинированного результата. Смотрите скриншот:

6. Нажмите OK. Вы можете видеть, что списки были объединены.

Совет: результат не может сохранить форматирование ячеек после выполнения вышеуказанного VBA.

Объединить списки без дубликатов с Kutools for Excel

С установленным Kutools for Excel, вы можете использовать Выберите дубликаты и уникальные ячейки функция для решения проблемы, объединяющая два списка без дубликатов.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас

1. Скопируйте один из двух списков и вставьте его в конец другого списка, выберите новый список, затем щелкните Кутулс > Выберите Инструменты > Выберите дубликаты и уникальные ячейки. Смотрите скриншот:

2. в Выберите дубликаты и уникальные ячейки диалог, проверьте Все уникальные посетители (включая 1-е дубликаты), нажмите Ok.

3. Затем появится диалоговое окно, в котором указано, сколько уникальных значений было выбрано, щелкните OK и Отмена закрыть два диалога. Смотрите скриншот:

4. Скопируйте выбранные уникальные значения и вставьте их в новый столбец. Смотрите скриншот:

Щелкните здесь, чтобы узнать больше о выборе дубликатов и уникальных ячеек.

Относительные статьи:

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

Типичная проблема — имеем два списка. Первый список — содержит объем продаж по менеджерам с фамилиями и суммами, другой содержит фамилии менеджеров и отделы к которым они относятся. Как в первый список быстро добавить название отдела, к которому относится менеджер, чтобы посмотреть продажи по отделу?

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример		6 Кб	2544

Графически эта задача выглядит, примерно, так: