Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Тест excel поиск решения

Практическое занятие по теме: «Подбор параметра и оптимизация (поиск решений) в Excel»

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Практическое занятие по теме: «Подбор параметра и оптимизация (поиск решений) в Excel »

Цель практического занятия: Изучение технологии «Подбора параметра и оптимизация (поиск решений) » для решения задач, имеющих точное целевое значение, зависящее от одного неизвестного параметра

Познакомится с основными ключевыми понятиями «Подбора параметра»

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

Решение задач по теме «Подбор параметра» по образцу и по алгоритму.

Решение задач по указанной теме самостоятельно

Ознакомление студентов с ключевыми понятиями.

Рассмотрение процесса нахождения исходных данных

Знакомство с понятием Подбор параметра и Поиск решения

Решение задач по теме «Подбор параметра» по образцу и по алгоритму

Решение задач по указанной теме самостоятельно

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

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

Итерация — это многократный пересчет листа до удовлетворения определенного числового условия. Excel не может автоматически рассчитать значение по формуле, которая ссылается (прямо или косвенно) на ячейку, содержащую формулу (это называется циклической ссылкой). Если формула содержит обратную ссылку на одну из своих собственных ячеек, необходимо определить, сколько раз следует пересчитывать формулу. Циклические ссылки могут пересчитываться до бесконечности. Однако существует возможность управления максимальным числом итераций и количеством допустимых изменений.

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

Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул в Excel называется анализом «что-если».

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

Другими словами, вы можете задать вопрос типа: Какой рост продаж необходим для получения дохода в $1 200 000? В Excel для этого предусмотрены два подходящих средства.

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

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

1. Подбор параметра

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

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

Создайте новый файл под именем Лаб.раб. Лист 1 переименуйте в Подбор .

Оформите таблицу (рис. 1). Введите указанную формулу.

Активизируйте ячейку В3 . Выполните команду Данные — -Анализ «что- если» Подбор параметра .

В открывшемся диалоговом окне укажите значение необходимой конечной суммы и ссылку на ячейку с искомым значением вклада (рис.2).

Нажмите кнопку ОК . Средство подбор параметра найдет решение и сообщит об этом (рис. 3). Нажмите кнопку ОК и убедитесь, что искомое значение помещено в ячейке В2 .

Решите задачу : Для покупки автомобиля Вам необходима сумма 200 000 руб. У Вас есть возможность взять ипотечную ссуду, при этом нужно сделать первый взнос 20%. Определите, какую сумму нужно взять в банке, чтобы на руки вы получили требуемую сумму.

Для решения задачи оформите таблицу (рис.4). Введите формулы.

С помощью средства Подбор параметра определите размер ссуды.

Задачи оптимизации (поиск решения)

Цель : Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).

Задание 1. Составление плана выгодного производства.

Фирма производит несколько видов продукции из одного и того же сырья – А, В, С. Реализация продукции А дает прибыль 10р., В – 15р., С-20р. На единицу изделия. Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.

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

Использование надстройки «Поиск решения» для определения оптимального набора продуктов

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

В этой статье описано использование поиска решения — надстройки Microsoft Excel, которую можно использовать для анализа «что если», чтобы определить оптимальный набор продуктов.

Как определить ежемесячный набор продуктов, который будет максимально подключаться к рентабельности?

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

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

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

Теперь рассмотрим следующий пример проблемы с набором продуктов. Вы можете найти решение этой проблемы в файле Продмикс. xlsx, показанном на рисунке 27-1.

Рассмотрим, что мы работаем для фармацевтической компании, которая создает шесть разных продуктов на своем предприятии. Для производства каждого продукта требуется материал и сырье. В строке 4 на рисунке 27-1 показано количество рабочих часов, необходимых для производства килограмма каждого продукта, а в строке 5 — количество килограммов сырья, необходимых для производства килограмма каждого продукта. Например, для производства килограмма продукта 1 требуется 6 часов трудозатрат и 3,2 фунта сырья. Для каждого лекарства Цена за штуку выдается в строке 6, стоимость единицы на килограмм дается в строке 7, а прибыль на килограмм — в строке 9. Например, если товар 2 продает на $11,00 для каждого фунта, то на него возмещаются стоимость за единицу $5,70, а для каждого из них — доход $5,30 долларов за каждый фунт. Потребность в месяце для каждого лекарства указана в строке 8. Например, спрос на товар 3 составляет 1041 фунта. В этом месяце на 4500 часов трудозатрат и 1600 килограмма сырья доступны. Как эта компания может максимально увеличить месячный доход?

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

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

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

