Поиск решения в Excel

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

Поиск решения путем подбора данных находит наиболее эффективное значение. В нашем случае, максимальную прибыль. Я взял небольшую таблицу:Поиск решения в ExcelЗдесь, как видите, синим обозначена целевая ячейка, та, которую нужно максимизировать, изменяя расходы на рекламу (зеленые ячейки). Хитрость в том, что прибыль зависит от объема продаж (в штуках), а от него, в свою очередь зависят и расходы и доходы, которые формируют прибыль. Т.е., просто увеличив или уменьшив расходы на рекламу, вы не получите лучшего результата. В этом и состоит ценность Поиска решения - он делает перебор всех возможных значений по своему алгоритму и получает наилучший результат. Кто проходил обучение по моему самоучителю, уже в курсе, как это делается и как применяется.

Еще есть ограничение бюджет рекламных расходов в 40 тыс. долл. за 4 месяца.

Итак, приступим к технической части.

  1. Если вы никогда не пользовались этой надстройкой, придется ее сначала установить. Дело в том, что по умолчанию Поиск решения не ставится. Заходим Офис/Параметры Excel/Надстройки/Кнопка "Перейти"Мы уже заходили сюда, когда делали сумму прописью.Сумма прописью в Excel
    Сумма прописью в Excel
  2. Теперь, у вас на вкладке Данные появилась команда "Поиск решения". Нажимаем и видим такое окошко:Поиск решения в Excel
  3. Целевая ячейка - это там, которую мы хотим максимизировать, это результат. У нас это B15. Т.е. я хочу увидеть, какими должны быть расходы на рекламу, чтобы в январе у меня была максимальная прибыль. Ставим выбор "максимальному значению".
  4. Изменяя ячейки - ставим диапазон ячеек, от которых зависит итог. У меня это все расходы на рекламу, т.е. диапазон B11 - E11.
  5. Ограничения - ну без них никак. Excel мыслит больше математически, поэтому нам надо:
  • Поставить условия положительности изменяемых ячеек. B11:E11 > 0
  • Ограничить рекламный бюджет за 4 месяца. F11=40000
  1. Нажимаем на кнопку "Выполнить". Если что-то пошло не так, а это бывает, обнулите диапазон изменяемых ячеек, может помочь.

В итоге мы получим нужный результатПоиск решения в Excel

Если не получим, значит, надо пересмотреть условия, возможно, что-то некорректно проставлено или задача не имеет решения.

Видео по теме (5 минут):

Скачать пример.

Эксель Практик
«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

 

 

 

 

 

 

 

 

Поиск решения в Excel: 6 комментариев

  1. Александр

    Здравствуйте Сарытай , искал инфо , как установить надстройку » поиск решения » и как ей пользоваться , а у Вас больше понравилось и как включить и как польоваться и одновременно нашёл у Вас , на сайте ёмкий урок о том как создать сводную таблицу , пока форум обсуждающий эту тему найдёшь , пока понятный для себя ответ найдёшь , а Вас быстро нашёл ответ , Спасибо , всего Вам Сарытай самого доброго , крепкого здоровья , счастья , удачи в Новом 2015 году ! Александр .

  2. admin Автор записи

    О, спасибо вам за комментарий :) Приятно, когда твои труды оценены по достоинству! С Новым годом Вас!

  3. Konstantin

    Как понять фразу: «расходы на рекламу занижают увеличивают общие расходы»? Получается, что они (расходы) одновременно и «занижают» и «увеличивают», вероятно, фразу нужно откорректировать.

  4. admin Автор записи

    Блин блинский :) спасибо, Константин :)

  5. Кирилл

    Добрый день!

    У меня расчет поиска решений длиться очень долго, при этом визуально я не вижу процесса подстановки и перебора значений (просто чего-то жду), вы знаете как включить визуализацию?

  6. admin Автор записи

    Надстройка так и работает — поставили задачу и ждёте. Это же не инженерно-математическая прога. Если долго работает, я обычно задавал количество итераций, чтобы контролировать процесс. Но это так, для того, чтобы убедиться, что машина не зависла и увидеть хоть какие-то промежуточные результаты. Или упростите задачу.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>