Главная Обратная связь

Дисциплины:






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



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

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

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

2) Ввести команду меню ДАННЫЕ→КОНСОЛИДАЦИЯ.

3) В диалоговом окне КОНСОЛИДАЦИЯ выбрать из раскрывающегося списка функцию, которую следует использовать для обработки данных, например СУММА (рис.1.1).

Рис.1.1. диалоговое окно КОНСОЛИДАЦИЯ

4) Щелкнуть ЛКМ в поле ССЫЛКА, указать первый диапазон ячеек, данные из которых должны быть консолидированы.

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

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

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

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

Для восстановления исходных размеров диалогового окна нужно снова нажать кнопку в правой части поля ССЫЛКА, тем самым развернув диалоговое окно КОНСОЛИДАЦИЯ.

5) Далее для включения выбранного диапазона в поле СПИСОК ДИАПАЗОНОВ следует щелкнуть по кнопке ДОБАВИТЬ. Кнопка ДОБАВИТЬ используется в тех случаях, когда в консолидации участвует несколько областей-источников данных.

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

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



Если область назначения и исходные данные находятся в разных книгах, но в одной и той же папке, следует указать имя книги, имя листа, а затем – имя или ссылку на диапазон, например: [Итоги.xls]Лист1!B2:F15.

Если исходные области и область назначения находятся в разных книгах и в разных папках диска, нужно использовать полный путь к файлу книги, имя книги, имя листа, а затем – имя или ссылку на диапазон, например: [D:\Works\Итоги.xls]Лист1!B2:F15.

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

· Использовать в качестве имен ПОДПИСИ ВЕРХНЕЙ СТРОКИ или ЗНАЧЕНИЯ ЛЕВОГО СТОЛБЦА (или одновременно оба переключателя) – в зависимости от расположения заголовков, выделенных в исходной области.

· СОЗДАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ – для того чтобы автоматически обновлять итоговую таблицу при изменении данных в источниках.

При установке переключателя в качестве имен ЗНАЧЕНИЯ ЛЕВОГО СТОЛБЦА значения в строках с одинаковыми метками будут просуммированы, даже если они расположены в несмежных областях.

8) Щелкнуть по кнопке ОК для выполнения консолидации данных из областей, указанных в поле СПИСОК ДИАПАЗОНОВ.

При установке переключателя СОЗДАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ обновление будет происходить автоматически, если исходные данные находятся на других листах в пределах одной рабочей книги.

Если исходная область и область назначения находится на одном листе, то переключатель СОЗДАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ не устанавливают, так как связи уже существуют.

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

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

Если связь с исходными данными не устанавливается, то после создания итоговой таблицы ссылки можно модифицировать – добавлять, удалять или изменять исходные области данных и пересчитывать данные в области назначения. Если не устанавливать переключатель СОЗДАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ, то при изменении исходных данных нужно будет заново выполнять операцию консолидации.

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

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





sdamzavas.net - 2019 год. Все права принадлежат их авторам! В случае нарушение авторского права, обращайтесь по форме обратной связи...