В этой статье обсуждается использование надстройки Microsoft Excel “Решение”, которая позволяет анализировать “что если” для определения оптимального сочетания продуктов.
Как определить ежемесячный набор продуктов, который позволяет повысить прибыльность?
Компаниям часто требуется определять количество каждого продукта, который будет создаваться ежемесячно. В простейшей форме проблема в наборе продуктов состоит в том, как определить объем каждого продукта, который должен быть произведен в течение месяца, чтобы максимально увеличить прибыль. Сочетание продуктов обычно должно соответствовать следующим ограничениям:
-
Сочетание продуктов не может использовать больше ресурсов, чем доступно.
-
Потребность в каждом продукте ограничена. В течение месяца мы не можем создать больше продукции, чем это диктует потребность в продукции, так как в нее нагребают лишние продукты (например, избежаемый продукт).
Теперь рассмотрим пример проблемы со сочетанием продуктов. Решение этой проблемы можно найти в файле Prodmix.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 “Решение”, мы могли бы решить эту проблему, построив на этом листах данные о прибылях и использовании ресурсов, связанных с этим сочетанием продуктов. Затем мы использовали пробные и ошибки, чтобы оптимизировать прибыль, не используя при этом больше ресурсов и необработанных материалов, чем доступно, и не изменяя при этом лишние продукты. Над решением этой процедуры мы используем только пробную стадию с ошибкой. По сути, “Поиск решения” — это механизм оптимизации, который безукоризненно выполняет поиск по пробным версиям и ошибкам.
Ключ к решению этой проблемы — эффективное вычисление использования ресурсов и прибыли, связанных с любым сочетанием продуктов. Для этого важно использовать функцию СУММПРОИDUCT. Функция СУММПРОИCT перемножает соответствующие значения в диапазонах ячеев и возвращает сумму этих значений. Каждый диапазон ячеок, используемый в оценке СУММПРОИДУCT, должен иметь одинаковые размеры, что подразумевает, что можно использовать суммпроидуц с двумя строками или двумя столбцами, но не с одной строкой и столбцом.
В качестве примера использования функции СУММПРОИПР в нашем примере мы постараемся вычислить использование ресурсов. Трудоемкие труды вычисляются по
(Количество трудовых единиц, использованных для одного фунта воды в секунду)*(1 фунт 1, произведено)+
(Трудоемка, используемая для одного фунта валюты 2)*(2 фунта, произведенного) + …
(Трудоемка, используемая для одного фунта валюты 6)*(6 фунтов в секунду)
Мы могли бы более утомительным образом вычислять использование труда, как D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Кроме того, использование необработанных материалов можно вычислять как D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Однако ввод этих формул на таблицу для шести продуктов отнимает много времени. Представьте, сколько времени займет работа с компанией, которая производит, например, 50 продуктов на своем заводе. Гораздо проще вычислять трудоемкие и необработанные данные, скопируя из D14 в D15 формулу СУММПРОИДУCT($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)
-
Ограничения. В этом примере есть следующие ограничения:
-
Не используйте больше трудовых или необработанных материалов, чем доступно. То есть значения в ячейках 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<=F14 (трудоемка меньше или равна доступной работе), так и D15<=F15 (используемый необработаный материал меньше или равен доступному неоцененному). Нажмите кнопку “Добавить”, чтобы ввести ограничения по запросу. Заполните поле в диалоговом окне “Добавить ограничение”, как показано на рисунке 27–6.
Добавление этих ограничений гарантирует, что при попытках “Найти решение” для изменяемого значения ячейки будут учитываться только сочетания, которые удовлетворяют следующим параметрам:
-
D2<=D8 (количество произведенного средства 1 не превышает потребность в этом средстве)
-
E2<=E8 (количество произведенного средства 2 не превышает или равно запросу на доступ к более 2).
-
F2<=F8 (количество произведенного средства 3 не превышает потребность в этом средстве)
-
G2<=G8 (количество произведенного средства 4 не превышает или равно запросу на доступ к наркотиками 4)
-
H2<=H8 (количество произведенного средства 5 не превышает или равно запросу на доступ к более 5).
-
I2<=I8 (количество произведенного средства 6 не превышает потребность в этом средстве)
В диалоговом окне “Добавить ограничение” нажмите кнопку “ОК”. Окно “Решение” должно выглядеть так: “Рисунок 27-7”.
В диалоговом окне “Параметры решения” введите ограничение на то, что изменяющиеся ячейки не должны быть отрицательными. Нажмите кнопку “Параметры” в диалоговом окне “Параметры решения”. Проверьте окне “Предполагаемая линейная модель” и “Нео отрицательная”, как показано на рисунке 27–8 на следующей странице. Нажмите кнопку “ОК”.
При проверке значения “Предположим, неохожим” над решением будут учитываться только сочетания изменяющихся ячеек, в которых каждая из изменяющихся ячеек принимает нео отрицательное значение. Мы проверили поле “Предполагаемая линейная модель”, так как проблема с сочетанием продуктов — это особый тип решения, называемый линейной моделью. По сути, модель “Поиск решения” является линейной в следующих условиях:
-
Целевая ячейка вычисляется путем с совокупности терминов формы (изменяемой ячейки)*(константа).
-
Каждое ограничение соответствует “требованиям к линейной модели”. Это означает, что каждое ограничение вычисляется путем с совокупности терминов формы (изменяемой ячейки)*(константа) и сравнения сумм с константой.
Почему эта проблема “Поиск решения” линейный? Целевая ячейка (прибыль) вычисляется как
(Доход от 1 дохода на фунт)*(1 фунт произведен) +
(Доход от 2 дохода на фунт)*(2 фунта произведено) + …
(6 доход на фунт)*(произведено 6 фунтов)
Эта вычисление вычисляется по шаблону, в котором вычисляется значение конечной ячейки путем с суммирования терминов формы (изменяемой ячейки)*(константа).
Наше ограничение на работу вычисляется путем сравнения значения, полученного из (Количество, используемого в фунте по 1)*(1 фунта влияния) + (Количество, используемого для одного фунта в фунте до 2)*(Фунт 2, полученный в качестве 2 фунта)+ (Трудоемкие мыed per pound of Drug 6)*(Pound 6 pound produced) to the labor available.
Следовательно, ограничение трудоемких ресурсов вычисляется путем с совокупности терминов формы (изменяемой ячейки)*(константа) и сравнения сумм с константой. Ограничение трудоемких ресурсов и ограничение в необработанных материалах отвечают требованиям к линейной модели.
Наши ограничения запроса принимают форму
(От 1 досье 1)<=(Потребность в наркотиками 1)
(От 2 досье 2)<=(Потребность в наркотиками 2)
§
(от 6 досье 6)<=(Потребность в наркотиками 6)
Каждое ограничение запроса также соответствует требованию к линейной модели, поскольку каждое из них вычисляется путем свести вместе условия формы (изменяемая ячейка)*(константа) и сравнить суммы с константой.
Если вы показываете, что модель нашего продукта является линейной, почему это важно?
-
Если модель “Поиск решения” является линейной и выбран вариант “Предположим, линейный режим”, “Поиск решения” гарантирован, что будет найдено оптимальное решение для модели “Поиск решения”. Если модель “Поиск решения” не линейный, “Поиск решения” может не найти оптимальное решение.
-
Если модель “Поиск решения” является линейной и выбран вариант “Предполагаемая линейная модель”, то для поиска оптимального решения модели используется очень эффективный алгоритм (метод простого решения). Если модель “Поиск решения” является линейной и не выбран вариант “Предполагаемая линейная модель”, “Поиск решения” использует очень неэффективный алгоритм (метод ОГР2), что может затруднить поиск оптимального решения модели.
После нажатия кнопки “ОК” в диалоговом окне “Параметры решения” вернимся в главное диалоговое окно “Решение”, показанное ранее на рисунке 27–7. При нажатии кнопки “Поиск решения” “Поиск решения” вычисляет оптимальное решение (если оно существует) для модели микса продуктов. Как было сказано в главе 26, оптимальным решением для модели набора продуктов является набор изменяемых значений ячеок (фунта, произведенного каждым продуктом), который позволяет максимально увеличить прибыль в наборе всех возможных решений. В этом же, целесообразное решение — это набор изменяющихся значений ячеок, удовлетворяющий всем ограничениям. Изменяющиеся значения ячеок, показанные на рисунке 27–9, являются допустимым решением, поскольку все производственные уровни неоценимы, производственные уровни не превышают потребность, а использование ресурсов не превышает доступных ресурсов.
Изменяемые значения ячеек, показанные на рисунке 27–10 на следующей странице, являются неизменяемым решением по следующим причинам:
-
Мы выпускаем больше 5, чем за него требуются.
-
Мы используем больше трудоемких ресурсов, чем доступно.
-
Мы используем больше необработанных материалов, чем доступно.
Нажав кнопку “Поиск решения”, “Поиск решения” быстро найдет оптимальное решение, показанное на рисунке 27–11. Вам нужно выбрать “Сохранить решение для решения проблемы”, чтобы сохранить оптимальные значения решения на работе.
Наша организация, которая занимается наркотиками, может увеличить ежемесячную прибыль в размере 6 625,20 долларов США, выполив 596,67 фунта 4, 1084 фунта для подавлили 5 рублей и ни одного другого фунта! Мы не можем определить, можно ли достичь максимальной прибыли в 6 625,20 долларов США другими способами. Все, что мы можем быть уверены, что из-за ограниченных ресурсов и требований в этом месяце нельзя внести больше 6 627,20 долларов США.
Предположим, что потребность в каждом продукте должна быть выполнены. (См. таблицу “Нет реального решения” в Prodmix.xlsx.) Затем нам нужно изменить ограничения по запросу с D2:I2<=D8:I8 на D2:I2>=D8:I8. Для этого откройте “Решение”, выберите ограничение D2:I2<=D8:I8 и нажмите кнопку “Изменить”. Появится диалоговое окно “Изменение ограничения”, показанное на рисунке 27–12.
Выберите >=, а затем нажмите кнопку “ОК”. Теперь над решением можно изменить только значения ячеок, которые соответствуют всем запросам. При нажатии кнопки “Найти решение” отобразилось сообщение “Поиск решения не удалось найти целесообразное решение”. Это сообщение не означает, что мы допустили ошибку в модели, а о том, что из-за ограниченных ресурсов мы не сможем выполнить потребность во всех товарах. Надстройка “Решение” просто сообщает нам, что если мы хотим удовлетворить потребность в каждом продукте, нам нужно добавить больше труда, дополнительных необработанных материалов или и тех, и других.
Давайте посмотрим, что произойдет, если разрешить неограниченную потребность в каждом продукте и разрешить отрицательные количества каждого товара. (Эта проблема возникает в области “Набор значений не сходится” на Prodmix.xlsx.) Чтобы найти оптимальное решение в этой ситуации, откройте “Поиск решения”, нажмите кнопку “Параметры” и откроем поле “Неохритимые”. В диалоговом окне “Параметры решения” выберите ограничение запроса D2:I2<=D8:I8, а затем нажмите кнопку “Удалить”, чтобы удалить это ограничение. При нажатии кнопки “Найти решение” возвращается сообщение “Установить значение ячейки не сходится”. Это сообщение означает, что если нужно развернуть целевую ячейку (как в нашем примере), существуют допустимые решения, в том числе и для суммарных значений целевых ячеок. (Если целевая ячейка должна быть свернута, сообщение “Установить значения ячейки не сходятся” означает, что существуют возможные решения с небольших целевых значений, заверяемого в третейском деле.) В нашем случае, разрешив негативное производство в случае злоупотреблений, мы фактически создадим ресурсы, которые можно использовать для вывода в произвольное количество других средств. С учетом нашего неограниченного запроса это позволяет нам получать неограниченную прибыль. В реальной ситуации мы не можем заработать бесконечно. Если вы видите сообщение “Установить значения не сходятся”, это означает, что в модели есть ошибка.
-
Предположим, в нашей компании в течение часа можно приобретать до 500 часов работы на 100 рублей больше, чем за текущие трудоемкие расходы. Как максимально увеличить прибыль?
-
На изготовителе микросхем четыре технических специалиста (A, B, C и D) выпускают три продукта (“Товары 1”, “2” и “3”). В этом месяце изготовитель микросхемы может продать 80 единиц продукта 1, 50 единиц продукта 2 и не более 50 единиц продукта 3. Специалист А может делать только продукты 1 и 3. Специалист Б может делать только продукты 1 и 2. Специалист C может сделать только продукт 3. Специалист D может сделать только продукт 2. Для каждого произведенного товара внести следующую прибыль: Товар 1; 600 рублей; Товар 2; 7000 рублей; и товар 3, 1000 рублей. Время (в часах) каждого технических специалиста, необходимое для производства продукта, должно быть следующим:
Продукт
Специалист А
Специалист Б
Специалист C
Специалист Д
1
2
2,5
Не удается сделать
Не удается сделать
2
Не удается сделать
3
Не удается сделать
3,5
3
3
Не удается сделать
4
Не удается сделать
-
Каждый специалист может работать до 120 часов в месяц. Как изготовитель микросхем может максимально увеличить ежемесячную прибыль? Предположим, что можно получить лишь дробное количество единиц.
-
Компьютерный завод производит мыши, клавиатуры и игровые joysticks. Доход за единицу, за единицу трудоемких ресурсов, ежемесячная потребность и за единицу машинного времени даются в следующей таблице:
Мыши
Клавиатуры
Joysticks
Прибыль/единица
8 $
11 $
9 $
Использование трудов и единицы
0,2 часа
0,3 часа
0,24 часа
Машинное время/единица
0,04 ч
0,055 ч.
0,04 ч
Ежемесячный запрос
15 000
27,000
11,000
-
Каждый месяц доступно 13 000 часов труда и 3000 часов машинного времени. Как изготовитель может максимально увеличить ежемесячный взнос в прибыль от растения?
-
Допустим, необходимо устранить проблему с этой проблемой при условии, что должны быть выполнены минимальные требования к 200 единицам для каждого из них.
-
Он делает ромбовидные ромбои, окаймлы и затейные. Он хочет работать не более 160 часов в месяц. У него 800 ромбов. Ниже дается прибыль, время труда и количество ромбов, необходимых для получения каждого товара. Если потребность в каждом продукте не ограничена, как Максим может увеличить прибыль?
Продукт
Прибыль за единицу
Трудоемкие часы на единицу
Ромбы на единицу
Лексема
300р.
.35
1,2
Кулигов
200 ₽
.15
.75
Кулигов
100р.
0,05
.5
Наряду со множеством других возможностей, в Microsoft Excel есть одна малоизвестная, но очень полезная функция под названием “Поиск решения”. Несмотря на то, что найти и освоить ее, может быть, непросто, ее изучение и применение может помочь в решении огромного количества задач. Функция берет данные, перебирает их и выдает самое оптимальное решение из возможных. Итак, давайте разберемся, как именно работает поиск решения и попробуем применить данную функцию на практике
Содержание
- Как включить функцию “Поиск решения”
- Подготовительный этап
- Применение функции и ее настройка
- Заключение
Как включить функцию “Поиск решения”
Несмотря на свою эффективность, функция “Поиск решения” не находится в первых рядах панели инструментов или контекстного меню. Многие пользователи, работающие в Excel годами, даже не подозревают о ее существовании. Дело в том, что по умолчанию она вообще отключена и для ее добавления на ленту нужно проделать следующие шаги:
- Открываем меню “Файл”, кликнув по соответствующему названию.
- Кликаем по разделу “Параметры”, который находится внизу вертикального перечня с левой стороны.
- Далее щелкаем по подразделу “Надстройки”. Здесь отображаются все надстройки программы, а внизу будет надпись “Управление”. Справа от нее представлено выпадающее меню, в котором должны быть выбраны “Надстройки Excel”, обычно уже установленные по умолчанию. Нажимаем кнопку “Перейти”.
- На экране появится новое вспомогательное окно “Надстройки”. Устанавливаем флажок напротив опции “Поиск решения” и нажимаем ОК.
- Все готово. Требуемая функция появится на ленте в правой части вкладки “Данные”.
Подготовительный этап
Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.
Итак, у нас есть данные про продаже товаров, представленные в табличном виде.
И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.
Наша задача – вычислить скидку, на которую будут умножены все суммы по продажам всех наименований. Она и будет найдена с помощью функции “Поиск решения”, а ячейка с этой скидкой будет называется искомой.
Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.
Применение функции и ее настройка
Формула готова. Теперь нужно применить саму функцию.
- Переключаемся во вкладку “Данные” и нажимаем кнопку “Поиск решения”.
- Откроются “Параметры”, где необходимо задать нужные настройки. В поле “Оптимизировать целевую функцию:” указываем адрес целевой ячейки, где планируется вывести сумму по всем скидкам. Можно прописать координаты вручную, либо выбрать из таблицы, для чего сначала кликаем по области ввода, затем – по нужной ячейке.
- Переходим к настройке других параметров. В пункте “До:” можно задать максимальную границу, минимальную границу или же точное число. Исходя из поставленной задачи ставим отметку рядом с опцией “Значение” и набираем “4500000” – сумма скидок по всем наименованиям.
- Следующее для заполнения поле – “Изменяя значения переменных:”. В него нужно внести координаты искомой ячейки, содержащей определенное значение. Это значение и есть та самая скидка, которую мы пытаемся вычислить. Также, как и с выбором целевой ячейки, координаты можно написать вручную, либо кликнуть по нужной ячейке в самой таблице.
- Теперь нужно отредактировать раздел “В соответствии с ограничениями:”, в котором задаем ограничения используемых данных. Например, можно исключить десятичные дроби или, скажем, отрицательные числа. Это делается через кнопку “Добавить”.
- Откроется вспомогательно окно, позволяющее добавить ограничения во время вычислений. В первом поле указываем координаты определенной ячейки или области ячеек, для которых это условие должно действовать. Согласно нашей задаче, указываем координаты искомой ячейки, в которой будет выводиться значение скидки. Следующий шаг – определить знак сравнения. Устанавливаем “больше или равно”, чтобы итоговое число не могло быть отрицательным. “Ограничение”, которое устанавливается в третьем поле, в этом случае будет равно цифре 0, поскольку именно относительно этого значения задается условие.
Можно установить еще одно ограничение с помощью кнопки “Добавить”. Дальнейшие действия по его настройке будут аналогичными. По готовности щелкаем OK. - После выполнения описанных выше действий в самом большом поле окна появится установленное только что ограничение. Список может быть довольно большим и зависит от сложности предполагаемых расчетов, но в данном случае будет достаточно и одного условия.
Под этим полем также есть опция, позволяющая делать все остальные переменные, не затрагиваемые ограничениями, неотрицательными. Однако, будьте внимательны и проследите за тем, чтобы между этим параметром и поставленными ограничениями не было противоречия, иначе при расчете в программе может возникнуть конфликт. - Также можно задать немалое количество дополнительных настроек. Чуть ниже справа есть кнопка “Параметры”, позволяющая это сделать. Нажимаем на нее и открываем новое окно.
- В этих настройках у нас есть возможность установить “Точность ограничения” и “Пределы решения”. В нашем случае задавать данные параметры нет необходимости, поэтому после ознакомления с представленным окном, его можно закрыть, нажав OK.
- Итак, все настройки выполнены и параметры установлены. Пора запускать функцию – для этого нажимаем кнопку “Найти решение”.
- После этого программа сделает все необходимые расчеты и выдаст результаты в нужных ячейках. При этом сразу же откроется окно “Результаты поиска решения”, где можно сохранить/отменить результаты или настроить параметры поиска заново. Если результаты нас устраивают, оставляем отметку напротив опции “Сохранить найденное решение” и нажимаем ОК. При этом, если мы предварительно установим галочку слева от надписи “Вернуться в диалоговое окно параметров поиска решения”, после того, как мы щелкнем OK, мы обратно переключимся к настройке функции поиска решения.
- Вполне вероятно, что расчеты могут показаться неправильными, либо возникнет желание немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно посмотреть поля с введенными данными.
- Если с данными все нормально, можно попробовать задействовать другой метод решения. Для этого щелкаем по текущему варианту и из раскрывшегося перечня выбираем способ, который нам кажется наиболее подходящим:
- Первый – ищет решение методом обобщенного приведенного градиента (ОПГ) для нелинейных задач. Стандартно выбран именно этот вариант, но можно попробовать и другие.
- Второй – пытается отыскать решение для линейных задач, используя симплекс-метод.
- Третий – для выполнения поставленной задачи использует эволюционный поиск.
- В том случае, если ни один из методов не принес удовлетворительных результатов, стоит проверить данные в таблице и параметрах еще раз, поскольку именно это является самой частой ошибкой в подобного рода задачах.
- Теперь, когда мы получили требуемую скидку, осталось ее применить, чтобы рассчитать суммы скидок по всем наименованиям. Для этого отмечаем первую ячейку столбца “Сумма скидки”, пишем в ней формулу “=D2*$G$2” и нажимаем Enter. Знаки доллара ставятся для того, чтобы при растягивании/копировании формулы на другие строки, ячейка G2 со скидкой оставалась неизменной в расчетах.
- Мы получили сумму скидки для первого наименования. Теперь наводим курсор на нижний правый угол ячейки с результатом, как только он поменяет форму на крестик, зажав левую кнопку мыши растягиваем формулу на все строки, по которым хотим посчитать аналогичную сумму.
- Теперь наша таблица полностью готова в соответствии с поставленной задачей.
Заключение
Таким образом, функция “Поиск решения” в Эксель может помочь в решении определенных задач, которые достаточно сложно или невозможно решить простыми методами. Однако, проблема в использовании данного способа заключается в том, что по умолчанию данная функция скрыта в программе, из-за чего многие пользователи не догадываются о ее существовании. Также функция довольно трудна в освоении и использовании, но при ее должном изучении, она может принести значительную пользу и облегчить работу.
Существует
множество задач, решение которых может
быть существенно облегченно с помощью
инструмента Поиск
решений.
Но для этого следует начать с организации
рабочего листа в соответствии с пригодной
для поиска решений моделью, для чего
нужно хорошо понимать взаимосвязи между
переменными и формулами. Хотя постановка
задачи обычно представляет основную
сложность, время и усилия, затраченные
на подготовку модели, вполне оправданы,
поскольку полученные результаты могут
уберечь от излишней траты ресурсов, при
неправильном планирование, помогут
увеличить прибыль за счет оптимального
управление финансами или выявить
наилучшее соотношение объемов
производства, запасов и наименований
продукции.
За
своей сущностью задача
оптимизации –
это математическая модель определенного
процесса производства продукции, его
распределение, хранение, переработки,
транспортирования, покупки или продажи,
выполнение комплекса сервисных услуг
и т.д. Это обычная математическая задача
типа: Дано/Найти/При условии, но
которая имеет множество возможных
решений. Таким образом, задача оптимизации
– задача выбора з множества возможных
вариантов наилучшего, оптимального.
Решение
такой задачи называют планом или программой,
например, говорят – план производства
или программа реконструкции. Другими
словами это те неизвестные которые нам
надо найти, например, количество
продукции которое даст максимальную
прибыль. Задача оптимизации – поиск
экстремума, то есть, максимального или
минимального значения определенной
функции, которую называют целевой
функцией,
например, это может быть функция прибыли
– выручка минус затраты. Так как и всё
в мире ограничено (время, деньги, природные
и человеческие ресурсы), в задачах
оптимизации всегда есть
определенные ограничения,
например, количество метала, рабочих и
станков на предприятии по изготовлению
деталей.
Далее рассмотрен пример
оформления очень простой задачи
оптимизации, но с помощью его можно
легко понять организации о построение
таблицы для эффективности решений
практический проблем оптимизации.
Имеем классическую
задачу когда фирма производит два вида
продукции (товар А и товар Б) по определенной
цене, на их производство требуется 4
вида ресурсов (ресурс 1, ресурс 2, ресурс
3, ресурс 4), которые есть в наличие на
фирме в определенном количестве (Запас),
также имеется информация сколько нужно
каждого ресурса на производство единицы
продукции, соответственно товара А и
товара Б. Нужно найти, то количество
товара А и товара Б, которое
максимизирует доход (выручку) (см. рис.).
Далее
нам надо сделать взаимосвязи между
ограничениями, планом и целевой функцией.
Для этого мы строим дополнительный
столбец (Использовано), в котором вводим
формулуСУММПРОИЗВ(Норма;
План). Норма – это затраты определенного
ресурса на производство единицы продукции
товара А и Б, а План – количество
продукции, которое мы ищем. В ячейки
Доход вводим формулу СУММПРОИЗВ(Цена;
План). Таким образом мы заполнили
формулами столбец Использовано и ячейку
Доход. Так как план это переменные от
которых зависит количество использованных
ресурсов и доход, то ячейки с формулами
напрямую зависят от данных, которые там
появятся в результате поиска решений.
С
выше сказанного можно сделать следующие
выводы, что каждая задача оптимизации
обязательно должна иметь три компоненты:
-
неизвестные (что
ищем, то есть, план); -
ограничение на
неизвестные (область поиска); -
целевая
функция (цель,
для которой ищем экстремум).
Мощным
средством анализа данных Excel является
надстройка Solver
(Поиск решения).
С ее помощью можно определить, при каких
значениях указанных влияющих ячеек
формула в целевой ячейке принимает
нужное значение (минимальное, максимальное
или равное какой-либо величине). Для
процедуры поиска решения можно задать
ограничения, причем не обязательно,
чтобы при этом использовались те же
влияющие ячейки. Для расчета заданного
значения применяются различные
математические методы поиска. Вы можете
установить режим, в котором полученные
значения переменных автоматически
заносятся в таблицу. Кроме того, результаты
работы программы могут быть оформлены
в виде отчета.
Программа Поиск решений
(в оригинале Excel Solver) – дополнительная
надстройка табличного процессора MS
Excel, которая предназначена для решения
определенных систем уравнений, линейных
та нелинейных задач оптимизации,
используется с 1991 года.
Размер задачи,
которую можно решить с помощью базовой
версии этой программы, ограничивается
такими предельными показателями:
-
количество
неизвестных (decision variable) – 200; -
количество
формульных ограничений (explicit constraint) на
неизвестные – 100; -
количество
предельных условий (simple constraint) на
неизвестные – 400.
Разработчик
программы Solver компания Frontline System уже
давно специализируется на разработке
мощных и удобных способов оптимизации,
встроенных в среду популярных табличных
процессоров разнообразных фирм-производителей
(MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).
Высокая
эффективность их применения объясняется
интеграциею программы оптимизации и
табличного бизнес-документа. Благодаря
мировой популярности табличного
процессора MS Excel встроенная в его
среду программа Solver есть наиболее
распространенным инструментом для
поиска оптимальных решений в сфере
современного бизнеса.
По
умолчанию в Excel надстройка Поиск решения
отключена. Чтобы активизировать ее
в Excel
2007,
щелкните значок Кнопка
Microsoft Office ,
щелкните Параметры
Excel,
а затем выберите категорию Надстройки.
В поле Управлениевыберите
значение Надстройки
Excel и
нажмите кнопку Перейти.
В поле Доступные
надстройки установите
флажок рядом с пунктом Поиск
решения и
нажмите кнопку ОК.
В Excel
2003 и
ниже выберите команду Сервис/Надстройки,
в появившемся диалоговом окне Надстройки
установите флажок Поиск
решения и
щелкните на кнопке ОК. Если вслед за
этим на экране появится диалоговое окно
с предложением подтвердить ваши
намерения, щелкните на кнопке Да.
(Возможно, вам понадобится установочный
компакт-диск Office).
Процедура
поиска решения
1. Создайте
таблицу с
формулами, которые устанавливают связи
между ячейками.
2.
Выделите целевую ячейку, которая должна
принять необходимое значение, и выберите
команду:
– В Excel
2007 Данные/Анализ/Поиск
решения;
–
В Excel
2003 и
ниже Tools >Solver
(Сервис > Поиск решения). Поле Set Target
Cell (Установить целевую ячейку) открывшегося
диалогового окна надстройки Solver (Поиск
решения) будет содержать адрес целевой
ячейки.
3. Установите переключатели
Equal To (Равной), задающие значение целевой
ячейки, — Мах (максимальному значению),
Min (минимальному значению) или Value of
(значению). В последнем случае введите
значение в поле справа.
4. Укажите в
поле By Changing Cells (Изменяя ячейки), в каких
ячейках программа должна изменять
значения в поисках оптимального
результата.
5. Создайте ограничения в
списке Subject to the Constraints (Ограничения). Для
этого щелкните на кнопке Add (Добавить)
и в диалоговом окне Add Constraint (Добавление
ограничения) определите ограничение.
6. Щелкните на
кнопке на кнопке Options (Параметры), и в
появившемся окне установите переключатель
Неотрицательные значения (если переменные
должны быть позитивными числами),
Линейная модель (если задача, которую
вы решаете, относится к линейным моделям)
7.
Щелкнув на кнопке Solver (Выполнить),
запустите процесс поиска решения.
8. Когда появится
диалоговое окно Solver Results (Результаты
поиска решения), выберите переключатель
Keep Solve Solution (Сохранить найденное решение)
или Restore Original Values (Восстановить исходные
значения).
9. Щелкните на кнопке ОК.
Параметры
средства Поиск решения
Максимальное
время –
служит для ограничения времени,
отпущенного на поиск решения задачи. В
этом поле можно ввести время в секундах,
не превышающее 32 767 (примерно девять
часов); значение 100, используемое по
умолчанию, вполне приемлемо для решения
большинства простых задач.
Предельное
число итераций –
управляет временем решения задачи путем
ограничения числа вычислительных циклов
(итераций).
Относительная
погрешность –
определяет точность вычислений. Чем
меньше значение этого параметра, тем
выше точность вычислений.
Допустимое
отклонение –
предназначен для задания допуска на
отклонение от оптимального решения,
если множество значений влияющей ячейки
ограничено множеством целых чисел. Чем
больше значение допуска, тем меньше
времени требуется на поиск
решения.
Сходимость –
применяется только к нелинейным задачам.
Когда относительное изменение значения
в целевой ячейке за последние пять
итераций становится меньше числа,
указанного в поле Сходимость, поиск
прекращается.
Линейная
модель –
служит для ускорения поиска решения
путем применения к задаче оптимизации
линейной модели. Нелинейные модели
предполагают использование нелинейных
функций, фактора роста и экспоненциального
сглаживания, что замедляет
вычисления.
Неотрицательные
значения –
позволяет установить нулевую нижнюю
границу для тех влияющих ячеек, для
которых не было задано соответствующее
ограничение в диалоговом окне Добавить
ограничение.
Автоматическое
масштабирование –
используется, когда числа в изменяемых
ячейках и в целевой ячейке существенно
различаются.
Показывать
результаты итераций –
приостанавливает поиск решения для
просмотра результатов отдельных
итераций.
Загрузить
модель –
после щелчка на этой кнопке отрывается
одноименное диалоговое окно, в котором
можно ввести ссылку на диапазон ячеек,
содержащих модель оптимизации.
Сохранить
модель –
служит для отображения на экране
одноименного диалогового окна, в
котором
можно ввести ссылку на диапазон ячеек,
предназначенный для хранения модели
оптимизации.
Оценка
линейная –
выберите этот переключатель для работы
с линейной моделью.
Оценка
квадратичная –
выберите этот переключатель для работы
с нелинейной моделью.
Разности
прямые –
используется в большинстве задач, где
скорость изменения ограничений
относительно невысока. Увеличивает
скорость работы средства Поиск
решения.
Разности
центральные –
используется для функций, имеющих
разрывную производную. Данный способ
требует больше вычислений, однако его
применение может быть оправданным, если
выдано сообщение о том, что получить
более точное решение не удается.
Метод
поиска Ньютона –
требует больше памяти, но выполняет
меньше итераций, чем в методе сопряженных
градиентов.
Метод
поиска сопряженных градиентов –
реализует метод сопряженных градиентов,
для которого требуется меньше памяти,
но выполняется больше итераций, чем в
методе Ньютона. Данный метод следует
использовать, если задача достаточно
большая и необходимо экономить память
или если итерации дают слишком малое
отличие в последовательных приближениях.
Соседние файлы в папке Excel
- #
- #
- #
- #
- #
- #
Содержание
- 1 Включение функции
- 2 Подготовка таблицы
- 3 Запуск инструмента Поиск решения
- 3.1 Помогла ли вам эта статья?
- 4 Что такое Поиск решений?
- 5 Зачем нужен Решатель?
- 6 Как работает Решатель?
- 7 Настройка параметров Поиска решений
- 8 Параметры задачи в функции Поиска решений
- 9 Формула в Excel
- 10 Создание формулы
- 11 Заключение
- 12 Краткое описание надстройки
- 13 Как установить надстройку?
- 14 Как подключить надстройку?
- 15 Где найти надстройку «Поиск решения» в Excel 2003/2007/2010?
Одной из самых интересных функций в программе Microsoft Excel является Поиск решения. Вместе с тем, следует отметить, что данный инструмент нельзя отнести к самым популярным среди пользователей в данном приложении. А зря. Ведь эта функция, используя исходные данные, путем перебора, находит наиболее оптимальное решение из всех имеющихся. Давайте выясним, как использовать функцию Поиск решения в программе Microsoft Excel.
Включение функции
Можно долго искать на ленте, где находится Поиск решения, но так и не найти данный инструмент. Просто, для активации данной функции, нужно её включить в настройках программы.
Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».
В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».
Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».
После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».
Подготовка таблицы
Теперь, после того, как мы активировали функцию, давайте разберемся, как она работает. Легче всего это представить на конкретном примере. Итак, у нас есть таблица заработной платы работников предприятия. Нам следует рассчитать премию каждого работника, которая является произведением заработной платы, указанной в отдельном столбце, на определенный коэффициент. При этом, общая сумма денежных средств, выделяемых на премию, равна 30000 рублей. Ячейка, в которой находится данная сумма, имеет название целевой, так как наша цель подобрать данные именно под это число.
Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить с помощью функции Поиска решений. Ячейка, в которой он располагается, называется искомой.
Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.
Запуск инструмента Поиск решения
После того, как таблица подготовлена, находясь во вкладке «Данные», жмем на кнопку «Поиск решения», которая расположена на ленте в блоке инструментов «Анализ».
Открывается окно параметров, в которое нужно внести данные. В поле «Оптимизировать целевую функцию» нужно ввести адрес целевой ячейки, где будет располагаться общая сумма премии для всех работников. Это можно сделать либо пропечатав координаты вручную, либо кликнув на кнопку, расположенную слева от поля введения данных.
После этого, окно параметров свернется, а вы сможете выделить нужную ячейку таблицы. Затем, требуется опять нажать по той же кнопке слева от формы с введенными данными, чтобы развернуть окно параметров снова.
Под окном с адресом целевой ячейки, нужно установить параметры значений, которые будут находиться в ней. Это может быть максимум, минимум, или конкретное значение. В нашем случае, это будет последний вариант. Поэтому, ставим переключатель в позицию «Значения», и в поле слева от него прописываем число 30000. Как мы помним, именно это число по условиям составляет общую сумму премии для всех работников предприятия.
Ниже расположено поле «Изменяя ячейки переменных». Тут нужно указать адрес искомой ячейки, где, как мы помним, находится коэффициент, умножением на который основной заработной платы будет рассчитана величина премии. Адрес можно прописать теми же способами, как мы это делали для целевой ячейки.
В поле «В соответствии с ограничениями» можно выставить определенные ограничения для данных, например, сделать значения целыми или неотрицательными. Для этого, жмем на кнопку «Добавить».
После этого, открывается окно добавления ограничения. В поле «Ссылка на ячейки» прописываем адрес ячеек, относительно которых вводится ограничение. В нашем случае, это искомая ячейка с коэффициентом. Далее проставляем нужный знак: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное», и т.д. В нашем случае, мы выберем знак «больше или равно», чтобы сделать коэффициент положительным числом. Соответственно, в поле «Ограничение» указываем число 0. Если мы хотим настроить ещё одно ограничение, то жмем на кнопку «Добавить». В обратном случае, жмем на кнопку «OK», чтобы сохранить введенные ограничения.
Как видим, после этого, ограничение появляется в соответствующем поле окна параметров поиска решения. Также, сделать переменные неотрицательными, можно установив галочку около соответствующего параметра чуть ниже. Желательно, чтобы установленный тут параметр не противоречил тем, которые вы прописали в ограничениях, иначе, может возникнуть конфликт.
Дополнительные настройки можно задать, кликнув по кнопке «Параметры».
Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.
После того, как все настройки установлены, жмем на кнопку «Найти решение».
Далее, программа Эксель в ячейках выполняет необходимые расчеты. Одновременно с выдачей результатов, открывается окно, в котором вы можете либо сохранить найденное решение, либо восстановить исходные значения, переставив переключатель в соответствующую позицию. Независимо от выбранного варианта, установив галочку «Вернутся в диалоговое окно параметров», вы можете опять перейти к настройкам поиска решения. После того, как выставлены галочки и переключатели, жмем на кнопку «OK».
Если по какой-либо причине результаты поиска решений вас не удовлетворяют, или при их подсчете программа выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше способом, в диалоговое окно параметров. Пересматриваем все введенные данные, так как возможно где-то была допущена ошибка. В случае, если ошибка найдена не была, то переходим к параметру «Выберите метод решения». Тут предоставляется возможность выбора одного из трех способов расчета: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По умолчанию, используется первый метод. Пробуем решить поставленную задачу, выбрав любой другой метод. В случае неудачи, повторяем попытку, с использованием последнего метода. Алгоритм действий всё тот же, который мы описывали выше.
Как видим, функция Поиск решения представляет собой довольно интересный инструмент, который, при правильном использовании, может значительно сэкономить время пользователя на различных подсчетах. К сожалению, далеко не каждый пользователь знает о его существовании, не говоря о том, чтобы правильно уметь работать с этой надстройкой. В чем-то данный инструмент напоминает функцию «Подбор параметра…», но в то же время, имеет и существенные различия с ним.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нет
Надстройка Поиск решений в Excel 2007 не является стандартной. Она предназначается для сложных вычислений, когда имеется больше одной неизвестной. Поэтому она не включается в обычный набор параметров программы. Но если в ней есть необходимость, то она способна предложить пользователю эффективную работу и высокую продуктивность.
Что такое Поиск решений?
Поиск решений в Excel 2007 является надстройкой программы. Это означает, что в обычной конфигурации, выпускаемой производителем, этот пакет не устанавливается. Его нужно загружать и настраивать отдельно. Дело в том, что чаще всего пользователи обходятся без него. Также надстройку нередко называют «Решатель», поскольку она способна вести точные и быстрые вычисления, зачастую независимо от того, насколько сложная задача ей представлена.
Если версия Microsoft Office является оригинальной, тогда проблем с установкой не возникнет. Пользователю нужно сделать несколько переходов:
Параметры→Сервис→Надстройки→Управление→Надстройки Excel.
Откроется окно, в котором есть кнопка перехода. После клика на нее на экране появится список всех предлагаемых надстроек, как установленных, так и неиспользуемых. Теперь нужно найти Поиск решений, затем поставить галочку. Инструмент активизирован, можно пользоваться им в любое время.
Зачем нужен Решатель?
Для чего можно использовать Поиск решений в Excel 2007, и стоит ли вообще его устанавливать? Когда у пользователя присутствует целевая функция, зависящая от нескольких параметров, надстройка будет подбирать решения задачи в соответствии с исходными данными. Таковыми может оказаться переменная, неизвестная или, например, итоговое значение. То есть, пользователь может иметь начальные характеристики и ответ, а программа подберет ход решения, предоставит формулу.
Таким образом, посредством надстройки можно найти:
- Удачное распределение рабочих ресурсов, чтобы достичь максимальной прибыли в ходе деятельности компании или отдельного отдела, филиала.
- Распределение вложений при минимизированных рисках.
- Решение задач, где есть больше одной неизвестной (будет предложено несколько вариантов ответов, из которых пользователь сам подберет наиболее подходящий).
- Сохранение и загрузка модели решения. Оптимальный вариант для сотрудников, которые вынуждены постоянно менять компьютер или ноутбук.
- Решение сразу нескольких задач с разными переменными, неизвестными, формулами и интегралами.
Программа открывает большие возможности, но ею нужно научиться правильно пользоваться.
Как работает Решатель?
Помимо решателя, в Excel есть такая функция, как подбор параметра. Она рекомендована к использованию в случаях, когда имеется только одно неизвестное значение. Эта возможность программы требует намного меньше ресурсных компьютерных затрат, поэтому быстрее выдаст результат.
Поиск решений в Excel 2007 применяется для самых сложных задач, где имеется несколько неизвестных, часто встречаются переменные. В общей постановке их можно сформулировать следующим образом:
- Найти неизвестные→несколько «x».
- При условии, что→формула или функция.
- При ограничениях→здесь обычно указывается неравенство, либо минимальные/максимальные значения.
Также нужно указать на ячейки, с которыми следует проводить вычисления. Есть возможность решать несколько разных задач, если задать программе соответствующие параметры.
Настройка параметров Поиска решений
Чтобы функция Поиска решений в Excel 2007 работала так, как необходимо пользователю, нужно ввести правильные параметры. Обычно они ограничиваются 1-3 характеристиками, но с более сложными задачами потребуется глобальная настройка.
Параметры в Поиске решений программы Office Excel 2007 могут быть следующими:
- Максимальное время – количество секунд, которые пользователь выделяет программе на решение. Оно зависит от сложности задачи.
- Максимальное число интеграций. Это количество ходов, которые делает программа на пути к решению задачи. Если оно увеличивается, то ответ не будет получен.
- Погрешность или точность, чаще всего применяется при решении десятичных дробей (к примеру, до 0,0001).
- Допустимое отклонение. Используется при работе с процентами.
- Неотрицательные значения. Применяется тогда, когда решается функция с двумя правильными ответами (например, +/-X).
- Показ результатов интеграций. Такая настройка указывается в случае, если важен не только результат решений, но и их ход.
- Способ поиска – выбор оптимизационного алгоритма. Обычно применяется «метод Ньютона».
После того как все настройки выбраны, обязательно нужно нажать кнопку сохранения.
Параметры задачи в функции Поиска решений
Работа такой надстройки, как Поиск решения в Excel, осуществляется в соответствии с заданными характеристиками вычисления. Наиболее важной из них является метод. Есть два их варианта. «Метод Ньютона» является настройкой по умолчанию. Он способен работать с большей памятью, но меньшими интеграциями. Поэтому для стандартных и не особо сложных уравнений он вполне подойдет.
Также есть «метод сопряженных градиентов». Здесь запрашивается меньше памяти, но требуется больше интеграций. Следовательно, при его использовании можно решать самые сложные уравнения, использовать масштабные формулы и функции.
Есть обязательный элемент, без которого не сможет функционировать надстройка Поиска решений в программе Excel 2007 – формулы. Они представляют собой такое выражение, которое выполняет то или иное вычисление. Без равенства формул не существует. Поэтому программа не начнет распознавать таковую, когда отсутствует соответствующий знак.
Формула может включать в себя следующее:
- Функция. Это стандартная формула, где присутствует определенный и конкретный порядок действий, поменять который не удастся.
- Ссылка. Она указывает на количество клеток, которые нужно решить. При этом ячейки могут располагаться хаотично или в определенном порядке.
- Оператор. Это символ, который задает тип вычисления (+ – сложение, * – умножение и т.д.).
- Константа. Постоянное значение, которое никогда не меняется. Также для его получения не нужно производить вычисления.
Решение формул осуществляется слева направо при соблюдении всех математических правил.
Создание формулы
Формулы являются уравнениями, которые способствуют выполнению вычислений программы. Если таковые не вводить, то не будет работать Поиск решения в Excel. Задачи, соответственно, тоже не станут решаться. Поэтому для удачного выполнения поставленного задания необходимо правильно ввести формулу.
Вычисление начинается со знака равенства. К примеру, если в ячейке указывается «=КОРЕНЬ(номер клетки)», то будет использована соответствующая функция.
После того как была напечатана основная формула со знаком «=», нужно указать на данные, с которыми она будет взаимодействовать. Это может быть одна или несколько ячеек. Если формула подходит для 2-3 клеток, то объединить их можно, используя знак «+».
Чтобы найти нужную информацию, можно воспользоваться функцией поиска. Например, если нужна формула с буквой «A», то ее и надо указывать. Тогда пользователю будут предложены все данные, ее в себя включающие.
Заключение
В заключении в программе Excel 2007 нужно сохранить заданные параметры решения задач. Сделать это можно несколькими способами. Стандартный вариант с кликом на соответствующую кнопку подойдет в том случае, если для всех данных используется один метод вычислений.
Когда нужно решить сразу несколько уравнений, к примеру, найти минимум и максимум функций, то нужно сохранять не все вычисление, а его модели. Затем пользователь сможет применить их к тому или иному решению.
Надстройка Microsoft Excel «Поиск решения» является мощным средством поиска решений и применяется при решении задач оптимизации. Процедура поиска решения позволяет находить оптимальное значение формулы содержащейся в ячейке, которую называют целевой. Процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке.
Краткое описание надстройки
Для получения заданного результата по формуле, процедура изменяет значения во влияющих ячейках. Для уменьшения интервала значений, используемых в модели, используются ограничения значений. Надстройка поиск решений является стандартной надстройкой Microsoft Office Excel и доступна сразу при установке Microsoft Office в целом или Microsoft Excel в частности.
Как установить надстройку?
Надстройку «Поиск решения» можно установить двумя способами. Стандартные надстройки, такие как «Поиск решения» и «Пакет анализа» устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки «Поиск решения» на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.
Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию «Добавить или удалить компоненты».
Далее, нажимаем кнопку «Продолжить», в параметрах установки находим приложение Microsoft Excel, в компонентах этого приложения находим раздел «Надстройки», выбираем надстройку «Поиск решения» и устанавливаем параметр «Запускать с моего компьютера».
Опять жмем кнопку «Продолжить» и ожидаем пока надстройка установится.
Как подключить надстройку?
Перед использованием необходимо предварительно включить надстройку, поставив галочку перед ее названием в списке доступных надстроек диалогового окна «Надстройки».
Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье «Как установить надстройку для Excel 2003/2007/2010?» со скриншотами для каждой из трех версий приложения Excel, поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка «Поиск решения») и произвести установку в соответствии с описанием по ссылке выше.
Где найти надстройку «Поиск решения» в Excel 2003/2007/2010?
После установки и подключения надстройки в Excel 2007/2010 на вкладке «Данные» появляется группа «Анализ» с новой командой «Поиск Решения». В Excel 2003 — появляется новый пункт меню «Сервис» с одноименным названием. Поиск решения — стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.
Чтобы познакомиться с мощным инструментом Excel Поиск решения, рассмотрим и решим с вами задачу.
Необходимо найти оптимальные объемы выпуска трех видов продукции для получения максимальной прибыли от их продажи.
При решении данной задачи должны быть учтены следующие ограничения:
- общий объем производства – всего 300 изделий;
- должно быть произведено не менее 50 изделий А;
- должно быть произведено не менее 40 изделий В;
- должно быть произведено не более 40 изделий С.
Технология:
1. Внести в новый рабочий лист данные для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6 должны быть введены формулы.
2. Запустить задачу поиска решений. Для этого: выполнить команду в Excel 2003 Сервис | Поиск решений … (В Excel 2007 и 2010 необходимо зайти в раздел Данные | Поиск решения)
и в окне “Поиск решений” ввести данные:
- в поле «Установить целевую ячейку» указать адрес D6;
- установить флажок «Равной максимальному значению»;
- в поле «Изменяя ячейки» определить изменяемые ячейки (B3:B5);
- в поле «Ограничения» по одному добавить каждое из следующих четырех ограничений задачи (B6=300; B3>=50; B4>=40; B5
Процедура поиска решения
О надстройке «Поиск решения»
Поиск решений является частью блока задач, который иногда называют анализ “что-если”. Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.
Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например можно изменить объем планируемого бюджета рекламы и увидеть, как это повлияет на проектируемую сумму расходов.
Пример поиска решения
В приведенном ниже примере (см. книгу «Поиск решения.xls») объем продаж в каждом квартале зависит от уровня рекламы, что косвенно определяет сумму доходов, издержки, а также прибыль. Чтобы найти максимальную возможную сумму общего дохода, процедура поиска решения может повышать ежеквартальные расходы на рекламу (ячейки B5:C5), пока общие расходы не превысят ограничения в 20 тысяч (ячейка F5). Значения во влияющих ячейках служат для вычисления дохода за каждый квартал, поэтому они связаны с формулой в целевой ячейке F7, =СУММА(Q1 Прибыль:Q2 Прибыль).
1 Изменяемые ячейки
2 Ячейка с ограничениями
3 Целевая ячейка
После выполнения процедуры получены следующие значения:
Примеры расчетов с использованием процедуры поиска решения
Примеры, содержащиеся в книге «Поиск решения.xls», помогут разрешить ваши вопросы. Чтобы применить любой из шести примеров: «Структура производства» и «Транспортная задача», — откройте книгу, перейдите к нужному листу и выберите команду Поиск решения в меню Сервис. В примерах уже подобраны целевая и влияющие ячейки, а также ограничения.
Добавление, изменение и удаление ограничения на поиск решения
-
В меню Сервис выберите команду Поиск решения.
Если команда Поиск решения отсутствует в меню Сервис, загрузите соответствующую надстройку.
Инструкции
1. В меню Сервис выберите команду Надстройки.
2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
4. Следуйте инструкциям программы установки, если они имеются.
-
Добавьте или измените ограничения.
Инструкции
Добавление ограничения
1. В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Параметры.
2. В поле Ссылка на ячейку введите адрес или имя ячейки, на значение которой накладываются ограничения.
3. Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич ), который должен располагаться между ссылкой и ограничение. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное».
4. В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.
5. Выполните одно из следующих действий.
-
Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить.
-
Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.
Примечания
o Условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.
o Флажок Линейная модель в диалоговом окне Параметры поиска решения позволяет задать любое количество ограничений. При решении нелинейных задач на значения изменяемых ячеек можно наложить более 100 ограничений, в дополнение к целочисленным ограничениям на переменные.
Изменение и удаление ограничений
1. В списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить.
2. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.
Изменение способа поиска решения
-
В меню Сервис выберите команду Поиск решения.
Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».
Инструкции
1. В меню Сервис выберите команду Надстройки.
2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
4. Следуйте инструкциям программы установки, если они имеются.
2. В диалоговом окне Поиск решения нажмите кнопку Параметры.
3. В диалоговом окне Параметры поиска решения задайте один или несколько следующих параметров.
Время поиска и количество итераций
1. В поле Максимальное время введите интервал в секундах, чтобы ограничить время поиска решения задачи.
2. В поле Предельное число итераций введите максимальное количество количество итераций, отводимое на достижение конечного результата.
Примечание. При достижении границы отведенного временного интервала или при выполнении отведенного числа итераций на экране появляется диалоговое окно Текущее состояние поиска решения.
Относительная погрешность
В поле Относительная погрешность введите необходимую погрешность — чем меньше введенное число, тем выше точность результатов.
Допустимое отклонение
В поле Допустимое отклонение введите необходимый допуск.
Сходимость
В поле Сходимость введите значение относительного изменения, при достижении которого в последних пяти итерациях поиск решения прекращается — чем меньше это значение, тем выше точность результатов.
Примечание. Чтобы получить дополнительные сведения о других параметрах, нажмите в диалоговом окне кнопку Справка .
1. Нажмите кнопку OK.
2. В диалоговом окне Поиск решения нажмите кнопку Выполнить или Закрыть.
3. Сформулируйте задачу и найдите решение.
Инструкции
1. В меню Сервис выберите команду Поиск решения.
2. Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».
3. В меню Сервис выберите команду Надстройки.
4. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
5. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
6. Следуйте инструкциям программы установки, если они имеются.
7. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
8. Выполните одно из следующих действий:
-
чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
-
чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
-
чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
9. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
10. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
11. В поле Ограничения введите все ограничения , накладываемые на поиск решения.
Добавление ограничения
1. В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Параметры.
2. В поле Ссылка на ячейку введите адрес или имя ячейки, на значение которой накладываются ограничения.
3. Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич), который должен располагаться между ссылкой и ограничение. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное».
4. В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.
5. Выполните одно из следующих действий.
-
Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить.
-
Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.
Изменение и удаление ограничений
1. В списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить.
2. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.
3. Нажмите кнопку Выполнить и выполните одно из следующих действий:
§ чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;
§ чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения.
4. Если решение будет найдено, выберите тип отчета в списке Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги.
Постановка задачи и оптимизация модели с помощью процедуры поиска решения
1. В меню Сервис выберите команду Поиск решения.
2. Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».
Инструкции
1. В меню Сервис выберите команду Надстройки.
2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
4. Следуйте инструкциям программы установки, если они имеются.
3. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
4. Выполните одно из следующих действий:
1. чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
2. чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
3. чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
5. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
6. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
7. В поле Ограничения введите все ограничения, накладываемые на поиск решения.
Загрузка модели оптимизации
Перед тем как использовать данную процедуру, необходимо сохранить хотя бы одну модель.
1. В меню Сервис выберите команду Поиск решения.
2. В диалоговом окне Поиск решения нажмите кнопку Параметры.
3. В диалоговом окне Параметры поиска решения нажмите кнопку Загрузить модель.
4. Введите ссылку на весь диапазон ячеек с областью модели.
Сохранение модели оптимизации
-
Сформулируйте задачу и найдите решение.
Инструкции
-
В меню Сервис выберите команду Поиск решения.
-
В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
-
Выполните одно из следующих действий:
-
чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
-
чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
-
чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
4. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
5. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
6. В поле Ограничения введите все ограничения , накладываемые на поиск решения.
7. В меню Сервис выберите команду Поиск решения.
8. Нажмите кнопку Параметры.
9. Нажмите кнопку Сохранить модель.
10.ведите ссылку на верхнюю ячейку столбца, в котором следует разместить модель оптимизации.
Совет
Значения элементов управления диалогового окна Поиск решения записываются на лист при выполнении команды Сохранить в меню Файл. Чтобы использовать на листе несколько моделей оптимизации, нужно сохранить их с помощью кнопки Сохранить модель.
Сохранение значений влияющих ячеек в качестве сценария
1. Сформулируйте задачу.
2. В меню Сервис выберите команду Поиск решения.
3. В поле Ограничения введите все ограничения, накладываемые на поиск решения.
4. В диалоговом окне Результаты поиска решения выберите Сохранить сценарий.
5. Введите в поле Название сценария название сценария.
Просмотр промежуточных результатов поиска решения
1. Сформулируйте задачу.
2. В меню Сервис выберите команду Поиск решения.
3. Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».
5. Чтобы просмотреть значения всех найденных решений, в диалоговом окне Параметры поиска решения установите флажок Показывать результаты итераций, а затем нажмите кнопку ОК.
6. В диалоговом окне Поиск решения нажмите кнопку Выполнить.
7. В диалоговом окне Текущее состояние поиска решения выполните одно из следующих действий.
o Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.
o Чтобы выполнить следующую итерацию и просмотреть ее результаты, нажмите кнопку Продолжить.
Работа со сценариями
Сценарии
Сценарии являются частью блока задач, который иногда называют инструментами анализа “что-если”. Сценарий — это набор значений, которые Microsoft Excel сохраняет и может автоматически подставлять на листе. Сценарии можно использовать для прогноза результатов моделей и систем расчетов. Существует возможность создать и сохранить на листе различные группы значений, а затем переключаться на любой из этих новых сценариев для просмотра различных результатов.
Создание сценариев. Например, если требуется создать бюджет, но доходы точно не известны, можно определить различные значения дохода, а затем переключаться между сценариями для выполнения анализов «что-если».
В приведенном примере можно назвать сценарий «Худший случай», установить в ячейке B1 значение 50 000р., а в ячейке B2 значение 13 200р.
Второй сценарий можно назвать «Лучший случай» и изменить значения в ячейке B1 на 150 000р., а в ячейке B2 на 26 000р.
Итоговые отчеты по сценариям. Для сравнения нескольких сценариев можно создать отчет, обобщающий их на одной странице. Сценарии в отчете могут располагаться один рядом с другим либо могут быть обобщены в отчете сводной таблицы.
Создание сценария
1. Выберите команду Сценарии в меню Сервис.
2. Нажмите кнопку Добавить.
3. Введите в поле Название сценария название сценария .
4. В поле Изменяемые ячейки введите ссылки на ячейки, которые требуется изменить.
Примечание. Для сохранения исходных значений изменяемых ячеек создайте сценарий, который использует исходные значения ячеек, до создания сценария, изменяющего значения.
5. Установите необходимые флажки в наборе флажков Защита .
6. Нажмите кнопку OK.
7. Введите необходимые значения в диалоговом окне Значения ячеек сценария.
8. Чтобы создать сценарий, нажмите кнопку OK.
9. Если требуется создать дополнительные сценарии, снова нажмите кнопку Добавить, а затем повторите эту процедуру. После завершения создания сценариев нажмите кнопку OK, а затем — кнопку Закрыть в диалоговом окне Диспетчер сценариев.
Создание итогового отчета по сценариям
1. Выберите команду Сценарии в меню Сервис.
2. Нажмите кнопку Отчет.
3. Установите переключатель в положение Структура или Сводная таблица .
4. В поле Ячейки результата введите ссылки на ячейки, значения которых были изменены с помощью сценариев . В качестве разделителя ссылок используется запятая.
Примечание. Не обязательно указывать ячейки результата в итоговом отчете, однако они потребуются в отчете сводной таблицы .
Удаление сценария
1. Выберите команду Сценарии в меню Сервис.
2. Выберите название сценария, который требуется удалить, а затем нажмите кнопку Удалить.
Просмотр сценария
При просмотре сценария изменяются ячейки, сохраненные как часть этого сценария.
1. Выберите команду Сценарии в меню Сервис.
2. Выберите название сценария, который необходимо просмотреть.
3. Нажмите кнопку Показать.
Правка сценария
Если после внесения изменений в сценарий он будет сохранен с первоначальным именем, новые значения изменяемых ячеек заменят значения в исходном сценарии.
1. Выберите команду Сценарии в меню Сервис.
2. Выберите название изменяемого сценария, а затем нажмите кнопку Изменить.
3. Внесите требуемые изменения.
4. Введите необходимые значения в диалоговом окне Значения ячеек сценария.
5. Выполните одно из следующих действий:
o чтобы сохранить изменения, нажмите кнопку OK;
o чтобы вернуться в диалоговое окно Диспетчер сценариев без изменения текущего сценария, нажмите кнопку Отмена .
Объединение сценариев с других листов
Сценарии проще объединять, когда все модели что-если на листах идентичны. Все изменяемые ячейки выбранного листа должны ссылаться на соответствующие изменяемые ячейки активного листа. Все сценарии Microsoft Excel будут скопированы с исходного листа на активный лист.
1. Откройте все книги, содержащие сценарии, которые требуется объединить.
2. Перейдите на лист, на котором требуется объединить сценарии.
3. Выберите команду Сценарии в меню Сервис.
4. Нажмите кнопку Объединить.
5. Выберите название книги из списка Книга .
6. В поле Лист щелкните имя листа, содержащего сценарии, которые требуется объединить, и нажмите кнопку OK.
7. Если требуется объединить сценарии с нескольких листов, повторите эту процедуру.
4. В диалоговом окне Поиск решения нажмите кнопку Параметры.
