Фгбоу во «государственный аграрный

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА

РОССИЙСКОЙ ФЕДЕРАЦИИ

ФГБОУ ВО «ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ

УНИВЕРСИТЕТ СЕВЕРНОГО ЗАУРАЛЬЯ»

ИНСТИТУТ ЭКОНОМИКИ И ФИНАНСОВ

КАФЕДРА ЭКОНОМИКО-МАТЕМАТИЧЕСКИХ МЕТОДОВ

И ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ

ИСПОЛЬЗОВАНИЕ MS EXCEL 2007

В ПРОФЕССИОНАЛЬНОЙ ДЕЯТЕЛЬНОСТИ

 

Методические указания

к выполнению лабораторных работ

 

 

Тюмень 2016

УДК

ББК

 

 

Автор-составитель: С.М. Каюгина

Использование MS EXCEL 2007 в профессиональной деятельности: Методические указания к выполнению лабораторных работ, для направлений подготовки осуществляемых в ГАУ Северного Зауралья / ГАУСЗ; Автор-сост С.М. Каюгина. – Тюмень, 2016. – 12 с.

 

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

 

Утверждены методической комиссией института экономики и финансов ГАУСЗ (протокол № 6.1 от 29 февраля 2016 г.)

 

Рецензент: Ерёмина Д.В., доцент кафедры ЭММ и ВТ

 

Ответственный за выпуск: Д.В. Ерёмина, к.с.-х.н., и.о. зав.кафедрой ЭММ и ВТ

 

© Государственный аграрный университет Северного Зауралья, 2016

© С.М. Каюгина, составление, 2016

 

ЛАБОРАТОРНАЯ РАБОТА 1. РАБОТА СО ВСТРОЕННЫМИ ФУНКЦИЯМИ

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

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

2. Составьте Отчет за месяц о проделанной работе и выплатах вознаграждений агентам (таблица 2).

3. Постройте диаграммы, отображающие данные отчета:

- степень участия каждого агента в общем объеме заключенных сделок;

- соотношение оплаты труда каждого агента;

- соотношение среднего размера заказа по каждому агенту.

Таблица 1 – Книга учета договоров за 1-й квартал 2013 года

№ дого-вора	Дата заключения договора	Приемле-мый для заказчика срок, в днях	Дата выполнения заказа	Сумма заказа	Фамилия агента	Комис-сион-ные	Премия
	02.01.2013		02.03.2013		Волков
	02.01.2013		02.03.2013		Гладков
	03.01.2013		03.10.2013		Волков
	05.01.2013		05.02.2013		Долгов
	05.01.2013		05.02.2013		Спицын
	06.01.2013		06.03.2013		Петров
	08.01.2013		08.02.2013		Шишов
	09.01.2013		09.03.2013		Волков
	09.01.2013		09.02.2013		Спицын
	11.01.2013		11.02.2013		Волков
	12.01.2013		12.01.2013		Гладков
	15.01.2013		15.01.2013		Долгов
	16.01.2013		16.03.2013		Гладков
	17.01.2013		17.02.2013		Долгов
	18.01.2013		18.01.2013		Шишов
	20.01.2013		20.02.2013		Долгов

Таблица 2 – Отчет за 1-й квартал 2013 года

Фамилия агента	Количество заказов	Общий объем заказов в руб.	Средний размер одного заказа	Оплата за выполненную работу
Волков
Гладков
Долгов
Петров
Спицын
Шишов
ИТОГО			х	х
Средний размер заказа		х
Средняя заработная плата агента по фирме	х

Алгоритм расчета

Оплата труда агентов складывается из двух источников: комиссионное вознаграждение и премия.

1. Комиссионное вознаграждение за заказ рассчитывается по следующему правилу: если сумма заказа свыше 250000 размер комиссионных составит 5% от суммы заказа, в противном случае – 3%.

2. Премия за выполнение заказа в заявленный срок исчисляется в размере 0,5 % от суммы заказа. Для определения срока, за который заказ выполнен, находится разность между Датой выполнения заказа и Датой заключения договора.

