Аппроксимация табличных значений математической формулой.
На первом уроке уже было рассмотрено, как с помощью «мастера диаграмм» аппроксимировать исходные данные математической формулой. Во многих случаях исходные данные имеют форму таблиц, в которых данные постоянны в определенных интервалах. С такими таблицами мы уже сталкивались в данном уроке. Как представить таблицу графически? Рассмотрим это на примере таблицы 1.8.
Функция (в данном случае Группа скоростей) имеет постоянное значение в определенном интервале.
Таблица 1.8 Группа скоростей подачи комбайна и соответствующая ей скорость подачи
| Группа рабочих скоростей комбайна | Средние рабочие скорости подачи, м/мин | Группа рабочих скоростей комбайна | Средние рабочие скорости подачи, м/мин |
| до 0,387 | 1,061-1,220 | ||
| 0,388-0,448 | 1,221-1,420 | ||
| 0,449-0,512 | 1,421-1,668 | ||
| 0,513-0,590 | 1,669-1,950 | ||
| 0,591-0,680 | 1,951-2,286 | ||
| 0,681-0,790 | 2,287-2,710 | ||
| 0,791-0,910 | 2,711-3,220 | ||
| 0,911-1,060 | 3,221-3,820 |
Перепишем таблицу, указав начало и конец каждого интервала (таблица 1.9).
| Средние рабочие скорости подачи, м/мин | Группа рабочих скоростей комбайна | Средние рабочие скорости подачи, м/мин | Группа рабочих скоростей комбайна | Средние рабочие скорости подачи, м/мин | Группа рабочих скоростей комбайна |
| 0,378 | 0,911 | 1,951 | |||
| 0,388 | 1,06 | 2,286 | |||
| 0,448 | 1,061 | 2,287 | |||
| 0,449 | 1,22 | 2,71 | |||
| 0,512 | 1,221 | 2,711 | |||
| 0,513 | 1,42 | 3,22 | |||
| 0,681 | 1,421 | 3,221 | |||
| 0,79 | 1,668 | 3,82 | |||
| 0,791 | 1,669 | ||||
| 0,91 | 1,95 |
Известным приемом EXCEL построим график зависимости группы скоростей от величины скорости подачи комбайна (рис. 1.9).
Рис. 1.9 Зависимость группы скоростей от скорости подачи комбайна
Анализ графика позволяет заключить, что наиболее близко отвечает табличным значениям логарифмическая зависимость. Таким образом, зависимость группы рабочих скоростей от рабочей скорости комбайна имеет вид G = 6,58Ln(v)+7,99
Однако, группа рабочих скоростей может принимать только целое значение, а приведенная формула этого не обеспечивает. В расчетах следует принимать целое значение (округлять до ближайшего целого значения).
Запишем программу нахождения группы рабочих скоростей комбайна при известной средней скорости подачи.
| А | В |
| Средняя скорость подачи комбайна, м/мин | |
| Группа рабочих скоростей | =если(В1 <0,388;1;еслиВ1<3,821;округл(6,58*Ln(В1)+7,99;0);17)) |
Составьте EXCEL-программу и убедитесь, что при средней скорости подачи 3 м/мин группа скоростей составит 15, а при скорости 4,5 соответственно 17.
Полученную формулу можно использовать и при «ручном» счете. Тогда она имеет вид:
Использование данных, заданных графически
На рис. 3.2. «Инструкции по безопасному ведению горных работ на пластах, опасных по внезапным выбросам угля, породы и газа» (Минуглепром СССР, Москва, 1989), приведена номограмма для определения ширины зоны опорного давления. На приведенном ниже рисунке показана эта номограмма для диапазона мощности пласта от 0,5 м до 3,0 м.
Рис. 1.10 Зависимость размера зоны опорного давления от глубины
Если необходимо использовать эту зависимость в дальнейших вычислениях, то как использовать ее в программировании?
Прежде всего, график необходимо преобразовать в таблицу. В данном случае это двухмерная таблица.
Таблица 1.10 Табличные значения данных диаграммы рис. 1.10
| Глубина работ | М о щ н о с т ь п л а с т а , м | ||
| 0,5 | 1,5 | 2,5 | |
| 19,5 | 35,2 | 44,3 | |
| 43,8 | 56,3 | ||
| 33,3 | 44,3 | 66,9 | 73,3 |
| 38,4 | 66,7 | ||
| 41,6 | 52,5 | 70,6 | 86,6 |
| 44,3 | 64,3 | 81,7 | 89,4 |
Возможны два приема: а) множественная регрессия, б) аппроксимация по одной переменной и интерполирование по другой.
В данном случае явно видно, что зависимость размера зоны Lоп от глубины работ Н не является линейной. Установим вид зависимости Lоп от Н. Для этого по данным таблицы строим график с помощью «Мастера диаграмм» (рис. 1.11) и устанавливает уравнение тренда. Достаточно рассмотреть крайние кривые рисунка.
Рис. 1.11 Зависимость размера зоны опорного давления от глубины
работ и мощности пласта (график построен по данным таблицы 1. )
Наиболее подходит для данного случая логарифмическая зависимость Lоп от Н. Перестроим таблицу, заменив Н на Ln(H) и построим график в новых координатах.
| Ln(H) | М о щ н о с т ь п л а с т а , м | |||
| 0,5 | 1,5 | 2,5 | ||
| 5,298317 | 19,5 | 35,2 | 44,3 | |
| 5,991465 | 43,8 | 56,3 | ||
| 6,39693 | 33,3 | 44,3 | 66,9 | 73,3 |
| 6,684612 | 38,4 | 66,7 | ||
| 6,907755 | 41,6 | 52,5 | 70,6 | 86,6 |
| 7,090077 | 44,3 | 64,3 | 81,7 | 89,4 |
Рис.1.12 Зависимость размера зоны опорного давления от логарифма глубины работ и мощности пласта (по данным таблицы 1.11)
В данном случае без анализа видно, что зависимость линейная.
Теперь установим форму зависимости размера зоны опорного давления от мощности пласта. Для этого на основании исходной таблицы построим графики зависимости Lоп от m.
И так. Искомое уравнение будем находить для переменных Ln(H) и m.
1. Построим в EXCEL регрессионную таблицу по данным таблицы исходной. В самой левой колонке расположим H, в следующей Ln(H), затем m и Lоп.
Рис. 1.13 Зависимость размера зоны опорного давления от мощности
Данные таблицы формируем так, чтобы было удобно копировать их из исходной таблицы. Так, вначале вносятся данные о размере зоны при мощности пласта 0,5 м и глубинах от 200 м до 1200 м, затем данные при тех же глубинах, но мощности пласта 1,0 м. Когда все данные о функции внесены, вычисляется величина Ln(H) (команда — =Ln(«ячейка для Н»)).
В главной строке активизируем окно «Сервис«. В выползающем меню активизируем строку «Анализ данных«. В выпавшем меню «Инструменты анализа» активизируем строку «Регрессия«.
Появляется шаблон для ввода «Входной интервал по Y» и «Входной интервал для Х«. Ставим курсор в окошко «Входной интервал для Y«, в регрессионной таблице выделяем столбец для Lоп (D2:D37).
Ставим курсор в окошко «Входной интервал для Х«, в регрессионной таблице выделяем столбцы для Ln(H) и m (В2:С37). Нажимаем на клавишу «Ok» в шаблоне.
| Регрессионная статистика | Y-пересечение | -101,702 | |
| Множественный R | 0,98856421 | Переменная X1 | 20,27473 |
| R-квадрат | 0,97725919 | Переменная X2 | 15,29714 |
В нашем случае — множественный коэффициент регрессии 0,98856421, R 2 — 0,97725919, свободный член уравнения -101,7, коэффициент при первой переменной (Ln(H)) 20,27473, коэффициент при второй переменной m 15,29714.
Таким образом, уравнение, аппроксимирующее номограмму, можно записать в виде
Формула применима при мощности пласта от 0,5 м до 3,0 м, глубине работ от 200 м до 1200 м.
Это уравнение можно использовать в программе и при ручном счете.
Программа расчета размера зоны опорного давления имеет вид:
| А | В |
| Исходные данные | |
| Мощность пласта, м | |
| Глубина работ, м | |
| Вычисления | |
| Размер зоны опорного давления, м | =-101,7+20,27*Ln(B3)+15,3*В2 |
В рассмотренном случае мы имели дело с двумя переменными. Рассмотренный метод аппроксимации может использоваться для достаточно большого количества переменных.
1. Запишите в EXCEL регрессионную таблицу, получите регрессионное уравнение, запишите программу вычисления Lоп и убедитесь, что при мощности пласта 1,5 м и глубине работ 600 м размер зоны опорного давления составит 50,91516 м.
2. В приведенной ниже таблице 1.14 показаны величины затрат Ск на сооружение костров для охраны штрека при различных величинах мощности пласта m, сменной тарифной ставки рабочего Т, стоимости 1 м 3 крепежного леса Ц и числа рядов костров N.
Постройте в EXCEL регрессионную таблицу и убедитесь, что уравнение множественной регрессии Ск=f(m,T,Ц,N) имеет вид
Таблица 1.14 Результаты наблюдений за величиной затрат на сооружение костров
[expert_bq id=»1570″]И в заключение если Вам интересна формула по которой построен тренд, в коне Формат линии тренда поставьте флажок напротив показать уравнение на диаграмме. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).
4. Построение графиков в Excel. Аппроксимация функций методом наименьших квадратов — курсовая работа
Ошибка аппроксимации
Оценим качество уравнения регрессии с помощью ошибки абсолютной аппроксимации. Средняя ошибка аппроксимации — среднее отклонение расчетных значений от фактических:
| Регрессионная статистика | Y-пересечение | -101,702 | |
| Множественный R | 0,98856421 | Переменная X1 | 20,27473 |
| R-квадрат | 0,97725919 | Переменная X2 | 15,29714 |
