50348 (Консолидация данных в Excel), страница 2

Рис. 2. Окно Консолидация для примера 7

Рис.3. Результат консолидации примера 7

5. В списке Функция выбрать тип консолидации. По умолчанию используется функция сумма.

6. Сбросить оба флажка в группе Использовать в качестве имен, поскольку ячейки привязаны к своим позициям, а не к заголовкам.

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

8. Нажать кнопку ОК.

Результат консолидации представлен на рис.3.

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

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

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

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

Таким образом, достигается большая гибкость.

Пример 8.

Необходимо составить сводную ведомость расходов двух различных филиалов компании (книги Филиал1.xls и Филиал2.xls). Названия статей и их порядок следования могут отличаться. Исходные диапазоны представлены на рис.5. Заголовки столбцов совпадают, заголовки строк нет.

Рис.5. Исходные данные для примера 8

Чтобы осуществить консолидацию по заголовкам строк и столбцов необходимо:

1. Выделить диапазон назначения. Если необходимо, чтобы поля были расположены в определенном порядке, следует включить в диапазон заголовки полей или строк. Заголовки должны быть написаны в точности так, как и на исходных листах. Если заголовки не вводить, то Excel создаст их автоматически (рис.6).

Рис.6. Диапазон назначения для примера 8

2. Выбрать команду Данные, Консолидация.

3. В поле Ссылка указать ссылку на исходный диапазон. Диапазон должен включать заголовки столбцов или строк. Если исходная книга закрыта, полный путь к ней следует указывать обязательно. Путь можно набрать или можно воспользоваться кнопкой Обзор и выбрать файл на диске:

='[Филиал1.xls]Лист1'!$А$1:$С$6

4. Нажать кнопку Добавить, ссылка будет занесена в Список диапазонов.

5. Повторить шаги 3 и 4 для всех исходных диапазонов. Если исходные рабочие листы имеют однотипные имена, достаточно будет немного отредактировать строку в поле Ссылка.

='[Филиал2.xls]Лист1'!$А$1:$С$6

6. В списке Функция выбрать тип консолидации. Для рассматриваемого примера – функция Сумма.

7. Установить флажки в группе Использовать в качестве имен. Можно установить как один флажок, так и оба Подписи верхней строки, Значения левого столбца.(рис. 7).

Рис.7.Окно Консолидация для примера 8

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

9. Нажать кнопку ОК.

Результат представлен на рис.8.

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

1.1.4 Консолидация данных с использованием ссылок

Пример 9. Подготовить отчет по результатам деятельности филиалов предприятия за 5 лет с 1995 года включительно. Данные за каждый год представлены в отдельной книге. Структура годовых отчетов однотипна. В качестве примера на рис.11 приведен отчет работы предприятия за 1995 год.

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

1.Скопировать или задать надписи для данных консолидации (рис.12).

2.Указать ячейку, в которую следует поместить данные консолидации. Выделить ячейку В2 на Лист1 книги "Отчет за 5 лет".

Рис.11. Исходные данные для примера 9

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

='[Год1995.xls]Лист1'!$B$2:$D$2

4. Повторить шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты консолидации данных.

Рис.12.Результат консолидации данных примера 9

1.1.5 Ручная консолидация рабочих листов

Если в исходных диапазонах данные расположены одинаково, можно для подведения итогов воспользоваться командой Правка, Специальная вставка. Эта команда позволяет производить суммирование (и другие арифметические операции) копируемых данных с уже имеющимися данными в диапазоне.

Пример 10. Дополнить отчет, полученный в примере 9 данными за 2000 год. Структура годового отчета не изменилась.

Для решения задачи:

1) Выделите диапазон с данными за 2000 год и скопируйте его с помощью команды Правка, Копировать.

2) Выделите диапазон назначения. Для рассматриваемого примера это диапазон ячеек G2-G5, расположенный на Лист1 книги "Отчет за 5 лет".

3) Активизируйте команду Правка-Специальная вставка. В появившемся диалоговом окне Специальная вставка (рис.13) выберите в группе Вставить переключатель Значения, а в группе Операция — Сложить.

Найденные таким образом итоги не будут связаны с исходными данными, и, следовательно, изменения в исходной информации не будут отражаться на консолидированных данных.

Рис.13 Вид окна Специальная вставка

1.1.6 Создание сводной таблицы на основе данных, находящихся в нескольких диапазонах консолидации

Можно получать сводные таблицы на основе данных, находящихся в нескольких диапазонах консолидации. Для этого достаточно в первом окне Мастера сводных таблиц (рис.14) выделить соответствующую опцию.

Рис.14.Вид окна Шаг1 из 4 Мастера сводных таблиц

Поскольку консолидированные данные как правило бывают получены на базе исходных данных, имеющих разную структуру, то и структура области СТРАНИЦА сводной таблицы в этом случае несколько меняется.

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

Выбрать тип поля страницы можно в диалоговом окне Мастера сводных таблиц - шаг 2а из 4 (рис. 15.).

Если необходимо отобразить одну страницу для каждого исходного диапазона или страницу, объединяющую все диапазоны исходных данных, установите переключатель в положение Создать одно поле страницы в диалоговом окне Мастер сводных таблиц - шаг 2a из 4.

Если необходимо создать несколько (можно создать до четырех) полей страниц и назначить каждому элементу исходного диапазона имя, а также создать структуру, не имеющую полей страниц, то установите переключатель в положение Создать поля страницы в диалоговом окне Мастер сводных таблиц - шаг 2a из 4. Этот переключатель используется для сравнения частей исходных данных или отображения всех данных.

Рис.15.Вид окна Шаг 2а из 4 Мастера сводных таблиц

Пример 11.

Проанализировать данные о продаже различных видов продукции филиалами компании за 3 месяца 1995 года.

Для этого выполните следующие действия:

1. Запустите Мастер сводных таблиц.

2. Установите переключатель на шаге 1 в положение В нескольких диапазонах консолидации(рис.14)

3. На шаге 2а установите переключатель в положение Создать одно поле страницы(рис.15)

4.Укажите диапазоны для консолидации на шаге 2б (рис.16)

5. На третьем шаге Мастера сводных таблиц нажмите кнопку Далее, чтобы подтвердить положение установленных по умолчанию полей (рис.17)

Рис.17 Вид окна Шаг 3 Мастера сводных таблиц

6. Укажите место, куда будет помещена сводная таблица, на четвертом шаге.

Результат построения сводной таблицы представлен на рис.18

Рис.18 Сводная таблица примера 11

Если щелкнуть по раскрывающемуся списку Страница1, то появятся общие имена: Объект1,Объект2,Объект3 ,которые Excel по умолчанию присвоил каждому из диапазонов консолидации. Для того, чтобы задать имена диапазонов консолидации необходимо:

1. На шаге 2а (рис.15) установить переключатель в положение Создать поля страницы.

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

Рис.19. Создание страничных полей.

Остальные шаги в алгоритме построения сводной таблицы рассмотрены в Примере 11.

1.2 Связывание объектов

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

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

Независимо от того, открыта ли исходная рабочая книга или закрыта, целевая рабочая книга получит необходимые данные через связь. Если во время открытия целевой рабочей книги исходная рабочая книга открыта, то связи обновляются автоматически. Если исходная рабочая книга закрыта, то при открытии целевой пользователю будет задан вопрос, хочет ли он работать с данными, оставшимися после последнего сохранения или их следует обновить из исходной рабочей книги (см.п."Открытие связанных рабочих книг").

Можно установить связь исходных данных

1) с ячейками и диапазонами ячеек

2) с изображениями ячеек рабочего листа.