Подведение промежуточных итогов

Теоретическая справка

Лабораторная работа №14. Анализ списка с помощью подведения промежуточных итогов и консолидации данных.

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

Для получения различной итоговой информации в MS Excel предназначена команда Данные→Итоги. Перед выполнением этой команды данные должны быть представлены в виде списка. Но прежде чем подводить итоги, нужно обязательно отсортировать список.

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

Создание промежуточных итогов основано на предварительной сортировке записей списка, при этом важен порядок сортировки − состав и подчиненность ключевой сортировки. Если сортировка была выполнена по полям: поле 1, поле 2, поле 3, т.е. поле 1 является самым старшим в сортировке, поле 2 определяет порядок сортировки строк список при одинаковых значениях поля 1, а поле 3 задает порядок сортировки при одинаковых значениях и поля 1, и поля 2, − то и подведение итогов имеет свой жесткий порядок: поле 1, поле 2, или поле 1.

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

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

Команда Итоги в меню данные позволяет выполнять следующие действия:

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

− для одинаковых значений поля группировки можно формировать итоги по одному или нескольким полям списка, при этом вид операции определяет, какие поля могут использоваться для подведения итогов. Так, для операций Сумма, Среднее, Максимальное, Минимальное и т. п. могут выбираться поля только числового типа. Для операции Количество значений − поля любого типа (числовые, текстовые, даты).

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

Рисунок 97 - Таблица-пример

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

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

1. Установите указатель ячейки в любой ячейке таблицы и активизируйте команду Сортировка из меню Данные. Сортируемый диапазон будет автоматически выделен. Задайте в диалоговом окне Сортировка критерии сортировки, как показано на рис.98.

Рисунок 98 - Диалоговое окно Сортировка Диапазона

После нажатия кнопки ОК в диалоговом окне Сортировка диапазона данные в таблице будут отсортированы (в столбце Продавец фамилии продавцов будут упорядочены по алфавиту).

2. Активизируйте одну из ячеек таблицы и выберите в меню Данные команду Итоги. В результате откроется диалоговое окно Промежуточные итоги. В поле списка При каждом изменении в выберите заголовок столбца Продавец, для которого необходимо вычислять промежуточные итоги после каждого изменения данных на рабочем листе. Для вычисления итоговой суммы в поле списка Операция установите функцию Сумма. Укажите также столбец для вычисления итогов, например столбец Оборот. Для этого активизируйте в области Добавить итоги по опцию Оборот и выключите опцию Дата, как показано на рис.99. В завершении щелкните на кнопке ОК.

Рисунок 99 - Диалоговое окно Промежуточные итоги

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

Рисунок 100 - Таблица-пример после автоматического вычисления итогов

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

Таблица 23 – Функции для обработки данных с помощью промежуточных итогов

Функция	Назначение
Сумма	Суммирует все значения и выдает общий итог
Кол-во значений	Определяет количество элементов ряда
Среднее	Вычисляет среднее арифметическое для ряда значений
Максимум	Определяет наибольшее значение в ряде значений
Минимум	Определяет наименьшее значение в ряде значений
Произведение	Вычисляет произведение всех значений ряда
Кол-во чисел	Устанавливает количество ячеек, содержащих числовые значения, в выделенной области
Смещенное отклонение	Определяет значение стандартного отклонения выборки
Несмещенное отклонение	Определяет значение стандартного отклонения для генеральной выборки
Смещенная дисперсия	Определяет значение дисперсии, если данные образуют выборку
Несмещенная дисперсия	Определяет значение дисперсии для генеральной выборки

3. При вычислении итогов таблица структурируется. Создание уровней структуры приводит к повышению наглядности таблицы. Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке второго уровня структуры, вследствие чего данные третьего уровня (исходные значения) будут скрыты (рис.101). Для восстановления отображения исходных значений необходимо выполнить щелчок на кнопке третьего уровня.

Рисунок 101 - Данные третьего уровня скрыты

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

4. Рассмотрите еще одну возможность MS Excel − комбинирование нескольких итогов (применение вложенных итогов). Добавьте в таблицу еще один показатель − количество автомобилей, проданных каждым продавцом.

Для этого:

a) Повторно откройте диалоговое окно Промежуточные итоги.

b) В поле При каждом изменении в выберите элемент Продавец.

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

Примечание: Для того чтобы в таблице отображались все итоги (рис.102), перед нажатием кнопки ОК следует выключить опцию Заменить текущие итоги.

Для удаления строк с итоговыми значениями предназначена кнопка Убрать все, расположенная в диалоговом окне Промежуточные итоги.

Рисунок 102 -Таблица-пример после вычисления промежуточных итогов

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