Light-electric.com

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

Поиск решения онлайн excel

Поиск решения в Excel

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

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

Еще есть ограничение бюджет рекламных расходов в 40 тыс. долл. за 4 месяца.

Итак, приступим к технической части.

  1. Если вы никогда не пользовались этой надстройкой, придется ее сначала установить. Дело в том, что по умолчанию Поиск решения не ставится. Заходим Офис/Параметры Excel/Надстройки/Кнопка «Перейти». Мы уже заходили сюда, когда делали сумму прописью .
  2. Теперь, у вас на вкладке Данные появилась команда «Поиск решения». Нажимаем и видим такое окошко:
  3. Целевая ячейка — это там, которую мы хотим максимизировать, это результат. У нас это B15. Т.е. я хочу увидеть, какими должны быть расходы на рекламу, чтобы в январе у меня была максимальная прибыль. Ставим выбор «максимальному значению».
  4. Изменяя ячейки — ставим диапазон ячеек, от которых зависит итог. У меня это все расходы на рекламу, т.е. диапазон B11 — E11.
  5. Ограничения — ну без них никак. Excel мыслит больше математически, поэтому нам надо:
  • Поставить условия положительности изменяемых ячеек. B11:E11 > 0
  • Ограничить рекламный бюджет за 4 месяца. F11=40000
  1. Нажимаем на кнопку «Выполнить». Если что-то пошло не так, а это бывает, обнулите диапазон изменяемых ячеек, может помочь.

В итоге мы получим нужный результат

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

Видео по теме (5 минут):

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

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

Предположим, у вас есть задача: оптимизировать расходы на производство 1 000 изделий. На это есть 30 дней и четыре работника, для которых известна производительность и оплата за изделие.

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

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

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

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

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

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

Читать еще:  Что такое автозаполнение в excel

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

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

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

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

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


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

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

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

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

  • общее количество изделий 1000 штук ($D$13 = $D$3);
  • число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
  • количество дней меньше либо равно 30 ($F$9:$F$12 > окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

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

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

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Как пользоваться функциями подбора параметра и поиска решения в Excel

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

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

Видео пример поиска решения в Excel

Функция «Подбор параметра»

Подбор параметра в Excel позволяет подобрать какой-то определенный параметр, значение которого неизвестно. Чтобы было понятней, можно привести такой пример. Допустим, есть прямоугольник со сторонами A и B. Известно, что общая площадь этой фигуры составляет 400 квадратных метров, а сторона B — 40 метров. Сторона A неизвестна и, соответственно, нужно ее найти. Для решения такой задачи необходимо заполнить рабочий лист программы теми данными, которые уже известны. Для этого нужно создать таблицу с 2 колонками и 3 строками (диапазон ячеек A1:B3).

Первый столбец будет содержать название сторон прямоугольника и букву, обозначающую его площадь (т.е. A, B и S). А во втором столбце необходимо указать известные значения:

  • в соседней ячейке для стороны B (ячейка B2) написать — 40 (значение для стороны А остается пустым);
  • а в соседнем поле для площади прямоугольника (поле B3) написать следующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было сделано правильно, то в поле B3 должно быть значение 0. Затем надо выделить эту ячейку и выбрать в панели меню пункты: «Сервис — Подбор параметра». В появившемся окне нужно указать то значение, которое должно быть получено в результате, т.е. 400. В строке «Установить в ячейке» будет указано поле «B3»: менять его не нужно, так и должно быть (сюда будет выведен результат). А в строке «Изменяя значение» необходимо выбрать неизвестный параметр, т.е. поле B1. После нажатия кнопки «ОК» программа выдаст результат: сторона А — 10 метров, а в поле общей площади прямоугольника будет указано число 400.

Это была очень простая задача на уровне 3 класса, но с помощью такой функции можно решать и более сложные задачи. Например, вы решили приобрести себе автомобиль в кредит. Вы точно знаете, что сможете выплачивать ежемесячную выплату в размере 1000 $ (но не больше), а также, что банк выдает автокредит с процентной ставкой 6,5%. Суть задачи заключается в следующем: «Какова максимальная сумма машины, которую можно взять в кредит на таких условиях?». То есть теперь программа будет искать стоимость автомобиля, отталкиваясь от того, что ежемесячный платеж не должен превышать 1000 $. Такой пример является уже более сложным, а также более практичным, нежели расчет площади прямоугольника.

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

Параметры инструмента поиск решения

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

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

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

Процедура поиска оптимального значения в Excel осуществляется не так уж и просто, но принцип примерно тот же, что и в первом случае: пользователь указывает диапазон изменяемых ячеек и поле, куда выводить итоговый результат, а все остальное программа сделает сама. Вот такими способами осуществляется решение задач оптимизации в табличном процессоре Microsoft 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

Поиск решения онлайн excel

Pers.narod.ru. Обучение. Excel. Поиск решения в Excel и примеры для него

Я настолько давно и прочно привык делать всё, что можно сделать в Excel, «Поиском решения», что удивляюсь, когда кто-то так не делает. Поэтому добавлю несколько слов об этом полезном инструменте и приведу какой-нибудь пример.

В общем виде задача оптимизации ставится следующим образом: найти максимальное, минимальное или фиксированное значение функции f(x1,x2,…,xn) при заданной системе ограничений ai ≤ xi ≤ bi , i=1,2,…,n , где xi – неизвестные величины. В реальных задачах, как правило, значения ограничений ai, bi выбираются из физических или математических соображений.

Например, решение любого уравнения — частный случай этой модели:

(хотя для решения уравнений есть отдельный инструмент Подбор параметра).

Любая задача об условном экстремуме функции тоже решается тем же методом.

Задачи, изучаемые в математическом программировании, тоже решаются «Поиском решения».

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

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

где Знак может принимать значения , = , >= или ограничение до целого числа, если задача целочисленна.

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

Примечание. Если указанный пункт меню отсутствует, включите соответствующую надстройку в Excel: меню Файл, команда Параметры Excel, вкладка Надстройки, в списке Управление выбрать пункт «Надстройки Excel» и нажать кнопку «Перейти», затем включить надстройку (или меню Сервис, Надстройки в старых версиях Excel).

Пример 1. Фабрика может выпускать продукцию 2 типов — по P1 и P2 рублей за единицу. Для выпуска продукции выделено L единиц ресурсов и T человеко-часов. Известно, что на выпуск единицы продукции 1-го и 2-го вида уходит по ZL1 и ZL2 единиц ресурсов и ZT1 и ZT2 человеко-часов соответственно. Сколько нужно произвести продукции первого и второго видов, чтобы ее общая стоимость была максимальна? Данные взять из приведенной ниже таблицы.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×