Что такое пивот в экселе. Создание Excel Pivot Tables

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

Это довольно компактная совокупность данных, но вообразите, как бы выглядел подобный список, если в нем представить пару десятков исполнителей проекта и несколько сотен рисков! Было бы весьма непросто даже прочитать такой список, не говоря уже о возможности проанализировать его и сделать на этом основании какие-то выводы.

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

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

Теперь, когда данные представлены в табличном формате, вы можете создать сводную таблицу, не забудьте купить клавиатуру для компьютера . Для начала установите табличный курсор в любой ячейке этой таблицы, затем щелкните на значке PivotTable (Сводная таблица) вкладки Insert (Вставка) и в появившемся меню выберите команду PivotTable (Сводная таблица).

На экране появится диалоговое окно Create Pivot Table (Создание сводной таблицы), показанное на рис. 4. В этом диалоговом окне следует указать программе, на основе каких данных будет построена сводная таблица — рабочего листа текущей рабочей книги или внешних данных (например, SQL Server). Обратите внимание, что в поле Table/Range (Таблица или диапазон) мы оставили заданное по умолчанию значение — Table1 (Таблица1).

Щелкните на кнопке ОК. Программа немедленно создаст макет сводной таблицы па новом рабочем листе (рис. 5), в правой части которого расположена панель Pivot Table Field List (Список полей сводной таблицы). Обратите внимание, что в верхней части этой панели перечислены названия всех полей созданной нами таблицы реестра рисков.

Названия полей, перечисленные на панели Pivot Table Field List , представляют собой названия заголовков столбцов, взятые из нашей таблицы. Области макета сводной таблицы предназначены для различного отображения данных. Их можно представлять как некую трехаспектную палитру. Допустим, нам требуется узнать количество рисков по каждой категории. Например, сколько внешних рисков у нашего проекта? Начните с перетаскивания поля Risk Category (Категория риска), как показано на рис. 6, в область Drop Row Fields Here (Перетащите сюда поля строк). (Местоположение этой области показано на рис. 5).

Как видите, название поля Risk Category (Категория риска) появилось в области Row Labels (Названия строк), которая расположена в нижней части панели Pivot Table Field List (Список полей сводной таблицы). Теперь перетащите поле Risk Name (Название риска) в область макета сводной таблицы Drop Data Items Here (Перетащите сюда элементы данных), как показано на рис. 7.

Обратите внимание, что в нижней части созданной нами сводной таблицы программа Excel автоматически добавила строку с заголовком Grand Total (Общий итог), в которой отображено общее количество названий рисков по отдельным категориям. Теперь нетрудно заметить, что, например, категория «Связанные с решением кадровых вопросов» (поле Organizational) содержит два риска, категория «Технические» (поле Technical) - четыре и т.д. В последней строке - Grand Total (Общий итог) - указано общее количество рисков (11) по всем категориям.

Как вы, должно быть, заметили, в области Values (Значения), расположенной в нижней части панели Pivot Table Field List (Список полей сводной таблицы), появился элемент Count of Risk Name (Количество по полю Risk Name), т.е. суммарное количество рисков (см. рис. 7). Существует множество способов отображения, представления и подсчета данных в сводных таблицах Excel. Если хотите увидеть результаты и подсчеты, которые сделает для вас Excel, поэкспериментируйте с перемещением полей из списка панели Pivot Table Field List (Список полей сводной таблицы) в разные области макета сводной таблицы.

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

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

В этой статье мы хотим познакомить Вас, уважаемые читатели, с инструментом анализа данных в пакете MS Excel 2007 под названием "сводные таблицы ". Данная статья открывает мини-цикл статей, посвященных вопросам - что такое сводные таблицы, как их создать, как правильно форматировать и изменять сводные таблицы, как с помощью сводных таблиц анализировать данные.

Что же такое сводные таблицы, и зачем они нужны? Мы часто сталкиваемся с ситуациями, когда у нас есть много разнообразных данных (которые можно назвать статистическими), но нас интересуют какие-то общие выводы или промежуточные итоги.

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

Все эти данные мы свели в одну таблицу, которую Вы можете увидеть ниже.

За 17 дней продаж у нас получилась большая таблица на 350 записей. Но эта таблица не решает наших проблем. Нам необходимо узнать объемы продаж в денежном и количественном выражении по датам и по отдельным магазинам, но как это сделать? Сортировать таблицу и суммировать отдельные её части? Это требует времени, а завтра поступят новые данные, и всю работу нужно будет снова повторить.

Вот тут нам может помочь сводная таблица. С помощью простого диалогового окна мы создаём нашу первую сводную таблицу. В этой таблице мы группируем данные по столбцам Дата и Точка продажи , а так же указываем, что нужно суммировать данные из столбцов Объем продаж, шт. и Сумма выручки .

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

