Флажок Создавать связи — сбросить. Вполе Использовать в качестве имен— установить флажок Значения левого столбца
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.