Auditportal.ru

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

Как найти максимальное или минимальное значение на основе критериев в Excel?

Критерии выбора решений в условиях полной неопределенности

Критерий максимакса (крайнего, «розового» оптимизма) основан на оптимистическом принципе Л. Гурвица, согласно которому выбирается вариант, обеспечивающий наибольший эффект в самой благоприятной ситуации.

Если рассматривать матрицу последствий (3.1) как матрицу влияния E, то оптимальное решение находится из условий обеспечения максимума:

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

Пример 3.3. Выберите вариант решения по критерию maximax, используя матрицу следствий из примера 3.2.

Исходные данные вставляются в Excel (рис. 3.9). Для ячеек (B4:F4; используется функция MAX. В результате для каждого решения (B7:F7) последовательно находятся максимальные значения: a1=8, a2=12, a3=10 и a4=8.

Рис. 3.9. Результаты выбора оптимального решения по критерию maximax

2 Из последовательности найденных максимальных значений ai (G4:G7) с помощью функции MAX (ячейка G8) было выбрано наибольшее значение: a2=12, поэтому рекомендуется принять второе решение.

Затраты H в матрице A (3.1) можно считать потерями, а решение, обеспечивающее наименьшие затраты, выбирается из условия минимизации затрат:

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

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

Этому критерию удовлетворяет стратегия 2 (см. рис.) 3.6).

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

1. Исходные данные вводятся в Excel (Рисунок 3.10). Затем, используя функцию MIN для ячеек (B4:F4;…; B7:F7), последовательно находятся минимальные значения каждого i-го решения: a1=2, a2=2, a3=3, a4=1.

Читайте так же:
Как найти или получить квартал с заданной даты в Excel?

Рисунок 3.10: Результаты выбора оптимального решения в соответствии с критерием минимума.

3. из последовательности минимальных значений ai (G4:G7) функция MIN (ячейка G8) выбирает минимальное значение: a4=1, поэтому четвертое решение является рекомендуемым.

При анализе матрицы стоимости критерий пессимизма принимает следующий вид

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

В случае если будущее неблагоприятное, т.е. принесет наименьший доход: ai = min aij, то выбираем решение, для которого минимальный (гарантированный) доход будет самым большим

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

Пример 3.5 Выберите вариант решения для критерия максимизации матрицы влияния из примера 3.2.

1 Найдите минимальные значения для каждого i-го альтернативного решения с помощью функции MIN: a1=2, a2=2, a3=3, a4=1 (см. рисунок 3.11, ячейки G4:G7)

Рис. 3.11. Результаты выбора оптимального решения по критерию максимума

2. С помощью функции MAX из последовательности найденных минимальных значений ai(G4:G7) выбирается максимальное a3 (ячейка G8).

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

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

В этом сценарии риск наибольший в наихудшей ситуации. Для выбора решения используется матрица рисков R (3.5). Наилучшим вариантом является тот, который имеет наименьшее значение риска:

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

Когда необходимо предотвратить экстремальный риск в любых условиях, этот критерий соответствует стратегии 4 (рис. 3.6).

Пример 3.6. Выберите минимальное решение из матрицы следствий в примере 3.2.

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

2. В каждой строке матрицы рисков функция MAX выбирает свой максимальный элемент (ячейки G4:G7): ri = : r1 = 8, r2 = 6, r3 = 5, r4 = 7.

Читайте так же:
Как найти и выделить повторяющиеся имена, которые совпадают как с именем, так и с фамилией в Excel?

Рис. 3.12. Результаты выбора оптимального решения по минимаксному критерию

Согласно правилу Сэвиджа, выбирается наименьшее из этих значений (функция MIN в ячейке G8): r3 = 5, то есть должно быть принято третье решение (i=3). Выбор этого варианта означает, что максимальный убыток при различных вариантах сценария будет минимальным и не превысит 5 единиц.

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

Используя этот критерий, вариант решения, дающий максимальный индекс G, определенный в выражении, будет следующий:

Gi = max [a min ai j + (1 — a) max ai j]. (3.15)

Где aij — выигрыш для i-го решения при j-м сценарии,

