Анализ данных «ЧТО-ЕСЛИ» в 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).
Рис. 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 есть инструменты, позволяющие решить и обратную задачу, подобрать исходные данные для получения желаемого результата. Одним из таких инструментов является Поиск решения, который особенно удобен для решения так называемых "задач оптимизации".