Консолидация с одним страничным полем

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

Консолидация – это объединение данных из одной или нескольких таблиц, однотипных по своей структуре, с одновременным выполнением действий над данными.

Области – источники данных могут размещаться на одном, на разных рабочих листах или в разных рабочих книгах. Таким образом можно получить итоговый отчет по данным, предоставленным, например, разными филиалами одной фирмы.

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

Для выполнения консолидации в Excel имеется два способа:

— с помощью команды «Консолидация»;

— с помощью команды «Мастера сводных таблиц».

Рассмотрим наиболее общий вариант консолидации с помощью команды «Консолидация»– консолидация с установлением связей.

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

Для примера подготовим одновременно на трех листах рабочей книги таблицы, содержащие количество проданных экземпляров книг по нескольким категориям:

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

Переименуем листы в «Кругозор», «Горизонт», «Мир книг» (пусть это будут названия книжных магазинов). Конечно, можно оставить таблицы одинаковыми, но т.к. мы рассматриваем более общий вариант, внесите в таблицы некоторые изменения. Например:

— на листе «Кругозор» удалите столбец «Январь» (магазин открылся только в феврале) и удалите строку «Учебная»;

— на листе «Горизонт» удалите столбец «Апрель» (магазин закрыли) и замените «Детская» на «Информационные технологии»;

— на листе «Мир книг» добавьте строку «Информационные технологии».

Заполните таблицы произвольными значениями. Вычислите суммы в строке и столбце «Итого».

Наша задача – получить итоговую таблицу продаж по трем магазинам за каждый месяц и по каждому виду литературы. Выполните следующие действия:

1. Вставьте новый рабочий лист и переименуйте его в «Итог».

2. Установите рамку в свободную ячейку, например, в А1.

3. Выполните команду Данные – Консолидация. Откроется окно, как на рис. 35.

Рис. 35. Окно «Консолидация»

Выполните следующие действия.

1. Щелкните в поле «Ссылка», выделите исходный диапазон, затем щелкните на кнопке Добавить, чтобы он появился в списке диапазонов. Повторите для всех диапазонов. Исходные диапазоны необходимо выделять вместе с заголовками строк и столбцов, и можно вместе с итоговыми данными.

2. Выберите функцию, определяющую действие над данными.

3. Установите флажки «подписи верхней строки», «значения левого столбца», «Создавать связи с исходными данными».

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

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

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

Замечание. В общем случае исходные таблицы могут иметь разное количество строк и столбцов и различный порядок следования элементов.

Рис. 36. Консолидированная таблица со связями с исходными данными

Таким образом, пользователь избавлен от необходимости записывать объемные формулы, суммирующие данные по нескольким листам. Управляя структурой с помощью кнопок, можно показывать и скрывать строки с исходными данными. При удалении структуры с помощью соответствующей команды таблица останется на листе в развернутом состоянии.

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

Рис. 37. Консолидация по одному исходному диапазону B1:C15

Такие способы консолидации вполне подходят, если не требуется выполнение других вычислений над данными.

Если же данные должны быть представлены в процентах, то есть два варианта: записывать формулы, (что, конечно, иногда не радует!) или воспользоваться консолидацией на базе сводных таблиц. Возможно, на первый взгляд, этот способ покажется более сложным. Но результат оправдает затраты – вы получите консолидированную таблицу, обладающую всеми возможностями сводной таблицы.

Консолидация с одним страничным полем

Рассмотрим порядок выполнения консолидации с использованием Мастера сводных таблиц.

Если вы работаете в седьмой (и выше) версии, то кнопка «Мастер сводных» таблиц может отсутствовать на ленте.

Необходимо добавить её на панель быстрого доступа:

Кнопка Office – Параметры Excel – Настройка – Все команды

Далее в списке команд по алфавиту найдите «Мастер сводных таблиц» и добавьте на панель быстрого доступа.

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

Порядок работы:

1. Откройте окно «Мастер сводных таблиц».

2. Установите переключатель в положение «В нескольких диапазонах консолидации».

3. Установите переключатель в положение «Создать одно поле страницы».

4. На следующем шаге откроется окно для ввода списка исходных диапазонов, аналогичное окну команды «Консолидация»

Рис. 38 Окно для ввода списка исходных диапазонов

