Где находится «Поиск решения» в Excel и как им пользоваться

Надстройка «Поиск решений» — специфическая возможность Excel 2007, 2010, 2013, 2016, которая предназначена для работы с формулой при наличии определённых условий. Описать её логику можно следуя принципу «что если?». То есть, просчитать изменение конечной ячейки при условии изменения других. Хотя и звучит это сложно, но описать данный принцип удобнее на конкретном примере: как будет изменяться остаток средств в конце месяца, если изменить разные статьи расходов.

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

Как включить функцию “Поиск решения”

Несмотря на свою эффективность, функция “Поиск решения” не находится в первых рядах панели инструментов или контекстного меню. Многие пользователи, работающие в Excel годами, даже не подозревают о ее существовании. Дело в том, что по умолчанию она вообще отключена и для ее добавления на ленту нужно проделать следующие шаги:

1. Открываем меню “Файл”, кликнув по соответствующему названию.
2. Кликаем по разделу “Параметры”, который находится внизу вертикального перечня с левой стороны.
3. Далее щелкаем по подразделу “Надстройки”. Здесь отображаются все надстройки программы, а внизу будет надпись “Управление”. Справа от нее представлено выпадающее меню, в котором должны быть выбраны “Надстройки Excel”, обычно уже установленные по умолчанию. Нажимаем кнопку “Перейти”.
4. На экране появится новое вспомогательное окно “Надстройки”. Устанавливаем флажок напротив опции “Поиск решения” и нажимаем ОК.
5. Все готово. Требуемая функция появится на ленте в правой части вкладки “Данные”.

Пример вычисления с помощью надстройки «Поиск решения»

В приведенном ниже примере количество проданных единиц в каждом квартале зависит от уровня рекламы, что косвенно определяет объем продаж, связанные издержки и прибыль. Надстройка “Поиск решения” может изменять ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5) до ограничения в 20 000 рублей (ячейка F5), пока общая прибыль (целевая ячейка F7) не достигнет максимального значения. Значения в ячейках переменных используются для вычисления прибыли за каждый квартал, поэтому они связаны с формулой в целевой ячейке F7, =СУММ (Q1 Прибыль:Q2 Прибыль).

1. Ячейки переменных

2. Ячейка с ограничениями

3. Целевая ячейка

После выполнения процедуры получены следующие значения.

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

После постановки задачи нажмите кнопку Параметры в диалоговом окне Параметры поиска решения.

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

В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение.

В диалоговом окне Показать предварительное решение выполните одно из указанных ниже действий.

Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.

Чтобы продолжить процесс поиска решения и просмотреть следующий вариант решения, нажмите кнопку Продолжить.

10 приемов, которые повысят вашу ценность перед начальником или клиентом

Универсальные советы, которые помогут сохранить доходы в мире, где вас и вашу работу могут заменить роботом.

Маркетинг помешан на привлечении новых клиентов. Больше и больше пользователей, регистраций, установок. Им достанутся самые невероятные скидки, все призы и подарки. А старым клиентам — незаметное подключение платных услуг и регулярно дорожающие тарифные планы с постепенным урезанием функционала.

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

• Они приводят в порядок резюме.
• Отыскивают лучшую одежду. Некоторые даже гладят ее.
• Протирают видеокамеру для собеседований.
• Прибираются в комнате, вешают экран на стену.
• Сочатся улыбками, вежливостью, вниманием.

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

Опытный сотрудник нечесан, неопрятен. Бывает пугающе раздраженным или наоборот веселым через край, постоянно шутит, а потом сам смеется. Дейла Карнеги хватила бы кондрашка от методов, которыми люди заводят новые деловые знакомства на пятом, десятом году службы. И все получается, что характерно. Ну как все. Пока не возникнут проблемы, несовместимые с сохранением работы.

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

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

Только будут ли они ждать? Рыночек (сюрприз) конкурентный. К тому же сейчас такие времена, что уже почти не осталось обычных менеджеров, выживают только антикризисные.

