Анализ что если в excel

Анализ что если в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Анализ что если в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

Вычисление множественных результатов с помощью таблицы данных

Таблица данных — это диапазон ячеек, в котором можно изменять значения в некоторых ячейках и вы можете найти разные ответы на вопрос. Хороший пример таблицы данных с использованием функции PMT с различными суммами займа и процентными ставками для расчета доступной суммы по ипотеке на домашний. Экспериментировать с различными значениями, чтобы увидеть соответствующий вариант результатов, — это распространенная задача при анализе данных.

Общие сведения

В Microsoft Excel таблицы данных являются частью набора команд, которые называются What-If анализа. При построении и анализе таблиц данных проводится анализ «что если».

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

Примечание: Вы можете выполнять более быстрые вычисления с таблицами данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в Excel What-If данных: более быстрые вычисления с помощью VBA.

Типы анализа «что если»

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

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

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

Подробнее об этом читайте в статье Введение в What-If анализа.

Общие сведения о таблицах данных

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

Таблицы данных с одной переменной

Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, таблицу данных с одной переменной можно использовать для того, чтобы узнать, как разные процентные ставки влияют на ежемесячный платеж по ипотеке с помощью функции PMT. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.

На рисунке ниже ячейка D2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая ссылается на ячейку ввода B3.

Таблицы данных с двумя переменными

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

На рисунке ниже ячейка C2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая использует две ячейки ввода: B3 и B4.

Вычисления таблицы данных

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

Создание таблицы данных с одной переменной

Таблица данных с одной переменной содержит входные значения в одном столбце (с ориентацией по столбцам) или в строке (по строкам). Любая формула в таблице данных с одной переменной должна ссылаться только на ячейка ввода.

1. Введите список значений, которые нужно подставить, в ячейку ввода : вниз на один столбец или в одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.
2. Выполните одно из указанных ниже действий.
3. Если таблица данных ориентирована на столбец (значения переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбец, а формула содержится в ячейке D2.

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

Добавление формулы в таблицу данных с одной переменной

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

Выполните эти действия

1. Сделайте это.
   • Если таблица данных ориентирована на столбец, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
   • Если таблица данных ориентирована на строки, введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.
2. Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
3. На вкладке Данные нажмите кнопку Анализ> данных «что если» (в группе «Средства данных» или «Прогноз» Excel 2016 ).
4. Сделайте следующее:
   • Если таблица данных ориентирована на столбец, введите ссылку на ячейку ввода в поле Ячейка ввода столбца.
   • Если таблица данных ориентирована на строку, введите ссылку на ячейку ввода в поле Ячейка ввода строки.

Создание таблицы данных с двумя переменными

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

1. В ячейку на этом сайте введите формулу, которая ссылается на две ячейки ввода. В следующем примере, в котором начальные значения формулы ввели в ячейки B3, B4 и B5, введите формулу =PMT(B3/12;B4;-B5) в ячейку C2.
2. Введите один список входных значений в том же столбце под формулой. В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.
3. Введите второй список в той же строке, что и формула, справа от нее. Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
4. Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5). В данном примере выделяется диапазон C2:E5.
5. На вкладке Данные в группе Средства данных или Прогноз (в Excel 2016 ) нажмите кнопку Анализ >«что если» (в группе «Средства данных» или «Прогноз» Excel 2016 ).
6. В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке.
   Введите ячейку B4 в поле Ячейка ввода строки.
7. В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце.
   Введите B3 в поле Ячейка ввода столбца.
8. Нажмите кнопку ОК.

Пример таблицы данных с двумя переменными

Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ниже ячейка C2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая использует две ячейки ввода: B3 и B4.

Ускорение вычислений на сайте, который содержит таблицы данных

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

Чтобы повысить производительность вычислений, выполните указанные здесь действия.

1. Щелкните Файл >параметры >Формулы.
2. В разделе Параметры вычислений в разделе Вычислениенажмите кнопку Автоматически, кроме таблиц данных.

Совет: При желании на вкладке Формулы щелкните стрелку на кнопке Параметры вычислений ивыберите вариант Автоматически, кроме таблиц данных (в группе Вычисления).

Дальнейшие действия

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

Подбор параметров

Если вы знаете, какой результат следует ожидать от формулы, но точно не знаете, какое входные значения должна получить формула, используйте функцию Goal-Seek формулы. Дополнительные сведения см. в статье Использование средств поиска целью для поиска нужного результата путем изменения входного значения.

Excel Решатель

С помощью надстройки Excel «Поиск решения» можно найти оптимальное значение для набора входных переменных. Над решением работает группа ячеек (называемых переменными решения или просто ячейками переменных), которые используются при вычислении формул в ячейках целей и ограничений. Надстройка «Поиск решения» изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке. Подробнее об этом читайте в статье Определение и решение проблемы с помощью «Решение».

Подключив разные числа к ячейке, вы можете быстро получить ответы на различные вопросы. Хороший пример — использование функции PMT с различными процентными ставками и периодами займа (в месяцах), чтобы выяснить, какая часть кредита вы можете позволить себе дом или автомобиль. Числа введите в диапазон ячеек, который называется таблицей данных.

Таблица данных — это диапазон ячеек B2:D8. Значение В4, сумму займа и ежемесячные платежи в столбце D можно изменить автоматически. Используя процентную ставку 3,75 %, D2 возвращает ежемесячный платеж в размере 1042,01 долларов США по формуле =PMT(C2/12;$B$3,$B$4).

В зависимости от количества переменных и формул, которые нужно проверить, можно использовать одну или две переменные.

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

В этой книге ячейка D2 содержит формулу платежа = PMT(C2/12;$B$3;$B$4). Ячейка B3 — это ячейка переменной, в которой можно подключить другую продолжительность терминов (количество периодов ежемесячных платежей). В ячейке D2 функция ПЛТ подсоеднит процентную ставку 3,75%/12, 360 месяцев и кредит на сумму 225 000 долларов США и вычислит ежемесячный платеж в размере 1 042,01 долларов США.

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

В этой книге ячейка C3 содержит формулу платежа =PMT($B$3/12;$B$2;B4), которая использует две ячейки переменных: B2 и B3. В ячейке C2 функция ПЛТ подключается к процентной ставке 3,875%/12, 360 месяцев и займу в размере 225 000 долларов США и вычисляет сумму ежемесячного платежа в размере 1 058,03 долларов США.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.