Анализ данных «ЧТО-ЕСЛИ» в Excel
С помощью средств анализа «что если» в Microsoft Excel можно экспериментировать с различными наборами значений в одной или нескольких формулах для изучения всех возможных результатов.
Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%.
Итак, проиллюстрируем проведение анализа данных «что-если» на примере работы функции ПЛТ, которая вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.
Вызов функции имеет вид: ПЛТ (ставка;кпер;пс;бс;тип)
Ставка — процентная ставка по ссуде.
Кпер — общее число выплат по ссуде.
Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, предполагается, что он равен 0 (например, значение «бс» для займа равно 0).
Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.
Рассмотрим пример использования функции ПЛТ в Exceel.
Итак, требуется определить ежемесячные выплаты по займу в 20 000 руб., взятому на 16 месяцев под 11% годовых.
Для решения задачи выделяем ячейку на рабочем листе Excel (в нашел случаи ячейка А1) и в строку формул вводим следующее выражение: =ПЛТ(11%/12; 16; 20000) (Рис.1.1)
Рис. 1.1 — Ввод формулы Excel.
Нажав на клавишу Enter , мы получаем величину ежемесячных выплат по ссуде, которая составит −1350 руб. Рис.1.2
Рис. 1.2 — Величина ежемесячной выплаты по ссуде.
При ином значении банковской учетной ставки, следует сделать исправления в ранее введенной функции в Excel.
Другой подход к вычислению функции ПЛТ методом «что если» в Excel проиллюстрирован на Рис. 1.3. Функция ПЛТ определена в ячейке D7, а значения аргументов записаны в ячейках D2, D3 и D4. Для получения значения функции при новых значениях аргумента достаточно внести соответствующие изменения в исходные данные. В этом случаи в строке формул на рис.1.3 мы вводим не конкретное значение аргумента, а ссылку ни соответствующую ячейку.
Рис. 1.3 — Пример расчета Excel, в котором исходные данные в отдельные ячейки
При изменении любых значений на рис.3 результаты расчета автоматически обновляются в разделе Результат расчета.
Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных «Что если» в Exceel.
Подбор параметра в Excel
При вычислении различных функций возникает вопрос: «Каким должно быть значение определенного аргумента функции, чтобы функция возвратила заданный результат?».
Для решения такой задач в состав Excel включен специальный инструмент — Подбор параметра. С помощью этого инструмента определяется значение в одной ячейке исходных данных, которое требуется для получения требуемого значения в ячейке результата.
Из расчетной части рис.1.3 видно, что при заданных исходных данных требуется ежемесячно выплачивать по 1350 руб. для погашения займа. Предположим, что по каким-то причинам кредитор имеется возможность выплачивать не более 1200 руб. в месяц. Спрашивается, какую максимальную величину ссуды может он запросить, если все прочие условия сохраняются?
Для решения этой задачи выберем команду Данные > Анализ «что если» > Подбор параметра (рис. 2.1). В верхнем поле этого окна указывается ссылка на ячейку D7, в которой устанавливается желаемый результат (в нашем случае — это −1200 руб). В нижнее поле диалогового окна вставляется ссылка на ячейку, в которой хранится значение искомого параметра, т.е. D4.
Рис. 2.1 — Диалоговое окно Подбор параметра в Excel
При нажатии клавиши ОК мы получим максимальную сумму займа, при условии выплаты ежемесячно 1 200 руб. Рис.2.2
Рис. 2.2 — Максимальная величина займа 17 783 руб.
Вывод: Выполнение анализа «что-если» в Excel обеспечивает достаточно оперативную оценку влияния того или иного аргумента на результат вычисления.
Проведение анализа на основе таблицы подстановки в Excel
Таблицы подстановки для одной переменной.
В Excel предусмотрено средство, позволяющее без особых усилий строить таблицу подстановки для одной и двух переменных.
Рассмотрим способ построения так называемой таблицы подстановки для одной переменной, используя приведенный выше пример вычисления функции ПЛТ.
Для построения таблицы подстановки необходимо подготовить исходные данные рис.3.1
Рис. 3.1 — Подготовка исходных данных для построения таблицы подстановки Excel
В ячейке G3 этой таблицы определена точно такая же формула, как и в ячейке D7. Первый столбец таблицы подстановки заполнен значениями аргумента функции ПЛТ, в зависимости от которого требуется проанализировать поведение финансовой функции (в нашем случае от 11 до 15%).
Чтобы получить соответствующие значения функции во втором столбце, нужно выделить диапазон ячеек — F3:G7, и после этого выполнить команду меню Данные > Анализ «что если» > Таблица данных… . В результате появляется диалоговое окно этой команды (рис. 3.2).
Это окно служит для задания абсолютного адреса рабочей ячейки, на которую ссылается расчетная функция (ячейка D2). В случае вертикальной организации таблицы подстановки ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по строкам.
Рис. 3.2. — Диалоговое окно Таблица подстановки в Excel
После щелчка на кнопке ОК столбец результатов таблицы подстановки будет заполнен (рис. 3.3).
Рис.3.3. Таблица подстановки для одной переменной в Excel
Таблица подстановки для двух переменных в Excel.
Более богатыми возможностями для анализа обладают таблицы подстановки для двух переменных, позволяющие изучать поведение функции при изменении одновременно двух ее аргументов.
Поставим задачу проследить характер изменения функции ПЛТ в зависимости от изменения годовой процентной ставки и срока погашения ссуды.
Для начала, подготовить исходные данные на рабочем листе, как это показано на рис. 3.4
В ячейке F2 таблицы подстановки определена точно такая же формула, как и в ячейке D7 в Excel. Первый столбец таблицы подстановки заполнен значениями годовой процентной ставки. Первая строка таблицы заполнена значениями срока вклада. Требуется в зависимости от изменения этих двух аргументов проанализировать поведение финансовой функции.
Рис. 3.4 — Подготовка исходных данных для построения таблицы подстановки Excel
Чтобы получить значения функции в таблице, выделяем диапазон ячеек F2:J7, который содержит исходные значения процентных ставок, исходные значения срока погашения ссуды и расчетную функцию. После этого нужно выполнить команду меню Данные > Анализ «что если» > Таблица подстановки. В результате появится диалоговое окно (рис. 3.5).
Рис. 3.5 Диалоговое окно Excel Таблица подстановки
Рис. 3.6 Расчетные значения таблицы подстановки Excelдля двух переменных
Вывод: С помощью таблицы подстановки выявляются характерные тенденции поведения функции в зависимости от изменения определенных параметров или аргументов.
Проведение графического анализа в Excel.
На рис. 3.7 и 3.8 представлены диаграммы, построенные на базе таблиц подстановки для одной-двух переменных соответственно. Так, для построения диаграммы для двух переменных выделим диапазон ячеек F3:J7 и выберем тип диаграммы «точечная». Затем следует отредактировать полученную диаграмму.
Ежемесячные выплаты по ссуде
Рис. 3.7 Диаграмма excel, построенная на основе диапазона ячеек F3:G7 таблицы подстановки для одной переменной (см. рис. 3.3)
Ежемесячные выплаты по ссуде
Рис. 3.8 — Диаграмма Excel, построенная на базе диапазона ячеек F3:J7 таблицы подстановки для двух переменных (см. рис. 3.6)
Поиск решения в Exceel
Существует достаточно широкий класс относительно сложных задач поиска оптимального решения, которые описываются системами уравнений с несколькими неизвестными и набором ограничений на решения. Для решения подобных задач весьма эффективным может оказаться средство Excel Поиск решения.
Средство Поиск решения — это надстройка Excel. Для ее подключения следует выполнить команду меню Сервис > Надстройки. В появившемся диалоговом окне Надстройки нужно установить флажок опции Поиск решения.
Характерные особенности задач, для решения которых предназначено данное средство, заключаются в следующем:
имеется единственная цель, например максимизация прибыли, минимизация расходов и т.п.;
имеются ограничения, выраженные в виде неравенств;
имеются переменные, значения которых влияют на ограничения и оптимизируемую величину.
Правильная формулировка ограничений — самая ответственная часть описания модели для поиска решения. Следует особенно внимательно следить за тем, чтобы задавать все объективно существующие ограничения. Неполнота описания ограничений приводит к неправильному решению.
Следует различать линейные и нелинейные модели, поскольку для линейных моделей существуют быстрые и надежные методы поиска решения.
Чтобы исключить использование общих более медленных методов для решения линейных задач, следует установить параметр Линейная модель в окне Параметры поиска решения.
Решение задачи оптимизации.
Для пояснения принципа работы средства Поиск решения рассмотрим пример, используя данные таблицы на рис. 4.1.
Рис. 4.1 — Таблица Excel для определения количества товаров, приносящих максимальную прибыль
Требуется определить, в каких количествах следует производить товары каждого вида, чтобы получить максимальную прибыль.
Ячейка (Е7), в которую помещается ответ, называется целевой. Целевая ячейка содержит формулу, результат которой зависит от значений, содержащихся в других ячейках, называемых изменяемыми.
Ограничения — это спецификации, которые применяются к целевой и изменяемым ячейкам для задания диапазона возможных значений.
Предположим, что имеются следующие ограничения, которые необходимо учитывать при составлении плана выпуска продукции:
общее число производимых товаров за отчетный период должно составлять ровно 1000 шт.;
товар С пользуется наименьшим спросом, поэтому, как показал опыт, удается реализовать товар этого вида не более 140 шт.;
на товары вида A, B, D имеются заказы соответственно на 50, 100 и 200 шт., которые необходимо выполнить.
Для реализации процедуры поиска решения необходимо выполнить следующие действия.
Ввести исходные данные, как это показано на рис. 4.1.
- Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
- Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
- Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
- Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
- Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.
Результат поиска решения представлен на рис. 4.5.
Рис. 4.2 — Диалоговое окно Поиск решений в Excel
Рис 4.3 — Диалоговое отношение Добавление ограничений Excel
Рис. 4.4. — Введение ограничения Excel
После того как найдем оптимальное решение, мы можем выбрать одну из следующих возможностей:
1) сохранить найденное решение;
2) восстановить исходные значения в изменяемых ячейках;
3) создать отчеты о процедуре поиска решения;
4) щелкнуть на кнопке Сохранить сценарий. Сохраненный сценарий может быть использован в средстве Диспетчер сценариев.
Большинство задач, решаемых с помощью электронной таблицы Excel, предполагают нахождение искомого результата по известным исходным данным. Но в Excel есть инструменты, позволяющие решить и обратную задачу, подобрать исходные данные для получения желаемого результата. Одним из таких инструментов является Поиск решения, который особенно удобен для решения так называемых «задач оптимизации».
