ЛАБОРАТОРНАЯ РАБОТА № 28

ТЕМА ЗАНЯТИЯ: Использование сводных таблиц Excel

УЧЕБНЫЕ ВОПРОСЫ:

  1. Создание сводных таблиц и диаграмм

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

КОНТРОЛЬНЫЕ ВОПРОСЫ:

1. В каких случаях используют макрос?

2. Какие существуют способы запуска макросов?

3. Какой объект в Excel называется связанным?

Основные действия и приемы работы при выполнении лабораторной работы

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

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

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

Пример простого отчета сводной таблицы показан на Рис. 28.1, где исходные данные отображаются в списке слева.


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

Сводная таблица - особый объект. Для него нельзя обычным способом:

· вставлять и удалять строки, столбцы, ячейки,



· изменять или перемещать ячейки.

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

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

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

Исходные данные в списках Excel и большинстве баз данных организованы в строки и столбцы (рис. 28.2.). Используемые исходные данные должны содержать в каждом столбце информацию одного типа. В приведенном примере регион продаж всегда отображается в столбце E, объем продаж - в столбце D и так далее.

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


Отчет сводной диаграммы всегда связан с отчетом сводной таблицы. Оба типа отчетов имеют соответствующие друг другу поля. Изменение положения поля в одном отчете вызывает изменение соответствующего поля в другом отчете. Поля строк в таблицах соответствуют полям категорий в диаграммах; поля столбцов в таблицах соответствуют полям рядов в диаграммах. Поля страниц и поля данных в таблицах соответствуют полям страниц и полям данных в диаграммах.

Приведенный пример (рис. 28.3) показывает соответствие между полями в каждом типе отчета.

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

Создать диаграмму на основе сводной таблицы очень просто. Переключитесь в режим Сводная диаграмма (PivotChart View), и на экране появится сводная диаграмма, примерно такая, как на рис. 8.50.

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

Рис. 8.50.

Но можно создать диаграмму и непосредственно на базе запроса или таблицы. При этом одновременно будет создаваться и сводная таблица. Сводная таблица и сводная диаграмма - это две формы представления одних и тех же данных.

В качестве примера предлагается построить сводную диаграмму для запроса "Продажи по сотрудникам и странам" (Employee Sales by Country).

  1. Откройте этот запрос в режиме Конструктора.
  2. Запрос имеет два параметра: [Начальная дата] и [Конечная дата], которые используются для фильтрации данных. Для сводной диаграммы эти параметры не нужны, поэтому сначала удалите выражение из строки Условие отбора (Criteria), затем откройте диалоговое окно Параметры (Query Parameters) (см. разд. "Запросы с параметрами" гл. 4) и удалите оба параметра.
  3. Щелкните по стрелке на кнопке Вид (View) панели инструментов и выберите из меню пункт Сводная диаграмма (PivotChart View). Появится окно, основную часть которого занимает область отображения диаграммы (рис. 8.51), ограниченная осями координат и размеченная линиями сетки. Кроме этого, видны область фильтра, которая играет ту же роль и расположена так же, как и в сводной таблице, область категорий и область рядов, которые соответствуют строкам и столбцам сводной таблицы. В область категорий переносятся поля, значения которых должны откладываться по оси X (горизонтальной), а в область рядов - поля, каждое значение которых соответствует одной серии точек или столбцов на диаграмме (в зависимости от типа диаграммы). Эти поля соответствуют полям столбцов на сводной диаграмме. В область данных помещаются поля, значения которых будут отображаться по оси Y (вертикальной) диаграммы.

Рис. 8.51.

  1. Перетащите из списка полей в область фильтра поле "Страна" (Country), в область категорий - поля "Фамилия" (Last Name) и "Имя" (First Name), в область рядов - поле "Дата исполнения по месяцам" (Shipped Date By Month). Следите, как будет меняться область диаграммы.[ Если вы не видите диалоговое окно со списком полей, щелкните по кнопке Список полей (Fields List) панели инструментов. ]
  2. Перенесите поле "СуммаПродаж" (Sale Amount) в область данных - и диаграмма готова. Нажмите кнопку Добавить легенду (Show Legend), чтобы отобразить легенду, после чего вы получите диаграмму, представленную на рис. 8.52.
  3. Можно еще ввести надписи у осей диаграммы. Щелкните по надписи Название оси (Axis Title) под осью X. Выведите на экран окно Свойства (Properties) и раскройте вкладку Формат (Format). Введите в поле Заголовок (Caption): Сотрудники. Аналогично введите надпись Объем продаж для оси Y.

Прочитав вышесказанное, можно решить, что создание сводной диаграммы - это сложное занятие. На самом деле это простая задача. Чтобы познакомиться с методом создания диаграммы для простой сводной таблицы, взгляните на сводную таблицу, показанную на рис. 6.1. Она содержит информацию о сумме продаж для каждого рынка сбыта.

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

Для начала расположите указатель в любом месте сводной таблицы и перейдите на вкладку ленты Вставка (Insert). В группе Диаграммы (Charts) этой вкладки ознакомьтесь с типами диаграмм, которые можно создавать на основе данных сводной таблицы. Выберите необходимый тип. Например, щелкните на кнопке Гистограмма (Column chart) и выберите первый двухмерный вариант гистограммы (рис. 6.2).

