Коврижных А.Ю., Конончук Е.А. и др. Прикладное программное обеспечение для решения экономических задач

Подождите немного. Документ загружается.

ЛГРФПРИБЛ и РОСТ для аппроксимации показательных функций вида:

у = а

х1

х2

. . .а

хп

(5.3)

Функции ЛИНЕЙН и ЛГРФПРИБЛ служат для вычисления неизвестных

коэффициентов а

, а

..., а

в выражениях (5.2) и (5.3) соответственно, а также

коэффициентов детерминации (R

), и ряда других показателей.

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

ЛИНЕЙН (известные

_значения_у;известные_значения__х;конст;статистика)

ЛГРФПРИБЛ

(известные_значения_у;известные_значения_х;конст;статистика)

Здесь:

9 Известные_значения_y — множество наблюдаемых значений у;

9 Известные_значения_x: — множество наблюдаемых значений х

...,

. Причем, если массив известные_значения_у имеет один столбец, то

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

отдельная переменная, а если массив известные_значения_у имеет одну

строку, то тогда каждая строка массива известные_значения_х

интерпретируется как отдельная переменная;

9 конст — логическое значение, которое указывает, требуется ли, чтобы

константа а

была равна 0 (для функции ЛИНЕЙН) или 1 (для функции

ЛГРФПРИБЛ). При этом, если конст имеет значение ИСТИНА или

опущено, то а

вычисляется обычным образом, а если конст имеет

значение ЛОЖЬ, то а

полагается равным 0 или 1;

9 статистика — логическое значение, которое указывает, требуется ли

вычислять дополнительную статистику по регрессии, если введено

значение ИСТИНА, то дополнительные параметры вычисляются, если

ЛОЖЬ, то — нет .

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

аппроксимирующих кривых (5.2) и (5.3), соответственно, для значений

коэффициентов а

, а

..., а

, найденных функциями ЛИНЕЙН и

ЛГРФПРИБЛ.

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

ТЕНДЕНЦИЯ(известные_значения_у;известные_значения_х;новые_зиачения

_х; конст);

РОСТ(известные_значения_у;известные__значения_х;новые_значения_х;конс

т).

Здесь:

9 Известные_значения_у — множество значений у;

9 известны _значения_х — множество значений х;

9 новые_значения_х — те значения, для которых необходимо определить

соответствующие аппроксимирующие или предсказанные значения у.

Новые_зна-чения_х должны содержать столбец (или строку) для каждой

независимой переменной, как и известные_значения_х. Если аргумент

новые_значения_x опущен, то предполагается, что он совпадает с

аргументом известные_значения_х;

9 конст — логическое значение, которое указывает, требуется ли, чтобы

константа m была равна 0 (для функции ТЕНДЕНЦИЯ) или 1 (для

функции РОСТ). При этом, если конст имеет значение ИСТИНА или

опущено, то а

вычисляется обычным образом, а если конст имеет

значение ЛОЖЬ, то а

полагается равным 0 или 1 (рис. 8).

Пример 5.3. Источник радиоактивного излучения помещен в жидкость.

Датчики расположены на расстоянии (х

{

) 20, 50 и 100 см от источника.

Измерения интенсивности излучения (у, мРн) проводились через 1,5 и 10

суток (х

) после установки источника. Результаты измерений (у) приведены

в таблице:

1 5 10

20 61,2 43,6 28,3

50 33,6 24,0 15,6

100 12,3 8,8 5,7

Необходимо аппроксимировать данные функцией вида (5.3) и найти

неизвестные параметры.

Решение

1. Введем данные в рабочую таблицу: в ячейку А1 — символ х

в ячейку

В1 — х

2 ,

в ячейку С1 — y. В диапазон ячеек А2:А10 внесем значения х

в диапазон В2:В10 — значения х

и в диапазон С2:С10 — значения y

(рис.5.7).

Рис.5.7

2. Выделяем блок ячеек D1:F5 под массив результатов.

3. Поскольку функция для вычисления интенсивности излучения имеет

степенной характер (5.3), вызываем функцию ЛГРФПРИБЛ (панель

инструментов Стандартная, кнопка Вставка функции, рабочее поле

Категория тип Статистические, рабочее поле Функция вид

ЛГРФПРИБЛ).

4. Заполняем рабочие поля: Изв_знач_у — С2:С10, Изв_знач_х — А2:В10,

Стат =1

Рис.5.8

Нажимаем сочетание клавиш CTRL+SHIFT+ENTER.

5. В результате в диапазоне D1:F5 получим следующие данные:

Рис.5.9

Здесь первая строка — значения коэффициентов а

, а

, соответственно,

вторая строка — стандартные ошибки этих коэффициентов, третья строка —

коэффициент детерминации R

и стандартная ошибка y, четвертая строка —

значение критерия Фишера и число степеней свободы и нижняя строка —

сумма квадратов регрессии и остаточная сумма квадратов.

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

y = 99,7·0,98

·0,92

Причем точность аппроксимации очень высокая — R

= 0,99998.

Пример 5.4. В бассейне проводится ежедневная частичная смена воды.

Имеются данные семидневных наблюдений зависимости уровня воды в

бассейне (у) от продолжительности заполнения водой (x

)и времени выпуска

воды (х

(заполнение)

х2

(выпуск)

120 20 3,2

100 25 2,8

130 20 3,3

100 15 3,0

110 23 3,1

105 26 2,9

112 16 3,1

Необходимо найти значения уровня воды в бассейне в зависимости от

длительностей заполнения х

∈[100; 130] и выпуска воды х

∈ [15; 25] с шагом

∆ = 5 минут. Построить поверхность.

Решение

1) Введем данные в рабочую таблицу: в ячейку А1 — символ х

в ячейку Bl

— х

2 ,

в ячейку С1 — у. В диапазон ячеек А2:А8 внесем значения х

диапазон В2:В8 — значения х

и в диапазон С2:С8 — значения у.

2) Введем значения х

