В этом руководстве вы найдете несколько приемов для объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах.
Как часто при анализе в Excel вы собираете всю необходимую информацию на одном листе? Сейчас это довольно редкое явление. В большинстве случаев разные данные разбросаны по многим листам и книгам. К счастью, есть несколько способов объединить информацию из нескольких таблиц в одну, и это руководство покажет вам, как это сделать быстро и эффективно.
Объединение нескольких таблиц — одна из самых сложных задач в Excel. Если вы решите сделать это вручную, вы можете потратить часы только на то, чтобы обнаружить, что вы испортили важную информацию. Если вы опытный специалист по Excel, вы можете положиться на формулы ВПР и ИНДЕКС ПОИСКПОЗ. Вы также можете использовать Power Query или дополнительные надстройки Excel. Выбор за вами.
- Сумма данных
- Использование ВПР
- Приложение ИНДЕКС + ПОИСКПОЗ
- Формула соединения для нескольких ключевых столбцов
- Объединение таблиц с помощью Power Query
- Быстрый способ соединить таблицы — сопоставить ключевые столбцы.
Как объединить две таблицы в Excel с помощью формул.
Если вам нужно что-то сделать в Excel, где вы в первую очередь ищете решение? Как и многие пользователи, я обычно открываю список функций. Соединение таблиц не исключение 🙂
Суммирование.
Начнем с самого простого примера, когда вам нужно объединить данные из нескольких таблиц путем их суммирования. Предположим, у нас есть данные о продажах за 4 квартала. Нам нужно найти их сумму.
Сразу оговоримся, что этот способ подходит в первую очередь для отчетных форм, когда таблицы имеют одинаковую структуру, а числа в них идут в точно таком же порядке.
Так что добавление соответствующих значений ячеек на каждом листе здесь нормально.
Вы можете использовать простейшую формулу сложения для создания годового отчета о продажах.
=’1q’!C3+’2q’!C3+’3q’!C3+’4q’!C3
Более технологичным вариантом является так называемое 3D дополнение —
=СУММ(‘1Q:4Q’!C3)
Указываем первый и последний лист, а также координаты ячейки. Excel самостоятельно находит все промежуточные координаты. Если вы добавите еще несколько листов с одинаковыми флажками между первым и последним листами, они также будут суммированы.
Конечно, это не в полном смысле объединения таблиц, так как набор показателей не меняется, не добавляются новые строки и столбцы. Но он объединяет существующие данные. Далее рассмотрим более сложные случаи, когда нужно объединить неравные таблицы.
Используем ВПР.
Если вы хотите объединить две таблицы на основе одного и того же столбца, вам подойдет ВПР.
Предположим, у вас есть две таблицы на двух разных листах: основная содержит коды заказов и клиентов (то есть кому вы продаете), а вторая содержит заказы и товары (то есть то, что вы продаете). Вы хотите объединить их, сопоставив данные в столбце Код заказа:
То есть в случае совпадения заказов информация о товаре будет добавлена в первую таблицу соответствующей строки.
Как видите, порядок заказов в основной таблице не совпадает со вторым. Так что простой метод копирования/вставки не сработает.
Чтобы нам было проще ориентироваться, мы используем именованные диапазоны. Назовем первый «Заказ», второй — «Товар».
Для объединения двух таблиц по соответствующему показателю (Код заказа) сначала добавляем дополнительные столбцы E, F и G, которые будем заполнять необходимой информацией.
Начнем с продукта. Введите эту формулу в ячейку E2, как показано на следующем снимке экрана:
=ВПР(A2,продукт,2,0)
или с обычными ссылками —
=ВПР(A2,Элемент!$A$2:$D$200,2,0)
Тогда в G2 пишем —
=ВПР(B2,продукт,3,0)
В H2 аналогично —
=ВПР(B2,продукт,4,0)
Скопируйте эти формулы и получите результат объединения:
Объединенная таблица состоит из родительских и дочерних данных, полученных из массива поиска.
Обратите внимание, что функция ВПР в Excel имеет несколько ограничений, наиболее важными из которых являются:
1) невозможность извлечения данных, расположенных слева от столбца поиска,
2) жестко закодированный номер столбца нарушит формулу при добавлении или удалении столбцов во второй таблице. С другой стороны, в случае таких изменений можно легко изменить порядок возвращаемых столбцов, просто изменив число в третьем аргументе функции.
Рассмотрим более сложный случай, когда мы получили информацию из разных источников и ключевой показатель (код заказа) не совпадает полностью. Например, в него добавляются какие-то символы.
В случае использования функции ВПР у нас нет другого выхода, кроме как создать вспомогательный столбец. В нем мы попробуем перевести наш код заказа в стандартный вид, используя различные функции обработки текста.
Итак, вставим в нашу таблицу заказов вспомогательную колонку B. Для этого кликаем правой кнопкой мыши по заголовку колонки B и в появившемся контекстном меню выбираем «Вставить». Получаем пустой столбец, а все остальное сместится вправо.
Здесь мы будем «очищать» наши порядковые номера от ненужного хлама. В нашем случае вполне подходит формула:
=ЛЕВО(A2,4)
A2 — это адрес ячейки, из которой мы извлекаем первые 4 символа.
Если, например, у нас есть «В-1007», то используем формулу
=ВПРАВО(A2,4)
Но наши идентификаторы могут выглядеть иначе.
Предположим, вы хотите пропустить первые X символов и извлечь следующие Y символов: например, вам нужно извлечь «0123» из ввода «SBBL-1007-SHKL». Здесь нам нужно пропустить первые пять символов и извлечь следующие четыре. Формула будет выглядеть так:
=СРЕДНЯЯ(A2,5,4)
Если вам нужно извлечь все символы до разделителя, а их количество может быть другим («1007-СРР 256» или «10071007-Б111НРР»), то действуем так —
=ВЛЕВО(A2,ПРОСМОТР(«-«,A2)-1)
Получаем 1007 и 10071007 соответственно.
Если вам нужно получить последнее слово после разделителя «BS-100777», и когда количество символов может быть другим —
=ВПРАВО(A2,ДЛИНА(A2)-ПРОСМОТР(«*»,ПОДСТАВИТЬ(A2,»-«,»*»,ДЛИНА(A2)-ДЛИНА(ПОДСТАВИТЬ(A2;»-«;»»)))))
Получаем 100777.
Одним словом, текстовые функции помогут нам заполнить вспомогательную колонку.
А затем применяем те же формулы, что и раньше, только меняем ссылку на ячейку поиска:
=ВПР(B2,продукт,2,0)
или
=ВПР(B2,Элемент!$A$2:$D$200,2,0)
Как видите, с помощью дополнительного столбца мы решили проблему несовпадения кодов клавиш и успешно объединили данные.
Комбинация ИНДЕКС + ПОИСКПОЗ.
Если вы ищете более мощную и универсальную альтернативу функции ВПР, используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.
ИНДЕКС(возвращаемый_диапазон; ПОИСКПОЗ(искомое_значение; искомый_диапазон; 0))
Если вернуться к предыдущему примеру с функцией ВПР, то формула
ВПР(A2,Продукт!$A$2:$D$200,2,0) в ячейке E2 можно заменить на
=ИНДЕКС(‘Элемент’!$B$2:$B$200,ПОИСКПОЗ(A2,’Элемент’!$A$2:$A$200,0))
Вроде ничего особенного, просто стало тяжелее. Тем не менее, здесь есть гораздо больше возможностей. При необходимости мы можем найти столбец B и вернуть соответствующее значение из A. То есть порядок столбцов в данном случае не важен. ВПР не позволит нам этого сделать.
Например, если во второй таблице указатели расположены так, как на скриншоте ниже, то функция ВПР нам здесь не поможет.
Выражение в ячейке C2 выглядит так:
=ИНДЕКС(‘Элемент’!$A$2:$A$20,MATCH(A2,’Элемент’!$B$2:$B$20,0))
Это так называемый «левый ВПР», который не зависит от взаимного расположения столбцов.
Обратите внимание на знак $, который исправляет диапазоны, чтобы предотвратить их изменение при копировании формулы.
В случае примерного совпадения кодов заказов, которое мы рассматривали ранее, комбинация ИНДЕКС+ПОИСКПОЗ позволяет обойтись без вспомогательного столбца.
Формула массива поможет нам
{=ИНДЕКС(Элемент!$B$2:$B$200,ПОИСКПОЗ(1,ПОИСК(СЛЕВА(A2,4),Элемент!$A$2:$A$200,1),0))}
Как видите, мы берем первые 4 символа номера заказа и сопоставляем товары.
Естественно, вместо LEFT мы можем использовать другие варианты текстовых функций. Мы подробно говорили о них ранее, когда рассматривали возможное содержимое вспомогательной колонки. С помощью формулы массива мы полностью вставляем ее непосредственно в формулу поиска.
Как объединить на основе нескольких столбцов.
Если две таблицы, которые вы хотите соединить, не имеют уникального идентификатора, такого как номер заказа, вы можете сопоставить значения в двух или более столбцах в качестве ключевых столбцов, используя эту формулу:
ИНДЕКС(искомая_таблица; ПОИСКПОЗ(1; (искомое_значение1 = искомое_диапазон1) * (искомое_значение2 = искомое_диапазон2); 0); возвращаемый_номер_столбца)
Это формула массива, поэтому не забудьте нажать Ctrl+Shift+Enter, чтобы ввести ее правильно.
Допустим, у вас есть две таблицы, которые нужно объединить в одну. Поскольку в массиве поиска нет флага «Заказ», единственный способ сопоставить их — это «Клиент» и «Продукт:
Основываясь на скриншоте выше, давайте определим аргументы нашей формулы:
- диапазон поиска — $F$3:$H$10
- искомое_значение1 — $B3
- search_range1 — $F$3:$F$10
- искомое_значение2 — $C3
- search_range2 — $G$3:$G$10
- return_column_number — 3
Обязательно добавьте абсолютные ссылки на ячейки, чтобы они не менялись при копировании формулы:
=ИНДЕКС($F$3:$H$10; ПОИСКПОЗ(1; ($B3=$F$3:$F$10) * ($C3=$G$3:$G$10); 0); 3)
Введите формулу в D3, нажмите Ctrl+Shift+Enter. Скопируйте его и проверьте результат:
Поэтому мы объединили значения в таблицах, если есть совпадение сразу в двух столбцах. Я думаю, вы понимаете логику этой формулы и можете добавить условие совпадения в еще один столбец, если это необходимо.
Объединяем с помощью Excel Power Query
В ситуациях, когда вам нужно соединить две или более таблиц с разным количеством строк и столбцов, Excel Power Query может пригодиться. Однако имейте в виду, что присоединиться к Power Query нельзя всего за пару кликов. Это лишь самые основные функции:
- Power Query может объединить две таблицы, сопоставив один или несколько столбцов.
- Исходная информация может находиться на одном листе или на разных листах.
- Исходные данные не будут изменены. Они копируются в новую таблицу, которую можно импортировать в существующий или новый рабочий лист.
- В Excel 2016 и Excel 2019 Power Query является встроенной функцией. В Excel 2010 и Excel 2013 его можно загрузить как надстройку.
В двух словах, Power Query (также известный как Get & Transform в Excel 2016 и Excel 2019) — это инструмент для объединения, очистки и преобразования данных из нескольких источников в нужный вам формат: обычную таблицу, сводную таблицу или сводную диаграмму.
Чтобы результаты соответствовали вашим ожиданиям, обратите внимание на следующее:
- Соединяемые таблицы должны иметь хотя бы один общий столбец (также известный как общий идентификатор или ключ). Кроме того, эти общие столбцы должны содержать только уникальные значения без дубликатов.
- В отличие от формул, Power Query не переносит данные из одной таблицы в другую. Создайте совершенно новую таблицу, объединяющую информацию из исходных таблиц.
- Результирующая таблица не обновляется автоматически. Вы должны явно указать Excel, чтобы сделать это.
Источник данных
В качестве примера давайте объединим 3 таблицы на основе общих показателей «Идентификатор заказа» и «Идентификатор клиента». Обратите внимание, что в каждой из них разное количество строк, в первой и второй есть дублирующиеся клиенты и товары, в третьей — только уникальные записи.
Наша задача — сопоставить данные в таблице 1 с соответствующими записями в двух других и объединить их все так:
Прежде чем вы начнете присоединяться, я предлагаю вам дать своим рангам описательные имена, чтобы вы могли легко распознавать их и управлять ими позже. Кроме того, хотя мы говорим «таблицы», вам не нужно преобразовывать данные в электронную таблицу Excel. Источниками могут быть обычные диапазоны или именованные диапазоны, как в этом примере:
- первый называется Орден,
- второй товар,
- третий — Скидка.
Создание подключений Power Query
Чтобы не загромождать книгу копиями исходных данных, мы преобразуем их в соединения, объединим их в редакторе Power Query, а затем загрузим только окончательный результат.
Чтобы создать объединение в Power Query, выполните следующие действия:
- Выберите свой первый диапазон (Порядок) или любую ячейку в нем.
- Перейдите на вкладку «Данные» и нажмите «Из таблицы/диапазона».
- В открывшемся редакторе Power Query щелкните стрелку раскрывающегося списка «Закрыть и загрузить» (не саму кнопку!) и выберите параметр «Закрыть и загрузить в…» .
- В диалоговом окне «Импорт данных» выберите параметр «Только создать соединение» и нажмите «ОК» .
Это создаст соединение с именем вашего диапазона и отобразит его на панели «Запросы и соединения», которая появится в правой части вашей книги.
- Повторите вышеуказанные шаги для всех остальных таблиц, которые вы хотите объединить.
Когда вы закончите, вы увидите все соединения на правой панели:
Как объединить два соединения в одну таблицу.
Теперь посмотрим, как можно объединить две таблицы в одну с помощью созданных подключений:
- На вкладке «Данные» нажмите кнопку «Получить данные», в раскрывающемся меню выберите «Объединить запросы» и нажмите «Объединить»:
- В диалоговом окне «Объединение» выполните следующие действия:
- Выберите свой первый ранг (Заказ) из первого выпадающего меню.
- Выберите второй (Продукт) из второго выпадающего списка.
- В обоих предварительных просмотрах щелкните соответствующий столбец (Идентификатор заказа), чтобы выбрать его. Он будет выделен зеленым цветом.
- В раскрывающемся списке Тип вложения оставьте значение по умолчанию: Снаружи слева (все с первого, совпадение со второго).
- Нажмите «ОК.
После выполнения описанных выше шагов редактор Power Query отобразит вашу первую таблицу (Порядок) с дополнительным столбцом. Она будет называться вашей второй таблицей (Product) и будет добавлена в конце. Значений пока нет, только слово «Таблица» во всех ячейках. Но не волнуйтесь, вы все сделали правильно, и мы скоро это исправим!
Выберите столбцы для добавления из второй таблицы
На данный момент у вас есть результат, показанный на скриншоте ниже. Чтобы завершить процесс слияния, выполните следующие действия в редакторе Power Query:
- В последнем столбце (Продукт) щелкните двунаправленную стрелку в заголовке.
- В открывшемся окне выполните следующие действия:
- Не снимайте флажок «Расширить».
- Отмените выбор всех вариантов, а затем выберите только те, которые вы хотите скопировать. В этом примере мы выбираем только столбцы «Продукт» и «Количество», поскольку исходная информация уже содержит код заказа.
- Снимите флажок Использовать исходное имя столбца в качестве префикса (если вы не хотите, чтобы перед именем столбца стояло имя таблицы, из которой оно было взято).
- Нажмите «ОК.
В результате вы получите новую таблицу, содержащую все записи из вашей первой и дополнительные столбцы из второй:
Если вам нужно только объединить две таблицы, можете считать работу почти выполненной и загрузить результат в Excel. Но нам нужно добавить больше информации.
Как объединить больше таблиц.
Если вам нужно присоединиться к трем или более столам, вам еще предстоит проделать определенную работу. Я кратко опишу здесь шаги, потому что вы уже все сделали, объединив первые два диапазона:
- Сохраните результат предыдущего шага (показан на скриншоте выше) как соединение:
- В редакторе запроса питания нажмите кнопку «Закрыть и загрузить» на стрелке раскрывающегося списка и выберите «Закрыть и загрузить…
- В диалоговом окне «Импорт данных» выберите «Только создать подключение» и нажмите «ОК» .
Это добавит еще одно соединение с именем Confluence1 на панель «Запросы и соединения». При желании вы можете изменить имя этого соединения. Щелкните его правой кнопкой мыши и выберите «Переименовать» во всплывающем меню.
- Объедините Merge1 с вашей третьей таблицей (Discount), следуя уже известным нам шагам (вкладка «Данные» — «Получить данные» — «Объединить запросы).
На следующем снимке экрана показана моя конфигурация:
- При нажатии кнопки «ОК» в диалоговом окне «Объединение» открывается редактор Power Query. В ней вы выбираете все, что нужно добавить из третьей таблицы.
В этом примере нам нужен только индикатор Discount:
В результате вы получите новую объединенную таблицу, состоящую из исходных и дополнительных столбцов, скопированных из двух других.
Как импортировать объединенную таблицу в Excel.
С результатами в редакторе Power Query все, что вам нужно сделать, это загрузить их в книгу Excel. И это самая легкая часть!
- В редакторе запросов нажмите «Закрыть и загрузить.
- В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новый лист».
- Нажмите «ОК.
На новом листе появится новая таблица, объединяющая информацию из двух или более источников. Поздравляем, вы сделали это!
В качестве последнего штриха вы можете применить к скидкам процентный формат и, возможно, изменить стиль по умолчанию на свой любимый. После этих улучшений моя объединенная таблица выглядит великолепно:
Примечание. Если вы работаете с числовыми показателями (например, по динамике продаж) и хотите провести их дальнейший анализ, вы можете скачать полученную сводку в виде отчета сводной таблицы (это второй пункт в окне импорта).
Как сделать объединение на основе нескольких столбцов.
В приведенном выше примере мы объединили таблицы, сопоставив ключевой столбец в каждой таблице. Но ничто не мешает вам выбрать две или более пар столбцов. Вот как мы это сделаем:
В диалоговом окне Merge, удерживая нажатой клавишу Ctrl, щелкните ключевые столбцы один за другим, чтобы выбрать их. Важно, чтобы вы щелкали их в одном и том же порядке в обоих предварительных просмотрах, чтобы одинаковые столбцы имели одинаковые номера. Например, менеджер — это ключевой столбец № 1, а продукт — это ключевой столбец № 2. Пустые ячейки или строки, которым Power Query не может сопоставиться, отображают null :
После этого выполните точно такие же шаги, как описано выше, и ваши таблицы будут объединены с соответствующими значениями по менеджеру и продукту.
Как обновить объединенную таблицу
Отличительной особенностью Power Query является то, что вам нужно настроить присоединяемую таблицу только один раз. Когда вы вносите некоторые изменения в исходные файлы, вам не нужно повторять весь процесс снова. Просто нажмите кнопку «Обновить» на панели «Запросы и подключения», и объединенная таблица будет немедленно обновлена:
Если панель исчезла из Excel, нажмите кнопку «Запросы и подключения», чтобы вернуть ее.
Кроме того, вы можете нажать кнопку «Обновить все» на вкладке «Данные» или кнопку «Обновить» в запросе (эта вкладка активируется после выбора любой ячейки в объединенной таблице).
Теперь рассмотрим использование специальной программы.
Быстрый способ объединить таблицы путем сопоставления ключевых столбцов.
Если вы еще не очень хорошо знакомы с формулами Excel и у вас нет времени на изучение загадочных функций Power Query, мастер объединения двух таблиц может сэкономить вам много времени и нервов. Ниже я покажу три наиболее популярных способа объединения таблиц.
Пример 1. Объедините две таблицы по нескольким столбцам.
Если вам трудно запомнить и применить формулу массива для столбцов, положитесь на специальный плагин, который сделает работу быстро и без проблем.
В этом примере мы воспользуемся уже известными таблицами из предыдущих разделов этой статьи и объединим их в 2 столбца: «Код клиента» и «Товар».
Итак, у нас в основной таблице есть список заказов для нескольких покупателей. И для этих покупателей на некоторые товары есть таблица скидок. Вы должны перенести процент скидки в основную таблицу. Обратите внимание, что в основной таблице больше строк, чем в таблице поиска:
С мастером соединения таблиц вам необходимо сделать следующее:
- Выберите любую ячейку в основной таблице и нажмите кнопку «Объединить две таблицы» на вкладке «Данные Ablebits»:
- Убедитесь, что программа правильно выбрала диапазон ячеек, и нажмите «Далее).
- Выберите таблицу поиска и нажмите «Далее:
- Укажите пары столбцов, которые должны совпадать в обеих таблицах, в нашем случае «Продавец» и «Продукт», и нажмите «Далее:
Примечание. Если важна чувствительность к регистру текста в ключевых столбцах, установите флажок С учетом регистра, чтобы буквы верхнего и нижнего регистра воспринимались как отдельные символы.
- При желании выберите столбцы для обновления со значениями из таблицы поиска. Поскольку нам нечего обновлять, мы оставляем все как есть и просто нажимаем «Далее».
- Выберите столбцы, которые вы хотите добавить в основную таблицу, и нажмите «Далее». Мы добавили скидку, как и планировали.
- На этом этапе вы сообщаете мастеру, как именно вы хотите объединить таблицы. Все опции имеют описательные метки, поэтому я не буду вдаваться в подробные объяснения. Если вы не уверены в каком-либо варианте, щелкните знак вопроса рядом с ним, и небольшой рисунок покажет вам, как будут объединены таблицы.
В нашем случае параметры по умолчанию работают нормально, поэтому нажимаем Finish, ничего не меняя:
Дайте мастеру несколько секунд на обработку и посмотрите результат:
Как видно на скриншоте выше, мастер сделал следующее:
- Добавлен столбец «Скидка» путем сопоставления имени поставщика и названия продукта в обеих таблицах.
- В столбце есть пустые ячейки, потому что не все товары были со скидкой для этих продавцов.
Пример 2. Объединение таблиц и обновление выбранных столбцов.
Если ваша основная таблица содержит устаревшие данные, вы можете обновить ее соответствующими значениями из таблицы поиска.
Продолжим рассмотрение данных из предыдущего примера. Предположим, некоторые покупатели изменили размер скидки и в таблицу скидок были добавлены дополнительные товары. В этом смысле наша основная таблица устарела и нуждается в обновлении. Давайте снова объединим 2 таблицы по имени клиента и продукту и обновим значения в столбце «Скидка.
Чтобы получить желаемый результат, нужно сделать следующее:
Шаг 1. Выберите главную таблицу.
Шаг 2. Выберите таблицу поиска.
Шаг 3. Выберите «Идентификатор клиента» и «Товар» в качестве ключевых столбцов.
Шаг 4: Выберите столбец «Скидка» для обновления. То есть обновленные значения соответствующего столбца таблицы поиска будут переданы в этот столбец основной таблицы. Параметры для этого дополнения вы укажете чуть позже.
Шаг 5. Пропустите, потому что у нас нет столбцов для добавления. Нам просто нужно обновить информацию.
Шаг 6. Поскольку в столбце «Скидка» есть несколько пробелов, мы выбираем обновление только в том случае, если ячейки в таблице поиска содержат данные. Таким образом мы обновим устаревшие значения, но не потеряем данные, которые по какой-то причине исчезли из таблицы поиска. При желании вы можете выделить обновленные ячейки любым цветом по вашему выбору. На следующем снимке экрана показаны настройки:
Наконечник. Чтобы предотвратить перезапись существующих данных, вы можете обновлять только пустые ячейки в основной таблице.
И вот результат:
Как видите, две скидки добавлены и две обновлены по мере изменения их значений.
Конечно, это можно сделать и по формулам, как описано в начале этой статьи, но затрат времени будет на порядок больше.
Пример 3. Объединение по нескольким совпадающим значениям из двух таблиц.
В ситуациях, когда таблица поиска содержит несколько вхождений значения ключа, вы можете перетащить их все в родительскую таблицу. Вы можете выполнить эту задачу, используя одну из пользовательских формул массива. Или вы можете сделать это простым способом с Мастером присоединения к таблице.
Допустим, ваша основная таблица содержит только один заказ от каждого клиента, но ваша таблица поиска содержит дополнительные заказы от многих клиентов. Теперь вы хотите добавить все существующие заказы покупателей в основную таблицу и сгруппировать их по коду поставщика. Например, найти во второй таблице все заказы покупателя с кодом «Красный» и поместить их в первую таблицу сразу после существующей записи для этого покупателя (то есть из 3-й строки).
Похоже, предстоит много работы? Нет, если у вас есть мастер объединения двух таблиц 🙂
А теперь давайте рассмотрим все шаги по объединению таблиц шаг за шагом.
Шаг 1. Выберите главную таблицу.
Шаг 2. Выберите таблицу поиска.
Здесь вы можете увидеть файл записей о продажах. Из него нам нужно извлечь только записи 4-х покупателей в основной таблице.
Шаг 3. Выберите Код покупателя в соответствующем столбце.
Эта операция нам уже знакома: выбираем ключевой столбец, в котором будем искать совпадения в обеих таблицах.
Шаг 4. Выберите столбцы, в которых необходимо обновить информацию.
Нам нужны все столбцы, поэтому мы можем просто установить флажок в заголовке таблицы, чтобы выбрать их все сразу.
Шаг 5: Нет столбцов для добавления. Мы прыгаем.
Шаг 6 Вставьте дополнительные совпадающие строки после строки с тем же значением ключа. При необходимости установите цвет фона для добавленных строк, чтобы просмотреть изменения с первого взгляда:
Чтобы не потерять значения, которые уже есть в основной таблице, активируйте опцию «Обновлять только пустые ячейки в основной таблице».
И вот результат:
Как видите, осталось 4 строки, и к каждой из них в конце добавляются данные этого клиента, извлеченные из справочной таблицы.
В приведенных выше примерах показаны только 3 из множества возможных способов соединения таблиц в Excel. Вы можете скачать 14-дневную пробную версию и попробовать объединить таблицы с вашими данными.
Если у вас есть вопросы или комментарии, не стесняйтесь использовать комментарии.
Мы познакомили вас с основными способами соединения двух или более таблиц. Думаю, вы сможете выбрать наиболее подходящий для вас исходя из имеющейся информации и решаемых задач.
Проверка данных в Excel: как, использовать и удалить — Мы увидим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недействительные записи,…
Быстрое удаление пустых столбцов в Excel. В этом руководстве вы узнаете, как легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простого нажатия кнопки. Как ни банально это может показаться, устранение пустоты…
Как быстро объединить несколько файлов Excel. Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» надстройки Ultimate Suite. Гораздо проще обрабатывать данные в…
6 примеров — как объединить данные и объединить листы Excel в один — в этой статье обсуждаются различные способы объединения листов в Excel в зависимости от результата, которого вы хотите достичь: объединить все данные из выбранных листов, объединить несколько листов с разным порядком столбцов, объединить…
Как полностью или частично заморозить ячейку в формуле: при вводе формулы Excel знак $ в ссылке на ячейку сбивает многих пользователей с толку. Но объяснение очень простое: это просто способ исправить это. Знак доллара в этом случае работает только…
В чем разница между абсолютной, относительной и смешанной адресацией? Нельзя недооценивать важность ссылок на ячейки в Excel. Ссылка включает адрес, с которого вы хотите получать информацию. При этом используются два основных вида адресации: абсолютная и относительная. Они есть…
Относительные и абсолютные ссылки: как создавать и изменять. В руководстве объясняется, что такое адрес ячейки, как правильно писать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылки на ячейки Excel,…
6 способов быстро транспонировать таблицу. В этой статье показано, как столбец может быть преобразован в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или специального инструмента. Другими словами, мы узнаем, как транспонировать таблицу… 4 способа быстро удалить разрывы строк в ячейках Excel. В этом совете вы найдете 4 совета по удалению разрывов строк из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013…
Как безопасно удалять пустые ячейки в Excel и как никогда не следует этого делать. В этом руководстве вы узнаете, как правильно и безопасно удалять пустые ячейки в электронных таблицах Excel, чтобы они выглядели четко и профессионально. Пустые ячейки — это неплохо, если оставить их намеренно