Как в экселе соединить две таблицы из разных файлов

Автоматический перенос данных из одной таблицы в другую в Excel

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

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

Рассмотрим, как соединить две таблицы по шагам.

Первый шаг.

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

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Третий шаг.

Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.

Четвертый шаг.

Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

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

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Правда нужно отметить, что этот способ подходит только пользователям   Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».

После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

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

Как объединить две или более таблиц?

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

Объединение строк на самом деле довольно просто, но объединение столбцов может быть громоздким, если строки в одной таблице не совпадают со строками в другой таблице. Используя функцию ВПР, вы можете избежать некоторых проблем с выравниванием.

Объединить две таблицы с помощью LOOKUP V

В следующем примере показаны две таблицы с новыми именами: «Синяя» и «Оранжевая».В синей таблице каждая строка соответствует позиции заказа. Таким образом, идентификатор заказа 20050 соответствует двум товарам, идентификатор заказа 20051 соответствует одному товару, идентификатор заказа 20052 соответствует трем товарам и так далее. Мы хотим объединить столбцы «Идентификатор продаж» и «Регион» с таблицей «Синий» на основе совпадающих значений в столбце «Идентификатор заказа» в таблице «Оранж».

Значения идентификатора заказа дублируются в синей таблице, но значения идентификатора заказа в оранжевой таблице уникальны.Если бы я просто скопировал и вставил данные из таблицы Orange, значения в столбцах «Идентификатор продаж» и «Регион» для второго элемента заказа 20050 были бы вне одной строки, что изменило бы значения в новых столбцах в Синий стол.

Вот данные синей таблицы, которые можно скопировать на пустой лист. Вставив его на лист, нажмите Ctrl + T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу в Excel Blue.

Вот данные для таблицы Orange.Скопируйте его на тот же лист. Вставив его на лист, нажмите Ctrl + T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу в Orange.

Нам нужно убедиться, что значения продаж и идентификатора региона для каждого заказа соответствуют каждой уникальной строке заказа.Для этого вставьте заголовки таблицы «Идентификатор продаж и регион» в ячейки справа от таблицы «Синий» и с помощью формул поиска. ВПР получите правильные значения из столбцов «Идентификатор продаж» и «Регион» таблицы «Оранж». .

Для этого выполните следующие действия:

1. Скопируйте заголовки Sales ID и Region из таблицы Orange (только эти две ячейки).

2. Вставьте заголовки в ячейку справа от заголовка идентификатора продукта в синей таблице.

   В синей таблице теперь пять столбцов, включая новые столбцы «Идентификатор продаж» и «Регион».

