Методические указания для выполнения задания 7
1. Создание сводной таблицы. Общие положения.
Создание сводной таблицы производится с помощью Мастера сводных таблиц и диаграмм, который вызывается командой Данные - Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 1) установим переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:
· В списке или базе данных Мiсгоsоft Ехсеl - если данные берутся с одного рабочего листа;
· Во внешнем источнике данных - если данные берутся из внешней базы данных;
· В нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;
· В другой сводной таблице или сводной диаграмме - если сводная таблица создается на основе данных другой сводной таблицы.
Рис. 1. Первое диалоговое окно Мастер сводных таблиц и диаграмм
В рассматриваемом примере нужно установить переключатель в положение В списке или базе данных Мiсгоsоft Ехсеl.
В поле Диапазон второго диалогового окна Мастер сводных таблиц и диаграмм укажите координаты диапазона ячеек на основе данных из которого должна быть построена строится сводная таблица (рис. 2)
Рис. 2. Второе диалоговое окно Мастер сводных таблиц и диаграмм
В третьем диалоговом окне Мастер сводных таблиц необходимо указать структуру сводной таблицы (рис. 3).
Рис. 3. Третье диалоговое окно Мастер сводных таблиц и диаграмм
Для этого достаточно нажать кнопку Макет. Появится диалоговое окно для определения структуры сводной таблицы (рис. 4).
Конструирование сводной таблицы сводится к перетаскиванию мышкой названий полей в различные области. В центральной части этого окна расположены четыре области: Данные, Строка, Столбец и Страница, а в правой части окна присутствуют названия полей, которые имеются в указанном источнике данных. Области сводной таблицы имеют следующее назначение:
1. Области Строка и Столбец предназначены для группировки данных. Если название какого-то поля помещено в область Строка, то все записи источника данных группируются так, что каждая группа соответствует одному из значений поля, помещенного в область Строка. В область Строка может быть помещено несколько названий, что означает вложенную группировку. Аналогичное назначение имеет область Столбец.
2. Область Данные предназначена для выполнения групповых операций над данными. Если название какого-то поля помещено в область Данные, то ко всем записям источника данных, входящим в группу, соответствующую определенным значениям полей из области Строка и Столбец, применяется указанная групповая операция. По умолчанию групповой операцией для числовых данных является суммирование. В область Данные может быть помещено несколько названий, что означает выполнение групповой операции над значениями каждого поля.
Рис. 4. Диалоговое окно Мастер сводных таблиц и диаграмм - макет
3. Область Страница предназначена для фильтрации данных. Если название какого-то поля помещено в область Страница, то для дальнейшей обработки из источника данных выбираются только те записи, которые имеют заданное значение в этом поле. Выбор значения осуществляется с помощью раскрывающегося списка, содержащего все различные значения, которые принимает поле, помещенное в область Страница. По умолчанию выбирается значение Все, что означает отсутствие фильтрации. В область Страница может быть помещено несколько названий, что означает фильтрацию по нескольким полям.
В нашем примере можно сделать следующее.
1). Перетащить поле «Специализация» в область Страница. В результате сводная таблица будет либо представлять данные по отдельной специализации (выбранной в списке), либо сразу по всем специализациям, если выбран вариант Все.
2). Перетащить поля «Название» и «Дата счета» в область Строка. Тем самым задается группировка: нас интересуют данные по фирмам и для каждой фирмы по датам выставления счета.
3). Перетащить поле «Дата оплаты» в область Столбец. Тем самым задается группировка и по датам оплаты.
4). Перетащить поля «Сумма в счете» и «Сумма оплаты» в область Данные. В результате для групп записей, которые определяются значениями полей «Название», «Дата оплаты» и «Дата счета» будут вычисляться суммы значений полей «Сумма в счете» и «Сумма оплаты».
Чтобы подчеркнуть, что в сводной таблице в области Данные будут содержаться не значения из источника данных, а результаты групповых операций над этими значениями, Excel автоматически припишет полям имена «Сумма по полю Сумма в счете» и «Сумма по полю Сумма оплаты». Изменим эти имена на более понятные. Для этого нужно сделать двойной щелчок на названии поля в области Данные. Появится диалоговое окно, приведенное на рис. 5. В поле ввода Имя укажите новое название – «Сумма по счетам» и «Сумма оплаты» и нажмите кнопку ОК.
В диалоговом окне Вычисление поля сводной таблицы можно выбрать другую групповую операцию в списке Операция, а также задавать дополнительные вычисления на дополнительной области окна, появляющейся после нажатия кнопки Дополнительно.
В диалоговом окне Вычисление поля сводной таблицы можно выбрать другую групповую операцию в списке Операция, а также задавать дополнительные вычисления на дополнительной области окна, появляющейся после нажатия кнопки Дополнительно.
Рис. 5. Диалоговое окно Вычисление поля сводной таблицы
После того как макет таблицы определен, можно указать ее параметры в диалоговом окне, которое появляется при нажатии кнопки Параметры на третьем шаге Мастера сводных таблиц и диаграмм (рис. 6).
Рис. 6. Диалоговое окно Параметры сводной таблицы
В данном случае мы сняли флажок «общая сумма по строкам» (итоги по строкам мы будем определять другим способом) и «сохранить данные вместе с таблицей» (в целях экономии памяти).
Флажок «сохранить данные вместе с таблицей» указывает Excel, следует ли сохранять копию исходных данных вместе со сводной таблицей. Если флажок снят, то сводная таблица занимает меньше места в памяти компьютера, но данные следует обновлять при каждой загрузке приложения.
Теперь все подготовлено и можно нажать кнопку Готово в диалоговом окне Мастера сводных таблиц и диаграмм. Получим сводную таблицу, основная часть которой представлена на рис.7
Рис. 7 Сводная таблица