Технологии решения систем эконометрических уравнений

Решение систем уравнений с помощью метода обратной матрицы. Система линейных уравнений может быть представлена в матричном виде — А*Х = В. В случае, когда число уравнений т в системе равно числу неизвестных п, решение системы можно найти методом обратной матрицы с помощью формулы X = А-1*В, где А-1 — матрица, обратная к А. Решение систем линейных уравнений методом обратной матрицы рассмотрим на следующем примере.

Пример. 9.1. Решить систему уравнений, заданную матрицами и .

Решение

• • Предварительно назовем диапазоны расположения элементов обеих матриц именами, А и В.
• • Выделим область с размерностью в две строки и один столбец для помещения результата решения системы линейных уравнений.
• • В выделенный диапазон введем формулу:

• Нажмем комбинацию клавиш Ctrl + Shift + Enter, в выделенном диапазоне будет получен результат (рис. 9.9).

Рис. 9.9. Решение систем уравнений с помощью метода обратной матрицы.

Решение систем линейных уравнений методом наименьших квадратов. Решение системы линейных уравнений, когда количество уравнений равно количеству неизвестных (т = п), было рассмотрено выше. Однако это является частным случаем, и возможны случаи, когда m не равно п, а именно: т > п, т < п.

В случае, когда m < п и система линейных уравнений является совместной, она не определена и имеет бесконечное множество решений.

Если т > п и система линейных уравнений совместна, то матрица А имеет по крайней мере m-n линейно независимых строк. В данном случае решение можно получить выбором п любых линейно независимых уравнений. Использовав известную нам формулу X = А-1*В, можно найти решение данной системы, но на практике применяется более общий подход — метод наименьших квадратов. В этом случае обе части уравнения умножаются на произведение матриц AT*А (AТ — транспонированная матрица А). Затем обе части умножаются на выражение (АT*А)-1, при условии что это матрица существует и система определена. После некоторых математических преобразований формула решения системы линейных уравнений методом наименьших квадратов примет следующий вид:

Технологию решения систем линейных уравнений методом наименьших квадратов рассмотрим на следующем примере.

Пример 9.2. Решить систему линейных уравнений.

Решение

• • Введем значения элементов матриц А и В в диапазоны рабочего листа, обозначим эти области именами соответственно, А и В.
• • Транспонируем матрицу А, для чего выделим диапазон ячеек размерностью 3 ´ 2 и с помощью формулы = ТРАНСП (А) вычислим транспонированную матрицу АT.
• • Далее вычислим произведение АT* В с помощью функции МУМНОЖ (АT; В), предварительно проверив условие умножения матриц.
• • Следующим шагом является вычисление произведения матриц АТ*А.
• • Далее вычислим матрицу, обратную матрице, полученной в ходе выполнения предыдущего пункта, использовав встроенную функцию МОБРАT*А).
• • Последним шагом в данном примере является вычисление произведения матрицы (в соответствии с правилами умножения матриц), полученной в ходе выполнения предыдущего пункта, и матрицы, полученной в ходе выполнения пункта 3, и так мы получим выражение МУМНОЖ (МОБР (АT*А); МУМНОЖ (АT; В)), что и будет являться решением данной системы линейных уравнений (рис. 9.10).

Рис. 9.10. Решение систем линейных уравнений методом наименьших квадратов.

Инструменты для решения сложных аналитических задач

При решении различных финансово-экономических задач часто приходится заниматься проблемой подбора одного значения путем изменения другого. Для этой цели весьма эффективен инструмент MS Excel «Подбор параметра». Данный инструмент является таким средством решения задач анализа данных, когда путем изменений (перебора) значения одного из параметров достигается заданное значение исследуемой функции (критерия оптимальности). Этот инструмент относится к средствам анализа «что если» .

Чтобы найти значение параметра, удовлетворяющее установленному значению критерия оптимальности, необходимо активизировать формулу оптимизируемой функции, затем на вкладке Данные в группе Работа с данными нажать на кнопку Анализ «что-если» (рис. 9.11).

Рис. 9.11. Работа с данными (Анализ «что если»).

В появившемся диалоговом окне Подбор параметра (рис. 9.12) необходимо установить:

• • ссылку на ячейку с расчетной формулой (поле Установить в ячейке);
• • предполагаемое значение критерия оптимальности в качестве частного экстремума (ноле Значение);
• • ссылку на ячейку с изменяемым параметром (иоле Изменяя значение ячейки).

После этого нажать ОК.

Рис. 9.12. Окно Подбор параметра.

Пример 9.3. Необходимо определить, каким должен быть курс доллара, чтобы на 1637 руб. можно было бы приобрести 60 долларов.

Решение

Составляем уравнение вида Х*60 = 1637, где X — искомый курс доллара.

В MS Excel строим модель:

• • ячейка A2 должна содержать значение курса доллара (предварительно ячейка пустая);
• • в ячейку В2 вводим формулу: =А2*60, в результате получаем 0;
• • выполняем команду: вкладка Данные/группа Работа с данными/кнопка Анализ «что если» /Подбор параметра.

