Задача вычисления расстояния

Предположим, что объект проехал 125 км со скоростью 70 км/час. Необходимо вычислить время, затраченное на преодоление этого расстояния.

2.1. Выберите команду Поиск решения и в открывшемся окне щёлкните по кнопке Восстановить.

2.2. В поле Установить целевую ячейку введите В12, щёлкнув левой кнопкой мыши по соответствующей ячейке.

2.3. Выберите переключатель значению и введите в соседнее поле число 125.

2.4. Поставьте курсор в поле Изменяя ячейки и выделите диапазон В10:В11. Нажмите кнопку Добавить.

2.5. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку введите В11. В раскрывающемся списке выберите элемент =, а в поле Ограничение введите число 70, щёлкните по кнопке ОК. Вид диалогового окна Поиск решения должен совпадать с тем, что представлено на рисунке 4.

Рис. 4. Окно средства Поиск решения. Вычисление расстояния

2.7. Нажмите кнопку Выполнить, затем ОК. Сравните полученные результаты с представленными на рисунке 5.

	А	В
	Вычисление расстояния
	время (мин)
	скорость (км/час)
	Расстояние (км)

Рис. 5. Результаты поиска решения при вычислении расстояния

Расчёты по банковским кредитам

Подготовьте таблицу как на рисунке 6.

	А	В
	Сумма кредита	6 000 000р.
	Срок кредита (месяцы)
	Процентная ставка (годовая)	9,75%
	Ежемесячные платежи	=ПЛТ(В3/12;В2;В1)

Рис. 6. Таблица данных для расчётов по банковским кредитам

3.1. Выберите команду Поиск решения и в открывшемся окне щёлкните по кнопке Восстановить.

3.2. В поле Установить целевую ячейку введите В4, щёлкнув левой кнопкой мыши по соответствующей ячейке.

3.3. Выберите переключатель максимальному значению и введите в соседнее поле число 125.

3.4. Поставьте курсор в поле Изменяя ячейки и выделите диапазон В1:В3. Нажмите кнопку Добавить.

3.5. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку введите В2. В раскрывающемся списке выберите элемент =, а в поле Ограничение введите число 60, щёлкните по кнопке Добавить.

3.6. В поле Ссылка на ячейку введите В3, в раскрывающемся списке выберите элемент =, а в поле Ограничение введите число 0,1 и нажмите кнопку Добавить.

3.7. В поле Ссылка на ячейку введите В4, в раскрывающемся списке выберите элемент =, а в поле Ограничение введите число -45000. Нажмите кнопку ОК.

3.8. Нажмите кнопку Выполнить, затем ОК. Сравните полученные результаты с рисунком 7.

	А	В
	Сумма кредита	2 117 942р.
	Срок кредита (месяцы)
	Процентная ставка (годовая)	10,00%
	Ежемесячные платежи	-45 000,00р.

Рис. 7. Результаты поиска решения при расчете банковских кредитов

Структура производства.

Откройте файл «к лабораторной работе №9». На листе Структура производства представлена модель предприятия, выпускающего телевизоры, стерео- и акустические системы и использующее общий склад комплектующих. В решаемых здесь задачах необходимо найти оптимальное соотношение объёмов выпуска изделий с учётом ограниченности запаса комплектующих. Следует также учитывать уменьшение удельной прибыли при увеличении объёмов производства в связи с дополнительными затратами на сбыт.

Задача максимизации прибыли (ячейка D18) решается путём определения структуры выпуска продукции (т.е. изменяя значения в диапазоне ячеек D9:F9) с учётом запаса комплектующих (значения в диапазоне С11:С15 не должны превышать соответствующие значения из диапазона В11:В15). Для решения этой задачи необходимо выполнить следующие действия.

4.1. В окне Поиск решения щёлкните по кнопке Восстановить.

4.2. В поле Установить целевую ячейку введите D18, щёлкнув левой кнопкой мыши по соответствующей ячейке.

