1. Среднее значение цены принтеров и сканеров;

2. Количество разновидностей наименований принтеров и сканеров;

3. Суммы, потраченные на покупку каждого типа товара (Рис. 6 )

Рис.6. Суммы, потраченные на покупку каждого типа товара.

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

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

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

Цель задания: научиться составлять консолидированные отчеты.

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

В качестве примера мы будем использовать предыдущий пример. Разнесите только данные о принтерах и данные о сканерах на два рабочих листа, переименуйте “Лист1” в “Принтеры”, а “Лист2” в “Сканеры”.

Составим консолидированный отчет по закупкам принтеров и сканеров (данным, размещенным на разных листах рабочей книги). Для этого:

• Выберите “Лист3” и присвойте ему новое имя “Отчет”. Создайте “шапку” новой таблицы (Рис.8);

• Выделите ячейку A7 (“Товар”), и в меню Данные выберите команду Консолидация. В открывшемся одноименном диалоговом окне укажите консолидируемые диапазоны ячеек и вид операции (функцию) (Рис.7.):

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

• убедитесь, что в поле Функция находится функция Сумма,

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

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

• Нажмите ОК, в полученном консолидированном отчете столбцы “Тип” и “Наименование” можно скрыть (Рис.8)

Рис.8. Консолидированный отчет

При изменении значений в исходных диапазонах ячеек консолидацию следует повторить. Если структура исходных таблиц (диапазонов ячеек) не меняется, то постоянного повторения этой процедуры можно из6ежать путем связывания консолидированных данных с исходными. В этом случае в консолидированном диапазоне ячеек будут содержаться ячейки с внешними ссылками на исходные данные. При изменении данных в исходном диапазоне ячеек автоматически будут изменяться и консолидированные данные. Для установки связей между консолидированными и исходными данными следует при задании консолидации установить опцию Создавать связи с исходными данными в диалоговом окне Консолидация (Рис.7).

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

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