Подбор параметра в Excel: рабочие примеры

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

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

А если у вас вдруг остались какие-то вопросы или идеи – добро пожаловать в комментарии. Пишем, не стесняемся помогать другим людям.

Где находится?

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

Вкладка «Данные» – Кнопка «Анализ что-если» – «Подбор параметра»

В браузерном Excel этой кнопки может не быть – открывайте файл в настольном Excel.

Видео по теме

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

YouTube

Пример работы с Подбором параметра

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

1. Знакомимся с нашей таблицей:

2. Посмотрели на картинку выше? Здесь все просто – первый столбец никак не участвует в расчетах, зарплата заполняется вручную, а вот премию будем считать простым коэффициентом, например ПРЕМИЯ = ЗАРПЛАТА * 0,3. Сразу же отражаю это в таблице:

3. Если все оставить «как есть», то ячейка зарплаты останется пустой, а в поле «Премия» появится значение 0. То есть, если мы заполним столбец зарплаты, все посчитается как нужно, но я предлагаю поставить задачу по-другому:

Какую зарплату нам нужно получать, чтобы премия составила 30 000 рублей?

4. Такое можно посчитать простой формулой, но мы воспользуемся подбором параметра. Устанавливаем курсор на ячейку с нашей формулой и переходим в «Подбор параметра» (смотрим первый раздел этой статьи). Должно появиться вот такое окошко:

5. Что сюда нужно вводить:
   • Установить в ячейке – C2 – Ячейка нашей формулы (ведь сюда мы хотим установить значение в 30 000).
   • Значение – Как раз и вводим значение 30000.
   • Изменяя значение ячейки – B2 – То есть изменять будем значение зарплаты.

C2 должна зависеть от B2, иначе Excelу нечего подбирать. Про адреса: ссылки в Excel.

6. Нажимаем кнопку «ОК», Excel делает перебор значений и выдает результат:

То есть, чтобы получить премию в 30 000, нужно иметь зарплату в 100 000. По нашей логике все верно, правда и намного дольше, чем просто посчитать делением.

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