4.3. Выберите переключатель максимальному значению,а в поле Изменяя ячейки и выделите диапазон D9:F9. Нажмите кнопку Добавить.

4.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон С11:С15, а в поле Ограничение выделите диапазон В11:В15, щёлкните по кнопке Добавить.

4.5. В поле Ссылка на ячейку введите диапазон D9:F9, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку ОК.

4.6. В окне Поиск решения нажмите кнопку Параметры и удалите (если он есть) флажок Линейная модель. Нажмите ОК.

4.7. Нажмите кнопку Выполнить.

4.8. Создайте отчёт «Результаты» по результатам поиска решения. Для этого, в окне Результаты поиска решения в списке Тип отчёта выберите Результаты, затем нажмите ОК.

Внимание! Отчёт будет создан на новом листе.

5. График занятости.На одноимённом листе представлена модель, в которой требуется составить график занятости персонала, обеспечивающего потребности в работниках при минимальных затратах на оплату труда. В данном примере ставки одинаковы, поэтому снижение числа ежедневно занятых сотрудников приводит к уменьшению затрат на оплату труда при пятидневной рабочей неделе с двумя выходными подряд.

Решение задачи минимизации расходов на оплату труда (ячейка D20) путём определения числа работников в группах с разными графиками работы (диапазон D7: D 13), при этом необходимо учитывать, что число работников в группе не может быть отрицательным и дробным числом, а число ежедневно занятых работников (диапазон F15:L15) не должно быть меньше ежедневной потребности (диапазон F17:L17). Выбор линейной модели ускорит получение результата

5.1. В окне Поиск решения щёлкните по кнопке Восстановить.

5.2. В поле Установить целевую ячейку введите D20, щёлкнув левой кнопкой мыши по соответствующей ячейке.

5.3. Выберите переключатель минимальному значению,а в поле Изменяя ячейки выделите диапазон D7: D 13. Нажмите кнопку Добавить.

5.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон D7: D 13, в раскрывающемся списке выберите цел., щёлкните по кнопке Добавить.

5.5. В поле Ссылка на ячейку введите D7: D13, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку Добавить.

5.6. В поле Ссылка на ячейку введите F15:L15, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите F17:L17. Нажмите кнопку ОК.

5.7. В окне Поиск решения нажмите кнопку Параметры и установите флажок Линейная модель. Нажмите ОК.

5.8. Нажмите кнопку Выполнить.

5.9. Создайте отчёт «Устойчивость» по результатам поиска решения. Для этого, в окне Результаты поиска решения в списке Тип отчёта выберите Устойчивость, затем нажмите ОК.

6. Управление капиталом.В данном примере представлена модель, в которой требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах. Одной из задач является управление средствами и краткосрочными вложениями с максимальной прибылью при сохранении достаточного резерва для покрытия расходов. Более доходными могут оказаться долгосрочные депозиты. Однако краткосрочные депозиты представляют более гибкие возможности управления финансовыми средствами.

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

Решим задачу максимизации дохода по процентам (ячейка Н8) путём определения девяти сумм:

Ø Ежемесячные суммы для 1-месячных депозитов (ячейки В14:G14);

Ø Суммы квартальных депозитов в 1- и 4-м месяцах (ячейки В15:Е15);

Ø Сумму шестимесячного депозита в 1-м месяце (ячейка В16).

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

6.1. В окне Поиск решения щёлкните по кнопке Восстановить.

6.2. В поле Установить целевую ячейку введите Н8, щёлкнув левой кнопкой мыши по соответствующей ячейке.

6.3. Выберите переключатель максимальному значению,а в поле Изменяя ячейки и выделите диапазон В14:G14, затем, удерживая нажатой клавишу Ctrl, последовательно выделите ячейки В15, Е15 и В16. Нажмите кнопку Добавить.

6.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон В14:G14, в раскрывающемся списке выберите >=, в поле Ограничение введите 0, щёлкните по кнопке Добавить.

