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

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

Функция подбора параметра

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

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

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

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

Расчет суммы займа

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

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

ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

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

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

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

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

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

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

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

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

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

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

Подбор нескольких параметров для поиска оптимального результата

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

Проверьте, доступна ли она для использования: вкладка «Данные», панель инструментов «Анализ». Если в программе нет такой панели или на ней отсутствует нужная команда, активируйте ее. Зайдите в параметры Excel (кнопка Microsoft Office в Excel 2007, меню «Файл» в версиях 2010 и выше) и найдите пункт «Надстройки». Перейдите в управление надстройками и установите флажок на элементе «Поиск решения». Теперь функция активирована.

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

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

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

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

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

2) Суммарные поставки товаров в торговые точки удовлетворяли требованиям.

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

4) Количество единиц продукции должно быть целым и неотрицательным.

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

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

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

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

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

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