Объединить 2 списка в Excel

Обновлено: 05.07.2024

Если вы получаете информацию на нескольких листах или книгах, которую хотите обобщить, команда «Объединить» может помочь вам объединить данные на одном листе. Например, если у вас есть лист данных о расходах каждого из ваших региональных офисов, вы можете использовать консолидацию, чтобы свести эти цифры в корпоративный отчет о расходах. Этот лист может содержать общие и средние данные о продажах, текущие уровни запасов и самые продаваемые продукты для всего предприятия.

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

Объединить по позиции

Чтобы консолидация по положению работала, диапазон данных на каждом исходном листе должен быть в формате списка без пустых строк или пустых столбцов в списке.

Откройте каждый исходный лист и убедитесь, что ваши данные находятся в одном и том же месте на каждом листе.

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

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

На вкладке "Данные" в группе "Инструменты данных" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

На каждом исходном листе выберите свои данные.

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Объединить по категориям

Чтобы консолидация по категориям работала, диапазон данных на каждом исходном листе должен быть в формате списка без пустых строк или пустых столбцов в списке. Также категории должны быть последовательно помечены. Например, если один столбец помечен как Avg. а другой помечен как «Среднее», команда «Объединить» не будет суммировать два столбца вместе.

Откройте каждый исходный лист.

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

На вкладке "Данные" в группе "Инструменты данных" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

Чтобы указать, где находятся метки в исходных диапазонах, установите флажки в разделе "Использовать метки в: верхней строке, левом столбце или в обоих".

На каждом исходном листе выберите свои данные. Не забудьте включить ранее выбранную информацию из верхней строки или левого столбца.

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

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

Объединить по позиции

Откройте каждый исходный лист и убедитесь, что ваши данные находятся в одном и том же месте на каждом листе.

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

На вкладке "Данные" в разделе "Инструменты" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

В каждом исходном листе выберите свои данные и нажмите "Добавить".

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Объединить по категориям

Откройте каждый исходный лист.

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

На вкладке "Данные" в разделе "Инструменты" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

На каждом исходном листе выберите свои данные. Убедитесь, что вы включили ранее выбранную информацию из верхней строки или левого столбца, а затем нажмите "Добавить".

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

Проблема: у меня есть список продаж по клиентам за текущий месяц. Мой коллега только что прислал мне список продаж за вчера. Мне нужно объединить и объединить эти списки.

Это трехэтапный процесс:

Выполните ПОИСКПОЗ или ВПР(,,1,) во втором списке, чтобы найти новых клиентов. Добавьте новых клиентов в исходный список с предыдущими продажами 0.
Теперь, когда первый список содержит надмножество клиентов из любого списка, выполните ВПР в первом списке, чтобы получить данные о продажах из второго списка.
Добавить предыдущие продажи к новым продажам и преобразовать в ценности. Теперь вы можете удалить новый список.

Тем не менее, теперь, когда вы прочитали о ПОИСКПОЗ, вы можете так же легко использовать ПОИСКПОЗ, чтобы найти, какие клиенты находятся в другом списке. Используйте =ПОИСКПОЗ(D6,$A$6:$A$25,0). На рисунке ниже совпадение есть у всех клиентов в столбце D, кроме Ford.

В текущем примере новым является только Ford, поэтому вы можете скопировать Ford из D9 и вставить в A26. Введите ноль в ячейку B26. Однако в реальной жизни у вас будет несколько новых клиентов. Скопируйте и вставьте под первым клиентом в A. Добавьте ноль во все соответствующие ячейки в B.

Теперь вы выполнили первый из трех шагов. Следующим шагом будет добавление настоящей ВПР в первый список.

Вставьте два новых столбца перед столбцом D. Назовите эти временные столбцы Addl Rev и New Total.

Формула в новом C6: =ЕСЛИОШИБКА(ВПР(A6,$F$6:$G$16,2,ЛОЖЬ),0). Скопируйте эту формулу в строку 26.

Если вы хотите проверить свою работу, используйте автосумму, чтобы добавить итог внизу столбца C и внизу столбца G. Обе суммы должны совпадать.

На этом второй из трех шагов завершен. Последним шагом является объединение доходов из столбца B и столбца C.

В D6 введите формулу =B6+C6. Скопируйте эту формулу вниз.

Скопируйте D6:D26. Выберите B6 и вставьте значения.