В появившемся окне указываем: установить в ячейке В2 значение 1637, изменяя значение ячейки А2 (см. рис. 9.12).

В результате получим значение 27,2833 (рис. 9.13).

Рис. 9.13. Результат решения примера 9.3.

Пример 9.4. Рассчитать прибыль по данным и формулам таблицы (рис. 9.14). С помощью подбора параметра определить, какое количество изделий нужно реализовать, чтобы получить прибыль 50 000 руб.

Рис. 9.14. Таблица «Расчет прибыли» .

Решение

Устанавливаем курсор в ячейку В7.

Выполняем команду: вкладка Данные/группа Работа с данными/кнопка Анализ «что если» /Подбор параметра.

В появившемся окне указываем: установить в ячейке В7 значение 50 000, изменяя значение ячейки ВЗ (рис. 9.15).

В результате получим таблицу и искомое значение 233,43 (рис. 9.16).

Рис. 9.15. Подбор параметра для примера 9.4.

Рис. 9.16. Таблица «Расчет прибыли» после подбора параметра.

Действенность рассмотренного инструмента «Подбор параметра» при решении задач, когда интересует не конкретный результат, а требуется отыскать наилучшее, оптимальное решение (например, дающее максимально возможную прибыль при минимальных затратах), удовлетворяющее при этом целому ряду дополнительных условий на значения используемых параметров, существенно ограничена. В этом случае прибегают к помощи надстройки, называемой «Поиск решения». Ее применяют для решения различных уравнений и систем уравнений, задач линейного и нелинейного программирования, а также для решения задач оптимизации, определения экстремумов функций в MS Excel.

Чтобы воспользоваться надстройкой «Поиск решения», ее необходимо установить и активировать. Для этого выполняется команда: вкладка Файл/раздел Параметры/Надстройки/Перейти. В появившемся диалоговом окне Надстройки (рис. 9.17) следует установить флажок в поле Поиск решения и нажать кнопку ОК.

На вкладке Данные в группе Анализ появится кнопка для надстройки «Поиск решения» (рис. 9.18).

Для того чтобы обратиться к надстройке «Поиск решения» для нахождения оптимальных значений функции F(x), необходимо определиться с терминологией. Модель оптимизации состоит из трех частей: целевая ячейка, изменяемые ячейки и ограничения (рис. 9.19).

Рис. 9.17. Диалоговое окно Надстройки.

Рис. 9.18. Вкладка Данные/группа Анализ/кнопка Поиск решения.

Рис. 9.19. Окно Параметры поиска решения.

Задачи, которые можно решать с помощью инструмента «Поиск решения», в общей постановке формулируются так:

Найти.

такие, что.

при ограничениях.

Целевая ячейка — это цель. В этой ячейке помещается формула, описывающая целевую функцию. Нужно получить либо минимальное, либо максимальное, либо конкретное значение целевой ячейки.

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

Ограничения — условия, которым должны удовлетворять значения изменяемых ячеек. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. Ограничения не являются обязательными. Правильная формулировка ограничений является наиболее ответственной частью при формировании модели.

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

Перед нахождением решения необходимо выбрать метод решения. MS Excel 2010 предлагает три метода: поиск решения нелинейных задач методом обобщенного понижающего градиента (ОПГ), поиск решения линейных задач симплекс-методом, эволюционный поиск решения^[1].

Дополнительно можно указать условия решения задачи, определяющие относительную погрешность, время вычислений, предельное число итераций и др. Эти значения указывают, нажав на кнопку Параметры в окне Параметры поиска решения (см. рис. 9.19). Значения и состояния элементов, заданных в параметрах по умолчанию, подходят для решения большинства задач.

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

Рис. 9.20. Окно Результаты поиска решения.

Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в ноле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение нс найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.

Отчет Результаты. Используется для создания отчета, состоящего из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о наложенных ограничениях.

Отчет Устойчивость. Используется для создания отчета, содержащего сведения о чувствительности решения к малым изменениям в формуле (поле Оптимизировать целевую функцию диалогового окна Параметры поиска решения) или в формулах ограничений. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел.

Отчет Пределы. Используется для создания отчета, состоящего из целевой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел.

Чтобы сохранить значения, помещенные в ячейку для переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.

Пример 9.5 (применение инструмента «Поиск решения» для решения систем линейных уравнений). Пусть дана следующая система линейных уравнений:

Решить данную систему с помощью инструмента «Поиск решения» .

Решение

Выпишем основную матрицу системы и вектор свободных членов.

Определим область для переменных х, у и z.

Выполним решение системы линейных уравнений с использованием соответствующих областей переменных.

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

Область расположения переменных (x, у, z) выделим в области изменяемых ячеек в инструменте «Поиск решения» и найдем решение данной системы (рис. 9.21).

Рис. 9.21. Решение систем линейных уравнений с помощью инструмента «Поиск решения» .

• [1] Для получения дополнительных сведений об этих методах см. материалы сайта solver.com