РАБОТА 7. СВОДНЫЕ ТАБЛИЦЫ

КРАТКАЯ СПРАВКА

Команда Данные, Сводная таблица вызывает Мастера сводных таблиц для построения сводов — итогов определенных видов на основании данных списков, других сводных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы Excel 97. Сводная таблица обеспечивает различные способы агрегирования информации.

Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:

Этап 1. Указание вида источника сводной таблицы:

·         использование списка (базы данных Excel);

·         использование внешнего источника данных;

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

·         использование данных из другой сводной таблицы.

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

Этап 2. Указание диапазона ячеек, содержащего исходные данные. Список (база данных Excel) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде

[имя_книги]имя_листа!диапазон ячеек

Если предварительно установить курсор в список, для которого строится сводная таблица, интервал ячеек будет автоматически указан. Для ссылки на закрытый интервал другой рабочей книги нажимается кнопка <0бзор>, в одноименном диалоговом окне выбирается диск, каталог и файл закрытой рабочей книги, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек.

Этап 3 . Построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей, определяемых в макете (рис.3.47.):

Рис.3.47. Схема макета сводной таблицы

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

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

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

данные — поля, по которым подводятся итоги, согласно выбранной функции; область определять обязательно.

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

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

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

а

б

Рис. 3.48. Диалоговое окно «Вычисление поля сводной таблицы»

Для этого следует установить курсор на настраиваемое поле и дважды нажать левую кнопку мыши длявызова диалогового окна «Вычисление поля сводной таблицы» (рис. 3,48, а), в котором можно переименовать поле, изменить операцию, производимую с данными поля, или изменить формат представления числа.

Кнопка «Дополнительно» вызывает панель Дополнительные вычисления для выбо­ра функций, список которых приведен в табл. 3.5. При использовании функции сравнения (Отличие, Доля, Приведенное отличие) выбирается (рис. 3,48, б) Поле и Элемент, с которым будет производиться сравнение. Список Поле содержит поля сводной таблицы, с которым связаны базовые данные для пользовательского вычисления. Список Элемент содержит значения поля, участвующего в пользовательском вычислении.

Таблица 3.4. Виды дополнительных функций над полем в области данных

Этап 4. Выбор места расположения и параметров сводной таблицы. В появляющемся на четвертом шаге диалоговом окне (рис.3.49) можно выбрать место расположения сводной таблицы, установив переключатель новый лист или существующий лист, для которого необходимо задать диапазон размещения. После нажатия кнопки <Готово> будет сформирована сводная таблица со стандартным именем.

Рис. 3.49. Диалоговое окно «Мастер сводных таблиц» на 4-м этапе

Кнопка <Параметры> в диалоговом окне 4-го шага вызывает диалоговое окно «Параметры сводной таблицы», в котором устанавливается вариант вывода информации в сводной таблице:

общая сумма по столбцам — внизу сводной таблицы выводятся, общие итоги по столбцам;                                                   

общая сумма по строкам — в сводной таблице формируется итоговый столбец;

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

автоформат — позволяет форматировать сводную таблицу с помощью команды Формат, Автоформат и другие параметры.

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

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

ЗАДАНИЕ

Для таблицы на рис. 3.35 постройте следующие виды сводных таблиц:

·         по учебным группам подведите итоги по каждому предмету и виду занятий с привязкой к преподавателю:

§         средний балл;

§         количество оценок;

§         минимальная оценка;

§         максимальная оценка;

·         по каждому преподавателю подведите итоги в разрезе предметов и номеров учебных

§       групп:

§       количество оценок;

§       средний балл;

§       структура успеваемости.

ТЕХНОЛОГИЯ РАБОТЫ

1.              Проведите подготовительную работу:

·         откройте созданную рабочую книгу Spisok командой файл, Открыть;

·         вставьте новый лист и назовите его Свод,

·         выделите блок ячеек исходного списка на листе Список, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Свод.

2.              Создайте сводную таблицу с помощью Мастера сводных таблиц по шагам:

·         установите курсор в области данных таблицы;

·         выполните команду Данные, Сводная таблица.

Этап 1 —выберите источник данных—текущую таблицу, щелкнув по кнопке <в списке или базе данных Ехсе1> и по кнопке <Далее>.

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

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

·         подведите курсор к имени поля, находящегося в правой стороне макета;

·         нажмите левую кнопку мыши и, удерживая ее нажатой, перетащите элемент с именем поля в одну из областей (Страница, Строка, Столбец, Данные),

·         отпустите кнопку мыши, поле должно остаться в этой области;

·         после установки поля в области Данные необходимо два раза щелкнуть по нему правой кнопкой мыши и в диалоговом окне «Вычисление поля сводной таблицы» выбрать операцию (функцию) над значением поля,

Этап 4 —выбор места расположения—существующий лист.

3.              Выполните автоформатирование полученной сводной таблицы командой Формат, Автоформат.

4.              Внесите изменения в исходные данные и выполните команду Данные, Обновить данные.

5.              Повторите процесс построения сводной таблицы для п.2 задания.