Лабораторная работа 6. Консолидация данных.

Трёхмерные формулы

1. Создайте новую рабочую книгу. На каждом из трех листов рабочей книги оформите и заполните данными таблицу по итогам продаж товаров за год. Назовите листы 2011 год, 2012 год, 2013 год.

Наименования	Квартал 1	Квартал 2	Квартал 3	Квартал 4	Всего
Сникерс
Марс
Твикс
Баунти

2. Добавьте новый лист, назовите его Итоги. Оформите на нём таблицу:

Наименования	Квартал 1	Квартал 2	Квартал 3	Квартал 4
Сникерс
Марс
Твикс
Баунти

3. Создание трёхмерной ссылки:

· Выделите ячейку, в которую нужно ввести функцию (в нашем примере это B2).

· Введите = (знак равенства), имя функции СУММ, а затем — открывающую круглую скобку.

· Щелкните ярлычок первого листа, на который нужно создать ссылку.

· Удерживая нажатой клавишу SHIFT, щелкните ярлычок последнего листа, на который необходимо создать ссылку.

· Выделите ячейку (диапазон ячеек), на которую (-ые) нужно создать ссылку (в нашем примере это В2).

· Завершите ввод формулы, а затем нажмите клавишу ВВОД.

Получится формула =СУММ('2011 год:2013 год'!B2). Скопируйте её в оставшиеся пустые ячейки таблицы на листе Итоги.

4. Скопируйте лист с данными 2013 года (перетащите ярлычок листа, удерживая клавишу CTRL). Переименуйте полученную копию листа, дав имя 2014 год. Измените числовые данные в таблице на листе 2014 год.

5. Чтобы на листе Итоги таблица включала данные с учётом добавленного нового листа необходимо перенести его и вставить в любом месте между листами 2011 год и 2013 год. Убедитесь в том, что трёхмерные формулы считают итоги верно.

G Трёхмерные формулы работают успешно в том случае, когда таблицы идентичны по количеству строк и столбцов.

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

1. Создайте новую рабочую книгу. Оформите таблицу, введите данные по десяти сотрудникам, рассчитайте НДФЛ, сумму к выдаче и итоги по столбцам.

ФИО	Оклад	Премия	НДФЛ 13%	К выдаче
Иванов А.А.
….
Итого

2. Переименуйте лист, дав ему имя Зарплата за январь.

3. Скопируйте таблицу на чистый лист. Переименуйте лист, дав ему имя Зарплата за февраль. Повторите эти действия еще 4 раза, при необходимости добавляя новые рабочие листы.

4. Поменяйте данные по некоторым месяцам, например, добавьте в марте столбец Материальная помощь, а в мае добавьте еще одного сотрудника.

5. Перейдите на чистый лист. Создайте консолидированную таблицу с данными о суммарном заработке за полгода. Для этого на вкладке Данные щелкните на кнопке Консолидация, выберитефункцию Сумма. Поставьте курсор в поле Ссылка, перейдите на лист с зарплатой за январь, выделите таблицу, щелкните на кнопке Добавить. По аналогии добавьте диапазоны остальных таблиц. Установите флажки Подписи верхней строки, Значения левого столбца, Создавать связи с исходными данными. Подтвердите создание консолидированной таблицы.

6. Измените числовые данные на нескольких листах (размер оклада кому-то из сотрудников или премию). Убедитесь в том, что консолидированная таблица эти изменения отразила.

ЛАБОРАТОРНАЯ РАБОТА 7. НАДСТРОЙКА «ПОИСК РЕШЕНИЯ»

Задание 1. Оптимизация ассортимента

Содержание задания:

Для производства четырех типов изделий С1, С2, С3, С4 предприятие должно использовать три вида сырья В1, В2, В3, запасы которого на планируемый период составляют соответственно 1000, 600, 150. В приведенной ниже таблице даны нормы расхода каждого вида сырья на производство единицы изделия, прибыль от реализации единицы изделия и запасы сырья по видам.

Таблица – Исходные данные

Виды	Запасы	Нормы расхода на изделия
сырья	сырья	С1	С2	С3	С4
В1
В2
В3
	Прибыль от реализации			2,5

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