Почему не устанавливается поиск решения в excel 2007
Перейти к содержимому

Почему не устанавливается поиск решения в excel 2007

Надстройка Microsoft Excel «Поиск решения»

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

Краткое описание надстройки

Для получения заданного результата по формуле, процедура изменяет значения во влияющих ячейках. Для уменьшения интервала значений, используемых в модели, используются ограничения значений. Надстройка поиск решений является стандартной надстройкой Microsoft Office Excel и доступна сразу при установке Microsoft Office в целом или Microsoft Excel в частности.

Как установить надстройку?

Надстройку «Поиск решения» можно установить двумя способами. Стандартные надстройки, такие как «Поиск решения» и «Пакет анализа» устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки «Поиск решения» на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.

Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию «Добавить или удалить компоненты».

kak-ustanovit-nadstrojku-poisk-resheniya

Далее, нажимаем кнопку «Продолжить», в параметрах установки находим приложение Microsoft Excel, в компонентах этого приложения находим раздел «Надстройки», выбираем надстройку «Поиск решения» и устанавливаем параметр «Запускать с моего компьютера».

ustanovka-nadstrojki-poisk-resheniya

Опять жмем кнопку «Продолжить» и ожидаем пока надстройка установится.

Как подключить надстройку?

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

kak-podklyuchit-nadstrojku-poisk-resheniya

Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье «Как установить надстройку для Excel 2003/2007/2010/2013/2016?» со скриншотами для каждой версии приложения Excel, поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка «Поиск решения») и произвести установку в соответствии с описанием по ссылке выше.

Где найти надстройку «Поиск решения» в Excel 2003/2007/2010?

После установки и подключения надстройки в Excel 2007/2010 на вкладке «Данные» появляется группа «Анализ» с новой командой «Поиск Решения». В Excel 2003 — появляется новый пункт меню «Сервис» с одноименным названием. Поиск решения — стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.

Ищем оптимальное решение задачи с неизвестными параметрами в Excel

bg

«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил. Рассказываем, как освоить функцию поиска решений.

Основные параметры поиска решений

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

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

Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).

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

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

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

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

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

  • нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
  • в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
  • в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

2019-08-06 18.58.30.jpg

2019-08-06 18.58.37.jpg

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

tg_image_2790408830.jpeg

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».

tg_image_954796317.jpeg
tg_image_2790408830.jpeg

tg_image_4145344377.jpeg

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

tg_image_2089575366.jpeg

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

  • общее количество изделий 1000 штук ($D$13 = $D$3);
  • число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
  • количество дней меньше либо равно 30 ($F$9:$F$12 < = $D$6, либо как в примере в ячейке F13 задать функцию МАКС(F9:F12) и поставить ограничение $F$13 < = $D$6).

tg_image_1670540083.jpeg
tg_image_1428577646.jpeg

tg_image_2951437605.jpeg

5) В конце проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится — нужно пересмотреть исходные данные, введенные формулы и ограничения.

tg_image_1895334008.jpeg

Хотите научиться решать задачи в Excel, как это делают в компаниях-лидерах? Приходите на наш онлайн-курс, на котором вы освоите этот инструмент на уровне профи. Вашими преподавателями будут эксперты-практики, а после обучения вы сможете дополнить резюме весомой строчкой. Регистрируйтесь!

Теги

Получите карьерную поддержку

Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Подписаться на карьерную рассылку

Подписывайтесь на рассылку и получайте карьерные советы — от выбора индустрии и компании до лайфхаков по самоорганизации и развитию коммуникативных навыков.

Б1

Стажер в риск-менеджменте

Лаборатория Касперского

Ассистент операционной поддержки

  • Москва
  • Лаборатория Касперского

CROC

Пресейл-менеджер в IT

Технологии Доверия

Специалист по кадровому администрированию

Avito

Стажер в отдел продаж

Kept

Стажер поддержки 1С

Технологии Доверия

Младший юрист

билайн

Координатор проектов

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

WiFiGid

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

ШАГ 1: Активация инструмента

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

  1. В новых версиях Microsoft Excel (2010, 2016 и старше) нажмите по надписи «Файл» в левом верхнем углу окошка.

ПРИМЕЧАНИЕ! Если у вас очень старая версия Эксель 2007 или младше, то нажимаем по логотипу, который также находится в том же самом месте.

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Теперь находим раздел «Параметры».

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Откроется окошко, где вам нужно выбрать соответствующую галочку в самом низу и нажать по кнопке «ОК».

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Подождите пока процесс установки закончится.

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

ШАГ 2: Знакомство с примером и создание таблицы

Итак, саму функцию мы установили, но как же теперь с ней работать? – Давайте разберемся на конкретном простом примере. Представим себе, что у нас есть компания, в которой работает ряд сотрудников. Ежемесячно они получают фиксированную заработную плату. Наступил декабрь и скоро новый год. Начальник поручил нам выделить дополнительную премию к ЗП. Премия будет добавлена относительно определенного коэффициента – то есть заработная плата за месяц умноженная на сам коэффициент.

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

Посмотрите на табличку выше. У нас есть несколько данных, которыми мы будем оперировать через функцию:

  • Целевая ячейка – она же премия.
  • Искомая ячейка – это коэффициент, на который будет умножаться ЗП. Именно это число нам и нужно будет найти.