Исходные диапазоны указываются без итоговых строк!

5. На третьем шаге автоматически формируется структура сводной таблицы (см. рис. 39).

Каждый интервал источника данных представлен элементом страничного поля со стандартным названием «Объект». Значения элементов можно заменить на реальные в готовой сводной таблице.

Стандартные названия полей необходимо поменять на реальные, дважды щелкнув на кнопке поля и указав новое название поля. Поле «Строка» переименуем в «Категория» в соответствии с его элементами, «Столбец» – в «Месяц», «Страница» – в «Магазин». Поля расположены так же, как в исходных таблицах.

Рис. 39. Первоначальная структура консолидированной таблицы

6. Далее указываем местоположение таблицы и заканчиваем команду.

В результате построения на рабочем листе получится таблица как на рис. 40.

Рис. 40. Консолидированная таблица

Значения элементов страничного поля были изменены на рабочем листе в соответствии со списком исходных диапазонов (список автоматически сортируется по алфавиту (см. рис. 38)). Чтобы изменить стандартное значение страничного поля, например, «Объект 1», выбираем его в списке и вводим в строке формул новое значение «Горизонт».

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

Эта таблица обладает всеми свойствами сводной таблицы, т.е. можно изменить ее структуру, выполнить дополнительные вычисления, ввести новые вычисляемые поля и элементы.

Например, можно получить ежемесячный отчет по каждому магазину по всем категориям литературы или по выбранной, выраженный в процентах к общему итогу. (Примените дополнительное вычисление для поля в области значений). Результат преобразований показан на рис. 41.

Рис. 41. Преобразованная сводная таблица

Консолидация с несколькими страничными полями *[1]

В предыдущем параграфе при построении консолидированной сводной таблицы был выбран вариант «Создать одно поле страницы». Рассмотрим вариант «Создать поля страницы».

Подготовим исходные таблицы для выполнения консолидации. Пусть имеется шесть таблиц по указанным магазинам:

— 3 таблицы за 2011 год (они были ранее созданы для задания по
консолидации);

— 3 таблицы за 2012 год.

Скопируйте исходные таблицы и дополните их месяцами до конца года и подпишите таблицы «2011 год».

Скопируйте исходные таблицы еще раз и подпишите «2012 год». Пусть они содержат данные до июня 2012 года (дополните необходимые столбцы).

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

Начнем построение консолидированной сводной таблицы.

1. Выполните «Мастер сводных таблиц».

2. Установите переключатель в положение «В нескольких диапазонах консолидации».

3. Установите переключатель в положение «Создать поля страницы».

4. На следующем шаге откроется окно для ввода исходных диапазонов, аналогичное окну «Консолидация» (см. рис. 42), но немного сложнее.
Во-первых, укажите все исходные диапазоны так же, как и в предыдущем примере (обратите внимание, что список упорядочен по алфавиту).
Во-вторых, установите переключатель в положение «2», т.к. имеем два «измерения»: магазины и годы.
В-третьих, выделите в списке первый интервал (это таблица для магазина Горизонт за 2011 год) и введите «Горизонт» в первое поле и «2011» во второе поле. Таким образом, мы устанавливаем, что первое страничное поле будет содержать названия магазинов, а второе – годы.
Далее выделите второй диапазон в списке и выберите в первом поле «Горизонт», а во второе введите «2012». Повторите действия для всех диапазонов.

Рис. 42. Заполнение страничных полей

5. Щелкните кнопку «Далее». Откроется структура сводной таблицы, содержащая два страничных поля . Переименуйте поля, установите вид вычислений.

6. Укажите местоположение сводной таблицы и закончите построение. Готовая таблица после группировки по месяцам может выглядеть, как на рис. 43.

Рис. 43. Консолидированная таблица с двумя страничными полями

Данная таблица содержит, во-первых все шесть исходных диапазонов, во-вторых, шесть консолидированных таблиц:

— по трем магазинам за два года (одна таблица);

— по каждому магазину за два года (три таблицы);

— за каждый год по трем магазинам (две таблицы).

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

Рис. 44. Преобразованные сводные таблицы

При изменении исходных данных обновление консолидированной таблицы происходит, как обычно, командой Данные – Обновить данные, или щелчком на кнопке  панели «Сводные таблицы» .

[1] Дополнительное задание