Лабораторные работы к разделу 10

1. Создать интерфейс, изображенный на рис. 10.1. Использовав процедуры, приведенные на рис. 10.2, опробовать программу.

2. Изменить интерфейс и программу, используя диалоговую форму – как на рис.10.3,10.4.

3. Разместить на листе Excel элементы TextBox1, TextBox2, TextBox3 сопроводив их статическими надписями (Label1, Label2, Label3) со значениями свойства Caption “Операнд 1”, “Операнд 2”, “Результат”. На этом же листе разместить рамку с заголовком “Операции”, а в рамке – переключатели с заголовками, обозначающими названия арифметических операций и конкатанации. На листе Excel также разместить командную кнопку, а в событийной процедуре, соответствующей нажатию кнопки, определить, какую операцию выбрал пользователь, и выполнить ее, поместив результат в поле TextBox3. Отладить программу.

4. Разместить на листе Excel матрицу типа той, что изображена на рис. 10.5, но с конкретными значениями aIJ и с конкретными значениями x1,x2,…,xJ,…,xN. На этот же лист поместить командную кнопку с заголовком «Вычислить», а также поле в сочетании со счетчиком – для выбора номера строки матрицы. Расширить модуль, приведенный после рис. 10.5, включив в него функцию XAI (I), вычисляющую скалярное произведение вектора x (вектор можно рассматривать как массив значений – см. подраздел 10.4) на вектор-строку аI (скалярное произведение векторов – сумма произведений их элементов с одинаковыми номерами). При написании этой функции считать, что вектор x хранится в массиве CX. С помощью разработанной функции вычислить YI=aI1*X1+aI2*X2+…+aIJ*XJ+…+aIN*XN, расширив событийную процедуру модуля. Вычисленным значением YI заменить идентификатор YI в массиве CY. В рассматриваемый модуль включить также процедуру CXCYTab, копирующую содержимое массивов CX,CY в первую строку и первый столбец листа Excel. Обратиться к этой процедуре из событийной процедуры. Отладить программу – она должна правильно умножать нулевой вектор (массив CX) на I-ый вектор, т.е. на I-ю строку матрицы. Варианты дальнейшего развития этой программы предлагаются в курсовых работах.

КУРСОВЫЕ РАБОТЫ

Общие требования и рекомендации

Курсовая работа должна содержать:

- титульный лист установленного образца,

- теоретическую (реферативную) часть, характеризующую назначение и возможности используемого приложения (Excel, Access) или среды программирования (VBA),

- практическую (иллюстративную) часть – решение задачи с помощью Excel (постановка задачи, табличные расчеты, графики или диаграммы, пояснения), создание и использование базы данных Access или создание приложения с применением VBA.

Курсовые работы, содержащие разработку приложений на VBA, рекомендуем для студентов, изучающих информатику в течение двух семестров и дольше. Далее приведены примеры тем с пояснениями для курсовых работ, ориентированных на создание базы данных Access и приложений VBA.

1. База данных “Архив предприятия”

Рис. База данных “Архив”

В окне базы данных перечислены таблицы. На схеме данных показан состав таблиц и связи между ними. Справа изображена форма “Документы”, созданная для удобства ввода данных в таблицу “Документы”. Справа внизу – одна из таблиц: таблица “Типы документов”.

1. Использовать систему управления базами данных (СУБД) Access. Создать новую базу данных в своем рабочем каталоге, присвоив ей имя. В примере, который рассматривается далее, файлу базы данных присвоено имя Архив.mdb.

2. База данных должна содержать несколько таблиц: основную и дополняющие ее таблицы. Пусть основная таблица – Документы (ее состав виден на схеме данных – см. рис. База данных “Архив”, поясняющий задание). В этой таблице фиксируются все документы, поступающие в архив предприятия. Каждому документу присваивается архивный номер и номер экземпляра. В таблице указывается количество экземпляров документа, а номер конкретного экземпляра заносится в таблицу Журнал при его выдаче абоненту. Каждая запись основной таблицы содержит также код типа документа, код объекта, к которому относится документ, и код организации-автора документа. Эти коды являются ключевыми полями в следующих дополняющих таблицах: Типы_док, Объекты, и Организации. В таблице Журнал фиксируются даты выдачи документов абонентам и даты возврата. Таблица Абоненты дополняет таблицу Журнал. Далее приведем рекомендации по созданию таблиц, схемы данных, форм, запросов и отчетов.