6.5. В поле Ссылка на ячейку введите В15:В16, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку Добавить.

6.6. В поле Ссылка на ячейку введите Е15, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку ОК.

6.7. В поле Ссылка на ячейку выделите диапазон В18:Н18, в раскрывающемся списке выберите >=, а в поле Ограничение введите 100000 щёлкните по кнопке Добавить.

6.8. В окне Поиск решения нажмите кнопку Параметры и установите флажок Линейная модель. Нажмите ОК.

6.9. Нажмите кнопку Выполнить.

6.10. Создайте отчёт «Пределы» по результатам поиска решения. Для этого, в окне Результаты поиска решения в списке Тип отчёта выберите Пределы, затем нажмите ОК.

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

Решим задачу максимизации прибыли по акциям (ячейка Е18) путём определения доли каждой акции (диапазон Е10:Е14), при этом должны выполняться ограничения: доли акций не должны быть отрицательными числами и их сумма (ячейка Е16) должна равняться 1, а суммарное изменение акций (ячейка G18) не должна превышать 7,1%. Для решения этой задачи необходимо выполнить следующие действия.

7.1. В окне Поиск решения щёлкните по кнопке Восстановить.

7.2. В поле Установить целевую ячейку введите Е18, щёлкнув левой кнопкой мыши по соответствующей ячейке.

7.3. Выберите переключатель максимальному значению,а в поле Изменяя ячейки и выделите диапазон Е10:Е14. Нажмите кнопку Добавить.

7.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон Е10:Е14, в раскрывающемся списке выберите >=, в поле Ограничение введите 0, щёлкните по кнопке Добавить.

7.5. В поле Ссылка на ячейку введите Е16, а в поле Ограничение введите 0,071. Нажмите кнопку ОК.

7.6. В окне Поиск решения нажмите кнопку Параметры и снимите (если он установлен) флажок Линейная модель. Нажмите ОК.

7.7. Нажмите кнопку Выполнить, затем ОК.

8. Самостоятельная работа.На листе «Транспортная задача» представлена модель, в которой требуется минимизировать затраты на перевозку товаров от производителей на торговые склады. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей.

Решать задачу минимизации общих транспортных расходов (ячейка В20) следует путём определения структуры перевозок (т.е. изменяя объёмы перевозок от каждого из заводов к каждому складу – значения в диапазоне С8:G10), при этом количество перевезённых грузов (диапазон В8:В10) не может превышать производственных возможностей заводов (диапазон В16:В18), и количество доставляемых грузов (диапазон С12:G12) не должно быть меньше потребностей складов (диапазон С14:G14). Кроме того, должно выполняться «техническое» требование – значение объемов перевозок (диапазон С8: G10) не могут быть отрицательными числами. Создайте отчёт по результатам поиска решения.

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

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

9.1. Необходимо проследить изменение курса валют в течение второго полугодия 2009 года. Для этого подготовьте таблицу в соответствии с рисунком 8.

	А	В	С
	месяц	евро	доллар
	июль	44,88	31,84
	август	45,08	31,49
	сентябрь	44,82	30,96
	октябрь	43,70	29,50
	ноябрь	43,62	29,14
	декабрь	43,80	29,90

Рис. 8. Таблица изменения стоимости валют

9.2. Постройте график по данным таблицы.

9.3. Измените вертикальный формат оси:

- минимальное значение: фиксированное 20,0

- максимальное значение: фиксированное 50,0

- цена основных делений: фиксированное 1,0

- цена промежуточных делений: фиксированное 0,5

9.4.Добавьте на график линию тренда. Для этого перейдите на вкладку Макет, нажмите кнопку Анализ и выберите Линия тренда.

9.5.В открывшемся окне выберите Линейное приближение, а в следующем окне выберите евро.

9.6. Аналогично постройте линию тренда для доллара, выбрав Линейный прогноз.

