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

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

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

Рассмотрим возможности использования сводных таблиц на примере. Создадим таблицу.

Название судна	Проект	Пункт назначения	Операция	Загрузка, т
Балтийский-1		Череповец	Погрузка
Балтийский-5		Череповец	Выгрузка
Волго-Дон-3		С-Петербург	Погрузка
Волго-Балт-10		Петрокрепость	Погрузка
Волго-Балт-15		Ростов	Погрузка
Волго-Балт-25		Река Свирь	Выгрузка

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

Создание и обработка сводных таблиц осуществляются с помощью специального мастера, для запуска которого предназначена команда Сводная таблица из меню Данные. После ее вызова открывается первое диалоговое окно мастера сводных таблиц – Мастер сводных таблиц – шаг 1 из 4.

В первом диалоговом окне мастера сводных таблиц указывается источник данных для сводной таблицы. В области «Создать таблицу на основе данных, находящихся:» по умолчанию активизирован переключатель «в списке или базе данных Microsoft Excel». Поскольку наша таблица размещена на рабочем листе Exсel, не будем изменять эту установку. Нажмите кнопку Далее, чтобы перейти в следующее диалоговое окно мастера.

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

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

Переместите поле Операция в область страниц. Таким образом вы зададите размещение данных по всем операциям на «отдельной странице». Операции будут находиться в поле списка. Для отображения на экране данных о какой-либо операции нужно будет выбрать ее в списке. Предположим, что нас интересует в первую очередь загрузка судов. В этом случае ее следует разместить в поле Данные. При этом отдельные значения будут автоматически просуммированы. Поместите поля Проект, Название судна и Пункт назначения в область полей строк.

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

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

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

Окно Параметры сводной таблицы состоит из двух областей – Формат и Данные. В первой области устанавливаются параметры форматирования сводной таблицы, а во второй – параметры источника данных сводной таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

1. Каким образом можно произвести анализ данных в таблицах?

2. В каких случаях применяется фильтрация данных?

3. Как применить расширенный фильтр к таблице?

4. Как задаются критерии? Приведите примеры.

5. С помощью какой команды можно произвести автоматическое вычисление итогов?

6. Что необходимо выполнить перед автоматическим вычислением итогов?

7. Как создать список данных на текущем листе?

8. Как присвоить списку имя?

9. Как использовать список, созданный на другом листе книги?

10. В каких случаях можно использовать консолидацию данных?