Где:

• $A2 — это значение, которое вы ищете.
• ‘Таблица поиска’!$A$2:$B$10 — это таблица для поиска (обратите внимание, что мы блокируем диапазон с абсолютными ссылками на ячейки).
• 2 — номер столбца, из которого нужно получить значение.

Скопируйте формулу вниз по столбцу, и вы получите объединенную таблицу , состоящую из основной таблицы и соответствующих данных, извлеченных из таблицы поиска:

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

Совет. Если у вас есть подписка на Excel 365, то вы можете использовать более мощный преемник ВПР — функцию XLOOKUP в Excel.

Как объединить таблицы в Excel с ПОИСКПОЗОМ ИНДЕКС

Если вы ищете более мощную и универсальную альтернативу функции ВПР, воспользуйтесь этой комбинацией ПОИСКПОЗ ИНДЕКС:

ИНДЕКС ( диапазон_возврата , ПОИСКПОЗ ( искомое_значение , диапазон поиска , 0))

Синтаксис подробно объясняется в этом руководстве: ИНДЕКС / ПОИСКПОЗ в Excel. И здесь я покажу вам, как использовать эту формулу для поиска справа налево , что не может сделать функция ВПР.

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

Для выполнения этой задачи вы вводите следующие аргументы в формулу Index Match:

• Диапазон_возврата — $E$2:$E$10
• Искомое_значение — $A2
• Lookup_range — $F$2:$F$10

Обратите внимание на знак $, который блокирует диапазоны, чтобы предотвратить их изменение при копировании формулы вниз по таблице:

Завершенная формула выглядит следующим образом:

=ИНДЕКС($E$2:$E$10, ПОИСКПОЗ($A2, $F$2:$F$10, 0))

…и прекрасно сочетает данные из двух таблиц:

В Excel 365 вы можете использовать новую функцию XLOOKUP для той же цели:

=XLOOKUP(A2, $F$2:$F$10, $E$2:$E$10, "Не найдено")

Как объединить таблицы путем сопоставления нескольких столбцов

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

ИНДЕКС( lookup_table , MATCH(1, ( lookup_value1 = lookup_range1 ) * ( lookup_value2 = lookup_range2 ), 0), return_column_number )

Примечание. Это формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно.

Разбивку формулы можно найти здесь: Поиск по нескольким критериям. А пока сосредоточимся на практическом использовании.

Предположим, что у вас есть следующие две таблицы, которые нужно объединить в одну. Поскольку столбец Order ID отсутствует в таблице поиска, единственный способ сопоставить заказы — это продавец и продукт :

Основываясь на приведенном выше снимке экрана, давайте определим аргументы для нашей формулы:

• Lookup_table — $F$2:$H$9
• Искомое_значение1 — $B2
• Lookup_range1 — $F$2:$F$9
• Искомое_значение2 — $C2
• Lookup_range2 — $G$2:$G$9
• Номер_возврата_столбца — 3

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

=ИНДЕКС($F$2:$H$9, MATCH(1, ($B2=$F$2:$F$9) * ($C2=$G$2:$G$9), 0), 3)

Введите формулу в D3, нажмите Ctrl + Shift + Enter, скопируйте ее в строки ниже и проверьте результат:

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

Объединение нескольких таблиц в одну с помощью Excel Power Query

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

• Power Query может объединить две таблицы, сопоставив один или несколько столбцов.
• Исходные таблицы могут находиться на одном листе или на разных рабочих листах.
• Исходные таблицы не изменены. Данные объединяются в новую таблицу, которую можно импортировать в существующий или новый рабочий лист.
• В Excel 2016 — Excel 365 Power Query является встроенной функцией. В Excel 2010 и Excel 2013 его можно загрузить как надстройку.

Подробное руководство можно найти в этом руководстве: Как соединить таблицы с помощью Excel Power Query.

Мастер объединения таблиц — быстрый способ объединения таблиц путем сопоставления столбцов

Если вы еще не очень хорошо разбираетесь в формулах Excel и у вас нет времени разбираться в загадочных тонкостях Power Query, наш мастер слияния таблиц поможет вам сэкономить время. Ниже я покажу три самых популярных варианта использования.

Пример 1. Объединение двух таблиц по нескольким столбцам

Если вам трудно запомнить формулу массива для сопоставления столбцов, положитесь на нашу надстройку, которая сделает эту работу быстро и идеально.

В этом примере мы будем использовать уже знакомые таблицы и соединить их на основе двух столбцов: Seller и Product . Обратите внимание, что таблица поиска имеет на 2 столбца больше, чем основная таблица:

После добавления мастера слияния таблиц на ленту Excel вам нужно сделать следующее:

1. Выберите любую ячейку в основной таблице и нажмите кнопку
   Merge Two Tables на вкладке Ablebits Data :
2. Убедитесь, что надстройка правильно указала диапазон, и нажмите Далее :
3. Выберите таблицу поиска и нажмите Далее :
4. Укажите пары столбцов для соответствия, Seller и Product в нашем случае, и нажмите Next :

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

