Консолидация по физическому расположению

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

1. Откройте рабочие книги Отчет первого исполнителя, Отчет второго исполнителя и Итоговый отчет.

2. Перейдите в рабочую книгу итогового отчета.

3. Выделите целевой диапазон ячеек C2:C9, в которых должен быть получен результат консолидации.

4. На вкладке Данные выполните команду Работа с данными, Консолидация ….

5. В диалоговом окне Консолидация выберите функцию Сумма для сложения количества проданных каждым распространителем экземпляров.

 

6. Установите курсор в поле ввода Ссылка.

7. Переключитесь в окно книги Отчет первого исполнителя через Панель задач.

8. Выделите диапазон чисел, представляющих количество распространенных исполнителем экземпляров каждой газеты (C2:C9). В диалоговом окне консолидации в строке Ссылка формируется трехмерная ссылка с указанием рабочей книги, рабочего листа и диапазона:

 

'[Отчет первого исполнителя.xlsx]Лист1'!$C$2:$C$9

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

10. Переведите курсор снова в поле ссылка и перейдите к отчету второго исполнителя.

11. Выделите диапазон чисел (количества экземпляров, распространенных исполнителем – C2:C9), как и у первого исполнителя.

12. Добавьте выделенный диапазон в список диапазонов консолидации.

13. Щелкните кнопку ОК, в итоговом отчете получите результаты.

 

Рисунок 6.3. Результаты консолидации по физическому расположению

Консолидация по заголовкам

Во всех трех книгах скопируйте содержимое первых листов и вставьте копии перед вторыми листами. В результате копирования получите три новых листа Отчет первого исполнителя(2), Отчет второго исполнителя(2) и Итоговый отчет(2). Перейдите на лист-копию в рабочей книге первого исполнителя и удалите в отчете строки «Местное время», «Ваши шесть соток» и «Эфир». В отчете второго исполнителя удалите строки «Комсомольская правда» и «Ва-банк». В итоговом отчете (копии) очистите столбик «Количество».

Теперь данные нельзя консолидировать по физическому расположению – строки соответствующие одним изданиям не совпадают, порядок нарушен.

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

1. Перейдите в окно рабочей книги итогового отчета на копию листа отчета.

2. Выделите диапазон B1:C9. Обратите внимание, что выделенный диапазон содержит заголовки таблицы «Наименование» и «Количество».

3. Выполните команду Данные, Работа с данными, Консолидация….

4. В диалоговом окне команды очистите список диапазонов консолидации (в нем выделяется диапазон и удаляется щелчком по кнопке Удалить).

5. Выберите функцию Сумма.

6. Установите курсор в поле ввода Ссылка.

7. Перейдите на копию рабочего листа отчета первого исполнителя.

8. Выделите диапазон B1:C6 и добавьте его щелчком по кнопке Добавить в список диапазонов консолидации.

9. Установите курсор в поле ввода «Ссылка».

10. Перейдите на копию рабочего листа отчета второго исполнителя.

11. Выделите диапазон B1:C7 и добавьте его щелчком по кнопке Добавить в список диапазонов консолидации.

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

13. Установите флажок Создать связи с исходными данными. Он позволит увидеть детальные данные по каждому исполнителю в итоговом отчете.

14. Щелкните кнопку ОК.

Слева от заголовков столбцов появились кнопки, позволяющие увидеть детальные данные, из которых получен отчет. Щелкните по кнопке «2». Рабочий лист примет вид, представленный на рис.6.4.

 

Рисунок 6.4. Консолидация по заголовкам.

 

Выводы:

1. Команды специальной вставки позволяют установить связь между различными электронными таблицами. В этом случае при внесении изменений в таблицу-источник эти изменения будут автоматически дублироваться во все связанные таблицы.

2. В ходе установки связей можно указать и операции (сложить, вычисть, умножить, поделить), которые нужно выполнить в связываемых таблицах с данными.

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