If <Выражение1> Then

<Блок 1 – исполняется, если выражение 1 истинно>

ElseIf <Выражение2> Then

<Блок 2 – исполняется, если выражение 2 истинно >

ElseIf <Выражение3> Then

<Блок 3 – исполняется, если выражение 3 истинно >

…………………..

Else

<Блок инструкций, исполняемых, если ложны выражения в инструкциях If и ElseIf>

End If

 

Приведем пример простой инструкции If:

If R Then MsgBox “Точка попала в прямоугольник”

Теперь пример блочной структуры:

R = False

If X < X1 Then

GoTo 10 ’уже ясно, что точка не попала в прямоугольник

ElseIf X > X2 Then

GoTo 10 ‘управление передается на метку 10

ElseIf Y < Y1 Then

GoTo 10

ElseIf Y > Y2 Then

GoTo 10

Else

R = True

End If

10: MsgBox R ’после метки ставится двоеточие

 

Блоки ElseIf и (или) Else могут отсутствовать. В любом случае блочная конструкция оканчивается инструкцией End If.

Приведенный пример, кроме блочной конструкции IF, поясняет возможность использования в программе меток и передач управления с помощью инструкции GoTo. Метка не обязательно обозначается числом – можно было написать и N10 или MsgR.

В ситуациях, когда требуется запрограммировать три и более разветвлений в зависимости от значения одной переменной, удобно использовать структуру Select Case:

Select Case <Переменная>

Case <Значение 1> либо Case is <Условие 1>

<Блок 1 – выполняется, если значение переменной равно значению 1

или соответствует условию 1>

Case <Значение 2> либо Case is <Условие 2>

<Блок 2 – выполняется, если значение переменной равно значению 2

или соответствует условию 2>

………………….

End Select

Приведем пример:

Возраст = TextBox1.Value

Select Case Возраст

Case Is < 13

Label1.Caption = "После 22-00 спать!"

Case 16

Label1.Caption = "Пора получать паспорт"

Case Is < 18

Label1.Caption = "Только чай!"

Case Is >= 18

Label1.Caption = "Можно почти все!"

End Select

 

Теперь рассмотрим организацию циклов. Вообще говоря, цикл нетрудно организовать и без специальных инструкций по схеме:

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

2) инструкции, исполняемые при очередном значении переменной цикла;

3) наращивание (изменение) переменной цикла и, возможно, других переменных, изменяемых одновременно с переменной цикла;

4) проверка переменной цикла на соответствие условию, при котором цикл должен исполняться повторно, и передача управления блоку 2 при выполнении этого условия (IF <условие> GoTo <метка в начале блока 2>).

Именно такой, классический цикл изображен на рис. 4.1 в виде блок-схемы. Но в языках высокого уровня, и в том числе в VBA, предусмотрены инструкции, позволяющие записывать циклы еще компактнее. Проще всего цикл записывается, если известны начальное, конечное значения переменной цикла и шаг ее изменения. Пусть в J-ом столбце на листе Excel надо суммировать четные ячейки до строки 20, но только пока не встретится число 7777:

J = 1: S = 0

For I = 2 To 20 Step 2

F = Cells(I, J).Value

If F = 7777 Then Exit For 'Выход из цикла - на инструкцию после Next

S = S + F

Next I

MsgBox "i= " & Str(I-2) & " S=" & Str(S)

После выхода из цикла сообщается номер последней ячейки, значение которой добавлено к S и сама накопленная сумма. Цикл этого примера называют циклом типа For-Next.

В большинстве случаев циклы этого типа выглядят проще, т.к. обычно не требуется задавать шаг изменения переменной цикла (Step) и не нужно выходить из цикла по дополнительному условию (Exit For). В модуле, приведенном после рис. 10.5 (см. подраздел 10.4), такие циклы используются в процедурах TabCXCY и TabA. В последней процедуре – вложенный (двойной) цикл.

В тех случаях, когда нет возможности указать начальное и конечное значения переменной цикла, используют циклы типа Do-Loop. Для таких циклов возможны 4 варианта записи:

 

условие True в начале цикла Do While <условие> <инструкции, исполняемые в цикле> Loop	условие False в начале цикла Do Until <условие> <инструкции, исполняемые в цикле> Loop
условие True в конце цикла Do <инструкции, исполняемые в цикле> Loop While <условие>	условие False в конце цикла Do <инструкции, исполняемые в цикле> Loop Until <условие>

 

