Часть 1. Использование пакета Microsoft EXCEL

Тема 1 Подготовка к программированию в EXCEL. Обработка результатов наблюдений.

Статистическая обработка результатов наблюдений.

Цель работы - научить студента использованию EXCEL-программы для получения математической зависимости при одной переменной величине. Рассмотрим пример влияния ширины раскоски проводимого широким забоем штрека на трудоемкость проведения штрека.

Результаты наблюдений показаны в таблице 1.1.

Таблица 1.1. Результаты наблюдений влияния ширины раскоски на трудоемкость проведения штрека.

Ширина раскоски, м	Трудоемкость проведения штрека, чел.см/м3	Ширина раскоски, м	Трудоемкость проведения штрека, чел.см/м3
2, 5	0, 61		1, 3
2, 8	0, 6		1, 5
2, 5	0, 58		1, 36
	0, 69		1, 46
3, 2	0, 67	13, 5	1, 6
4, 5	0, 75	15, 5	1, 73
	0, 8		1, 8
	0, 85		1, 9
	0, 79		2, 1
	0, 8		1, 82
			2, 3
	1, 1		2, 4

Если эта зависимость будет в дальнейшем использована в расчетах, желательно иметь математическое описание такой зависимости.

Перенесем данные этой таблицы в лист EXCEL. В листе EXCEL запишем таблицу, начиная с ячейки А1, в которую запишем «Ширина раскоски, м», а в ячейке В1 запишем «Трудоемкость проведения штрека, чел.см/м³», в ячейках А2: В25 запишем конкретные величины наблюдений.

Выделяем ячейки А1: В25 и, используя известные приемы EXCEL («Мастер диаграмм»), покажем эту таблицу в виде графика (рис. 1.1). Используем точечную диаграмму без соединения точек отрезками.

Рис.1.1 Графическое представление зависимости трудоемкости проведения

Штрека от ширины раскоски.

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

1. Линейная зависимость.

Щелкнем левой клавишей на окошке " Линейная зависимость", затем откроем меню " параметры" и активизируем список " показать уравнение на диаграмме" и " поместить на диаграмму величину достоверности аппроксимации R^2" . Диаграмма принимает вид (рис. 1.2).

Рис.1.2 Линейная аппроксимация зависимости

Теперь по той же методике рассмотрим другие виды зависимости:

Рис. 1.3 Логарифмическая аппроксимация зависимости

Ниже показаны графики полиномиальной (рис. 1.4), степенной (рис. 1.5) и экспоненциальной (рис. 1.6) зависимостей.

Рис. 1.4 Аппроксимация полиномиальной зависимостью.

Рис. 1.5 Аппроксимация зависимости степенной функцией

Рис. 1.6 Экспоненциальная аппроксимация зависимости

(Заполните в EXCEL строки с приведенной выше таблицей и выполните вычисления и постройте графики по изложенной методике, сравните с рассмотренными выше графиками).

Все указанные формы зависимости получены на основе " Метода наименьших квадратов", т.е. сумма квадратов отклонений истинных значений от расчетных по данной формуле наименьшая. То, где эта сумма самая маленькая, можно судить по величине R² (смотри таблицу 2.1). В нашем случае значения R² расположились следующим образом:

Таблица 1.2 Уравнения, описывающие зависимость

Вид зависимости	R²	Уравнение
Логарифмическая	0, 8887	y = 0, 7688Ln(x) - 0, 3187
Степенная	0, 9567	y = 0, 302x^{0, 6453}
Экспоненциальная	0, 9569	y = 0, 5403e^{0, 0767x}
Линейная	0, 9692	y = 0, 0954x + 0, 3347
Полиномиальная	0, 9703	y = 0, 0007x² + 0, 0803x + 0, 3917

Таким образом, наибольшие значения R² имеют место при полиномиальной и линейной зависимостях. Одна из этих зависимостей и принимается для дальнейшей работы.

Рассматривая приведенную ниже таблицу, можно убедиться в том, что при полиномиальной форме зависимости сумма квадратов отклонений наименьшая. Так, она составляет 0, 228698 при полиномиальной и 0, 237238 при линейной форме зависимости.

