Создание промежуточных и общих итогов

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

Разделение данных на несколько столбцов

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

С помощью команды Данные→Работа с данными→Текст по столбцамвызывается мастер, позволяющий указать параметры разбивки. Можно выбрать разделитель для данных, имеющих одинаковые знаки-разделители, либо указать параметр «фиксированная ширина» (если в столбце данные имеют одинаковую длину) и указать точное местоположение разделителя.

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

Рис. 7.5. Диалоговое окно Консолидация

Если в разных листах книгах или даже разных книгах имеются однотипные значения, которые нужно разместить в одном месте или провести с ними однотипные операции, это можно сделать при помощи функции Консолидация. При ее использовании данные из указанных источников соби­раются в одном месте и лю­бые общие значения будут объединяться, как указано. Это позволяет более нагляд­но анализировать итоговые данные.

Чтобы выполнить консо­лидацию, сначала нужно выделить первую ячейку места, в котором будут расположены консолидированные данные, затем выбрать команду Данные →Работа с данными →Консолидация . В диалоговом окне (рис. 8.5) необходимо выбрать диапазоны ячеек (поля ссылка, список диапазонов) и тип предстоящей консолидации (поле Функция), а также указать использование в качестве имен некоторых значений диапазона данных и установить связь с исходными данными (чтобы при изменении исходных данных производилось обновление консолидированных). После задания параметров итоговые данные будут размещены в указанных ячейках.

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

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

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

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

Чтобы добавить итоги для списка данных нужно:

1. Выделить исходный диапазон ячеек.

2. Выбрать команду Данные →Структура → Промежуточные итогидля вызова соответствующего диалогового окна (рис. 8.6).

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

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

4. В списке Операциявыберите функцию, при помощи которой будут подводиться итоги.

5. В группе флажков Добавить итоги пок укажите, по каким полям должны вычисляться итоги. Отметьте только те поля, которые вы хотите просуммировать.

6. Нажмите кнопку ОК. Excel вставит в рабочий лист строки с промежуточными итогами. Общий итог по всей таблице появится в ее нижней части.

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

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