3. В синей таблице в первой ячейке под заголовком Идентификатор продажи начните вводить следующую формулу:

   = ВПР (

4. В синей таблице выберите первую ячейку в столбце идентификатора заказа, 20050.

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

   Часть [@ [Идентификатор заказа]] означает «получить значение в той же строке из столбца идентификатора заказа».

   Введите точку с запятой и выберите мышью всю таблицу Orange, чтобы добавить в формулу «Orange [#All]».

5. Введите следующую точку с запятой, число 2, снова точку с запятой и число 0, например:; 2; 0

6. Нажмите клавишу Enter.Вся формула выглядит следующим образом:

   Оранжевая часть [#All] означает "искать все ячейки в таблице Orange". Число 2 означает «получить значение из второго столбца», а число 0 означает «вернуть значение, только если есть точное совпадение».

   Обратите внимание, что Excel заполнил ячейки в этом столбце формулой ВПР.

7. Вернитесь к шагу 3, но на этот раз начните вводить ту же формулу в первой ячейке под заголовком «Регион».

8. На шаге 6 замените 2 на 3. Полная формула выглядит так:

   Между этой формулой и первой формулой есть только одно отличие — первая получает значения из столбца 2 таблицы Orange, а вторая — из столбца 3.

   Теперь вы увидите значения в каждой ячейке новых столбцов в синей таблице.Они содержат формулы ВПР, но отображают значения. Рекомендуется преобразовать формулы ВПР в этих ячейках в их фактические значения.

9. Выделите все ячейки со значениями в столбце Sales ID, а затем нажмите Ctrl+C, чтобы скопировать их.

10. Щелкните Главная > стрелку под Вставьте .

11. В галерее вставки щелкните Вставить значения .

    90 418

12. Выберите все ячейки со значениями в столбце «Регион», скопируйте их и повторите шаги 10 и 11.

    Теперь формулы ВПР в этих двух столбцах заменены значениями.

Дополнительная информация о таблицах и функциях ВПР

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить техническую поддержку в сообществе ответов.

Создание связей между таблицами в Excel

Вы когда-нибудь перемещали столбец из одной таблицы в другую с помощью функции ВПР? Теперь в Excel есть встроенная модель данных, поэтому функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе совпадающих данных в каждой таблице. Затем вы можете создавать листы Power View и создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы получены из разных источников.Например, для данных о продажах клиентов вы можете импортировать и связать данные аналитики времени для анализа моделей продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

1. Убедитесь, что ваша рабочая книга содержит как минимум две таблицы и что в каждой таблице есть столбец, который можно сопоставить со столбцом в другой таблице.

2. Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новый лист.

3. Дайте таблицам осмысленное имя: На вкладке Работа с таблицами щелкните Дизайн > Имя таблицы , чтобы ввести имя.

4. Убедитесь, что столбец в одной из таблиц имеет уникальные значения данных без дубликатов. Excel может создать связь только в том случае, если один столбец содержит уникальные значения.

   Например, чтобы связать продажи клиентов с данными аналитики времени, обе таблицы должны содержать даты в одном и том же формате (например, 01.01.2012) и по крайней мере в одной таблице (аналитика времени) каждая дата должна быть указана только один раз за столбец.

5. Нажмите Данные > Отношения .

Если Отношения неактивны, это означает, что рабочая книга содержит только одну таблицу.

1. В диалоговом окне Управление отношениями нажмите Создать .

2. В диалоговом окне Создать связь щелкните стрелку в Таблица и выберите таблицу из списка.Эта таблица должна находиться на стороне "многие" отношения "один ко многим". В нашем примере с данными о клиентах и ​​времени сначала следует выбрать таблицу продаж клиентов, так как каждый день можно назначить большое количество продаж.

3. В поле Column (Foreign Object) выберите столбец, содержащий данные, относящиеся к Related Column (Primary Object) . Например, если в обеих таблицах есть столбец даты, выберите этот столбец.

4. В поле Связанная таблица выберите таблицу, в которой есть хотя бы один столбец данных, связанный с таблицей, выбранной в Таблица .

5. В Related Column (Primary Object) выберите столбец, который содержит уникальные значения, совпадающие со значениями в столбце, выбранном в Column .

6. Нажмите OK .

Подробнее о связях между таблицами в Excel

Примечания о связи

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

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

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

• Типы данных в двух столбцах должны совпадать.Дополнительные сведения см. в разделе Типы данных в моделях данных Excel.

• Другие методы создания отношений могут быть более интуитивными, особенно если вы не знаете, какие столбцы использовать. См. раздел Создание связи в представлении схемы в Power Pivot.

Пример: создание связи между данными временного анализа и полетными данными для самолета

Узнайте о связях между таблицами и анализе времени, используя бесплатные данные из Microsoft Azure Marketplace.Некоторые из этих наборов данных очень велики и требуют высокоскоростного подключения к Интернету для их загрузки в разумные сроки.

1. Запустите Power Pivot в Microsoft Excel и откройте окно Power Pivot.

2. Нажмите Получить внешние данные > Из Данные> Из Microsoft Azure Marketplace .Домашняя страница Microsoft Azure Marketplace открывается в мастере импорта таблиц.

3. Под Цена (Цена), нажмите Бесплатно (Бесплатно).

4. В категории щелкните Наука и статистика .

5. Найдите DateStream и нажмите Подписаться .

6. Введите данные своей учетной записи Microsoft и нажмите Войти . Вы должны увидеть предварительный просмотр ваших данных в окне.

7. Прокрутите вниз и нажмите Выберите запрос .

8. Нажмите Далее .

9. Выберите BasicCalendarUS (Основной календарь США), а затем нажмите Готово для импорта данных.При высокоскоростном интернет-соединении импорт должен занять около минуты. По завершении вы должны увидеть отчет о состоянии, в котором указано, что было передано 73 414 строк. Нажмите кнопку Закрыть .

10. Щелкните Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace , чтобы импортировать второй набор данных.

11. Под Введите (Тип), нажмите Данные (Данные).

12. Под Цена (Цена), нажмите Бесплатно (Бесплатно).

13. Найдите Задержки рейсов авиаперевозчика США и нажмите Выберите .

14. Прокрутите вниз и нажмите Выберите запрос .

15. Нажмите Далее .

16. Щелкните Готово , чтобы импортировать данные. При высокоскоростном подключении к Интернету импорт может занять около 15 минут. По завершении вы должны увидеть отчет о состоянии, в котором сообщается, что было передано 2 427 284 строки. Нажмите кнопку Закрыть .Теперь в модели данных должно быть две таблицы. Чтобы создать связь между ними, каждая таблица должна содержать соответствующие столбцы.

17. Обратите внимание, что столбец DateKey (DateKey) в таблице BasicCalendarUS (USCalendar) содержит данные в формате 1/1/2012 12:00:00. Таблица On_Time_Performance (Пунктуальность) также содержит столбец даты и времени, FlightDate (DateLotu), значения которого указаны в том же формате: 1/1/2012 12:00:00.Эти два столбца содержат совпадающие данные одного типа, и по крайней мере один из этих столбцов — DateKey (DateKey) — содержит только уникальные значения. В следующих нескольких шагах вы будете использовать эти столбцы для создания связи между таблицами.

18. В окне Power Pivot щелкните Сводная таблица , чтобы создать сводную таблицу на новом или существующем листе.

19. В списке полей разверните таблицу On_Time_Performance (Пунктуальность) и щелкните столбец ArrDelayMinutes , чтобы добавить его в область значений.Общее время задержки рейса в минутах должно отображаться в сводной таблице.

20. Разверните таблицу BasicCalendarUS (US PrimaryCalendar) и щелкните столбец MonthInCalendar , чтобы добавить его в область строк.

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

22. В списке полей в разделе «Могут потребоваться связи между таблицами» нажмите Создать .

23. В поле «Связанная таблица» выберите On_Time_Performance (Пунктуальность), а в поле «Связанный столбец (основной)» выберите FlightDate (DataLot).

24. Выберите BasicCalendarUS (Primary CalendarUS) в поле Table и выберите DateKey (DateKey) в поле Column (Foreign). Щелкните OK , чтобы создать связь.

25. Обратите внимание, что сумма минут задержки теперь разная для каждого месяца.

26. В таблице BasicCalendarUS (PrimaryCalendarUS) перетащите столбец YearKey (YearKey) в область строк над MonthInCalendar (CalendarMonth).

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

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

1. Убедитесь, что таблица BasicCalendarUS (календарь BasicUS) открыта в окне Power Pivot.

2. На вкладке Главная щелкните Сортировать по столбцу .

3. В поле Сортировка выберите MonthInCalendar (MonthWCalendar).

4. В поле По выберите MonthOfYear (Месяц года).

В сводной таблице каждое сочетание месяц-год (октябрь 2011 г., ноябрь 2011 г.) теперь отсортировано по номеру месяца года (10, 11).Изменить порядок сортировки легко, так как канал данных DateStream предоставляет все столбцы, необходимые для запуска этого сценария. Если вы используете другую таблицу операций со временем, этот шаг будет другим.

"Могут потребоваться связи между таблицами"

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

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

Шаг 1. Определите, какие таблицы указать в отношении

Если ваша модель содержит только несколько таблиц, вы можете сразу понять, какую из них использовать.Однако для более крупных моделей вам может понадобиться помощь. Один из способов — использовать представление схемы в Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной части модели.

90 374

Примечание: Вы можете создавать неоднозначные отношения, которые недопустимы при использовании в сводной таблице или отчете Power View.Предположим, что все ваши таблицы каким-то образом связаны с остальными таблицами в модели, но когда вы пытаетесь объединить поля из разных таблиц, вы получаете сообщение «Возможно, вам понадобятся связи между таблицами». Наиболее вероятной причиной является отношение «многие ко многим». Если вы проанализируете цепочку связей между таблицами, соединяющими таблицы, которые вы хотите использовать, вы, скорее всего, обнаружите, что две или более связей являются отношениями «один ко многим». Не существует простого решения, подходящего для всех ситуаций, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить столбцы, которые вы хотите использовать, в одну таблицу.

Шаг 2. Найдите столбцы, которые можно использовать для создания пути от одной таблицы к другой

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

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

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

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

• Типы данных исходного столбца и столбца подстановки должны совпадать. Дополнительные сведения о типах данных см. в разделе Типы данных в моделях данных.

Дополнительные сведения о связях между таблицами см. в разделе Связи между таблицами в модели данных.

Начало страницы

Power Query (часть 2) — объединение и добавление — Excel BI — Power Query, Power Pivot, Power BI

Одной из основных функций баз данных является возможность объединения двух или более таблиц в одну большую таблицу. В базах данных, поддерживаемых языком SQL, есть соответствующие команды, отвечающие за эти действия типа JOIN. Соответствующие параметры также предусмотрены в надстройке Power Query, которая может успешно заменить формулу ПРОСМОТР в Excel.ПЕРПЕНДИКУЛЯРНО.

Прежде чем мы перейдем к PowerQuery, полезно вспомнить эквивалент команды JOIN в рабочей книге Excel — функцию VLOOKUP. Однако стоит помнить, что при очень большом количестве записей в таблицах могут возникнуть проблемы с производительностью.

Параметр Merge , аналогичный команде join в языке запросов SQL, сопоставляет записи из одной таблицы с соответствующими записями из другой таблицы. В результате их работы мы получаем таблицу, которая содержит столбцы из обеих таблиц-компонентов.Чтобы это произошло, в таблицах должны быть столбцы, действующие как первичный ключ и внешний ключ — другими словами, обе таблицы должны иметь столбец со значениями, на основе которых мы можем их связать.

Проще всего объяснить эту опцию на примере.

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

На этой основе мы можем создать таблицу, в которой каждому покупателю будет присвоен продавец - по имени, фамилии, филиалу и региону. Один из способов — использовать надстройку PowerQuery и параметр слияния .

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

После загрузки таблиц в наш файл выберите функцию Scal на вкладке POWERQUERY :

Этот же параметр также можно найти в редакторе запросов :

Откроется окно Merge, в котором выбираем объединяемые таблицы и выбираем столбцы с первичным и внешним ключом:

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

Каждый из приведенных выше параметров соответствует некоторому варианту команды SQL JOIN.

- Левый внешний (все в порядке, совпадение на другом) эквивалентно команде ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ . Это означает, что в результирующую таблицу войдут все строки из первой ("левой") таблицы и те, которые можно присвоить из второй ("правой") таблицы. Если невозможно присвоить строку правой таблицы записи из левой таблицы, ячейки будут заполнены значениями NULL.

На рисунке выше мы видим, что в таблице Vendor номер 14 не присвоен ни одному поставщику.

- Правый внешний (все вторые, совпадают с первыми) эквивалентен команде ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ . Это означает, что в результирующую таблицу будут включены все строки из второй таблицы и те, которые могут быть присвоены из первой таблицы. Если невозможно сопоставить какую-либо запись из левой таблицы с записью из правой таблицы, в ячейках появится значение NULL.

- Полное внешнее (все строки из обоих) эквивалентно ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ . В этом случае таблица результатов содержит все строки как из первой, так и из второй таблицы. Если в записях таблицы нет соответствующих ячеек, они заполняются значением NULL .

В этом случае порядок таблиц не имеет значения - таблица результатов будет содержать те же данные (без учета порядка столбцов в таблице результатов)

В приведенном выше случае компании 276,356 и 161 не присвоен ни один продавец (в таблице Клиенты в столбце Идентификатор продавца номер, который не имеет эквивалента в таблице Продавец ), а продавец Конрад Брожек не назначен ни один заказчик.

- Внутренний (только совпадающие строки) эквивалентен команде INNER JOIN . Это означает, что таблица результатов будет содержать строки из первой таблицы, которые могут быть связаны со строками второй таблицы, и строки из второй таблицы, которые могут быть связаны с записями из первой таблицы. Это означает, что в таблице результатов мы не найдем строк из левой или правой таблицы, которые не будут связаны между собой, а значит - программа нигде не присвоит значение NULL.

- Левый анти (только строки в первом) эквивалентен команде ЛЕВЫЙ АНТИ СОЕДИНИТЬ . Это особый тип соединения, который возвращает строки, которые не объединены во вторую правую таблицу, в качестве результирующего набора.

- Right anti (только строки из второго) эквивалентно функции RIGHT ANTI JOIN . Как и в предыдущем соединении, набор результатов представляет собой набор строк, не связанных с первой левой таблицей.

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

Сообщает, сколько строк взаимно совпало (количество строк в таблице, полученной в результате команды . Полный внешний )