Методические указания

1. Размер комиссионных и Премия (таблица 1) рассчитываются с помощью логической функции ЕСЛИ.

2. Количество сделок, заключенных каждым агентом (таблица 2) рассчитывается с помощью функции СЧЕТЕСЛИ.

3. Общий объем сделок, заключенных каждым агентом и Оплата за выполненную работу каждому агенту за месяц (таблица 2) определяется с помощью функции СУММЕСЛИ.

4. Средняя величина заказа и Средняя зарплата агента по фирме рассчитывается с использованием функции СРЗНАЧ.

 

ЛАБОРАТОРНАЯ РАБОТА 2. СОЗДАНИЕ ТАБЛИЦ С ИСПОЛЬЗОВАНИЕМ ПРОМЕЖУТОЧНЫХ ИТОГОВ. ФУНКЦИИ ВПР И ГПР

Промежуточные итоги

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

На основании данных, приведенных в таблице 3, рассчитайте:

- объем продаж за период по каждому наименованию продукции;

- размер вознаграждения за период по каждому продавцу (комиссионные от суммы выручки 5%);

- постройте графики и диаграммы, наиболее наглядно отображающие полученные результаты.

Таблица 3 – Журнал о продажах продукции

Дата	Наименование	Продавец	Количество	Цена	Сумма	Комиссионные
2 апр.	Optima P6	Долгов
2 апр.	Action K7	Петров
3 апр.	IDE	Борков
3 апр	DVD	Петров
4 апр.	Keyboard	Долгов
4 апр.	Keyboard	Петров
4 апр.	Optima P6	Петров
5 апр.	DVD	Борков
5 апр.	Mouse	Петров
6 апр.	Action P6	Борков
6 апр.	Optima P6	Долгов
6 апр.	Keyboard	Долгов
7 апр.	Action K7	Петров
7 апр.	IDE	Петров
7 апр.	Action P6	Долгов

Методические указания

1. Создайте в своей папке рабочую книгу с именем Продажи.

2. На первом листе создайте таблицу 3, введите в неё исходные данные и расчетные формулы. Переименуйте лист в Журнал продаж.

3. Скопируйте Журнал на второй лист и отсортируйте по графе «Наименование». Подведите итоги по графам «Количество» и «Сумма», используя кнопку Промежуточный итог на вкладке Данные. Переименуйте лист в Отчет по продукции. Постройте диаграмму, характеризующую структуру выручки.

4. Скопируйте Журнал на третий лист и отсортируйте по графе «Продавец». Подведите итоги по графам «Сумма» и «Комиссионные», используя кнопку Промежуточный итог на вкладке Данные. Переименуйте лист в Отчет по продавцам. Создайте диаграмму, отражающую соотношение полученного продавцами вознаграждения.

5. Скопируйте Журнал на четвертый лист и отсортируйте по графе «Дата». Подведите итоги по графам «Количество» и «Сумма», используя кнопку Промежуточный итог на вкладке Данные. Переименуйте лист в Отчет по датам. Создайте диаграмму, отражающую соотношение продаж продукции по датам.

Подстановка цены

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

Создайте две таблицы - таблицу заказов (не менее 30 строк) и прайс-лист (не менее 10 наименований). Для облегчения заполнения поля Наименование используйте выпадающий список. Для этого выделите в таблице заказов диапазон столбца Наименование (например, В3:В33), на вкладке Данные щелкните по кнопке Проверка данных, выберите вариант проверки Список и укажите в поле Источник адрес диапазона с перечнем наименований фруктов и овощей в таблице прайс-лист.

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

Методические указания

В наборе функций Excel, в категории Ссылки и массивы имеется функция ВПР. Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.).

Для простоты дальнейшего использования функции сразу дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа, кроме "шапки", нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. В дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте Мастер функций (вкладка Формулы - Вставка функции). В категории Ссылки и массивы найдите функцию ВПР и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