Читать еще:  Как очистить видео в вк

(Трудозатраты на килограмм лекарства 1) * (произведенные
килограммы лекарства 1) + (трудозатраты на фунты лекарства 2) * (количество килограммов лекарства 2) *.
(Трудозатраты на килограмм лекарства 6) * (произведенные килограммы для лекарства 6)

Мы могли вычислить использование трудовых ресурсов более утомительно, как D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 и I2 * I4. Кроме того, использование сырья может быть вычислено как D2 * D5 + E2 *SHIFT+F5 + F2 * F5 + G2 * G5 + H2 * H5 и I2 * I5. Однако ввод этих формул на листе для шести продуктов занимает много времени. Представьте себе, сколько времени потребуется, если вы работали с компанией, которая создала, например, продукты 50 на своем предприятии. Намного проще всего вычислять ресурсы и использование сырья — копирование из D14 в D15 формулы СУММПРОИЗВ ($D $2: $I $2, D4: i4). В этой формуле представлено значение D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (это наш трудный расход), но это еще проще вводить! Обратите внимание, что я использую знак $ с диапазоном D2: I2, так что при копировании формулы по-прежнему захватывается набор продуктов из строки 2. Формула в ячейке D15 вычисляет необработанное использование сырья.

Аналогичным образом, наш доход определяется с помощью

(Прибыль на лекарства 1 за килограмм) * (произведенные килограммы лекарства 1) +
(стоимость лекарства 2 на фунт) * (произведенные килограммы лекарства 2) *.
(Прибыль на килограмм лекарства 6) * (произведенные килограммы лекарства 6)

Прибыль легко вычисляется в ячейке D12 с помощью формулы СУММПРОИЗВ (D9: I9, $D $2: $I $2).

Теперь мы можем идентифицировать три компонента нашей модели «Поиск решения» для набора продуктов.

Целевая ячейка.Наша цель – это максимизировать прибыль (вычисленные в ячейках D12).

Изменяемые ячейки.Количество произведенных единиц продукта (указывается в диапазоне ячеек D2: I2)

Unique. Существуют указанные ниже ограничения.

Не используйте больше трудовых и необработанных материалов, чем доступно. Таким образом, значения в ячейках D14: D15 (используемые ресурсы) должны быть меньше или равны значениям в ячейках F14: F15 (доступные ресурсы).

Не делайте лекарства больше, чем по запросу. Таким образом, значения в ячейках D2: I2 (произведенные килограммы для каждого лекарства) должны быть меньше или равны спросу на каждое лекарство (в ячейках D8: I8).

Мы не можем выдать отрицательную сумму лекарства.

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

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

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

Появится диалоговое окно Параметры поиска решения, как показано на рисунке 27-2.

Щелкните поле задать целевую ячейку и выберите нашу ячейку дохода (ячейка D12). Щелкните поле изМеняя ячейки и наведите указатель мыши на диапазон D2: I2, который будет содержать произведенные килограммы для каждого лекарства. В диалоговом окне должно появиться представление 27-3.

Теперь мы готовы к добавлению ограничений в модель. Нажмите кнопку Добавить. Появится диалоговое окно Добавление ограничения, показанное на рисунке 27-4.

Чтобы добавить ограничения на использование ресурсов, щелкните поле Ссылка на ячейку и выберите диапазон D14: D15. Выберите _Лт_ = из среднего списка. Щелкните поле ограничения и выберите диапазон ячеек F14: F15. Диалоговое окно Добавление ограничения теперь должно выглядеть примерно так, как показано на рисунке 27-5.

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

Предположим, что требуется выполнение требования для каждого продукта. (Просмотрите лист «нет подходящего решения » в файле продмикс. xlsx.) Мы должны изменить ограничения на спрос с D2: I2 = D8: I8. Для этого откройте «Поиск решения», выберите ограничение D2: I2

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

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

