Составление консолидированных отчетов

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

При консолидации данных вам необходимо выбрать место для размещения итогового отчета, функцию (например, суммирование) и источники данных для консолидации. Итоговый отчет можно расположить на одном листе с исходными данными, на другом листе той же книги или вообще в другом файле. Используемая функция зависит от типа данных и вида составляемого отчета. Например, для подведения итогов по таблице Ассортимент необходима функция СУММ. Источниками данных могут служить как рабочие листы одной книги, так и листы сразу нескольких рабочих книг. В любом случае необходимо следить за тем, чтобы данные каждого листа имели одни и те же заголовки строк и столбцов. Иначе вместо итогового отчета вы получите список детальных элементов.

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

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

Упражнение 5. Подготовьте данные для консолидации

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

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

1. Дважды щелкните на ярлычке листа Факсы (2), присвойте ему новое имя «Отчет» и измените таблицу, как показано на следующей иллюстрации (рис 3.5).

Рис. 3.5 Размещение итогового отчета

2. Выделите ячейку A6 и в группе Работа с данными вкладки Данныещелкните на кнопкеКонсолидация. Откроется диалоговое окно Консолидация.

3. Убедитесь, что из раскрывающегося списка Функция выбран пункт Сумма.

Упражнение 7. Выделите данные для консолидации

1. Щелкните в текстовом поле Ссылка, а затем на вкладке листа Факсы.

2. Переместите диалоговое окно Консолидация в сторону и выделите блок ячеек B6:F16.

Название листа Факсы и адрес блока ячеек автоматически заносятся в поле Ссылка. Блок ячеек B6:F16 содержит данные ассортимента факсов и строку заголовков.

3. Щелкните на кнопке Добавить. Содержимое поля Ссылка внесено в окно Список диапазонов.

4. Щелкните на ярлычке листа Ксероксы.

5. Выделите на нем блок ячеек B6:F16 (если он не выделен). Щелкните на кнопке Добавить.

Обе таблицы консолидации занесены в Список диапазонов.Диалоговое окно Консолидация изображено на рис. 3.6.

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

Упражнение 8. Добавьте метки и завершите консолидацию

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

2. Щелкните на кнопке ОК. Диалоговое окно Консолидация закроется. Теперь все данные по факсам и ксероксам сведены в единую таблицу, и вы можете сравнить суммарную стоимость, объемы продаж и полный доход от сбыта персональной, деловой и профессиональной оргтехники. Возможно, вам потребуется изменить ширину столбцов, чтобы увидеть все данные. Рабочий лист Отчет представлен на рисунке 3.7.

3. Сохраните файл в своей папке.

Рис.3.7 Консолидированные данные

Контрольные вопросы

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

2. Как удалить промежуточные итоги?

3. Что такое консолидация данных?

4. Можно ли расположить итоговый отчет на одном листе с исходными данными?

5.Как добавить вложенные итоги?

Заполните таблицу
(заполненная таблица помещается в отчет
по вычислительной практике)

Краткое изложение задания 3

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

Задание 4. Анализ данных с помощью сводных таблиц

Вы научитесь:

· подводить итоги с помощью сводной таблицы;

· изменять структуру данных без перестройки рабочего листа;

· придавать документам профессиональный вид с помощью форматирования;

· строить диаграммы сводных таблиц.

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

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

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

Начало работы

Откройте в папке Мои документы свою папку и выберите файл с именем Задание3.xls, а затем сохраните его под новым именем Задание4.xls.

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

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

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

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

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