Консолидация листов

Работа со связанными книгами.

Краткая теоретическая часть.

Занятие 2

Учебные вопросы:

1. Работа со связанными книгами.

2. Консолидация листов.

3. Создание связей с исходными листами.

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

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

Для установления связи между книгами нужно создать формулы, содержащие внешние ссылки.

При сохранении книги, которая содержит формулы с внешними ссылками, Excel сохраняет самые последние результаты этих формул. Предположим, вы изменили значения некоторых ячеек в исходной книге после закрытия зависимой книги. При следующем открытии зависимой книги она будет содержать старые значения внешних ссылок в зависимых формулах. В этом случае Excel выведет сообщение «Данная книга содержит автоматически обновляемые связи с другой книгой. Обновить их?».

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

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

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

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

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

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

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

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

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

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

· Консолидация данных путем создания сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость и информативность.

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

· Консолидация данных с использованием Мастера шаблонов с функцией автоматического сбора данных.

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

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

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

Для выполнения указанного способа консолидации вводят команду ДАННЫЕ ► Консолидация, а затем в диалоговом окне Консолидация выбирают функцию и указывают области консолидируемых данных.

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

Для выполнения консолидации данных по категориям следует:

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

2. Ввести команду меню ДАННЫЕ ► Консолидация.

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

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

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

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

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

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

Если исходные данные расположены на другом рабочем листе, нужно активизировать этот лист и выделить нужный диапазон или ввести его имя. Имя или адрес диапазона с указанием имени листа (и книги, если лист находится в другой рабочей книге) появится в поле Ссылка.

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

5. Далее для включения выбранного диапазона в поле Список диапазонов следует щелкнуть по кнопке Добавить. Кнопка Добавить используется в тех случаях, когда в консолидации участвуют несколько областей-источников данных.

6. Далее следует повторить п. 4 и 5 для всех консолидируемых исходных областей - ввести адреса всех диапазонов, подлежащих консолидации.

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

Если область назначения и исходные данные находятся в разных книгах, но в одной и той же папке, следует указать имя книги, имя листа, а затем - имя или ссылку на диапазон, например [Итоги.xls]Юпитер!D4:G22.

Если исходные области и область назначения находятся разных книгах и в разных папках диска, нужно использовать полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон, например: [H:\Works\Итоги.xls]Caтурн!D4:G13.

7. После ввода адресов всех исходных областей следует установить нужные переключатели:

· Использовать в качестве имен подписи верхней строки или значения левого столбца (или одновременно оба переключателя) - в зависимости от расположения заголовков, выделенных в исходной области;

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

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

8. Щелкнуть по кнопке ОК для выполнения консолидации данных из областей, указанных в поле Список диапазонов.

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

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

Если исходные данные расположены в других рабочих книгах, для обновления данных нужно использовать команду ПРАВКА ► Связи.

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

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

Для выполнения консолидации данных с использованием других функций, например для вычисления средних значений в тех же исходных диапазонах, для которых ранее использовалась функция Сумма, следует установить курсор в новом месте (для вставки новой области консолидированных данных), ввести команду ДАННЫЕ ► Консолидация и в диалоговом окне Консолидация выбрать нужную функцию. В этом случае нет необходимости снова вводить адреса исходных диапазонов.

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

Тема 31. «Печать листов»

Дата добавления: 2014-10-23