LTV само себя не вытянет. Если хотите долгой и красивой любви на возмездной основе, будьте любезны поддерживать огонек хотя бы на таком уровне, чтобы он сам не задохся. Нужно и дальше быть вежливым, профессиональным, помнить как кого зовут, выглядеть презентабельно — причем как в реале, так и онлайн, на уровне профилей и аккаунтов.

Ниже еще 10 рекомендаций, без выполнения которых можно потерять доход даже раньше, чем ваше рабочее место или бизнес автоматизируют ненасытные айтишники. Все советы универсальны. Разница между b2c и b2b вообще слишком преувеличена. В целом все везде примерно одинаковое, просто суммы меняются.

1. Уточняйте задачи

Роберт Кийосаки учил будущих жертв кризиса рынка недвижимости, что прибыль образуется не в момент продажи, а в момент покупки. И это справедливое суждение, в том числе при получении задачи или заказа.

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

Если обсуждаете чего-то, обязательно записывайте. Присылают голосовые? А вы их расшифровывайте в том же мессенджере. Потом еще отдельно напишите резюме встречи или беседы. Без фанатизма: не надо требовать ЭЦП (электронную цифровую подпись – прим. ред.) и нотариуса. Тезисно, чисто для взаимопонимания.

Это так важно, что по одной процедуре принятия задачи можно составить исчерпывающее мнение о специалисте или подрядчике.

2. Соблюдайте сроки

Удивительно, что приходится писать такой пункт. Как иначе-то! Но мы все превосходно знаем, как много тут вариантов. Так вот, а на самом деле их не должно быть. Вообще.

Дело даже не в том, что «время — деньги». Часто платят за возможность не беспокоиться. За уверенность в том, что здесь-то все точно будет хорошо. Без этого условия может оказаться, что результаты сами по себе не очень нужны. Их можно получить иначе, множеством разных способов и главное без вашего участия.

Бывает, что заказчик и сам способен сделать задание. Однако он ищет тех, кто освободит его мозг (а не нагрузит необходимостью контроля и поиска запасных вариантов для подстраховки).

3. Говорите о проблемах сразу

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

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

Обиднее всего случаи, когда ты порушил все свои планы, сидишь дома, ждешь курьера — а он заглянет в другой раз, оказывается. Не будьте таким курьером.

4. Будьте проактивным и креативным

Фонтанировать идеями как на бесконечном брейнсторме не нужно – такое всегда лишнее (даже на брейнстормах). Все ценят адекватные, уместные предложения. Но чтобы научиться их делать, надо практиковаться. Кроме того, без обратной связи бывает сложно понять, что ценится.

Важное правило — все озвучивается один раз. Идея, предложение, правка, альтернатива, другой поставщик, новая технология, любые оценки «поперек» предложенных могут оказаться полезными. Могут и не оказаться.

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

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

5. Слушайте возражения

Главное, что можно услышать от собеседника по существу — это негатив. Он всегда конструктивен, и важно распознать причину недовольства. При определенном внимании с годами нарабатывается машинальная скоринг-система, которая позволяет быстро сортировать наезды и претензии.

В таком случае надо выбрать:

• Некоторые требования не грех и учесть.
• Если речь пошла о смежных темах – это вообще вас не касается.
• Если ясно, что у человека личные проблемы, возможно ему стоит аккуратно предложить помощь.
• Если же вы видите признательные показания латентного мракобеса – с ним пора прощаться.

6. НЛП. Научитесь легко подыгрывать

Подстройка под собеседника — основа комфортного общения (в том числе для вас). Раньше это касалось только личных встреч, теперь и онлайн.

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

Как минимум, следует учитывать стиль общения собеседника и синхронизироваться с ним. Задача в том, чтобы уверенно проходить тест «свой/чужой».

7. NDA — наше все

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

Неважно, подписывали NDA (соглашение о неразглашении – прим. ред.), не подписывали, какие пункты в договоре, что обсуждали на словах. Лучше вести любые дела и отношения так, будто вы каждую ночь обновляете клятву молчания кровью на городском кладбище у склепа самого страшного бандита.