На заводской фабрике (A, B, C и D) выводятся три продукта (товары 1, 2 и 3). В этом месяце производитель микросхем может продавать 80 единиц продукта 1, 50 единиц продукта 2 и не менее 50 единиц продукта 3. Специалист по A может вносить только продукты 1 и 3. Специалист B может вносить только продукты 1 и 2. Специалист на языке C может сделать только продукт 3. Техника D может быть доступна только на товар 2. Для каждой изготовленной единицы продукты вносят следующие прибыль: товар 1, $6; Продукт 2, $7; и Product 3, $10. Время (в часах), необходимое каждому специалисту по производству продукта, можно получить, выполнив указанные ниже действия.

Функция в Excel: поиск решения

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

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

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

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

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

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

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

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

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

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

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

Формула готова. Теперь нужно применить саму функцию.

  1. Переключаемся во вкладку “Данные” и нажимаем кнопку “Поиск решения”.
  2. Откроются “Параметры”, где необходимо задать нужные настройки. В поле “Оптимизировать целевую функцию:” указываем адрес целевой ячейки, где планируется вывести сумму по всем скидкам. Можно прописать координаты вручную, либо выбрать из таблицы, для чего сначала кликаем по области ввода, затем – по нужной ячейке.
  3. Переходим к настройке других параметров. В пункте “До:” можно задать максимальную границу, минимальную границу или же точное число. Исходя из поставленной задачи ставим отметку рядом с опцией “Значение” и набираем “4500000” – сумма скидок по всем наименованиям.
  4. Следующее для заполнения поле – “Изменяя значения переменных:”. В него нужно внести координаты искомой ячейки, содержащей определенное значение. Это значение и есть та самая скидка, которую мы пытаемся вычислить. Также, как и с выбором целевой ячейки, координаты можно написать вручную, либо кликнуть по нужной ячейке в самой таблице.
  5. Теперь нужно отредактировать раздел “В соответствии с ограничениями:”, в котором задаем ограничения используемых данных. Например, можно исключить десятичные дроби или, скажем, отрицательные числа. Это делается через кнопку “Добавить”.
  6. Откроется вспомогательно окно, позволяющее добавить ограничения во время вычислений. В первом поле указываем координаты определенной ячейки или области ячеек, для которых это условие должно действовать. Согласно нашей задаче, указываем координаты искомой ячейки, в которой будет выводиться значение скидки. Следующий шаг – определить знак сравнения. Устанавливаем “больше или равно”, чтобы итоговое число не могло быть отрицательным. “Ограничение”, которое устанавливается в третьем поле, в этом случае будет равно цифре 0, поскольку именно относительно этого значения задается условие.Можно установить еще одно ограничение с помощью кнопки “Добавить”. Дальнейшие действия по его настройке будут аналогичными. По готовности щелкаем OK.
  7. После выполнения описанных выше действий в самом большом поле окна появится установленное только что ограничение. Список может быть довольно большим и зависит от сложности предполагаемых расчетов, но в данном случае будет достаточно и одного условия.Под этим полем также есть опция, позволяющая делать все остальные переменные, не затрагиваемые ограничениями, неотрицательными. Однако, будьте внимательны и проследите за тем, чтобы между этим параметром и поставленными ограничениями не было противоречия, иначе при расчете в программе может возникнуть конфликт.
  8. Также можно задать немалое количество дополнительных настроек. Чуть ниже справа есть кнопка “Параметры”, позволяющая это сделать. Нажимаем на нее и открываем новое окно.
  9. В этих настройках у нас есть возможность установить “Точность ограничения” и “Пределы решения”. В нашем случае задавать данные параметры нет необходимости, поэтому после ознакомления с представленным окном, его можно закрыть, нажав OK.
  10. Итак, все настройки выполнены и параметры установлены. Пора запускать функцию – для этого нажимаем кнопку “Найти решение”.
  11. После этого программа сделает все необходимые расчеты и выдаст результаты в нужных ячейках. При этом сразу же откроется окно “Результаты поиска решения”, где можно сохранить/отменить результаты или настроить параметры поиска заново. Если результаты нас устраивают, оставляем отметку напротив опции “Сохранить найденное решение” и нажимаем ОК. При этом, если мы предварительно установим галочку слева от надписи “Вернуться в диалоговое окно параметров поиска решения”, после того, как мы щелкнем OK, мы обратно переключимся к настройке функции поиска решения.
  12. Вполне вероятно, что расчеты могут показаться неправильными, либо возникнет желание немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно посмотреть поля с введенными данными.
  13. Если с данными все нормально, можно попробовать задействовать другой метод решения. Для этого щелкаем по текущему варианту и из раскрывшегося перечня выбираем способ, который нам кажется наиболее подходящим:
    • Первый – ищет решение методом обобщенного приведенного градиента (ОПГ) для нелинейных задач. Стандартно выбран именно этот вариант, но можно попробовать и другие.
    • Второй – пытается отыскать решение для линейных задач, используя симплекс-метод.
    • Третий – для выполнения поставленной задачи использует эволюционный поиск.
    • В том случае, если ни один из методов не принес удовлетворительных результатов, стоит проверить данные в таблице и параметрах еще раз, поскольку именно это является самой частой ошибкой в подобного рода задачах.
  14. Теперь, когда мы получили требуемую скидку, осталось ее применить, чтобы рассчитать суммы скидок по всем наименованиям. Для этого отмечаем первую ячейку столбца “Сумма скидки”, пишем в ней формулу “=D2*$G$2” и нажимаем Enter. Знаки доллара ставятся для того, чтобы при растягивании/копировании формулы на другие строки, ячейка G2 со скидкой оставалась неизменной в расчетах.
  15. Мы получили сумму скидки для первого наименования. Теперь наводим курсор на нижний правый угол ячейки с результатом, как только он поменяет форму на крестик, зажав левую кнопку мыши растягиваем формулу на все строки, по которым хотим посчитать аналогичную сумму.
  16. Теперь наша таблица полностью готова в соответствии с поставленной задачей.
