Создание связанных таблиц
Как Вы могли убедиться из моих прошлых постов, меры являются сильными инструментами анализа данных и разрешают создавать невообразимые ранее виды расчётов. Но, до сего времени при знакомствами с мерами мы употребляли лишь одну таблицу t_sales. Но вся красота Power Pivot в том, что с его помощью можно создавать расчёты, сочитая данные из нескольких таблиц. По моему личному воззрению, если б даже Powe Pivot не имел встроенного движка функций DAX, одна лишь способность связывания таблиц, уже оправдывала бы его существование.
Создание связей
Так как создаются связи меж таблицами? Всё весьма просто. Заходим в окно Power Pivot и в правом нижнем углу, жмем на иконку со всплывающей надписью «Диаграмма».
Или по кнопочке «Представление диаграммы» на вкладке «Основная».
Раскроется окно представления диаграммы, в котором все таблицы нашей модели данных показаны в виде отдельных окошек, со перечнем колонок.
Как лицезреем, эти таблицы меж собою пока никак не соединены. Чтоб сделать меж ними связь, нам поначалу необходимо найти схожие колонки. Схожими колонками, именуют колонки содержащие однообразные данные. Например, и в таблице t_sales и в таблице t_products есть колонки КодПродукта, содержащие однообразные данные (при всем этом не непременно, чтоб наименования колонок в обоих таблицах были схожими). Свяжем эти две таблицы меж собою кликнув по наименованию колонки КодПродукта в t_sales и удерживая левую клавишу мыши нажатой, перетащим эту колонку к иной колонке КодПродукта в t_products.
Буквально также, связь меж таблицами можно сделать через команду «Создание связи» на вкладке «Конструктор».
Но можно ли создавать связь меж таблицами по хоть каким схожим столбцам? К примеру столбцы «ЦенаЗаШтуку» в t_sales и «Стоимость» в t_products содержат однообразные данные. Попробуем сделать меж ними связь путём перетаскивания. Power Pivot выдаст ошибку: «Не удалось сделать связь, так как в любом столбце содержатся повторяющиеся значения. Изберите по последней мере один столбец, содержащий лишь неповторимые значения.»
Другими словами для того, чтоб установить связь меж таблицами, один из связывающих столбцов должен содержать лишь неповторимые, не повторяющиеся значения. Например, стоимость у нескольких товаров быть может схожей (повторяться), потому употреблять эти столбцы для сотворения связи меж таблицами не получится. А вот «КодПродукта» в t_products содержит лишь неповторимые значения, потому мы и смогли употреблять его для сотворения связи.
Таблицы, содержащие столбцы с неповторимыми значениями, по которым устанавливается связывание, именуются «таблицами поиска» (lookup tables).
Ниже представлена сводная таблица на базе данных таблицы t_sales.
Как это работает
Функция CALCULATE () и связанные таблицы
Давайте сделаем ещё одну связь меж таблицами. Свяжем таблицу t_sales с таблицей t_clients.
В таблице t_sales, в столбце КоличествоДетейНаПопечении, имеется информация о том сколько отпрысков находятся на попечении всякого клиента. Сделаем меру, которая бы на основании этих данных, рассчитывала сумму продаж клиентам с детками:
Как в 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 бранится и дает поменять их местами.
Аналогично создаём остальные связи.
В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Дела
Чтоб созидать больше полей на панеле Поля сводной таблицы , можно через клавишу Сервис (в виде шестерёнки) избрать это представление:
Итог будет таковым:
В итоге все наши таблицы сейчас соединены и вы сможете сформировать, например, таковой отчёт:
Просто и комфортно!
Читайте также:
Введение в сводные таблицы
Автоматизация форматирования сводных таблиц
0
Неувязка, поднятая Николаем весьма верная. Здесь вправду не всё так просто. Потому пошевелил мозгами, что мой ответ будет увлекателен и иным читателям данной нам статьи:
————————————-
Николай,здрасти.
Я понимаю ваши затруднения. К примеру, чтоб посчитать стоимость какого-нибудь
продукта в заказе, нужно [OrderLines].[количество]
помножить на [Goods].[Цена]. Это делается с помощью
вычисляемого поля, которое вы сделать в меню Анализ сводной таблицы не сможете,
потому что эта таблица построена на базе Модели данных, а это уже часть PowerPivot функционала. Добавлять
вычисляемый столбец нужно через модуль PowerPivot,
который у вас в Excel будет
лишь в версии Prof Plus. Речь идёт про MS Office 2013.
0
Получил такое письмо:
——————————-
Денис, здрасти,
спасибо за вашу статью про сводные таблицы по нескольким спектрам.
http://perfect-excel.ru/publ. -1-0-67
не могли бы вы дать подсказку, как при помощи сводных таблиц выяснить стоимость
заказов из вашего примере.
к примеру, выстроить такие отчеты.
— вид продукта — общая стоимость согласно заказам
— клиент — общая сумма заказов
— заказ № — стоимость заказа
и т.п.
конкретно через сводные таблицы, а не модификацией текущих 4 таблиц.
Заблаговременно огромное спасибо.
Как синхронизировать таблицы Microsoft Excel
Вы сможете синхронизировать электрические таблицы Microsoft Excel, чтоб конфигурации в одном автоматом отражались в другом. Можно создавать ссылки меж разными рабочими листами, также отдельными рабочими книжками Excel. Давайте разглядим три метода, как это создать.
Синхронизируйте электрические таблицы Excel, используя функцию вставки ссылки
Функция вставки ссылок в Excel обеспечивает обычный метод синхронизации электрических таблиц Excel. В этом примере мы желаем сделать сводный лист итогов продаж по нескольким разным листам.
Начните с открытия электрической таблицы Excel, кликнув ячейку, на которую вы желаете сослаться, и потом изберите клавишу «Копировать» на вкладке «Основная».
Изберите ячейку, с которой вы ссылаетесь, нажмите стрелку перечня «Вставить», потом изберите «Вставить ссылку».
Адресок, по которому ячейка синхронизируется, отображается на панели формул. Он содержит имя листа, за которым следует адресок ячейки.
Синхронизировать таблицы Excel при помощи формулы
Иной подход — сделать формулу без помощи других, не используя клавишу «Вставить ссылку».
Синхронизация ячеек на различных листах
Поначалу кликните ячейку, из которой вы создаете ссылку, и введите «=».
Потом изберите лист, содержащий ячейку, на которую вы желаете сослаться. Ссылка на лист отображается в панели формул.
В конце концов, нажмите на ячейку, на которую желаете сослаться. Заполненная формула отображается на панели формул. Нажмите кнопку «Ввод».
Синхронизация ячеек на отдельных рабочих книжках
Вы также сможете стопроцентно связать ячейку на листе иной рабочей книжки. Для этого нужно поначалу убедиться, что иная рабочая книжка открыта, до этого чем начинать формулу.
Нажмите на ячейку, с которой желаете связать, и введите «=». Переключитесь на другую книжку, изберите лист, потом нажмите на ячейку для ссылки. Имя книжки предшествует имени листа в строке формул.
Если книжка Excel, на которую вы ссылаетесь, закрыта, в формуле будет показан полный путь к файлу.
И когда рабочая книжка, содержащая ссылку на другую рабочую книжку, будет открыта, для вас, возможно, будет предложено обновить ссылки. Это зависит от ваших опций сохранности.
Нажмите «Включить содержимое», чтоб обновления в иной книжке автоматом показывались в текущей.
Синхронизация таблиц Excel с внедрением функции поиска
Прошлые методы синхронизации 2-ух листов либо рабочих книжек употребляют ссылки на определенную ячейку. Время от времени это быть может недостаточно, поэтому что ссылка возвратит неверное значение, если данные отсортированы и перемещены в другую ячейку. В этих сценариях внедрение функции поиска является неплохим подходом.
Существует огромное количество функций поиска, но почаще всего употребляется VLOOKUP, потому давайте воспользуемся сиим.
В этом примере у нас есть обычный перечень данных о сотрудниках.
На другом листе мы храним учебные данные о сотрудниках. Мы желаем отыскать и возвратить возраст служащих для анализа.
Эта функция просит 4 частей инфы: что находить, где находить, номер столбца с возвращаемым значением и какой тип поиска для вас нужен.
Была применена последующая формула VLOOKUP.
A2 содержит идентификатор сотрудника для поиска на листе служащих в спектре A: D. Столбец 4 этого спектра содержит возраст для возврата. И False показывает четкий поиск по идентификатору.
Способ синхронизации электрических таблиц Excel почти во всем определяется тем, как структурированы ваши данные и как они употребляются.
