Связь между таблицами в эксель практическая работа
С помощью кубов OLAP и других сложных источников данных, которые уже имеют богатые связи между таблицами, с помощью стеблиных таблиц традиционно были сложены. Однако в Excel вы можете импортировать несколько таблиц и создать собственные подключения между таблицами. Хотя эта гибкость является очень гибкой, она также упрощает сбор не связанных данных, что приводит к странным результатам.
Вы когда-нибудь создавали такие стебли? Вы создали разбивку по покупкам по регионам, поэтому вы сбросили поле "Сумма покупки" в область "Значения" и сбросили поле "Регион продаж" в область "Подписи столбцов". Однако результаты неправильные.
Проблема в том, что поля, добавленные в таблицу, могут быть в одной книге, но таблицы, содержащие каждый столбец, не связаны. Например, у вас может быть таблица со списком каждого региона продаж, а в другой — список покупок для всех регионов. Чтобы создать таблицу и получить правильные результаты, необходимо создать связь между двумя таблицами.
После создания связи данные из таблицы покупок будут правильно сочетаться со списком регионов, и результаты будут выглядеть так:
Excel 2013 содержит технологию, разработанную корпорацией Microsoft Research (MSR), для автоматического обнаружения и устранения таких проблем связей.
Использование автоматического обнаружения
Процесс состоит из двух этапов:
Обнаружение связей. По завершению анализа можно просмотреть список предлагаемых связей. Если вы не отмените ее, Excel будет автоматически перенаться к следующему шагу создания связей.
Создание связей. После того как связи будут применены, появится диалоговое окно подтверждения, и вы можете щелкнуть ссылку Сведения, чтобы увидеть список созданных связей.
Вы можете отменить процесс обнаружения, но не процесс создания.
Алгоритм MSR ищет "наилучший из возможных" набор связей для подключения таблиц в модели. Алгоритм определяет все возможные связи для новых данных с учетом имен столбцов, типов данных столбцов, значений в столбцах и столбцов, которые находятся в стеблях.
Excel затем выбирает отношение с самым высоким "качеством" оценки, определяемой внутренней ауристией. Дополнительные сведения см. в обзоре связейи устранении неполадок в связях.
Если автоматическое обнаружение не дает правильных результатов, вы можете изменить связи, удалить их или создать новые вручную. Дополнительные сведения см. в таблицах Создание связи между двумя таблицами и Создание связей в представлении диаграммы.
Пустые строки в сводных таблицах (неизвестный участник)
Так как в таблице собраны связанные таблицы данных, если какая-либо из них содержит данные, которые не могут быть связаны ключом или совпадаемым значением, эти данные необходимо каким-то образом обрабатывать. В многомерных базах данных для обработки несоответствия данных необходимо назначить всем строкам, которые не имеют совпадающих значений, неизвестному участнику. В ней неизвестный участник отображается в пустом заголовке.
Например, если вы создаете таблицу, которая должна группировать продажи по магазину, но в некоторых записях в таблице продаж нет названия магазина, все записи без допустимых названий магазинов будут сгруппироваться.
Если в итоге у вас пустые строки, у вас есть два варианта. Можно либо определить отношение между таблицами, которое работает, например путем создания цепочки связей между несколькими таблицами, либо удалить поля из таблицы, из-за которых появляются пустые строки.
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
Практическая работа № 13
Тема: СВЯЗИ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL
Цель: изучить технологию связей между файлами и консолидации данных в MS Excel .
Задание 1. Задать связи между файлами.
Порядок работы
1. Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу.
2. Создайте таблицу «Отчет о продажах 1 квартал» по образцу рис. 1. Введите исходные данные (Доходы и расходы):
Доходы = 234,58 р.;
Расходы = 75,33 р.
и проведите расчет Прибыли: Прибыль = Доходы – Расходы. Сохраните файл под именем «1 квартал».
3. Создайте таблицу «Отчет о продажах 2 квартал» по образцу рис. 1 в виде нового файла. Для этого создайте новый документ (Кнопка « Office »/Создать ) и скопируйте таблицу отчета о продаже за первый квартал, после чего исправьте заголовок таблицы и измените исходные данные:
Расходы = 125,8 р.
Обратите внимание, как изменится расчет Прибыли. Сохраните файл под именем «2 квартал».
4. Создайте таблицу «Отчет о продажах за полугодие» по образцу рис. 1 в виде нового файла. Для этого создайте новый документ (Кнопка « Office »/Создать ) и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и в колонке В удалите все значения исходных данных и результаты расчетов. Сохраните файл под именем «Полугодие».
Рис. 1. Задание связей между файлами
5. Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».
Краткая справка: для связи формулами файлов Excel выполните действия:
· откройте эти файлы (все три файла);
· начните ввод формулы в файле-клиенте (в файле «Полугодие» введите формулу для расчета «Доход за полугодие»).
Формула для расчета:
Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.
Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.
Полный адрес ячейки состоит из названия рабочей книги в квадратных скобках, имени листа, восклицательного знака и адреса ячейки на листе.
В ячейке В3 файла «Полугодие» формула для расчета полугодового дохода имеет следующий вид:
= `[1 квартал. xls ]Лист1`!$ B $3 + `[2 квартал. xls ]Лист1`!$ B $3.
Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1 квартал» и «2 квартал». Результаты работы представлены на рис. 1. Сохраните текущие результаты расчетов.
Примечание: если файл-источник данных закрыт, в формуле, которая на него ссылается, будет указан весь путь для этого файла.
Задание 2. Обновить связи между файлами.
Порядок работы
1. Закройте файл «Полугодие» предыдущего задания.
2. Измените значения «Доходы» в файлах первого и второго кварталов, увеличив их на 100 р.:
Доходы 1 квартала = 334,58 р.;
Доходы 2 квартала = 552,6 р.
Сохраните изменения и закройте файлы.
3. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи (рис. 2). Для обновления связей нажмите кнопку Да. Проследите, как изменились данные файла «Полугодие» (величина «Доходы» должна увеличится на 200 р. и принять значение 887,18 р.).
В случае, когда вы отказываетесь от автоматического обновления связи, вам приходится выполнить это действие вручную.
4. Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.
5. Вновь откройте файлы первого и второго кварталов и измените исходные данные Доходов, увеличив значения на 100 р.:
Доходы 1 квартала = 434,58 р.;
Доходы 2 квартала = 652,6 р.
Сохраните изменения и закройте файлы.
Рис. 2. Окно предложения обновления связи
6. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Нет. Для ручного обновления связи в меню Данные в поле Подключения выберите Изменить связи, появится окно, как на рис. 3.
Рис. 3. Ручное обновление связей между файлами
В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие». Расположите его так, чтобы были видны данные файла «Полугодие», выберите файл «1 квартал» и нажмите кнопку Обновить и проследите, как изменились данные файла «Полугодие». Аналогично выберите файл «2 квартал» и нажмите кнопку Обновить. Проследите, как вновь изменились данные файла «Полугодие».
Примечание: при изменении данных в нескольких исходных файлах обновление связи производится для каждого файла.
Задание 3. Консолидация данных для подведения итогов по таблицам данных сходной структуры.
Краткая справка: в Excel существует удобный инструмент для подведения итогов по таблицам данных сходной структуры, расположенных на разных листах или разных рабочих книгах, - Консолидация данных. При этом одна и та же операция (суммирование, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, и все формулы Excel строит автоматически.
Порядок работы
1. Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку В3.
2. Выполните команду Данные/Работа с данными/Консолидация (рис. 3).
Рис. 4. Консолидация данных
В появившемся окне Консолидация выберите функцию – «Сумма».
В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и опять нажмите кнопку Добавить (см. рис.4). В списке диапазонов будут находиться две области данных за первый и второй квартал для консолидации. Далее нажмите кнопку ОК, произойдет консолидированное суммирование данных за первый и второй кварталы.
Вид таблиц после консолидации данных приведен на рис. 5.
Рис. 5. Таблица «Полугодие» после консолидированного суммирования
Задание 4. Консолидация данных для подведения итогов по таблицам неоднородной структуры.
Порядок работы
1. Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу (рис. 6). Произведите расчеты и сохраните файл с именем «3 квартал».
2. Создайте новую электронную книгу. Наберите отчет по отделам за четвертый квартал по образцу (рис. 7). Произведите расчеты и сохраните файл с именем «4 квартал».
Рис. 6. Исходные данные для 3 квартала Задания 4
Рис. 7. Исходные данные для 4 квартала Задания 4
3. Создайте новую электронную книгу. Наберите название таблицы «Полугодовой отчет о продажах по отделам». Установите курсор на ячейку А3 и проведите консолидацию за третий и четвертый кварталы по заголовкам таблиц. Для этого выполните команду Данные/Работа с данными/Консолидация. В появившемся окне консолидации данных сделайте ссылки на диапазон ячеек А3:Е6 файла «3 квартал» и А3: D 6 файла «4 квартал» (рис. 8). Обратите внимание, что интервал ячеек включает имена столбцов и строк таблицы.
В окне Консолидация активизируйте опции (поставьте галочку):
· подписи верхней строки;
· значения левого столбца;
· создавать связи с исходными данными (результаты будут не константами, а формулами).
После нажатия кнопки ОК произойдет консолидация (рис. 9).
Рис. 8. Консолидация неоднородных таблиц
Сохраните все файлы в вашей папке.
Обратите внимание, что все данные корректно сгруппированы по их заголовкам (по отделам). В левой части экрана появятся так называемые кнопки управления контуром (иерархической структурой). С их помощью можно скрывать или показывать исходные данные.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите 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. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Чтобы сделать анализ данных более емким, создайте связи в разных таблицах. Связь — это соединение между двумя таблицами, которые содержат данные: один столбец в каждой таблице является основой для связи. Чтобы понять, чем полезны связи, представим, что отслеживаются данные для заказов клиентов в бизнесе. Вы можете отслеживать все данные в одной таблице с такой структурой:
DiscountRate
Однообъективный зеркальный фотоаппарат
Этот подход может быть эффективным, но он подразумевает хранение множества избыточных данных, таких, как адрес электронной почты клиента для каждого заказа. Хранение данных обходится дешево, но если адрес электронной почты изменился, необходимо убедиться, чтоб была обновлена каждая строка для этого клиента. Одним из решений этой проблемы является разбиение данных на несколько таблиц и задание связей между этими таблицами. Этот подход используется в реляционных базах данных таких, как SQL Server. Например, импортированная база данных может представлять данные заказа, используя три связанные таблицы.
CustomerDiscounts
DiscountRate
Однообъективный зеркальный фотоаппарат
Связи существуют в модели данных ( та, которую вы создали явным образом или которая Excel автоматически создается от вашего имени при одновременном импорте нескольких таблиц. Кроме того, вы можете воспользоваться надстройкой Power Pivot для создания модели и управления ею. Дополнительные сведения см. в статье Создание модели данных в Excel.
Во время импорта таблицы из одной базы данных с помощью надстройки Power PivotPower Pivot может обнаруживать связи между таблицами, основанными на столбцах, заключенных в [квадратные скобки], и воспроизводить эти связи в модели данных, создаваемой в фоновом режиме. Дополнительные сведения см. в разделе Автоматическое обнаружение и вывод связей этой статьи. Если таблицы импортируются из нескольких источников, можно вручную создать связи, как это описано в статье Создание связей между двумя таблицами.
Связи основываются на столбцах в каждой таблице, содержащих одинаковые данные. Например, можно связать таблицу "Клиенты" с таблицей "Заказы", если каждая из них содержит столбец с ИД клиента. В данном примере имена столбцов одинаковы, но это не является обязательным условием. Один столбец может называться CustomerID, а другой — CustomerNumber, при условии, что все строки в таблице Orders содержат идентификатор, который также хранится в таблице Customers.
В реляционной базе данных существует несколько типов ключей. Ключ обычно является столбцом со специальными свойствами. Знание назначения каждого ключа помогает в управлении моделью данных с несколькими таблицами, предоставляющей данные для сводной таблицы, сводной диаграммы или отчета Power View.
Хотя существует множество типов ключей, они являются самыми важными в нашем предназначении:
Первичный ключ: однозначно определяет строку в таблице, например CustomerID в таблице Customers.
Альтернативный ключ (или первичный ключ): уникальный столбец, который не является первичным ключом. Например, таблица Employees может хранить идентификатор работника и номер карточки социального страхования, при том что оба они являются уникальными.
Внешнее ключ: столбец, который ссылается на уникальный столбец другой таблицы, например CustomerID в таблице Orders, который ссылается на CustomerID в таблице Customers.
В модели данных первичный или резервный ключ называется связанным столбцом. Если таблица содержит первичный и резервный ключ, любой из них можно использовать как основу для связи между таблицами. Внешний ключ называется исходным столбцом или просто столбцом. В нашем примере связь между customerID в таблице Orders (столбцом) и CustomerID в таблице Customers (столбцом подытов) будет определена. Если данные импортируются из реляционной базы данных, по умолчанию Excel выбирает внешний ключ из одной таблицы и соответствующий первичный ключ из другой таблицы. Тем не менее для столбца подстановки можно использовать любой столбец, содержащий уникальные значения.
Связь между клиентом и заказом является связью "один-к-многим". Каждый клиент может иметь несколько заказов, однако ни один из заказов не может иметь несколько клиентов. Еще одна важная связь между таблицами — "один к одному". В нашем примере таблица CustomerDiscounts, которая определяет единую ставку дисконтирования для каждого клиента, имеет отношение "один-к-одному" с таблицей Customers.
В этой таблице показаны связи между тремя таблицами(Customers, CustomerDiscountsи Orders):
Столбец подстановки
Примечание: Связи «многие ко многим» не поддерживаются в модели данных. Примером связи «многие ко многим» является прямая связь между таблицами Products и Customers, в которой заказчик может купить много продуктов и одинаковый продукт может быть одновременно куплен несколькими заказчиками.
После создания связи необходимо Excel пересчет всех формул, которые используют столбцы из таблиц в созданной связи. Обработка может занять некоторое время в зависимости от объема данных и сложности связей. Дополнительные сведения см. в теме Пересчет формул.
Модель данных может содержать несколько связей между двумя таблицами. Для точного вычисления Excel требуется один путь от одной таблицы к другой. Поэтому одновременно активной может быть только одна связь между каждой парой таблиц. Хотя другие неактивны, вы можете указать неактивное отношение в формулах и запросах.
В представлении диаграммы активная связь является сплошной линией, а неактивные — пунктирными линиями. Например, в таблице AdventureWorksDW2012 таблица DimDate содержит столбец DateKey, связанный с тремя разными столбцами в таблице FactInternetSales:OrderDate, DueDateи ShipDate. Если есть активная связь между столбцами DateKey и OrderDate, эта связь и будет использоваться по умолчанию в формулах, если не указано иное.
Связь можно создать, если выполняются следующие требования.
Уникальный идентификатор для каждой таблицы
Каждая таблица должна иметь один столбец, однозначно определяющий каждую строку в этой таблице. Такой столбец часто именуется первичным ключом.
Уникальные столбцы подстановки
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это значит, что столбец подстановки не может содержать несколько значений NULL.
Совместимые типы данных
Типы данных в исходном столбце и в столбце подстановки должны быть совместимыми. Дополнительные сведения о типах данных см. в теме Типы данных, поддерживаемые в моделях данных.
В модели данных нельзя создать связь между таблицами, если ключ является составным. Также существует ограничение на создание связей «один к одному» и «один ко многим». Другие типы связей не поддерживаются.
Составные ключи и столбцы подстановки
Составной ключ состоит из нескольких столбцов. Модели данных не могут использовать составные ключи: таблица должна всегда иметь ровно один столбец, однозначно определяя каждую строку в таблице. При импорте таблиц, имеющих существующую связь на основе составного ключа, мастер импорта таблиц в Power Pivot не будет учитывать эту связь, так как ее нельзя создать в модели.
Для создания связи между двумя таблицами, имеющими несколько столбцов, в которых определены первичный и внешние ключи, сначала объедините значения для создания единого ключевого столбца. Это можно сделать перед импортом данных или путем создания вычисляемого столбца в модели данных с помощью надстройки Power Pivot.
Связи «многие ко многим»
Модель данных не может иметь связи «многие ко многим». В модель нельзя добавлять соединяющие таблицы . Тем не менее для моделирования связей «многие ко многим» можно использовать функции DAX.
Самосоединения и циклы
В модели данных не разрешается использование самосоединений. Самосоединение — это рекурсивная связь таблицы с самой собой. Самосоединения часто используются для определения иерархий типа «родители-потомки». Например, можно настроить самосоединение для таблицы Employees, чтобы создать иерархию, показывающую цепочку управления на предприятии.
Excel не позволяет создавать циклы среди связей в книге. Иными словами, следующий набор связей запрещается.
Таблица 1, столбец «а» к Таблице 2, столбец «f»
Tаблица 2, столбец «f» к Таблице 3, столбец «n».
Таблица 3, столбец «n» к Таблице 1, столбец «a».
При попытке создания связи, которая приведет к образованию цикла, выдается ошибка.
Одно из преимуществ импорта данных с помощью надстройки Power Pivot заключается в том, что Power Pivot иногда может обнаруживать связи и создавать новые связи в модели данных, создаваемой в Excel.
При импорте нескольких таблиц Power Pivot автоматически определяет все существующие связи между ними. Кроме того, при создании сводной таблицы Power Pivot анализирует данные в таблицах. Он обнаруживает возможные связи, которые не были определены, и предлагает столбцы, которые можно включить в них.
Алгоритм обнаружения на основании статистических данных о значениях и метаданных столбцов формирует выводы о вероятности связей.
Типы данных во всех связанных столбцах должны быть совместимыми. Для автоматического обнаружения поддерживаются только целочисленные и текстовые типы данных. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые вмоделях данных.
Для успешного обнаружения связи количество уникальных ключей в столбце подстановки должно превышать количество значений в таблице на стороне «многие». Другими словами, ключевой столбец на стороне «многие» связи не должен содержать значений, не содержащихся в ключевом столбце таблицы подстановки. Например, предположим, что имеется таблица, в которой перечислены продукты и их идентификаторы (таблица подстановки), а также таблица продаж, содержащая данные продаж всех продуктов (сторона «многие» связи). Если записи продаж содержат идентификатор продукта, не имеющего соответствующий идентификатор в таблице Products, связь нельзя создать автоматически, но можно создать вручную. Для обеспечения обнаружения связи с помощью Excel необходимо сначала обновить таблицу подстановки Product с использованием идентификаторов недостающих продуктов.
Убедитесь, что имя ключевого столбца на стороне «многие» совпадает с именем ключевого столбца в таблице подстановки. Имена не должны быть абсолютно идентичны. Например, в бизнес-параметрах часто имеются варианты имен столбцов, которые содержат фактически одинаковые данные: "ИД сотрудника", "ИД сотрудника", "ИД сотрудника", "EMP_ID"и так далее. Алгоритм выявляет похожие имена и задает более высокие значения вероятности столбцам, имена которых похожи или полностью совпадают. Поэтому, чтобы увеличить вероятность создания связи, можно попытаться переименовать столбцы в импортируемых данных, подобрав имена чем-то похожие на имена строк в существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.
Эти сведения помогают понять, почему не удалось выявить все связи и какие изменения в метаданных (именах полей и типах данных) могут повысить эффективность автоматического обнаружения связей. Дополнительные сведения см. в разделе Устранение неполадок в связях.
Автоматическое обнаружение именованных наборов
Связи между именованными наборами и связанными полями в сводной таблице не обнаруживаются автоматически. Такие связи можно создать вручную. При необходимости использования автоматического обнаружения связей удалите каждый именованный набор и добавьте отдельные поля из именованного набора непосредственно в сводную таблицу.
Вывод связей
В некоторых случаях связи между таблицами автоматически объединяются в цепочки. Например, если создать связь между первыми двумя наборами таблиц, указанных ниже, то определяется наличие связи между другими двумя таблицами и эта связь устанавливается автоматически.
Products и Category — связь создается вручную
Category и SubCategory — связь создается вручную
Products и SubCategory — связь определяется автоматически
Для автоматического объединения связей в цепочки эти связи должны идти в одном направлении, как показано выше. Если исходные связи были установлены, например между таблицами Sales и Products, а также между Sales и Customers, то связь не выводится. Это вызвано тем, что связь между таблицами Products и Customers является связью «многие ко многим».
Практическая работа "связанные таблицы" используется как закрепление знаний по теме "Excel”. Учащиеся повторяют темы абсолютные и относительные ссылки, форматы данных, виды меню. Рассматривая интерфейс прикладной программы Excel, учащиеся вспоминают понятие переключатель, флажок, списки, текстовое поле …
При вычислении используется логическая функция если… то..
Просмотр содержимого документа
«Меню разработчик»
Приложение (меню разработчик)
Меню разработчик
Лист1 – мат.плата
пкм
Просмотр содержимого документа
«Практическая работа»
Практическая работа (прайс-лист комплектующие компьютера)
Связанные таблицы
В зависимости от количества комплектующих создаем несколько листов с соответствующими именами. (пример)
Лист 1 –присвоим имя Материнская плата
Создаем итоговый лист (лист – связку, смотрим приложение)
Заполняем лист связку (смотри приложение)
Заполняем первую форму - Материнская плата
В поле Формировать список по диапазону указываем Материнская плата
и адрес диапазона $ A $2:$ A $4 (лкм на имени листа и диапазона)
В поле Связь с ячейкой введите адрес любой пустой ячейки на том же листе, например, D 6. В этой ячейке будет храниться номер элемента, выбранного из списка (по порядку: 1-ый, 2-ой или 3-ий).
Проверяем работу поля со списком и выполните аналогичные действия для остальных полей комплектующих.
Заполняем D 6 на листе – связке используя данные листа Материнская плата
Для того чтобы при выборе разных компонентов в столбце D на лист – связку подставлялись правильные цены, используйте функцию ЕСЛИ. Смысл ее таков: если в той ячейке, где хранится номер выбранного элемента (в нашем примере это ячейка D 6 на листе Платы), хранится число 1 – то на листе – связке должна появиться цена соответствующей модели материнской платы из ячейки В2, если 2 – другая цена из ячейки В3 и т.д. (указывайте адреса соответствующих ячеек щелчком мыши)
Для проверки: вложенная функция ЕСЛИ в строке формул:
=ЕСЛИ(Платы!D6=1;Платы!B2;ЕСЛИ(Платы!D6=2;Платы!B3;Платы!B4))
Если в D 6 стоит 1, то цена из В2; иначе: если в D 6 стоит 2, то цена из В3, иначе цена из В4
Проверьте работу функции ЕСЛИ (выбирайте разные платы и следите за изменением цены):
Выполните аналогичные действия для всех остальных полей (процессоров, памяти, винчестеров и т.д.)
В ячейке D 18 на листе – связке вычислите сумму по столбцу D (=D6+D8+D10+D12+D14+D16). Проверьте ее изменение при выборе других моделей комплектующих.
Работа с переключателями. Переключатели будут работать следующим образом: если будет выбран переключатель 2 (гарантия 2 года), то сумма покупки увеличится на 50$. Щелкните ПКМ по любому из переключателей и выберите команду Формат объекта, установите связь с любой пустой ячейкой на листе – связке, например G 20, в которой будет храниться номер выбранного переключателя (1 или 2)
В ячейке D 20, используя функцию ЕСЛИ (=ЕСЛИ(G20=2;50;0)), вычислите стоимость гарантии (если переключатель в ячейке G 20 = 2, то начисляем 50 $, иначе нет начислений)
Работа с флажком. Если установлен флажок «нужна доставка», то цена покупки увеличится на 20 $. Щелкните ПКМ по флажку и команду Формат объекта. Установите связь с любой пустой ячейкой на листе – связке, например G 22, в которой будет храниться значение, соответствующее состоянию флажка (ИСТИНА, если флажок установлен и ЛОЖЬ в противном случае).
В ячейке D 22, используя функцию ЕСЛИ (=ЕСЛИ(G22=ИСТИНА;20;0)), вычислите стоимость доставки (если в ячейке G 22 стоит значение ИСТИНА, то начисляем 20$, иначе нет начислений)
В ячейке D 24, используя функцию ЕСЛИ (=ЕСЛИ(D18700;D18*0,05;0)), вычислите величину скидки, которая зависит от стоимости заказа (если сумма в ячейке D 18 более 700$ то предоставляется скидка 5%, иначе – не предоставляется).
В ячейке D 26 вычислите полную стоимость компьютера с учетом гарантии, доставки, скидки. (=D18+D20+D22-D24)
Вычислите в столбце Е соответствующие цены в рублях, используйте абсолютную ссылку на курс $.
Подберите самый дешевый и самый дорогой вариант комплектации компьютера.
Читайте также: