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

Цель работы:

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

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

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

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

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

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

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

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

Задание 10.1.

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

Рекомендации по выполнению:

1. Откройте файл Лабораторная работа 8 и сохраните его в своей папке под именем Лабораторная работа 10. Удалите все листы кроме листа Сортировка. Переименуйте лист Сортировка в Структура.

2. Создайте копию листа Структура:

• вызвать контекстное меню в области названия вкладки листа и выполнить команду Переместить или скопировать…;

• в окне Переместить или скопировать задать параметры (рис. 10.1):

  • в книгу – Лабораторная работа 10.xlsx;

  • перед листом – (переместить в конец);

  • Создать копию – .

Рис. 10.1. Параметры копирования листа

Нажать кнопку ОК.

Переименуйте лист Структура (2) в Группировка.

2. Перейти на лист Группировка. Выделить диапазон A3:I19.

Выполнить структурирование используя команду Данные (Структура)  Группировать.

В окне Группирование выбрать столбцы ( ) и нажать кнопку ОК.

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

Рис.10.2. Структура первого уровня списка

3. Выделить из нее таблицы второго уровня, содержащие поля: № склада, Поставщик, Категории, Наименование:

• выделить диапазон B3:D19;

• выполнить команду Данные (Структура)  Группировать  Столбцы.

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

Рис. 10.3. Структура третьего уровня списка

Щелкая по соответствующим кнопкам со знаком «–» и «+», можно сворачивать и разворачивать элементы структуры.

4. Удалим структуру. Для удаления структуры выбирается команда Данные  Структура  Разгруппировать  Удалить структуру.

5. Построить на данной таблице другую структуру. Выделим связные блоки полей и записей. Например, выделим сначала столбцы Поставщик и Категории (диапазон В3:С3) и выполнить группировку. Результатом операции будет группировка полей Поставщик и Категории.

Выделить столбцы Цена, у.е., Цена, руб. и Количество, выполнить группировку. Результатом операции будет группировка вышеперечисленных полей.

6. Выполните группировку по записям Оливария. Для этого необходимо вначале отсортировать записи таблицы по полю Поставщик:

• выделить диапазон А4:I19;

• выполнить команду Данные (Сортировка и фильтр)  Сортировка. Указать Сортировать по – Поставщик, Порядок – От А до Я. Нажать кнопку ОК.

Выделить поля таблицы с названием поставщика – Оливария (диапазон В14:В16 и выполнить группировку по строкам.

Свернуть (рис. 10.4) и развернуть классы структуры.

Рис. 10.4. Свернутая структура списка

Задание 10.2.

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

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

Рекомендации по выполнению:

1. Откройте файл Лабораторная работа 9-Консолидация, выделите лист Ведомость.

Вызвать контекстное меню в области названия вкладки листа Ведомость и выполнить команду Переместить или скопировать…

Рис. 10.5. Параметры копирования листа

В окне Переместить или скопировать задать параметры (рис. 10.5):

• в книгу – Лабораторная работа 10.xlsx;

• перед листом – (переместить в конец);

• Создать копию – .

Нажать кнопку ОК.

2. Выполнить над заполненным списком Автоструктурирование:

Данные (Структура)   Создание структуры (рис. 10.5).

Рис. 10.5. Автоструктурирование таблицы Ведомость оплаты труда

3. Свернуть структуру по нижним уровням (рис 10.6).

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

4. Свернуть структуру по верхнему уровню. Получим только одно поле К выдаче (рис 10.7).

Рис.10.7. Свернутая структура по верхнему уровню

Задание 10.3.

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

Выполним структурирование списка Ведомость оплаты труда с подсчетом Итогов.

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

Рекомендации по выполнению:

1. В файле Лабораторная работа 10 создать копию листа Ведомость.

Переименовать лист Ведомость (2) в Итоги-ФИО.

На листе Итоги-ФИО удалить структуру.

2. Выполнить сортировку записей в поле Фамилия и инициалы (т.е. разбить записи на классы):

• выделить диапазон ячеек А3:К12;

• выполним команду Данные (Сортировка и фильтр)  Сортировка, в окне установите сортировку по полю Фамилия и инициалы.

3. Выполнить команду Данные (Структура)  Промежуточный итог. В открывшемся окне установить параметры (рис. 10.8):

• При каждом изменении в: – Фамилия и инициалы;

• Операция: – Сумма;

• Добавить итоги по: – Начислено, Удержано, К выдаче, тыс. руб.

• Заменить текущие итоги – ;

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

Нажать кнопку ОК.

Рис. 10.8. Промежуточные итоги

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

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

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

Рис. 10.10. Итоговые строки

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

6. В файле Лабораторная работа 10 создать копию листа Итоги-ФИО.

Переименовать лист Итоги-ФИО (2) в Итоги-Месяц.

На листе Итоги-Месяц удалить структуру:

• выделите список;

• выполнить команду Данные (Структура)  Промежуточный итог  Убрать все.

7. Отсортировать данные по месяцам:

• выделить диапазон А3:К12;

• выполнить команду Данные (Сортировка и фильтр)  Сортировка;

• в окне установить параметры:

  • Сортировать по – Месяц;

  • Порядок – Настраиваемый список…; в окне Списки выделить – Январь, Февраль, Март, …, нажать кнопку ОК.

• После установления порядка сортировки нажать кнопку ОК.

8. Подвести итоги помесячно. вычисляя средние значения полей Начислено, Удержано, К выдаче, тыс. руб.:

• выполнить команду Данные (Структура)  Промежуточный итог. В открывшемся окне установить параметры:

• При каждом изменении в: – Месяц;

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

• Добавить итоги по: – Начислено, Удержано, К выдаче, тыс. руб.

• Заменить текущие итоги – ;

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

Нажать кнопку ОК (рис. 10.11).

Рис. 10.11. Перегруппировка данных с подсчетом промежуточных итогов

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

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

Задание 10.4.

Упорядочение данных в списках с помощью команды Промежуточные итоги.

Рекомендации по выполнению:

1. Откройте файл Лабораторная работа 8 и сохраните его в своей папке под именем Лабораторная работа 10-1.

2. Переименуйте лист Сортировка в Итоги.

3. На листе Итоги выделить диапазон А3:I19.

4. Выполнить команду Данные (Структура) → Промежуточные итоги. Задать параметры итогов:

• При каждом изменении в: – № склада;

• Операция – Сумма;

• Добавить итоги по: – Стоимость, руб.;

• Заменить текущие итоги – ;

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

Нажать кнопку ОК.

После этого список примет следующий вид (рис. 10.12).

Рис. 10.12. Результат вычисления промежуточных итогов

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

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