· Искомое значение - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.

· Таблица - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее.

· Номер_столбца - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.

§ Интервальный_просмотр - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА. Если введено значение 0 или ЛОЖЬ, то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе указанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных). Если введено значение 1 или ИСТИНА, то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле, поэтому для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать.

Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ступенчатые скидки

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

Магазин дает скидки на оптовые закупки, причем процент скидки зависит от количества купленного товара. Например, при покупке от 5 до 20 штук дается скидка 2%, при покупке от 20 до 50 штук - 6% и при закупке партии от 50 и более штук - 10%. Необходимо вычислить процент скидки при вводе количества купленного товара.

Методические указания

Составляем таблицу скидок (см. рисунок). Для расчета процента скидки используем следующую формулу:

где

· ВПР - функция, которая проверяет значение первого столбца таблицы скидок и ищет в нем значение максимально похожее на количество купленного товара (С1)

· B6:C9 - ссылка на таблицу скидок (без "шапки")

· 2 - порядковый номер столбца в таблице скидок, из которого мы хотим получить значение скидки

· ИСТИНА - здесь и зарыта "собака". Если в качестве последнего аргумента функции ВПР указать ЛОЖЬ или 0, то функция будет искать строгое совпадение в столбце количества (и в случае на рисунке выдаст ошибку, поскольку значения 22 в таблице скидок нет). А вот если вместо ЛОЖЬ написать ИСТИНА или 1, то функция будет искать не точное, а ближайшее наименьшее значение и выдаст процент скидки для него.

G Используя Специальную вставку транспонируйте таблицу скидок и для расчета размера скидки введите функцию ГПР.

Интерактивная диаграмма

1. Создайте новую рабочую книгу. Сохраните рабочую книгу в папке Выполненные задания под именем Курс_евро.

2. Оформите таблицу с датами и курсами евро.

3. Создайте два именованных динамических диапазона Дата и Курс, введя в качестве ссылки функцию СМЕЩ (на вкладке Формулы щелкните по кнопке , в открывшемся диалоговом окне щелкните по кнопке Создать…). Функция СМЕЩ выдает ссылку на диапазон, смещенный относительно исходного на заданное количество ячеек и имеет несколько аргументов:

· Ссылка - исходная ячейка, от которой идет отсчет (для диапазона Курс это ячейка $B$1).

· Смещение по строкам и столбцам - сдвиг начала диапазона относительно Ссылки: 1 – вниз, 0 – влево.

· Высота - количество ячеек по вертикали, из которых состоит диапазон - определяем количество значений в столбце В при помощи функции СЧЁТ($В:$В).

· Ширина - количество ячеек по горизонтали – 1, так как данные занимают один столбец.

4. Постройте простую гистограмму по данным таблицы, разместив её на текущем листе. Щелкните левой кнопкой мыши по любому столбцу гистограммы. В строке формул будет показана функция РЯД, которая формирует ряды данных и подписей для диаграммы.

5. Подмените в ней диапазоны данных на:

=РЯД(Лист1!$B$1; 'Курс_евро.xlsx'!Дата; 'Курс_евро.xlsx'!Курс;1)

6. Добавьте 3-5 значений дат и курсов евро. Убедитесь в том, что данные были добавлены на диаграмму автоматически.

7. Закройте книгу, сохранив внесенные изменения.

Методика выполнения

1. Создайте полосы прокрутки, с помощью которых легко можно будет мышью перемотать и увеличить любой нужный фрагмент графика.

2. Щелкните поочередно по каждой полосе прокрутки правой кнопкой мыши, выберите пункт Формат объекта и задайте следующие настройки: минимум=1; максимум=60; связать с ячейкой - выделить ячейку справа от соответствующей полосы (для первой это О2, для второй - О4).

Теперь при перемещении ползунков по полосам значение в связанных ячейках О2 и О4 должны меняться в диапазоне от 1 до 60.

