Тема: Расчеты с использованием математического редактора в MS Excel

Работа 2.3.1.Применение в расчетах математического аппарата программы MS Excel

Цель работы: используя математический аппарат программы MS Excelрассчитать загруженность сотрудников испытательной лаборатории сертификации.

Задание: Необходимо рассчитать загруженность сотрудников испытательной лаборатории сертификации. Одно исследование показателя качества в среднем затрачивается 50 мин. Количество проведенных исследований в течение недели представлено в таблице (табл. 2.3.1).

Таблица 2.3.1

	А	В	С	D	E	F	G	H
1	День недели	Пн	Вт	Ср	Чт	Пт	Сб	Всего
2	Число проведенных исследований
3	Иванцова Л.И.	3	7	5	3	5	2
4	Смирнова В.А.	2	3	6	7	4	3
5	Панова Л.А.	7	3	6	3	2	4
6	Всего исследований
7	В среднем за неделю
8	Расчет нагрузки (час)
9	Иванцова Л.И.
10	Смирнова В.А.
11	Панова Л.А.
12	Всего часов
13	В среднем за неделю

Решение.

Открываем таблицу MS Excel .Вводим таблицу 2.3.1 в ячейки А1-Н13 (во 2-й и 8-й строках объединять ячейки не нужно, а вводить подпись в первую ячейку). Рассчитываем всего исследований каждый день (6 строка). Ставим курсор в В6 и вводим функцию вычисляющую сумму ячеек В3, В4 и В5: «=СУММ(В3:В5)» (адреса ячеек В - латинские буквы), а можно вызвать мастер функций кнопкой fx, в категории «Математические» выбрать функцию СУММ и поставив курсор в поле «Число1» обвести диапазон ячеек от В3 до В5.

Затем данную ячейку автозаполняем на ячейки C6-G6. Для этого выделяем ячейку В6, щелкнув по ней мышью и подводим курсор в нижний правый угол ячейки так, чтобы он принял форму крестика. Тянем этот крестик вправо на ячейки C6-G6.

Далее рассчитываем среднее число исследований в седьмой строке. Ставим курсор в В7, вызываем мастер функций кнопкой fx, и выбираем в категории «Статистические» функцию СРЗНАЧ, в поле «Число1» обвести диапазон ячеек от В3 до В5. Затем данную ячейку автозаполняем на ячейки С7-G7.

Рассчитываем время (час) затраченное на исследования показателей качества. Ставим в ячейку В9 курсор и задаем в нее формулу пересчета данных из В3, умноженную на 50 мин и деленную на 60 мин, т.е. вводим формулу «=В3*50/60». При этом адрес В3 можно не набирать вручную, а щелкнуть мышью по ячейке В3. Автозаполняем результат на С9-G11. Для этого тащим крестик автозаполнения вправо на 6 ячеек, а затем вниз на 2 ячейки. Строки 12 и 13 «Всего часов» и «В среднем за неделю» рассчитываем так же, как 6-ю и 7-ю строки.

Рассчитываем теперь столбец Н «Всего». Ставим курсор в Н3 и вводим «=CУMM(B3:G3)», автозаполняем вниз на 5 ячеек. Ставим курсор в Н9 и вводим «=СУММ(В9:G9)», автозаполняем вниз на 5 ячеек. Таблица готова. Можно делать вывод о загруженности штата лаборатории сертификации.

Работа 2.3.2. Работа с матрицами.Решение балансовых задач.

Цель работы: используя матричные операторы, изучить методы решения аналитических задач в программе MS Excel .

Задание:Имеется баланс поступления средств взаимосвязанных лабораторий центра сертификации за предыдущий период (табл. 2.3.2):

Таблица 2.3.2

Лаборатории центра сертификации	Затраты лабораторий на оценку показателей качества в месяц, млн. руб.			Доход от сертификата, млн. руб.
	Первая	Вторая	Третья
Первая	17	13	11	83
Вторая	8	16	9	97
Третья	21	15	13	132

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

2. Какой будет планируемый доход-1 за сертификат по каждой лаборатории, если общий доход-1 увеличить, соответственно, до 200, 250 и 300.

3. Какой будет общий доход-2 каждой лаборатории, если доход-1 от выдачи сертификата первой лаборатории увеличить на 50%, второй увеличить на 10 млн. руб., а третьей увеличить на 20 млн. руб.

Решение.

Подготавливаем таблицу исходных данных в электронной таблице Excel (рис. 2.3.1).

1. Для нахождения общего дохода по каждой лаборатории в ячейку F3 вводим формулу «=СУММ(В3:Е3)» (для ее ввода достаточно нажать кнопку автосуммы со значком Е). Результат - 124. Автозаполнением переносим результат ячейки на F4 и F5.

2. Для расчета чистой прибыли по каждой лаборатории вводим в ячейку В6 формулу «=F3-B3-B4-B5», в С6 формулу            «=F4-C3-C4-C5», в D6 формулу «=F5- D3-D4-D5».

3. Находим коэффициенты прямых затрат. Для этого каждый столбец матрицы B3-D5 нужно разделить на соответствующий доход. В ячейку В7 вводим «=B3/$F$3» (чтобы сделать абсолютную ссылку $F$3 нужно щелкнуть по ячейки F3 и нажать клавишу F4). Автозаполняем В7 на В8 и В9.

Рис. 2.3.1

Аналогично вводим в С7«=C3/$F$4» и автозаполняем С7 на С8 и С9. Вводим в D7 «=D3/$F$5» и автозаполняемD7 на D8 и D9. Матрица коэффициентов затрат рассчитана.

4. Так, как планируемый общий доход-1 каждой лаборатории равен, соответственно 200, 250 и 300, то вводим эти числа в ячейки Н3, Н4 и Н5. Планируемый общий доход-1 Y₁можно рассчитать по формуле:

Y₁ = (E - A)X,

где А – матрица коэффициентов прямых затрат; Х – матрица планируемый общий доход-1, Е – единичная матрица вида:

1	0	0
0	1	0
0	0	1

 

  Е=

 

 

В ячейку А11 вводим подпись «Е=», а в В11-D13 вводим числа единичной матрицы. Рассчитываем матрицу (Е-А). Для этого вводим в А15 запись «(Е-А)=», а в В15 вычисление «=В11-В7». Автозаполняем ячейку на B15-D17.

Для вычисления результата, новых значений планируемого дохода-1 за сертификат, в ячейку G3 вводим функцию перемножения матриц - МУМНОЖ (категория «Математические»). Аргументы функции: в поле «массив 1» даем ссылку B15:D17 (т.е. матрица Е-А), в поле «массив 2» - H3:H5 (данные планируемого общего дохода-1) и нажимаем Enter. Далее обводим ячейки G3-G5 курсором мыши, выделяя их, и нажимаем F2 и затем Ctrl+Shift+Enter. В результате получим - планируемый доход-1 за выдачу сертификатов.

5. Если доход-1 от выдачи сертификата первой лаборатории увеличить на 50 %, то доход-2 станет 124,5, второй увеличить на 10 млн. руб. - он станет 107, а третий увеличить на 20 млн. руб, то он будет 152 млн. руб. Вводим в ячейки G7-G9 числа 124,5; 107; 152. Тогда общий доход-2 находим по формуле:

X = (E - A)^-1•Y₂.

Для расчета обратной матрицы (E - A)^-1 в ячейку Е15 вводим подпись «(Е-А) обрат.», а в F15 ставим формулу расчета обратной матрицы МОБР (категория «Математические»). Аргумент функции - ссылка на B 15- D 17. Обводим курсором ячейки F15-H17 и нажимаем F2 и Ctrl+Shift+Enter.

Для вычисления значений общего дохода-2 в ячейку Н7 вводим функцию перемножения матриц - МУМНОЖ. Аргументы: в поле «массив 1» даем ссылку F15:H17, в поле «массив 2» - G7:G9. Далее обводим ячейки Н7-Н9 и нажимаем F2 и Ctrl+Shift+Enter. Результат – значение общего дохода-2 получено.

Вывод: Анализ полученных результатов позволит планировать работу центра сертификации на перспективу. Проанализируйте полученные результаты.

Лабораторная работа 4

Тема: Определение параметров линейной регрессии с помощью статистических функций ЛИНЕЙН

 

Цель работы:Ознакомление с методикой применения статистических функций ЛИНЕЙН программы MS Excelв расчетах.

Задание: 1. Изучить методику применения статистической функции ЛИНЕЙН программы MS Excelв расчетах прогнозирования качества продукции.

2. Выполнить самостоятельно задание обработки результатов вMS Excel ,применяя статистические функции ЛИНЕЙН.

 

Основные сведения

 

Синтаксис функции: ЛИНЕЙН (Изв_знач_у, Изв_знач_х, Константа, Стат).

При построении уравнения регрессии подбирают вид функции, связывающей результативный показатель у и факторы (аргументы) х₁, х₂,..., х_к.Отбирают наиболее информативные аргументы, вычисляют оценки неизвестных значений параметров уравнения регрессии и анализируют точность полученного уравнения.

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

у =f(х₁, х₂,..., х_к).

Многочисленные опыты по подбору параметров уравнения ре­грессии показывают, что в реальных процессах зависимость резуль­тативного показателя (отклика системы) у от аргументов (факторов) х₁, х₂,..., х_к хорошо описывается полиномом вида:

 

