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

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

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

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

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

Если консолидированная информация выводится на том же рабочем листе, что и исходные данные, то для итоговой таблицы с консолидированными данными не создается структуры, поэтому для вычисления итоговых данных на одном и том же рабочем листе лучше использовать команду ДАННЫЕÞИтоги.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Консолидации данных по категориям и модификации консолидированных данных посвящено задание 6 практической работы 3.