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

Для получения различной итоговой информации в 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 -Таблица-пример после вычисления промежуточных итогов

Консолидация данных

Консолидация − это еще один из способов получения итоговой информации, т.е. агрегирование согласно выбранной функции обработки данных, представленных в исходных областях-источниках. Результат консолидации находится в области назначения. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей-источников, а сами источники могут быть закрыты во время консолидации.

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

Существуют следующие варианты консолидации:

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

− консолидация по категориям для различающихся по расположению данных;

− консолидация внешних данных.

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

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

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

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

Переключатель Создавать Связи с Исходными Данными создает при консолидации связи области назначения к областям-источникам.

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

а)

б)

в)

Рисунок 103 - Данные об объемах продаж в филиалах: а) − Оренбургском, б) − Бузулукском, в) − Орском

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

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

1. В строке «Всего» для каждой таблицы просуммируйте данные.

2. На Листе4 активизируйте ячейку А4, которая послужит началом диапазона ячеек с итогами.

3. Активизируйте команду Консолидация меню Данные.

4. В одноименном окне Консолидация в поле Функция выберите функцию, которая будет использоваться при объединении данных (а нашем примере − Сумма).

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

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

7. Введите в поле Ссылка адреса всех консолидируемых диапазонов.

8. Установите опцию значения левого столбца в области использовать в качестве имен диалогового окна Консолидация. Таким образом вы зададите консолидацию по именам. При этом значения в строках с одинаковыми метками будут просуммированы.

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

Рисунок 104 - Диалоговое окно Консолидация, в котором заданы параметры консолидации

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

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

Задания для самостоятельного выполнения:

1. Таблицу с консолидированными данными отформатировать, задать границы (рис.105).

2. Построить диаграмму на основе таблицы с консолидированными данными.