Такой полином называют регрессионной зависимостью (уравнением регрессии), а коэффициенты b_ib_ii, b._ij— статистическими оценками коэффициентов регрессии. При этом b_i- линейные коэффициенты, b_ii - нелинейные коэффициенты, b._ij. - коэффициенты, учитывающие взаимное влияние факторов.

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

Линейная модель уравнения регрессии строится с использованием следующей зависимости (2.4.1):

 

B = ( X^TX )^-1 X^TY,                            (2.4.1)

где

B = X = Y =

 

B - матрица-столбец статистических оценок коэффициентов регрессии; X-  матрица значений объясняющих переменных;        Y- матрица-столбец значений результативного показателя; Х^Т—транспонированная матрица X; (Х^ТХ)^-1- обратная матрица; k — число факторов; п — число наблюдений.

Решая уравнение (2.4.1) в матричном виде, получаем коэффициенты уравнения регрессии b_i, зная которые, можно записать линейную модель уравнения регрессии.

В результате можно построить линейную модель вида:

y= b_o +b₁x₁ +b₂x₂+b₃x₃+…+ b_ix_i + b₁₁ + b₂₂ + b₃₃ + b_ij  +…+ + b ₁₂ x ₁ х₂ + b ₁₂ x ₁ х₂+ b ₁₃ x ₁ х₃+ b ₂₃ x ₂ х₃+ b ₁₂₃ x ₁ х₂х₃ +…+ b_ijx_i х _j +…

Для построенной модели вычисляют коэффициент детерминации по формуле (2.4.2):

(2.4.2)

где e_i = у_i - — регрессионные остатки;

 = - среднее результативного признака.

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

Проверка значимости уравнения регрессии проводится на основе критерия Фишера

Расчетное значение критерия F_Bсравнивается с критическим F_кр(α; п-к-1), определяемым по таблице критических точек распределения Фишера.Также можно определить с помощью функции FРАСППОБР.

Если F_B>F_Kp,то уравнение регрессии считается значимым, т.е. хотя бы один коэффициент регрессии не равен нулю.

Значимость коэффициентов регрессии проверяется с помощью критерия Стьюдента, основанного на статистике

где  = S²( X^TX ) _n ^-1 — дисперсия коэффициента регрессии b_i;S²— несмещенная оценка остаточной дисперсии;

( X^TX ) _n ^-1 — элементы обратной матрицы, стоящие на главной диагонали.

 

Расчетное значение критерия . сравнивают с критическим

t_кр(α, f), где f=n-k-1 – число степеней свободы

Если >t_кр, то коэффициент b_i значим.

При построении регрессионных моделей рассчитываются:

• сумма квадратов регрессии

с числом степеней свободы k;

• сумма квадратов остатков

 с числом степеней свободы п - k - 1.

С помощью этих величин можно рассчитать:

• дисперсию регрессииMS₁ = =SS₁/k

• остаточную дисперсиюMS₂ = =SS₂/n-k-1

Если оценка уравнения регрессии имеет вид у = b₀ + b₁х, то параметры данного уравнения определяются по методу наименьших квадратов на основе решения системы уравнений.

Расчет коэффициентов уравнения регрессии можно осуществлять с помощью СТАТИСТИЧЕСКОЙ функции ЛИНЕЙН. Окно данной функции имеет вид (рис. 2.4.1)

 

Рис. 2.4.1. Окно статистической функции ЛИНЕЙН

 

где Изв_знач_у- это множество значений у;

Изв_знач_х- это необязательное множество значений х;

Константа- это логическое значение, которое указывает, требуется ли, чтобы коэффициент b₀был равен 0;

Стат- это логическое значение, которое указывает, требуется ли рассчитать дополнительную статистику по регрессии.

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

Если массив Изв_знач_уимеет одну строку, то каждая строка массива Изв_знач_хинтерпретируется как отдельная переменная.

Массив Изв_знач_хможет содержать одно или несколько множеств переменных. Если используется только одна переменная, то Изв_знач_уи Изв_знач_хмогут быть массивами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то Изв_знач_удолжны быть вектором (т.е. интервалом высотой в одну строку или шириной в один столбец).

Если Изв_знач_хопущены, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и Изв_знач_у.

Если Константаимеет значение ИСТИНА или опущено, то коэффициент b₀ вычисляется обычным образом.

Если Константаимеет значение ЛОЖЬ, то коэффициент b₀ полагается равным 0.

Если Статимеет значение ИСТИНА, то функция ЛИНЕЙН рассчитывает дополнительную регрессионную статистику (табл. 2.4.1).

Если Статимеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН определяет только коэффициентырегрессииb₀, b₁b₂,...

 

 

Таблица 2.4.1

