Анализ экономических зависимостей

В практике экономических исследований не всегда удается воспользоваться аналитическими зависимостями для анализа данных. Как правило, экономические данные представляются в табличном виде.

1. Аппроксимация экспериментальных данных

Аппроксимацией называется подбор аналитической формулы у = f( х) для установленной из опыта функциональной зависимости у = ср(х).

Аппроксимируемая функция у может зависеть от одной или от нескольких переменных. Рассмотрим оба случая.

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

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

На основе этих данных требуется подобрать такую функцию у = f(x), которая с точки зрения некоторого критерия оптимальности наилучшим образом описывала бы экспериментальную зависимость.

Обычно задача аппроксимации распадается на две части. Сначала устанавливают вид зависимости у = f(x) и, соответственно, вид эмпирической формулы, то есть решают, является ли она линейной, квадратичной, логарифмической или какой-либо другой. После этого определяются численные значения неизвестных параметров выбранной эмпирической формулы, для которых приближение к заданной функции оказывается наилучшим.

Для сглаживания экспериментальных зависимостей у, =ф(лс,), заданных таблично, в MS Excel используются различные функции у = f(x).

и Линейная.

• Полиномиальная.
• Логарифмическая.
• Степенная.
• Экспоненциальная.

Параметры аппроксимирующей функции подбираются так, чтобы выполнялось условие минимума среднеквадратичных отклонений (критерий оптимальности):

Z = ?[/(.*, )-ф(*,)Г -gt;min              (8.1)

і 1

где У' =ф(л:,) - экспериментальные точки (/= 1...п).

Степень точности аппроксимации экспериментальных данных в MS Excel оценивается коэффициентом детерминации (R2). Чем ближе этот коэффициент к значению 1, тем точнее приближение. Рассмотрим процедуру аппроксимации на примере.

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

Решение.

1. На основе имеющейся таблицы строим Точечную диаграмму.

Наводим курсор на одну из точек полученного графика и из контекстного меню выбираем команду: Добавить линию тренда (рис. 10.1).

2. На вкладке Тип указываем тип Логарифмическая (рис. 10.2).

В итоге мы получим аппроксимацию экспериментальных данных в виде кривой, показанной на рис. 10.4.

Как видно из рисунка, результат аппроксимации не является удовлетворительным. Для того, чтобы убедиться в правильности выбора типа аппроксимирующей функции, следует выбрать несколько разных функций для аппроксимации (трендов) и сравнить значения величин достоверности для каждого варианта тренда.[53]

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

Полученная аналитическая зависимость позволяет вычислять значения функции в дополнительных точках. Для этого в ячейку листа MS Excel можно занести полученную в результате аппроксимации формулу со ссылкой на ячейку с независимой переменной.

Полиномиальная аппроксимация

Несколько независимых переменных. В тех случаях, когда аппроксимируемая переменная у зависит от нескольких независимых переменных

V = /(.Y„ -Х2,...Хп)

используются следующие специальные функции Excel:

• ЛИНЕЙН и ТЕНДЕНЦ для аппроксимации линейных функций вида: у = а„ + c/,.v, + а2х2 + ... + апхп

и ЛГРФПРИБЛ и РОСТ для аппроксимации показательных функций вида: у = а0а{'а,'’ ...а]"

Функции ЛИНЕЙН и ЛГРФПРИБЛ имеют одинаковые параметры:

• множество наблюдаемых значений у;

и множество наблюдаемых значений , х2,...х11;

• логическое значение, которое указывает, равна ли 0 константа а0;

ш логическое значение (статистика), которое указывает, нужна ли дополнительная статистика по регрессии.

Функции ТЕНДЕНЦИЯ и РОСТ позволяют находить точки, лежащие на аппроксимирующих кривых

у = ап +о]х] + а2х2 + ... + а„хп и у = aQa^a^- ...ахп" соответственно.

Обе функции имеют одинаковые аргументы:

• множество известных значений у;
• множество известных значений х;
• новые значения х (строка или столбец новых значений для каждой независимой переменной х);
• логическое значение для константы (равна нулю или нет).

Пример 2

Датчики расположены на расстоянии 20, 50 и 100м. от источника радиоактивного излучения. Измерения интенсивности излучения проводились через 1, 5, и 10 суток после установки источника.

Найти зависимость интенсивности излучения от расстояния и времени.

Решение.

• Составим таблицу:

• Выделим свободный диапазон из 3-х столбцов и 5 строк
• Предположим, что аппроксимация имеет степенной характер и вызовем функцию ЛГРФПРИБЛ из типа Статистические.

