Лабораторная работа 9Аппроксимация
 Скачать 2.82 Mb.
|
|
2 2022 Оглавление Лабораторная работа №6. Условные операторы и программирование разветвления программы на VBA........................................................................................................................ 4 Лабораторная работа №7. Циклы на VBA ................................................................................ 12 Лабораторная работа №8. Интерполяция и экстраполяция. ................................................... 20 Лабораторная работа №9Аппроксимация. ................................................................................ 30 3 4 Лабораторная работа №6. Условные операторы и программирование разветвления программы на VBA Цель работы: научиться на VBA: программировать разветвляющиеся вычисления с помощью инструкций IF-THEN-ELSE и SELECT CASE, а также устанавливать выборочный доступ к листам Excel по паролям Порядок работы: Уровень Примечание А 1.На первых трех листах набрать «секретную» информацию. Защитить эти листы от записи в ячейки с помощью команд меню «Сервис» - «Защита» - «Защитить лист» и скрыть три первых листа (перед этим добавить лист 4) с помощью команд меню «Формат» - «Лист» - «Скрыть». Сохранить файл с «секретной» информацией на диске под именем LAB10ФИО.xls (при его открытии первые 3 листа должны быть не видны) 2.Через меню «Сервис-Макрос» вызвать редактор VBA, вставить модуль и набрать пример программы проверки пароля при автозагрузке файла с помощью инструкции IF- THEN-ELSE. Проверить и отладить ее работу в пошаговом режиме. 3. Поочередно открывать «секретный» файл LAB10ФИО.xls (при этом должен автоматически запрашиваться пароль). Показать, что при наборе пароля “LEVEL1” все три листа становятся видимыми с доступом к записи в ячейки, пароля” LEVEL2” – становится видимым лист 1 с доступом к записи в этот лист (лист 2 должен оставаться невидимым, а лист 3 – защищен от записи в ячейки), пароля” LEVEL3” – лист 1 появляется только для чтения ячеек. Любой другой пароль не должен показывать листы с «секретной» информацией. Максимум 30 баллов 4.Изменить программу доступа к листам по паролям в соответствии с вариантом индивидуального задания и добиться в правильности работы программы, проверяя ее в соответствии с п. 3. В 5.Заменить инструкцию IF-THEN-ELSE на инструкцию SELECT CASE и добиться такой же работы программы. Пример найти в рекомендованной литературе (по ссылке, найденной в л.р.№2 и 3). 6.Изменить пароли “LEVEL1”, “LEVEL2”, “LEVEL3” на свои собственные и поставить пароль на саму программу. В отчете представить блок-схему разветвления программы по своему индивидуальному заданию. +40 баллов, если пройден уровень А Индивидуальное задание: LEVEL1 – все три листа видимы, но защищены от записи, LEVEL2 – все три листа видимы и доступны для записи, LEVEL3 – видим, но защищен от записи только 3 лист Пример программы: 5 Sub Auto_Open() PassWord End Sub Sub PassWord() Dim PassW As String PassW = GetPassWord If PassW = "LEVEL1" Then For Each Sheet In ActiveWorkbook.Sheets Sheet.Visible = True Sheet.Unprotect Next MsgBox "Вы получили полный доступ" ElseIf PassW = "LEVEL2" Then ActiveWorkbook.Worksheets(1). Visible = True ActiveWorkbook.Worksheets(1). Unprotect MsgBox "Вы получили полный доступ к листу1" ElseIf PassW = "LEVEL3" Then ActiveWorkbook.Worksheets(1). Visible = True MsgBox " Вы получили доступ на чтение к листу1" Else MsgBox "Пароль неверен" End If End Sub Function GetPassWord() GetPassWord = LCase(InputBox("ВВЕДИ ПАРОЛЬ: ", "PassWord")) End Function Ход работы. Задание А. 6 1.Для того, чтобы начать работу, необходимо открыть Excel, написать на первых трёх листах любую “секретную информацию”,(информацию, которую нужно будет скрыть и защитить) Лист1 Лист 2 Лист 3 Рисунок 1.1. Запись информации на листах. 2. Защищаем эти листы от записи в ячейки с помощью команд меню «Сервис» - «Защита» - «Защитить лист». Рисунок 1.2. Защита листов. 7 3. Добавить лист 4. После этого скрыть три первых листа, с помощью команд меню «Формат» - «Лист» - «Скрыть». Рисунок 1.3. Скрытые листы. 4.Сохранить файл с «секретной» информацией на диске под именем LAB10Зайнутдинова.xls (при его открытии первые 3 листа должны быть не видны) Рисунок 1.4. Сохранение файла. 5. Через меню «Сервис-Макрос» вызвать редактор VBA, вставить модуль Рисунок 1.5. Вставка модуля. 6. Набираем пример программы проверки пароля при автозагрузке файла с помощью инструкции IF-THEN-ELSE. 8 Рисунок 1.6. Программа проверки пароля. 7. Проверить и отладить ее работу в пошаговом режиме. Рисунок 1.7. Проверка работы. 8.Поочередно открывать «секретный» файл LAB10Смагин.xls (при этом должен автоматически запрашиваться пароль) Рисунок 1.8. Открытие файла. 9. При наборе пароля “LEVEL1” все три листа становятся видимыми с доступом к записи в ячейки. 9 Рисунок 1.9. При пароле “LEVEL1”. 10. При наборе пароля” LEVEL2” – становится видимым лист 1 с доступом к записи в этот лист (лист 2 должен оставаться невидимым, а лист 3 – защищен от записи в ячейки). Рисунок 1.10. При пароле “LEVEL2”. 11. При наборе пароля” LEVEL3” – лист 1 появляется только для чтения ячеек. 10 Рисунок 1.11. При пароле “LEVEL3”. 12. Любой другой пароль не должен показывать листы с «секретной» информацией. Рисунок 1.12. Проверка другим паролем. 13.Изменяем программу доступа к листам по паролям в соответствии с вариантом индивидуального задания и добиваемся в правильности работы программы. 11 Рисунок 1.13.Изменение программы и её проверка. Задание В. 1.Заменяем инструкцию IF-THEN-ELSE на инструкцию SELECT CASE и добиваемся такой же работы программы. Рисунок 2.1. Замена инструкции. Контрольные вопросы: 1.Какие управляющие структуры для разветвления программ VBA вы знаете? 2. Для чего предназначена структура ELSEIF? 3.Чем отличается структура IF от структуры SELECT CASE? 4.Какой вид условия можно применять после служебного слова IF? 5.Можно ли делать вложенные структуры IF (SELECT CASE)? Вывод В ходе данной лабораторной работы мы научились на VBA: программировать разветвляющиеся вычисления с помощью инструкций IF-THEN-ELSE и SELECT CASE, а также устанавливать выборочный доступ к листам Excel по паролям. 12 Лабораторная работа №7. Циклы на VBA Цель работы: научиться на VBA применять циклы FOR-NEXT, WHILE-WEND, DO- LOOP, FOR-EACH на примере инженерного расчета разгазирования пластовой нефти. Порядок работы: Уровень Примечание A 1. На листе 1 набрать справочную информацию по составу пластовой нефти. Столбец 1 – название компонента нефти, столбец 2 – химическая формула, столбец 3 –молекулярная масса по таблице Менделеева(Mi), первые 2 строки использовать для заголовков столбцов) 2. В столбце 4 набрать состав пластовой нефти в % мольных Ci, в ячейках A15,A17 - набрать исходное количество пластовой нефти (m) и коэффициент пересчета тонн в баррели (для заданной Вам нефти). Для контроля под столбцами поместить сумму элементов (сумма концентраций компонентов должна быть 100%) . 5 столбец задать через формулу Excel как произведение 4 столбца на 3 столбец. 3. Поместить на лист 1 кнопки «Расчет» и «Очистка». Подготовить заголовки для всех расчетных столбцов и ячеек на листе. Сохранить файл. Максимально 45 баллов 4. По заданным расчетным формулам набрать пример программы перерасчета состава пластовой нефти из мольных % (Ci) в число молей (Ni) массовые % (Gi), а также расчета массы каждого компонента (mi), количества товарной нефти Kn (жидкая фаза пластовой нефти при н.у.) в тоннах и баррелях, количества нефтяного газа Kg ( углеводороды из газовой фазы пластовой нефти при н.у.) и количества балластного газа (Kb) в тоннах и кубометрах. 5. Отладить программу расчета и очистки по шагам и по кнопкам. Результаты проверить по закону сохранения массы (сумма всех компонентов на каждом шаге должна быть точно равна исходной заданной массе). 13 B 6. Заменить в примере циклы FOR-NEXT на циклы, указанные в индивидуальном задании и добиться того же результата. В отчете все внесенные изменения в программу. 7. Дополнить программу расчета (и очистки) одним из заданных преподавателем циклов для заполнения столбца I (газ нефтяной), столбца J (нефть товарная) или столбца K (газ балластный) Максимально 60 баллов i Расчетные формулы (1) G = M i * C i *100 M i * C i i=1 (2) m i = m*G i /100 14 Ход работы: Задание А 1. На листе 1 набираем справочную информацию по составу пластовой нефти. Столбец 1 – название компонента нефти, столбец 2 – химическая формула, столбец 3 – молекулярная масса по таблице Менделеева (Mi), первые 2 строки использовать для заголовков столбцов). Рисунок 1.1 2. В столбце 4 набираем состав пластовой нефти в % мольных Ci, в ячейках A15,A17 – набираем исходное количество пластовой нефти (m) и коэффициент пересчета тонн в баррели для заданной нам нефти. Для контроля под столбцами помещаем сумму элементов (сумма концентраций компонентов должна быть 100%) . В 5 столбец задаем через формулу Excel как произведение 4 столбца на 3 столбец. 15 Рисунок 1.2 2. Помещаем на лист 1 кнопки «Расчет» и «Очистка». Рисунок 1.3 3. Подготавливаем заголовки для всех расчетных столбцов и ячеек на листе и сохраняем файл. 16 Рисунок 1.4 5. По заданным расчетным формулам набираем пример программы перерасчета состава пластовой нефти из мольных % (Ci) в число молей (Ni) массовые % (Gi), а также расчета массы каждого компонента (mi), количества товарной нефти Kn (жидкая фаза пластовой нефти при н.у.) в тоннах и баррелях, количества нефтяного газа Kg ( углеводороды из газовой фазы пластовой нефти при н.у.) и количества балластного газа (Kb) в тоннах и кубометрах. 17 18 Рисунок 1.5 6. Присваиваем программу расчета и очистки к созданным ранее кнопкам. 7. Проверяем результаты по закону сохранения массы (сумма всех компонентов на каждом шаге должна быть точно равна исходной заданной массе)/ Рисунок 1.6 8. Значения в окне во время расчета (8 цикл) должны соответствовать столбцу H. 19 Рисунок 1.7 Контрольные вопросы 1. Какие структуры для циклического выполнения фрагментов программ VBA Вы знаете? 2. Для чего предназначена структура WHILE-WEND? 3. Чем отличается структура WHILE-WEND от структуры DO-WHILE-LOOP? 4. Чем отличается структура DO-UNTIL-LOOP от структуры DO-WHILE-LOOP? 5. Сколько раз выполняется цикл с помощью структуры FOR-EACH? 6. Сколько раз выполняется цикл с помощью структуры FOR-NEXT? 7. Какой вид условия можно применять после служебного слова UNTIL? Вывод по проделанной работе: В ходе выполнения данной лабораторной работы я научилась применять на VBA циклы FOR-NEXT, WHILE-WEND, DO-LOOP, FOR-EACH на примере инженерного расчета разгазирования пластовой нефти. 20 Лабораторная работа № 8. Интерполяция и экстраполяция. Цель работы: научиться применять формулы полиномиальной и сплайн-интерполяции для восстановления отсутствующих (дефектных) значений в массивах экспериментальных данных. Порядок работы: Уровень Примечание А 1. Подготовить исходный (точный) ряд данных на листе Excel в виде 2- х столбцов (А3:В13) для X и Y. Где X – аргумент, изменяющийся с некоторым шагом, Y – некоторая функция от Х, например, Y=SIN(X) и представить этот массив на графике, например, - один период синусоиды. 2. Подготовить заголовки для дефектного ряда данных, восстановленного ряда (по формуле Лагранжа), восстановленного ряда (по формуле сплайн- интерполяции) с соответствующими графиками. 3. Разместить на листе 4 командные кнопки. В правом верхнем углу поместить таблицу для задания номера дефектной точки, порядка сплайна и вывода результатов восстановления дефектной точки и ее погрешности. В 3, 4 столбце создать дефектный ряд данных путем копирования 1, 2 столбца и «порчи» заданной дефектной точки (в примере это точка 4, для ее Y ввести произвольное значение, например, -1). 4. Сохранить заготовленный файл на диске под своей фамилией с номером лабораторной работы (в двух местах) или отправить файл на свой почтовый ящик для продолжения работы на следующем занятии. 5. По кнопке «Расчет точки по формуле Лагранжа» составить программу, состоящую из трех частей. 1 часть – считывание исходных данных с листа Excel: номер дефектной точки (Nz из ячейки L2), массивы X(i) и Y(i) из 3 и 4 столбцов, а также точное значение Yt из второго столбца для расчета погрешности восстановления. 2 часть – расчет дефектной точки по формуле Лагранжа (см. фрагмент программы ниже). 3 часть – расчет погрешности и запись результатов на лист: в 5, 6 столбцы, в ячейки L3, L4, M4. Max 60 баллов В 6. По кнопке «Очистка» набрать программу стирания содержимого 5, 6 столбцов, ячейки L3, L4, M4. Отладить обе программы с помощью выполнения по шагам, чтобы для 4-ой дефектной точки результат получался как в приведенном ниже примере. 7. Отлаженную программу скопировать и разместить по кнопкам «Расчет по формуле сплайн-интерполяции» и «Очистка». При этом необходимо изменить номера столбцов в очистке (на 7, 8) и заголовки циклов во 21 фрагменте расчета по формуле Лагранжа (см. лекции). А в 1 часть добавить считывание порядка сплайна (Nspline) из ячейки L6. Отладить эту программу по шагам, чтобы для 1 порядка сплайна результат получался как в приведенном ниже примере. 8. Взять у преподавателя номер индивидуального задания и выполнить расчеты, по результатам составить отчет и сделать вывод. Порядок сплайна увеличивать с 1 до максимально возможного, при этом добиться, чтобы результат совпал с формулой Лагранжа. 9. Создать дефектный ряд данных из исходного ряда путем потери нескольких последних точек (конкретное значение задается преподавателем). Скорректировать фрагмент программы восстановления по формуле Лагранжа для задачи экстраполяции (см. лекции) и отладить ее на заданном ниже примере. 10. Взять номер индивидуального задания и на отлаженной программе восстановить все недостающие точки (с расчетом погрешности по каждой). По результатам составить отчет и сделать выводы. Расчетные формулы Формула Лагранжа для N узлов и формула для расчета относительной погрешности: Фрагмент программы расчета по формуле Лагранжа S = 0 For i = 1 To N p = 1 For j = 1 To N If j <> i And j <> Nz Then p = p * ((X(Nz) - X(j)) / (X(i) - X(j))) Next j If i <> Nz Then S = S + Y(i) * p Next i Y(Nz) = S 22 Где N – это общее количество точек в ряде, Nz – номер дефектной точки Ход работы. 1. Подготовить исходный (точный) ряд данных на листе Excel в виде 2- х столбцов (А3:В13) для X и Y. Где X – аргумент, изменяющийся с некоторым шагом, Y – некоторая функция от Х, например, Y=SIN(X) и представить этот массив на графике, например, - один период синусоиды. Рисунок 1.1. Создание столбцов и графика. 2. Подготовить заголовки для дефектного ряда данных, восстановленного ряда (по формуле Лагранжа), восстановленного ряда (по формуле сплайн-интерполяции) с соответствующими графиками. Разместить на листе 4 командные кнопки. Рисунок 1.2.Создание заголовков, графиков и кнопок. 23 3. В правом верхнем углу поместить таблицу для задания номера дефектной точки, порядка сплайна и вывода результатов восстановления дефектной точки и ее погрешности. В 3, 4 столбце создать дефектный ряд данных путем копирования 1, 2 столбца и «порчи» заданной дефектной точки (в примере это точка 4, для ее Y ввести произвольное значение, например, -1). Рисунок 1.3. Создание таблицы. 4. Сохранить заготовленный файл на диске под своей фамилией с номером лабораторной работы (в двух местах) или отправить файл на свой почтовый ящик для продолжения работы на следующем занятии. Рисунок 1.4. Сохранение файла. 5. По кнопке «Расчет точки по формуле Лагранжа» составить программу, состоящую из трех частей. 1 часть – считывание исходных данных с листа Excel: номер дефектной точки (Nz из ячейки L2), массивы X(i) и Y(i) из 3 и 4 столбцов, а также точное значение Yt из второго столбца для расчета погрешности восстановления. 2 часть – расчет дефектной точки по формуле Лагранжа (см. фрагмент программы ниже). 3 часть – расчет погрешности и запись результатов на лист: в 5, 6 столбцы, в ячейки L3, L4, M4. 24 Рисунок 1.5. Написание программы для кнопки “Расчет точки по формуле Лагранжа”. 6. По кнопке «Очистка» набрать программу стирания содержимого 5, 6 столбцов, ячейки L3, L4, M4. Отладить обе программы с помощью выполнения по шагам, чтобы для 4-ой дефектной точки результат получался как в приведенном ниже примере. Рисунок 1.6.Работа программ. 7. Отлаженную программу скопировать и разместить по кнопкам «Расчет по формуле сплайн-интерполяции» и «Очистка». При этом необходимо изменить номера столбцов в очистке (на 7, 8) и заголовки циклов во фрагменте расчета по формуле Лагранжа (см. лекции). А в 1 часть добавить считывание порядка сплайна (Nspline) из ячейки L6.. 25 Рисунок 1.7. «Расчет по формуле сплайн-интерполяции» и «Очистка». 8.Отладить эту программу по шагам. Рисунок 1.8. Проверка по шагам. 26 |