3. Для создания таблиц используйте кнопку Создать и в следующем диалоговом окне – кнопку Конструктор. Конструктор позволяет ввести сведения о каждом поле создаваемой таблицы - см. подраздел 9.3. Ключевые поля, идентифицирующие записи в каждой таблице, на схеме данных изображены жирным шрифтом. Чтобы сделать поле ключевым, надо воспользоваться правой клавишей мыши или кнопкой с изображением ключа на панели инструментов. После ввода параметров полей таблицу надо закрыть и при этом сохранить, дав ей имя.

4. Для создания схемы данных воспользуйтесь одноименной кнопкой панели инструментов. Важно, чтобы на схеме были не только представлены таблицы базы данных, но и установлены связи между ними – см. подраздел 9.3. Например, чтобы связать номер абонента в таблице Журнал с номером абонента в таблице Абоненты, надо как бы перетащить мышкой это поле из первой таблицы во вторую – на схеме появится стрелка. Установить связи на схеме данных надо до создания форм, запросов и отчетов, чтобы получить возможность включать в них поля из всех имеющихся таблиц.

5. Для создания формы выберите Формы в меню базы данных, нажмите мышью кнопку Создать и далее выберите из списка Мастера форм. Форма отличается от таблицы тем, что она отображает одновременно только одну запись, но в удобном для обзора и ввода данных виде - см. подраздел 9.3. Создайте формы «Документы» и «Журнал» - последняя облегчит ввод данных в одноименную таблицу.

6. Для создания запроса выберите Запрос в меню базы данных, нажмите кнопку Создать, выберите из списка Конструктор, далее укажите используемые таблицы и поля. Например, можно создать запрос по таблицам Журнал и Абоненты, чтобы определить, кому выданы экземпляры документа с архивным номером 1001. В этом случае для поля Ном арх надо задать условие (=1001). Сохранив запрос под именем «Журнал1001», выберите Отчеты в меню базы данных, нажмите мышью кнопку Создать и далее выберите из списка Мастера отчетов. Для построения отчета в качестве источника данных укажите запрос «Журнал1001».

Курсовые работы на тему “Базы данных” могут иметь разнообразную ориентацию: книги в домашней библиотеке, музыкальные записи, семейные расходы и т.п.

2. Разработка приложения “Решение системы линейных уравнений”

1. Взять за основу модуль, приведенный после рис. 10.5 и расширенный в лабораторной работе 4 раздела 10. Модуль должен содержать следующие общие процедуры:

MNTab - Определение M, N - размерности матрицы,

TabCXCY - Копирование идентификаторов столбцов и строк в массивы CX, CY,

TabA - Копирование матрицы с листа Excel в массив A,

XAI (I) - вычисление скалярного произведения вектора x на вектор aI (функция) с записью результата в I-ю ячейку первого столбца на листе Excel,

Jordan (r) - Шаг жорданова исключения (процедура поясняется ниже) – преобразование всей матрицы A с записью новых значений ее элементов в таблицу на листе Excel, обращение к процедуре TabA, перемена местами элементов CX(r) и CY(r) с записью значений и в ячейки Excel, копирование матрицы с листа Excel в массив A.

2. Разместить на листе Excel командную кнопку BTN1 с надписью (свойство Caption) Решение системы уравнений.

3. В событийную процедуру BTN1_Click включить:

- определение M, N, вывод сообщения и завершение работы, если M не равно N;

- переопределение размерности массивов CX, CY, A и копирование в них содержимого соответствующих ячеек листа Excel;