• Укажем диапазон известных значений у и х и логическую константу, равную 1. а Нажмем сочетание клавиш CTRL+SHIFT+ENTER и в указанном диапазоне получим результат:

Здесь в первой строке расположены коэффициенты ар а2, а3, соответственно. Во второй строке — стандартные ошибки коэффициентов, в третьей - коэффициент детерминации R2 и стандартная ошибка у, в четвертой и пятой строках — другие статистические характеристики.

Таким образом, аппроксимирующее уравнение примет вид:

gt;gt; = 99,7 х 0,98А' х0,92" ,

причем точность аппроксимации R2=0,99998 достаточно высока.

Пример 3

Менеджерами книжного магазина в течение недели собирались данные о прибыли от продажи книг (у) в зависимости от числа посетителей магазина (х{) и числа покупок (х2). В результате была получена таблица:

Требуется построить эмпирическую таблицу, отражающую динамику прибыли в зависимости от числа посетителей (от 100 до 130) и числа покупок (от 15 до 25) с шагом А = 5.

Решение.

• Организуем данные на листе Excel, оставив пустой диапазон для у:

• Выделим диапазон G3:G23 для у, вызовем функцию ТЕНДЕНЦИЯ и укажем для нее параметры:

| ТЕНДЕНЦИЯ '1x7 = =ТЕНДЕНЦИЯ{0'СЭ,АЗ:В9;ЕЗ:Р23)              ~

¦ Нажмем сочетание клавиш CTRL+SHIFT+ENTER и получим результат в столбце у:

Нов_знач_х новые значения х, для которых ТРЕНД возвращает соответствующие значения у.

* {34,2800992828315 34, Возвращает значения в соответствии с линейной аппроксимацией по методу наименьших

А              В              С              1              D              Є              F              G

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

Упражнения

1. Построить функции, наилучшим образом аппроксимирующие зависимости:

2. Построить функцию, отражающую зависимость дефицита бюджета от времени в России и США.

3. Вложенные в производства средства дают прибыль:

Определить зависимость прибыли от вложенных средств и

вычислить прибыль для вложений, равных 10 ООО руб.

1. 2. Нахождение экстремального значения функции

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

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

f(x)=0

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

В MS Excel для решения таких уравнений используется удобный инструмент Подбор параметра, который реализует алгоритм численного решения уравнения, зависящего от одной переменной.[54] Приведем примеры использования процедуры.

После нахождения производной данной функции и приравнивания ее к нулю получаем уравнение, которое на интервале (0,2) имеет 2 корня. Решение начинаем с нахождения первого корня.

1. Занесем в ячейку А1 ориентировочное значение первого корня, например, 0,1.
2. Занесем в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула будут иметь вид: =1,5*((3,5*CQS(3,5*A1)*(0,2*A1+0,01)-Q,2*SIN(3,5*A1))/ /(0,2*А1+0,01Г2)
3. Вызовем процедуру Подбор параметра (команда Сервис —gt; Подбор параметра).
4. В поле Установить в ячейке укажем В1, в поле Значение зададим 0 (правая часть уравнения), в поле Изменяя значение ячейки укажем А1.
5. Щелкнем на кнопке ОК и получим результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкнем на кнопке ОК, чтобы сохранить полученное значение ячеек, участвовавших в операции. Таким образом, в ячейке А1 получим приближенное значение ^=0.212. Повторим расчет для второго корня t2 зада

   вая в ячейке А2 другое начальное значение, например 2.

Получаем значение второго корня уравнения t2=1.286.1

6. В ячейках С1 и С2 получим максимальное и минимальное значения уровня соответственно.

@gt; Упражнения

1. Сезонная динамика спроса на товар описывается зависимо

стью:

S = lALn2t-0.5t2

г

где t — номер месяца.

Определить, в каком месяце следует ожидать минимальный и максимальный спрос на товар и величину спроса.

2. Капитальные вложения в строительство дорожных конструкций в сейсмически опасных районах зависят от того, насколько необходимо усилить конструкцию с учетом возможных сейсмических воздействий. Частотный спектр отклика грунта на сейсмическое воздействие у есть функция от круговой частоты колебаний ш амплитуды а и двух параметров грунтов а и Р:

у = аш“е~рш

Найти максимальное значение частотного спектра в диапазоне шє (0.1, 2) при значениях а = 1, а=3, Р=—4

' В задачах с ограничениями на целочисленность переменных следует округлять результат решения.