МОБР (диапазон C)

Решение систем линейных алгебраических уравнений.

Оно выполняется в три последовательных этапа.

1. Заданная система n линейных алгебраических уравнений записывается как векторное уравнение вматричной форме:

[x] * [C] = [r], где

[x] – вектор неизвестных (матрица-столбец) x₁

x₂

...

x_n,

[C] – матрица коэффициентов C_ij, C₁₁ C₁₂... C_1m

C₂₁ C₂₂... C_2m

.........................

C_n1 C _n2... C_nm

[r] – вектор констант правой части r₁

(матрица-столбец ) r₂

...

r_n

т.е. вектор-столбец неизвестных, умноженный на матрицу коэффициентов, равен заданному векторустолбцу правой части;

Для нахождения вектора неизвестных [x] надо поделить правую часть [r] на матрицу коэффициентов [C]. Однако, для матриц, являющихся векторами, операция деления возможна только как умножение на обратную матрицу. Поэтому на втором этапе производится

2. Нахождение матрицы [C_обр], обратной матрице коэффициентов [C].

Эта стандартная задача решается с помощью функции обращения

3. На третьем этапе решения системы уравнений левая и правая части векторного уравнения умножаются на обратную матрицу [C_обр]. Произведение самой матрицы [C] на её обратную матрицу [C_обр] равно единице. Поэтому в левой части останется искомое значение вектора-столбца неизвестных [x], а в правой – произведение обратной матрицы на вектор-столбец констант правой части [C_обр]*[r].

Перемножение матриц осуществляется стандартной функцией МУМНОЖ (диапазон [C_обр]; диапазон [r]).

В выделенном диапазоне результата (столбец длиной n), после нажатия комбинации клавиш CTRL+SHIFT+ENTER записывается результат решения в виде матрицы-столбца вектора неизвестных [x].

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

ЛЕКЦИЯ 6

EXCEL. Регрессия. Статистические функции. Численное интегрирование

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

1. нахождение углового коэффициента линии регрессии с помощью функции НАКЛОН (диапазон зависимой переменной; диапазон независимой переменной), в круглых скобках которой записаны диапазоны переменных, для которых строится регрессионная зависимость;

2. нахождение точки пересечения линии регрессии с осью ординат с помощью функции ОТРЕЗОК () с теми же аргументами.

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

3. оценка точности приближения линии регрессии к исходным данным производится по критерию Пирсона функцией КВПИРСОН () с теми же аргументами. Полученное значение квадрата коэффициента корреляции R² имеет максимальное значение, равное единице, в теоретическом случае абсолютного совпадения. Реально допустимые значения точности аппроксимации задаются условиями задачи и обычно составляют 0.7..0.9.

Ещё проще получить уравнение регрессии можно при построении диаграммы с помощью опции ЛИНИЯ ТРЕНДА. Для этого надо кликнуть по любому маркёру точечной диаграммы правой клавишей и выбрать во всплывающем меню команду Добавить линию тренда. В диалоговом окне надо выбрать Тип и кликнуть OK. При этом происходит автоматический подбор наилучшей в смысле приближения к исходным данным линии тренда. Далее следует дважды кликнуть по появившейся на диаграмме линии тренда, после чего откроется окно ФОРМАТ ЛИНИИ ТРЕНДА. В его разделе ПАРАМЕТРЫ надо установить флажки: Показать уравнение на диаграмме и Поместить величину достоверности аппроксимации (R^2). После нажатия OK, указанные параметры отобразятся на диаграмме. Линия тренда представляет собой геометрическое отображение уравнения регрессии. С помощью этого уравнения можно прогнозировать значения зависимой переменной при варьировании значений независимой переменной.

Рассмотренная линейная модель регрессии содержит два коэффициента. Однако далеко не всегда зависимость между переменными может быть точно отображена прямой линией. Поэтому в окне ФОРМАТ ЛИНИИ ТРЕНДА имеются также форматы ЭКСПОНЕНЦИАЛЬНАЯ, ЛОГАРИФМИЧЕСКАЯ, СТЕПЕННАЯ, а также ПОЛИНОМИАЛЬНАЯ. Первые три формата, как и формат ЛИНЕЙНАЯ, предусматривают наличие двух коэффициентов в уравнении регрессии. Применяются форматы с учётом характера процессов.

Полиномиальная регрессия осуществляется полиномом с числом коэффициентов более двух. На вкладке этого раздела следует установить порядок выбранного для аппроксимации полинома из диапазона от 2-ой до 6-ой степени. Число коэффициентов полиномов на единицу больше их порядка (от 3 до 7). Выбор степени полинома определяется как характером процесса, так и требуемой точностью. При этом следует учитывать, что каждый элемент полинома должен иметь реальный физический аналог в рассматриваемой задаче аппроксимации. Это позволяет учитывать влияние того или иного коэффициента регрессии на результат и точность аппроксимации.

Решение этих и более сложных задач регрессии может быть выполнено также с помощью устанавливаемого дополнительно пакета регрессионного анализа.

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