- выполнение шага жорданова исключения N раз (r – номер диагонального элемента матрицы, называемого разрешающим; с ним выполняется шаг жорданова исключения); перед обращением к процедуре Jordan (r) надо проверять, не равен ли нулю элемент матрицы A(r,r) – этот фрагмент программы можно записать так

For r = 1 To N

If Abs(A(r, r)) < 0.000000001 Then

MsgBox "Матрица содержит линейно зависимые строки (столбцы)": Exit Sub

End If

Jordan r

Next r

(проверка на нуль в программах выполняется с учетом точности вычислений);

- вычисление каждого значения Xi (i=1,…,N) путем умножения вектора y на вектор Ai с использованием функции XAI; заметим, что после того, как были выполнены N шагов жордановых исключений, на месте матрицы A находится обратная матрица A ^-1, а массивы CX и CY поменялись своим содержимым – в массиве CX теперь хранятся известные значения Yj (j=1,…,N), а в массиве CY (и в первом столбце на листе Excel) – идентификаторы X1,…,XN. Эти идентификаторы и надо заменить вычисленными значениями Xi (i=1,…,N).

Поясним алгоритм шага жорданова исключения. В подразделе 10.4 упоминалось, что матрицу A (см. рис. 10.5) можно рассматривать как таблицу коэффициентов, позволяющих связать любой Yi уравнением со значениями X1,…, XN: Yi = x ∙ Ai. Глядя на эту таблицу, над столбиками представим себе значения Xj (вектор-строку x ), а левее строк – значения Yi (вектор-столбец y ). Один шаг жорданова исключения с разрешающим элементом Arr приводит к тому, что Xr и Yr меняются местами, а вся матрица A изменяется так:

- разрешающий элемент A(r,r)=1/A(r,r),

- элементы разрешающего столбца A(i,r)=A(i,r)/A(r,r), i=1,…, N,

- элементы разрешающей строки A(r,j)= - A(r,j)/A(r,r), j=1,…, N,

- прочие элементы A(i,j) = (A(i,j) ∙ A(r,r) – A(i,r) ∙ A(r,j)) / A(r,r).

После того, как будут выполнены N шагов жордановых исключений, вектор y окажется наверху (над матрицей), а вектор x – слева от матрицы. Сама матрица к этому моменту будет обращена – теперь она содержит коэффициенты, позволяющие вычислять значения Xi, если известны значения Yj: X(i)= y ∙ Ai.

Приложение надо отладить на примерах. Подготовить примеры совсем не трудно. Представим себе, что N=3, а значения элементов вектора x известны, например: x ={1,2,3}. Теперь, задавшись матрицей A, например, A ={11,13,13; 21,22,24; 30,32,33}, получим y ={76, 137, 193}. Неизвестными будем считать значения X1=1, X2=2, X3=3. Их и найдем.

Литература к курсовой работе 2. Зуховицкий С.И., Авдеева Л.И. Линейное и выпуклое программирование. М: Наука, 1967. –460 с.

3. Разработка приложения “Статистический анализ данных”

Постановку задачи для этого приложения ограничим упрощенным статистическим анализом: вычислением средних значений признаков, стандартов (среднеквадратических отклонений от генерального среднего), матрицы коэффициентов корреляции и матрицы стандартов коэффициентов корреляции. Поясним эту задачу, обратившись к матрице на рис. 10.5. Теперь матрицу будем рассматривать не как таблицу коэффициентов, связывающих зависимые переменные y ={Y1,…,YN} c независимыми переменными x ={X1,…,XN} (см. предыдущую работу), а как таблицу объекты-свойства. Соответственно, в первом столбце листа Excel разместим идентификаторы объектов (например, их номера: 1,…, M), а в первой строке – идентификаторы свойств (например, X1,…, XN). Одна строка матрицы содержит значения свойств одного объекта, а один столбец – значения одного свойства для всех объектов.

