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

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

Переменная часть таблиц (столбцы Объем и Дата) должна соответствовать данным, приведенным на рисунке 103. Переименуйте листы, дав им соответствующие имена («Октябрь», «Ноябрь», «Декабрь»).

2. Для одновременного просмотра листов одной рабочей книги, как показано на рисунке 103, необходимо воспользоваться командой Вид→Окно→ Новое окно. Перейдите на лист «Ноябрь», нажмите на кнопку команды Новое окно. Перейдите на лист «Декабрь», нажмите на кнопку команды Новое окно. В результате этих действий в заголовке окна будет имя Книга2:1[2], и появятся два дополнительных окна Книга2:2 и Книга2:3, о чем свидетельствуют кнопки программы MS Excel на панели задач (рис. 104).

Рис.103. Исходные данные для таблиц-источников

Рис.104. Кнопки программы после создания новых окон

Чтобы расположить окна в определенном порядке относительно друг друга, воспользуйтесь командой Упорядочить все области Окно. Появится окно, в котором отметьте нужный вариант расположения окон (рис. 105). Затем измените размер окон по своему желанию.

Рис.105. Окно Расположение окон команды Упорядочить все

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

3. Создайте новый лист, дав ему имя «Конс_данные». В первую строку листа внесите название таблицы «Поставки товаров (консолидировано)» (рис.107), в ячейки A2 и B2 названия столбцов Город и Объем соответственно. На этом листе установите указатель ячейки в первую свободную ячейку (А3).

3.1. Вызвать диалоговое окно Консолидация посредством команды Данные→Работа с данными→Консолидация (рис.105), и, последовательно указывая в области Ссылка необходимые адреса консолидируемых областей, сформировать их полный список, состоящий из трех записей, как представлено на рисунке 106.

Рис.105. Команда Консолидация вкладки Данные

Рис.106. Заполнение диалогового окна Консолидация меню Данные

3.2. В области Функция оставить функцию вычисления суммы. В области Использовать в качестве имен указать, что в качестве имен (названий строк) будут выбираться данные из первого столбца – столбца A консолидируемой области (рис.106). Также указать на необходимость создания динамической связи с исходными данными.

3.3. Выполнить консолидацию, нажав кнопку ОК. Сравнить полученные результаты с результатами, приведенными на рисунке 107.

3.4. Просмотрите созданную структуру, последовательно показывая или скрывая уровни этой структуры с помощью кнопок и . Откройте второй (внутренний) уровень для поставщиков из Иркутска и Ангарска. Сравните полученный результат с результатом, представленным на рисунке 108.

3.5. Меняя данные в ячейках листов «Октябрь», «Ноябрь», «Декабрь», проследить за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д. на листе «Конс_данные».

3.6. Сохраните в своей папке созданную книгу с четырьмя листами под именем Конс_1.

Рис.107. Результат консолидации данных

Рис. 108. Просмотр созданной структуры Рис.109. Исходные данные новой книги

4. Выполнить консолидацию данных из разных книг.

4.1. Создайте новую книгу и постройте в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы показан на рисунке 109. Назовите лист с таблицей «2013». Сохраните созданную книгу под именем Конс_2.

4.2. Сверните окно книги Конс_2.

5. На новом листе книги Конс_1 выполнить консолидацию четырех диапазонов ячеек – трех из листов «Октябрь», «Ноябрь», «Декабрь» книги Конс_1, а четвертого из соответствующего диапазона книги Конс_2 листа «2013» (рис.110). Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рисунке 111.

Рис. 110. Окно Консолидация для объединения данных из разных книг

Рис. 111. Итог консолидации данных из разных книг

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

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

2. Покажите нахождение команд для выполнения этих операций.

3. Как создать несколько окон? Какое имя появляется в заголовке каждого нового окна?

4. Каким образом могут располагаться окна относительно друг друга? Как добиться соответствующего расположения?

5. Поясните понятие «связывание рабочего листа».

6. Объясните принцип консолидации по формуле. Приведите примеры связывания листов одной рабочей книги и разных рабочих книг.

7. Что собой представляют формулы при связывании листов одной рабочей книги и разных рабочих книг?

8. Объясните принцип консолидации по расположению.

9. Как выполнить операцию консолидации?

[1] Объемная (трехмерная) ссылка - ссылка на диапазон, включающий более одного листа книги

[2] Номер книги может быть иным, в нашем случае это Книга2