Применение Excel в математических методах

Работа с матрицами. Балансовые модели

Задание. Межотраслевой баланс производства и распределения продукции для 4 отраслей имеет вид:

1. Найти конечный продукт каждой отрасли, чистую продукцию каждой отрасли, матрицу коэффициентов прямых затрат.
2. Какой будет конечный продукт каждой отрасли, если валовой продукт первой отрасли увеличится в 2 раза, у второй увеличится на половину, у третьей не изменится, у четвертой – уменьшится на 10 процентов.
3. Найти валовой продукт, если конечный станет равен 700, 500, 850 и 700.
Решение:
1. Составим матрицу коэффициентов прямых затрат А={aij}, где , j=1,2,…,n.
Зная величины валовой продукции (Xi) для каждой отрасли, можно определить объёмы конечной продукции каждой отрасли (Yi) по формуле:
Y = (E – A)X.
Для нахождения Y будем использовать функцию MS Excel, выполняющую умножение матриц «МУМНОЖ».
Чистую продукцию каждой отрасли (Zj) найдем по формуле:

Таблица 1.1
Полная балансовая таблица для четырех отраслей, полученная на основе исходных данных

Проверка:  
2. Определим, какой будет конечный продукт каждой отрасли, если валовой продукт первой отрасли увеличится в 2 раза, у второй увеличится на половину, у третьей не изменится, у четвертой – уменьшится на 10 процентов, т.е. если :

Рассчитаем дополнительно величину чистой продукции по каждой отрасли и запишем полную балансовую таблицу, соответствующую данной ситуации.

Таблица 1.2

Проверка: .
3. Найдем валовой продукт, если конечный станет равен 700, 500, 850 и 700, т.е. Для этого используем формулу:
,
где  — матрица коэффициентов полных материальных затрат.
На основе матрицы (Е-А) рассчитаем матрицу , используя функцию «МОБР» MS Excel.
После расчета вектора валового продукта , элементы хij (объём продукции отрасли i, расходуемой в отрасли j) рассчитаем по формуле:
.
Рассчитаем дополнительно величину чистой продукции по каждой отрасли и запишем полную балансовую таблицу, соответствующую данной ситуации.

Таблица 1.3

Проверка: .

Построение графиков. Исследование статистических функций

Задание 1. Построить график плотности распределения хи-квадрат, протабулировав эту функцию на отрезке от 0 до 10 с шагом 0,2 и взяв степень
свободы k=5. Проанализировать зависимость параметра распределения k на график.
Решение:
Для построения графика функции, зададим значения аргумента х по формуле:
, где i=0,…,n-1, х0=0.
Значения функции плотности распределения хи-квадрат находим с помощью функции MS Excel «ХИ2РАСП».

Рис. 2.1
Проследим как влияет параметр распределения k на график. Для этого построим дополнительно два графика для k=8 и  k=15 (при этом же изменении аргумента).
Из рис. 2.1 видим, что с ростом значения параметра k происходит растяжение графика вдоль оси Ох, т.е. на одном и том же отрезке график функции с большим значением параметра более пологий.

Задание 2. Построить график плотности распределения Стьюдента, протабулировав эту функцию на отрезке от 0 до 7 с шагом 0,2 и взяв степень
свободы k=4. Проанализировать зависимость параметра распределения k на график.
Решение:
Значения функции плотности распределения Стьюдента находим с помощью функции MS Excel «СТЬЮДРАСП».

Рис. 2.2
Из рис. 2.2 видим, что с ростом значения параметра k график становится более вогнутый.

Задание 3. Построить график плотности распределения Фишера, протабулировав эту функцию на отрезке от 0 до 5 с шагом 0,2 и взяв степени
свободы m=4 и n=5. Проанализировать зависимость параметров распределения m и n на график.
Решение:
Значения функции плотности распределения Фишера находим с помощью функции MS Excel «FРАСП».

Рис. 2.3

Рис. 2.4
Влияние параметров распределения m и n на график отображено на рис. 2.3 и 2.4.

Статистические методы обработки данных

Задание. Дана выборка выручки магазина за последние 30 дней. Составить статистический ряд, построить гистограмму, полигон, кумуляту.

Решение:
Построим интервальный статистический ряд. Оптимальное число интервалов определим по формуле Стерджесса:
 округляем до 6
Ширину интервала разбиения рассчитываем по формуле:

Записываем интервальный статистический ряд:

49               52,5            56               59,5           63               66,5           70

Рис. 3.1. Гистограмма
Построим полигон – ломаную кривую, соединяющую последовательно (в порядке возрастания значения абсциссы) точки, абсцисса которой равна середине интервала, а ордината – частоте попадания в интервал.

Рис. 3.2. Полигон
Для построения кумуляты рассчитаем накопленные частоты. Расчеты оформим во вспомогательной таблице.

Рис. 3.3. Куммулята

Точечное и интервальное оценивание параметров распределений