Не бывает ситуаций, когда можно «по-тихому» слить чужие данные, опубликовать скрин переписки, сделать любое публичное движение без явного согласования с другой стороной. Тут важно понимать, что Интернет весь публичный. Что одному поисковому роботу известно, то могут узнать все. Или наименее подходящие для оповещения лица.

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

8. Работайте не за деньги

Мир пропитан лицемерием, и сойти за своего гораздо проще, если абстрагироваться от озвучивания реальных мотивов.

• Не «хотим больше бабла», а «заинтересованы в интересном проекте с вашим брендом».
• Не «повысьте мне зарплату», а «надеюсь расти вместе с командой и расширять наш бизнес».

Работодатели любят, когда сотрудники будто случайно оговариваются, называя чужое юрлицо «нашей компанией». Это действует на них успокаивающе. В точности как заказчиков радует неподдельный (oh really?) интерес подрядчика к бизнес-показателям и долгосрочной выгоде, к которой они не имеют прямого отношения.

9. Будьте франшизой

Сложное развитие игры в откровенность приводит к неожиданно честному, самурайскому формату. Бусидо сотрудника в том, что он готовится к увольнению. Бусидо подрядчика в передаче дел конкуренту. Нужно осознать это, принять и использовать.

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

Но при этом нужно позаботиться о том, чтобы оснований остаться всегда было больше. Самурай не ищет смерти, а готов к ней. Это другое.

10. Всегда продавайте

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

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

И здесь важно, как в том анекдоте, бежать не быстрее медведя, а быстрее других охотников.

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

Как включить функцию «Поиск решения»

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

1. Открываем «Файл», нажимая на соответствующее название.
2. Кликаем на раздел «Параметры».
3. Затем выбираем подраздел «Надстройки». Тут будут отображены все надстройки программы, внизу появится надпись «Управление». С правой стороны от нее будет всплывающее меню, где следует выбрать «Надстройки Excel». Потом нажимаем «Перейти». 1
4. На мониторе высветится дополнительное окно «Надстройки». Устанавливаем флажок возле искомой функции и кликаем «ОК».
5. Нужная функция появится на ленте справа от раздела «Данные».

О моделях

Данная информация будет крайне полезна тем, кто лишь ознакамливается с понятием «оптимизационная модель». До того, как воспользоваться «Поиском решения», рекомендуется исследовать материалы о методах построения моделей:

• рассматриваемая опция даст возможность выявить оптимальный метод, чтобы осуществить выделение средств на вложения, загрузку помещения, поставку товаров либо иные действия, где необходимо отыскать оптимальный вариант решения.
• «Оптимальный метод» в такой ситуации будет означать: увеличение доходов, снижение трат, улучшение качества и др.

Типовые задачи по оптимизации:

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

Важно! Чтобы формализовать поставленную задачу, необходимо создать модель, отражавшую основные параметры предметной области. В Эксель модель является комплексом формул, использующих переменные. Рассматриваемая опция подыскивает такие показатели, чтобы целевая функция была больше (меньше) либо равнялась указанному значению.

2

Подготовительный этап

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

Нашей задачей станет вычисление скидки, на которую умножаются суммы по реализации различной продукции. Эти 2 элемента связываются формулой, прописываемой так: =D13*$G$2. Где в D13 прописывается суммарное количество по реализации, а $G$2 – адрес искомого элемента.

3

Применение функции и ее настройка

Когда формула будет готова, необходимо использовать непосредственно саму функцию:

1. Нужно переключиться в раздел «Данные» и нажать «Поиск решения».

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

1. Далее нужно перейти к настройкам прочих параметров. В разделе «До:» есть возможность задать максимальную и минимальную границу либо точное число.

1. Потом заполняется поле «Изменяя значения переменных:». Здесь вносятся данные искомой ячейки, которая содержит конкретное значение. Координаты прописываются самостоятельно или кликается соответствующая ячейка в документе.

