Функция "подбор параметра" в "Excel". Анализ "что если"

Опубликованно 06.10.2018 02:20

Функции программы Excel, что они позволяют решать задачи, которые не могут быть выполнены вручную или слишком сложно. Это трудоемкие задачи могут Brute с большим количеством опций,-Force-расчеты или большие цифры. Знание функций Excel, которые не решить их трудно. Функция Подбор Параметра

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

Excel предлагает метод решения подобных проблем, поиск трека на целевое значение. Вызов функции находится на вкладке «данные» панели инструментов «Работа с данными». В начиная с версии MS Excel 2007 - «анализ "что если"», пункт меню «выбор параметров».

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

Как видно на картинке, позволяет вводить формулы для изменения и замены только один аргумент. Это не всегда является пользователь. При необходимости выбрать несколько значений, для получения нужного результата, выбор из вариантов «Excel» подходит. В этой ситуации специальный add-on распространяется, на котором мы находимся ниже. Расчет суммы кредита

Одна из самых популярных задач, которые помогает решить этот модуль, - расчет возможной суммы кредита или банковского кредита, исходя из ежемесячных платежей, срок и процентная ставка. Предположим, что процентная ставка по кредиту составляет 10%, мы хотим сэкономить деньги на 1 год и заплатить 7 тысяч рублей в месяц.

В «Excel» 2007 есть подходящая функция для расчета ежемесячных платежей по кредиту с известной проценты и срок. Это называется PMT. Синтаксис команды:

ПЛТ(ставка; кпер; ПС; [БС]; [тип]), где: Процентная ставка – проценты по кредиту. Кпер – количество платежей (для годового кредита в случае ежемесячного платежа составляет 12 раз). PS – первоначальная сумма. БС – будущая стоимость (если вы намерены платить не всю сумму, а только часть, здесь будет указано, какие долги должны остаться). Это опционально, по умолчанию это значение на 0. Типа – если вы делаете платеж в начале или в конце месяца. Этот параметр не указывать, если он не является полным, принимается равным 0, что означает, что оплата в конце месяца.

Обратите внимание, что атрибут «Скорость» будет годовой процент и ежемесячная, так что нам известна процентная ставка будет через количество платежей в год – 12.

Необходимых данных производится на листе «Excel» 2007. В качестве первоначальной суммы 000 рублей до 1 00 условная и применить поиск реальной суммы. Вызовите диалоговое окно подбор параметра. Отправной точкой для поиска сумма ежемесячного платежа. Формула ПЛТ дает отрицательные данные, поэтому мы ставим число со знаком «минус»: - 7 000 рублей в поле «значение». Эту сумму мы должны в клетке с наложенным платежом, изменяя данные в поля кредитного.

Пишет все в окно и начать подбор параметра «Excel». В результате функция 79 вычисляет, какой кредит можно себе позволить - 621,56 руб.

Определение процентной ставки

Теперь рассмотрим обратную задачу. Банк выдает кредит в размере 100 тысяч рублей на 2 года и хочет получить доход от 10 тысяч рублей. Какую минимальную процентную ставку, чтобы такой доход нужно установить?

Чтобы добавить уже существующую информацию на листе строки »прибыль». Она рассчитывается по формуле:

Прибыль = |Оплаты|*Срок-Сумма

Установите срок 24 месяца. Обратите внимание на поле «Bet». Числовое значение должно быть выражено в процентах. Для этого выберите числовой формат «процент» в Excel: вкладка «главная» панель инструментов «решка» - кнопка с изображением процентов.

Мы вызываем функцию подбора и представляют свои аргументы. Ожидаемый результат записывается в поле «прибыль» путем изменения значения в ячейке «Bet» и составляет 10 000 евро. После запуска программа покажет желаемый процент в размере 9,32354423334073 %. Выбор нескольких параметров для поиска оптимального результата

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

Убедитесь, что для использования: вкладки «панель данных» символ «анализ». Если программы нет такой панели или отсутствует нужная команда, включите ее. Перейдите в параметры Excel (кнопка Office в Excel 2007 в меню «файл» в версии 2010 и выше) и открыть «Надстройки». Щелкните управление в надстройки и установите флажок на пункт «поиск решения». Теперь она включена.

Транспортная Задача

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

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

Мы приносим значения поставок, соблюдения условий:

1) были Полные затраты минимальны.

2) сумма поставок товаров в местах продажи удовлетворяли требованиям.

3) суммарный вывоз продукции со складов не превышает имеющиеся запасы.

4) количество единиц целое и не отрицательное целое число должно.

Результат поиска решения.

Другие способы анализа данных

Помимо перечисленных выше вариантов, существуют и другие методы анализа данных. Вы находитесь в пункте меню «анализ "что если"». Эти «диспетчер сценариев» и «Таблица данных».

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

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

Выше мы подробно остановились на функцию Excel «подбор параметра» и практические примеры их использования. Используя эту возможность, можно легко решить задачи. Автор: Татьяна Казмирчук 24. Сентябрь 2018

Категория: обо всём