a – коэффициент, отражающий степень оптимизма (0 ≤ a ≤ 1): при a = 0 выбирается линия поведения в расчете на лучшее, т.е. делается ориентация на предельный риск (получаем максимаксный критерий); при a = 1 делается ориентация на худшее, тогда получаем критерий Вальда — ориентир на осторожное поведение. Промежуточные значения a между 0 и 1 и выбираются в зависимости от конкретной обстановки и склонности к риску лица, принимающего решение.

Пример.3.7. Предприятие готовится к производству новых видов продукции, называемых Q1, Q2, Q3, Q4. Каждый вид продукции или комбинации продуктов связан с одним из четырех возможных решений. Структура спроса на продукцию характеризуется тремя вариантами условий S1, S2, S3. На рисунке 1 эффективность новых видов продукции aij показана для каждой пары-комбинации решений Qi (i = 1, 2, ., m) и условий Sj (j=1, 2, ., n). 3.12. Поиск наиболее выгодного решения является критическим, как и оценка влияния фактора оптимизма на выбор решения с помощью критерия Гурвица.

1. Введите исходные данные на рабочем листе Excel, рис. и установите последовательность коэффициентов k следующим образом: 0; 0.25; 0.50; 0.75; 1.00. 3.12.

2. В нижней таблице рисунка 3 представлены результаты расчета показателя G по выражению (3.13) для различных вариантов решений. 3.13.

Рисунок 3.13. Исходные данные, расчетные формулы и результаты критерия Гурвица (стрелками указаны эффективные решения).

Как видно на рисунке (ячейки B18:F18), изменение коэффициента k влияет на выбор приоритетного варианта решений.

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

— характер проблемы, которую нужно решить;

— склонность к риску тех, кто принимает решения.

Читайте так же:
Как использовать подстановочный знак vlookup (~, *,?) Буквально в Excel?

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

Максимальный поток минимальной стоимости. Решение в Excel

Так как я закончил совсем недавно железнодорожный вуз, и курс логистики имел место там быть, нахлынули на меня приятные воспоминания. Как всегда все расчёты проводились, конечно же вручную, после, пораздумав немного была написана простенькая программка, так сказать, в помощь однокурсникам…
но какого же было моё удивление, когда я узнал, что всё это, как говориться, без меня придумано, да притом и ниодин раз ))).
Речь в статье пойдёт о решении транспортной задачи средствами Microsoft Excel.
как всегда всё гениальное просто, есть такой пунк меню — Поиск решений…

Создадим в Excel’e новый проект, в нём три таблицы: потребителей, поставщиков и стоимостную
image

Далее, где-нибудь рядом, создайте аналогичные по размероности 3 таблицы, где таблица А будет пустая, элементы таблицы Б будут равняться сумме соостветствующих строк таблицы А (отK до O), таблица В аналогично таблице Б, только со столбцами таблицы А
image

Далее будет посложнее, нам нужно создать таблицу Г(это результирующая таблица, которая отразит оптимальные затраты на перевозки), каждый элемент которой будет равняться произведению соответствующего элемента стоимостной таблицы и таблицы А
image

Ну, думаю, до сих пор было всё нитак уж и сложно…
Теперь, для дальнейших оптимизационных расчётов, нам необходима целевая, так сказхать, функция.
Для этого выбираем пустой место на Листе, и пишем туда формулу:
image

Внутри фунции «сумма произведений» у нас находяться адреса наших, уже полюьившихся, двух таблиц: стоимостная и таблица А.
Ну вот, все подготовительные моменты пройдены, теперь осталось дело за excel’ем…
Я пользуюсь 2007 версией, хотя разница только в месте нахождения ключевых элементов… и так, идём в ленту Данные и находим там вот
такой вот пунктик image, скорее всего его там не будет )))), нужно просто его туда добавить. Находится он в наДстройках, от туда и вытащите его на панель.
Запустив «поиск решений» мы увидем следующее окно
image

, где в качестве целевой ячейки указываем нашу ячейку с целевой функцией; далее нужно поразмыслить над целью задачи. Моя цель — получение максимальной прибыли, следовательно я выбираю максимальное значение (в этом пункте всё понятно); в качестве изменяемых ячеек устанавливаем нашу таблицу А.
Теперь устанавливаем ограничения. Из рисунка видно, что у нас их 3. Добавляются ограничения элементарно
image