Циклы такого типа уже неоднократно применялись в примерах процедур в подразделах 10.3, 10.4. В число инструкций, исполняемых в цикле, может быть включена инструкция Exit Do, если из цикла надо выйти “досрочно”, например, при выполнении какого-нибудь дополнительного условия. Заметим, что Do While означает Выполнять пока … Do Until означает Выполнять пока не … Другими словами, вместо Do Until можно написать Do While Not. В некоторых случаях эти проверки удобнее ставить в конец цикла (Loop – цикл, дословно – петля).

В заключительной части этого раздела приведем рекомендации по отладке приложений.

Во-первых, для отладки надо подбирать разнообразные примеры, не ограничиваясь самыми простыми – ведь многие ошибки проявляются только при определенных значениях исходных данных, а иногда даже только при определенных сочетаниях этих значений.

Во-вторых, для просмотра промежуточных и окончательных значений переменных можно на время отладки включать в тексты процедур обращения к процедуре MsgBox, а также выводить значения переменных в виде свойства Caption элемента управления Label (метка – статический текст) или в виде свойства Text элемента управления TextBox (поле с редактируемым текстом). Важную роль играет выбор точек процедуры для вывода значений переменных при отладке. Для выбора таких точек можно рекомендовать рассматривать процедуру состоящей из логически завершенных фрагментов программного кода, формирующих определенные «информационные сцены». Эти-то сцены, характеризующиеся содержимым переменных и массивов, и надо контролировать.

В-третьих, VBA предоставляет специальные средства для отладки процедур. Если в окне редактора слева от текста инструкции (на полях) щелкнуть мышью, то создастся точка останова. При этом инструкция выделится красным цветом. Теперь переключаемся через панель задач на окно Excel и запускаем процедуру (обычно – командной кнопкой). В точке останова выполнение программы приостановится, причем произойдет переключение на окно редактора Visual Basic, а очередная инструкция будет выделена желтым цветом. Теперь можно навести мышь на любую переменную (на ее имя в любой инструкции), и появится окошечко со значением этой переменной в момент останова. Далее можно установить курсор в следующую представляющую интерес позицию модуля и нажать <Ctrl>+<F8> - программа выполнится до этой позиции и снова приостановится. Можно снова “посмотреть” значения переменных, наводя на них мышь. Есть также возможность перейти к пошаговому исполнению инструкций, пользуясь клавишей <F8> или комбинациями клавиш <Shift>+<F8> (шаг с обходом вызываемой процедуры) и <Shift>+<Ctrl>+<F8> (шаг с выходом из процедуры – см. также пункт меню Отладка).

Таким образом, современные среды визуального программирования предельно облегчают отладку программ.

 

Контрольные вопросы к разделу 10: что такое программирование; особенности и достоинства VBA; создание интерфейса пользователя, как разместить элементы управления на листе Excel, как установить их свойства, как перейти в окно редактора VBA, как создать диалоговое окно; что такое модуль, процедура, функция; как оформляются и вызываются процедуры; как оформляются и вызываются функции; категории встроенных функций; встроенные типы данных VBA; назначение переменных, их описание, локальные и глобальные переменные; назначение инструкции Option Explicit; массивы, их описание, доступ к элементам массива, многомерные массивы, динамическое переопределение размерности массивов; пояснить связь математических понятий вектора и матрицы с понятиями одномерного и двумерного массивов в программировании; конструирование собственных типов данных; константы, их определение и использование; доступ к данным, хранимым в виде свойств объектов-элементов управления; основные группы инструкций языка Visual Basic; инструкция присваивания; арифметические операции; конкатанация; операции сравнения; логические операции; инструкции проверки условий и организации циклов; методы отладки приложений.

11. Самостоятельные работы

Самостоятельные работы ориентированы на создание базы данных Access и приложений VBA. Эти работы должны восполнить недостаток часов, отводимых на лабораторные работы при изучении баз данных и основ программирования.

 

 

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

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

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

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

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

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

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

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

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

Подобно базе данных «Архив» можно создавать разнообразные базы данных: книги в домашней библиотеке, музыкальные записи, семейные расходы и т.п.

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

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

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 с.

 

 

11.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 велико, то коэффициент корреляции случайно может получиться большим или малым. Чтобы оценить значимость коэффициентов парной корреляции, вычисляют их стандарты:

, 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 с.

 

11.4. Разработка приложения “Преобразование географических координат в прямоугольные и прямоугольных в географические”

 

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

Y-500

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

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

X

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 элементы управления, как показано ниже:

 

Номер т.	LT	LN	X	Y	Число итераций
		105.75	6654.426	541.8501		(LT,LN) à (X,Y)
	55.12		6112.524	627.5992
					Осевой меридиан
