Сводные таблицы в MS Excel 2013

Современные информационные технологии являются одними из самых динамично развивающихся и самых востребованных в современном мире. При этом, у взрослых людей возникает проблема самообразования без отрыва от исполнения своих прямых обязанностей. Для решения этой проблемы используются разнообразные самоучители, например [1], написанные под конкретную информационную систему. Чаще всего они объясняют работу с объектами, не описывая общие характеристики объектов. Цель этой статьи собрать базовую модель построения одного из часто используемых объектов MS Excel — сводную таблицу, и выделить неизменную часть этого объекта.

Сводные таблицы являются мощным средством анализа многомерных данных. Суть заключается в том, что данные представляются в виде многомерного куба с возможностью дальнейшего произвольного манипулирования ими. Они дают возможность проводить анализ структуры взаимосвязей различных показателей, обрабатывать большие объемы информации, проводить анализ в различных разрезах и плоскостях. Потребность в них возникает, когда пользователь должен произвести простейшую выборку с группировкой и простейшими алгебраическими или статистическими действиями. Они являются упрощенным вариантом перекрестного запроса, который используется для построения итоговых отчетов в системах управления реляционными базами данных, например в MS Access. Сводные таблицы дают также возможность заниматься проверкой возникающих у аналитика гипотез. Распространенным видом аналитических запросов здесь является анализ по принципу «что, если?».

Основа сводной таблицы

Исходными данными для сводной таблицы являются записи, представленные в виде списка [3, с. 90]. Основные правила создания и простейшие приёмы работы со списками были рассмотрены нами ранее в работе [2].

1.                  Напомним, что список — это прямоугольный непрерывный диапазон ячеек, определяемый адресом левой верхней и адресом правой нижней ячейки, удовлетворяющий следующим правилам:

2.                  Первая строка диапазона содержит заголовки столбцов.

3.                  В столбцах информация однородная, то есть одного типа: числа, даты, символы.

4.                  По строке идет описание равно одного события (покупка единицы товара, опрос одного человека, описание одного элемента).

5.                  В диапазоне отсутствуют пустые ячейки

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

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

Как же его настроить? Для этого надо обратиться к дополнительной панели — Панели быстрого доступа и в списке Другие команды среди Всех команд выбрать Мастер сводных таблиц и диаграмм. В результате на панели быстрого доступа появится пиктограмма для вызова Мастера.

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

Работа этого мастера состоит из трёх шагов.

На первом шаге определяется источник данных (рис. 1). Автоматический выбор — Список, в котором установлен курсор. При этом список выделять не надо. Внешний источник данных — это или таблицы Lotus, или таблицы или запросы MS Access. Самый сложный вариант — Несколько диапазонов консолидации. При этом выборе необходимо указать несколько списков, или частей списков, из которых будет строиться таблица.

Создадим сводную таблицу на основе списка Excel.

Рис. 1. Создание сводной таблицы, шаг 1

 

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

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

Рис. 2. Создание сводной таблицы, шаг 2

 

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

Рис. 3. Создание сводной таблицы, шаг 3

 

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

Макет сводной таблицы

На листе, выбранном пользователем, появляется структура макета сводной таблицы(рис.4). В ней, перетягивая мышкой вниз, можно из списка заголовков списка определить, какие поля записей будут заголовками строк (Строки), какие столбцов (Колонны) и какие Значениями.

Рис. 4. Настройка структуры сводной таблицы

 

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

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

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

Внутренним полем является в первую очередь поле Значения. Это поле используется для математической обработки данных. Поэтому, чаще всего, в нем стоят числовые данные. Допустимы следующие действия с данными: Сумма, Количество, Среднее, Максимум, Минимум и другие. Также к внутренним полям будут относиться поля, стоящие на нижнем уровне группировки.

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

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

Для группировки заголовков с данными, отличными от Дата, следует сначала выделить необходимые поля, а затем вызвать контекстное меню и выбрать действие Группировка. Например, поле Фамилия клиента можно сгруппировать по алфавиту, или по половому признаку. При этом новое поле заголовка по умолчанию имеет название Группа 1, 2 и т. д. Пользователь может его поменять в строке формул (рис. 5).

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

Рис. 5. Группировка по половому признаку

 

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

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

Фильтр позволяет произвести наложение дополнительных условий на отображение. Он работает также, как и обычный фильтр списка.

На рисунке 5 приведен результат построения сводной таблицы, в которой поле Фамилия было сгруппировано по половому признаку, поле Дата сделки поквартально. Поле значения Количество суммируется.

На основе полученной сводной таблицы можно построить сводную диаграмму (рис. 6).

Рис. 6. Сводная диаграмма

 

Основные элементы сводных таблиц

Так какие же элементы сводной таблицы существуют вне зависимости от реализации MS Office? В первую очередь — мастер сводных таблиц и диаграмм. Он может быть встроен в меню Данные, а может находится в списке дополнительных команд. Его присутствие значительно упрощает алгоритм построения таблицы.

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

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

Заключение

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

 

Литература:

 

1.                  Дж. Билл, А. Майкл. «Сводные таблицы В Microsoft Exсel 2010», Москва, «Вильямс», 2011, 464 стр.

2.                  А. Л. Белкова, С. Н. Леора «Осваиваем работу с реляционными базами в MS Excel 2013«, «Теория и практика образования в современном мире: материалы VI междунар. науч. конф. (г. Санкт-Петербург, декабрь 2014 г.)», СПб, «Заневская площадь», 2014. — С. 349–356.

3.                  Э.Г Бурнаева, А. Л. Белкова, С. Н. Леора. «Информатика. Работа в MS Excel 2007», СПб, СПбГУ, 2012, 135 стр.