имя_книги ] имя_листа!диапазон ячеек
Если предварительно установить курсор в список, для которого строится сводная таблица, интервал ячеек будет автоматически указан. Для ссылки на закрытый интервал другой рабочей книги нажимается кнопке <Обзор>, в одноименном диалоговом окне выбирается диск, каталог и файл закрытой рабочей книга, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек.
3. Выбрать место расположении и параметров сводной таблицы. Можно выбрать место расположения сводной таблицы, установив переключатель новый лист или существующий лист, для которого необходимо задать диапазон размещения. После нажатия кнопки OK будет сформирована сводная таблица со стандартным именем.
Структура сводной таблицы состоит из следующих областей, определяемых в макете:
страница— на ней размещаются поля, значения которых обеспечивают отбор записей на первом уровне; на странице может быть размещено несколько полей, между которыми устанавливается иерархия связи – сверху вниз; страницу определять необязательно;
столбец – поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец необязательно;
строка — поля размещается сверху вниз, обеспечивая группировку данных таблицы по иерархии полей; при условии существовании области страницы или столбцов определял, строку необязательно;
данные — поля, по которым подводятся итоги, согласно выбранной функции; область определять обязательно.
Размещение полей выполняется путем их перетаскивания при нажатой левой кнопке мыши в определенную область макета. Каждое поле размещается только один раз в областях: страница, строка или столбец, По этим полям можно формировать группы и получать итоговые значения в области данные — группировочные поля. В области данные могут находиться поля произвольных типов, одно и то же поле может многократно размешаться в области данные. Для каждого такого поля задается вид функции и выполняется необходимая настройка.
Для изменения структуры сводной таблицы выполняется перемещение полей из одной области в другую (добавление новых, удаленно существующих полей» изменение местонахождения поля). Для сводных таблиц существен порядок следования полей (слева направо, сверху вниз), изменяется порядок следования полей также путем их перемещение.
В макете сводной таблицы можно выполнить настройку параметров полей, размещенных в области данных. Эта настройка полей осуществляется с помощью диалогового окна «Вычисление поля сводной таблицы». Для этого следует установить курсор на настраиваемой поле и дважды нажать левую кнопку мыши для вызова диалогового окна «Вычисление поля сводной таблицы» котором можно переименовать поле, изменить операцию, производимую с данными поля, или изменить формат представления числа.
ТЕХНОЛОГИЯ РАБОТЫ
1. Создайте новую книгу. На листе этой книги создайте таблицу (рис. 1), содержащую данные о продажах автомобилей тремя фирмами (Альфа, Бета и Гамма) за первое полугодие 2009 года. Назовите лист "Продажи" и сохраните книгу.
Рис. 1
2. Выполните команду Вставка – Таблицы – Сводная таблица, предварительно установив курсор в исходную таблицу. В диалоговом окне «Создание сводной таблицы» выберите диапазон данных для анализа и местоположение сводной таблицы (на новом листе).
§ Для создания таблицы, приведенной на рис. 2, заполните макет сводной таблицы в панели Список полей сводной таблицы, поместив поля таблицы в соответствующие области: поле Марка – в Названия строк, поле Фирма – в Названия столбцов, а поле Цена – в область Значения. Полученная в результате этих действий сводная таблица, а также панель Список полей сводной таблицы показаны на рис. 2.
Рис. 2
§ Познакомьтесь с вкладкой Работа со сводными таблицами. Измените функцию суммирования по полю Цена на среднее значение, установив курсор на числовые данные таблицы и выбрав из контекстного меню команду Итоги по…или команду Работа со сводными таблицами – Параметры – Активное поле – Параметры поля – Операция. Измените название поля на Средняя цена, используя текстовое поле Пользовательское имя в том же диалоговом окне.
§ Поменяйте ориентацию строк и столбцов таблицы, пользуясь раскрывающимися списками в областях панели «Список полей сводной таблицы».
§ Измените форматирование числовых значений сводной таблицы, применив денежный формат (Работа со сводными таблицами – Параметры – Активное поле – Параметры поля). Примените стили к сводной таблице (Работа со сводными таблицами – Конструктор – Параметры стилей сводной таблицы).
§ Рассмотрите возможности отображения и сокрытия итогов вычислений (Работа со сводными таблицами – Конструктор – Макет).
§ Скройте и восстановите заголовки полей (Работа со сводными таблицами – Параметры – Показать или скрыть – Заголовки полей).
Дополнительное задание.
3. Сформируйте сводную таблицу, позволяющую оценить количество продаж и сумму продаж трех фирм различных марок машин. Таблица представлена на рис. 3.
Примечание. Каждую новую таблицу создавать на новом листе, который необходимо переименовать в соответствии с заданием.
Рис. 3
4. Сформируйте сводную таблицу, позволяющую оценить сумму продаж и даты продаж. Таблица представлена на рис. 4. Проанализируйте данные таблицы.
§ Сгруппируйте данные по месяцам, затем по кварталам, затем по месяцам и кварталам (Работа со сводными таблицами – Параметры – Группировать – Группировка по полю). Таблица представлена на рис. 5.а. Проанализируйте данные таблицы.
Рис. 4
§ Примените к датам продажи группировку по выделенному, выделив сначала группу зимнее-летних месяцев, а затем группу остальных. После выделения обратитесь к команде Работа со сводными таблицами – Параметры – Группировать – Группировка по выделенному.Таблица представлена на рис. 5.б. Проанализируйте данные таблицы.
| 
|
| Рис. 5.а | Рис. 5.б |
5. Создайте новую сводную таблицу, которая будет отображать суммарную цену и количество проданных каждой фирмой автомобилей в зависимости от года выпуска автомобиля и даты продажи. Сгруппируйте данные о годах выпуска с шагом в пять лет. Таблица представлена на рис. 6. Проанализируйте данные таблицы.
Рис. 6
6. Создайте сводную таблицу, представленную на рис. 7. Используйте в ней вычисляемое поле Сумма в рублях.Для его создания примените команду Работа со сводными таблицами – Параметры – Сервис – Формулы - Вычисляемое поле. Дайте имя полю Сумма в рублях, а в поле Формула введите формулу =30*Цена.
Рис. 7