Читайте так же:
Как импортировать текстовые файлы в Excel в виде отдельных листов с оригинальными именами в Excel?

Ограничения:
1) таблица поставщики = таблице Б
2) таблица потребители = таблице В
3) мы говорим, что элементы нашей таблицы А обязательно должны быть целыми
Добавив 3 ограничения, нажимаем кнопку параметры
image

Сдесь важно отметить галкой пункт линейная модель и неотрицательные значения.
ВСЁ!
Жмём Ок, выполнить…
и перед нами результат-решение нашей транспортной задачи в Excel
image
upd. Как добавить меню поиск решений

Как пользоваться функцией МИН в Excel

Как пользоваться функцией МИН в Excel

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

Объявление и написание функции МИН

Начать стоит с самого важного – синтаксиса функции, поскольку при неправильной записи на экране просто появится ошибка. МИН(MIN) состоит всего из одного аргумента, поэтому запутаться будет тяжело. Задача этой функции – возвращение наименьшего значения из указанного перечня данных. То есть вы выбираете несколько ячеек, а функция покажет, какое число в них минимальное. Кстати, точно так же работает и МАКС, поэтому сегодняшнее руководство можете применить при вычислении максимального значения, объявив подходящую функцию.

Просмотр синтаксиса функции МИН в Excel

Выше вы видите скриншот с окном «Аргументы функции», которое можно использовать для создания функции. В поле «Число» для МИН вы вписываете тот самый диапазон, который и нужно обработать. Если таких диапазонов несколько, запишите второй в «Число2», после чего на экране появится поле «Число3», и так может продолжаться долго, пока вы не впишите все требуемые диапазоны. Если хотите написать функцию в ячейке самостоятельно, используйте для этого =МИН(), а в скобках указывайте диапазоны чисел, отделяя их знаком «;», как об этом сказано в подсказке от разработчиков.

Запись функции МИН в Excel

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

Выбор диапазона данных для функции МИН в Excel

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

Читайте так же:
Как запустить макрос на основе значения ячейки в Excel?

Просмотр результата возвращения числа функции МИН в Excel

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

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

Математические операции и функция МИН

Кратко расскажу о том, как вы можете использовать функцию МИН, выполняя различные математические расчеты. Самый простой пример – сложение двух диапазонов данных. Вам нужно объявить несколько функций МИН в одной ячейке, поставив между ними «+», в результате чего два минимальных значения сложатся, а на экране отобразится их сумма. Конечно, вместо плюса вы можете подставить любой знак, а в качестве второй функции при расчетах необязательно использовать МИН.

Математические операции с функцией МИН в Excel

Запись МИН в других функциях

Некоторые пользователи, только начинающие свой путь в Microsoft Excel, не знают, что в рамках одной функции может находиться и другая. Конечно, непосредственно в МИН нельзя засунуть еще какое-то выражение, поскольку функция применяет только аргумент, а вот саму МИН можно поместить в другую функцию. Например, объявите логическую операцию =ЕСЛИ(МИН(A1:A10)). Вы видите, что первое выражение для ЕСЛИ – это возвращение минимального значения.

Вставка функции МИН в ЕСЛИ в Excel

Теперь нужно решить, какой знак поставить, а затем записать значения лжи и истины. Полностью формула выглядит следующим образом: =ЕСЛИ(МИН(A1:A10)>X;”+”;”-”). То есть если МИН будет больше указанного числа, значение ЕСЛИ станет «+», в противном случае «-». Вместо X можно подставить любую другую функцию, если в этом есть надобность.

Запись аргументов для ЕСЛИ с применением функции МИН в Excel

Если описание выше вам показалось сложным, объявите ЕСЛИ самостоятельно и откройте окно «Аргументы функции». В нем вы увидите разделение на поля и поймете, что в логическом выражении нужно написать саму функцию МИН и число либо другую функцию после знака «больше», «меньше» или «равно». Дополнительно сами разработчики представляют подсказки, поэтому понять принцип действий будет нетрудно.

Просмотр окна Аргументы функции при записи ЕСЛИ в Excel

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector