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

Задание № 9 (Excel)            

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

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

Перед проведением консолидации создайте три таблицы следующим образом.

1.  Три листа таблицы Excel назовите соответственно Январь, Февраль, Март, а четвертый – Квартал_1.

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

3.  На первых трех листах заполните таблицу с колонками Фамилия, Ремонт, Стоимость, Дата.  Введите примерно по 10 – 15 строк и разное количество строк в каждой таблице.

4.  В первом столбце фамилии повторяются, но не обязательно встречаются в каждом месяце. Используйте 5 – 6 фамилий.

5.  В столбец Ремонт введите, например, следующие приборы (бытовые машины): холодильник, пылесос, утюг, эл. чайник,  дрель. Можно придумать другие приборы.

6.  В столбце Стоимость можно выбрать тип данных денежный и ввести стоимость ремонта в рублях.

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

Первая таблица со столбцами: Фамилия, Стоимость. В этой таблице фамилии работников будут встречаться один раз, а в столбце Стоимость – просуммированные стоимости ремонта, выполненные работником за квартал. 

Вторая таблица со столбцами: Фамилия и январь, февраль, март. В трех столбцах с названиями месяцев должны быть приведены стоимость ремонта, выполненного каждым работником в каждом месяце (январе, феврале, марте).

Третья таблица со столбцами: Ремонт, Стоимость. В первом столбце должны быть приведены названия всех отремонтированных приборов и суммарная стоимость ремонта по каждому прибору за квартал.

Четвёртая таблица со столбцами: Ремонт, Январь, Февраль, Март. В первом столбце –  перечень всех отремонтированных приборов, а в столбцах с названием месяцев – стоимость их ремонта за каждый месяц.

Пятая таблица со столбцами: Ремонт, Январь, Февраль, Март. В первом столбце – перечень всех отремонтированных приборов, в трёх последних – их количество в каждом месяце.

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

Порядок проведения консолидации для получения пяти итоговых таблиц следующий.

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

2.  В диалоговом окне Консолидация в качестве Функции выберите Сумма.

3.  В текстовом окне Ссылка нажмите справа кнопку свертывания диалогового окна.

4.  Перейдите в на лист январь и выделите массив &А&1:&C&13 (если в таблице на листе Январь последняя строка13) и нажмите кнопку свертывания диалогового окна.