Главная » Статьи » Excel » Сводные таблицы
Сводная таблица на основе двух и более связанных таблиц

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы, Строки заказов, Товары, Клиенты.

Таблица заказов:

Таблица Строк заказов:

Таблица Товаров

Таблица Клиентов

Исходные таблицы оформлены в виде умных таблиц: Orders, OrderLines, Goods и Clients.

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа, таблицы Orders и Clients - по полю ID_клиента, таблицы OrderLines и Goods - по полю ID_товара.

Скачать пример

Создание модели данных

Создадим сводную таблицу на основе любой из имеющихся таблиц.

  1. Выбираем в меню Вставка пункт Сводная таблица. В указанном диалоговом окне мы видим опцию Добавить эти данные в модель данных. Мы могли бы её выбрать, но я рекомендую другой, более удобный способ. Просто нажмите OK.

  2. В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ...

  3. Нажмём её. Появится такой вопрос:

  4. Отвечаем Да и видим, что в список полей добавились все наши таблицы:

  5. Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ...

  6. Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines. Обратите внимание, что Excel умеет создавать связь типа "один к одному" или "один ко многим". Причём первой надо указывать таблицу, где "много", в противном случае Excel ругается и предлагает поменять их местами.

  7. Аналогично создаём другие связи.


  8. В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

  9. Чтобы видеть больше полей на панеле Поля сводной таблицы, можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

  10. Результат будет таким:

  11. В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:

Просто и удобно!

Читайте также:

Категория: Сводные таблицы | Добавил: dsb75 (23.10.2014) | Автор: Батьянов Денис E W
Просмотров: 54338 | Комментарии: 2 | Теги: pivot tables, сводные таблицы | Рейтинг: 4.4/5
Всего комментариев: 2
0
2 dsb75   (19.08.2015 12:14) [Материал]
Проблема, поднятая Николаем очень правильная. Тут действительно не всё так просто. Поэтому подумал, что мой ответ будет интересен и другим читателям этой статьи:
-------------------------------------
Николай,здравствуйте.

Я понимаю ваши затруднения. Например, чтобы посчитать стоимость какого-либо
товара в заказе, надо [OrderLines].[количество]
умножить на [Goods].[Цена]. Это делается при помощи
вычисляемого поля, которое вы создать в меню Анализ сводной таблицы не можете,
так как эта таблица построена на основе Модели данных, а это уже часть PowerPivot функционала. Добавлять
вычисляемый столбец надо через модуль PowerPivot,
который у вас в Excel будет
только в версии Prof Plus. Речь идёт про MS Office 2013.

0
1 dsb75   (19.08.2015 12:13) [Материал]
Получил такое письмо:
-------------------------------
Денис, здравствуйте,
спасибо за вашу статью про сводные таблицы по нескольким диапазонам.
http://perfect-excel.ru/publ....-1-0-67
 
не могли бы вы подсказать, как с помощью сводных таблиц узнать стоимость
заказов из вашего примере.
 
например, построить такие отчеты.
 
- вид продукта - общая стоимость согласно заказам
- клиент - общая сумма заказов
- заказ № - стоимость заказа 
и т.п.
 
именно через сводные таблицы, а не модификацией текущих четырех таблиц.
 Заранее большое спасибо.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика