ЛАБОРАТОРНАЯ РАБОТА № 8
Создание сводных таблиц
Цель работы: научиться создавать генерируемые отчеты на основе сводных таблиц.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Одним из мощных инструментов анализа и обработки данных являются сводные таблицы, с помощью которых можно быстро и легко обобщить большие объемы данных, представляя их в разных разрезах.
По существу, сводная таблица представляет собой отчет, генерируемый на основе других существующих таблиц, списков или внешней базы данных. При этом исходная информация может находиться как на рабочем листе, так и во внешнем файле. Данный отчет содержит цифры, агрегированные в различных разрезах.
Отчеты сводных таблиц допускают различные варианты форматирования, сортировки, фильтрации, группировки данных, получение различных итогов с любой желаемой степенью детализации. Пользователь может легко управлять столбцами и строками, перемещая их из одной области в другую, фильтровать значения в области данных, что позволяет оперативно анализировать данные в различных разрезах.
Лучшим способом ознакомления со сводными таблицами является наглядное рассмотрение технологии их создания, а также методов и средств реорганизации сводных таблиц, т.е. добавление, удаление, переупорядочивание полей и элементов таблицы, вставка специальных формул для новых вычислений и подведения итогов.
ПРАКТИКУМ
Задача 1. Создать сводную таблицу на примере анализа учета кредитных договоров. Имеются данные о заключенных кредитных договорах юридических и физических лиц по нескольким филиалам банка (рисунок 25).
Алгоритм решения задачи
Данная таблица достаточно объемна, содержит большое количество записей, поэтому провести анализ по имеющейся информации сложно. Использование технологии сводных таблиц позволяет агрегировать информацию в различных разрезах, а также интерактивно управлять вычислениями и формой отчета, используя различные функции и разную степень детализации информации.
Рисунок – Фрагмент таблицы кредитных договоров банка с юридическими и физическими лицами
Прежде чем углубиться и процесс создания сводных таблиц, важно разобраться с данными, которые подходят для этих таблиц. Данные, по которым подводятся итоги, должны быть представлены в виде базы данных. База данных может находиться на рабочем листе (такую базу данных называют табличной или списком) либо в файле внешней базы данных. Хотя Excel может преобразовать любую базу данных в сводную таблицу, подобная операция будет иметь смысл не для любой базы данных.
Поля в табличных базах данных могут относиться к одному из двух типов.
Данные. В полях этого типа содержатся значения (в нашем примере это поля «Срок (дн.)», «Сумма кредита», «% ставка», в которых содержатся данные по каждому договору).
Категория. Поля этого типа описывают данные (это поля «№ договора», «Клиент», «Статус», «Валюта», «Филиал» и «Отрасль»).
Простая табличная база данных может иметь любое количество полей данных и любое количество полей категорий. При создании сводной таблицы обычно необходимо подвести итог по одному или нескольким полям данных. Что касается значений в полях категорий, то они появляются в сводной таблице в виде полей строк, столбцов или страниц.
Однако можно создать полезные сводные таблицы даже для баз данных, не содержащих числовых полей данных. Например, можно создать сводную таблицу, которая будет считать количество полей, а не суммировать их значения.
Для нашего примера применение сводных таблиц позволит получить ответы на следующие вопросы:
сколько заключено договоров в каждом филиале банка и какова доля каждого филиала в общем объеме?
чему равна общая сумма кредитования юридических и физических лиц в каждом филиале?
в каком филиале было заключено наибольшее количество договоров по каждому типу и в какой валюте?
как распределены кредитные договоры по срокам?
предприятиям какой отрасли выдается большее количество кредитов в каждом из филиалов?
Создание сводной таблицы осуществляется выбором команды на ленте Вставка → Таблицы →Сводная таблица.
В результате Excel отобразит диалоговое окно Создание сводной таблицы, показанное на рисунке 26.
Рисунок – Диалоговое окно
Создание сводной таблицы
Если вы создаете сводную таблицу на основе данных из внешней базы данных, то в диалоговом окне Создание сводной таблицы установите переключатель Использовать внешний источник данных, а затем щелкните на кнопке Выбрать подключение.
В рассматриваемом примере анализируемые данные хранятся в базе данных рабочего листа, поэтому оставляем установленный по умолчанию переключатель Выбрать таблицу или диапазон.
Рисунок – Панель Список полей
сводной таблицы
В нижней части диалогового окна Создание сводной таблицы задается местоположение сводной таблицы. По умолчанию она создастся на новом листе, но можно указать любой диапазон на любом рабочем листе, включая лист, содержащий исходные данные. При этом в поле ввода необходимо указать левую верхнюю ячейку диапазона, куда должна быть помещена сводная таблица.
Щелкните на кнопке ОК, и Excel создаст пустую сводную таблицу и отобразит панель Список полей сводной таблицы, как показано на рисунке 27.
Панель Список полей сводной таблицы обычно прикреплена к правой стороне окна Excel. Но, перетаскивая заголовок панели, можно переместить панель в любое место окна. Если щелкнуть на любой ячейке вне диапазона сводной таблицы, то панель исчезнет с экрана.
Далее необходимо наполнить содержанием макет сводной таблицы. Это можно сделать несколькими способами:
– Перетащите имя поля из верхнего списка в одно из полей панели Список полей сводной таблицы.
– Щелкните правой кнопкой мыши на имени поля и выберите в открывшемся меню область его местоположения.
В предыдущих версиях Excel можно было перетаскивать имена полей непосредственно в области сводной таблицы. Такой способ заполнения макета сводной таблицы остался и в Excel 2007, но теперь он по умолчанию отключен. Чтобы активизировать его, выберите Работа со сводными таблицами → Параметры → Сводная таблица → Параметры. В открывшемся диалоговом окне Параметры сводной таблицы перейдите на вкладку Вывод и установите флажок Классический макет сводной таблицы (разрешено перетаскивание полей).
Формирование структуры сводной таблицы осуществляется путем перетаскивания полей базы данных на соответствующие области макета. При формировании необязательно использовать все поля. Важно разместить на макете только те из них, которые позволяют получать искомые итоговые данные. Неиспользованные поля базы данных в сводной таблице не отображаются. При необходимости данные можно перемещать из одной области в другую. Если на макет они были занесены ошибочно, их можно «сбросить».
Поле «Сумма кредита» перемещено в область Значения. Теперь сводная таблица будет подсчитывать сумму всех значений в поле «Сумма кредита». При перетаскивании полей в область Значения к данным применяются разные функции. Общий перечень функций точно такой же, как и при использовании средства Промежуточные итоги. По умолчанию устанавливается функция суммирования для числовых данных и функция счета для нечисловых значений.
Одно и то же поле можно многократно разместить в области Значения, задав для него разные виды итогов с помощью команды Параметры полей значений из динамического меню. Вкладка Операция используется для выбора стандартной вычисляющей функции. Вычисленные значения в сводной таблице можно отображать в различной форме с применением дополнительных вычислений. Форма представления значений выбирается в раскрывающемся списке Дополнительные вычисления одноименной вкладки диалогового окна Параметры поля значений.
Поля «Филиал» и «Статус» перемещены в область Названия строк. Значения этих полей будут заголовками строк сводной таблицы.
Поле «Валюта» перемещено в область Названия столбцов. Значения поля «Валюта» будут заголовками столбцов сводной таблицы.
Поле «Отрасль» перемещено в область Фильтр отчета. Значения поля «Отрасль» будут заголовками страниц.
Полученная в результате этих действий сводная таблица, а также области панели Список полей сводной таблицы показаны на рисунке 28.
Рисунок 28 – Фрагмент окна сформированной сводной таблицы
Как видно, поля, которые были помещены в области Фильтр отчета, Названия строк и Названия столбцов, отображаются в виде раскрывающихся списков. Так, для элемента области Фильтр отчета «Отрасль» можно выбрать в списке параметр с именем «Все», что обеспечит вывод сведений по всем отраслям базы данных (в нашем примере: для юридических лиц – медицина, промышленность, торговля, для физических лиц – предприниматель), а можно указать только определенные параметры, что обеспечит вывод соответствующих данных.
Для вывода информации только о клиентах – юридических лицах следует раскрыть в сводной таблице список поля «Статус» и указать флажок детализации только рядом со значением «Ю» (так в базе данных закодированы юридические лица).
Если табличный курсор находится в пределах сводной таблицы, на экране отображаются две дополнительные ленты Параметры и Конструктор, которые объединены в общую вкладку Работа со сводными таблицами.
Построив сводную таблицу, можно различными способами проанализировать ее данные. Для этого надо изменить состав элементов каждого поля, создать новое вычисляемое поле, новый элемент поля.
В связи с тем, что сводная таблица представляет собой особый тип диапазона данных, в нее нельзя вставлять строки или столбцы, содержащие выражения, элементами которых являются данные этой таблицы. Для этой цели можно использовать механизм создания вычисляемых полей. Вычисляемое поле состоит из вычислений, в которых использованы другие поля сводной таблицы. Оно должно находиться только в области данных сводной таблицы и не может использоваться в области страницы, строки или столбца.
В качестве примера приведем механизм формирования вычисляемого поля для расчета платы банку за издержки при оформлении кредита. Пусть данная величина составляет 0,5 % от суммы кредита.
Схема создания вычисляемого поля представлена следующим образом:
Рисунок 29 – Диалоговое окно
Вставка вычисляемого поля
установить курсор в любую ячейку сводной таблицы;
Выберите команду Работа со сводными таблицами → Параметры → Сервис → Формулы → Вычисляемое поле. Откроется диалоговое окно Вставка вычисляемого поля(рисунок 29);
в поле Имяввести название вычисляемого поля (Плата банку за издержки);
в поле Формулаввести формулу расчета нового поля (в примере: =Сумма кредита*0,5%). Выражение формулы может содержать поля сводной таблицы, встроенные функции и константы, связанные знаками операций. Для вставки в выражение полей сводной таблицы следует в списке Полявыделить требуемое поле, и выполнить щелчок мышью по кнопке Добавить поле;
для добавления созданного поля в сводную таблицу выполнить щелчок по кнопке Добавить;
для закрытия диалогового окна выполнить щелчок по кнопке ОК.
Сводная таблица после добавления в нее вычисляемого поля приобретает следующий вид (рисунок 30).
Рисунок 30 – Фрагмент сводной таблицы с вычисляемым полем
Excel также позволяет создать для поля сводной таблицы вычисляемый элемент. Такой элемент использует содержимое других элементов в пределах поля сводной таблицы. Вычисляемый элемент должен находиться в области страницы, строки или столбца и не может использоваться в области данных.
Приведем механизм формирования вычисляемого элемента, отображающего сумму кредита по Московскому региону.
Для создания вычисляемого элемента следует выполнить такие действия:
поместить курсор на то поле, которое будет содержать новый вычисляемый элемент. В нашем примере таким полем будет поле Филиал;
Выберите команду Работа со сводными таблицами → Параметры → Сервис → Формулы → Вычисляемый объект. Появится диалоговое окно Вставка вычисляемого элемента(рисунок 31);
Рисунок 31 – Диалоговое окно
Вставка вычисляемого элемента
в поле Имя укажите название нового элемента, в поле Формула введите формулу расчета нового элемента. В нашем примере имя нового объекта – Итоги по Московскому региону, а формула имеет следующий вид: = Перово + Сокол + Фили;
для добавления созданного нового элемента в поле сводной таблицы щелкните по кнопке Добавить;
для закрытия диалогового окна щелкните по кнопке ОК.
Сводная таблица после добавления вычисляемого объекта приведена на рисунке 32.
Рисунок 32 – Сводная таблица с вычисляемым полем и вычисляемым элементом
|
Скачать 1.61 Mb.