В этой статье я расскажу о решении задач линейного программирования в Google Таблицах и Microsoft Excel. С данными сервисами эти задачи сводятся к описанию условий и паре кликов кнопкой мыши.
В Microsoft Excel инструмент решения оптимизационных задач находится по адресу: Данные->Анализ->Поиск решения. В Google Таблицах потребуется установить дополнение (меню Дополнения->Установить дополнение) - Solver (первое в списке).
Построим схему для решения некоторых типовых задач линейного программирования (подробнее читай здесь). Она должна содержать ячейки для искомых переменных, функции Z, которую надо максимизировать или минимизировать, а также набора ограничений. Сформируем следующий макет:
Строка "Переменные" будет содержать названия искомых переменных, которые входят в функцию Z, "Коэффициенты" - заданные в условиях количественные показатели переменных в Z. Под строкой "Ограничения" в группе столбцов "Первоначальные условия" задаются коэффициенты при переменных в неравенствах или равенствах, а также свободный член, которые в агрегированном виде (с использованием формул) также помещаются в столбцах "Левая часть" и "Правая часть".
Рассмотрим задачу. Мебельная фабрика производит столы и кровати по цене 6 и 8 руб. При этом затрачиваются три вида ресурсов: древесина 1 породы - 0.2 на стол и 0.1 на кровать, древесина второй породы - 0.1 на стол и 0.3 на кровать, человекочасы - 1,2 на стол и 1,5 на кровать. Общее количество ресурсов в нашем распоряжении - 40 древесины 1 породы, 60 - второй и 371,4 человекочасов. Требуется определить, сколько столов и кроватей сможет изготовить фабрика, чтобы прибыль от реализации была максимальной.
Зададим функцию максимизации прибыли- 6*X1 + 8*X2, где X1 и X2 количество произведенных столов и кроватей. Ограничения будут иметь схожий вид - a*X1 + b*X2 <= c, где a и b - показатели затрат ресурса, а c - общее его количество. Также можно задать условие на неотрицательность X1 и X2.
В результате наша таблица примет вид:
где Z = СУММПРОИЗВ(B21:C21;B22:C22, а строки в столбце левая часть:
- СУММПРОИЗВ(D26:E26;B22:C22),
- СУММПРОИЗВ(D27:E27;B22:C22),
- СУММПРОИЗВ(D28:E28;B22:C22).
В расширении Solver параметры будут выглядеть так:
После нажатия кнопки Solve в заданных ячейках появятся соответствующие значения. Маленькая неприятность заключается в том, что инструмент при подборе параметров в качестве отделителя целой и дробной части использует точку, что может привести к ошибке или некорректной интерпретации значений в формулах. Вместе с тем после замены таких точек на запятые, все сходится:
