4. Консолидация данных

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

Рассмотрим работу механизма консолидации на примере.

Несколько распространителей занимаются продажей газет. Каждый из них заполняет отчет о количестве проданных экземпляров газет в виде таблицы Excel в отдельной книге. Таблица имеет вид, представленный на рис.8.

Заполните таблицу и сохраните ее в рабочей книге Отчет первого исполнителя. Скопируйте эту таблицу во вторую книгу Отчет второго исполнителя. Повторите сохранение таблицы в книге Итоговый отчет и дополните в таблицу два столбца для подсчета выручки (сумма вычисляется по формуле: количество * цена экземпляра) (рис. 9.)

Рис.8. Структура исходной таблицы

Рис. 9 . Исходная таблица перед консолидацией

Откройте рабочие книги с отчетами первого и второго исполнителей. Внесите информацию о проданных экземплярах газет (для простоты проверки пусть первый исполнитель продал по 100 экз. каждой газеты, а второй – по 50). Сохраните все созданные книги. Для подведения итогов нужно консолидировать данные из отчетов распространителей.

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

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

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

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

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

4. В меню Данные выполните команду Консолидация….

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Выделите диапазон B1:C9.

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы:

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

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

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

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

Вопросы для контроля:

1. Можно ли установить связь между тремя (четырьмя и т.д.) таблицами? Как это сделать?

2. Какие операции кроме суммирования можно выполнить в ходе специальной вставки?

3. Сколько операций и какие операции можно включать в макрос?

4. Как создать кнопку для запуска макроса? Как удалить кнопку запуска макроса и сам макрос, если они больше не нужны?

5. Можно ли консолидировать три и более таблиц? Как это сделать?