3. Переименуйте рабочий лист, дав ему имя Zoom&scroll. Перейдите на вкладку Формулы и в группе команд Определенные имена щелкните по кнопке Диспетчер имен. Создайте 4 поименованных диапазона:

Xs - это диапазон отобранных на полосах прокрутки дат, а Ys - диапазон отобранных значений курсов доллара.

4. Выделите любую ячейку диапазона с данными, перейдите на вкладку Вставка и в группе команд Диаграммы выберите тип гистограмма с группировкой.

5. Выделите столбцы данных на диаграмме и посмотрите в строку формул. Функция РЯД формирует ряды данных и подписей для диаграммы. Подмените в ней диапазоны данных на те, что создали в п.3, не забыв указать имя файла:

= РЯД('Zoom&scroll'!$B$1;'ZoomScroll.xlsх'!Xs;'ZoomScroll.xlsx'!Ys;1)

Методические указания

1. Создайте на Листе 1 каталог с наименованиями и фотографиями товаров, состоящий из двух столбцов (Модель и Фото).

2. На вкладке Формулы щелкните на кнопке Диспетчер имен. Создайте именованный диапазон: имя Фотоальбом, а в качестве адреса введите формулу:

=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1).

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

3. Перейдите на Лист 2 и создайте там ячейку с выпадающим списком для выбора пользователем модели телефона (пусть это будет A1). Для этого выделите ячейку, на вкладке Данные нажмите Проверка данных. Далее в поле Тип данных следует выбрать Список, а в качестве Источника указываем Фотоальбом (не забудьте перед ним добавить знак равенства). Кроме того, ячейке А1 удобно дать имя, например Выбор.

4. Перенесем первую фотографию из фотоальбома к выпадающему списку. Выделите ячейку с первой фотографией (не сам рисунок, а ячейку!) и разверните выпадающий список под кнопкой Копировать на Главной вкладке:

Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем наш мини-скриншот ячейки с фотографией (меню Правка - Вставить или обычное CTRL+V).

5. Теперь необходимо сделать ссылку, которая будет указывать на ячейку с выбранной фотографией. Открываем меню Диспетчер имен на вкладке Формулы и создаем еще один именованный диапазон. Имя ссылки будет Фото, а формула

=СМЕЩ(Лист1!$B$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1).

Технически, функция ПОИСКПОЗ находит ячейку с нужной моделью в каталоге по названию, а функция СМЕЩ затем выдает ссылку на соседнюю справа от найденного названия ячейку, т.е. ячейку с фотографией товара.

6. Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул = Фото и нажать ENTER.

 

ЛАБОРАТОРНАЯ РАБОТА 5. БАЗЫ ДАННЫХ (СОРТИРОВКА, ФИЛЬТРАЦИЯ). СВОДНЫЕ ТАБЛИЦЫ И ДИАГРАММЫ

Задание. Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам овощи и фрукты. Для учёта проданного товара в Excel заполняется таблица. В ней каждая отдельная строка содержит полную информацию об одной отгрузке: кто из менеджеров заключил сделку и с каким заказчиком; какого товара и в каком количестве продано; с какого из складов была отгрузка;когда (месяц и день месяца).

Каждый день к этой таблице будет дописываться несколько десятков строк и к концу, например, года или хотя бы квартала размеры таблицы станут огромными. Допустим необходимо создать отчет по этим данным. Например: сколько и каких товаров продали в каждом месяце, какова сезонность продаж, кто из менеджеров сколько заказов заключил и на какую сумму? Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить с помощью инструмента Ms Excel - сводные таблицы.

Сводные таблицы представляют собой интерактивный метод быстрого суммирования больших объемов данных. Отчеты сводной таблицы специально предназначены для подведения промежуточных итогов и применения статистических функций к числовым данным, суммирования данных по категориям и подкатегориям, а также для создания дополнительных вычислений и формул.

Методические указания

1. Необходимо подготовить в программе Excel таблицу, содержащую следующие поля:

Наименование

Дата

Склад

Цена за тонну

Продано, тонн

Менеджер

Заказчик