Подсказка: не беспокойтесь о том, что столбец D показывает более высокое значение, чем должно. Столбец D все еще добавляет доход за текущий день к новой сумме в B. Вы очень скоро удалите столбец D. Если вы не хотите, чтобы столбец D когда-либо показывал неправильное значение, вы можете сначала скопировать D и вставить значения поверх самого себя.

Измените дату в A3, чтобы указать, что в отчете есть данные до 18 июня.

Удалите временные столбцы с C по H.

Похоже, исходные данные были отсортированы по убыванию дохода. Выберите ячейку B6 и нажмите кнопку ZA на вкладке "Данные" на ленте.

Этот процесс сравнения и объединения двух списков с помощью функции ВПР — основной элемент анализа данных. Есть более быстрый и простой способ сделать это. См. "– Использование сводной таблицы для сравнения двух списков" на странице 415.

Как объединить два списка без дубликатов в Excel?

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

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

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

< br />

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

<р>3. В диалоговом окне «Удалить дубликаты», если у вашего столбца нет заголовка, снимите флажок «Мои данные имеют заголовки» и нажмите «ОК». Смотрите скриншот:

<р>4. Затем на экране отобразится диалоговое окно, сообщающее, что дубликаты были удалены, нажмите «ОК». Смотрите скриншот:

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

Объединение списков без дубликатов с помощью VBA

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

<р>1. Удерживая нажатой клавишу ALT, нажмите F11 на клавиатуре, чтобы открыть окно Microsoft Visual Basic for Application.

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

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

<р>3. Нажмите кнопку «Выполнить» или нажмите F5, чтобы запустить VBA.

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

<р>5. Нажмите «ОК», после чего появится другое диалоговое окно, в котором вы можете выбрать ячейку для вывода объединенного результата. Смотрите скриншот:

<р>6. Нажмите «ОК». Вы можете видеть, что списки были объединены.

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

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

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

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

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

<р>2. В диалоговом окне "Выбор дубликатов и уникальных ячеек" установите флажок "Все уникальные ячейки (включая первые дубликаты)", нажмите "ОК".

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

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

В Excel вы можете объединить два списка без дублирования каких-либо значений с помощью функции «Удалить дубликаты». Допустим, у вас есть два списка имен (в столбцах B и C), как на рисунке ниже.

Чтобы объединить эти два списка в столбец B и удалить все повторяющиеся значения, выполните следующие действия.

<р>1. Выберите и щелкните правой кнопкой мыши второй диапазон, который будет объединен (C2:C6), и нажмите "Копировать" (или используйте сочетание клавиш CTRL + C).

<р>2. Щелкните правой кнопкой мыши первую пустую ячейку после первого списка (например, B10) и выберите "Вставить" (или используйте сочетание клавиш CTRL + V).

<р>3. Теперь у вас есть значения из обоих столбцов в столбце B, но есть несколько дубликатов.
Чтобы удалить дубликаты, щелкните в любом месте первого списка (B2:B15) и на ленте выберите Данные > Удалить дубликаты.

<р>4. Во всплывающем окне снимите флажок во втором столбце и нажмите «ОК».
Это удалит дубликаты для первого столбца в нашем диапазоне данных (столбец B). Содержимое второго столбца теперь не имеет значения, поскольку оно скопировано в столбец B.

<р>5. Вы получите информационное сообщение о том, что три повторяющихся значения удалены и осталось десять уникальных значений. Нажмите "ОК".

Наконец вы получили объединенный список значений из столбцов B и C в столбце B без дубликатов.

Имена Лиза, Роберт и Стивен удаляются из второго списка, поскольку они уже присутствовали в первом списке.< /p>

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

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

Вы также можете объединять списки без дубликатов в Google Таблицах.

<р>1. Выберите и щелкните правой кнопкой мыши второй диапазон, который будет объединен (например, C2:C6), и нажмите "Копировать" (или используйте сочетание клавиш CTRL + C).

<р>3. Теперь у вас есть значения из обоих столбцов в столбце B, но есть несколько дубликатов.
Чтобы удалить дубликаты, щелкните в любом месте первого списка (здесь B2:B15) и в меню выберите Данные > Удалить дубликаты.

<р>4. В окне «Удалить дубликаты» (1) снимите флажок «Выбрать все» и (2) установите флажок «Столбец Б», чтобы сравнивать только значения в столбце Б. Затем (3) нажмите «Удалить дубликаты».

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

Читайте также: