Большинство задач, решаемых с помощью электронной таблицы, предполагают нахождение искомого результата по известным исходным данным. Но в Excel есть инструменты, позволяющие решить и обратную задачу: подобрать исходные данные для получения желаемого результата.
Одним из таких инструментов является Поиск решения, который особенно удобен для решения так называемых «задач оптимизации».
Если Вы раньше не использовали Поиск решения, то Вам потребуется установить соответствующую надстройку.
Сделать это можно так:
для версий старше Excel 2007 через команду меню Сервис —> Надстройки;
начиная с Excel 2007 через диалоговое окно Параметры Excel
Начиная с версии Excel 2007 кнопка для запуска Поиска решения появится на вкладке Данные.
В версиях до Excel 2007 аналогичная команда появится в меню Сервис
Разберём порядок работы Поиска решения на простом примере.
Пример 1. Распределение премии
Предположим, что Вы начальник производственного отдела и Вам предстоит по-честному распределить премию в сумме 100 000 руб. между сотрудниками отдела пропорционально их должностным окладам. Другими словами Вам требуется подобрать коэффициент пропорциональности для вычисления размера премии по окладу.
Первым делом создаём таблицу с исходными данными и формулами, с помощью которых должен быть получен результат. В нашем случае результат — это суммарная величина премии. Очень важно, чтобы целевая ячейка (С8) посредством формул была связана с искомой изменяемой ячейкой (Е2). В примере они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника (С2:С7).
Теперь запускаем Поиск решения и в открывшемся диалоговом окне устанавливаем необходимые параметры. Внешний вид диалоговых окон в разных версиях несколько различается:
Начиная с Excel 2010
До Excel 2010
- Целевая ячейка, в которой должен получиться желаемый результат. Целевая ячейка может быть только одна
- Варианты оптимизации: максимальное возможное значение, минимальное возможное значение или конкретное значение. Если требуется получить конкретное значение, то его следует указать в поле ввода
- Изменяемых ячеек может быть несколько: отдельные ячейки или диапазоны. Собственно, именно в них Excel перебирает варианты с тем, чтобы получить в целевой ячейке заданное значение
- Ограничения задаются с помощью кнопки Добавить. Задание ограничений, пожалуй, не менее важный и сложный этап, чем построение формул. Именно ограничения обеспечивают получение правильного результата. Ограничения можно задавать как для отдельных ячеек, так и для диапазонов. Помимо всем понятных знаков =, >=, <=, при задании ограничений можно использовать варианты цел (целое), бин (бинарное или двоичное, т.е. 0 или 1), раз (все разные — только начиная с версии Excel 2010).
В данном примере ограничение только одно: коэффициент должен быть положительным. Это ограничение можно задать по-разному: либо установить явно, воспользовавшись кнопкой Добавить, либо поставить флажок Сделать переменные без ограничений неотрицательными.
Для версий до Excel 2010 этот флажок можно найти в диалоговом окне Параметры Поиска решения, которое открывается при нажатии на кнопку Параметры
- Кнопка, включающая итеративные вычисления с заданными параметрами.
После нажатия кнопки Найти решение (Выполнить) Вы уже можете видеть в таблице полученный результат. При этом на экране появляется диалоговое окно Результаты поиска решения.
Начиная с Excel 2010
До Excel 2010
Если результат, который Вы видите в таблице Вас устраивает, то в диалоговом окне Результаты поиска решения нажимаете ОК и фиксируете результат в таблице. Если же результат Вас не устроил, то нажимаете Отмена и возвращаетесь к предыдущему состоянию таблицы.
Решение данной задачи выглядит так
Важно: при любых изменениях исходных данных для получения нового результата Поиск решения придется запускать снова.
Разберём еще одну задачу оптимизации (получение максимальной прибыли)
Пример 2. Мебельное производство (максимизация прибыли)
Фирма производит две модели А и В сборных книжных полок.
Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.
Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В — 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.
Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В — 30 мин. в неделю можно использовать 160 ч машинного времени.
Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В — 120 руб. прибыли?
Порядок действий нам уже известен.
Сначала создаем таблицы с исходными данными и формулами. Расположение ячеек на листе может быть абсолютно произвольным, таким как удобно автору. Например, как на рисунке
Запускаем Поиск решения и в диалоговом окне устанавливаем необходимые параметры
- Целевая ячейка B12 содержит формулу для расчёта прибыли
- Параметр оптимизации — максимум
- Изменяемые ячейки B9:C9
- Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D16); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D15). Здесь вместо ссылок на ячейки D15 и D16 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице
- Нажимаем кнопку Найти решение (Выполнить) и после подтверждения получаем результат
Но даже если Вы правильно создали формулы и задали ограничения, результат может оказаться неожиданным. Например, при решении данной задачи Вы можете увидеть такой результат:
И это несмотря на то, что было задано ограничение целое. В таких случаях можно попробовать настроить параметры Поиска решения. Для этого в окне Поиск решения нажимаем кнопку Параметры и попадаем в одноимённое диалоговое окно
Первый из выделенных параметров отвечает за точность вычислений. Уменьшая его, можно добиться более точного результата, в нашем случае — целых значений. Второй из выделенных параметров (доступен, начиная с версии Excel 2010) даёт ответ на вопрос: как вообще могли получиться дробные результаты при ограничении целое? Оказывается Поиск решения это ограничение просто проигнорировал в соответствии с установленным флажком.
Пример 3. Транспортная задача (минимизация затрат)
На заказ строительной компании песок перевозиться от трех поставщиков (карьеров) пяти потребителям (строительным площадкам). Стоимость на доставку включается в себестоимость объекта, поэтому строительная компания заинтересована обеспечить потребности своих стройплощадок в песке самым дешевым способом.
Дано: запасы песка на карьерах; потребности в песке стройплощадок; затраты на транспортировку между каждой парой «поставщик-потребитель».
Нужно найти схему оптимальных перевозок для удовлетворения нужд (откуда и куда), при которой общие затраты на транспортировку были бы минимальными.
Пример расположения ячеек с исходными данными и ограничениями, искомых ячеек и целевой ячейки показан на рисунке
В серых ячейках формулы суммы по строкам и столбцам, а в целевой ячейке формула для подсчёта общих затрат на транспортировку.
Запускаем Поиск решения и устанавливаем необходимые параметры (см. рисунок)
Нажимаем Найти решение (Выполнить) и получаем результат, изображенный ниже
Иногда транспортные задачи усложняются с помощью дополнительных ограничений. Например, по каким-то причинам невозможно возить песок с карьера 2 на стройплощадку №3. Добавляем ещё одно ограничение $D$13=0. И после запуска Поиска решения получаем другой результат
И последнее, на что следует обратить внимание, это выбор метода решения. Если задача достаточно сложная, то для достижения результата может потребоваться подобрать метод решения
Начиная с Excel 2010
До Excel 2010
В заключение предлагаю попробовать свои силы в применении Поиска решения и решить с его помощью старинную задачу:
Крестьянин на базаре за 100 рублей купил 100 голов скота. Бык стоит 10 рублей, корова 5 рублей, телёнок 50 копеек. Сколько быков, коров и телят купил крестьянин?
Лист Excel не всегда используется для обработки данных. Расположение строк и столбцов — это удобный способ представления и управления большим объемом информации, а функции Excel позволяют использовать электронную таблицу в качестве инструмента сбора данных.
Excel предлагает некоторые функции, которые позволяют вам использовать его как форму, которую могут заполнять другие пользователи, но если вам нужно ввести инструкции по заполнению формы в электронную таблицу, это будет беспорядочно.
Более простой способ убедиться, что вы получаете правильные данные, — это добавить лимиты и ограничения к ячейке. Любые данные или значения, выходящие за пределы лимита, будут отклонены.
Предел символов для ячеек в Excel
Вы можете установить ограничение на количество символов для ячеек в Excel. Это ограничение работает таким образом, что введенное значение должно содержать минимальное количество символов или не длиннее определенного количества символов.
Это полезно, когда пользователь должен ввести номер телефона, почтовый индекс или что-то подобное.
- Откройте файл Excel вы хотите добавить ограничения на количество символов.
- Выберите ячейки / столбцы / строки к которому будет добавлен лимит.
- Перейти к Вкладка «Данные».
- Нажмите «Проверка данных». на панели инструментов Data Tools.
- Перейти к Вкладка «Настройки».
- Открой Раскрывающийся список «Разрешить» и выберите «Длина текста».
- Открой Раскрывающийся список «Данные» и выберите ограничивающие критерии. Чтобы ограничить его определенным числом, выберите «Меньше или равно».
- Чтобы установить максимальное значение, введите значение в поле Максимум.
- Щелкните ОК.
Добавить всплывающую подсказку для ограничения количества символов
Пользователь не сможет определить, есть ли в ячейке ограничение на количество символов, поэтому добавление всплывающей подсказки поможет им.
- Выберите ячейки / столбцы / строки с ограничением символов.
- Перейти к Вкладка «Данные».
- Нажмите Проверка достоверности данных.
- Выберите Вкладка «Входное сообщение».
- Включите Опция «Показывать входное сообщение, когда ячейка выбрана».
- В поле заголовка введите тему для всплывающей подсказки.
- Введите сообщениее в ‘Входное сообщение’ поле.
- Нажмите ХОРОШО.
Добавить сообщение об ошибке
Когда пользователь вводит значение, которое не соответствует ограничениям на количество символов в ячейке, он увидит ошибку. К сожалению, сообщение об ошибке носит общий характер и не сообщает пользователю, почему значение не было исключено. Вы можете изменить его, чтобы пользователь знал, что не так.
- Выберите ячейку / столбец / строки с ограничением символов.
- Перейти к Вкладка данных на ленте.
- Нажмите Проверка достоверности данных.
- Перейти к Окно сообщения об ошибке.
- Включите Параметр «Показывать предупреждение об ошибке после ввода неверных данных».
- Введите заголовок оповещения в ‘Заголовок ».
- Введите сообщение который сообщает пользователю, какие данные вводить в Поле сообщения об ошибке.
Вывод
Вы можете добавить верхний или нижний предел символов для ячеек или ввести точное количество символов, которое пользователь должен ввести, чтобы значение было принято. Просмотрите различные варианты и добавьте тип ограничения данных, который вам нужен для ячейки, столбца или строки.
[expert_bq id=»1570″]По умолчанию, как серые, базовые линии на поверхности листа Excel, так и любые другие, не печатаются при выводе на бумагу через принтер. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Вкладка «Границы» позволяет сделать разметку более сложной. Вам доступны 13 видов контура и все цвета спектра, а также возможность нарисовать внешние и внутренние линии объекта (в том числе диагональные).
Как сделать или убрать границы в Excel
- Выберите ячейки / столбцы / строки с ограничением символов.
- Перейти к Вкладка «Данные».
- Нажмите Проверка достоверности данных.
- Выберите Вкладка «Входное сообщение».
- Включите Опция «Показывать входное сообщение, когда ячейка выбрана».
- В поле заголовка введите тему для всплывающей подсказки.
- Введите сообщениее в ‘Входное сообщение’ поле.
- Нажмите ХОРОШО.
Чтобы упростить работу в будущем и застраховать себя от возможных ошибок при добавлении новой информации о продажах, мы рекомендуем использовать «умную» таблицу. Она автоматически подстроит свой размер с учетом добавленных строк, и никакие ссылки в формулах не нужно будет менять.
Обновлено: 15.04.2023
Ограничения задаются в поле В соответствии с ограничениями (Ограничения). Для ввода ограничений нажмите кнопку Добавить. Откроется окно Добавление ограничения (рис. 1.25), в котором два поля ввода:
1.62.Ссылка на ячейки (Ссылка на ячейку) — для указания ячейки или
Оптимизировать целевою функцию: 5F54
До: (о) Максимум Q Минимум Q Значения: Изменяя ячейки переменных:
| S8$3:SES3 | Ш |
| В соответствии с ограничениями: |
SFS12:SFS15 >= SHSi2:SH5l5 iF56:5Fsil <= SHS6:SH5li
[71 Сделать переменные без ограничений неотрицательными
Выберите метод решения:
Для гладких нелинейных задач используйте поиск решения нелинейных задач методом ОПГ, для линейных задач — поиск решения линейных задач симплекс-методом, а для негладких задач — эволюционный поиск, решения.
| Справка | Найти решение | Закрыть |
Рис. 1.23. Окно средства Поиск решения в Excel 2010
диапазона ячеек, содержащих формулы левых частей ограничений. Диапазон можно задать лишь в том случае, когда несколько ограничений подряд, находящихся в смежных строках, имеют один и тот же знак. (Конечно, можно каждое ограничение ввести отдельно, но задание диапазонов удобнее.)
1.63.Ограничение — для указания ячейки или диапазона ячеек, содержащих правые части ограничений. Если в поле Ссылка на ячейки (Ссылка на ячейку) указан диапазон ячеек, то и в поле Ограничение тоже должен быть
Установить целевую ячейку:
Равной: (3) максимальному значению Означению: [0_
0 минимальному значению Изменяя ячейки:
| |$Б$3:$Е$3 | (^] [ Предположить ] |
| Ограничения: |
Рнс. 1.24. Окно средства Поиск решения в Excel 2007 и предыдущих версиях
Ссылка на ячеики:
Рис. 1.25. Добавление ограничения
диапазон, причем той же размерности.
В раскрывающемся списке (между этими полями) можно выбрать:
1.65.цел — целое число;
1.66.бин в Excel 2010 (или двоим в предыдущих версиях)— бинарное (двоичное) значение: число 0 или 1;
1.67.раз (только в Excel 2010)— разные целые числа от 1 до N (где N — число переменных).
Последние три варианта используются при целочисленном решении (см. далее гл. 2).
Для ввода ссылок на ячейки и диапазоны перетаскивайте окно, открывая выделяемые ячейки, или пользуйтесь кнопками свертывания, расположенными справа от полей ввода. После ввода ограничений, кроме самого последнего, следует нажимать кнопку Добавить. При этом поля окна Добавление ограничения очищаются и можно вводить следующие ограничения.
После ввода последнего из них щелкните кнопку ОК, чтобы вернуться в окно Поиск решения, где вы увидите список всех введенных ограничений. При необходимости их изменения или удаления можно выделить любое из ограничений и нажать кнопку, соответственно, Изменить или Удалить.
Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»
Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».
После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке
Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.
Пример первый.
Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.
То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.
В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).
Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.
Под «1» обозначена наша целевая ячейка. Она может быть только одна.
Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.
Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).
Но в нашем примере ограничение может быть лишь одно – положительный коэффициент. Задать его, конечно, можно несколькими способами – либо используя «Добавить» (что называют «явно указать ограничение»), либо просто отметить действующей функцию «Сделать переменные без ограничений неотрицательными». Это можно сделать в надстройке «Поиск решения», нажав на кнопочку «Параметры».
Кстати, после подтверждения параметров и запуска программы (кнопочка «Выполнить»), вы сможете в таблице просмотреть полученный результат. Тогда программа продемонстрирует окошко «результатов поиска».
Правильное решение задачи примера должно получиться вот таким
Чтобы более подробно взглянуть на то, как действует данная программа, давайте разберем еще один пример.
Допустим, вы являетесь владельцем крупного мебельного предприятия и необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Вы производите только книжные полки, при этом всего двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).
Поскольку порядок действия известен, то начинаем создавать необходимую нам таблицу с данными и формулами. Расположение ячеек, как и ранее, вы можете установить на свое усмотрение. Или же воспользоваться нашим
Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.
Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).
Конечно, в этом случае можно было не указывать адреса ячеек, а напрямую прописать необходимые цифровые значения, однако если использовать адреса, то изменения ограничений можно будет проводить и в таблице, что поможет рассчитывать прибыль этого предприятия в будущем, при смене исходных данных.
Активируем программу, и она подготавливает решение.
Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было
Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».
Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.
Так что будьте предельно внимательны в будущем.
Третий и, пожалуй, последний пример. Попробуем минимизировать затраты транспортной компании используя поиск решений в Excel 2010.
Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.
Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».
Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.
Серые ячейки нашей таблицы содержат формулы суммы по столбцам и строкам, а целевая ячейка – формула для общего подсчета затраты на доставку груза. Запускаем наш «Поиск решения» и вносим необходимые настройки
После этого приступаем к поиску решения этой задачки
Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным
Напоследок осталось сказать только о выборе метода решения. И если задачка действительно очень сложная, то чтобы получить необходимый результат, скорее всего, понадобиться подобрать необходимый метод решения.
Вот и все по данному вопросу.
Не очень частый, но и не экзотический случай. На моих тренингах такой вопрос задавали не один и не два раза 
Суть в том, что мы имеем конечный набор каких-то чисел, из которых надо выбрать те, что дадут в сумме заданное значение.
В реальной жизни эта задача может выглядеть по-разному.
- Например, мы выгрузили из интернет-банка все платежи, которые поступили на наш счет за последний месяц. Один из клиентов разбивает сумму своего платежа на несколько отдельных счетов и платит частями. Мы знаем общую сумму оплаты и количество счетов, но не знаем их сумм. Надо подобрать те суммы в истории платежей, которые дадут в общем заданное значение.
- У нас есть несколько рулонов стали (линолеума, бумаги. ), из которых надо подобрать под заказ те, что дадут заданную длину.
- Блэкджек или в народе «очко». Надо набрать карты суммарной стоимостью максимально близкой к 21 баллу, но не превысить этот порог.
В некоторых случаях может быть известна разрешенная погрешность допуска. Она может быть как нулевой (в случае подбора счетов), так и ненулевой (в случае подбора рулонов), или ограниченной снизу или сверху (в случае блэкджека).
Давайте рассмотрим несколько способов решения такой задачи в Excel.
Способ 1. Надстройка Поиск решения (Solver)
Эта надстройка входит в стандартный набор пакета Microsoft Office вместе с Excel и предназначена, в общем случае, для решения линейных и нелинейных задач оптимизации при наличии списка ограничений. Чтобы ее подключить, необходимо:
- в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти (File — Excel Options — Add-ins — Go)
- в Excel 2003 и старше — открыть меню Сервис — Надстройки (Tools — Add-ins)
и установить соответствующий флажок. Тогда на вкладке или в меню Данные (Data) появится нужная нам команда.
Чтобы использовать надстройку Поиск решения для нашей задачи необходимо будет слегка модернизировать наш пример, добавив к списку подбираемых сумм несколько вспомогательных ячеек и формул:
- Диапазон A1:A20 содержит наши числа, из которых мы будем выбирать нужные, чтобы «вписаться» в заданную сумму.
- Диапазон В1:B20 будет своего рода набором переключателей, т.е. будет содержать нули или единички, показывая, отбираем мы данное число в выборку или нет.
- В ячейке E2 стоит обычная автосумма всех единичек по столбцу B, подсчитывающая кол-во выбранных чисел.
- В ячейке E3 с помощью функции СУММПРОИЗВ (SUMPRODUCT) считается сумма попарных произведений ячеек из столбцов А и B (то есть A1*B1+A2*B2+A3*B3+. ). Фактически, здесь подсчитывается сумма чисел из столбца А, отобранных единичками из столбца В.
- В розовую ячейку E4 пользователь вводит желаемую сумму для подбора.
- В ячейке E5 вычисляется абсолютное по модулю значение погрешности подбора с целью ее будущей минимизации.
- Все желтых ячейках Е8:E17 хотелось бы получить список отобранных чисел, т.е. тех чисел из столбца А, напротив которых в столбце В есть единички. Для этого необходимо выделить сразу все (!) желтые ячейки и в них ввести вот такую формулу массива:
=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$20;НАИМЕНЬШИЙ(ЕСЛИ(B1:B20=1;СТРОКА(B1:B20);»»);СТРОКА()-СТРОКА($E$8)+1));»»)
=IFERROR(INDEX($A$1:$A$20;SMALL(IF(B1:B20=1;ROW(B1:B20);»»);ROW()-ROW($E$8)+1));»»)
После ввода формулы ее необходимо ввести не как обычную формулу, а как формулу массива, т.е. нажать не Enter, а Ctrl+Shift+Enter. Похожая формула используется в примере о ВПР, выдающей сразу все найденные значения (а не только первое).
Теперь перейдем на вкладку (или в меню) Данные и запустим инструмент Поиск решения (Data — Solver):
В открывшемся окне необходимо:
- Задать как целевую функцию (Target Cell) — ячейку вычисления погрешности подбора E5. Чуть ниже выбрать опцию — Минимум, т.к. мы хотим подобрать числа под заданную сумму с минимальной (а лучше даже нулевой) погрешностью.
- В качестве изменяемых ячеек переменных (Changing cells) задать диапазон столбца переключателей B1:B20.
- С помощью кнопки Добавить (Add) создать дополнительное условие на то, что ячейки диапазона B1:B20 должны быть бинарными (т.е. содержать только 0 или 1):
После ввода всех параметров и ограничений запускаем процесс подбора кнопкой Найти решение (Solve). Процесс подбора занимает от нескольких секунд до нескольких минут (в тяжелых случаях) и заканчивается появлением следующего окна:
Теперь можно либо оставить найденное решение подбора (Сохранить найденное решение), либо откатиться к прежним значениям (Восстановить исходные значения).
Необходимо отметить, что для такого класса задач существует не одно, а целое множество решений, особенно, если не приравнивать жестко погрешность к нулю. Поэтому запуск Поиска решения с разными начальными данными (т.е. разными комбинациями 0 и 1 в столбце В) может приводить к разным наборам чисел в выборках в пределах заданных ограничений. Так что имеет смысл прогнать эту процедуру несколько раз, произвольно изменяя переключатели в столбце В.
Найденные комбинации можно сохранять виде сценариев (кнопка Сохранить сценарий), чтобы вернуться к нем позднее с помощью команды Данные — Анализ «что-если» — Диспетчер сценариев (Data — What-If Analysis — Scenario Manager):
И весьма удобно будет вывести все найденные решения, сохраненные в виде сценариев, в одной сравнительной таблице с помощью кнопки Отчет (Summary):
Способ 2. Макрос подбора
В этом способе всю работу делает макрос, который тупо перебирает случайные комбинации чисел, пока не наткнется на нужную сумму в пределах разрешенной погрешности. Добавлять столбец с нулями и единичками и формулы в этом случае не нужно.
Для использования макроса нажмите сочетание Alt+F11, в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert — Module и скопируйте туда этот код:
Аналогично первому способу, запуская макрос несколько раз, можно получать разные наборы подходящих чисел.
Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты — от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.
Уровень 0. Защита от ввода некорректных данных в ячейку
Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные — Проверка (Data — Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
- Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие — нет, можно воспользоваться этим макросом.
- Для включения защиты текущего листа в Excel 2003 и старше — выберите в меню Сервис — Защита — Защитить лист(Tools — Protection — Protect worksheet) , а в Excel 2007 и новее — нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис — Защита — Разрешить изменение диапазонов (Tools — Protection — Allow users to change ranges) :
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать «в своем огороде».
Уровень 3. Защита листов книги
Если необходимо защититься от:
- удаления, переименования, перемещения листов в книге
- изменения закрепленных областей («шапки» и т.п.)
- нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки «плюс/минус»)
- возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel
то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или — в старых версиях Excel — через меню Сервис — Защита — Защитить книгу (Tools — Protection — Protect workbook) :
Уровень 4. Шифрование файла
При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл — Сохранить как (File — Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис — Общие параметры (Tools — General Options) . В появившемся окне мы можем ввести два различных пароля — на открытие файла (только чтение) и на изменение:
Читайте также:
- Обнаружен неожиданный конец файла фотошоп
- Обмен crm и 1с
- Selenium развернуть окно браузера python
- Pc300 nvme sk hynix 256gb прошивка
- Reolink программа для андроид
У меня есть упражнение по производственному программированию, которое я должен решить с помощью решателя Excel. Однако я не могу установить целочисленное ограничение для определенной ячейки, которая должна быть только целой.
Я приложил минимальный пример к этому посту, структура выглядит следующим образом:
Существует 6 продуктов, в каждом из которых есть количество для изготовления на заказ и для производства, сумма этих двух и соответствующая маржинальная прибыль. Общая маржинальная прибыль рассчитывается как произведение общего количества каждого продукта на сумму его маржинальной прибыли.
Теперь для решателя я хочу максимизировать общую маржу вклада, изменяя значения для каждого продукта на складе и значения для некоторых продуктов (есть еще некоторые ограничения, которые не относятся к этой проблеме). ).
Теперь я уже установил решатель на место возникновения проблемы, и как только я пытаюсь добавить целочисленное ограничение в ячейку «сделать на склад», где ячейка «сделать на заказ» над ней должна измениться, я получаю ошибку Msgstr «Ссылка на ячейку целочисленного ограничения должна включать только переменные ячейки.» Я не понимаю почему, потому что ячейка (в данном случае F3) помечена как переменная ячейка. Это ошибка в Солвере, или я что-то упустил?
Минимальный пример: ссылка
I recently encountered the same problem. Even without having the FormulaText at the end of the integer constraint.
This should work:
Sub Solve()
SolverReset
SolverOk SetCell:="$N$95", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$87:$K$93"
SolverAdd CellRef:="$C$87:$K$93", Relation:=4
SolverAdd CellRef:="$C$87:$K$93", Relation:=1, FormulaText:="$C$48:$K$54"
SolverAdd CellRef:="$L$87:$L$93", Relation:=1, FormulaText:="$M$87:$M$93"
SolverAdd CellRef:="$C$87:$K$93", Relation:=3, FormulaText:="0"
SolverSolve UserFinish:=True
End Sub
When applying an integer constraint (SolverAdd) to cells that are not part of the decision variables (ByChange), the solver prompts:
«Integer Constraint Cell Reference must include only Variable Cells.»
This is not the case in your example, but the setting the objective function still plays a role. I assume that if you add the integer constraint before you add the SolverOk, the solver does not know the decision variables (ByChange) yet and therefore does not include the integer constraint.
I recently encountered the same problem. Even without having the FormulaText at the end of the integer constraint.
This should work:
Sub Solve()
SolverReset
SolverOk SetCell:="$N$95", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$87:$K$93"
SolverAdd CellRef:="$C$87:$K$93", Relation:=4
SolverAdd CellRef:="$C$87:$K$93", Relation:=1, FormulaText:="$C$48:$K$54"
SolverAdd CellRef:="$L$87:$L$93", Relation:=1, FormulaText:="$M$87:$M$93"
SolverAdd CellRef:="$C$87:$K$93", Relation:=3, FormulaText:="0"
SolverSolve UserFinish:=True
End Sub
When applying an integer constraint (SolverAdd) to cells that are not part of the decision variables (ByChange), the solver prompts:
«Integer Constraint Cell Reference must include only Variable Cells.»
This is not the case in your example, but the setting the objective function still plays a role. I assume that if you add the integer constraint before you add the SolverOk, the solver does not know the decision variables (ByChange) yet and therefore does not include the integer constraint.
I would like a constraint for a formula so it can only return an even number.
For example:
A formula in cell A1, *SUM(A2+A3)/2* should only return a even number.
MDaniyal
1,0973 gold badges13 silver badges29 bronze badges
asked Apr 1, 2016 at 11:09
4
You can use the MOD function:
In another cell, say A2 have the formula =MOD(A1,2) and constrain it to be 0.
Another possibility is to create a new changing cell (say B1) and constrain it to be an integer, and then add a constraint that forces A1 = 2*B1. The new changing cell will have no other role to play in the model.
answered Apr 1, 2016 at 11:19
John ColemanJohn Coleman
51.1k7 gold badges51 silver badges116 bronze badges
8
I would like a constraint for a formula so it can only return an even number.
For example:
A formula in cell A1, *SUM(A2+A3)/2* should only return a even number.
MDaniyal
1,0973 gold badges13 silver badges29 bronze badges
asked Apr 1, 2016 at 11:09
4
You can use the MOD function:
In another cell, say A2 have the formula =MOD(A1,2) and constrain it to be 0.
Another possibility is to create a new changing cell (say B1) and constrain it to be an integer, and then add a constraint that forces A1 = 2*B1. The new changing cell will have no other role to play in the model.
answered Apr 1, 2016 at 11:19
John ColemanJohn Coleman
51.1k7 gold badges51 silver badges116 bronze badges
8
Чтобы
получить целочисленное решение задачи
линейного программирования с помощью
программы Excel,
к
обычным ограничениям задачи добавляются
условия целочнсленности. Это можно
сделать в окне Добавление
ограничения, вид
которого для нашего примера (см. раздел
1.1) показан на рис. 2.2.
В
поле
Ссылка
на ячейки (Ссылка на ячейку) задается
диапазон ячеек содержащих значения
переменных, а в раскрывающемся списке
вместо знака выбирается один из следующих
пунктов:
-
цел
—
целое число;
в
бин в
Excel
2010
(или двоич
в
предыдущих версиях)—
бинарное
(двоичное) число 0 или 1;
-
раз
(только
в Excel
2010)
— разные целые числа от 1 до N
(где
N
—
число переменных).
При
этом в поле Ограничение
автоматически
появляется слова: целое,
бинарное
(двоичное) или
Все
разные.
Если
переменные в модели — разных типов
(целые, бинарные, разные целые,
вещественные), можно задать несколько
условий целочнсленности, для каждого
типа переменных в отдельности.
Поскольку
нахождение целочисленного решения
зачастую является довольно длительной
процедурой, предусмотрена возможность
ее ускорения. Для этого используются
следующие параметры, задаваемые в Excel
2010
в окне Параметры
(см.
рис. 1.26), а в предыдущих версиях — в окне
Параметры
поиска решения (см.
рис. 1.27) (названия параметров, используемые
в предыдущих версиях, даны в скобках):
-
Игнорировать
целочисленные ограничения (только
в Excel
2010)
—
Добавление
ограничения
Ссылка
на ячеикм:
ГШ
Ограничение:
ок
Отмена
Добавить
Рис.
2.2. Добавление требования целочнсленности
переменных
флажок,
установка которого приводит к решению
задачи без учета условий целочисленное™.
-
Целочисленная
оптимальность (Допустимое отклонение)—
указывает, на сколько процентов
полученное значение целевой функции
может отличаться от оптимального
(по умолчанию в Excel
2010
— 1%, а в предыдущих версиях — 5% ). Для
ускорения можно увеличить это значение.
Если же необходимо получить точное
решение, то следует установить этот
параметр равным 0%, но это может значительно
увеличить время оптимизации, -
Максимальное
время —
позволяет задать в секундах максимальное
время, которое отводится на поиск
решения (значение по умолчанию в Excel
2010
не задано, а в предыдущих версиях— 100
секунд). -
Число
итераций (Предельное число итераций)
—
позволяет определить максимальное
количество итераций при поиске решения
(значение по умолчанию в Excel
2010
не задано, а в предыдущих версиях—
100). Для целочисленных моделей данный
параметр указывает максимальное
количество итераций для каждой
подзадачи. -
Максимальное
число подзадач (только
в Excel
2010)—
позволяет задать максимальное количество
подзадач при целочисленном решении. -
Максимальное
число допустимых решений (только
в Excel
2010)
— позволяет определить максимальное
количество рассматриваемых допустимых
целочисленных решений.
Если
вы задали некоторые из перечисленных
предельных значений, то поиск решения
будет продолжаться до тех пор, пока
какое-нибудь нз них не будет достигнуто.
Тогда появится окно с предложением
остановить вычисления или продолжить.
Если вы решили продолжить вычисления,
достигнутое предельное значение
отменяется н расчеты продолжаются до
достижения следующего нз заданных
предельных значений. Если вы выбрали
вариант остановки вычислений, появится
окно с сообщением о том, что решение
остановлено по запросу пользователя и
на листе окажутся результаты, достигнутые
на этот момент. Прервать вычисления
можно также нажатием клавиши Esc.
При
этом также появляется окно с предложением
остановить пли продолжить вычисления.
Остальные
ограничения и параметры поиска решения
— те же, что и для обычной задачи линейного
программирования (см. раздел 1.3). Выполнив
вычисления с условием целочнсленности,
показанным на рис. 2.2, вы получите
целочисленное решение нашей задачи (5;
1; 3, 4).
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
21.03.201614.11 Mб12МЕТОДИЧКА ЭТМ №2.rtf
- #
- #
- #
- #
- #
- #
- #
- #
|
0 / 0 / 0 Регистрация: 22.05.2012 Сообщений: 5 |
|
|
22.05.2012, 14:43 [ТС] |
4 |
|
Суть задачи — составление модели управления активами и пассивами по срокам погашения. Целевая функция: F(x)=0,2*X11+0,46*X12+0,06*X13+0,16*X14+0,1*X15+0, 02*X16 0 |
28310859 , где X12,13…16 -активы с разными сроками погашения, а коэффициенты — это удельные веса активов. имеются ограничения:|
Поиск решения, бинарные значения |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
[/p.s.]
Чтобы
получить целочисленное решение задачи
линейного программирования с помощью
программы Excel,
к
обычным ограничениям задачи добавляются
условия целочнсленности. Это можно
сделать в окне Добавление
ограничения, вид
которого для нашего примера (см. раздел
1.1) показан на рис. 2.2.
В
поле
Ссылка
на ячейки (Ссылка на ячейку) задается
диапазон ячеек содержащих значения
переменных, а в раскрывающемся списке
вместо знака выбирается один из следующих
пунктов:
-
цел
—
целое число;
в
бин в
Excel
2010
(или двоич
в
предыдущих версиях)—
бинарное
(двоичное) число 0 или 1;
-
раз
(только
в Excel
2010)
— разные целые числа от 1 до N
(где
N
—
число переменных).
При
этом в поле Ограничение
автоматически
появляется слова: целое,
бинарное
(двоичное) или
Все
разные.
Если
переменные в модели — разных типов
(целые, бинарные, разные целые,
вещественные), можно задать несколько
условий целочнсленности, для каждого
типа переменных в отдельности.
Поскольку
нахождение целочисленного решения
зачастую является довольно длительной
процедурой, предусмотрена возможность
ее ускорения. Для этого используются
следующие параметры, задаваемые в Excel
2010
в окне Параметры
(см.
рис. 1.26), а в предыдущих версиях — в окне
Параметры
поиска решения (см.
рис. 1.27) (названия параметров, используемые
в предыдущих версиях, даны в скобках):
-
Игнорировать
целочисленные ограничения (только
в Excel
2010)
—
Добавление
ограничения
Ссылка
на ячеикм:
ГШ
Ограничение:
ок
Отмена
Добавить
Рис.
2.2. Добавление требования целочнсленности
переменных
флажок,
установка которого приводит к решению
задачи без учета условий целочисленное™.
-
Целочисленная
оптимальность (Допустимое отклонение)—
указывает, на сколько процентов
полученное значение целевой функции
может отличаться от оптимального
(по умолчанию в Excel
2010
— 1%, а в предыдущих версиях — 5% ). Для
ускорения можно увеличить это значение.
Если же необходимо получить точное
решение, то следует установить этот
параметр равным 0%, но это может значительно
увеличить время оптимизации, -
Максимальное
время —
позволяет задать в секундах максимальное
время, которое отводится на поиск
решения (значение по умолчанию в Excel
2010
не задано, а в предыдущих версиях— 100
секунд). -
Число
итераций (Предельное число итераций)
—
позволяет определить максимальное
количество итераций при поиске решения
(значение по умолчанию в Excel
2010
не задано, а в предыдущих версиях—
100). Для целочисленных моделей данный
параметр указывает максимальное
количество итераций для каждой
подзадачи. -
Максимальное
число подзадач (только
в Excel
2010)—
позволяет задать максимальное количество
подзадач при целочисленном решении. -
Максимальное
число допустимых решений (только
в Excel
2010)
— позволяет определить максимальное
количество рассматриваемых допустимых
целочисленных решений.
Если
вы задали некоторые из перечисленных
предельных значений, то поиск решения
будет продолжаться до тех пор, пока
какое-нибудь нз них не будет достигнуто.
Тогда появится окно с предложением
остановить вычисления или продолжить.
Если вы решили продолжить вычисления,
достигнутое предельное значение
отменяется н расчеты продолжаются до
достижения следующего нз заданных
предельных значений. Если вы выбрали
вариант остановки вычислений, появится
окно с сообщением о том, что решение
остановлено по запросу пользователя и
на листе окажутся результаты, достигнутые
на этот момент. Прервать вычисления
можно также нажатием клавиши Esc.
При
этом также появляется окно с предложением
остановить пли продолжить вычисления.
Остальные
ограничения и параметры поиска решения
— те же, что и для обычной задачи линейного
программирования (см. раздел 1.3). Выполнив
вычисления с условием целочнсленности,
показанным на рис. 2.2, вы получите
целочисленное решение нашей задачи (5;
1; 3, 4).
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
21.03.201614.11 Mб13МЕТОДИЧКА ЭТМ №2.rtf
- #
- #
- #
- #
- #
- #
- #
- #
Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.
«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.
Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»
Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».
После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке
Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.
Пример первый.
Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.
То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.
В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).
Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.
Под «1» обозначена наша целевая ячейка. Она может быть только одна.
«2» — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.
«3» — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.
«4» — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.
«5» — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.
Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.
Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).
Но в нашем примере ограничение может быть лишь одно – положительный коэффициент. Задать его, конечно, можно несколькими способами – либо используя «Добавить» (что называют «явно указать ограничение»), либо просто отметить действующей функцию «Сделать переменные без ограничений неотрицательными». Это можно сделать в надстройке «Поиск решения», нажав на кнопочку «Параметры».
Кстати, после подтверждения параметров и запуска программы (кнопочка «Выполнить»), вы сможете в таблице просмотреть полученный результат. Тогда программа продемонстрирует окошко «результатов поиска».
Если продемонстрированный результат полностью вам подходит, тогда останется только вновь подтвердить его (кнопочка «ОК»), что зафиксирует результат в вашей таблице. Если же что-то в расчетах вас не устраивает, то необходимо отменить результат (кнопочка «Отмена»), вернуться к предыдущему состоянию нашей таблицы и исправить допущенные ошибки.
Правильное решение задачи примера должно получиться вот таким
Очень важно — чтобы получить правильный результат даже при малейшем изменении исходных данных необходимо перезапустить «Поиск решений».
Чтобы более подробно взглянуть на то, как действует данная программа, давайте разберем еще один пример.
Допустим, вы являетесь владельцем крупного мебельного предприятия и необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Вы производите только книжные полки, при этом всего двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).
Модель «А» требует 3 м3 досок, а модель «В» — на 1 м3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.
Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?
Поскольку порядок действия известен, то начинаем создавать необходимую нам таблицу с данными и формулами. Расположение ячеек, как и ранее, вы можете установить на свое усмотрение. Или же воспользоваться нашим
Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.
Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).
Конечно, в этом случае можно было не указывать адреса ячеек, а напрямую прописать необходимые цифровые значения, однако если использовать адреса, то изменения ограничений можно будет проводить и в таблице, что поможет рассчитывать прибыль этого предприятия в будущем, при смене исходных данных.
Активируем программу, и она подготавливает решение.
Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было
Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».
Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.
Так что будьте предельно внимательны в будущем.
Третий и, пожалуй, последний пример. Попробуем минимизировать затраты транспортной компании используя поиск решений в Excel 2010.
Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.
Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».
Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.
Серые ячейки нашей таблицы содержат формулы суммы по столбцам и строкам, а целевая ячейка – формула для общего подсчета затраты на доставку груза. Запускаем наш «Поиск решения» и вносим необходимые настройки
После этого приступаем к поиску решения этой задачки
Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным
Напоследок осталось сказать только о выборе метода решения. И если задачка действительно очень сложная, то чтобы получить необходимый результат, скорее всего, понадобиться подобрать необходимый метод решения.
Вот и все по данному вопросу.
Мы выполнили поиск решений в Excel 2010 — для решения сложных задач
|
0 / 0 / 0 Регистрация: 22.05.2012 Сообщений: 5 |
|
|
22.05.2012, 14:43 [ТС] |
4 |
|
Суть задачи — составление модели управления активами и пассивами по срокам погашения. Целевая функция: F(x)=0,2*X11+0,46*X12+0,06*X13+0,16*X14+0,1*X15+0, 02*X16
0 |
dremastar
Пользователь
Сообщений: 25
Регистрация: 07.11.2013
Добрый день, уважаемые знатоки.
Столкнулся с трудностью которую самостоятельно не могу решить.
Во вложении задача по поиску минимальной стоимости Гигакалории в зависимости от включённого оборудования.
Поиск решения минимизирует стоимость, однако работает только в том случае, если я в ручном режиме указываю какое оборудование включено, а какое выключено.
Как изменить постановку задачи, чтобы инструмент Поиск решения сам находил оптимальные сочетания оборудования при необходимой производительности??
Заранее благодарен…