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

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

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

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

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

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

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

Видео по теме

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

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

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

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

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

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

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

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

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

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

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

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