Как связать таблицы в excel
Как связать таблицы в excel
Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.
Проблема
Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.
Пример
В нашем примере мы располагаем 4-мя таблицами: Заказы , Строки заказов , Товары , Клиенты .
Таблица Строк заказов:
Исходные таблицы оформлены в виде умных таблиц: Orders , OrderLines , Goods и Clients .
Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа , таблицы Orders и Clients — по полю ID_клиента , таблицы OrderLines и Goods — по полю ID_товара .
Скачать пример
Создание модели данных
Создадим сводную таблицу на основе любой из имеющихся таблиц.
Выбираем в меню Вставка пункт Сводная таблица . В указанном диалоговом окне мы видим опцию Добавить эти данные в модель данных . Мы могли бы её выбрать, но я рекомендую другой, более удобный способ. Просто нажмите OK .
В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ.
Нажмём её. Появится такой вопрос:
Отвечаем Да и видим, что в список полей добавились все наши таблицы:
Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ.
Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines . Обратите внимание, что Excel умеет создавать связь типа » один к одному » или » один ко многим «. Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.
Аналогично создаём другие связи.
В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения
Чтобы видеть больше полей на панеле Поля сводной таблицы , можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:
Результат будет таким:
В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:
Связывание таблиц в Excel
Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения ячеек из нескольких разных таблиц на одном рабочем листе.
Изменение содержимого ячейки на одном листе или таблице (источнике) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.
В зависимости от техники исполнения связывание бывает «прямым» и через команду «Специальная вставка».
Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа 1) и оба листа загружены данными, то такое связывание листов называется «прямым».
Термин «прямое» связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком «!».
Примеры формул: = C5*Лист1! A4
= Лист1! A1-Лист2! A1
Примечание.Для указания ссылки на ячейки и листы, находящиеся в незагруженных (неоткрытых) рабочих книгах, в формуле нужно без пробелов задать полный путь местонахождения файла. Путь задается в одинарных кавычках, где указывается название диска, каталога (папки), имя рабочей книги (имя файла) в квадратных скобках и имя листа, на который идет ссылка.
= ‘D:Документы студентов[Расчет дохода.xls]Закупка продукции’!H19
Связывание через команду «Специальная вставка« производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.
Чтобы отразить в ячейке С4 на листе Цена продукциизначение ячейки Н4 на исходном листе Закупка продукции,нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка/Копировать.На листе Цена продукциипоставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка/Специальная вставка/Вставить связь(см. рис. 6). Тогда на листе Цена реализациипоявится указание на ячейку исходного листа Закупка продукции,например: = Закупка продукции!$Н$4
При таком связывании EXCEL автоматически использует абсолютный адрес на ячейку, т.к. относительный адрес обращения может привести к ошибкам, особенно если обращаться к незагруженным файлам (рабочим книгам).
Задание 7.Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена реализациис соответствующими ячейками на листе Закупка продукции,используя при этом различные способы связывания ячеек (рис. 28).
Рис. 28. Связывание ячеек различных рабочих листов
Не нашли то, что искали? Воспользуйтесь поиском:
Создание связи между двумя таблицами в Excel
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Нажмите кнопку ОК.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей похоже на использование Влукупс: вам понадобятся столбцы с соответствующими данными, чтобы приложение Excel могло перекрестно ссылаться на строки в одной таблице с помощью других таблиц. В примере логики операций со временем в таблице клиента должны быть указаны значения даты, которые также есть в таблице операций со временем.
В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите кнопку Далее.
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите кнопку Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите кнопку Далее.
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов ( DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
В поле «Таблица» выберите BasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле «Сортировать» выберите MonthInCalendar.
В поле «По» выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
«Могут потребоваться связи между таблицами»
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Связанные таблицы в Excel, как их сделать? Простые советы
Как сделать связанные таблицы в Excel? В статье будет раскрыт ответ на вопрос. С помощью простой инструкции мы соединим таблицы.
Связанные таблицы в Excel, что это такое и зачем с ними работать
Здравствуйте друзья! Во время работы с таблицами Excel приходится их связывать. Что такое связанные таблицы в Эксель? Их называют Эксель таблицами, с помощью которых переносится информация с одной таблицы на другую таблицу.
С помощью связанных таблиц, можно легко работать с большими таблицами. Например, быстро проводить по ним поиск нужных Вам данных, менять различные значения, добавлять в них символы, текст и так далее. А слишком большая таблица, занимает много времени в работе, когда в ней пишешь или ищешь какую-нибудь информацию.
Связанные таблицы могут разделяться на несколько листов и работа с ними будет проходить гораздо быстрее. Далее, будем разбирать инструкцию, которая поможет в создании связанных таблиц.
Как связать две таблицы в Excel, варианты
Разберём два эффективных варианта создания связанных Эксель таблиц. В начале работы, создайте на своём компьютере две таблицы. Например, первая таблица будет с таким названием, как «Даты», а вторая таблица пустая.
Вместе с тем, запускаете на компьютере ещё одну таблицу – «Даты» и записываете в неё значения. Например, «0.1.11.2019» Январь и так далее. Выделяете столбцы в таблице с любой информацией и нажимаете правой кнопкой мыши – «Копировать» (Скрин 1).
Запускаем второй лист таблицы Excel. Затем, нужно кликнуть в любую ячейку таблицы компьютерной мышкой и кликните кнопки – «Вставить» и «Вставить связь» (Скрин 2).
После их нажатия, в Вашей таблице появятся данные из другой таблицы (Скрин 3).
Таким образом она приобретёт связь с другой таблицей. Это способ одиночный. Вам придётся несколько раз нажимать на рассмотренные выше кнопки. Поэтому, рассмотрим ещё один метод, как связать две таблицы в Excel по пошаговой инструкции.
Запустите на своём компьютере пустую Excel таблицу. Далее, в ней нажмите кнопку – «Вставка» затем, «Объект» (Скрин 4).
Выбираете из появившегося окна раздел – «Из файла». Далее, нажимаете кнопку «Обзор» и загружаете другую таблицу Эксель нажатием кнопки «Вставить». После этого, Вы сможете соединить сразу две таблицы.
Заключение
В статье мы научились создавать связанные таблицы в Excel. Мы разобрали два способа, которые наиболее эффективны для новичков. Конечно, есть и другие варианты. Например, вставить таблицу в Эксель, через кнопки «Файл» и «Открыть» или с помощью формул. Эти практические советы Вам должны помочь в соединении двух таблиц Excel. Спасибо за внимание, удачи Вам!
С уважением, Иван Кунпан.
P.S. Практические статьи по работе с Excel-таблицами:
