Вычисление промежуточных итогов

Для вычисления значений итогов используется итоговая функция, например, СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены для нескольких типов функций одновременно.

Порядок подведения итогов:

1. Отсортировать список по столбцам, для которых необходимо подвести итоги.

2. Указать ячейку в этом списке.

3. В пункте меню Данные выбрать пункт Итоги.

4. Выбрать столбец, содержащий группы, по которым необходимо подвести итоги, в поле При каждом изменении в. Это должен быть столбец, по которому проводилась сортировка списка на шаге 1.

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

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

Пример 3. В базе данных (рис. 1) нужно подвести итоги по полю Продавцам - вывести сумму по столбцу Сумма. Для этого необходимо выполнить команду Данные – Итоги. В диалоговом окне Промежуточные итоги установить параметры в соответствии с рис.

Диалоговое окно Промежуточное окно

Результат выполнения операции может выглядеть так.

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

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

Результат вычисления промежуточных итогов по полю

Продавцам в развернутом виде

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

Диалоговое окно Промежуточное окно

Результат выполнения операции может выглядеть так как показано на рис.

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

Или в развернутом виде.

Результат вычисления промежуточных итогов

по полю Продукт в развернутом виде

Работа со сводными таблицами

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

Сводные таблицы могут использоваться:

1. Для обобщения большого количества однотипных данных.

2. Для реорганизации данных (с помощью перетаскивания).

3. Для отбора и группировки данных.

4. Для построения диаграмм.

Методика создания сводной таблицы:

1. Выбрать место для сводной таблицы, т.е. установить указатель в необходимое место на рабочем листе.

2. Выполнить команду Данные – Сводная таблица.

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

Диалоговое окно Мастер сводных таблиц и диаграмм

4. На втором шаге определить исходный диапазон и нажать кнопку Далее.

Окно определения исходного диапазона

5. На третьем шаге Мастера сводных таблиц и диаграмм необходимо нажать кнопку Макет, чтобы сформировать макет сводной таблицы (т.е. задать страницу, строки, столбцы и вычисляемые поля сводной таблицы).

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

6. В диалоговом окне Мастер сводных таблиц – Макет поля списка представлены в виде кнопок, которые нужно просто перетащить в соответствующие области макета таблицы и нажать кнопку ОК.

Диалоговое окно Мастер сводных таблиц – макет

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

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

Данные – поле (поля), для которого подводится итог в сводной таблице.

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

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

7. После проведения всех подготовительных операций нажать кнопку ОК и затем Готово.

Пример 5. По исходной базе данных

1. Построить сводную таблицу с макетом, приведенным на рис. 33. Сводную таблицу расположить на том же листе, что и исходная таблица.

2. В созданной сводной таблице поле Продукт переместить в строки, поле Цена добавить в столбец.

3. Сгруппировать продукты в две группы: Хлебобулочные продукты (хлеб, булка) и Молочные продукты (молоко, кефир), и переименовать их.

Макет сводной таблицы для исходной базы данных

Для построения сводной таблицы для примера необходимо:

· установить курсор в любую ячейку исходной таблицы.

· выполнить команду Данные – Сводная таблица.

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

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

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

В созданной сводной таблице поле Продукт переместить в строки, поле Цена добавить в столбец. Для этого на созданной сводной таблице в контекстном меню выбрать пункт Мастер сводных таблиц в диалоговом окне Мастер сводных таблиц и диаграмм - шаг 3 из 3 нажать кнопку Макет… и поле Продукт переместить в строки, поле Цена добавить в столбец.

Изменение макета сводной таблицы

В результате, измененная сводная таблица примет следующий вид:

Вид измененной сводной таблицы

На основании созданной таблицы сгруппировать продукты в две группы: Хлебобулочные продукты (хлеб, булка) и Молочные продукты (молоко, кефир), и переименовать их. Для этого в сводной таблице, удерживая нажатой клавишу Ctrl, необходимо выделить те продукты, которые должны входить в одну группу. Затем в контекстном меню выбрать Группа и структура – Группировать.

Создание группы с сводной таблице

Появившуюся структуру Группа 1 нужно переименовать. Для этого необходимо перевести курсор на ячейку с текстом Группа 1 и ввести новый текст в строке формул. В данном случае нужно ввести следующее: Хлебобулочные продукты.

Переименовывание группы в сводной таблице

Аналогично переименовывается Группа 2, для которой вводится новое название Молочные продукты.

В результате выполнения операции группировки будет сформирована таблица, показанная на рис.

Результат выполнения группировки продуктов

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

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

Предусмотрены разные способы консолидации в зависимости от ситуации:

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

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

Для создания итоговой таблицы необходимо выполнить следующие действия:

1. Указать верхнюю левую ячейку области назначения консолидируемых данных.

2. В пункте меню Данные выполнить команду Консолидация.

3. Ввести в поле Функция из раскрывающегося списка функцию, которую следует использовать для обработки данных.

4. Ввести в поле Ссылка исходную область консолидируемых данных.

5. Нажать кнопку Добавить.

6. Повторить шаги 4 и 5 для всех консолидируемых исходных областей.

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

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

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