Вычисление итогов и структурирование данных

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

 

Рис.1.1

 

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

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

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

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

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

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

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

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

Рис.1.2

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

Технология выполнения работы

Задание 1. Создание списков.

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

 

Создание списка КЛИЕНТЫ.

Создайте рабочую книгу (Файл / Создать) (рис.1.3).

Присвойте первому рабочему листу имя Клиенты.

Введите в ячейки A1: F1 следующие заголовки:

Название фирмы

Код

Контактная персона

Город

Телефон

Скидка (%)

 

Рис.1.3 Создание списка Клиенты

 

Отформатируйте таблицу, используя пункт меню Формат / Автоформат. В списке форматов выберите Список1.

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

 

Создание списка ТОВАРЫ.

Перейдите на второй лист рабочей книги и присвойте ему имя ТОВАРЫ.

Список ТОВАРЫ должен состоять из трех столбцов: Номер, Наименование товара, Цена (USD).

В ячейки А1-С1 введите соответствующие заголовки.

Введите записи в список ТОВАРЫ.

Отформатируйте таблицу аналогично списку КЛИЕНТЫ (рис.1.4).

Рис.1.4 Создание списка Товары

Создание списка ЗАКАЗЫ.

Перейдите на третий лист рабочей книги и присвойте ему имя ЗАКАЗЫ.

Определяем следующую структуру списка (рис.1.5):

Рис.1.5 Структура списка

 

Введите данные по следующим полям: Месяц, Дата, Номер заказа, Номер товара, Код заказчика, Количество (рис.1.6).

Рис.1.6. Создание списка заказы

 

Отформатируйте таблицу по своему усмотрению.

Заполним автоматически столбцы Наименование товара, Фирма, Сумма, Скидка, Оплачено в списке ЗАКАЗЫ, используя данные из соответствующих столбцов списка ТОВАРЫ и списка КЛИЕНТЫ соответственно.