Лабораторная работа № 14. Построение сводных таблиц

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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