Таблицы объекты-свойства часто применяются в различных предметных областях. Например, в геологии при поисках и разведке полезных ископаемых отбирают из массива горных пород M проб, а в каждой пробе химическими или физическими методами определяют содержания N компонентов (обычно M>N). По этим анализам судят о массиве горной породы. Аналогично тестируют продукцию металлургического комбината – выборочно отбирают образцы и подвергают их анализам на содержание железа, серы и других элементов. Статистически обработав результаты анализов образцов, судят о качестве целой партии продукции. Здесь важно понять, что в подобных ситуациях практически невозможно проанализировать весь массив горных пород или всю партию продукции - как говорят статистики, всю генеральную совокупность. Поэтому в таблицу объекты-свойства включают результаты анализов (измерений) для выборки из генеральной совокупности. По этой выборке находят средние значения Xj_o каждого признака, характеризующие центр рассеяния значений признака и коэффициенты ковариации Sjk, характеризующие меру и форму рассеяния:

Коэффициент ковариации Sjk вычисляется так: берутся два столбца матрицы с номерами j и k, затем суммируются произведения отклонений i-ых элементов этих столбцов от своих средних, и накопленная сумма делится на M, полученное среднее значение произведения отклонений умножается на поправочный коэффициент M/(M-1). Этот коэффициент компенсирует занижение коэффициентов ковариации, которое произошло, т.к. вместо неизвестных генеральных средних в формуле использованы выборочные средние значения признаков.

Рассчитанную матрицу коэффициентов ковариации можно разместить на листе 2 книги Excel – так же, как на листе 1 размещена исходная матрица (см. рис. 10.5), только размерность ковариационной матрицы не M∙N, а N∙N, и идентификаторами строк и столбцов являются идентификаторы признаков (свойств) объектов. Средние значения надо разместить на листе 1 под соответствующими столбцами исходной матрицы, но пропустив одну строку. На листе 1 надо также разместить командную кнопку BTNSTAT с надписью (свойство Caption) Расчет статистик. В начало событийной процедуры BTNSTAT_Click надо включить определение M, N, копирование в массив CX идентификаторов признаков, а в двумерный массив A – матрицы (см. предыдущую работу и модуль, приведенный в подразделе 10.4 после рис. 10.5). После копирования данных в массивы, запрограммируйте вычисление средних значений признаков и матрицы ковариаций.

Диагональные элементы ковариационной матрицы называются дисперсиями. Корни квадратные из дисперсий Sjj называются стандартами s_j или среднеквадратическими отклонениями значений признаков от генеральных средних (от математических ожиданий значений признаков). Стандарты надо вычислить и разместить на листе Excel под средними значениями.

Часто между признаками наблюдаются связи: при переходе от объекта к объекту мы видим, что увеличение признака j, как правило, сопровождается увеличением признака k – это положительная корреляция. Если же при увеличении значения Xj значение Xk, как правило, уменьшается, то это корреляция отрицательная. Близость связи между двумя признаками j и k к линейной оценивается коэффициентом парной корреляции:

r_jk = Sjk / (s_j ∙ s_k), j=1,…, N, k=1,…, N.

Матрицу коэффициентов корреляции можно разместить на месте ковариационной матрицы, нормируя ее элементы на стандарты. Значения коэффициентов парной корреляции должны принадлежать интервалу [-1, 1 ].

Достоверность, или, как говорят статистики, значимость коэффициентов корреляции зависит от объема выборки. Если в выборку включить малое число проб, а рассеяние значений Xj и Xk велико, то коэффициент корреляции случайно может получиться большим или малым. Чтобы оценить значимость коэффициентов парной корреляции, вычисляют их стандарты:

s(r_jk) = (1- r²_jk) / , j=1,…, N, k=1,…, N.

Матрицу стандартов коэффициентов парной корреляции можно разместить на листе 3 книги Excel. С некоторым приближением о значимости коэффициентов корреляции судят так: если │ r_jk │< s(r_jk), то корреляцию нельзя считать значимой.

При разработке рассматриваемого приложения наиболее трудным этапом является программирование вычисления коэффициента ковариации. Это вычисление целесообразно локализовать в функции

Function SJK (j As Integer, k As Integer) As Double

