Расширенные сводные таблицы: объединение данных из нескольких листов

Что вы будете созодать, если необходимо сделать сводную таблицу, а ваши данные на различных листах? С установленным Excel 2013 у вас есть для этого обычный метод. Существует разработка, именуемая Data Model и она употребляет дела данных так, как это делает база данных.

В этом уроке я покажу для вас всё для сотворения сводной таблицы в Excel 2013 из данных на нескольких листах, используя Data Model.

Видеоролик

Если вы желаете следовать уроку, используя свой файл Excel, сможете так и сделать. Либо загрузите zip-файл к этому уроку, в котором содержится эталон книжки Pivot Consolidate.xlsx.

Исследование данных

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

Нажмите на лист Customer Info и удостоверьтесь, что в нём содержатся номера заказов, также заглавие и состояние клиентов.

Customer Info лист

Нажмите на лист Order Info и поглядите, что в нём содержатся номера заказов, также поля в месяц, заказанные продукты и то, являются ли эти продукты органическими.

Order Info лист

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

Payment Info лист

Соединив все эти листы в области задач сводной таблицы, мы можем избрать данные из всякого листа. Так как номера заказов есть на всех трёх листах, они станут точками подключения. Это то, что база данных вызывает primary key. Направьте внимание: не непременно иметь primary key, но он уменьшает возможность ошибки.

Создание именованных таблиц

Перед созданием сводной таблицы давайте сделаем таблицу из всякого листа.

Нажмите назад в Customer Table, потом щёлкните в любом месте снутри области данных. Перейдите на вкладку Insert на панели ленты и щёлкните значок Table.

Преобразуйте данные на листе, выбрав Insert > Table

Диалоговое окно Create Table верно описывает область таблицы. Флаг понизу должен также идентифицировать, что 1-ая строчка таблицы создана для заголовков. (Если нет, изберите этот вариант.)

Диалог Create Table должен верно угадывать область данных

Нажмите OK, и сейчас у вас есть таблица с чередующимися штрихами и клавишами фильтра. Вы сможете щёлкнуть снутри, чтоб снять выделение, если желаете лучше разглядеть её (просто не жмите вне таблицы). На панели ленты также отображается вкладка Design для таблицы. На левой стороне ленты в поле Table Name отображается временное имя Table1. Удалите это и назовите его Customer_Info (используйте знак подчёркивания заместо пробела). Нажмите Enter.

Применить имя к каждой таблице

Повторите эти деяния с листами Order Info и Payment Info. Назовите таблицы Order_Info и Payment_Info.

Сейчас мы готовы вставить PivotTable.

Вставка PivotTable

Удостоверьтесь, что на листе Payment Info курсор находится кое-где в таблице. Вернитесь на вкладку Insert ленты и щёлкните значок PivotTable (это самый 1-ый значок).

При помощи курсора снутри одной из таблиц изберите Insert > PivotTable

В показавшемся диалоговом окне нужно верно найти таблицу и избрать, чтоб PivotTable перебежала на новейший рабочий лист. Понизу установите флаг Add this data to the Data Model. Нажмите OK.

Добавление данных в Data Model — это то, что дозволяет соединениям работать

Сейчас у вас будет PivotTable на новеньком листе, в правой части экрана будет панель задач, а на ленте покажется вкладка Analyze.

На панели задач отображается таблица и поля лишь активного листа, потому нажмите ALL, чтоб узреть все сделанные вами таблицы. Но до этого чем мы сможем их применять, мы должны связать их вместе, а это значит создание отношений. Нажмите клавишу Relationships на панели ленты.

Настройка отношений таблиц

Нажатие данной для нас клавиши показывает диалоговое окно Manage Relationships. Нажмите клавишу New и покажется диалоговое окно Create Relationship . Мы сделаем два дела, используя поле Order # в качестве соединителя.

В раскрывающихся перечнях изберите Payment_Info для таблицы, а рядом с ним изберите Order # в раскрывающемся перечне Column. Во 2-ой строке изберите Customer_Info из раскрывающегося перечня Related Table, а рядом с ней изберите Order # из раскрывающегося перечня Related Column.

Есть три таблицы, потому сделайте два дела

Это значит, что таблицы Payment_Info и Customer_Info соединены меж собой по совпадению номера заказов.

Нажмите клавишу OK и мы увидим эти дела, перечисленные в окне Manage Relationships.

Повторите этот процесс, чтоб сделать связь меж Payment_Info и Order_Info, также используя поле Order #. Сейчас Manage Relationships окно смотрится так:

В диалоговом окне Manage Relationships показываются дела, которые вы создаёте

Направьте внимание, что нет необходимости создавать дела меж таблицами Order_Info и Customer_Info, так как они автоматом соединяются через таблицу Payment_Info.

Нажмите клавишу Close в нижней части окна. Сейчас мы можем, в конце концов, перетащить поля в PivotTable.

Вставка полей в PivotTable

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

• State и Month в строчки
• Product в колонки
• $ Sale в значения
• Status в фильтры

Сейчас вы сможете применять и изменять её, как и всякую другую PivotTable.

Заключение

Используя новейшую функцию Object Data Model в Excel 2013, вы сможете избрать розовые поля из нескольких листов для сотворения единой PivotTable. Имейте в виду, что строчки каждой таблицы должны быть каким-то образом соединены вместе. У вас больше шансов на фуррор, когда таблицы имеют общее поле с неповторимыми значениями.

Если вы ищете отличные методы представления собственных данных, Envato Market имеет неплохой выбор Excel and PowerPoint templates, также scripts and apps для преобразования данных Excel в веб-форматы и напротив.

Создание сводной таблицы Excel из нескольких листов

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

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

