Консолидация данных. Построение макета сводной таблицы

Построение макета сводной таблицы.

Задание

Контрольные вопросы

1 Что такое структурированная таблица?

2 Виды структурирования таблиц в Excel.

3 Как выполняется ручное структурирование?

4 Как выполняется автоструктурирование?

5 Как выполняется структурирование с подведением итогов?

6 Для чего применяется структурирование таблиц?

7 Как выглядит структура таблицы?

8 Как отменить структуру таблицы?

Рисунок 5Пример структуры с промежуточными итогами

1 Подготовить для структурирования таблицу своего варианта из лабораторной работы №9 (excel-9) "Списки в Excel. Сортировка и фильтрация данных".

Для этого добавить:

▪ справа - ещё три столбца: в первые два ввести числовые данные,

▪ в третий – формулу для их обработки по строкам.

Например, в первом и втором - Зарплата и Налог, в третьем – Сумма к выдаче (как разность зарплаты и налога), или Цена и Количество = Общая стоимость (произведение цены на количество), или Доза и Количество приёмов = Общая доза (произведение дозы и количества), или Стоимость бензина и Пробег = Затраты на топливо (произведение стоимости и пробега) и т.п.

▪ снизу – ёщё одну строку, ввести в её ячейки формулы для обработки данных в столбцах (например, сумма, среднее, произведение, максимум и т.п.)

2 Выполнить в подготовленной таблице:

▪ Ручное структурирование

▪ Автоструктурирование с введением нового иерархического уровня по строкам.

▪ Структурирование с подведением итогов.

В результате выполнения работы должно получиться три таблицы – аналогично рис.1, 3 и 5.

Пояснения к выполнению:

1 Для ручного структурирования:

▪ отсортировать таблицу по любому какому-нибудь столбцу, например, по фамилии, должности, маршруту;

▪ создать структурную группу для строк 1-3. Для этого нужно выделить строки 1-3 и выполнить команду Данные\ Группа и Структура\ Группировать (для строк). Аналогично создать структуру для двух других групп (строки 4-6 и 7-10).

▪ создать структурные группы для столбцов. Для этого выделить 2-3 столбца (начиная от ячейки с именем и вниз - до последней строки) и выполнить команду Данные\ Группа и Структура\ Группировать (для столбцов). Аналогично повторить для оставшихся столбцов.

Сохранить результат на дискете.

2 Для автоструктурирования таблицы и введения нового уровня ручным способом:

2.1 Создание автоструктуры

▪ установить курсор в любую ячейку области данных, выполнить команду Данные\ Группа и Структура\ Создать структуру;

2.2 Введение нового иерархического уровня ручным способом – по строкам:

▪ выделить строки 1-3 (с данными) и выполнить команду Данные\ Группа и Структура\ Группировать;

▪ выделить остальные строки таблицы и выполнить команду Данные\ Группа и Структура\ Группировать;

В результате внутри первого уровня структурирования (1) появится второй уровень (2), созданный вручную.

3 Структурирование с подведением итогов (на примере таблицы на рис. 2, 5).

▪ отсортировать список записей таблицы рис. 2 командой Данные\ Сортировка, в окне Сортировка диапазона указать те столбцы, которые нужны для вычисления итогов. Для таблицы на рис. 5 сначала задавалась сортировка для № группы, затем для Код преп, чтобы получить итоги – средний балл по каждой группе, всей группе и по каждому преподавателю.

▪ подобрать вид итогов, которые можно сделать для таблицы своего варианта и отсортировать нужные для этого столбцы.

▪ создать 1-й уровень итогов – средний балл по группе. Для таблицы рис. 2 устанавливался курсор в любую ячейку списка и выполнялась команду Данные\ Итоги. В диалоговом окне Промежуточные итоги указывалось:

При каждом изменении в -№ группы

Операция:Среднее

Добавить итоги по:Оценка

Заменять текущие итоги:нет

Конец страницы между группами: нет

Итоги по данными:да.

После нажатия ОК был рассчитан средний балл по каждой группе и общее среднее.

▪ создать 2-й уровень итогов – средний балл по каждому преподавателю для каждой группы: установить курсор в любую ячейку списка и выполнить команду Данные\ Итоги. В окне Промежуточные итоги указать:

