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

Консолидация - это объединение или накопление информации из двух или более рабочих листов, которые могут находиться в разных рабочих книгах. В некоторых случаях консолидация может включать в себя создание связанных формул. Приведем два типичных примера консолидации:

• Бюджеты отделов фирмы храняться в отдельных рабочих листах одной и той же рабочей книги. Вам нужно консолидировать эти данные и создать итоговый документ бюджета всей фирмы.
• Все руководители отделов представляют свои боджеты в отдельных рабочих книгах. Ваша задача заключается в том, чтобы консолидировать эти файлы в общий бюджет компании.

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

• Использовать формулы, содержащие внешние ссылки.
• Скопировать данные и воспользоваться диалоговым окном Специальная вставка.
• Воспользоваться командой Данные | Консолидация.
• Воспользоваться сводной таблицей.

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

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

Если рабочие листы, которые Вы консолидируете, находятся в одной рабочей книге и если способы размещения информации в них идентичны, то в этом случае можно воспользоваться стандартными формулами. Например, для вычисления суммы значений из ячеек A1, находящихся в рабочих листах, начиная с листа Лист2 и заканчивая листом Лист10, надо ввести формулу: =СУММ(Лист2:Лист10!А1).

Если консолидация затрагивает другие рабочие книги, то для ее выполнения можно воспользоваться формулами, содержащими внешние ссылки. Например, если нужно сложить значения из ячеек A1, которые находятся в рабочих листах (Лист1) из двух разных рабочих книг, с именами Регион1 и Регион2, то можно воспользоваться следующей формулой: =[Регион1.xls]Лист1!А1 + [Регион2.xls]Лист1!А1.

Формула может включать произвольное число внешних ссылок, при этом число содержащихся в ней символов не должно превышать 1024.

Консолидация с помощью диалогового окна Специальная вставка
Этот метод применим только в том случае, если все рабочие листы, которые Вы консолидируете, открыты. Недостатком этого метода является то, что консолидация получается нединамичной, т.е. если любые консолидированные данные изменятся, результат будет неправильным. Преимуществом данного метода является то, что при вставке данных из буфера обмена с помощью команды Специальная вставка можно выполнять математические операции. Чтобы воспользоваться этим методом, нужно выполнить следующие действия:

• Скопировать данные из первого диапазона исходного рабочего листа.
• Активизировать зависимую рабочую книгу и выбрать ячейку, в которую нужно поместить консолидированные данные.
• Выбрать команду Правка | Специальная вставка, установить переключатель Сложить, и щелкнуть на кнопке OK.

Использование команды Данные | Консолидация
Этот метод универсален, с его помощью можно выполнить статичную (без формул связи) или динамичную консолидацию (с формулами связи). Команда Данные | Консолидация поддерживает следующие методы консолидации:

• По позиции. Применяется в том случае, если способы размещения информации в исходных рабочих листах идентичны.
• По категории. Проводится подбор данных из исходных рабочих листов по заголовкам строк и столбцов.

На рисунке 1 показано диалоговое окно Консолидация, которое содержит следующие элементы управления:

Рис. 1. Диалоговое окно Консолидация

• Функция. Определяется тип консолидации (Сумма, Количество значений, Среднее, Максимум и т.д.)
• Ссылка. Адрес диапазона для консолидации из исходного файла. После того, как Вы введете адрес диапазона, щелкните на кнопке Добавить, чтобы добавить его в список диапазонов. Если Вы выполняете консолидацию по позиции, не включайте в диапазон консолидации заголовок диапазона. А в случае консолидации по категории Вы должны этот заколовок включить.
• Список диапазонов. Список всех ссылок, которые были зафиксированы с помощью кнопки Добавить.
• Использовать в качестве имен. Опции этого раздела позволяют при выполнении консолидации использовать заголовки в верхней строке, левом столбце или обеих позициях. Используйте эти опции для выполнения консолидации по категории.
• Создать связи с исходными данными. При выборе этой опции в зависимом рабочем листе создается структура, которая содержит внешние ссылки на ячейки назначения. Кроме того, структура содержит такжн формулы вычисления итогов. Если не активизировать эту опцию, то при выполнении консолидации формулы со связями создаваться не будут.
• Кнопка Обзор. Служит для вызова диалогового окна, в котором можно выбрать рабочую книгу.
• Кнопка Добавить. Перенос ссылки из поля Ссылка в поле Список диапазонов.
• Кнопка Удалить. Удаление выбранной ссылки из списка диапазонов.

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

Рис. 1. Рабочие листы, содержащие данные за месяц о продажах товаров

Для создания сводной таблицы выполните следующие действия.

1. Добавьте новый лист, назовите его Итоги.
2. Выберите команду Данные | Сводная таблица, чтобы запустить средство Мастер сводных таблиц и диаграмм.
3. В первом диалоговом окне мастера выберите переключатель В нескольких диапазонах консолидации и щелкните на кнопке Далее.
4. В следующем диалоговом окне Мастер сводных таблиц и диаграмм - шаг 2а из 3 выберите переключатель Создать одно поле страницы. Щелкните на кнопке Далее.
5. Теперь определите диапазоны для консолидации. Первый диапазон - Магазин1!А$1:$D12 (его адрес можно ввести непосредственно или указать на рабочем листе). Щелкните на кнопке Добавить для добавления диапазона к списку Список диапазонов.
6. Повторите пункт 5 для двух других диапазонов. Щелкните на кнопке Далее, чтобы снова перейти к диалоговому окну шага 3.
7. В третьем диалоговом окне Мастер сводных таблиц и диаграмм щелкните на кнопке Готово.

В результате сводная таблица будет иметь вид:

Рис. 2. Сводная таблица

На четвертом шаге описанной процедуры в диалоговом окне Мастер сводных таблиц и диаграмм - шаг 2а из 3 можно выбрать переключатель Создать поля страницы. Это позволит назначить имя каждому элементу в поле страницы.

Файл с данным примером можно взять здесь.

---

Дата добавления: 2015-07-20