9.7.Щёлкните правой кнопкой мыши по линии тренда евро и в левой части открывшегося окна Формат линии тренда в разделе Прогноз в окошке вперёд на: периодов впишите 6,0. отметьте галочкой строку поместить на диаграмму величину достоверности аппроксимации (R^2).

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

Возможные проблемы при работе со средствами «Поиск решения»

Проблемы до начала работы со средством «Поиск решения»

Сразу после выбора команды Сервис/ Поиск решения могут появиться следующие сообщения об ошибках.

Активный документ не является рабочим листом или защищен. Это сообщение появляется тогда, когда текущий рабочий лист защищен от изменений. Решение проблемы — снимите защиту с листа.

Невозможно выполнить данную команду в режиме группового редактирования. Это сообщение появляется в случае, если несколько рабочих листов объединены в группу. Решение проблемы — разгруппируйте листы и работайте только с одним рабочим листом.

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

Сообщения об ошибках диалогового окна «Поиск решения»

Неправильный ввод данных в диалоговом окне Поиск решения может инициировать такие сообщения об ошибках:

Целевая ячейка должна содержать формулу. Это сообщение появляется тогда, когда в ячейке, адрес которой указан в поле Установить целевую ячейку, не содержится формула. Решение проблемы — правильно ввести адрес целевой ячейки. Помните, что целевая ячейка обязательно должна содержать формулу.

Целевая ячейка должна быть единственной и располагаться на активном листе. Это сообщение появляется в случае, если в диалоговом окне Поиск решения вы щелкнули на кнопке Выполнить, но в поле Установить целевую ячейку содержится ссылка на диапазон ячеек, а не на одну ячейку, или это ссылка на ячейку, которая находится на листе, отличном от текущего. Решение проблемы — введите правильный адрес в поле Установить целевую ячейку.

Не поставлена задача для поиска решения. Это сообщение появляется в том случае, если вы щелкнули на кнопке Выполнить, но в диалоговом окне Поиск решения не указали или адрес целевой ячейки, или адреса изменяемых ячеек. Решение проблемы — введите адреса целевой ячейки и изменяемых ячеек.

Смежные ячейки должны находиться на активном листе. Это сообщение появляется тогда, когда в поле Изменяя ячейки имеется ссылка на ячейки рабочего листа, отличного от текущего листа. Решение проблемы — правильно введите адреса изменяемых ячеек.

Невозможно определение смежных ячеек без задания целевой. Сообщение появляется, если вы щелкнули на кнопке Предположить, но в поле Установить целевую ячейку не содержится адреса ячейки. Исправление ошибки — сначала введите адрес целевой ячейки, а затем щелкните на кнопке Предположить.

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

Ограничение должно быть числом, простой ссылкой или формулой с числовыми значениями. Это сообщение появляется, если в диалоговом окне Добавление ограничения (или в окне Изменение ограничения) вы щелкнули на кнопке ОК, но поле Ограничение пусто, содержит текст или адрес ячейки, которая содержит нечисловое значение. Исправление ошибки — измените содержимое поля Ограничение или щелкните на кнопке Отменить, а затем снова откройте это диалоговое окно.

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

Сообщения об ошибках диалогового окна «Параметры поиска решения»

Средство не может найти подходящего решения. Средство Поиск решения не смогло найти набор значений изменяемых ячеек, которые удовлетворяли бы всем ограничениям в пределах точности, задаваемой параметром Относительное отклонение диалогового окна Параметры поиска решения. Это сообщение обычно появляется тогда, когда ограничения модели противоречивы. Причиной появления этого сообщения для нелинейных моделей может быть то, что процедура поиска не может найти допустимое решение вблизи начальных значе­ний изменяемых ячеек. В этом случае нужно ввести другие значения в изменяемые ячейки и повторно запустить Поиск решения.

При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения. При пересчете формул модели в ячейках рабочего листа обнаружены ошибки, такие как #ДЕЛ/0! или #ЧИСЛО!. В этих условиях Поиск решения останавливает свою рабо­ту. Решение проблемы — найдите ячейку, содержащую ошибку, и исправьте формулу. Можно также добавить ограничения, исключающие значения, при которых возникают ошибки.