К этой функции необходимо обратиться N∙N раз: цикл по j от 1 до N, а при каждом j цикл по k от 1 до N.

Методы статистического анализа данных полезно знать каждому специалисту. Эта курсовая работа знакомит с началами статистического анализа. Любознательные студенты могут, обратившись к литературе, расширить свои знания в этой области, а при желании – и само разработанное приложение, включив в него, например, вычисление коэффициентов асимметрии и эксцесса для каждого признака, коэффициентов уравнения регрессии, связывающего первый признак с остальными, и т.п.

Литература к курсовой работе 3.

Корн Г., Корн Т. Справочник по математике для научных работников и инженеров. М.: Наука, 1968. –720 с.

Шор Я. Статистические методы анализа и контроля качества и надежности. М.: Советское радио, 1962. –552 с.

Ломтадзе В.В. Программное и информационное обеспечение геофизических исследований. М.: Недра, 1993. –268 с.

4. Разработка приложения “Преобразование географических координат

в прямоугольные и прямоугольных в географические”

Эта курсовая работа особенно полезна для студентов, будущая специальность которых имеет отношение к наукам о Земле (геология, геодезия, экология, горное дело и т.п.). Но, вообще говоря, представлять системы координат, в которых строятся различные карты, должен каждый образованный человек. Известно, что в качестве геометрической модели Земли используют эллипсоид. В России обычно применяют параметры эллипсоида Красовского:

a = 6 378 245.000 м - большая полуось,

b = 6 356 863.019 м - малая (полярная) полуось,

e₁² = 0.006 693 4216 – квадрат первого эксцентриситета,

e₂² = 0.006 719 2188 – квадрат второго эксцентриситета.

Для перехода к прямоугольным координатам эллипсоид (как дыню) разрезают от полюса к полюсу на шестиградусные зоны (дольки). Каждая зона-долька как бы распрямляется – делается плоской. Координата X отсчитывается от экватора, а Y – от осевого меридиана зоны с добавлением 500 км. Эта добавка к координате Y служит для того, чтобы координаты Y были положительными. Таким образом, в каждой зоне свои координаты Y, отсчитываемые от своего осевого меридиана.

Каждая шестиградусная зона делится еще параллелями через 4 градуса, и получаются листы, для которых строятся карты масштаба 1:1000000 (в 1 см 10 км). Каждый лист миллионного масштаба делится на 36 листов масштаба 1:200000 или на 144 листа масштаба 1:100000 (в 1 см 1 км) – так строятся карты в прямоугольных координатах.

Обозначим широту точки через LT (Latitude – широта), а долготу – через LN (Longitude – долгота). С формулами для расчета X, Y по известным значениям LT, LN при заданном осевом (центральном) меридиане CM можно познакомиться по указанным в конце этой работы книгам. Вместо формул, здесь просто приведем начало модуля, содержащего общую процедуру LTLNtoXY, пересчитывающую географические координаты в прямоугольные, и опишем постановку остальных задач, которые надо запрограммировать, чтобы завершить разработку приложения.

Option Explicit

Const Pi180 As Double = 0.0174532925

Const A As Double = 6378245

Const B As Double = 6356863.019

Const EE1 As Double = 0.0066934216

Const EE2 As Double = 0.0067192188

Private Sub LTLNtoXY(LT As Double, LN As Double, X As Double, Y As Double, CM As Double)

' LT – ШИPOTA (радианы)

' LN – ДOЛГOTA (радианы)

' CM - ОСЕВОЙ MEPИДИAH (градусы)

' X, Y – KOOPДИHATЫ (км)

Dim N As Double, DL As Double, S As Double, DL2 As Double, T As Double, T2 As Double

Dim SINLT As Double, COSLT As Double, COS2 As Double, COS3 As Double, COS5 As Double

Dim A2 As Double, A4 As Double, B1 As Double, B3 As Double, B5 As Double, T4 As Double

DL = LN - CM * Pi180: DL2 = DL * DL

SINLT = Sin(LT): COSLT = Cos(LT): T = SINLT / COSLT

