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

Ошибки при загрузке надстройки Excel «Поиск решения»

​ нам? Догадываюсь, ему​ Excel 2016 (KB3114374)​

​ быть? Подскажите, пожалуйста,​

​ просто нужно нажать​ в роли изменяемых​ конкретного работника.​ уровня.​ – это удобный​.​

​ задайте диапазон ячеек,​

​ и ящиков), а1​ действительно не существует​ такие количества коробок​ целевую функцию, на​ случае линейных моделей,​ на перевозку были​​ значение Надстройки Excel​ как минимум для​​ 32-Bit Edition​ кто чем сможет.​ на кнопку «ОК».​

1. ​ ячеек могут выступать​

2. ​Разумеется, что работа будет​​Кроме того, в состав​​ инструмент, созданный для​​Нажмите кнопку​​ содержащих сохраненные ранее​​ и а2 –​​ (хотя это может​

3. ​ и ящиков, при​​ формулы для ограничений​​ в нелинейном случае​

4. ​ бы минимальными;​​ и нажмите кнопку​​ официальности нужна папка,​

5. ​Он же: excel2016-kb3114374-fullfile-x86-glb​​ Тема мутная, просвета​ После этого понравившийся​​ как отдельные поля,​ начинаться с разработки​ нынешней версии сего​​ работы с большими​​Параметры ActiveX​

   ​ настройки (нельзя указывать​​ их веса. В​ быть и так).​ котором общий их​ и сами значения​ находит локальный оптимум​Найти распределение нескольких станков​ Перейти;​

​ где будет находиться​ (40 724 Кб).​ не видно.​ вам ответ будет​ так и целый​ таблицы, внесения в​ табличного редактора были​

​ базами данных, количество​

​.​ только одну верхнюю​ линейной модели ограничения​ Теперь, основываясь на​ вес (целевая функция)​

​ ограничений;​

​ (ближайший к первоначальному​

​ по разным видам​В поле Доступные надстройки​ указанный файл.​Попробую установить. Чуть​У меня стоит​

​ зафиксирован в таблице.​ их диапазон. Вообще-то,​

​ нее всех требуемых​

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

​ максимален, и при​

​Запустите Поиск решения для​ значению переменной).​ работ, чтобы общие​ установите флажок рядом​

support.office.com>

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

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

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

Как добавить ограничения?

Если вам требуется добавить какие-то ограничения, то необходимо использовать кнопку «Добавить». Учтите, что при задании таких значений нужно быть предельно внимательным

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

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

Вами могут применяться знаки: «=», «>=», «

Важно! Последний вариант допускает использование всех разных значений. Это доступно только в версиях Ексель 2010 и выше, что является еще одной причиной перехода именно на них

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

Если мы говорим о расчете премии, то коэффициент должен быть строго положительным. Задать данный параметр можно сразу несколькими методами. Легко выполнить эту операцию, воспользовавшись кнопкой «Добавить». Кроме того, можно выставить флажок «Сделать переменные без ограничений неотрицательными». Где найти эту опцию в старых версиях программы?

Зачем нужна функция

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

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

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

Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.

Итак, у нас есть данные про продаже товаров, представленные в табличном виде.

И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.

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

Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.

Транспортная задача: описание

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

Транспортные задачи бывают двух типов:

• Закрытая – совокупное предложение продавца равняется общему спросу.
• Открытая – спрос и предложение не равны. Чтобы решить такую задачу, нужно сначала привести ее к закрытому типу. В этом случае добавляется условный покупатель или продавец с недостающим количеством спроса или предложения. Также в таблицу издержек следует внести соответствующую запись (с нулевыми значениями).

Пример решения транспортной задачи в Excel

Теперь давайте разберем конкретный пример решения транспортной задачи.

Условия задачи

Имеем 5 поставщиков и 6 покупателей. Объёмы производства этих поставщиков составляют 48, 65, 51, 61, 53 единиц. Потребность покупателей: 43, 47, 42, 46, 41, 59 единиц. Таким образом, общий объем предложения равен величине спроса, то есть, мы имеем дело с закрытой транспортной задачей.

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

Решение задачи

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

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

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

Открывается «Мастер функций». В списке, который предлагает он, нам следует отыскать функцию СУММПРОИЗВ. Выделяем её и жмем на кнопку «OK».

Открывается окно ввода аргументов функции СУММПРОИЗВ. В качестве первого аргумента внесем диапазон ячеек матрицы затрат. Для этого достаточно выделить курсором данные ячейки. Вторым аргументом выступит диапазон ячеек таблицы, которая была приготовлена для расчетов. Затем, жмем на кнопку «OK».

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

Становимся в нижний правый угол ячейки с функцией СУММ. Появляется маркер заполнения. Жмем на левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы для расчета. Таким образом мы скопировали формулу.

Кликаем по ячейке размещенной сверху от верхней левой ячейки таблицы для расчетов. Как и в предыдущий раз вызываем функцию СУММ, но на этот раз в качестве аргумента используем первый столбец таблицы для расчетов. Жмем на кнопку «OK».

Копируем маркером заполнения формулу на всю строку.

Переходим во вкладку «Данные». Там в блоке инструментов «Анализ» кликаем по кнопке «Поиск решения».

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

Запускается окно добавления ограничения. Прежде всего, нам нужно добавить условие того, что сумма данных в строках таблицы для расчетов должна быть равна сумме данных в строках таблицы с условием. В поле «Ссылка на ячейки» указываем диапазон суммы в строках таблицы расчетов. Затем выставляем знак равно (=). В поле «Ограничение» указываем диапазон сумм в строках таблицы с условием. После этого, жмем на кнопку «OK».

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

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

Как видим, решение транспортной задачи в Excel сводится к правильному формированию вводных данных. Сами расчеты выполняет вместо пользователя программа.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Из Википедии — свободной энциклопедии

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

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

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

Алгоритм решения

Итак, приступи к решению нашей задачи:

1. Для начала строим таблицу, количество строк и столбцов в которой соответствует числу продавцов и покупателей, соответственно.
2. Перейдя в любую свободную ячейку щелкаем по кнопке “Вставить функцию” (fx).
3. В открывшемся окне выбираем категорию “Математические”, в списке операторов отмечаем “СУММПРОИЗВ”, после чего щелкаем OK.
4. На экране отобразится окно, в котором нужно заполнить аргументы:
   • в поле для ввода значения напротив первого аргумента “Массив1” указываем координаты диапазона ячеек матрицы затрат (с желтым фоном). Сделать это можно, используя клавиши на клавиатуре, или просто выделив нужную область в самой таблице с помощью зажатой левой кнопки мыши.
   • в качестве значения второго аргумента “Массив2” указываем диапазон ячеек новой таблицы (либо вручную, либо выделив нужные элементы на листе).
   • по готовности жмем OK.
5. Щелкаем по ячейке, расположенной слева от самого верхнего левого элемента новой таблицы, после чего снова жмем кнопку “Вставить функцию”.
6. На этот раз нам нужна функция “СУММ”, которая также, находится в категории “Математические”.
7. Теперь нужно заполнить аргументы. В качестве значения аргумента “Число1” указываем верхнюю строку созданной для расчетов таблицы (целиком) – вручную или методом выделения на листе. Жмем кнопку OK, когда все готово.
8. В ячейке с функцией появится результат, равный нулю. Наводим указатель мыши на ее правый нижний угол, и когда появится Маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца таблицы.
9. Это позволит скопировать формулу и получить аналогичные результаты для остальных строк.
10. Выбираем ячейку, которая находится сверху от самого верхнего левого элемента созданной таблицы. Аналогично описанным выше действиям вставляем в нее функцию “СУММ”.
11. В значении аргумента “Число1” теперь указываем (вручную или с помощью выделения на листе) все ячейки первого столбца, после чего кликаем OK.
12. С помощью Маркера заполнения выполняем копирование формулы на оставшиеся ячейки строки.
13. Переключаемся во вкладку “Данные”, где жмем по кнопке функции “Поиск решения” (группа инструментов “Анализ”).
14. Перед нами появится окно с параметрами функции:
    • в качестве значения параметра “Оптимизировать целевую функцию” указываем координаты ячейки, в которую ранее была вставлена функция “СУММПРОИЗВ”.
    • для параметра “До” выбираем вариант – “Минимум”.
    • в области для ввода значений напротив параметра “Изменяя ячейки переменных” указываем диапазон ячеек новой таблицы (без суммирующей строки и столбца).
    • нажимаем кнопку “Добавить” в блоке “В соответствии с ограничениями”.
15. Откроется небольшое окошко, в котором мы можем добавить ограничение – сумма значений первых столбцов исходной и созданной таблицы должны быть равны.
    • становимся в поле “Ссылка на ячейки”, после чего указываем нужный диапазон данных в таблице для расчетов.
    • затем выбираем знак “равно”.
    • в качестве значения для параметра “Ограничение” указываем координаты  аналогичного столбца в исходной таблице.
    • щелкаем OK по готовности.
16. Таким же способом добавляем условие по равенству сумм верхних строк таблиц.
17. Также добавляем следующие условия касательно суммы ячеек в таблице для расчетов (диапазон совпадает с тем, который мы указали для параметра “Изменяя ячейки переменных”):
    • больше или равно нулю;
    • целое число.
18. В итоге получаем следующий список условий в поле “В соответствии с ограничениями”. Проверяем, чтобы обязательно была поставлена галочка напротив опции “Сделать переменные без ограничений неотрицательными”, а также, чтобы в качестве метода решения стояло значение “Поиск решения нелинейных задач методов ОПГ”. Когда все готово, нажимаем “Найти решение”.
19. В результате будет выполнен расчет и отобразится окно с результатами поиска решения. Оцениваем их, и в случае, когда они нас устраивают, нажимаем OK.
20. Все готово, мы получили таблицу с заполненными данными и транспортную задачу можно считать успешно решенной.

Создание формулы

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

Вычисление начинается со знака равенства. К примеру, если в ячейке указывается «=КОРЕНЬ(номер клетки)», то будет использована соответствующая функция.

После того как была напечатана основная формула со знаком «=», нужно указать на данные, с которыми она будет взаимодействовать. Это может быть одна или несколько ячеек. Если формула подходит для 2-3 клеток, то объединить их можно, используя знак «+».

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

Установка Поиска решения

Команда Поиск решения находится в группе Анализ на вкладке Данные.

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

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

Примечание. Окно Надстройки также доступно на вкладке Разработчик. Как включить эту вкладку читайте здесь.

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

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

Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.

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

Ниже приведен небольшой ликбез по этой теме.

Надстройка Поиск решения помогает определить лучший способ сделать что-то:

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

Вот некоторые типичные примеры оптимизационных задач:

• Определить план производства, при котором доход от реализации произведенной продукции максимальный;
• Определить схему перевозок, при которой общие затраты на перевозку были бы минимальными;
• Найти распределение нескольких станков по разным видам работ, чтобы общие затраты на производство продукции были бы минимальными;
• Определить минимальный срок исполнения всех работ проекта (критический путь).

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

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

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

Использование функции

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

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

При этом важно, чтобы в ячейке, в которой в дальнейшем отразится сумма скидки, была записана формула ее расчета (в нашем случае – умножение полной суммы на размер скидки)

Итак, алгоритм действий следующий:

1. Переходим во вкладку “Данные”, в которой нажимаем на кнопку “Анализ “что если” в группе инструментов “Прогноз”. В раскрывшемся списке выбираем “Подбор параметра” (в ранних версиях кнопка может находиться в группе “Работа с данными”).
2. На экране появится окно для подбора параметра, которе нужно заполнить:
   • в значении поля “Установить в ячейке” пишем адрес с финальными данными, которые нам известны, т.е. это ячейка с суммой скидки. Вместо ручного ввода координат можно просто щелкнуть по нужной ячейке в самой таблице. При этом курсор должен быть в соответствующем поле для ввода информации.
   • В качестве значения указываем сумму скидки, которая нам известна – 560 руб.
   • В поле “Изменяя значение ячейки” вручную или посредством клика мышью указываем координаты ячейки (должна участвовать в формуле расчета суммы скидки), в которой планируем вывести начальное значение.
   • по готовности нажимаем OK.
3. Программа выполнит расчеты и выдаст результат в небольшом окошке, которое можно закрыть, нажав кнопку OK. Также найденные значения автоматически появятся в заданных ячейках таблицы.
4. Аналогичным образом можно посчитать цену без скидки для других товаров, если нам известна точная сумма скидки по каждому из них.

Использование

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

Перенесем эти сведения на рабочий лист excel.

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

1. Процентная ставка.
2. Период (кпер).
3. Сумма платежа (плт).

Для первого года формула будет выглядеть следующим образом:

Как видите, число отрицательное – это особенной функции БС. Чтобы этого избежать, ячейку с суммой денег нужно сделать отрицательной. Тогда итоговые результаты будут отображаться корректно.

Воспользуемся автозаполнением и получим сумму средств после 5 лет нахождения на депозите под 4 процента годовых с ежегодным пополнением.

Полученная цифра не удовлетворяет условию в 12000 после пятилетнего периода. Теперь необходимо воспользоваться инструментом Поиск решения. При этом изменяемыми параметрами будет процент и первоначальная сумма. Заполняете диалоговое окно построчно.

Нажимаете кнопку Выполнить и получаете решение задачи с условием достижения поставленной цели за пять лет.

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

Повторяете решение с новой конфигурацией и получаете следующие данные:

Как видите, чтобы достигнуть отметки в 12000$ через пять лет, необходимо найти депозит под 4,03 процента годовых и ежегодно пополнять его на сумму 2214 доллара 01 цент.

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