1. Затем редактируется вкладка «В соответствии с ограничениями:», где задаются ограничения применяемых данных. К примеру, исключаются десятичные дроби либо отрицательные числа.

1. После открывается окно, которое позволяет добавлять ограничения при расчетах. В начальной строке указываются координаты ячейки либо целого диапазона. Следуя условиям задачи, указываются данные искомой ячейки, где выводится показатель скидки. Затем определяется знак сравнения. Устанавливается «больше либо равно», чтобы конечное значение не было со знаком «минус». «Ограничение», устанавливаемое в 3 строке, в такой ситуации равняется 0. Возможно выставить также ограничение посредством «Добавить». Последующие действия аналогичны.

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

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

1. В настройках можно выставить «Точность ограничения» и «Пределы решения». В нашей ситуации использовать эти опции нет нужды.

1. Когда настройки завершены, запускается сама функция – нажимается «Найти решение».

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

1. Есть вероятность, что расчеты оказались ошибочными или есть необходимость в изменении исходных данных в целях получения других показателей. В такой ситуации требуется вновь открыть окно с настройками и перепроверить сведения.
2. Когда данные точны, можно воспользоваться альтернативным методом. В этих целях нужно нажать на текущий вариант и из появившегося списка выбрать самый подходящий способ:

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

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

1. Когда получена искомая скидка, остается ее применить для подсчета суммы скидок по каждому наименованию. В этих целях выделяется начальный элемент столбика «Сумма скидки», прописывается формула «=D2*$G$2» и жмется «Enter». Значки доллара проставляются, чтобы во время растягивания формулы на смежные строчки G2 не изменялась.

1. Теперь будет получена сумма скидки для начального наименования. Затем следует навести курсор на угол ячейки, когда он станет «плюсом», зажимается ЛКМ и формула растягивается на необходимые строки.
2. После этого таблица будет окончательно готова.

Загрузить/сохранить параметры Поиска решений

Данная опция полезна при применении различных вариантов ограничений.

1. В меню «Параметры поиска решения» следует нажать «Загрузить/сохранить».
2. Вводится диапазон для области модели и нажимается «Сохранить или Загрузить».

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

Важно! Для сохранения последних настроек в меню «Параметры поиска решения» сохраняется книга. Каждый лист в ней имеет собственные параметры надстройки «Поиск решения». Помимо того, для листа возможно выставить больше 1 задачи при нажатии кнопки «Загрузить или сохранить» в целях сохранения отдельных задач.

Простой пример использования Поиска решения

Нужно провести загрузку контейнера тарой, чтобы его масса была максимальной. Емкость обладает объемом в 32 куб. м. Наполненная коробка имеет вес в 20 кг, ее объем равен 0,15 куб. м. Ящик – 80 кг и 0,5 куб. м. Требуется, чтобы общее число тары составляло не менее 110 шт. Данные организовываются так:

18

Переменные модели отметим зеленым. Целевая функция выделяется красным. Ограничения: по наименьшему количеству тары (больше либо равно 110) и по массе (=СУММПРОИЗВ(B8:C8;B6:C6) – суммарный вес тары, находящейся в контейнере.

По аналогии считаем общий объем: =СУММПРОИЗВ(B7:C7;B8:C8). Такая формула необходима, чтобы выставить ограничение на суммарный объем тары. Потом посредством «Поиск решения» вводятся ссылки на элементы с переменными, формулами и самими показателями (либо ссылки на конкретные ячейки). Разумеется, что количество тары – целое число (также является ограничением). Нажимаем «Найти решение», в результате чего находится такое число тары, когда общая масса максимальна и учтены все ограничения.

Поиску решения не удалось найти решения

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

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

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

В любой ситуации изначально следует изучить модель на отсутствие противоречия ограничений. Зачастую подобное взаимосвязано с ненадлежащим подбором соотношения либо предельного показателя.

В вышеуказанном примере показатель максимального объема указан 16 куб. м вместо 32, потому такое ограничение противоречит показателям по минимальным количествам мест, поскольку ему будет соответствовать число 16,5 куб. м.

19

Заключение

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