Упражнение 2. Сводные таблицы

1. Создать новую книгу. На первый лист этой книги ввести таблицу, приведенную на рис. 7, содержащую данные о продажах строительных материалов. Назвать лист Продажи и сохранить книгу под именем Имя_10_1.

Рисунок 7 - Таблица исходных значений

о продажах строительных материалов

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

- на 1-м шаге - Создать таблицу на основе списка или базы данных Excel;

- на 2-м шаге - задать в качестве области данных диапазон "$A$1:$G$11" листа Продажи;

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

- на 4-м шаге - оставить без изменения все параметры, установленные по умолчанию и нажать кнопку Готово.

Рисунок 8 - Результат полученной сводной таблицы

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

4. Активизировать инструментальную панель Сводные таблицы.

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

Рисунок 9 - Результат группировки по датам продаж по месяцам

6. Повторить п. 5 задания, выбрав в качестве критерия группировки - квартал. Сравнить полученный результат с представленным на рис. 10.

Рисунок 10 - Результат группировки по кварталу

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

Рисунок 11 - Результат группировки одновременно по кварталам и месяцам

8. Просмотреть детализированную информацию о содержимом ячеек сводной таблицы.

9. Повторить группировку данных сводной таблицы по кварталам. Для этой сводной таблицы установить курсор в непустую ячейку, например ячейку D4 (Доски - Кв-л4), и нажать в панели Сводные таблицы кнопку Отобразить детали. Просмотреть в открывшемся листе детализированные данные по наименованию Доски, проданные в 4-м квартале. Сравнить результат с представленным на рис. 12.

Рисунок 12 - Результат группировки данных сводной таблицы по кварталам

10. Выполнить Вычисление поля сводной таблицы для поля Название. Для этого дважды щелкнуть мышью по полю Название и в открывшемся диалоге Вычисление поля сводной таблицы установить следующие параметры:

- имя: Название;

- ориентация: По строкам.

Остальные параметры диалога оставить без изменения. Сравнить результат с представленным на рис. 13.

11. Самостоятельно создать три новых варианта сводной таблицы, в которых будет выполнено Вычисление полей сводной таблицы для полей Название и Дата с различной ориентацией - по строкам и по столбцам. Оценить все созданные варианты сводных таблиц и сделать выводы об их применимости для решения практических задач.

Рисунок 13 - Результаты выполнения сводной таблицы

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

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

Рисунок 14 - Диалоговое окно мастера сводных таблиц

14. После формирования сводной таблицы сгруппировать данные о датах продаж по месяцам. Сравнить полученный результат со сводной таблицей, представленной на рис.15. Обратите внимание на демонстрационный характер задачи, а именно на то, что в результате получены суммарные данные по Суммам продаж и суммарные значения для Цен на товары.

Рисунок 15 - Результат формирования сводной таблицы

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

16. Для полученной в п. 13 сводной таблицы изменить выполняемую над данными операцию, заменив вычисление среднего значения определением максимального значения. Для выполнения этого пункта поместите курсор в непустую ячейку сводной таблицы и на панели Сводные таблицы нажмите кнопку Поле сводной таблицы. В открывшемся диалоговом окне Вычисление поля сводной таблицы выберите нужную функцию и проанализируйте полученный результат.

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