Интересное и полезное о Microsoft Excel с минимумом теории
Поиск решения в Excel
Поиск решения - это надстройка Excel, позволяющая делать задачи на оптимизацию. Как это работает: у вас есть данные, которые связаны между собой формулами. При этом на какой-то результирующий итог влияют как положительным, так и отрицательным образом. Например, прибыль зависит от расходов на рекламу как положительно (ведь чем больше рекламы, тем больше объем продаж), так и отрицательно (расходы на рекламу увеличивают общие расходы и уменьшают прибыль). В конце статьи будет ссылка на файл, где вы сможете сами посмотреть наглядно таблицу и прорешать параллельно со мной.
Поиск решения путем подбора данных находит наиболее эффективное значение. В нашем случае, максимальную прибыль. Я взял небольшую таблицу:Здесь, как видите, синим обозначена целевая ячейка, та, которую нужно максимизировать, изменяя расходы на рекламу (зеленые ячейки). Хитрость в том, что прибыль зависит от объема продаж (в штуках), а от него, в свою очередь зависят и расходы и доходы, которые формируют прибыль. Т.е., просто увеличив или уменьшив расходы на рекламу, вы не получите лучшего результата. В этом и состоит ценность Поиска решения - он делает перебор всех возможных значений по своему алгоритму и получает наилучший результат. Кто проходил обучение по моему самоучителю, уже в курсе, как это делается и как применяется.
Еще есть ограничение бюджет рекламных расходов в 40 тыс. долл. за 4 месяца.
Итак, приступим к технической части.
Если вы никогда не пользовались этой надстройкой, придется ее сначала установить. Дело в том, что по умолчанию Поиск решения не ставится. Заходим Офис/Параметры Excel/Надстройки/Кнопка "Перейти". Мы уже заходили сюда, когда делали сумму прописью.
Теперь, у вас на вкладке Данные появилась команда "Поиск решения". Нажимаем и видим такое окошко:
Целевая ячейка - это там, которую мы хотим максимизировать, это результат. У нас это B15. Т.е. я хочу увидеть, какими должны быть расходы на рекламу, чтобы в январе у меня была максимальная прибыль. Ставим выбор "максимальному значению".
Изменяя ячейки - ставим диапазон ячеек, от которых зависит итог. У меня это все расходы на рекламу, т.е. диапазон B11 - E11.
Ограничения - ну без них никак. Excel мыслит больше математически, поэтому нам надо:
Поставить условия положительности изменяемых ячеек. B11:E11 > 0
Ограничить рекламный бюджет за 4 месяца. F11=40000
Нажимаем на кнопку "Выполнить". Если что-то пошло не так, а это бывает, обнулите диапазон изменяемых ячеек, может помочь.
В итоге мы получим нужный результат
Если не получим, значит, надо пересмотреть условия, возможно, что-то некорректно проставлено или задача не имеет решения.
P.S.Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.
Поиск решения в Excel: 6 комментариев
Александр
Здравствуйте Сарытай , искал инфо , как установить надстройку » поиск решения » и как ей пользоваться , а у Вас больше понравилось и как включить и как польоваться и одновременно нашёл у Вас , на сайте ёмкий урок о том как создать сводную таблицу , пока форум обсуждающий эту тему найдёшь , пока понятный для себя ответ найдёшь , а Вас быстро нашёл ответ , Спасибо , всего Вам Сарытай самого доброго , крепкого здоровья , счастья , удачи в Новом 2015 году ! Александр .
adminАвтор записи
О, спасибо вам за комментарий Приятно, когда твои труды оценены по достоинству! С Новым годом Вас!
Как понять фразу: «расходы на рекламу занижают увеличивают общие расходы»? Получается, что они (расходы) одновременно и «занижают» и «увеличивают», вероятно, фразу нужно откорректировать.
adminАвтор записи
Блин блинский спасибо, Константин
Кирилл
Добрый день!
У меня расчет поиска решений длиться очень долго, при этом визуально я не вижу процесса подстановки и перебора значений (просто чего-то жду), вы знаете как включить визуализацию?
adminАвтор записи
Надстройка так и работает — поставили задачу и ждёте. Это же не инженерно-математическая прога. Если долго работает, я обычно задавал количество итераций, чтобы контролировать процесс. Но это так, для того, чтобы убедиться, что машина не зависла и увидеть хоть какие-то промежуточные результаты. Или упростите задачу.
Здравствуйте Сарытай , искал инфо , как установить надстройку » поиск решения » и как ей пользоваться , а у Вас больше понравилось и как включить и как польоваться и одновременно нашёл у Вас , на сайте ёмкий урок о том как создать сводную таблицу , пока форум обсуждающий эту тему найдёшь , пока понятный для себя ответ найдёшь , а Вас быстро нашёл ответ , Спасибо , всего Вам Сарытай самого доброго , крепкого здоровья , счастья , удачи в Новом 2015 году ! Александр .
О, спасибо вам за комментарий Приятно, когда твои труды оценены по достоинству! С Новым годом Вас!
Как понять фразу: «расходы на рекламу занижают увеличивают общие расходы»? Получается, что они (расходы) одновременно и «занижают» и «увеличивают», вероятно, фразу нужно откорректировать.
Блин блинский спасибо, Константин
Добрый день!
У меня расчет поиска решений длиться очень долго, при этом визуально я не вижу процесса подстановки и перебора значений (просто чего-то жду), вы знаете как включить визуализацию?
Надстройка так и работает — поставили задачу и ждёте. Это же не инженерно-математическая прога. Если долго работает, я обычно задавал количество итераций, чтобы контролировать процесс. Но это так, для того, чтобы убедиться, что машина не зависла и увидеть хоть какие-то промежуточные результаты. Или упростите задачу.