ЗАДАЧА 2. Консолидация рабочих листов

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

1. Создайте рабочую книгу Сделки.xls, состоящую из четырех листов: Январь, Февраль, Март, 1 квартал.

2. Внесите заголовки столбцов одновременно в несколько листов. Для этого:

- выделите все листы с названиями месяцев: перейдите на листЯнварь, нажмите клавишу Shift и, не отпуская ее, щелкните по ярлычку листа Март. Будут выделены все листы рабочей книги, при этом активным листом останется Январь;

- введите в ячейку А3 – Фамилия И.О., в ячейку В3 – Сделки, в С3 – Объем;

- щелкните по ярлыку листа 1 квартал,выделение листов будет снято;

- убедитесь, что в ранее выделенные листы внесен один и тот же текст в ячейки А3, В3, С3. Для иллюстративных целей поменяйте на листе Февраль содержимое ячеек: в В3 – Объем, а в С3 – Сделки.

3. Введите в листы с названиями месяцев информацию в соответствии с таблицей (рис. 2.3.).

Рис. 2.3. Содержимое листов Январь, Февраль, Март

Обратите внимание, что фамилии в листах идут в полном беспорядке, заголовки столбцов тоже перепутаны (но фамилии всегда в первом столбце!).

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

5. В меню Данные выберите команду Консолидация. Появится диалоговое окно Консолидация.

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

7. Перейдите в поле Ссылка.Щелкните мышью по ярлычку листа Январь (в поле ввода появится Январь! - формируется адрес). Выделите блок А3:С6 (в поле ввода - Январь!$A$3:$C$6) - вокруг блока бегущая пунктирная рамка. Нажмите кнопку ,ссылка будет введена в Список диапазонов. Аналогично добавим диапазоны Февраль!$A$3:$C$5 и Март!$A$3:$C$7. Список диапазонов консолидации сформирован.

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

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

10. Флажок Создавать связи с исходными данными устанавливать пока не будем.

11. После щелчка по кнопке ОК на рабочем листе 1 квартал появится таблица (рис. 2.4.):

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

Обратите внимание, что заголовок Фамилия И.О. отсутствует.

11. Внесите изменения в один из диапазонов, например, на листе Март у Иванова И.И. увеличьте Объем до 250. Таблица на листе 1 кварталне изменится, т.к. не был установлен флажок Создавать связи с исходными данными.Выполните команду Данные /Консолидация, в диалоговом окне ничего не меняйте, только щелкните по кнопке ОК, произойдет обновление таблицы.

12. Установите связи. Выделите на листе 1 кварталячейку А1. Откройте диалоговое окно Консолидацияи установите флажок Создавать связи с исходными данными. Таблица изменилась (рис. 2.5).

Рис. 2.5. Консолидация в режиме связей с исходными данными

13. Выполните подгонку ширины столбцов. Столбец В пустой, столбцы Сделки и Объем переместились в столбцы С и D. Слева появились символы структуры.

14. Раскроем второй уровень структуры. В столбце В появились имена текущей рабочей книги (можно консолидировать данные из разных рабочих книг), а в столбцах С и Dвы увидите, из каких исходных данных сложились итоговые данные. Если теперь изменить количество, например, сделок на листе Март, то итоговые данные будут обновлены автоматически.