Задание 1. Дана выборка выручки магазина за последние 30 дней. Вычислить объем выборки, выборочное среднее, дисперсию, стандартное отклонение, медиану, моду, коэффициент эксцесса, коэффициент асимметрии, перцентиль 60%, перцентиль 90%.

Решение:
Расчет указанных показателей осуществим с помощью встроенных функций MS Excel.

Задание 2. Для данных из задания 1 вычислить доверительные интервалы для математического ожидания и дисперсии при ? = 0,01. Изменяя значение уровня значимости ? сделать вывод о его влиянии на ширину интервала.
Решение:
Доверительный интервал для оценки математического ожидания в генеральной совокупности определим по формуле:
,
где  определяется из формулы: .
При  имеем: . По таблице значений функции Лапласа находим, что Ф(2,58)=0,495, следовательно, .
В задании 1 было найдено (с точностью до 10-2): , , n=30.


Изменяя значение уровня значимости ? сделаем вывод о его влиянии на ширину интервала.
При  имеем: . По таблице значений функции Лапласа находим, что Ф(3,29)=0,4995, следовательно, . Тогда


При  имеем: . По таблице значений функции Лапласа находим, что Ф(1,65)=0,45, следовательно, . Тогда


Вывод: при уменьшении уровня значимости ширина доверительного интервала для математического ожидания увеличивается; при увеличении уровня значимости – ширина доверительного интервала уменьшается.

Доверительный интервал для дисперсии определяется по формуле:
,
где  — значение функции «хи-квадрат» при доверительной вероятности  и числе степеней свободы r=n-1;
 — значение функции «хи-квадрат» при доверительной вероятности  и числе степеней свободы r=n-1.
При  и r=29 имеем:




Изменяя значение уровня значимости ? сделаем вывод о его влиянии на ширину интервала.
При  и r=29 имеем:




При  им и r=29 имеем:




Вывод: при уменьшении уровня значимости ширина доверительного интервала для дисперсии увеличивается; при увеличении уровня значимости – ширина доверительного интервала уменьшается.

Ответ: при уровне надежности ? = 0,01  — доверительный интервал для математического ожидания; — доверительный интервал для дисперсии.
При уменьшении уровня значимости ширина доверительного интервала как для мат. ожидания, так и для дисперсии увеличивается; при увеличении уровня значимости – ширина доверительного интервала уменьшается.

Проверка статистических гипотез о виде распределения

Задание 1. Дана выборка числа посетителей Интернет – сайта за 30 дней. Проверить по критерию Пирсона на уровне значимости ? = 0,02 статистическую гипотезу о том, что генеральная совокупность, представленная выборкой, имеет нормальный закон распределения.

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

Записываем интервальный статистический ряд:

По исходным данным выборки с помощью встроенных функций «СРЗНАЧ» и «СТАНДОТКЛОН» рассчитаем выборочную среднюю и среднее квадратическое отклонение:

