Лабораторная работа 3.9. Сводные таблицы

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

1 На основе данных о работе трех магазинов, торгующих канцелярскими товарами (рис. 3.46), определить:

1) выручку для каждого магазина;

2) выручку от продажи товаров каждого наименования;

3) общую выручку по всем магазинам;

4) выручку от продажи ручек (шариковая, гелевая, перьевая) для каждого магазина;

5) общую выручку от продажи ручек.

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

Рис. 3.46. Исходная таблица для задания 1

Выполнение:

1.1 В книгу Лабораторные.xls добавьте новый лист с названием Сводные таблицы. Внесите на этот лист исходные данные (рис. 3.46).

1.2 В ячейку Е1 введите заголовок столбца Выручка, в ячейку Е2 введите формулу =С2*D2 и скопируйте ее с помощью маркера заполнения в остальные ячейки столбца. Таблица примет вид (рис. 3.47).

Рис. 3.47. Дополненная таблица для задания 1

1.3 Вызов мастера сводных таблиц: сделайте активной какую-либо ячейку таблицы (всю таблицу выделять необязательно); войдите в меню Данные / Сводная таблица… В результате появится окно мастера сводных таблиц.

1.4 Определение типа исходных данных для сводной таблицы: оставьте вариант, предлагаемый по умолчанию («В списке или базе данных Microsoft Excel»); [Далее >]. Этот вариант используется в ситуациях, когда сводная таблица создается на основе одной исходной таблицы данных.

1.5 Определение диапазона с исходными данными: оставьте вариант по умолчанию (А1:Е12, то есть вся таблица); [Далее >].

1.6 Определение макета сводной таблицы (в Excel 2000 для перехода в режим определения макета надо дополнительно нажать кнопку [Макет…]: зацепите мышью элемент с текстом Магазин и перетащите его на область Столбец; затем перетащите элемент Наименование на область Строка и элемент Выручка на область Данные (в результате макет сводной таблицы примет указанный на рис. 3.48 вид); [Далее >] ([OK] в Excel 2000).

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

R Для удаления ошибочно размещенного элемента макета следует зацепить его мышью и «стащить» с таблицы.

1.7 Размещение сводной таблицы: выберите вариант Существующий лист и щелкните мышью на ячейке F1 (эта ячейка станет левым верхним углом созданной сводной таблицы); нажмите Готово. На листе будет сформирована сводная таблица, и одновременно на экране появится панель Сводные таблицы.

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

1.8 Форматирование ячеек в области данных: сделайте активной одну из ячеек в области данных сводной таблицы;  (панель Сводные таблицы); в появившемся окне Вычисление поля сводной таблицы нажмите Формат…, выберите в списке вариант Денежный и установите число десятичных знаков равным «2»; [OK], [OK]. В результате сводная таблица примет следующий вид (рис. 3.49).

Рис. 3.49. Вид сводной таблицы

Теперь можно ответить на первые три вопроса задания:

1) выручка для каждого магазина указана в нижней строке;

2) выручка по каждому наименованию - в правом столбце;

3) общая выручка - в правой нижней ячейке.

Для ответа на последние два вопроса надо выполнить группировку данных.

1.9 Группировка данных и скрытие деталей в сводной таблице: в столбце «Наименование» сводной таблицы выделите три несмежные ячейки, соответствующие товарам «ручка» (шариковая, гелевая, перьевая), щелкнув на них мышью при нажатой клавише Ctrl; меню Данные / Группа и структура / Группировать… (в Excel 97 достаточно нажать кнопку  Æ  на панели Сводные таблицы). В результате в сводной таблице появится еще один столбец с заголовком Наименование 2, в котором всем товарам «ручка» будет соответствовать одна ячейка Группа1. Для большей наглядности ее содержимое следует заменить на «Ручки» (отредактировав текст этой ячейки обычным образом). Теперь осталось скрыть детали в столбце Наименование: щелкните на заголовке Наименование 2;  (панель Сводные таблицы). В результате сводная таблица примет вид (рис. 3.50).

Рис. 3.50. Сводная таблица с выполненной группировкой данных

и скрытыми деталями

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

1.10 Возврат к исходному виду сводной таблицы (отображение деталей и разгруппировка данных):

- отображение деталей: щелкните на заголовке Наименование 2;  ;

- разгруппировка данных: не снимая выделения со столбца Наименование 2, выполните команду Данные / Группа и структура / Разруппировать… (в Excel 97 достаточно нажать кнопку  Å  на панели Сводные таблицы).

Сохраните измененный файл.

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

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

1) количество товаров, проданных в каждом магазине;

2) общее количество проданных товаров определенного наименования;

3) общее количество проданных товаров;

4) количество ручек (шариковая, гелевая, перьевая), проданных в каждом магазине;

5) общее количество проданных ручек.