Теория:
В реальной жизни мы часто сталкиваемся с задачами, которые невозможно решить точно с помощью простых формул. Например, уравнение смешанного типа \(x - cos(x) = 0\) не имеет простого алгебраического решения. В таких случаях на помощь приходят численные методы — подходы, позволяющие получить приближённое решение с заданной точностью путём последовательных приближений.
Другой класс задач — оптимизация. Представь, что нужно распределить ограниченные ресурсы (деньги, время, материалы) так, чтобы прибыль была максимальной, а затраты — минимальными. Здесь мы ищем не просто решение, а наилучшее решение в заданных условиях.
Электронные таблицы (в частности, LibreOffice Calc) предоставляют удобные инструменты для решения обоих классов задач без необходимости программирования.
Подбор параметра — это инструмент для решения уравнений с одним неизвестным. Он автоматически изменяет значение в одной конкретной ячейке до тех пор, пока формула в другой ячейке, зависящая от первой, не достигнет нужного результата.
Алгоритм решения уравнения \(f(x) = 0\)
Чтобы решить уравнение, нужно привести его к виду \(f(x) = 0\). Рассмотрим на примере уравнения .
Подготовка рабочего листа
В ячейку, отведённую под переменную \(x\) (например, \(A1\)), введи любое начальное приближение (например, \(1\)). Это «точка», от которой начнётся поиск корня.
В соседнюю ячейку (например, \(B1\)) введи формулу, соответствующую функции \(f(x)\). Для данного примера: .
Перейди в меню «Сервис» \(→\) «Подбор параметра».
Заполнение диалогового окна
Целевая ячейка: указываем ячейку с формулой (\($B$1\)).
Целевое значение: вводим значение, которое мы хотим получить от формулы. Так как мы решаем уравнение \(f(x)=0\), вводим \(0\).
Изменяемая ячейка: указываем ячейку, значение которой будет подбираться (\($A$1\)).
Целевое значение: вводим значение, которое мы хотим получить от формулы. Так как мы решаем уравнение \(f(x)=0\), вводим \(0\).
Изменяемая ячейка: указываем ячейку, значение которой будет подбираться (\($A$1\)).
Рис. \(1\). Подбор параметра
Анализ результата
Нажми ОК. Через мгновение появится окно с информацией о том, что решение найдено, и значением подобранного параметра.
Если решение не найдено, стоит попробовать другое начальное приближение в ячейке \(x\), так как у уравнения может быть несколько корней.
Обрати внимание!
Подбор параметра последовательно сужает отрезок поиска, пока не достигнет нужной точности. Инструмент подходит только для задач с одним изменяемым параметром.
Оптимизация — это процесс нахождения наилучшего (экстремального) значения некоторого критерия при заданных ограничениях.
Чтобы формализовать задачу оптимизации, вводятся три ключевых понятия.
Целевая функция: это формула, которую нужно максимизировать (например, прибыль, доход) или минимизировать (например, затраты, время, риск).
Пример: прибыль \(= 16*x1 + 14*x2\), где \(x1\) и \(x2\) — объёмы производства двух видов товара.
Пример: прибыль \(= 16*x1 + 14*x2\), где \(x1\) и \(x2\) — объёмы производства двух видов товара.
Управляемые переменные: это параметры, значения которых мы можем изменять, чтобы повлиять на целевую функцию. Пример: \(x1\) и \(x2\) — сколько производить сливочного и шоколадного мороженого.
Ограничения — это условия, которые накладываются на переменные или ресурсы.
Они могут быть связаны:
- с запасом ресурсов (например, молока не больше \(400~\)кг: \(0,8*x1 + 0,5*x2 ≤ 400\));
- спросом (например, спрос на шоколадное мороженое не больше \(350~\)кг: \(x2 ≤ 350\));
- неотрицательностью (например, нельзя произвести минус \(100~\)кг мороженого: \(x1 ≥ 0, x2 ≥ 0\)).
Совокупность целевой функции и ограничений называется математической моделью задачи оптимизации.
Решатель в электронных таблицах — это надстройка для решения задач оптимизации, позволяющая находить максимальное, минимальное или точное значение целевой функции путём изменения указанных ячеек с переменными с учётом заданных ограничений. В отличие от простого подбора параметра, Решатель способен одновременно управлять множеством переменных и анализировать сложные сценарии при наличии нескольких условий (например, ресурсных или технологических ограничений), что делает его незаменимым инструментом для планирования, распределения ресурсов и поиска наилучших решений в экономике, технике и других областях.