Флажок Создавать связи — сбросить. Вполе Использовать в качестве имен— установить флажок Значения левого столбца

8. В поле Ссылка ввести поочередно интервалы исходных данных, из р/л Семестр1 — Семестр4 (без шапки, но с левым столбцом). После ввода каждой ссылки нажимать кнопку Добавить.

9.После ввода последней ссылки щелкнуть ОК.

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

 

Задание 2. Изучите построение анализа решений.

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

Анализ решений с помощью модели «что-если» проведем на следующем примере: требуется взять кредит в размере 10 000 000 р. на 30 лет. Рассчитать ежемесячные выплаты в счет погашения долга и рассмотреть разные варианты процентных ставок. Действуем по следующему алгоритму:

1.Создать р/л Что_если.

2.Ввести на р/л исходные данные (например, C2:D5)

Первый взнос	Нет
Процентная ставка	10%
Срок(месяцы)
Величина займа	10 000 000 р.

и входной интервал (например, В10:В14) - годовые процентные ставки: 9; 9,25; 9,5; 9,75; 10.

3.В ячейки С9 и D9 ввести формулы =ПЛТ(D3/12;D4;D5) и =C9*D4 + D5 соответственно, использующие входные параметры. Вспомогательная ячейка D3 служит для перебора значений из входного интервала (вместо D3 можно использовать любую другую ячейку).

4.Выделить интервал для таблицы данных - наименьший прямоугольный блок, содержащий формулы и все значения из входного интервала, - (B9:D14).

5.Выполнить команду Данные|Таблица подстановки. В диалоговом окне Таблица подстановки выбрать Подставлять значения по столбцам в: или Подставлять значения по строкам в: и щелкнуть ячейку D3.

6.ОК.

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

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

Процентные ставки — 9; 9,25; 9,5; 9,75; 10.

Сроки выплаты - 15, 20, 25, 30, 35 лет (180, 240, 300, 360, 420 месяцев):

1.Ввести в столбец процентные ставки (А25:А29).

2.Ввести в строку правее процентных ставок и на одну ячейку выше сроки выплат (B24:F24).

3.Создать табличную формулу в ячейке А24 на пересечении строки и столбца с исходными данными — =ПЛТ(D33/12;D34;D35).

4.Выделить интервал для таблицы данных — наименьший прямоугольный блок, включающий все исходные данные и табличную формулу — (A24:F29).

5.Выполнить команду Данные|Таблица подстановки. Задать две ячейки ввода для массивов исходных данных. Щелкнуть ячейку D3 в поле Подставлять значения по строкам в:и D4 в поле Подставлять значения по столбцам в:.

6.ОК.

• Диспетчер сценариев. Средства Excel позволяют создавать и сохранять в виде сценариев наборы входных значений, приводящих к различным результатам. Эти множества входных значений, называемые изменяемыми значениями, сохраняются под присвоенными именами. Для каждого сценария можно определить до 32 изменяемых ячеек. Сценарии используются для исследования модели «что-если» с неопределенными параметрами. С помощью диспетчера сценариев формируются разные сценарии, выполняется их анализ и сохранение вместе с моделью. Диспетчер сценариев позволяет также отслеживать модификации путем автоматического ведения истории сценария. Создание сценария осуществляется по команде Сервис|Сценарии. Примечание. Перед использованием Диспетчера сценариев следует присвоить имена изменяемым ячейкам и ячейкам с формулами, зависящими от изменяемых ячеек. Перед изменением значений первому сценарию следует присвоить имя. Иначе стартовые данные будут потеряны.

Рассмотрим работу Диспетчера сценариев на примере торгового предприятия. Первый сценарий представлен в таблице.

Ячейки Выручка от покупателя, Затраты на покупателя, Среднее число покупателей, Зарплата, Оборудование, Амортизация, Реклама, Расходные материалы, Коммунальные расходы являются изменяемыми. Им следует присвоить соответствующие имена. Также следует

		За неделю	Загод
1 Выручка от покупателя		10,00
1 Затраты на покупателя		5,00
| Доход от покупателя		5,00
1 Среднее число покупателей
1 Общий доход		2 500	130 000
	Накладные расходы
	Зарплата		1 500
	Оборудование		2 500
	Амортизация
	Реклама
	Расходные материалы		2 000
	Коммунальн. расходы		10 000
	Накл. расх. всего		17 100
	Чистый доход		112 900

присвоить имена ячейкам Доход от покупателя, Общий доход (за неделю и год), Накл, расх. всего, Чистый доход, содержащим формулы. Далее действуем по алгоритму:

1.Копировать таблицу первого сценария на р/л Сценарий р/к UchebnFile.