Вычисление промежуточных итогов
Для вычисления значений итогов используется итоговая функция, например, СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены для нескольких типов функций одновременно.
Порядок подведения итогов:
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. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, нужно установить флажок Создаватьсвязи с исходными данными. Связи нельзя использовать, если исходная область и область назначения находятся на одном листе.
При консолидации данных по расположению заголовки категорий исходных областей автоматически не копируются в область назначения. Если необходимо в области назначения разместить заголовки, следует их скопировать или набрать вручную.