Заполните таблицу данными (не менее 30 строк).

Для облегчения заполнения некоторых полей таблицы (Наименование, Склад, Менеджер, Заказчик) используйте выпадающие списки. Для этого предварительно на отдельном листе рабочей книги составьте «справочную» таблицу с перечнем значений полей. Для заполнения цены используйте функцию ВПР.

2. На вкладке Данные щелкните по кнопке Фильтр. Попробуйте разные варианты фильтрации базы данных (по наименованию, по заказчику, по менеджеру, по складу, по дате, по объему продаж). Отмените все фильтры.

3. На вкладке Данные нажмите кнопку Сортировка и примените к базе данных не менее трех уровней сортировки.

4. Скопируйте наименования столбцов базы данных в ячейки I1:O1. В ячейке M2 введите условие отбора, например >=500. Поставьте курсор в любую ячейку базы данных и на вкладке Данные в группе команд Сортировка и фильтр щелкните по кнопке . В открывшемся окне диалога Расширенный фильтр установите переключатель Скопировать результат в другое место. Убедитесь в том, что в поле Исходный диапазон указана вся база данных. В поле Диапазон условий введите I1:O2. В поле Поместить результат в диапазон введите диапазон достаточного размера, чтобы поместились результаты выборки. Нажмите ОК. Посмотрите результат.

5. Очистите результаты фильтрации. Введите другие условия отбора, например, по наименованию товара, фамилии менеджера или названию фирмы-заказчика. Примените новые условия фильтра.

6. Выделите любую ячейку в таблице с исходными данными и нажмите кнопку Сводная таблица на вкладке Вставка. Отобразится диалоговое окно, в котором нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Определите, на какую сумму было продано каждого из наименований: на панели Список полей сводной таблицы перетащите поле Наименование в область Названия строк. Перетащите поле Продано, тонн в область Значения.

7. Измените оформление сводной таблицы, выбрав другой стиль на ленте Конструктор.

8. Измените сводный отчет таким образом, чтобы можно было определить какому заказчику какое наименование было продано и на какую сумму. Для этого перетащите в область Названия столбцов поле Заказчик.

9. Определите, с какими заказчиками, какие менеджеры работали, и на какую сумму. Удалите из области Названия строк поле Наименование, вместо него поместите поле Менеджер.

10. Перейдите на лист с базой данных. Добавьте внизу таблицы строку с еще одной продажей. Перейдите обратно на лист сводной таблицы (Лист4). Обновите данные сводной таблицы (щелкните по сводной таблице, на вкладке Параметры нажмите на кнопку Изменить источник данных, заново выделите исходную таблицу и нажмите ОК).

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

12. Определите, какие продукты сколько раз продавались. Для этого отобразите в сводной таблице только данные по наименованиям в области строк. Щелкните по полю Сумма по полю Продано в списке Значения, области Списка полей сводной таблицы. Выберите команду Параметры полей значений... В окне Параметры полей значений выберите вид операции - Количество.

Нажмите кнопку ОК.

Отмените расчет по количеству наименований.

13. Добавьте в макет в область Наименования строк поле Склад, разместив его над полем Наименование. Сверните группы с номерами складов (на вкладке Параметры, в области Активное поле нажмите кнопку Свернуть все поле).

Удалите поле Склад из области Наименования строк.

14. Рассчитайте общую сумму проданных наименований товаров.

На вкладке Параметры нажмите кнопку Формулы и выберите команду Вычисляемое поле. В окне Вставка вычисляемого поля задайте имя нового поля: «Стоимость». В поле Формула введите: = 'Цена за тонну'* Продано, тонн.

Нажмите ОК.

Задайте для нового, вычисляемого поля Стоимость денежный, рублевый формат.

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

В окне Параметры полей значений нажмите на кнопку Числовой формат.

Задайте в окне Форматы денежный, рублевый формат. Нажмите ОК.

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

