Материал: 2337
31
Практическая работа №5
Статистическая обработка в табличном процессоре EXCEL при сертификации продукции
При проведении сертификации продукции часто приходится связывать какие-либо производственные показатели качества изделия с потребительскими показателями качества. Это целесообразно делать построением уравнений регрессии по результатам экспериментов. Однако статистическая обработка результатов экспериментов сопряжена с большими объемами однотипных вычислений, для автоматизации которых удобно использовать табличный процессор
Microsoft Excel.
Табличные процессоры предназначены для обработки информации, представленной в табличной форме в виде двумерных массивов, организованных по столбцам и строкам. К основным функциям табличного процессора относят:
–создание и редактирование электронных таблиц;
–создание многотабличных документов;
–создание итоговых и сводных таблиц на основе имеющихся данных;
–построение диаграмм и их модификация на основе табличных данных;
–сортировка содержимого таблиц и выборка из них информации по заданным фильтрам;
–обмен информацией с базами данных;
–решение прикладных задач с использованием встроенных функций электронных таблиц.
Структура электронных таблиц представлена в виде столбцов и строк, имеющих имена. Столбцы именуются буквами латинского алфавита и двухбуквенными сочетаниями, строки – упорядоченным цифровым набором. Место пересечения строки и столбца называется ячейкой. Имя ячейки составляется из имен столбца и строки и является уникальным.
32
Табличный процессор работает как с отдельными ячейками, так и с блоками ячеек. Имя блока состоит из имен верхней левой и нижней правой ячеек разделенных двоеточием.
У табличного процессора EXCEL таблица максимального размера содержит 256 столбцов и 16384 строки.
Все данные таблицы размещаются в ячейках и могут быть: текстом, чи-
словым значением или формулой.
При вводе формул вначале ставится знак =, а потом сама формула записывается в строку, где между адресами ячеек проставляются все знаки операций, например =A1*D2+(C56/G32). При завершении ввода в клетку и нажатии клавиши Enter, программа вычисляет значение и в данной клетке отображается его результат. Для автоматизации ввода данных используется копирование и вставка, переадресация, перенос. При копировании формул программой автоматически изменяются адреса, если же необходимо оставить некоторые адреса без изменений, то для этого используют знак $, который ставится перед указателем строки или столбца, например, $A14, F$15, $K$18 и т.п.
Рис. 5.1. Статистическая обработка в табличном процессоре EXCEL
33
ЗАДАНИЕ По указанному преподавателем варианту для 14 опытных пар значений
показателей производственного (X) и потребительского (Y) качества подсистем двигателя и коробки передач автомобиля Газель ГАЗ-33021 с помощью табличного процессора EXCEL (программа "Регрессия") построить и выполнить анализ уравнения регрессии Y = F(X)
Варианты выполняемого задания представлены в табл. 5.1, исходные данные для их выполнения – в табл. 5.2 и 5.3.
Таблица 5.1
Варианты выполнения задания
Номера вариантов |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
i |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
Параметры |
|
|
|
|
|
|
|
|
|
|
|
∆X |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0,1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
∆Y |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
|
|
|
|
|
|
|
|
|
|
|
Номера вариантов |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
i |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
2 |
Параметры |
|
|
|
|
|
|
|
|
|
|
|
∆X |
0,01 |
0,01 |
0,1 |
0,05 |
0,01 |
3,0 |
2,0 |
10,0 |
0,1 |
0,01 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
∆Y |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
10,0 |
10,0 |
|
|
|
|
|
|
|
|
|
|
|
|
Номера вариантов |
21 |
22 |
23 |
24 |
25 |
26 |
27 |
28 |
29 |
30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
i |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
2 |
3 |
Параметры |
|
|
|
|
|
|
|
|
|
|
|
∆X |
0,01 |
0,1 |
0,05 |
0,01 |
3,0 |
3,5 |
8,0 |
0,3 |
0,03 |
0,02 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
∆Y |
10,0 |
10,0 |
10,0 |
10,0 |
5,0 |
4,0 |
17,0 |
14,0 |
17,0 |
13,0 |
|
|
|
|
|
|
|
|
|
|
|
|
Номера вариантов |
31 |
32 |
33 |
34 |
35 |
36 |
37 |
38 |
39 |
40 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
i |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
2 |
3 |
4 |
Параметры |
|
|
|
|
|
|
|
|
|
|
|
∆X |
0,2 |
0,07 |
0,04 |
6,0 |
5,5 |
11,0 |
0,4 |
0,07 |
0,05 |
0,35 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
∆Y |
13,0 |
14,0 |
16,0 |
9,0 |
7,0 |
21,0 |
17,0 |
23,0 |
19,0 |
19,0 |
|
|
|
|
|
|
|
|
|
|
|
|
Номера вариантов |
41 |
42 |
43 |
44 |
45 |
46 |
47 |
48 |
49 |
50 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
i |
5 |
6 |
7 |
8 |
9 |
1 |
2 |
3 |
4 |
5 |
Параметры |
|
|
|
|
|
|
|
|
|
|
|
∆X |
0,09 |
0,07 |
9,0 |
7,0 |
11,0 |
0,1 |
0,01 |
0,01 |
0,1 |
0,05 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
∆Y |
19,0 |
19,0 |
13,0 |
9,0 |
21,0 |
10,0 |
10,0 |
10,0 |
10,0 |
10,0 |
|
|
|
|
|
|
|
|
|
|
|
|
14 |
13 |
12 |
11 |
10 |
9 |
8 |
7 |
6 |
5 |
4 |
3 |
2 |
1 |
№ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0,03 |
0,18 |
0,11 |
0,02 |
0,05 |
0,09 |
0,13 |
0,07 |
0,00 |
0,05 |
0,14 |
0,08 |
0,06 |
0,10 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
по(данныеисходныеОсновные производственногопоказателях |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
111 |
96 |
110 |
124 |
112 |
104 |
108 |
117 |
119 |
124 |
95 |
112 |
107 |
97 |
4 |
|
|
1 |
|
|
||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
|
|
0,05 |
0,00 |
0,04 |
0,02 |
0,05 |
0,03 |
0,06 |
0,03 |
0,04 |
0,02 |
0,01 |
0,03 |
0,04 |
0,02 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
|
|
112 |
121 |
117 |
121 |
117 |
119 |
113 |
120 |
114 |
118 |
123 |
114 |
118 |
117 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
|
|
0,02 |
0,03 |
0,00 |
0,03 |
0,06 |
0,02 |
0,04 |
0,05 |
0,03 |
0,04 |
0,01 |
0,03 |
0,05 |
0,02 |
3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
119 |
108 |
116 |
110 |
104 |
120 |
114 |
108 |
117 |
107 |
122 |
109 |
103 |
112 |
3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0,14 |
0,20 |
0,10 |
0,22 |
0,12 |
0,17 |
0,28 |
0,16 |
0,21 |
0,26 |
0,14 |
0,18 |
0,13 |
0,17 |
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
98,3 |
60,7 |
87,3 |
51,3 |
77,3 |
70,9 |
50,8 |
63,2 |
75,4 |
35,0 |
91,9 |
80,0 |
70,3 |
70,8 |
Y |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0,15 |
0,10 |
0,16 |
0,24 |
0,17 |
0,15 |
0,17 |
0,20 |
0,18 |
0,17 |
0,13 |
0,28 |
0,16 |
0,21 |
X |
X( |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
i |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
пассивногоопытам) 14 потребительскогои ) |
34 |
75,0 |
100,1 |
97,5 |
55,4 |
90,2 |
105,9 |
72,6 |
68,3 |
93,8 |
80,5 |
88,8 |
57,0 |
81,1 |
75,5 |
5 |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
0,04 |
0,03 |
0,02 |
0,05 |
0,03 |
0,07 |
0,04 |
0,02 |
0,05 |
0,08 |
0,03 |
0,06 |
0,04 |
0,03 |
Y |
|
|
6 |
|
|
||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
|
|
186 |
185 |
187 |
181 |
187 |
179 |
183 |
189 |
185 |
177 |
180 |
182 |
187 |
185 |
6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
|
|
4 |
5 |
9 |
5 |
5 |
2 |
10 |
7 |
1 |
5 |
8 |
4 |
6 |
3 |
X |
|
|
7 |
|
|
||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
оэксперимента качества) |
Таблица |
49,6 |
38,9 |
37,2 |
46,3 |
40,4 |
56,0 |
32,8 |
36,5 |
47,2 |
42,1 |
35,3 |
39,8 |
33,9 |
42,7 |
9 |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
Y ( |
|
9,5 |
12,0 |
10,5 |
9,5 |
8,5 |
11,0 |
11,5 |
10,0 |
13,0 |
8,0 |
11,0 |
10,0 |
9,0 |
10,5 |
8 |
i |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
|
|
152 |
139 |
162 |
187 |
160 |
127 |
119 |
159 |
112 |
188 |
157 |
155 |
147 |
160 |
8 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
350 |
450 |
310 |
250 |
300 |
510 |
370 |
280 |
320 |
400 |
200 |
330 |
420 |
290 |
X |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
39,2 |
36,8 |
41,2 |
40,5 |
38,3 |
35,1 |
40,6 |
41,1 |
40,9 |
35,8 |
42,4 |
40,9 |
39,5 |
38,9 |
Y |
|
|
9 |
|
2. |
||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
35
Таблица 5.3 Дополнительные данные (по 4 опытам) пассивного эксперимента о показателях производственного (Xi) и потребительского (Yi) качества
№ |
X1 |
Y1 |
X2 |
Y2 |
X3 |
Y3 |
X4 |
Y4 |
X5 |
Y5 |
X6 |
Y6 |
X7 |
Y7 |
X8 |
Y8 |
X9 |
Y9 |
1 |
0,09 |
109 |
0,01 |
121 |
0,02 |
120 |
0,11 |
73,8 |
0,22 |
60,4 |
0,02 |
189 |
9 |
32,1 |
10 |
158 |
330 |
40,7 |
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
0,04 |
116 |
0,04 |
117 |
0,05 |
106 |
0,22 |
55,7 |
0,1 |
100,4 |
0,07 |
181 |
4 |
39,5 |
8,5 |
176 |
290 |
40,5 |
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
0,13 |
103 |
0,03 |
118 |
0,01 |
123 |
0,15 |
86,3 |
0,18 |
91,1 |
0,01 |
196 |
6 |
35,2 |
11 |
135 |
380 |
38,2 |
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
0,07 |
111 |
0,06 |
113 |
0,07 |
106 |
0,25 |
40,1 |
0,17 |
73,3 |
0,04 |
180 |
3 |
44,8 |
9,5 |
159 |
230 |
42,6 |
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Пример определения исходных параметров задания по выданному варианту № 27.
|
1. |
Из таблицы 5.1 |
находим для варианта № 27 i = 9, ∆X = 8,0, ∆Y = 17,0; |
|||||||||||||
|
2. |
Из таблицы 5.2 |
определяем опытные данные для X9 и Y9: |
|
|
|
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
290 |
|
420 |
330 |
200 |
400 |
320 |
280 |
370 |
510 |
300 |
250 |
310 |
450 |
350 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
38,9 |
|
39,5 |
40,9 |
42,4 |
35,8 |
40,9 |
41,1 |
40,6 |
35,1 |
38,3 |
40,5 |
41,2 |
36,8 |
39,2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3. Добавляем к каждому значению X величину ∆X = 8,0, а к значению Y – величину ∆Y = 17,0. В результате получим исходные данные для статистической обработки:
X |
298 |
428 |
338 |
208 |
408 |
328 |
288 |
378 |
518 |
308 |
258 |
318 |
458 |
358 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Y |
55,9 |
56,8 |
57,9 |
59,4 |
52,8 |
57,9 |
58,1 |
57,6 |
52,1 |
55,3 |
57,5 |
58,2 |
53,8 |
56,2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|