5. При необходимости выберите столбцы для обновления значениями из таблицы поиска. Так как в 9 нечего обновлять0003 столбец Order ID , оставляем его невыбранным и просто нажимаем Next .
6. Выберите столбцы для добавления в основную таблицу и нажмите Далее .
7. На этом шаге вы сообщаете мастеру, как именно вы хотите объединить таблицы. Все опции имеют описательные метки, поэтому я не буду вдаваться в длинные объяснения. Если вы не уверены в каком-либо параметре, щелкните знак вопроса рядом с ним, и небольшая диаграмма покажет вам, как будут объединены таблицы.

Параметры по умолчанию прекрасно работают в нашем случае, поэтому мы нажимаем Готово ничего не меняя:

Дайте мастеру несколько секунд для обработки и просмотрите результат:

Как видно на скриншоте выше, мастер сделал следующее:

1. Добавлен столбец Сумма путем сопоставления имени продавца и продукта в обеих таблицах.
2. Добавлен столбец Статус , который позволяет легко фильтровать совпадающие и новые строки. Если вы этого не хотите, снимите соответствующий флажок на последнем шаге.
3. Новые строки, которые присутствовали только в таблице поиска, были скопированы в конец и выделены синим цветом.
   • Если вы не хотите выделять новые строки, снимите флажок Установить цвет фона для всех добавленных строк на последнем шаге.
   • Если вы не хотите добавлять новые строки, снимите флажок Добавить несоответствующие строки в конец основной таблицы на последнем шаге.

Пример 2. Объединение таблиц и обновление выбранных столбцов

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

В качестве примера объединим 2 таблицы по ID заказа и обновим значения в столбце Цена :

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

Шаг 1. Выберите главную таблицу.

Шаг 2. Выберите таблицу поиска.

Шаг 3. Выберите

Шаг 4. Выберите Цена в качестве столбца для обновления.

Шаг 5. Пропустите его, так как нет столбцов для добавления.

Шаг 6. Поскольку в столбце Новая цена есть несколько пробелов, мы выбираем обновление только в том случае, если ячейки таблицы поиска содержат дату a. При желании вы можете выделить обновленные ячейки любым цветом по вашему выбору. На скриншоте ниже показаны настройки:

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

Пример 3. Объединение нескольких совпадений из двух таблиц

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

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

Похоже, работы много? Нет, если у вас есть мастер слияния таблиц 🙂

Шаг 1. Выберите главную таблицу.

Шаг 2. Выберите таблицу поиска.

Шаг 3. Выберите Seller в качестве столбца для сопоставления.

Шаг 4. Обновление ID заказа и Product .

Шаг 5. Нет столбцов для добавления.

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

В приведенных выше примерах показаны только 3 из множества возможных способов соединения таблиц в Excel. Если вам интересно узнать о других сценариях, с которыми могут справиться мастера слияния таблиц, ознакомьтесь с иллюстрациями на этой странице. Или вы можете загрузить 30-дневную пробную версию и попробовать ее.

Объединение таблиц в Excel по заголовкам столбцов

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

На изображении ниже показаны исходные таблицы и желаемый результат:

А вот как можно выполнить задание:

1. На ленте Excel перейдите на вкладку Ablebits > группу Объединить и нажмите кнопку Объединить листы :
2. Выберите все рабочие листы, которые вы хотите объединить в один.

   Если вы хотите объединить только одну таблицу, а не все данные, наведите указатель мыши на имя листа и щелкните диалоговое окно « Свернуть». Значок 0004 справа для выбора диапазона:

3. Выберите столбцы, которые вы хотите объединить, Идентификатор заказа и Продавец в этом примере:
4. При необходимости выберите дополнительные параметры. Мы используем стандартные, которые отлично работают в большинстве случаев:
5. Наконец, укажите, куда вы хотите поместить результирующую таблицу, и нажмите Объединить :

Готово! Три таблицы объединяются в одну точно так же, как показано в начале этого примера.

Дополнительные инструменты для объединения таблиц в Excel

Мастер объединения таблиц и объединение листов — самые популярные инструменты для объединения таблиц в Excel. Если у вас есть какая-то другая задача, скорее всего, вы также найдете быстрое решение на вкладке Ablebits Data :

Позвольте мне кратко описать, что делает каждая из этих надстроек:

Объединить две таблицы — объединяет две таблицы, имеющие один или несколько одинаковых столбцов, как показано в этих примерах.

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

Объединить дубликаты — объединяет повторяющиеся строки по ключевым столбцам.

Consolidate Sheets — объединяет таблицы и суммирует их данные.

Копировать листы — предоставляет 4 различных способа объединения листов в Excel.

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

Мастер Vlookup — быстрый способ создать формулу Vlookup или Index/Match, наиболее подходящую для вашего набора данных.

Сравните листы — найдите, выделите и объедините различия между двумя листами.

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

Все вышеперечисленные функции, а также более 70 других инструментов для экономии времени включены в наш Ultimate Suite for Excel. Ознакомительная версия доступна для скачивания прямо под этим постом. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)

de 238 edd дополнительные инструкции