Произведем проверку статистической гипотезу о том, что генеральная совокупность, представленная выборкой, имеет нормальный закон распределения с помощью критерия согласия Пирсона (уровень значимости .
Вычислим наблюдаемое значение критерия Пирсона:


n=30



Значения функции Лапласа вычислим, используя встроенную функцию MS Excel «НОРМРАСП» с помощью формулы:

Оформим расчеты в таблице.
Таблица 5.1

Рассчитываем в MS Excel критическое значение  для уровня значимости  и числа степеней свободы k=s-3=6-3=3 (s — число интервалов) находим .
Так как , то гипотезу о нормальном распределении принимаем.
Ответ: на 2% уровне значимости статистическая гипотеза о том, что генеральная совокупность, представленная выборкой, имеет нормальный закон распределения, подтверждается.

Задание 2. При производстве микросхем процессоров используются кристаллы кварца. Стандартом предусмотрено, чтобы у 50 % образцов не было обнаружено ни одного дефекта кристаллической структуры, у 15% — один дефект, у 13 % — 2 дефекта, у 12 % — 3 дефекта, у 10 % более 3 дефектов.
При анализе выборочной партии оказалось, что из 1000 экземпляров распределение по дефектам имеет распределение, указанное в таблице:

Можно ли с вероятностью 0,99 (при ? = 0,01) считать, что партия соответствует стандарту?
Решение:
Вычислим наблюдаемое значение критерия Пирсона:

ni – теоретические частоты (в случаях, предусмотренных стандартом);
 — эмпирические частоты.
Таблица 5.2

Рассчитываем в MS Excel критическое значение  для уровня значимости  и числа степеней свободы k=s-1=5-1=4 (s — число групп) находим .
Так как , то гипотеза о соответствии СТАНДАРТУ проверенной партии образцов микросхем подтверждается.
Ответ: с вероятностью 0,99 можно считать, что партия соответствует стандарту.

Проверка гипотезы о равенстве дисперсий

Задание. Четыре станка в цеху обрабатывают детали. Для проверки точности обработки взяли выборки размеров деталей у каждого станка.
Необходимо сравнить с помощью F-теста попарно точности обработки всех станков (рассмотреть пары 1-2, 1-3, 1-4, 2-3, 2-4, 3-4) и сделать вывод, для каких станков точности обработки (дисперсии) равны, для каких нет. Взять уровень значимости ? = 0,02 .

Решение:
Будем рассматривать пары 1-2, 1-3, 1-4, 2-3, 2-4, 3-4.
Обозначим через Х – первую выборку в паре, через Y – вторую выборку в паре.
Основная (нулевая) гипотеза Н0: D(Х)=D(Y).
Альтернативная (конкурирующая) гипотеза Н1: D(Х)D(Y).
Для каждой из выборок Х и Y рассчитываем исправленные дисперсии S2 с помощью встроенной функции MS Excel «ДИСП» и далее рассчитываем наблюдаемое значение критерия (отношение большей исправленной дисперсии к меньшей):
.
С помощью MS Excel рассчитываем критическое значение критерия:
,
где ,  — степени свободы.
В данной задаче . Тогда .
Если , то нулевую гипотезу о равенстве дисперсий принимаем. Если , то нулевую гипотезу о равенстве дисперсий отвергаем и принимаем альтернативную гипотезу.

Таблица 6.1

Таблица 6.2

Вывод: для всех 4-х станков точности обработки (дисперсии) равны.

Проверка гипотезы о равенстве математических ожиданий

Задание. Имеются данные о количествах продаж товара в двух городах. Проверить на уровне значимости 0,01 статистическую гипотезу о том, что среднее число продаж товара в городах различно.

Решение:

Используем пакет «Анализ данных». В зависимости от типа критерия выбирается один из трех: «Парный двухвыборочный t-тест для средних» — для связных выборок, и «Двухвыборочных t-тест с одинаковыми дисперсиями» или «Двухвыборочных t-тест с разными дисперсиями» — для несвязных выборок.
Вызовем тест с одинаковыми дисперсиями, в открывшемся окне в полях «Интервал переменной 1» и «Интервал переменной 2» вводим ссылки на данные (А1-N1 и А2-L2, соответственно).
Если имеются подписи данных, то ставится флажок у надписи «Метки» (у нас их нет, поэтому флажок не ставится).
Далее вводим уровень значимости в поле «Альфа» — 0,01.
Поле «Гипотетическая средняя разность» оставляем пустым.
В разделе «Параметры вывода» ставим метку около «Выходной интервал» и поместив курсор в появившемся поле напротив надписи, щелкаем левой кнопкой в ячейке В4. Вывод результата будет осуществляться начиная с этой ячейки.
Процедура выводит основные характеристики выборки, t-статистику, критические значения этих статистик и критические уровни значимости «Р(Т<=t) одностороннее» и «Р(Т<=t) двухстороннее». Если по модулю t-статистика меньше критического, то средние показатели с заданной вероятностью равны.
Таблица 7.1
Результаты двухвыборочного t-теста с одинаковыми дисперсиями

Так как ¦-1,2105¦ < 2,7969, следовательно, среднее число продаж значимо не отличается.
Ответ: на 1% уровне значимости статистическая гипотеза о том, что среднее число продаж товара в городах различно не подтверждается.

Основы регрессионного и корреляционного анализа

Некоторая фирма, производящая товар, хочет проверить, эффективность рекламы этого товара. Для этого в 10 регионах, до этого имеющих одинаковые средние количества продаж, стала проводиться разная рекламная политика и на рекламу начало выделяться xi денежных средств.
При этом фиксировалось число продаж yi.
Задание. По этим выборкам найти уравнение линейной регрессии = ax + b. Найти коэффициент парной корреляции. Проверить на уровне значимости                ? = 0,05 регрессионную модель на адекватность.

Решение:
Используем инструмент «Регрессия» пакета «Анализ данных» MS Excel.
Таблица 8.1
Результаты регрессионного моделирования

Коэффициент b – это коэффициент при «Y-пересечение», т.е. b=26,76.
Коэффициент а – это коэффициент при «Переменная X 1», т.е. a=2,28.
Уравнение линейной регрессии имеет вид: = 2,28x +26,76.
Найдем коэффициент парной корреляции.
Для этого используем встроенную функцию MS Excel «КОРРЕЛ».
.
Значение коэффициента парной корреляции положительно и близко к 1, что говорит об  очень тесной прямой связи между рассматриваемыми показателями, т.е. с увеличением расходов на рекламу объем продаж так же растет.
Проверим на уровне значимости ? = 0,05 регрессионную модель на адекватность с помощью F-критерия Фишера.
Рассчитаем фактическое значение F-критерия по формуле:
,
или  определяем из таблицы 8.1.

С помощью встроенной функции «FРАСПОБР» MS Excel для уровня значимости  и числа степеней свободы k1=1 и k2=n-2=10-2=8 находим критическое значение критерия Fкр.

Если окажется что , то это означает, что гипотеза о несущественности связи между Y и Х с вероятностью ошибочности суждения  отклоняется, т.е. связь между этими переменными может быть признана существенной, а модель в целом статистически значимой (адекватной).
, следовательно, полученная модель линейной регрессии адекватна.