Сводная таблица в Excel

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

Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтоб отыскать величину продаж по любому отделу, придется посчитать вручную на калькуляторе. Или сделать еще одну таблицу Excel, где средством формул показать итоги. Таковыми способами рассматривать информацию непроизводительно. Недолго и ошибиться.

Самое рациональное решение – это создание сводной таблицы в Excel:

1. Выделяем ячейку А1, чтоб Excel знал, с какой информацией придется работать.
2. В меню «Вставка» избираем «Сводная таблица».
3. Раскроется меню «Создание сводной таблицы», где избираем спектр и указываем пространство. Так как мы установили курсор в ячейку с данными, поле спектра заполнится автоматом. Если курсор стоит в пустой ячейке, нужно прописать спектр вручную. Сводную таблицу можно сделать на этом же листе либо на другом. Если мы желаем, чтоб сводные данные были на имеющейся страничке, не запамятовывайте указывать для их пространство. На страничке возникает последующая форма:
4. Сформируем табличку, которая покажет сумму продаж по отделам. В перечне полей сводной таблицы избираем наименования столбцов, которые нас заинтересовывают. Получаем итоги по любому отделу.

Просто, стремительно и отменно.

• 1-ая строчка данного для сведения данных спектра обязана быть заполнена.
• В базисной табличке любой столбец обязан иметь собственный заголовок – проще настроить сводный отчет.
• В Excel в качестве источника инфы можно применять таблицы Access, SQL Server и др.

Как сделать сводную таблицу из нескольких таблиц

Нередко требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Необходимо соединить их в одну общую. Для науки придумаем остатки на складах в 2-ух магазинах.

Порядок сотворения сводной таблицы из нескольких листов таковой же.

Сделаем отчет при помощи мастера сводных таблиц:

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем клавишу опции панели резвого доступа и жмем «Остальные команды». Тут на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель резвого доступа. Опосля прибавления:
2. Ставим курсор на первую табличку и жмем инструмент «Мастера». В открывшемся окне отмечаем, что сделать таблицу желаем в «нескольких спектрах консолидации». Другими словами нам необходимо соединить несколько мест с информацией. Вид отчета – «сводная таблица». «Дальше».
3. Последующий шаг – «сделать поля». «Дальше».
4. Прописываем спектр данных, по которым будем сформировывать сводный отчет. Выделяем 1-ый спектр совместно с шапкой – «добавить». 2-ой спектр совместно с заглавием столбцов – опять «добавить».
5. Сейчас в перечне избираем 1-ый спектр. Ставим птичку у единицы. Это 1-ое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем 2-ой спектр данных – ставим птичку у числа «2». Заглавие поля – «Магазин 2». Жмем «Дальше».
6. Избираем, где расположить сводную таблицу. На существующем листе либо новеньком. Лучше избрать новейший лист, чтоб не было наложений и смещений. У нас вышло так:

Видите ли всего в несколько кликов можно создавать сложные отчеты из нескольких листов либо таблиц различного размера инфы.

Как работать со сводными таблицами в Excel

Начнем с простого: прибавления и удаления столбцов. Для примера разглядим сводную табличку продаж по различным отделам (см. выше).

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

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

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой клавишей мыши по полю «Дата». Жмем «Группировать». Избираем «по месяцам». Выходит сводная таблица такового вида:

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

А вот таковой отчет можно сделать, если перетащить поля меж различными областями :

Чтоб заглавие строчки сделать заглавием столбца, избираем это заглавие, щелкаем по всплывающему меню. Жмем «переместить в заглавие столбцов». Таковым методом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями продуктов. Воспользовавшись разделом меню «переместить в начало».

Покажем детали по определенному продукту . На примере 2-ой сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой клавишей мыши – «развернуть».

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

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

Проверка корректности выставленных коммунальных счетов

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

1-ый столбец = первому столбцу из сводной таблицы. 2-ой – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

Наши формулы ссылаются на лист, где размещена сводная таблица с тарифами.

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

Exceltip

Блог о программке Microsoft Excel: приемы, хитрости, секреты, трюки

Создание перечня из сводной таблицы

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

На рисунке показан принцип, который я обрисовал. Т.е. в спектре A2:E5 находится начальная сводная таблица, которая преобразуется в перечень данных (спектр H2:J14). 2-ая таблица представляет этот же набор данных, лишь в другом ракурсе. Каждое значение начальной сводной таблицы смотрится в виде строчки, состоящее из пт поля строчки, поля столбца и соответственного им значения. Такое отображение данных бывает полезно, когда нужно отсортировать и манипулировать данными иными методами.

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

Перейдите по вкладке Файл -> Характеристики. В показавшемся диалоговом окне Характеристики Excel, во вкладке Панель резвого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.

Сейчас на панели резвого доступа у вас возник новейший значок.

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

На первом шаге мастера нужно избрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких спектрах консолидации и нажимаем Дальше.

На шаге 2а укажите, как следует создавать поля странички. Расположите переключатель Сделать поля странички -> Дальше.

На шаге 2б, в поле Спектр изберите спектр, содержащий данные, и щелкните Добавить. В нашем случае это будет положение начальной сводной таблицы A1:E4.

На 3-ем шаге нужно обусловиться, куда нужно поместить сводную таблицу, и нажмите клавишу Готово.

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

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

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

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

Для вас также могут быть увлекательны последующие статьи

7 объяснений

Спасибо огромное за хорошую идею преобразования «плоских» таблиц в обычные
На форумах, обычно, для этого пишут макросы (дают применять макросы)

Спасибо за идею! Весьма нужная статья!

Хороший денек!
может быть ли схожее преобразование в случае, если переменных не две, а три: столбец есть верхний и есть нижний заглавия (друг под другом), необходимы оба позже в перечне в поле напротив соответственной строчки. Спасибо заблаговременно.