Тема:

Подбор параметра выполняется с помощью команды меню ДАННЫЕ/АНАЛИЗ «ЧТО-ЕСЛИ»/ ПОДБОР ПАРАМЕТРА Функция Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.

Пример: пусть надо решить уравнение вида:

Формулировка задачи на листе электронной таблицы:

Для получения решения данного уравнения нужно использовать функцию Подбор параметра и выполнить следующую последовательность действий: 1) выполнить команду Данные/Анализ «что-если»/Подбор параметра ; 2) заполнить диалоговое окно Подбор параметра.

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

Матричная алгебра тесно связана с линейными функциями и с линейными ограничениями, в связи, с чем находит себе применение в различных экономических задачах Особое отношение к матричной алгебре в экономике появилось после создания моделей типа «Затраты - Выпуск», где с помощью матриц технологических коэффициентов объясняется уровень производства в каждой отрасли через связь с соответствующими уровнями во всех прочих отраслях. ТРАНСП – транспонирование исходной матрицы; МОПРЕД – вычисление определителя квадратной матрицы; МОБР – вычисление матрицы обратной к данной; МУМНОЖ – нахождение матрицы, являющейся произведением двух матриц. Электронная таблица EXCEL имеет ряд встроенных функций для работы с матрицами:

Пример: найти матричное выражение: Y=(FH -1 )/ 29 +K

Найдем матрицу обратную к матрице Н

Умножим матрицы F и H -1

Результат умножим на число (1/29)

Сложим полученную матрицу с матрицей К

Вычислим определитель матрицы : 1.Определим исходную матрицу. 2.Определим место под результат. 3.Обратимся к мастеру функций, найдем функцию МОПРЕД, выполним постановку задачи.

Системы линейных алгебраических уравнений -12X 1 +12X 2 +23X 3 +6X 4 =120 -3X X 2 -3X 3 +X 4 = X 1 -3X 2 -51X 3 -73X 4 = X 1 -6X 2 +4X 3 -13X 4 =-316 Пример: рассчитать определитель системы, пользуясь функцией МОПРЕД.

матричное решение уравнения выглядит так: Х=А -1 В, где А -1 – матрица обратная к исходной матрице А.

Средство подбора параметров поддерживает только одно входное значение переменной. Если необходимо определить несколько входных значений, например размер кредита и сумму ежемесячного платежа, следует использовать вместо этого надстройку «Поиск решения» Программа Поиск решения позволяет получить результат на основе изменения значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия - ввести ограничения. При поиске решения, также как и при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями.

Многие проблемы прогнозирования, проектирования и производства сводятся к широкому классу задач оптимизации. Решение задач такого вида может быть осуществлено EXCEL с помощью инструмента Поиск решения.

Пример: предположим, что мы решили производить два вида объективов А и В. Объектив вида А состоит из 3-х линзовых компонентов, вид В – из 4-х. За неделю можно изготовить не более 1800 линз. На сборку объектива вида А требуется – 15 минут, вида В – 30 минут. Рабочая неделя для 4 сотрудников составляет 160 часов. Сколько объективов А и В надо изготовить, чтобы получить максимальную прибыль, если объектив вида А стоит 3500 рублей, вида В – 4800 рублей.

Для решения этой задачи необходимо составить и заполнить таблицу:

Пример заполнения окна Поиск решения

Вид таблицы результатов решения с применением команды Поиск решения

Рассмотрим задачу линейного программирования на примере. Пример: решить задачу линейного программирования с помощью Поиска решения, показать графически область допустимых решений и целевую функцию. Найдем максимум функции F = -2x 1 + 2x 2max при ограничениях: x 1 + x x 1 + x 2 0,3 x 1 – x 2 1 x 1 + x 2 6 x 1 0 x 2 0.

Сформируем страницу электронной таблицы и постановку задачи линейного программирования в диалоговом окне Поиск решения.

После выполнения поставленной задачи получаем следующие значения переменных : Графическое решение поставленной задачи выглядит так :

Мы вступаем в этап, когда стоящие перед нами проблемы невозможно решить без применения компьютера. Использование компьютерных технологий освобождает от рутинной вычислительной работы по реализации математических методов и позволяет сконцентрировать внимание не на алгоритме вычисления, а непосредственно на анализе результатов моделирования, что заметно повышает «коэффициент полезного действия» затраченного времени.