Пр.р. Поиск решения

Практическая работа по Табличному редактору. Поиск решения.

Большинство задач, решаемых с помощью электронной таблицы, предполагают нахождение искомого результата по известным исходным данным. Но в Excel/Calc есть инструменты, позволяющие решить и обратную задачу: подобрать исходные данные для получения желаемого результата.

Одним из таких инструментов является Поиск решения, который особенно удобен для решения так называемых "задач оптимизации".

Сервис -> Решатель / Сервис -> Поиск решения… / Данные -> Поиск решения

Пример 1. Распределение премии

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

Первым делом создаём таблицу с исходными данными и формулами, с помощью которых должен быть получен результат. В нашем случае результат - это суммарная величина премии. Очень важно, чтобы целевая ячейка (С8) посредством формул была связана с искомой изменяемой ячейкой (Е2). В примере они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника (С2:С7).

Итого – сумма премий, столбец премий – оклад * коэффициент – вводим формулы в лист

Теперь запускаем Поиск решения и в открывшемся диалоговом окне устанавливаем необходимые параметры.

(2) Целевая ячейка, в которой должен получиться желаемый результат. Целевая ячейка может быть только одна.

(3) Варианты оптимизации: максимальное возможное значение, минимальное возможное значение или конкретное значение. Если требуется получить конкретное значение, то его следует указать в поле ввода (указываем 100 000)

(4) Изменяемых ячеек может быть несколько: отдельные ячейки или диапазоны. Собственно, именно в них Excel перебирает варианты с тем, чтобы получить в целевой ячейке заданное значение (указываем ячейку с итогом)

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

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

(6) Нажать кнопку Решить

После нажатия кнопки Найти решение (Выполнить) Вы уже можете видеть в таблице полученный результат. При этом на экране появляется диалоговое окно Результаты поиска решения.

Нажимаем ОК и редактируем формат ячеек.

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

Пример 2. Мебельное производство (максимизация прибыли)

Фирма производит две модели А и В сборных книжных полок.

Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.

Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В - 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.

Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В - 30 мин. в неделю можно использовать 160 ч машинного времени.

Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В - 120 руб. прибыли?

Порядок действий нам уже известен.

Сначала создаем таблицы с исходными данными и формулами.

Задаем функции для вычисления в ячейках B12 (выручка), B15, B16 (затраченные ресурсы)

И выбираем Поиск решения с заданными параметрами:

Итогом решения должны получится целочисленные значения:

Задание 3. Крестьянин

Крестьянин на базаре за 100 тыс.рублей купил 100 голов скота. Бык стоит 10 тыс.рублей, корова 5 тыс.рублей, телёнок 500 рублей. Сколько быков, коров и телят купил крестьянин?

Задание 4. Лесопилка

На лесопилку поступают доски длиной 10 м. По контракту лесопилка должна поставить клиенту не менее 100 досок длиной 5 м, не менее 200 досок длиной 4 м и не менее 300 досок длиной 3 м. Как работникам лесопилки выполнить условия контракта, разрезав наименьшее количество досок?

Подсказка: Составьте модель задачи. Найдите все возможные способы распила бревен длиной 10 м на доски длиной 5, 4 и 3 м

Задание 5. Цилиндр 1

Какими должны быть радиус основания r и высота h цилиндра объёмом V=1 м3, чтобы диаметр основания цилиндра как можно меньше отличался от его высоты?

Задание 6. Цилиндр 2

Изготовляется полый цилиндр с радиусом основания r см и высотой h см, одно из оснований отсутствует. Минимизируйте общую площадь поверхности цилиндра для объёма 1.2 м3. Допустимые значения величин r и h - от 0.3 до 1 м

Задание 7. Цилиндр 3

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

Какими должны быть значения r и h, чтобы минимизировать длину сварного шва для требуемого объёма цилиндра 1 м3?

Задание 8. Цилиндр 4

Найдите радиус основания r и высоту h цилиндра максимального объёма, площадь полной поверхности которого составляет 10 м2. Толщиной стенок цилиндра можно пренебречь

Задание 9. Равносторонний треугольник

Найти такой равносторонний треугольник, для которого выполняется соотношение P/S=S/P, где S - площадь, P - периметр. Треугольник задан сторонами A, B, C.