Две эти ячейки должны быть связаны между собой. Как вы уже поняли, мы будем использовать произведение ячеек ЗП и коэффициента. Для того чтобы коэффициент не изменялся, установите знак доллара ($) перед буквой и цифрой:

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

Теперь растяните формулу на все ячейки столбца «Премия». Посмотрите, чтобы адреса были привольными. Теперь переходим к следующей главе.

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

ШАГ 3: Использование функции и настройка

  1. Переходим в «Данные» и в разделе анализ нажимаем по нужной функции. Откроется дополнительное окно.

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Теперь выбираем её, нажав по ней левой кнопкой мыши.

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Теперь ниже нам нужно установить само значение, то есть сколько компания выделила нам денег на премии. Можно установить максимальное значение, но так как работники работали хорошо, то давайте все же их порадуем и распределим весь бюджет равномерно. Поэтому устанавливаем конкретное значение в 40 000 рублей.

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Давайте еще зайдем в «Параметры».

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

  1. Теперь жмем по кнопке «Найти решение».

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

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

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

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

Обзор и возможности функции

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

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

Включение возможности

Функция поиска решений является надстройкой Excel. Для версий Excel ниже 2010 её потребуется запускать следующим образом:

  1. Открыть «Параметры» программы.параметры программы
  2. Перейти в раздел «Надстройки».Раздел надстроек
  3. Найти «поиск решений» и клацнуть по нему дважды. В случае если пакет не установлен Office предложит загрузить его. С таким предложением потребуется согласиться.

раздел данных

Теперь возможность можно будет найти в разделе «Данные». Отмечена она названием «Поиск решений».

Подготовка таблицы

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

  1. Представим, что в таблице имеются пункты, которые имеют какое-то значение.Заполненная таблица
  2. Осложним задачу введением двух виртуальных групп, которые могут соответствовать пунктам (это могут быть статьи расходов).Ввод виртуальных групп

Как использовать функцию «Найти и заменить» в Excel

В случае соответствия группы пункту она получает «вес» равный «1». В противном случае – «0». Это потребуется для дальнейших операций.

  1. Теперь добавим 2 группы формул, которые выражают взаимодействие между группами, «весомостью» (значение) и пунктами. Пускай это будет сложение и умножение, для простоты. Таким образом мы получим сумму важности пунктов (итоговое значение для каждой из групп) и что-то вроде важности затрат для каждого из пунктов.добавление формул
  2. Теперь постараемся прийти к конечным решениям по затратам и итогам. Сделаем это при помощи операций сложения в соответствующих столбцах.Конечное решение
  3. Последним действием будет сведений разницы между затратами групп.Результат вычислений

Теперь таблицу можно считать сведённой и подготовленной. Требуется только завершить работу с функцией и настроить формулы для её эффективного срабатывания. Предположим, что нас интересует результат, при котором разница между затратами групп будет минимизирована или сравняется с нулём (ячейка «Разница»).

Запускаем функцию подбора решений и начинаем задавать ей необходимые переменные и значения:

Метод решения

  1. Указывается, в какой ячейке находится формула, результат которой интересует пользователя.
  2. Указывается результат, к которому должно привести изменений значений. Если указать в качестве значения 0, то, вероятнее всего, таблица будет заполнена нулями. Чтобы избежать подобного требуется выставить хотя-бы «1».
  3. Диапазон ячеек, подлежащих изменению.
  4. Ограничения, которые можно логически вписать в переменные. В указанном случае это должны быть целые числа 0 или 1.
  5. Метод решения. Лучше оставить без изменений, если ранее не было произведено знакомство с алгоритмами работы.
  6. Запуск поиска решений.

параметры поиска

Получаем следующее заполнение:

Получаем решения

Получаем решение в течении нескольких секунд. Для сравнения, по самым оптимистичным прикидкам, человек потратил бы на аналогичное действие около 5 минут.

Конкретные примеры использования

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

Как использовать поиск в Excel по одному или нескольким значениям

Изготовление йогурта

Пример таблицы

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

расход сырья

В раздел «Расход сырья» внесены формулы, которые опираются на «количество» и нормы расхода. Прибыль является произведением стоимости и количества. Количество и будет переменной, которая будет изменяться в пределах «запасы». Для этого формируется следующий набор условий:

В результате вычислений (с учётом дробного остатка, поскольку условие работы только с целыми числами добавлено не было), получилось, что эффективнее всего производить 1 и 3 йогурты, а второй полностью игнорировать.

Затраты на рекламу

Оптимизация расходов

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

Целевые ячейки

Итак, прибыль является целевой ячейкой (выделена изумрудным цветом). Зелёным выделены расходы на рекламу, а красным максимальные затраты. При поиске решения ограничиваем подстановку переменных в значениях рекламы максимумом, а в качестве цели ставим максимизацию прибыли.

Результат вычислений

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

Отсюда и вытекает главный недостаток «поиска решений». Он оперирует лишь конечной (одной) ячейкой. Чтобы максимизировать прибыль требуется работать с последней ячейкой (прибыль – всего), что сопряжено с вероятностью появления ошибки в программе, если формулы настроены неверно.

Оптимизация игрового процесса

Пример таблицы

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

Способы открыть несколько документов Microsoft Excel в разных окнах

Параметры поиска

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

В условиях имеем: требуется получить максимальную выгоду при лимите времени. Следовательно, программа определяет на каком комплекте сфокусировать внимание. Результат предсказуем: самый дорогой комплект достоин 100% временных затрат.

Установка ограничений

Ввод параметров

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

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

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

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