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

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

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

Задача 1

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

Месяц	Год	Продукция	Продавец	Сбыт	Объем	Район
Jan		молоко	Петров			Восточный
Jan		молоко	Петров			Восточный
Sept		бакалея	Марченко			Восточный
Jan		бакалея	Петров			Восточный
Feb		напитки	Никитин			Восточный
Jan		молоко	Петров			Восточный
May		напитки	Сидоров			Восточный
Feb		бакалея	Марченко			Восточный
Jan		молоко	Никитин			Западный
May		бакалея	Сидоров			Западный
March		мясо	Петров			Западный
April		напитки	Марченко			Западный
March		молоко	Сидоров			Западный
April		бакалея	Никитин			Западный
Sept		бакалея	Сидоров			Западный
April		мясо	Никитин			Западный
May		напитки	Сидоров			Северный
Feb		мясо	Петров			Северный
April		напитки	Никитин			Северный
Feb		бакалея	Марченко			Южный
May		мясо	Марченко			Южный
Sept		напитки	Петров			Южный
Feb		бакалея	Никитин			Южный

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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