(X,Y) à (LT,LN)

 

Поясним элементы управления, событийные и общие процедуры. Поле с именем 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

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

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

 

 

11.5. Разработка приложения “Графические построения в плоскости XOY”

Различные графические построения часто необходимы в любой практической деятельности. В литературе по VBA этот вопрос практически не освещен, но в Excel и в Word всегда можно вызвать на экран панель инструментов Рисование и конструировать рисунок, включая в него линии, надписи и другие фигуры, образцы которых (Shapes) представлены на панели инструментов Рисование. Если перед созданием рисунка включить запись макроса (кнопка Записать макрос на панели инструментов Visual Basic), а затем в окне Visual Basic посмотреть его текст и, кроме того, обратиться к встроенной справке, то становится ясно, как программировать векторную графику в VBA. Именно так мы и поступили. Далее приводится начало модуля, содержащего базовые процедуры, с помощью которых можно программировать графические построения на плоскости XOY.

 

' Глобальные переменные и константы

Dim cx As Single, cy As Single

' cx,cy - масштабные коэффициенты: пиксели/сантиметры

Dim nx1 As Integer, ny1 As Integer, nx2 As Integer, ny2 As Integer

Dim RedClr As Integer, GreenClr As Integer, BlueClr As Integer

Dim FntName As String, FntSize As Integer, FntColor As Integer

Const nx0 As Integer = 25.5: Const ny0 As Integer = 25.5:

' Инициализация графического окна размером Lx ∙ Ly (в сантиметрах)

' Начало координат в левом верхнем углу окна, ось X направлена вправо, Y- вниз

Sub GrIni()

SP 0, 0, 0

cx = TextBoxCX.Value: cy = TextBoxCY.Value

Lx = TextBoxLX.Value: Ly = TextBoxLY.Value

Лист2.Activate: Лист2.Shapes.SelectAll

Selection.Delete ' Удаление графических элементов с листа 2

Лист2.Shapes.AddShape(msoShapeRectangle, nx0, ny0, Lx * cx, Ly * cy).Select

End Sub

 

' Пересчет координат из сантиметров чертежа в пиксели (точки)

Function GETnx(xcm As Single) As Integer

GETnx = nx0 + xcm * cx

End Function

Function GETny(ycm As Single) As Integer

GETny = ny0 + ycm * cy

End Function

' Перемещение в точку xcm, ycm (Move Absolute, координаты точки - в см)

Sub MA(xcm As Single, ycm As Single)

nx1 = GETnx(xcm): ny1 = GETny(ycm)

End Sub

' Вычерчивание линии из текущей точки в точку xcm, ycm (Plot Absolute)

Sub PA(xcm As Single, ycm As Single)

nx2 = GETnx(xcm): ny2 = GETny(ycm)

With Лист2.Shapes.AddLine(nx1, ny1, nx2, ny2).Line

.ForeColor.RGB = RGB(RedClr, GreenClr, BlueClr)

End With

nx1 = nx2: ny1 = ny2

End Sub

' Установка цвета линий (Set Pen)

Sub SP(Red As Integer, Green As Integer, Blue As Integer)

RedClr = Red: GreenClr = Green: BlueClr = Blue

End Sub

' Установка шрифта, его размера и цвета (Set Font)

' Цвет: 1-черный, 2-пусто, 3-красный, 4- ярко-зеленый, 5-синий,

' 6-желтый, 7-светло-фиол., 8-голубой, 9-коричн., 10-зеленый, 11-темно-синий

Sub SF(Name As String, Size As Integer, Color As Integer)

FntName = Name: FntSize = Size: FntColor = Color

End Sub

' Надпись (Write String) – указываются размеры текстового окна и сам текст

Sub WS(xcm As Single, ycm As Single, Lxcm As Single, Lycm As Single, _

Txt As String)

Лист2.Shapes.AddTextbox(msoTextOrientationHorizontal, _

GETnx(xcm), GETny(ycm), Lxcm * cx, Lycm * cy).Select

Selection.ShapeRange.Line.Visible = msoFalse

Selection.Characters.Text = Txt

With Selection.Characters(Start:=1, Length:=Len(Txt)).Font

.Name = FntName

.Size = FntSize

.ColorIndex = FntColor

End With

End Sub

Private Sub Btn1_Click() ' Событийная процедура

GrIni

MA 5, 5

PA 10, 5

SP 200, 0, 0

PA 10, 10

SF "Times New Roman Cyr", 9, 11

WS -0.8, -0.2, 0.8, 0.4, "0.0"