Как видно на рис. 6.3, после выбора типа диаграммы она появляется в окне программы.

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

Кстати, для изменения расположения уже созданной сводной диаграммы щелкните на ней правой кнопкой мыши (в области построения) и в контекстном меню выберите команду Переместить диаграмму (Move Chart). На экране появится диалоговое окно, в котором нужно указать новое расположение объекта диаграммы.

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

Обратите внимание: на диаграмме, показанной на рис. 6.4, не выводятся промежуточные итоги. При создании сводных диаграмм программа Excel полностью игнорирует ячейки с любыми итоговыми данными. Если в качестве фильтра поля страницы выбрать Направление деятельности, то он будет применяться не только к сводной таблице, но и к сводной диаграмме. Подобное поведение программы основывается на том, что сводная диаграмма и сводная таблица строятся на основе данных, взятых из одного кеша. Таким образом, если вы добавите в источник данных новую информацию, а затем обновите сводную таблицу, то сводная диаграмма будет обновлена автоматически.

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

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

Создание сводной диаграммы «с нуля»

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

Знакомство с панелью области фильтра сводной таблицы

В Excel 2003 на сводных диаграммах выводились все доступные в сводной таблице поля. На основе представленных полей пользователи могли изменять вид диаграммы и фильтровать данные в ней. В Excel 2007 список полей сводной таблицы был успешно удален из области сводной диаграммы. Таким образом, в этой версии программы сводные диаграммы перестали загромождаться сомнительными элементами интерфейса, а их вид мало чем отличается от вида обычных диаграмм. Вместо полей сводной таблицы в Excel 2007 для управления сводной диаграммой используется панель Область фильтра сводной таблицы (PivotChart Filter Pane). Эта панель применяется для фильтрации данных, на основе которых выводится сводная диаграмма.

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

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

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

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

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

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

    На вкладке Вставка в группе Диаграммы выберите необходимый тип диаграммы, например, простую гистограмму, и нажмите О K .

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

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

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

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

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

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

    На вкладке Вставка в группе Таблицы выбрать раздел Сводная таблица , а затем пункт Сводная диаграмма .

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

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

  1. Анализ данных с помощью таблиц данных

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

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

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

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

Таблица данных с одним параметром для одной функции

Принцип использования Таблицы данных состоит в следующем:

    задаются исходные данные задачи, в том числе начальное значение аргумента-параметра ;

    задается диапазон значений параметра (в виде столбца или строки);

    выделяется место для размещения значений функции от заданных значений аргумента (столбец справа или строка ниже);

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

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

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

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

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

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

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

Таблица данных с двумя параметрами

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

Для получения искомых значений функции выделяется блок таблицы, содержащий ячейку формулы и ячейки значений заданных диапазонов параметров. Затем открывается окно Таблица данных в списке команды Анализ "что если " в группе Работа с данными на вкладке Данные . В окне необходимо указать, в какую ячейку подставлять значения параметра из диапазона значений параметра по столбцам, а в какую - по строкам. Очевидно, что нужно указать ячейки, где находятся начальные значения изменяемого аргумента вычисляемой функции.

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

Сводная диаграмма в Excel похожа на сводную таблицу. Несколько отличаются режимы отображения. В сводную таблицу программа помещает подробные сведения. В диаграмму – итоги или сводки. Вместо строк и столбцов – ряды и категории. Каждый ряд имеет определенный цвет и представляет собой связанные точки данных. Категория – это одна точка с каждого ряда (на оси Х).

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

На разных листах одной книги находятся отчеты по продажам в нескольких магазинах:

Они имеют одинаковую структуру и одинаковые способы представления данных.

Прежде чем делать сводную диаграмму в Экселе, добавим на панель быстрого доступа кнопку «Мастера сводных таблиц и диаграмм». Через «Офис» заходим в «Параметры Excel» - «Настройка». Выбираем «Все команды». Находим инструмент «Мастер сводных таблиц и диаграмм». Жмем «Добавить».

На панели появится такой значок:

Переходим на новый лист. Вызываем «Мастера»:


После нажатия кнопки «Готово» получаем сводную диаграмму и таблицу следующего вида:


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



Сводная диаграмма в Экселе на основе готовой сводной таблицы

Выбираем сводную таблицу, щелкнув в любом месте мышкой. После чего переходим на вкладку «Вставка-Гистограмма».

Выбираем простую гистограмму. В результате появляется диаграммы с данными из сводной таблицы и область фильтра. Зададим фильтр для значений в столбцах – отобразить продажи за 1 квартал 2013, 2014, 2015 гг.


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

С помощью окна «Область фильтра» можно управлять сводной таблицей и диаграммой.

Сравнение продаж по кварталам за анализируемый период:


Анализ продаж в магазине «Серебрянка»:


Если для удобства необходимо переместить диаграмму на новый лист, щелкаем правой кнопкой по области построения и нажимаем «Переместить диаграмму».

В первом примере мы объединяли в одну диаграмму сразу несколько диапазонов. Но можно построить график и на основе одной базовой таблицы. Выделить ее – перейти на вкладку «Вставка».

Программа создаст одновременно сводную таблицу и диаграмму. Настраивайте поля – и анализируйте.