Значения целевой ячейки не сходятся. Это сообщение означает, что значения целевой функции неограниченно возрастают (или убыва­ют), даже если все ограничения выполняются. Наиболее вероятной причиной появления такого сообщения являются пропущенные огра­ничения. Решение проблемы — проверьте, не пропустили ли вы ка­кое-либо ограничение и нет ли других логических ошибок, которые делают «неработающими» некоторые ограничения. Для нелинейной модели, возможно, была неправильно определена формула для вы­числения целевой функции.

Условия для линейной модели не выполняются. Это сообщение появ­ляется в том случае, если в диалоговом окне Параметры поиска решения установлен флажок Линейная модель, но расчеты, выполняемые сред­ством Поиск решения, не согласуются с линейной моделью. Решение проблемы — либо пересмотрите формулировку модели, чтобы опреде­лить и устранить источник нелинейности, либо примите нелинейную формулировку модели и откажитесь от линейной оптимизации.

Мало памяти для решения задачи. Это сообщение появляется, ког­да Поиск решения определяет, что для оптимизации модели недоста­точно памяти. Часто это сообщение появляется в тех случаях, когда в Excel открыто слишком много рабочих книг или в дополнение к Excel открыто много других приложений. Решение проблемы — попробуйте закрыть рабочие книги и приложения, не имеющие отношения к решаемой задаче.

Поиск остановлен (истекло заданное на поиск время) и Поиск решения остановлен (достигнуто максимальное число итераций). Эти со­общения появляются после того, как в диалоговом окне Текущее состояние поиска решения вы щелкнули на кнопке Стоп после остановки средства Поиск решения вследствие окончания време­ни, отведенного на процесс оптимизации, или выполнения макси­мального количества итераций (параметры Максимальное время и Предельное число итераций в диалоговом окне Параметры поиска решения), но достичь оптимального решения не удалось. Решение проблемы — можно увеличить значение указанных параметров, но сначала необходимо исследовать текущие решения и найти при­чину длительного процесса вычислений, быть может, это большой разброс возможных значений переменных решения (плохо мас­штабированная модель).

Лабораторная работа №10

Тема: Сценарии

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

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

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

1. Создание и работа со сценариями

1.1. Создайте таблицу для расчёта амортизационных отчислений оборудования (рис. 1).

Начальная стоимость	180 000, 00р.
Остаточная стоимость	25 000, 00р.
Срок эксплуатации
Ежегодные амортизационные отчисления	25 833, 33

Рис. 1. Таблица данных для расчёта амортизационных отчислений

В ячейку, вычисляющую ежегодные амортизационные отчисления, введите формулу: =АПЛ(начальная стоимость;остаточная стоимость;срок эксплуатации). АПЛ – функция, возвращающая величину амортизации актива за один период, рассчитанную линейным методом.

1.2. Вызовите окно Диспетчера сценариев: на вкладке Данные, в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите Диспетчер сценариев. В появившемся окне Диспетчера сценариев нажмите кнопку Добавить. В окне Добавление сценария в поле Название сценария впишите имя создаваемого сценария, например, СЦ1.

Внимание! Имена сценариев не должны повторяться.

1.3. В поле Изменяемые ячейки введите диапазон, в который будут подставляться варианты значений (ссылки на ячейки, содержащие значения). Нажмите ОК.

1.4. В следующем окне во всех полях измените значения, например: начальная стоимость – 210 000,00р., остаточная стоимость – 21 000,00р., срок эксплуатации – 9 лет.

1.5. Нажмите кнопку Добавить и создайте ещё два сценария с разными вариантами значений. Нажмите кнопку ОК.

1.6. Для того чтобы просмотреть каждый сценарий, выделите Сц1 и нажмите кнопку Вывести. В таблице должны появиться значения для первого сценария. Затем аналогично посмотрите второй и третий сценарии.

