Учебно-методическое пособие для студентов вузов Павлодар (стр. 42 из 76)

На 2-м шаге указываем диапазон, содержащий исходные данные. Если перед вызовом мастера сводных таблиц мы отметили ячейку внутри списка, то этот диапазон установится автоматически.

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

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

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

В результате получим сводную таблицу, в которой наглядно отображены данные по продаже товара (рисунок 37)

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

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

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

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

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

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

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

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

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

На этой панели располагаются следующие кнопки (рисунок 38)

Рисунок 38 – Панель инструментов Сводные таблицы

Кнопка Сводная таблица. При нажатии этой кнопки появляется список различных команд для работы со сводной таблицей.

Кнопка Мастер сводных таблиц - вызов мастера построения сводных таблиц.

Кнопка Поле сводной таблицы – в зависимости от того, какая ячейка является текущей в сводной таблице, будут изменены частичные суммы, значения вычисляемых полей или свойства области данных.

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

Кнопка Разгруппировать - разделяет группы на отдельные элементы, например, квартал на отдельные даты.

Кнопка Группировать – группирует элементы по категориям для сведения нескольких элементов в один. Например, можно сгруппировать дни, недели или месяцы в кварталы для анализа, построения диаграмм и печати поквартальных результатов.

Кнопка Скрыть детали – скрытие подчиненных данных внешнего элемента поля строки или столбца в сводной таблице.

Кнопка Отобразить детали – отображение данных в скрытых строках или столбцах сводной таблицы.

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

Кнопка Заголовки - выделение только заголовка при выборе поля или заголовка в сводной таблице. Сами поля и связанные с ними данные при этом не выделяются.

Кнопка Данные - выделение только связанных данных при выборе поля или заголовка в сводной таблице. Сами поля и заголовки при этом не выделяются.

Кнопка Заголовки и данные - выделение как связанных данных, так и заголовка при выборе поля или заголовка в сводной таблице.

Создание диаграммы для сводной таблицы:

- на панели инструментов Сводные таблицы выберите команду Сводная таблица - Выделить. Проверьте, что кнопка Разрешить выделение не нажата;

- удалите любые промежуточные итоги из сводной таблицы;

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

- нажмите кнопку Мастер диаграмм

;

- следуйте инструкциям мастера диаграмм.

Задание

1 Откройте рабочую книгу Оргтехника.

2 Составьте итоговый отчет на листе Ксероксы – по каждому виду ксероксов выведите итоговую сумму от продажи

3 На листе Полный ассортимент введите вложенные промежуточные итоги - усредните стоимость и цену по видам товаров

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

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

6 Откройте рабочую книгу Продажа товаров.

7 На листе Январь для ячейки Сумма в у.е. добавьте примечание – «Выбрано среднее значение курса доллара за месяц».

8 Определите приемлемые данные для поля Дата. Добавьте проверку данных при вводе и сообщение об ошибке, например - «Дата должна быть в пределах от 01.01.04 до 31.01.04, так как это отчет за январь. Проверьте дату!»

9 Подведите на листе Январь промежуточные итоги о количестве проданного товара, а затем вложенные итоги о сумме выручке за каждый день продажи товаров.

10 Сохраните рабочую книгу.

11 Добавьте новый лист, переместите его и оставьте после листа «Полный ассортимент», назовите его «Отчет»

12 Скопируйте диапазон ячеек A1:C6 с листа «Полный ассортимент» на лист «Отчет», измените название в ячейке C1 на «Фирма ЗАПАД. Продажа персональной, деловой и профессиональной оргтехники».

13 На листе Полный ассортимент выделите ячейку C9 и в меню Данные выберите команду Сводные таблицы, откроется диалоговое окно Мастер сводных таблиц.

На первом шаге нажмите Далее

На втором шаге укажите диапазон

На третьем шаге перетащите мышью кнопку Товар в поле Столбец, кнопку Название в поле Строка, кнопку Сумма в поле Данные.

На четвертом шаге постройте сводную таблицу на существующем листе «Отчет» в ячейке C8.

14 Ваш рабочий лист должен выглядеть так (рисунок 39)

Рисунок 39 – Вид рабочего листа

15 Добавьте количество проданных товаров каждого вида:

- щелкните мышью в любой из ячеек сводной таблицы и перетащите кнопку Количество в поле Данные;

- щелкните правой кнопкой мыши в ячейке D10 и выберите команду Параметры поля, откроется диалоговое окно Вычисление поля сводной таблицы, в поле имя наберите Выручка вместо Сумма по полю Сумма и нажмите ОК;

- аналогичным образом в ячейке D11 наберите Продано всего.

16 Поместите поле на отдельной странице:

- сделайте активной сводную таблицу и перетащите кнопку Товар из ячейки E8 в поле страницы;

- ваш рабочий лист выглядит так (рисунок 40).

Рисунок 40 – Вид рабочего листа Отчет 1

17 Перенесите кнопку из области страниц в область строк

- кнопка Товар перенесена из области страниц в область строк, и теперь все данные расположены на одной странице.

18 Скройте, а затем покажите детали сводной таблицы:

- в столбце Товар щелкните дважды на заголовке Ксерокс, данные по этому виду товара сжались;

- повторным двойным щелчком в этой ячейке сводная таблица вернется к прежнему виду.

19 Скройте данные:

- перетащите кнопку Товар в область страниц;

- дважды щелкните на кнопке Товар, откроется окно Вычисление сводной таблицы;

- в окне списка Скрыть элементы выделите строку Факс и ОК;

- в списке кнопки Товар теперь находятся только названия страниц Все им Ксерокс;

- аналогичным образом покажите скрытые данные.

20 Покажите все данные о продаже профессиональных ксероксов