Лабораторная №7 Консолидация данных. Сводная таблица.
Консолидация— это операция, позволяющая в одной ячейке обобщающей таблицы объединить значения из множества ячеек таблиц-источников, используя необходимую математическую функцию (например, суммирование или нахождение среднего
В MS Excel 2000 предусмотрено несколько способов консолидации данных:
По расположению. Консолидацию по расположению следует использовать в случае, если данные всех исходных областей находятся в одном месте и размещены в одинаковом порядке. Например, этот способ можно использовать для консолидации данных нескольких листов, созданных на основе одного шаблона.
JJo категории. Консолидацию по категории следует использовать в случае, если требуется обобщить набор листов, имеющих одинаковые заголовки, но с различной организацией данных. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов,
С помощью отчета сводной таблицы. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации 'категорий.
С помощью трехмерных ссылок (наиболее предпочтительный способ) При использовании трехмерных ссылок отсутствуют ограничения на расположения данных в исходных областях.
Основные понятия и правила работы при консолидации данных.
Прежде чем консолидировать данные, необходимо определить, где будет находится область назначения для консолидируемой информации — конечная область
Конечная область — это выбранный для хранения консолидируемых данных диапазон ячеек. Конечная область может находиться на том же листе, что и исходные данные, или на другом листе. Лист может содержать только один конечный диапазон консолидации. Исходные области — это диапазоны, данные которых консолидируются и помещаются в конечную область. Исходные области могут располагаться на любом листе или книге,
Если используется метод консолидации данных по расположению, то метки данных в областях-источниках не копируются в область назначения. Если метки на рабочем листе области назначения все-таки нужны, то они вводятся и одновременно выделяется область назначения подметками и справа от меток строк.
Далее необходимо определить источники тех данных, которые нужно консолидировать. Для задания исходной области может быть использован один из следующих способов:
Ссылки на ячейки.
Ссылки на лист и ячейки.
Ссылки на книгу, лист и ячейки.
Задание полного пути и ссылки на книгу, лист и ячейки.
Задание имени области
Способ задания исходной области выбирается в зависимости от того, где они расположены:
На том же листе. Если исходные области и область назначения находятся на одном листе, используйте имена или ссылки на диапазоны.
На разных листах. Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя (или ссылку) на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в книге на листе «Бухгалтерия», введите Бухгалтерия ! Бюджет.
В разных книгах. Если исходные области и область назначения находятся в разных книгах, используйте имя книги, имя листа, а затем — ссылку на диапазон. Например, чтобы включить диапазон «Продажи» с листа «Солнечногорск» книги «1999», введите: ' [1999.xls] Солнечногорск' !Продажи.
На разных устройствах. Если исходные области и область назначения находятся в разных книгах разных каталогов диска, используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя (или ссылку) на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет», введите: ' [С : \Бюджет\Отдел продаж . xls ] Февраль ' !Оборот.
Использование команды Консолидация
Если используемые исходные области содержат однотипные дань организованы различным образом, то для консолидации данных по категориям нужно использовать метки. В случае консолидации по категориям с использованием меток включить метки в выделенные исходные области.
Если различные исходные области содержат совпадающие категории данных, организованные одинаково, то данная команда может консолидировать данные по расположению, используя ссылки на файлы и ячейки для определения вида консолидированной таблицы.
Консолидация данных по расположению
Для консолидации данных по расположению выполните следующие действия:
1. Укажите верхнюю левую ячейку конечной области консолидируемых данных.
2
.
Выполните команду Данные | Консолидация.
Откроется окно диалога «Консолидация»,
представленное ниже:
3. Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных.
4. Введите в поле Ссылка исходную область консолидируемых данных.
5- Нажмите кнопку Добавить.
6- Повторите шаги 4 и 5 для каждой из консолидируемых исходных областей.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных установите флажок Создавать связи с исходными данными
В поле ввода Список диапазонов перечисляются все ссылки для исходной области-источника, которые были выбраны для консолидации.
С помощью флажков в области «Использовать в качестве имен» определите, использовать ли метки из верхней строки областей-источников, из левого столбца, из того и другого или не использовать вовсе. Эти флажки имеют значение только в том случае, когда вы консолидируете данные по категориям. Исходные области должны быть аккуратно расположены и одинаково помечены, чтобы данная команда работала корректно. При консолидации данных по категориям метки включаются в исходные области, выделенные для консолидации. MS Excel переносит эти метки в область назначения.
При установке флажка Создавать связи с исходными данными при консолидации создаются связи от области назначения к исходным областям-источникам. В этом случае область назначения будет автоматически обновляться изменении данных в исходных областях.
MS Excel создает формулу связи для каждой ячейки и вставляет строки и столбцы в область назначения с целью поддержки формул связи для каждой части источников данных. Затем область назначения объединяется вместе с формулами связи, помещенными в скрытые строки или столбцы.
Задание (по шагам расписанным выше)
Откройте созданную ранее вами таблицу Мебель.xls из вашей папки.
Для консолидации данных откройте чистый лист и задайте название Консолидация этому листу.
Установите курсор в ячейку А1.
Выполните команду Данные | Консолидация. В открывшемся диалоговом окне выберите из раскрывающегося списка Функция функцию сумма.
Введите в поле Ссылка исходную область консолидируемых данных: в качестве консолидируемых данных выберем данные из столбцов штук (количество) различных видов мебели (стол, шкаф, табурет). Мы хотим иметь данные о количестве производимой мебели ежедневно, т.е. сколько суммарно в день было выпущено столов, шкафов, табуретов вместе взятых. Для ввода исходных областей с разных листов выполните следующее:
Щелкнуть по кнопке Ссылка
Открыть лист Стол
Выделить столбец данных Штук и снова щелкнуть по кнопке Ссылка
Нажмите кнопку Добавить
Повторите шаг 5 для каждой из оставшихся консолидируемых исходных областей : шкаф, табурет.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных установите флажок Создавать связи с исходными данными
Нажмите ОК. и изучите полученный результат. Добавьте в столбец результатов итоговую сумму – общее количество мебели , произведенной за три недели.
Самостоятельно , используя консолидацию данных , постройте итоговую таблицу с суммарным ежедневным доходом от производимой мебели.