Для вычисления математического ожидания (среднего арифметического значения генеральной совокупности) и выборочного среднего (для выборки заданного объёма) используется общая функция СРЗНАЧ (диапазон), где аргументом является диапазон значений генеральной совокупности или диапазон значений выборки. В результате получаются оценки математического ожидания m и выборочного среднего x_ср по формулам

m = S^N_i=1 (x_i), N- объём генеральной совокупности,

x_ср = Sⁿ_i=1 (x_i), n- объём выборки.

Оценка разброса значений относительно математического ожидания m производится встроенными функциями ДИСПР (ДИАПАЗОН) и корнем квадратным из её величины СТАНДОТКЛОНП (ДИАПАЗОН) по формулам

D = (S^N_i=1(x_i -m)²)/N, s = D^0,5.

Стандартное отклонение s имеет ту же размерность, что и процесс x, что позволяет для относительной оценки степени разброса использовать значение коэффициента вариации V=(m/s)*100%.

Оценка разброса значений относительно выборочного среднего x_ср производится другими встроенными функциями:ДИСП (ДИАПАЗОН) и СТАНДОТКЛОН (ДИАПАЗОН). При этом расчётные формулы имеют вид

D = (Sⁿ_i=1(x_i -x_ср)²)/(n-1), s = D^0,5.

Использование выборочных оценок вместо оценок генеральной совокупности вносит определённую погрешность. Чтобы иметь возможность задавать и варьировать её, используют понятие доверительного интервала. Обычно задаётся доверительный уровень 95%; тогда погрешность вычисления выборочной оценки, называемая уровнем достоверности, составляет a = 1 – 0,95 = 0,05. Зная эту величину и количество элементов в выборке n, можно вычислить вспомогательную величину t с помощью встроенной функции обратного распределения Стьюдента СТЬЮДРАСПРОБР (a, КСС). Здесь аргумент КСС= n – 1 называется количеством степеней свободы и увеличивается по мере роста объёма выборки.Функция вычисляет значения t для двухстороннего распределения. Теперь можно вычислить верхнюю B_врх и нижнюю B_иж границы доверительного интервала. Например, для оценки среднего арифметического x_ср выборки объёмом n

B_врх = x_ср + t*(s/ n^0,5)

B_нж = x_ср - t* (s/ n^0,5)

Это значит, что выборочное среднее x _ср выборки объёмом n находится в пределах

B_нж <= x _ср<= B_врх,

вычисленных с заданным уровнем достоверности a. Для уменьшения доверительного интерваласледует увеличить объём выборки n или уменьшить доверительный уровень a.

Анализ случайных процессов предусматривает определение вида и параметров закона распределения элементов процесса. Для построения плотности распределения, являющейся определяющим параметром закона распределения, весь диапазон значений разбивается на равные интервалы, расположенные на оси абсцисс вправо и влево от среднего значения. По оси ординат откладывается количество значений процесса, попадающее в тот или иной диапазон. Большинство процессов подчиняется нормальному закону распределения, гистограмма (плотность распределения) которого имеет колоколообразную форму (кривая Гаусса). Максимум значений процесса (см. рис) находится в интервале, расположенном вокруг среднего значения; 99% значений находятся в интервале от -3s до +3s. Для других законов распределения, также представленных в библиотеке EXCEL, параметры другие.

Для построения гистограммы распределения частот при нормальном законе распределения выполняются следующие процедуры:

1. Вычисляются или задаются значения математического ожидания m и среднего квадратического отклонения s случайного процесса

2. Выбирается количество интервалов в диапазоне от -3s до +3s

3. Границы интервалов вводятся столбцом в электронную таблицу

4. Активируется пакет Анализ данных выбором в меню команды Сервис (пакет установлен, но не всегда активирован)

5. В диалоговом окне из многочисленных инструментов анализа выбирается Гистограмма

6. В окне указывается диапазон исходных данных – ячеек, содержащих исследуемый процесс, и диапазон границ интервалов

7. Далее задаётся место расположения результатов и включается флажок Вывод графика; после нажатия OK в выбранном месте появляется график гистограммы, а в таблицу интервалов помещается количество значений в каждом интервале.

Численное интегрирование в EXCEL основано на приближённом вычислении площадей под графиком подынтегральной функции в интервале пределов интегрирования. Существует три основных приёма приближённого вычисления площадей:

1. метод прямоугольников

2. метод трапеций

3. метод Симпсона

Во всех случаях схема решения универсальна и включает (рис):

- ввод или импортирование дискретных значений подынтегральной функции с выбранным интервалом в заданных пределах интегрирования;

- ввод формулы для вычисления площади элементарного прямоугольника или трапеции на выбранном интервале;

- копирование формулы во все остальные интервалы, количество которых может не совпадать с количеством заданных точек подынтегральной функции;

- вычисление сумм площадей фигур разбиения и общей суммы, приближённо равной искомому значению интеграла в пределах интегрирования.

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

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

S_i= (x_прав – x_лев)* y,

а приближённое значение интеграла будет равно сумме площадей элементарных прямоугольников S_i.

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

S_i = ((y_прав + y_лев)/2)* (x _прав - x_лев).

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

Дата добавления: 2014-01-07