Объединение строк на самом деле довольно простое, но объединение столбцов может быть сложным, если строки одной таблицы не соответствуют строкам в другой таблице. Используя VLOOKUP, вы можете избежать некоторых проблем с выравниванием.
Объединить две таблицы с помощью функции VLOOKUP
В примере, показанном ниже, вы увидите две таблицы https://goo-table.ru/, которые ранее имели другие имена для новых имен: «Blue» и «Orange». В Синей таблице каждая строка является позицией для заказа. Итак, идентификатор заказа 20050 содержит два элемента, идентификатор заказа 20051 содержит один элемент, идентификатор заказа 20052 содержит три элемента и так далее. Мы хотим объединить столбцы Sales ID и Region с синей таблицей на основе совпадающих значений в столбцах Order ID оранжевой таблицы.
Значения идентификатора заказа повторяются в синей таблице, но значения идентификатора заказа в оранжевой таблице уникальны. Если бы мы просто скопировали и вставили данные из Оранжевой таблицы, значения идентификатора продажи и региона для второй позиции заказа 20050 были бы уменьшены на одну строку, что изменило бы значения в новых столбцах в синей таблице.
Вот данные для Синей таблицы, которые вы можете скопировать на пустой лист. После того, как вы вставите его в рабочий лист, нажмите Ctrl + T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу Excel в синий.
| Идентификатор заказа | Дата продажи | Идентификатор продукта |
|---|---|---|
| 20050 | 2/2/14 | C6077B |
| 20050 | 2/2/14 | C9250LB |
| 20051 | 2/2/14 | M115A |
| 20052 | 2/3/14 | A760G |
| 20052 | 2/3/14 | E3331 |
| 20052 | 2/3/14 | SP1447 |
| 20053 | 2/3/14 | L88M |
| 20054 | 2/4/14 | S1018MM |
| 20055 | 2/5/14 | C6077B |
| 20056 | 2/6/14 | E3331 |
| 20056 | 2/6/14 | D534X |
Вот данные для Оранжевой таблицы. Скопируйте его в тот же рабочий лист. После того, как вы вставите его в рабочий лист, нажмите Ctrl + T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу в Orange.
| Идентификатор заказа | Идентификатор продажи | Регион |
|---|---|---|
| 20050 | 447 | Запад |
| 20051 | 398 | Юг |
| 20052 | 1006 | Север |
| 20053 | 447 | Запад |
| 20054 | 885 | Восток |
| 20055 | 398 | Юг |
| 20056 | 644 | Восток |
| 20057 | 1270 | Восток |
| 20058 | 885 | Восток |
Нам нужно убедиться, что значения идентификатора продажи и региона для каждого заказа правильно совпадают с каждой уникальной позицией строки заказа. Для этого давайте вставим заголовки таблиц Sales ID и Region в ячейки справа от синей таблицы и с помощью формул VLOOKUP получим правильные значения из столбцов Sales ID и Region оранжевой таблицы.
Вот как:
- Скопируйте заголовки Sales ID и Region в оранжевую таблицу (только эти две ячейки).
- Вставьте заголовки в ячейку справа от заголовка идентификатора продукта в синей таблице.
Теперь синяя таблица имеет ширину в пять столбцов, включая столбцы new Sales ID и Region.
- В синей таблице, в первой ячейке под идентификатором продаж, начните записывать эту формулу:
= ВПР (
- В синей таблице выберите первую ячейку в столбце идентификатора заказа, 20050.
Частично заполненная формула выглядит следующим образом:
Часть [@[Идентификатор заказа]] означает «получить значение в этой же строке из столбца идентификатора заказа».
Введите запятую и выделите всю оранжевую таблицу с помощью мыши, чтобы к формуле добавился «Оранжевый [#Все]».
- Введите еще одну запятую, 2, еще одну запятую и 0 — вот так: ,2,0
- Нажмите Enter, и завершенная формула будет выглядеть следующим образом:
Оранжевая часть [#All] означает «просмотр всех ячеек в оранжевой таблице». 2 означает «получить значение из второго столбца», а 0 означает «возвращать значение только при точном совпадении».
Обратите внимание, что Excel заполнил ячейки в этом столбце, используя формулу VLOOKUP.
- Вернитесь к шагу 3, но на этот раз начните писать ту же формулу в первой ячейке под областью.
- На шаге 6 замените 2 на 3, чтобы завершенная формула выглядела следующим образом:
Есть только одно отличие между этой формулой и первой формулой — первая получает значения из столбца 2 Оранжевой таблицы, а вторая получает их из столбца 3.
Теперь вы увидите значения в каждой ячейке новых столбцов в синей таблице. Они содержат формулы ВПР, но они будут показывать значения. Вам нужно преобразовать формулы ВПР в этих ячейках в их фактические значения.
- Выделите все ячейки со значениями в столбце Идентификатор продажи и нажмите Ctrl + C, чтобы скопировать их.
- Нажмите Главная> стрелка ниже Вставить.
- В галерее вставки нажмите Вставить значения.
- Выделите все ячейки со значениями в столбце Регион, скопируйте их и повторите шаги 10 и 11.
Теперь формулы ВПР в двух столбцах были заменены значениями.