Технология выполнения работы

 

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

Сводная таблица может быть создана на основании данных находящихся:

- в списке или базе данных Microsoft Excel;

- во внешнем источнике данных;

- в нескольких диапазонах консолидации;

- в другой сводной таблице.

 

Каждая сводная таблица состоит из 4 областей: страница, строка, столбец, данные (рис.1).

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

Рис.1.

 

Назначение областей следующее:

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

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

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

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

 

Задание 1. Создайте на основе таблицы сводную таблицу.

 

Наименование	Артикул	Цена
Стол	В-876	2000
Шкаф	Е-764	3500
Стол	У-65	2800
Кровать	К-765	4000
Шкаф	Ш-554	5000
Шкаф	Ш-621	4700
Кровать	К-443	2500

 

1.1. Выделите любую ячейку исходной таблицы. В меню Данные выберите Сводная таблица. Появится окно Мастера сводных таблиц и диаграмм (рис.2).

 

Рис.2

 

Установите переключатели в списке или базе данных Excel и сводная таблица. Щелкните на кнопке Далее.

1.2. В диалоговом окне Мастера сводных таблиц и диаграмм – шаг 2 (рис.3) проверьте, правильно ли указан диапазон, в котором размещается исходная таблица. Щелкните на кнопке Далее.

 

Рис.3

 

1.3. В окне мастера сводных таблиц и диаграмм (рис.4) установите переключатель в положение новый лист и щелкните на кнопке Макет.

 

Рис.4

 

1.4. В диалоговом окне мастера сводных таблиц и диаграмм (рис.5) перетащите кнопки с наименованием полей исходной таблицы на план-макет новой таблицы. Например, перетащите кнопку Наименование в Столбцы, Артикул – в Строка, Цена в Данные. Щелкните по кнопке ОК и, вернувшись в окно Мастера сводных таблиц и диаграмм, щелкните по кнопке ОК, Готово.

Рис.5

 

1.5. Проанализируйте полученный вид исходной таблицы (рис.6). Стала ли ваша база данных удобнее для работы? Если это не то, что вам нужно, выделите любую ячейку сводной таблицы, войдите в главное меню Данные выберите Сводная таблица. Попав снова в окно мастера сводных таблиц и диаграмм, щелкните по кнопке Макет, войдите в макет и измените размещение полей, перемещая их левой кнопкой мыши. Подберите тот вид сводной таблицы, который бы вас полностью устроил.

Рис.6

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

Рис.7

1.7. Для работы с данными сводной таблицы, помимо мыши, пункта главного меню Данные и контекстного меню, предусмотрена специальная панель управления Сводные таблицы (рис.8).

Рис.8

 

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

1.8. Если вы хотите иметь более детализированное представление некоторых параметров в сводной таблице, воспользуйтесь кнопками Отобразить детали и Скрыть детали на панели управления Сводные таблицы. Выделите в сводной таблице ячейку D4, содержащую наименование поля Шкаф. В этом поле приведены цены товара шкаф для различных артикулов. Вы хотите иметь для цены каждого артикула отдельный столбец. Щелкните на кнопке Отобразить детали и убедитесь в результатах (рис.9). Отмените созданную детализацию таблицы.

 

Рис.9

 

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

 

Рис.10

 

1.10. Если вы хотите изменить вид сводной таблицы, то необязательно вызывать Мастера сводных таблиц и диаграмм. Выделите ячейку в сводной таблице, например В7, и щелкните по кнопке Параметры поля на панели управления Сводные таблицы. Появится диалоговое окно Вычисление поля сводной таблицы (рис.11) .

Рис.11

 

В списке Операция: выберите, что вы хотите иметь в Общем итоге. Выберите Максимум и щелкните по кнопке ОК. Убедитесь, что в строке Общие итоги выводятся максимальные значения цен на изделия, а в ячейке А3 появилась запись Максимум по полю цена (рис.12). Сделайте так, чтобы в строке Общий итог выводилась сумма.

 

Рис.12

 

1.11. Если вы хотите построить диаграмму, то щелкните на кнопке Мастер диаграмм и выберите тип диаграммы (рис.13).

 

Рис.13

 

Задание 2.1. На основании следующей таблицы:

 

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

Перетащите кнопки «Продукция» и «Менеджер» в область «Строка». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область «Столбец» перетащите кнопку «Месяц» и в область страниц – кнопку «Регион». В область данных перетащите кнопку «Прибыль». Укажите место размещения сводной таблицы.

Построенная сводная таблица будет иметь следующий вид (рис.14):

 

Рис.14

 

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

1. Скопируйте сводную таблицу задания 2.1. на другой лист или повторите процесс ее построения. Можно также создать копию листа со сводной таблицей.

Отметьте диапазон A4: C15. Для этого достаточно сделать текущей ячейку С4 (выделится столбец сводной таблицы за январь месяц) и нажав клавишу « Shift» щелкнуть по ячейке E4.

2. Выполните команды «Данные» «Группа и структура» «Группировать». В поле столбца появиться новое поле «Месяц 2» и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название «Группа 1».

3. Выполните аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появится название «Группа 2».

4. Удалите поле месяц. Для этого вызовите контекстное меню или перетащите его из области сводной таблицы. Исправьте название «Месяц 2» на «Квартал», «Группа 1» – на «Первый», «Группа 2» – на «Второй».

Полученная таблица должна иметь следующий вид (рис.15):

 

Рис.15

Задание 2.3. Скопируйте первуюсводную таблицу на новый лист. Последовательно удаляя поля «Менеджер», «Месяц» и «Продукция» получите новые сводные таблицы. Поясните их смысл.

Лабораторная работа №12

Функции ссылок и массивов.