Лабораторная работа 18

КОНСОЛИДАЦИЯ ДАННЫХ

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

Задача1

1. Проведите консолидацию данных (вкладка Данные —группаРабота с данными — Консолидация)по сбыту на основе приведённой ниже таблицы.

Исходные данные отсортировать по районам и скопировать блоками в соответствии с образцом на новый лист.

Рядом с исходной таблицей создать консолидированную таблицу.

Выделить область ячеек как показано на рисунке.

Столбцов и строк в консолидированной таблице должно быть не меньше чем в исходной.

И провести консолидацию вкладка Данные – Работа с данными – Консолидация

В окнеКонсолидация– Использоватьв качестве имени поставить «˅» напротивзначения левого столбца , тогда названия продуктов добавятся автоматически в консолидированную таблицу. В строкеСсылка указать диапазон ячеек относящихся квосточному району и нажать кнопкуДобавить

Ссылка» переместится вСписок диапазонов

Чтобы добавить данные по Западному району нужно вернуться вокно Ссылка убрать информацию (выделить и нажать Delete) затемвыделить блок ячеек относящихся к западному району и нажатькнопку Добавить.

Все действия повторить с оставшимися районами

После обработки информации по районам окно Консолидации должно быть таким

После нажатия на кнопкуОКполучим консолидированную таблицу следующего вида

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

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

2. Создайте на отдельном листе консолидированную таблицу со связью. Для этого перейдите на новый лист. Выделите область ячеек для консолидированной таблицы. Откройте окно Консолидации — вкладка Данные— группа Работа сданными — Консолидация— в окне Ссылка указать диапазон ячеек по восточному району (ссылка на лист с данными по всем районам Выбрать данные по Восточному району, затем по западному и т.д.). И повторить все действия описанные выше для заполнения информации в окне Консолидация.

3. Проверьте автоматическое обновление, изменив одно или не­сколько значений в исходной таблице. Есть еще преимущество при установлении связи: данные при этом консолидируются с применением функции структурирования (на втором уровне будут представлены отдельные значения, на основе которых вычисляются консолидированные данные). Просмотрите возникшие группы структуры.

Задача 2. На основе исходных данных по продажам хлебобулочных изделий в трех магазинах ТОО «Веселый пекарь» создать итоговую консолидированную таблицу с обобщенными данными по трем магазинам по всем видам продукции.

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

Вычислите в консолидированной таблице долю продаж для каждого вида товаров.

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

7. Сохраните файл.