Выполним над заполненным списком Автоструктурирование. Данные / Группа и структура / Создать структуру.

Лабораторная работа №66. Вычисление итогов и структурирование данных.

Цель работы: Изучение инструментов для вычисления промежуточных итогов на основе одной или нескольких таблиц.

Задачи:

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

Теоретические сведения.

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

Структурирование таблиц можно проводить как по строкам, так и по столбцам. На одной и той же таблице можно построить несколько вариантов структур.

Требования к структурируемым данным:

1. Структурируемые данные находились в смежных полях (столбцах) или записях (строках), которые образуют классы структуры;
2. Пересечение классов данных должно быть пустое множество, в противном случае два класса сливаются в один (связные классы).

Выполнение структурирование выполняется с помощью команд: Данные / Группа и структура; Данные / Итоги. Последняя команда не только выполняет структурирование, но и позволяет выполнять расчеты над записями таблицы.

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

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

Рассмотрим список ЗАКАЗЫ, созданный в предыдущих работах. Поля списка содержат сведения о заказчиках (левая часть списка), заказах (центральная часть) и исполнении заказа (правая часть).

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

Задание 1. Структурирование таблицы по полям и записям.

1. Выделим весь список.
2. Выберем команду Данные / Группа и структура / Группировать / Столбцы. Получим структуру первого уровня (вся таблица). Обратите внимание на появившуюся линию уровня Охватывающую все поля списка и заканчивающуюся кнопкой со знаком « - ». Выполнив щелчок по кнопке можно свернуть список.

1. Выделим из нее таблицы второго уровня, содержащие поля

Месяц

Дата

Номер заказа

Номер товара

Наименование товара

1. Выберем команду Данные / Группа и структура / Группировать / Столбцы. Получим структуру второго уровня. Продолжая процесс группировки по столбцам, а затем по строкам, получите следующую иерархию:

1. Щелкая по соответствующим кнопкам со знаком «- » и «+», сверните и разверните элементы структуры.
2. Для удаления структуры выбирается команда Данные / Группа и структура / Удалить структуру.
3. Построим на данной таблице другую структуру. Выделим связные блоки полей и записей. Например, выделим сначала столбцы Месяци Дата и выполним группировку. Результатом операции будет группировка полей Месяц и Дата.
4. Выделим столбцы Номер товара и Наименование товара. Выполним группировку. Результатом операции будет группировка полей Номер товара и Наименование товара.
5. Выполните группировку по записям ОАО Финиш.
6. Сверните и разверните классы структуры.

Задание 2. Автоструктурирование.

Автоструктурирование выполняется только для таблиц содержащих формулы.

Требование: ссылки в формулах должны быть на ячейки в смежных левых полях или смежных верхних записях.

1. Сформируем и заполним таблицу ВЕДОМОСТЬ.

2. В ячейки E4, F4, G4, H4, I4, J4, K4 вставьте формулы и скопируйте их на остальные ячейки списков.

Выполним над заполненным списком Автоструктурирование. Данные / Группа и структура / Создать структуру.

4. Свернем структуру по нижним уровням.

5. Свернем по верхнему уровню. Получим только одно поле «К выдаче»

6. Удалите структуру.

Задание 3. Структурирование с подсчетом ИТОГОВ.

Выполним структурирование списка ВЕДОМОСТЬ с подсчетом Итогов.

Для выполнения структурирования необходимо определить основное поле, по которому будет проводиться структурирование. Пусть это будет поле Ф.И.О.

1. Выполним сортировку записей в поле, тем самым разобьем записи на классы. Для этого выделим диапазон ячеек А4:К12, выполним команду Данные / Сортировка, в окне установите сортировку по полю ФИО.

2. Выполним команду Данные / Итоги. В открывшемся окне установим

· Заголовок изменяющегося поля;

· Операцию;

· Поля с вычисляемыми итогами (Начислено, Удержано, К выдаче);

· Итоги под данными;

· Другие опции.

3. Получим структуру. Строки с итогами выделены жирным шрифтом.

4. Свернем структуру. Получим только Итоговые строки.

5. Выполнив свертку еще раз, получим одну строку Общих итогов.

6. Удалите структуру, выделите список, затем введите команду Данные / Итоги / Убрать все.

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

8. Подведите Итоги, изменив функцию Суммы на функцию Среднее.

9. Перегруппируйте данные (проведите сортировку по месяцам) и выполните подсчет Итогов по месяцам.

10. Свернем структуру. Получим только Итоговые строки.

11. Выполнив свертку еще раз, получим одну строку Общих итогов.

12. Измените функцию Суммы на функцию Среднее.

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

1. Что такое структурирование таблиц, для чего оно применяется.
2. Какие требования к подклассам данных структуры.
3. Как вы понимаете термин «Смежные классы».
4. Как выполняется «ручное структурирование»
5. Основные требования для автоструктурируемых таблиц. Почему нельзя выполнить Автоструктурирование для таблицы «Заказы»
6. Что такое промежуточные итоги.
7. Для чего нужно сортировать записи перед просчетом итогов.

Задание для самостоятельной работы.

1. Создайте на одном из рабочих листов список.

1. Выполните Автоструктурирование.
2. Выполните «ручное» структурирование по полям Предмет - Семестр, План в часах, Фактически в часах и записям «Весенний - Осенний».
3. Подсчитайте Итоги по изменяемым полям: а) Предмет; б) Семестр.