При каждом изменении в – Код преп

Операция:Среднее

Добавить итоги по:Оценка

Заменять текущие итоги:нет

Конец страницы между группами:нет

Итоги по данными:да.

После нажатия ОК будет рассчитан средний балл по каждому преподавателю в каждой группе.

Для получения промежуточных итогов можно скомпоновать любые поля исходного списка, например, на 1-м уровне – по маршруту (званию,

коду предмета…), на 2-м уровне – по № зачётной книжки (зарплате, должности…, на 3-м уровне - по дате, стоимости, телефону…).

Содержание отчёта

1 Название работы (в скобках – имя файла описания лаб. работы)

2 Цель работы

3 Содержание работы (порядок выполнения)

4 Структуры задания (на дискете) – три таблицы.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

Лабораторная работа №11

Сводные таблицы

Цель работы: научиться создавать и применять сводные таблицы

при работе с данными

Содержание работы:

1 Мастер сводных таблиц

2 Построение макета сводной таблицы

3 Работа со сводной таблицей

Мастер сводных таблиц

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

Пример 1 Дан список "Экзаменационная ведомость" (рис. 1).

Рисунок 1 Список "Экзаменационная ведомость"

Требуется построить сводную таблицу, в которой строками являются № группы, Фамилия И.О. и № зачётной книжки, а столбцами – Коды преподавателей. В ячейках на их пересечении нужно расположить оценки,

которые каждый студент получил у принимавшего экзамен преподавателя и вычислить средний балл для каждого преподавателя.

Сводная таблица строится с помощью Мастера сводных таблиц(рис. 2), который вызывается командой Данные\Сводная таблица. Мастер сводных таблиц осуществляет построение сводной таблицы в три этапа:

Рисунок 2 Мастер сводных таблиц, шаг 1

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

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

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

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

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

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

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

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

(параметр в квадратных скобках необязателен)

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

Шаг 3 Выбор места расположения и параметров сводной таблицы.

В появляющемся на четвертом шаге диалоговом окне (рис.3) можно

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

Рисунок 3 Окно Мастер сводных таблиц, шаг 3

Кнопка Параметры вызывает е окно Параметры сводной таблицы, в котором устанавливается вариант вывода информации в сводной таблице:

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

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

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

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

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

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

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

тельно.

Область для строк- поля размещаются сверху вниз, обеспечивая

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

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

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

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

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

Переместим в область для столбцов поля № группы, Фамилия И.О. и № зач. кн., в область для строк – поле Код преп., а в область данных – поле Оценки.

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

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

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

Рисунок 5Окно Вычисление поля сводной таблицы

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

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

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

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

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

Рисунок 6 Сводная таблица для примера 1

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

Функция	Результат
Отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках, поле и элемент
Доля	Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элементам.
Приведенное отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в стеках поле и элемент, нормированной к значе­нию этого элемента
С нарастающим итогом в поле	Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге
Доля от суммы по строке	Значения ячеек области данных отображаются в Процентах от итога строки
Доля от суммы по столбцу	Значения ячеек области данных отображаются в Процентах от итога столбца
Доля от общей суммы	Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы
Индекс	При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) *(Итог столбца)

Контрольные вопросы

1 Что называется сводной таблицей, сводом?

2 Для чего предназначены сводные таблицы?

3 Что помещается в областях для строк, столбцов, данных макета?

4 Как задать в сводной таблице вид вычислительного итога Сумма, Максимум, Произведение?

5 Как обновить данные в сводной таблице?

6 Как изменить структуру сводной таблицы (добавить или изменить поля строк, столбцов, данных?

Задание

Для таблицы своего варианта из лабораторной работы №9 (excel-9) Списки в Excel. Сортировка и фильтрация данных построить две сводные таблицы. Поля, помещаемые в области строк, столбцов и данных выбрать самостоятельно.

Результаты (две сводные таблицы) сохранить на дискете.

Содержание отчёта

1 Название работы

2 Цель работы

3 Содержание работы

4 Задание и своды (на дискете) – две таблицы.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

Лабораторная работа №12

Цель работы: научиться создавать консолидацию данных втаблицах

Содержание работы:

1 Консолидация данных по расположению

2 Консолидация данных по категориям

Дата добавления: 2014-10-15