Читать еще:  Как создать свободную таблицу в excel

Заключение

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

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

Постановка задачи

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

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

Перед началом оптимизации необходимо будет составить несложную таблицу на листе Excel – нашу математическую модель, описывающую ситуацию:

  • Светло-желтая таблица (C4:G6) описывает стоимость доставки одной единицы товара от каждого склада до каждого магазина.
  • Лиловые ячейки (C15:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
  • Красные ячейки (J10:J13) отображают емкость каждого склада – предельное количество товара, которое склад вмещает.
  • Желтые (C13:G13) и синие (H10:H13) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
  • Общая стоимость доставки (J18) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки — для подсчёта здесь используется функция СУММПРОИЗВ(SUMPRODUCT).

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

Решение

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

Если на вкладке Данные вашего Excel такой команды нет – ничего страшного — значит надстройка просто еще не подключена. Для ее активации откройте Файл, далее выберите ПараметрыНадстройкиПерейти (Options — Add-Ins — Go To) . В открывшемся окне поставьте галочку напротив нужной нам строки Поиск решения (Solver) .

В этом окне нужно задать следующие параметры:

  • Оптимизировать целевую функцию(Set targetcell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (J18). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданному значению (например, вписаться ровно в выделенный бюджет).
  • Изменяя ячейки переменных(Bychangingcells) – здесь укажем зеленые ячейки (C10:G12), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.
  • В соответствии с ограничениями(SubjecttotheConstraints) – список ограничений, которые надо учитывать при проведении оптимизации. Для добавления ограничений в список нужно нажать кнопку Добавить(Add) и ввести условие в появившееся окно. В нашем случае, это будет ограничение на спрос:

и ограничение на предельный объем складов:


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

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

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

В выпадающем списке Выберите метод решения (Select a solving method) дополнительно требуется подобрать подходящий математический метод для решения на выбор из трех вариантов:

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

Наша задача явно относится к линейным: доставили 1 шт — затратили 40 р., доставили 2 шт — затратили 80 р. и т.д., так что симплекс-метод будет наилучшим выбором.

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

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

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

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

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

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

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

Решаем задачу с помощью Поиска решений в Excel

Методика решения задачи симплекс-методом с
использованием Microsoft Excel

Алгоритм получения решения задачи симплекс-методом с использованием офисного приложения Microsoft Excel рассмотрим на примере 2.2.1. Математическая модель задачи имеет следующий вид:

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

Ввод исходных данных задачи

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

Экранная форма для ввода условий задачи имеет следующий вид
(рис. 2.2.2):

В ячейках В4:С4 находятся значения коэффициентов целевой функции; в массиве В6:С8 –коэффициенты левой части ограничений; в столбце Е6:Е8значения правой части ограничений. Ячейки В2:С2соответствуют переменным задачи, а в ячейке Е2будет отображаться значение целевой функции. Сюда необходимо ввести формулу, по которой это значение рассчитывается, то есть . Для этого курсор ставится в ячейку и далее набирается следующее выражение: .

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

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

поставить курсор в поле Е2;

выбрать на панели инструментов кнопку ;

в окне «Категория» выбрать «Математические». В окне «Выберите функцию» «СУММПРОИЗВ» (рис. 2.2.3) и нажать «ОК»;

Ввести аргументы функции: в строку «Массив 1» выражение В2:С2, а в строку «Массив 2» выражение В4:С4 (можно, выделять соответствующие массивы с помощью мыши) (рис. 2.2.4) и нажать «ОК»;

После этого в ячейке Е2 появится текущее значение целевой функции, вычисленное по введенной формуле. Оно равно нулю, так как переменные в данный момент равны нулю.

Аналогично в ячейки D6:D8вводятся формулы для расчета левых частей ограничений (рис. 2.2.5):

Для ячейкиD6формула имеет вид ,а ее реализация в ячейке: или =СУММПРОИЗВ(В2:C2; В6:C6).

Для ячейкиD7формула имеет вид ,а ее реализация в ячейке: или = СУММПРОИЗВ(В2:C2; В7:C7).

Для ячейкиD8формула имеет вид ,а ее реализация в ячейке: или = СУММПРОИЗВ(В2:C2; В8:C8).

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

Для того, чтобы сделать абсолютную ссылку на определенный столбец, необходимо поставить символ $, перед буквой, обозначающей имя столбца. Например $В2:$C2.Чтобы зафиксировать строку, символ $, ставится перед номером строки: В$2:C$2.Если необходимо сделать абсолютную ссылку на конкретную ячейку (ячейки), символ $ ставится и перед именем столбца и перед номером строки: $В$2:$C$2.

Абсолютную ссылку на ячейку (ячейки) можно сделать, нажав клавишу F4, когда курсор находится в поле имени ячейки. При однократном нажатии клавиши будет сделана абсолютная ссылка на массив или ячейку ($В$2: $C$2). Если клавишу нажать дважды, будет сделана абсолютная ссылка на номер строки (В$2: C$2). При следующем нажатии клавиши ссылка будет сделана на имя столбца ($В2: $C2).

При данном способе реализации симплекс-метода достаточно сделать ссылку лишь на соответствующую строку: В$2: C$2. В то же время допустима и абсолютная ссылка на конкретный массив ячеек: $В$2: $C$2.

Таким образом, для ячейки D6формула будет иметь вид или = СУММПРОИЗВ(В$2: C$2;В6:C6) (в случае абсолютной ссылки на массив = СУММПРОИЗВ($В$2: $C$2;В6:C6)).

Затем эту формулу необходимо скопировать в ячейки D7иD8.Копировать формулу можно с помощью клавиш «Ctrl-Insert» копировать и клавиш «Shift-Insert» вставить. Другой способ копирования формул поставить курсор в ячейку, содержащую формулу и протянуть ее за правый нижний угол на все ячейки, в которые ее необходимо скопировать.

После этого экранная форма условий задачи будет иметь вид (рис. 2.2.6).

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

В диалоговом окне «Поиск решения» (рис. 2.2.7) необходимо выполнить следующие действия:

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

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

Поставить курсор в поле «Изменяя ячейки» и ввести адрес массива, в котором находятся значения переменных. В примере это В2:C2.Адрес можно внести также с помощью выделения мышью соответствующих ячеек.

В окне «Ограничения» выбрать кнопку «Добавить», после чего появится окно «Добавление ограничения» (рис. 2.6.8).

В поле «Ссылка на ячейку» ввести адрес ячейки, в которой содержится левая часть ограничения. (Это можно сделать путем выделения мышью соответствующей ячейки на экране). В поле знака открыть список предлагаемых знаков и выбрать нужный. В поле «Ограничения» ввести адрес ячейки, содержащей правую часть ограничений. В примере первое ограничение: D6

Ссылка на основную публикацию
Adblock
detector