1.7. Общую картину удобнее всего увидеть в режиме отчёта. В окне Диспетчера сценариев нажмите кнопку Отчёт. Отчёт будет создан на новом листе книги Excel. В отчёте представляются все варианты сценариев. Анализ возможных сценариев поможет принять экономически обоснованное решение.

1.8. Для редактирования сценария в диалоговом окне Диспетчера сценариев щёлкните по кнопке Изменить и в открывшемся окне внесите нужные изменения.

Внимание! Обратите внимание, в отчётах заголовки строк обозначены адресами ячеек с данными и не совсем понятно, какие именно данные представлены. Чтобы отчёт был более наглядным, всем изменяемым ячейкам нужно присваивать имена.

2. Самостоятельная работа. Использование сценариев для прогноза объемов продаж

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

	А	В	С	D
	Предлагаемая розничная цена одной упаковки
	Оптовая скидка	0,15
		Предполагаемое количество проданных упаковок	Стоимость розничных продаж	Чистая стоимость
	регион
	Северный
	Восточный
	Южный
	Западный
	всего

Рис. 2. Таблица данных для создания сценария

Всем изменяемым ячейкам (В1:В2; В5:В8; С5:С8) необходимо присвоить соответствующие имена. Например, ячейка С5 имеет имя Северный_розн. Для этого на вкладке Формулы в группе Определённые имена нажмите кнопку Присвоить имя. В поле Имя: задайте имя ячейки. В поле Область: выберите лист, на котором расположена таблица. Нажмите ОК. Таблица для расчета объемов продаж состоит из двух разделов.

1. В диапазоне А1:В2 записаны предполагаемая розничная цена одной упаковки напитков и оптовая скидка, вычисляемая как процент от розничной цены.

2. В диапазоне A5:D8 по каждому региону приведены предполагаемые объемы продаж упаковок напитков, их розничная и чистая (оптовая) стоимости.Стоимость розничных продаж вычисляется по формуле: =Предполагаемое количество проданных упаковок*Предполагаемая розничная цена*Оптовая скидка. Чистая стоимость вычисляется по формуле: =Стоимость розничных продаж*(1-Оптовая скидка/предполагаемая розничная цена).

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

2. Создайте отчёт об изменении продаж в зависимости от сезона.

3. Защита сценариев от изменений

Для защиты сценариев от возможных изменений выполните следующие действия.

3.1. Откройте окно Диспетчер сценариев и в списке Сценарии щелкните на том сценарии, который хотите защитить от изменений, и затем щелкните на кнопке Изменить.

3.2. В диалоговом окне Изменение сценария в области Защита уста­новите флажок запретить изменения и/или флажок скрыть (в этом случае данный сценарий не будет отображаться в списке Сценарии диалогового окна Диспетчер сценариев).

3.3. Щелкните на кнопке ОК. В открывшемся диалоговом окне Значения ячеек сценария щел­кните на кнопке ОК и закройте окно Диспетчер сценариев.

3.4. Чтобы ввести в действие защиту сценария перейдите на вкладку Рецензирование в группе Изменения нажмите кнопку Защитить лист. В открывшемся окне задайте пароль и отметьте галочкой строку изменение сценариев.

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

Внимание! Если вы решили применить пароль, обязательно запишите его и сохраните в надежном месте — Excel не сможет подсказать забытый вами пароль.

3.6. Для отключения защиты сценариев на вкладке Рецензирование в группе Изменения нажмите кнопку Снять защиту листа. Если вы использовали пароль, то появится диалоговое окно Снять защиту листа, в котором вы долж­ны ввести пароль. После ввода правильного пароля щелкните в этом окне на кнопке ОК.

Внимание! Хотя изменение и удаление созданных ранее сценариев в защищенном рабочем листе невозможно, создавать новые сценарии на таком же листе можно. При этом разрешается изменять значения в защищенных ячей­ках, но нельзя их (ячейки) удалять.