16. Перенесите в область Названия столбцов поле Менеджер, в область Фильтр отчета поле Заказчик. Постройте сводную диаграмму. Для этого выделите любую ячейку сводной таблицы. На вкладке Параметры нажмите кнопку Сводная диаграмма. Выберите тип диаграммы – гистограмма. Нажмите ОК.

17. Для сводной диаграммы выполните отбор по одному из менеджеров, выбрав его в области фильтра сводной таблицы в полях легенды.

Отобразите данные по всем менеджерам.

Для сводной диаграммы выполните отбор всех наименований, которых было продано на сумму, превышающую 500 тыс.руб.

Отключите фильтр.

18. Сохраните изменения в рабочей книге, дайте ей имя Склад.

Закройте книгу.

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

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

Методические указания

1. Оформите исходную таблицу:

2. Ячейку F3 введите формулу =СУММПРОИЗВ($B$2:$E$2;B3:E3).Скопируйте формулу в ячейки F5:F7.

3. Выделите оптимизируемую ячейку F3. На вкладке Данные щелкните по кнопке Поиск решения. В окне диалога Поиск решения в поле Установить целевую ячейку уже находится ссылка на выделенную ячейку. При необходимости эту ссылку можно изменить.

4. Укажите, что хотите максимизировать значение целевой ячейки.

5. Перейдите в поле Изменяя ячейки. Изменяемая ячейка – это ячейка, которая может быть изменена в процессе Поиска решения для достижения нужного результата. В данном примере это ячейки В2:Е2 – количество изделий.

6. Нажмите кнопку Добавить, чтобы ввести Ограничения.

7. В окне Добавления ограничения введите первое ограничение: значения в ячейках В2:Е2 должны быть больше нуля. Для этого:

- В поле Ссылка на ячейку укажите ячейки В2:Е2.

- Нажмите клавишу TAB для перехода в следующее поле.

- Выберите Оператор >=.

- В поле Ограничение введите 0.

8. Нажмите кнопку Добавить, чтобы ввести следующее ограничение: F5:F7<= H5:H7 (расход ресурсов не должен превышать их наличие). После ввода ограничения нажмите ОК.

9. Нажмите кнопку Выполнить. По окончании поиска решения появится диалоговое окно результатов. Выберите переключатель Сохранить найденное решение. Нажмите ОК.

10. Сделайте вывод по результатам решения.

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

Фирма «ТЕХНОМИР» хочет разработать план сборки компьютеров. Спрос на компьютеры прогнозируется: в первом квартале – 1000, во втором – 500, в третьем – 3000 и в четвертом – 2000 штук.

При работе в одну смену фирма может собрать 1200 компьютеров каждый квартал при стоимости сборки одного компьютера 100 рублей. Если ввести вторую смену, то ежеквартально можно собирать еще 800 компьютеров, но сборка каждого компьютера во вторую смену обходится дороже – 110 руб. Изготовленные в данном квартале компьютеры могут продаваться в одном из последующих кварталов. При этом хранение каждого компьютера обходится в 25 рублей за квартал.

Определите, сколько компьютеров нужно собирать ежеквартально в первую и во вторую смены, чтобы удовлетворить спрос с минимальными совокупными затратами.

Методические указания

Оформите в EXCEL таблицу (см. ниже) и введите в ячейки следующие зависимости:

С3=СУММ(D3:G3); C4=СУММ(D4:G4); C5=СУММ(E5:G5)

C6=СУММ(E6:G6); C7=СУММ(F7:G7); C8=СУММ(F8:G8); С9=G9

C10=G10; С11=СУММ(D11:G11); D11=СУММ(D3:D4)

E11=СУММ(E3:E6); F11= СУММ(F3:F8); G11= СУММ(G3:G10)

C22= СУММ(D22:G22); D22=СУММПРОИЗВ(D3:D4;D14:D15)

E22= СУММПРОИЗВ(E3:E6;E14:E17); F22= СУММПРОИЗВ(F3:F8;F14:F19)

G22= СУММПРОИЗВ(G3:G10;G14:G21)