Таблица 1.3 Квадрат отклонений расчетной и истинной величин.

Ширина раскоски, м	Трудоемкость проведения штрека, чел.см/м³	(y-y_i)² при рассмотренных формах зависимости
полиноми-альнаяная	линей-ная	экспонен-циальная	степен-ная	логариф-мическая
2, 5	0, 61	0, 000174	0, 001354	0, 00198	0, 00416	0, 050291
2, 8	0, 6	0, 000485	3, 31E-06	0, 004863	0, 000172	0, 016162
2, 5	0, 58	0, 000283	4, 62E-05	0, 005551	0, 00119	0, 037735
	0, 69	0, 002611	0, 004775	9, 82E-05	0, 005835	0, 026925
3, 2	0, 67	0, 000201	0, 000901	0, 000424	0, 000918	0, 008925
4, 5	0, 75	0, 000297	0, 000196	0, 000169	0, 002221	0, 00768
	0, 8	0, 000114	0, 000137	5, 12E-05	0, 002831	0, 014074
	0, 85	0, 001544	0, 001467	0, 003267	1, 03E-05	0, 004711
	0, 79	0, 000428	0, 000471	8, 08E-06	0, 003995	0, 016547
	0, 8	0, 035382	0, 041006	0, 015448	0, 067654	0, 142367
		0, 006225	0, 009584	4, 12E-06	0, 024181	0, 078386
	1, 1	0, 000445	4, 41E-06	0, 01041	0, 003081	0, 032391
	1, 3	0, 001246	0, 000128	0, 018652	0, 001188	0, 022961
	1, 5	0, 055366	0, 044648	0, 113282	0, 027402	0, 00235
	1, 36	0, 009082	0, 005084	0, 038641	0, 000652	0, 008377
	1, 46	0, 008817	0, 013202	1, 97E-05	0, 014555	0, 037339
13, 5	1, 6	1, 11E-05	0, 000511	0, 006133	0, 000385	0, 006765
15, 5	1, 73	0, 005554	0, 006956	0, 001933	0, 001651	0, 003417
	1, 8	0, 002144	0, 001176	0, 008607	0, 004414	0, 001351
	1, 9	0, 003493	0, 003192	0, 008148	0, 000425	0, 001642
	2, 1	0, 019853	0, 020592	0, 012041	0, 048671	0, 057853
	1, 82	0, 059487	0, 053778	0, 108194	0, 0169	0, 006959
	2, 3	0, 000497	0, 003283	0, 042108	0, 045289	0, 099591
	2, 4	0, 014957	0, 024743	0, 011067	0, 097851	0, 172708
Сумма	0, 228698	0, 237238	0, 411101	0, 37563	0, 857504

Теперь запишем программу для определения трудоемкости работ по проведению штрека (номера строк указаны произвольно). Открываем новый лист в EXCEL, в ячейку В1 будем вводить значение ширины раскоски, в ячейке В2 запишем программу вычисления трудоемкости. Образец программы в ячейке В2 показан ниже.

	А	В
	Ширина раскоски
	Трудоемкость проведения штрека, чел.см/м3	=0, 0007B1^2+0, 0803B1+0, 3917

(Запишите эту короткую программу. Результат – 1, 4561.)

Статистическая обработка результатов наблюдений будет использоваться студентами-горняками при выполнении дипломных проектов и при НИРС. Отдельные примеры использования этого приема будет рассмотрено в дальнейших уроках данного курса.

Задание на самостоятельную работу.

В приведенной ниже таблице по данным ВНИМИ [3] указаны величины смещений пород в выработку в процессе ее проведения U и соответствующие этим смещениям условия проведения выработки – отношение глубины работ H к прочности горных пород в месте расположения выработки R. Необходимо установить наиболее соответствующую этим данным зависимость U = f(H/R).

H/R	U	H/R	U	H/R	U	H/R	U


		12, 5
6, 6		13, 3				33, 3
6, 6
7, 5				23, 3


				26, 6		13, 3
		16, 6		26, 6

⇐ Предыдущая 123 4 5 6 7 8 Следующая ⇒