End Sub

 

Построить график

cy

cx

Lx

Ly

Предлагаемые процедуры разработаны нами, чтобы избежать непосредственного использования конструкций VBA, позволяющих размещать на листе книги Excel линии и надписи, поскольку синтаксис этих конструкций довольно сложен. Приведенный в конце модуля пример простейшей событийной процедуры показывает, как используя процедуры GrIni, MA, PA, SP, SF, WS, программировать построение графиков, схем, чертежей и т.п. Набор перечисленных процедур расчитан на то, что на листе 1 книги Excel размещены поля TextBoxLX, TextBoxLY, TextBoxCX, TextBoxCY и командная кнопка Btn1.

В полях редактирования (TextBox) задаются размеры чертежа в см и масштабные коэффициенты для перехода от см к точкам (пикселям). Коэффициенты cx, cy можно скорректировать после вывода чертежа на принтер. Процедура GrIni должна использоваться первой – она вычерчивает прямоугольное окно размером Lx∙Ly на листе 2 книги Excel. В этом окне затем проводятся дальнейшие построения. Остальные процедуры легко понять по распечатке. Теперь сформулируем варианты самостоятельной работы.

Вариант 1. На листе 1 разместить элементы управления и в двух столбцах - значения X, Y. На листе 2 построить график Y(X). При построении координатных осей найти диапазоны изменения X, Y; оси оцифровать «круглыми» значениями.

Вариант 2. Аналогичен варианту 1, но надо строить график X(Y).

Вариант 3. Построить разными цветами графики F1(X), F2(X), разместив на листе 1 три ряда (столбца) чисел: X, F1, F2. Возможно построение обоих графиков в разных масштабах или в одном и том же масштабе.

 

 

 

ЛИТЕРАТУРА

1. Бояринцева Т.П., Воропаева Е.Ф., Дмитриенко Т.А., Шишкина Л.П.. Лабораторный практикум по информатике. Расширенные возможности Excel.-Иркутск: Изд-во ИрГТУ.-2003.-71с.

2. Гончаров А. Excel 97 в примерах. –СПб.: Питер, 1997. –336 с.

3. Громов Г.Г. Национальные информационные ресурсы: проблемы промышленной эксплуатации. –М.: Наука, 1984.

4. Информатика: Учебник /Под ред. проф. Н.В.Макаровой. -М.: Финансы и статистика, 1997. -768 с.

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

6. Ломтадзе В.В., Агафонов Ю.А., Бояринцева Т.П. и др. Лабораторный практикум по информатике. Часть I. Windows, Word, Excel, Access. – Иркутск: Изд-во Иркутского госуд. техн. ун-та, 2003. - 69 с.

7. Мэнсфилд Р. Windows 95 для занятых /Перев. с англ. - СПб: Питер, 1996. -304 с. Также другие книги по Windows.

8. Нельсон С. Office 97 для занятых/Перев. с англ. - СПб: Питер, 1997. -288 с. Также другие книги по Microsoft Office, Word, Excel, Access, PowerPoint, OutLook.

9. Очков В.Ф. Mathcad 7 Pro для студентов и инженеров. -М.: КомпьютерПресс, 1998. -384 с.

10. Петров А.В. Windows-информатика. Учебное пособие. -Иркутск: Изд-во Иркутского госуд. техн. ун-та, 1998. -135 с.

11. Санна П. и др. Visual Basic для приложений (версия 5) в подлиннике: пер. с англ. – СПб.: BHV – Санкт-Петербург, 1998. – 704 с.

12. Симонович С.В., Евсеев Г.А., Алексеев А.Г. Специальная информатика: Учебное пособие. – М.: АСТ-ПРЕСС: Инфорком-Пресс, 1999. – 480 с.

13. Microsoft Visual Basic 5. Шаг за шагом: Практ.пособ./Пер. с англ. кн. Микаэла Хальворсона. -М.: Изд-во ЭКОМ, 1998. -432 с.

14. Oracle 7 и вычисления клиент.сервер /Пер.с англ. кн. Стивена Бобровски. -М.: Изд-во ЛОРИ, 1996. -651 с.

15. Шишкина Л.П., Бояринцева Т.П., Сержант Т.Н.. Лабораторный практикум по информатике. Часть II. Visual Basic for Applications. – Иркутск: Изд-во Иркутского госуд. техн. ун-та, 2002. - 52 с.

16. Экслер А.Б. Microsoft Office 2003. Самый полный и понятный самоучитель/ Алекс Экслер. – М.: НТ Пресс. 2008. – 400 с.