Таблица сразу же отобразит нужные нам результаты:

Этот пример наглядно демонстрирует преимущества сводных таблиц, к которым относятся:

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

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

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

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

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

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

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

Чтобы создать сводную таблицу из этого диапазона данных, выполните следующие действия -

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

Назовите диапазон данных как SalesData_Range.

Нажмите на область данных - SalesData_Range.

В Создание сводной таблицы диалоговое окно, под Choose the data that you want to analyze , Choose the data that you want to analyze , вы можете либо выбрать таблицу или диапазон от текущей книги или использовать внешний источник данных.

Как вы создаете сводную таблицу из диапазона данных, выберите следующий из диалогового окна -

Выберите Select a table or range .

В поле Table / Range, введите имя диапазона - SalesData_Range.

Выберите New Worksheet под Выберите, где вы хотите, чтобы отчет сводной таблицы для размещения и нажмите кнопку OK.

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

Новый лист вставляется в книгу. Новый рабочий лист содержит пустую сводную таблицу. Назовите рабочий лист - Range-сводную таблицу.

Как вы можете наблюдать, то PivotTable Fields список на правой стороне листа появляется, содержащее имена заголовков столбцов в диапазоне данных. Кроме того, на ленте, PivotTable Tools - Анализировать и КОНСТРУКЦИЯ появляются.

Добавление полей в сводную таблицу

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

Предположим, вы хотите, чтобы суммировать количество заказа SALESPERSON-накрест за месяцы январь, февраль и март. Вы можете сделать это в несколько простых шагов следующим образом -

Нажмите на поле в менеджере по продажам списка сводных таблиц Поля и перетащите его в области строк.

Нажмите на поле Месяц в списке полей сводной таблицы и перетащите, что также в области ROWS.

Нажмите на количестве заказа и перетащите его в области Й ЗНАЧЕНИЙ.

Ваш первый PivotTable готов, как показано ниже

Заметим, что две колонны появляются в сводной таблице, одна из которых содержит меток строк, которые вы выбрали, т.е. SALESPERSON и месяц, и второй, содержащий сумма заказа количества. В дополнении к Сумме заказа Сумма месяца мудрого для каждого продавца, вы также получите подытоги представляющий общие объем продаж от этого человека. Если прокрутить таблицу, вы найдете последнюю строку как Grand Total, представляющий общий объем продаж.

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

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

Рассмотрим следующую таблицу Excel, которая содержит те же данные о продажах, как и в предыдущем разделе -

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

Чтобы создать сводную таблицу из таблицы Excel, выполните следующие действия -

Нажмите на стол - SalesData_Table.

Перейдите на вкладку ВСТАВИТЬ на ленте.

Нажмите сводную таблицу в группе Таблицы. Create PivotTable появляется диалоговое окно.

Нажмите Выбрать таблицу или диапазон.

В поле Table / Range, введите имя таблицы - SalesData_Table.

Выберите New Worksheet под Choose where you want the PivotTable report to be placed , Choose where you want the PivotTable report to be placed . Нажмите кнопку ОК.

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

Вы можете добавлять поля в сводную таблицу, как вы видели в разделе - Добавление полей в сводную таблицу, ранее в этой главе.

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

Для создания сводной таблицы из таблицы Excel SalesData-таблицы, используя рекомендуемый PivotTables, действуйте следующим образом: -

Нажмите на стол SalesData-Table.

Перейдите на вкладку INSERT.

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

Нажмите на сводной таблице - Сумма заказа Суммы по продавцу и месяц, и нажмите кнопку ОК.

Вы будете получать предварительный просмотр на правой стороне.

на новом рабочем листе в вашей книге появится выбранное сводной таблицы.

Вы можете видеть, что PivotTable Fields - менеджер по продажам, Регион, сумма заказа и месяц было выбраны. Из них область и Salesperson в области рядов, Месяц в области COLUMNS и Сумма заказа Суммы в Σ ЗНАЧЕНИЯ области.

Сводная таблица суммированы данные Регион-мудрый, Salesperson мудрый и месяц-накрест. Промежуточные итоги отображаются для каждого региона, каждого менеджера по продажам, и каждый месяц.

Цель работы: научиться создавать и применять сводные таблицы

при работе с данными

1 Мастер сводных таблиц

2 Построение макета сводной таблицы

3 Работа со сводной таблицей

Мастер сводных таблиц

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

Пример 1 Дан список "Экзаменационная ведомость" (рис.11.1).

Рисунок 11.1 Список "Экзаменационная ведомость"

Требуется построить сводную таблицу, в которой строками являются № группы, Фамилия И.О. и № зачётной книжки, а столбцами – Коды преподавателей. В ячейках на их пересечении нужно расположить оценки, которые каждый студент получил у принимавшего экзамен преподавателя и вычислить средний балл для каждого преподавателя.

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

В окне Создание сводной таблицы (рис. 11.2) нужно ввести следующую информацию:

1Указание вида источника сводной таблицы:

использование списка (базы данных Excel);

использование внешнего источника данных;

использование нескольких диапазонов консолидации;

использование данных из другой сводной таблицы.

Рисунок 11.2 Составление сводной таблицы

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

2Указание диапазона ячеек, содержащего исходные данные. Список (база данных Ехсеl) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде:

имя_книги] имя_листа! диапазон ячеек

(параметр в квадратных скобках необязателен)

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

3 Выбор места расположения и параметров сводной таблицы.

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

2 Построение макета сводной таблицы.

После нажатия кнопки ОК будет сформирована сводная таблица со стандартным именем СводнаяТаблица1 и список её полей (рис. 11.3). Если список полей не вывелся, нужно щелкнуть правой мышью по имени СводнаяТаблица1 и выбрать команду Показать список полей.

Структура сводной таблицы состоит из следующих полей и областей, определяемых в макете (рис.11.3):

Поля для добавления в отчёт – для размещения полей, значения которых обеспечивают отбор записей в сводную таблицу. На странице может быть размещено несколько полей, между которыми устанавливается иерархия связи - сверху вниз, определять страницу необязательно. Значок списка в правом верхнем углу позволяет задать расположение полей и областей в столбик (как на рис. 11.3), рядом, только поля, только области и др.

Рисунок 11.3 Макет отчёта сводной таблицы

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

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

Область Названия строк - поля размещаются сверху вниз, обеспечивая

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

Область ∑ Значения по умолчанию появляется вариант Сумма по полю Оценка. При необходимости использования другой функции в качестве подведения итогов свода нужно щелкнуть мышью по строке Сумма по полю Оценка и в раскрывающемся меню выбрать опцию Параметры полей значений. В результате появится окно (рис. 11.4), во вкладке Операция можно выбрать одну из 12 функций (количество, среднее, максимум, произведение, …, дисперсия), во вкладке Дополнительные вычисления – задать доли, нарастающий итог и пр. – см. табл. 1. При использовании функции сравнения (Отличие, Доля, Приведенное отличие) выбирается Поле и Элемент, с которым будет производиться сравнение. Список Поле содержит поля сводной таблицы, с которым связаны базовые данные для пользовательского вычисления. Список Элемент содержит значения поля, участвующего в пользовательском вычислении.

Таблица 1Виды дополнительных функций в полях области данных

В окне можно также задать числовой формат – общий, денежный, процентный и др.

Рисунок 11.4 Окно Параметры полей значений

Для выполнения задания примера 1 в окне списка полей для добавления в отчёт нужно поставить галочки во всех полях, затем перетащить мышью поля № группы, Фамилия И.О. и № зач. кн. в раздел Названия строк, поле Код преп – в раздел Названия столбцов, а поле Оценки – в Фильтр отчёта. В области ∑ Значения по умолчанию стоит вариант Сумма по полю Оценка, поэтому нужно щелкнуть мышью по этой строке и в появившемся окне во вкладке Операции заменить Сумма на Среднее (рис. 11.4).

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

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

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

В областях строка или столбец каждое поле размещается только один раз. Поля, помещённые в область для столбцов, остаются столбцами, а поля, помещённые в область для строк, разворачиваются в строку. На их пересечении формируются группы и получаются итоговые (сводные) значения в области для данных- группировочные поля. В области Фильтр отчёта могут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данных. Для каждого такого поля в области ∑ Значения задается вид функции и выполняется необходимая настройка.

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

Рисунок 11.5 Исходная таблица, сводная таблица и список её полей.

После завершения построения сводной таблицы могут измениться исходные данные. Для обновления данных сводкой таблицы в этом случае следует выполнить командуДанные\ Обновить все .

Для изменения структуры уже построенной сводной таблицы курсор устанавливается в область сводной таблицы, повторно выполняется команда Вставка\ Сводная таблица .

Контрольные вопросы

1 Что называется сводной таблицей, сводом?

2 Для чего предназначены сводные таблицы?

3 Что помещается в областях для строк, столбцов, данных макета?

4 Как задать в сводной таблице вид вычислительного итога Сумма, Максимум, Произведение?

5 Как обновить данные в сводной таблице?

6 Как изменить структуру сводной таблицы (добавить или изменить поля строк, столбцов, данных?

Задание

Для таблицы своего варианта из лабораторной работы №9 "Списки в MS Excel. Сортировка и фильтрация данных" построить две сводные таблицы. Поля, помещаемые в области строк, столбцов и данных выбрать самостоятельно.

1 Название работы

2 Цель работы

4 Задание и своды (на носителе) – две таблицы.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

Лабораторная работа №12