- •Задание
- •Методика обработки экспериментальных данных
- •Первичная обработка экспериментальных данных
- •Определение коэффициентов уравнения методом наименьших квадратов
- •Аппроксимация степенной функцией
- •Аппроксимация экспоненциальной зависимостью
- •Определение теоретических значений средней толщины стенки трубы
- •Определение теоретических значений толщины стенки трубы
- •А.В. Данилин
Определение теоретических значений средней толщины стенки трубы
Шаг 18: В ячейке А56 набрать «Определение степенных показателей при аргументе».
Шаг 19: Задавшись степенью полинома для аппроксимации экспериментальных данных, выполнить следующее:
-
присвоить ячейкам А42 … А53 имя «n»;
-
присвоить ячейкам В42 … В53 имя «s»;
-
в ячейки В58 … I58 ввести числовые значения от 0 до 7;
-
в ячейки А59 … А66 ввести числовые значения от 0 до 7;
-
присвоить ячейкам В58 … I58 имя «i»;
-
присвоить ячейкам имя А59 … А66 «j»;
Шаг 20: Выделить ячейки В59 … I66 и ввести в строку формул выражение с массивами: =i+j (нажать Ctrl+Shift+Enter);
Шаг 21 Отформатировать таблицу.
Шаг 22: В ячейке А69 набрать «Определение коэффициентов линейной системы уравнений».
Шаг 23: В ячейке В71(I71) набрать «матрица коэффициентов». В ячейке J71 набрать «вектор свободных членов».
Шаг 24: Выделить ячейку В72 и ввести формулу: =СУММ(n^В59) (нажать Ctrl+Shift+Enter); Скопировать из ячейки В72 формулу во все остальные ячейки области В72 … I79
Рисунок 3 . Вид таблиц «Определение степенных показателей при аргументе» и «Определение коэффициентов линейной системы уравнений»
Шаг 25: Выделить J72 и ввести формулу: =СУММ(n^А59*s) ( нажать Ctrl+Shift+Enter); Скопировать из ячейки J72 формулу в ячейки J73 … J79;
Шаг 26 Отформатировать таблицу.
Шаг 27: В ячейке А82 набрать «Вычисление параметров обратной матрицы и искомых коэффициентов системы уравнений».
Шаг 28: В ячейке В84 набрать «Параметры обратной матрицы». В ячейке J84 набрать «Искомые коэффициенты системы».
Шаг 29: Выделить ячейки В85 … I92 и ввести формулу: =МОБР(В72: I79)(нажать Ctrl+Shift+Enter);
Шаг 30: Выделить ячейки J85 … J92 и ввести формулу: =МУМНОЖ(В85:I92; J72: J79)(нажать Ctrl+Shift+Enter); В ячейках J85 … J92 будут находиться коэффициенты аi полинома:
;
Шаг 31: Присвоить ячейкам J85 … J92 имя «kf»;
Шаг 32: В ячейке D95 набрать «Расстояние сечения от торца трубы, мм».
Шаг 33: В ячейке Е95 набрать «Средняя толщина стенки трубы, мм». В ячейке Е96 набрать «опытная». В ячейке F96 набрать «теоретическая».
Шаг 34: Выделить и скопировать ячейки двух столбцов А42:А53 и В42:В53, а затем с помощью опции «Специальная вставка» вставить значения соответственно в ячейки D98:D109 и Е98:Е109;
Шаг 35: Выделить ячейку F98 и ввести формулу: =СУММ(kf*A42^j)(нажать Ctrl+Shift+Enter);
Скопировать из ячейки F98 формулу в ячейки F99 … F109;
Шаг 36 отформатировать таблицу.
Шаг 37: Переименовать текущий лист. Указав его имя «ВАРИАНТ-1».
Рисунок 4 . Вид таблицы «Вычисление параметров обратной матрицы и искомых коэффициентов системы уравнений »
С ЧАСТИЧНОЙ АВТОМАТИЗАЦИЕЙ РАСЧЕТА
Определение опытных значений толщины стенки трубы
ШАГ 1: Переименовать лист 2, указав его имя «ВАРИАНТ-2».
ШАГ 2: Скопировать информацию из ячеек рабочего листа «ВАРИАНТ-1» в аналогичные ячейки рабочего листа «ВАРИАНТ-2»;
ШАГ 3: Заполнить таблицу «Результат обработки опытных значений толщины стенки трубы», скопировав информацию из ячеек B6 … I17 в ячейки B24 …I35;
ШАГ 4: Дополнить таблицу «Показатели обработки опытных значений толщины стенки трубы» двумя столбцами. В ячейку L40 ввести «Выпадающее значение толщины стенки , мм». Выделить ячейки L40…L41 и отформатировать их , указав опции: «Объединение ячеек», «Переносить по словам», «Выравнивание – по центру», «Начертание - полужирный». В ячейку М40 ввести «Номер точки выпадающего значения». Выделить ячейки М40…М41 и отформатировать их, указав опции: «Объединение ячеек», «Переносить по словам», Выравнивание - по центру»б «Начертание полужирный»
ШАГ 5: В диапазоне В42:В53 разместить числовые значения средней величины толщины стенки трубы, используя статистическую функцию СРЗНАЧ(). В ячейку В42 ввести формулу = СРЗНАЧ(В24:I24). Формулу из ячейки В42 скопировать в ячейки В43:В53;
ШАГ 6: В диапазоне С42:С53 разместить числовые значения суммарного квадратичного отклонения , используя статистическую функцию КВАДРОТКЛ(). В ячейку С42 ввести формулу = КВАДРОТКЛ(В24:I24). Формулу из ячейки С42 скопировать в ячейки С43:С53;
ШАГ 7: В диапазоне D42:D53 разместить числовые значения числа ячеек в строке занятых данным (не пустых ячеек), используя статистическую функцию СЧЁТ(). В ячейку D42ввести формулу : = СЧЁТ(В24:I24). Формулу из ячейки D42 скопировать в ячейки D43:D53;
ШАГ 8: В диапазоне G43:G53 построчно разместить минимальное числовое значение толщины стенки трубы. В ячейку G42 ввести формулу: МИН(В24:I24). Формулу из ячейки G42 скопировать в ячейки G43:G53;
ШАГ 9: В диапазоне ячеек H43:H53 построчно разместить максимальное числовое значение толщины стенки трубы. В ячейку H42 ввести формулу: МАКС(В24:I24). Формулу из ячейки Н42 скопировать в ячейки H43:H53;
ШАГ 10: В диапазоне ячеек L42:L53 необходимо указать наиболее «выпадающее» значение толщины стенки трубы (ниже значения нижней границы или выше значения верхней границы доверительного интервала). В ячейку L42 ввести формулу
= ЕСЛИ(И(J42>0;K42>0);0;ЕСЛИ(J42<K42;G42;H42)). Формулу ячейки L42 скопировать в ячейки L43:L53;
ШАГ 11: В Диапазоне М42:М53 необходимо указать наиболее «выпадающее» значение толщины стенки трубы. В ячейку М42 ввести формулу: = ЕСЛИ(L42=0;0;ПОИСКПОЗ(L42;B24:I24;0)).Формулу из ячейки М42 скопировать в ячейки М43:М53;
ШАГ 12: Выделить ячейки А42:А53 и присвоить им имя «номер». Выделить ячейки В42:В53 и присвоить им имя «стенка»;
ШАГ 13: Отформатировать таблицу «Показатели обработки опытных значений толщины стенки трубы»;
ШАГ 14: Войти в редактор алгоритмического языка Visual Basiс , используя меню «Сервис»-«Макрос»-«Редактор Visual Basiс»;
ШАГ 15: Создать модульный лист, используя меню «Вставка»-«Модуль»;
ШАГ 16: В модульном листе «Модуль 1» набрать следующий программный код макроса:
Public Sub обработка()
Dim i As Integer, j As Integer
Do: j = 0
For i = 42 To 53
If Cells(i, 13) > 0 Then
Cells(i - 18, 1 + Cells(i, 13)) = ""
j = j + 1
End If
Next i
Loop Until j = 0
End Sub
ШАГ 17: По окончании набора вернуться в рабочий лист, используя последовательность команд меню: «Файл»-«Закрыть и вернуться в Microsoft Excel»;
ШАГ 18: Используя меню «Сервис»-«Макрос»-«Макросы…» открыть диалоговое окно «Макрос». Сделать текущим макрос «Обработка». Нажать кнопку «Параметры…». В диалоговом окне «Параметры макроса» указать сочетание клавиш для запуска макроса, удерживая нажатой клавишу «Shift» , нажать клавишу «Z». Щелкнуть кнопку «ОК» и затем закрыть окно «Макрос»;
ШАГ 19: Запустить макрос «Обработка» нажав комбинацию клавиш <Ctrl+Shift+Enter>;
ШАГ 20: По завершению работы макроса сравнить опытные значения средней толщины стенки трубы в рабочих листах «ВАРИАНТ 1» и «ВАРИАНТ 2»;