T2 = T * T: T4 = T2 * T2

COS2=COSLT*COSLT: COS3=COSLT*COSLT * COSLT: COS5 = COS2 * COS3

N = A / Sqr(1 - EE1 * SINLT * SINLT)

S = 6367558.49587 * LT - 16036.48027 * Sin(2 * LT) + _

16.828067 * Sin(4 * LT) - 0.021975 * Sin(6 * LT)

A2 = N * SINLT * COSLT / 2: A4 = N * SINLT * COS3 * (5 - T2) / 24

B1 = N * COSLT: B3 = N * COS3 * (1 - T2 + EE2 * COS2) / 6

B5 = N * COS5 * (5 - 18 * T2 + T4) / 120

X = ((A2 + A4 * DL2) * DL2 + S) * 0.001

Y = ((B3 + B5 * DL2) * DL2 + B1) * DL * 0.001 + 500

End Sub

Для построения приложения (назовем его Координаты.xls) на листе Excel в первой строке поместим названия столбцов таблицы: Номер точки, LT, LN, X, Y, Число итераций. Введем в ячейки второй и следующих строк номера нескольких точек и их географические координаты в градусах и поместим на лист Excel элементы управления, как показано ниже:

Поясним элементы управления, событийные и общие процедуры. Поле с именем TextBox1 (имена элементов управления на листе Excel не показаны) предназначается для ввода значения осевого меридиана – в нашем примере 105^о. Командные кнопки c именами BTN1 и BTN2 инициируют исполнение событийных процедур BTN1_Click и BTN2_Click. Первая из них из каждой непустой строки таблицы (начиная со строки 2) извлекает географические координаты LT, LN, переводит их из градусов в радианы и обращается к общей процедуре LTLNtoXY, текст которой приведен выше. Вычисленные координаты X,Y процедура BTN1_Click заносит в столбцы 4, 5 таблицы Excel.

Событийная процедура BTN2_Click похожа на процедуру BTN1_Click, только исходные данные она берет из столбцов 4, 5 таблицы, а результаты помещает в столбцы 2, 3. Для вычисления значений LT, LN по известным прямоугольным координатам X, Y она обращается к общей процедуре XYtoLTLN, которая вычисляет географические координаты в следующей последовательности:

1) устанавливается счетчик итераций K=0;

2) задается первое приближение, например, LT = 56 ∙ Pi180, LN = CM ∙ Pi180, где Pi180 – константа для перевода градусов в радианы, CM – осевой (центральный) меридиан;

3) путем обращения к процедуре LTLNtoXY находятся координаты XX, YY, соответствующие LT, LN;

4) наращивается на 1 счетчик итераций и вычисляются невязки DX = X - XX, DY = Y – YY, DXY = DX² + DY², а также новые значения широты и долготы LT = LT + DX ∙ 1000 / a, LN = LN + DY ∙ 1000 /a / cos(LN);

5) если DXY < 0.0000001, то задача считается решенной; в противном случае проверяется значение счетчика итераций;

6) если K< 20, то управление передается блоку 3; иначе считается, что процесс не сходится (это может быть, если заданы нереальные для Земли координаты X,Y), и в качестве результатов условно присваиваются значения LT = 0, LN = 0.

Обе событийные и общую процедуру XYtoLTLN надо разработать, а в событийной процедуре BTN2_Click также предусмотреть занесение в столбец 6 таблицы числа итераций, за которое сошелся процесс. Для проверки этой процедуры, можно очистить столбцы 2, 3 таблицы и нажать вторую командную кнопку – мы вновь должны получить исходные географические координаты.

Литература к курсовой работе 4.

Ломтадзе В.В. Программное обеспечение обработки геофизических данных. Л.: Недра, 1982. –280 с.

Ломтадзе В.В. Программное и информационное обеспечение геофизических исследований. М.: Недра, 1993. –268 с.

Справочник геодезиста (в двух книгах). Книга 1. М.: Недра, 1975. –544 с.

Поиск по сайту: