Автоматическое подведение промежуточных итогов

Назначение промежуточных итогов состоит в обобщении данных. При этом не надо писать формулы. MS Excel автоматически создаёт необходимые математические выражения, вставляет строки промежуточных (общих) итогов. Для подведения промежуточных итогов (рис. 10.5) необходимо:

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

2. Указать, как будет идти группировка данных, выбрав значение в списке «При каждом изменении в». Предварительно столбец, по которому будет идти группировка, должен быть отсортирован.

3. Из списка «Операция». Выберите функцию, которая будет использоваться при обработке данных: сумма, количество значений, среднее, максимальное, минимальное, произведение, количество чисел, несмещённое отклонение, не смещённая дисперсия.

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

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

Флажок «Конец страницы между группами» вставляет жёсткий разделитель страниц между группами, что даёт возможность выводить каждую группу с итогами на отдельной странице для печати.

Флажок «Итоги под данными» управляет размещением итогов (над или под данными). «Убрать всё»- убирает промежуточные итоги. Возможно выполнение команды Данные - Итоги для подгрупп одной группы данных. Это позволяет проводить вложенные промежуточные итоги. Флажок «Заменить текущие итоги» должен быть сброшен.

Рис. 10.5. Параметры и результат работы команды Итоги

Вместе с итогами MS Excel создаёт структуру, при использовании которой можно управлять отображением результатов.

Сводная таблица.

Это средство, предназначенное для упорядочивания информации. Она не просто обобщает данные, а даёт возможность произвести глубокий анализ имеющейся информации. Сводная таблица создаётся при помощи Мастера сводных таблиц командой меню Данные – Сводная таблица. Работа с Мастером выполняется по шагам:

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

2. Затем указать диапазон, содержащий исходные данные.

3. При помощи кнопки «Макет» сконструировать конфигурацию сводной таблицы (рис. 10.6).

Рис. 10.6. Макет сводной таблицы

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

4. При помощи кнопки «Параметры» задать необходимые параметры сводной таблицы, таких как: название таблицы; подведение общих итоги по столбцам (строкам); сохранять данные (при установке флажка, в файле записывается копия данных, по которым построена таблица); автоматический формат таблицы и т. д. (рис. 10.7).

Рис. 10.7. Диалоговое окно Параметры сводной таблицы

5. Указать место размещения сводной таблицы.

В результате получим сводную таблицу следующего вида (рис.10.8).

Рис. 10.8. Пример сводной таблицы

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

Рис. 10.9. Диалоговое окно Вычисление поля сводной таблицы

Кнопка «Формат» позволяет задать числовые форматы для отображения данных.

Кнопка «Дополнительно» позволяет использовать дополнительные вычисления:

1. Выводится разница между результатом и элементом, заданным в списках «Поле» и «Элемент».

2. «Доля» - выводится результат, деленный на указанный элемент заданного поля, выражен в процентах.

3. «Приведённое отличие» - разница между результатом и указанным элементом заданного поля, делённая на этот же элемент, выражена в процентах.

4. С нарастающим итогом «Поле». В ячейке будет накапливаться результат для указанного поля, вычисляя общую сумму.

5. «Доля от суммы по строке» - результат, делённый на общий итог по строке, выраженный в процентах.

6. «Доля от суммы по столбцу» - результат, делённый на общий итог по столбцу, выражен в процентах.

7. «Индекс» - вычисляется так: результат умножается на общий итог и делится на произведение итога по столбцу и итога по строке.

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

С помощью списка «Проект» можно просматривать результаты по отдельному проекту или по всем вместе.

Пользователь может изменять имена полей и элементов свободной таблицы. Это достигается обычным редактированием содержимого ячейки. На исходных данных изменения не отображаются. При редактировании исходных данных сводная таблица автоматически не меняется. Чтобы отобразить новые результаты в сводной таблице, нужно выделить любую ячейку внутри сводной таблицы и нажать кнопку «Обновить данные», которая находится на панели инструментов «Сводная таблица».

Нельзя добавлять – удалять строку, столбцы, ячейки там, где находится сводная таблица, если они пересекают область сводной таблицы. Однако их можно скрывать, изменять размеры. Сводную таблицу можно удалять только полностью. Имеется возможность группировки элементов в категорию более высокого уровня. Для этого их необходимо выделить и нажать кнопку «Группировать». Кнопки «Скрыть – показать детали» управляют отображением отдельных элементов. Для группировки чисел по диапазонам или элементов по временным значениям можно воспользоваться последовательностью: меню Данные – Группа и структура – Сгруппировать.

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

Консолидация.

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

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

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

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

В качестве ссылок могут использоваться ссылки на другую рабочую книгу. В этом случае формат ссылки имеет вид: [имя рабочей книги] имя рабочего листа! адрес ячейки.

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

В случае консолидации данных по столбцам (строкам) с одинаковыми именами, флажки следует установить. При установленном флажке «Создавать связь с исходными» результаты консолидации будут обновляться при изменении данных, а в области назначения будет создана структура.