и х

для получения расчетных значений у в

соответствии с заданием: х

∈

[100; 130] в диапазон А10:А30, а х

∈

[15;

25] в диапазон В10:В30

3) Выделим блок ячеек С10:С30 под массив расчетных (предсказанных)

значений у.

4) Поскольку уравнение для вычисления уровня воды близко к линейному

(5.2), вызываем функцию ТЕНДЕНЦИЯ (панель инструментов

Стандартная, кнопка Вставка функции, рабочее поле Категория тип

Статистические, рабочее поле Функция вид ТЕНДЕНЦИЯ).

5) Заполняем рабочие поля: Изв_знач_у — С2:С8, Изв_знач_х – А2:В8,

Нов_знач_х — А10:В30.

Нажимаем сочетание клавиш CTRL + SHIFT + ENTER.

Рис.5.10

6) В результате в диапазоне С10:С30 получим предсказанные значения у

(рис.5.11).

Рис.5.11

Для построения поверхности построим таблицу в диапазоне E4:H11, где в

столбце E поместим значения x

, а значения x

– в первой строке. Значения у

поместим в ячейки на пересечении соответствующих строк и столбцов

(рис.5.12). Получилась таблица:

Рис.5.12

Применим прием построения поверхности, рассмотренный в разделе 1.

Получим следующую диаграмму:

100

105

110

115

120

125

130

2,50

2,60

2,70

2,80

2,90

3,00

3,10

3,20

3,30

3,40

заполнение

выпуск

Изменение уровня воды

Рис.5.13

6. Решение задачи коммивояжера.

Приведем математическую модель задачи. Пусть переменная х

означает

наличие дуги вида (i, j) в гамильтоновом контуре, а а

интерпретируется как

длина указанной дуги. Тогда математическая модель задачи имеет вид (Рис.1):

(1)

(2)

(3)

(4)

(5)

{}()

{} { }()

{}()

⎪

⎩

⎪

⎨

⎧

∈∀∈

≠∈∀−≤⋅+−

∈∀==

⎯→⎯

∑

∑∑

....,3,2

;...,3,2,1,1,0

;,...,3,2,1

;...,3,2,11

min

niRu

njix

jinjinxnuu

nix

njx

ijji

ijij

(6)

Рис.1

Ограничения (2) – (3) означают, что из каждой вершины можно выйти только

один раз и, аналогично, войти в нее можно только один раз. Так как решением

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

невозможность распадения контура-решения на отдельные контуры.

Ограничения (5) указывают, что переменные х

являются булевыми, то есть

принимают значения только 0 или 1. Ограничения (6) указывает на

принадлежность переменных u

множеству вещественных чисел.

Задание. Найти решение задачи коммивояжера для графа с заданной (Рис.2)

матрицей расстояний с использованием надстройки «Поиск решения».

1 23456

1 ∞ 20 28 12 39 32

2 21 ∞ 15 9 17 27

3 30 25 ∞ 45 29 47

4 7 52 40 ∞ 15 1

5 60 46 11 5 ∞ 34

6 11 45 14 21 30 ∞

Рис.2

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

исходных данных, целевой функции и ограничений на рабочем листе MS

EXCEL (рис. 3).

Рис.3

На рис. 4 приведены формулы, находящиеся в ячейках G8:G13, A14:F14,

B17:F21 соответственно.

Рис.4

На рис.5 – 7 приведены состояние окна диалога надстройки «Поиск решения»

и его параметров, а также рабочего листа MS EXCEL.

Рис.5

Рис.6