Дополнительная статистика
Величина	Описание
se 1 , se2, ..., sen	Стандартные ошибки коэффициентов уравнения регрессииb1, b2,..., bп(sb1,sb2..., sbn)
seb	Стандартная ошибка коэффициентаb0- sb0 (seb= #Н/Д, если константа имеет значение ЛОЖЬ)
r2	Коэффициент детерминации R2
sey	Стандартная ошибка регрессионных остатков s
F	Расчетное значение F-критерия (FB)
df	Степени свободы
ssreg	Сумма квадратов регрессии (SS1)
ssresid	Сумма квадратов остатков (SS2)

 

Пример 2.4.1.Имеются данные 15 наблюдений прочности ткани (у) в зависимости от прочности нитей (х₁) изаполнения ткани (х₂) (табл. 2.4.2). Построить линейную модель регрессии, учитывающую эту зависимость и провести ее анализ.

Таблица 2.4.2

№	Прочность ткани, у	Прочность нитей, x1	Заполнение ткани, х2
1	26	37	39
2	33	33	40
3	24	15	35
4	29	36	48
5	42	26	53
6	24	24	42
7	52	15	54
8	56	33	54
9	26	44	50
10	45	34	53
11	27	63	46
12	54	8	50
13	34	44	43
14	48	43	55
15	45	31	51

Решение.

Определяем коэффициенты уравнения регрессии

X = X^Т=

 

X^ТХ= X^ТY=

 

(Х^ТХ)^-1=

 

B = (X^TX)^-1(X^TY)=

 

Уравнение регрессии имеет вид

у = -20,4137 - 0,3136х₁ + 1,4357х₂.

Рассчитываем коэффициент детерминации по формуле (2.2)

R²= 0,731.

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

Проверяем значимость уравнения регрессиипо критерию Фишера:F_B = (0,731 / 2) / [(1-0,731)/12] = 16,3

По таблице критических точек распределения Фишера (см. приложения) по уровню значимости α = 0,05 и числу степеней свободы f₁=k = 2 и f₂=n-k-1=12определяем F =3,88.

Так как F_B>F_Kp,то получаемое уравнение регрессии значимо, т.е. хотя бы один из коэффициентов b. не равен нулю.

Решим данный пример с использованием статистической функции ЛИНЕЙН.

Алгоритм действий следующий.

1. Формируем в Excelтаблицу исходных данных:

	А	В	С	D
1	№	Прочность ткани,у	Прочность нитей, x1	Заполнение  ткани,х2
2	1	26	37	39
3	2	33	33	40
4	3	24	15	35
5	4	29	36	48
6	5	42	26	53
7	б	24	24	42
8	7	52	15	54
9	8	56	33	54
10	9	26	44	50
11	10	45	34	53
12	11	27	63	46
13	12	54	8	50
14	13	34	44	43
15	14	48	43	55
16	15	45	31	51

 

2. Выбираем диапазон ячеек размером 3x5 (5 - число строк: постоянная величина; 3 - число столбцов: равно числу коэффициентов уравнения регрессии), в которую будет выведен результат вычислений (А20:С24).

3. Вызываем Мастер функций, нажав кнопку f_x на панели инструментов.

4. В Мастере функцийиз категории Статистические выбираем функцию ЛИНЕЙН и нажимаемОК.

5. В левом верхнем углу листа Excelпоявится окно функции ЛИНЕЙН (рис. 2.4.1).

6. Нажав кнопку вполе Изв_знач_уперходим на рабочий лист и выделяем мышью исходными данными (В2:В16). Затем возвращаемся к окну функции ЛИНЕЙН снованажав кнопку в поле.

7. Нажав кнопку в поле Изв_знач_х, переходим на рабочий лист с исходными данными и выделяем его мышью (C 2: D 16). Затем, повторно нажав кнопку, возвращаемся к окну функции ЛИНЕЙН.

В поле Константавводим логическое значение ИСТИНА.

В поле Статвводим логическое значение ИСТИНА.

Набираем комбинацию клавиш CTRL+SHIFT+ENTER(ввод формулы массива).

В окне функции появится результат решения (первый элемент массива). В ячейках А20:С24 появится результат вычислений

Таблица результатов

19
20	1.4357	-0,313611829	-20.41372146
21	0.2759	0,126781486	13.74803059
22	0,7308	6,548502705	#Н/Д
23	16,286	12	*Н/Д
24	1396,7	514,5946521	#Н/Д
25

 

Таблица результатов содержит следующие данные:

b 2	b1	b0
Sb2	Sb1	Sb0
R2	S2	-
FB	f	-
SS1	SS2	-

Таким образом, результаты полученные с помощью ПК совпадают с классическими расчетами.

---

Дата добавления: 2018-10-27; просмотров: 263; Мы поможем в написании вашей работы!

Поделиться с друзьями:

---

---

Мы поможем в написании ваших работ!