Мой Kbyte.Ru
Рассылка Kbyte.Ru
Группы на Kbyte.Ru
Партнеры Kbyte.Ru
Сделано руками
Сделано руками
> Статьи - Анатолий Присяжнюк -

Visual Basic for Applications (VBA) - Microsoft Excel

Все статьи / Microsoft Office / Microsoft Excel

Visual Basic в офисных приложениях

Автор: Анатолий Присяжнюк | добавлено: 03.03.2010, 16:05 | просмотров: 5053 (0+) | комментариев: 0 | рейтинг: *x5

Среду программирования Visual Basic, а особенно её разновидность VBA (Visual Basic for Aplication), можно успешно использовать при проведении всевозможных олимпиад и турниров по программированию. Именно под этим углом зрения мы рассмотрим два следующих вопроса: файлы и массивы, так как без первоначальных умений работать с массивами и файлами мы не можем приступать к разработке на VB.Net обещанной игрушки. Сначала сформулируем условие задачи.

 

Paint3D (Житомирская ХІХ областная олимпиада по информатике, 28.01 2005)

Фигуры на поле редактора Paint можно выделить, скопировать (полностью), вставить и переместить (перетянуть). Какое наименьшее количество этих операций нужно выполнить, чтобы, имея на поле изображение единичного кубика, построить еще одно изображение - прямоугольного параллелепипеда AxBxC, составленного из этих кубиков? (А,B,C <=100 - натуральные числа).

 

Образцы текстовых файлов PAINTх.DAT и PAINTх.SOL (x-номер теста).

PAINT0.DAT

2 3 4

значения А, В і С

 

PAINT0.SOL

20

минимум операций

Рис. 1

Рис. 1

 

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

 

Робота с файлами

 

Давайте создадим для начала файл Paint0.dat, который будет содержать данные с примера, приведенного в условии задачи. Сделать это можно разными способами, например, в любом файловом менеджере в командной строке набрать команду copy con paint0.dat, нажать Enter, в окне сеанса MS DOS, который откроется, набрать через пробел 2 3 4, нажать Ctrl + Z и Enter. В результате в текущей папке будет создан текстовый файл paint0.dat, который и будет содержать входные данные. Наша задача - прочитать эти входные данные с файла средствами языка программирования VBA. Договоримся, что все файлы будут расположены на диске С в папке VBА (если в другом - внесите самостоятельно необходимые изменения в программах, рассмотренных ниже).

Для решения задачи сначала загрузим Microsoft Excel, и сразу же сохраним документ в папку, где находиться созданный файл paint0.dat, под любым именем, например, Файлы и массивы.xls. После этого приступим к программированию на Visul Basic в Excel. Для этого сделаем следующее: в главном меню выберем последовательно “Сервис” => “Макрос” => “Макросы” (Рис. 2).

Рис. 2

Рис. 2

 

В открывшемся диалоговом окне напишите имя макроса, например Paint3D, укажите, что он будет расположен в вашем документе - “Эта книга” (Рис. 3) и нажмите кнопку Создать.

 

Рис. 3

Рис. 3

 

После этого автоматически загрузится редактор VBA (Рис. 4), если, конечно, он установлен вами при инсталляции офисного пакета.

 

Рис. 4

Рис. 4

 

Внешний вид этой среды не очень отличается от среды VB 6.0. Нам не будут нужны окна “Project (Проект)”, “UserForm (Форма)” и “Properties (Свойства)”, поэтому их можно закрыть, чтобы они нам не мешали.

Переместим курсор в положение над названием процедуры (выше слова Sub) и на всякий случай внесем строку Option Explicit, (данная строка будет требовать от пользователя явного объявления всех переменных), а после этого объявим нужные нам переменные (их назначение объяснено в комментариях). Перед дальнейшим написанием программы закроем макрос и в Excel внесем в следующие ячейки соответствующие заголовки (Рис. 5). В процесс работы над программой мы будем иметь возможность периодически проверять работоспособность нашего макроса по значениям, которые он будет заносить в соответствующие ячейки.

 

Рис. 5

Рис. 5

 

Нам нужно считать входные данные с файла paint0.dat и записать результат работы в файл paint0.sol. Для этого внесите следующий код в тело макроса:

 

Option Explicit         'Будем требовать явного объявления  всех переменных
Const maxN = 100     ' Согласно  условия  задачи
Dim a As Integer  ' Для значений исходных данных
Dim b As Integer  ' Для значений исходных данных
Dim  c As Integer  ' Для значений исходных данных
Dim fin As String       ' Для имени исходного файла
Dim fout As String      ' Для имени выходного файла
Dim st As String    ' Для работы со строчными переменными
Dim ss As String    ' Для работы со строчными переменными
Dim i, j As Integer     ' Переменные  для организации циклов
Dim Sol As Integer      ' Для подсчета результата
Dim num As Integer ' Для возможности работы со всеми тестами
Sub Paint3D()
    num = 1   ' Подготовка к творческой работе - номер теста
    fin = "c:\vba\paint0.dat"
' Поставили в соответствие 1-й файловой переменной имя файла
    fout = "c:\vba\paint0.sol"  
' Поставили в соответствие 2-й файловой переменной имя файла
    Open fin For Input As #1 ' Открыли  файл для чтения
    Open fout For Output As #2  
'Открыли файл для записи результатов работы
    Line Input #1, st   'Считали из фала строку
    st=st + " ": j=1 ' Пример  записи двух команд в одной строке
    For i = 1 To 3 ' Алгоритм преобразования текстовых переменных в числовые
        ss = ""
        Do While Mid(st, j, 1) <> " "
            ss = ss + Mid(st, j, 1): j = j + 1
        Loop
                    If i = 1 Then a = Val(ss)
        If i = 2 Then b = Val(ss)
        If i = 3 Then c = Val(ss)
        Do While Mid(st, j, 1) = " "
            j = j + 1
        Loop
    Next i ' Конец алгоритма -  разберитесь  самостоятельно по материалам сайта
    Close #1    ' Закрыли файл с исходными  данными
    Range("B" & (num + 1)).Select ' Стали в ячейку  ВNum
    ActiveCell.FormulaR1C1 = num  ' Записали в неё номер теста
    Range("C" & (num + 1)).Select ' Стали в ячейку  CNum+1
    ActiveCell.FormulaR1C1 = a ' Записали в неё  значение a
    Range("D" & (num + 1)).Select ' Стали в ячейку у DNum+1
    ActiveCell.FormulaR1C1 = b ' Записали в неё значение b
    Range("E" & (num + 1)).Select ' Стали в ячейку ENum+1
    ActiveCell.FormulaR1C1 = c ' Записали в неё значение с
    Range("F" & (num + 1)).Select ' Стали в ячейку FNum+1
    ActiveCell.FormulaR1C1 = Sol ' Записали в неё значение Sol
' … Далее будем писать код программы, начиная отсюда
    Print #2, Sol   ' Записали результаты вычислений в файл
    Close #2        ' Закрыли выходной файл
End Sub

Закройте редактор VBA и, находясь в Microsoft Excel, нажмите Alt + F8, выберите из списка макросов, если их несколько, Paint3D и нажмите кнопку „Выполнить”, в результате чего будут прочитаны данные с исходного файла, записано результаты вычислений в выходной файл и значения всех переменных отобразятся в Microsoft Excel (Рис. 6).

 

Рис. 6

Рис. 6

 

Как и следовало ожидать, в результате мы получили пока что 0, так как никаких вычислений не производили, но мы уже можем увидеть, что данные с файла считаны верно и результат записано в выходной файл также верно.

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

 

КРАН (Житомирская ХІХ областная олимпиада с информатики, 28.01.2005)

На складе, имеющем один кран, сохраняется N грузов. Время, необходимое для вывоза со склада k-го груза, становит Tk часов, а стоимость его хранения - Bk рублей в час, k=1..N. Оплата за хранение груза прекращается после завершения его вывоза. В каком порядке должен работать кран, чтобы итоговая стоимость хранения грузов на складе была минимальной?

Значения N,Tk, Bk - натуральные, меньше 50

 

Образцы текстовых файлов CRANEx.DAT и CRANEx.

CRANE0.SOL

67

Найменьшая стоимость

 

CRANE0.DAT

3
2 1 4
5 3 7

значение N
время погрузки T[1..N]
стоимость хранения B[1..N]

Как видно из условия задачи, нам нужно будет работать с большим количеством однотипных данных, то есть с массивами.

 

Массивы

Как видно из приведенного примера, в исходном файле содержится 3 строки. В первой находится значение N - количество грузов на складе. Далее в двух строках через пробел размещено по N чисел: значения времени погрузки і-го груза и стоимость его хранения за 1 час. Мы уже умеем считывать входные данные с файла, но в данном случае нам необходимо считывать не отдельные переменные, а массив переменных, причем их количество наперед не известно.

Объявление массивов в VB почти не отличается от объявления переменных. Если обычная переменная I объявляется как Dim I As Integer, то массив однотипных переменных t объявляется так Dim t(50) As Integer. Это объявление создаст в памяти компьютера массив из 51 элемента, поскольку нумерация элементов массива в языке Бейсик начинается с нуля.

Перейдем на новый лист Microsoft Excel (желательно переименовать как предыдущий, так и этот лист в соответствии с названием задачи, например, Paint3D и Crane) и считаем исходные данные следующим образом (не забудьте создать новый макрос!):

 

Option Explicit
Const maxN = 50
Dim i As Integer
Dim  n As Integer
Dim  j As Integer
Dim  num As Integer
Dim ss As String
Dim st As String
Dim fin As String
Dim  fout As String
Dim t(50) As Integer ' Объявили  массив для времени погрузки  і-го груза
Dim b(50) As Integer ' Объявили массив стоимостей хранения
Dim op(50) As Double ' Вспомогательный массив (действительные числа)
Sub Crane()
     num = 1  
     fin = "c:\vba\crane0.dat"
    Open fin For Input As #1
    Line Input #1, st
        n = Str(st)     ' Считываем N
    Line Input #1, st  ' Считываем строку для определения  t[i]
        st = st + " ": j = 1
        For i = 1 To n  
            ss = ""
            Do While Mid(st, j, 1) <> " "
                ss = ss + Mid(st, j, 1): j = j + 1
            Loop
            t(i) = Val(ss)
            Do While Mid(st, j, 1) = " "
                j = j + 1
            Loop
        Next i
    Line Input #1, st 'Считываем строку для определения b[i]
        st = st + " "
        j = 1
        For i = 1 To n
            ss = ""
            Do While Mid(st, j, 1) <> " "
                ss = ss + Mid(st, j, 1)
                j = j + 1
            Loop
            b(i) = Val(ss)
            Do While Mid(st, j, 1) = " "
                j = j + 1
            Loop
        Next i
    Close #1
    ' … Дальше  будем писать код программы, начиная отсюда
End Sub

Как видно из приведенного кода, объявление массивов почти ничем не отличается от объявления обычных переменных. Приступим к решению этой задачи. На первый взгляд нужно применить жадный алгоритм: сначала грузим наиболее дорогие грузы, а затем те, которые дешевле. Но полный перебор, который для приведенного примера можно осуществить вручную (или программно!) в том же таки Microsoft Excel, показывает, что это далеко не так (Рис. 7).

 

Рис. 7

Рис. 7

 

Как было сказано выше, для решения этой задачи применим метод динамического программирования. Динамическое программирование - раздел математического программирования, в котором исследуются многошаговые задачи принятия оптимальных решений. При этом многошаговость задачи отражает или реальное прохождение процесса принятия решения во времени, или вводится в задачу искусственно за счет разбиения процесса принятия однократного решения на отдельные этапы, шаги. Цель такого представления состоит в приведении исходной задачи, которая заключается в нахождении решения как точки в пространстве высокой размерности, к решению на каждом шаге задачи небольшой размерности (даже одномерной задачи).

В основу принципа динамического программирования полажено следующую простую стратегию оптимальной политики, указанную Р.Беллманом и названную им принципом оптимальности: „Именное оптимальной политике присуще то свойство, что какими бы не были начальное состояние и первое решение, последующие решения составляют оптимальную политику по относительно начального состояния, полученного в результате первого решения.”

В применении к нашей задаче критерием оптимальности Беллмана есть отношение стоимости хранения груза за единицу времени к времени погрузки: op(i) = b(i) / t(i). Если мы будем грузить грузы в порядке убывания (не возрастания) этого критерия, мы и получим указанную политику. Доказательство данного утверждения приведено в соответствующей литературе.

Для решения нашей задачи нам необходимо создать новый массив op(i), отсортировать его за не возрастанием, одновременно сортируя и массивы b(i) и t(i). У нас не стоит за цель в данной статье использование эффективных алгоритмов сортировки, поэтому используем один из наиболее простых.

Для этого добавим в макрос следующий код, нажав последовательно Alt + F8, выберем со списка макросов, если их несколько, Crane и нажмем кнопку „Изменить”:

 


' Формирование массива op
    For i = 1 To n
            op(i) = b(i) / t(i)
    Next i
   ' Одновременная сортировка  трех массивов
   For i = 1 To n - 1
    For j = i + 1 To n
        If op(i) < op(j) Then
          op(0) = op(i)
          op(i) = op(j)
          op(j) = op(0)
          t(0) = t(i)
          t(i) = t(j)
          t(j) = t(0)
          b(0) = b(i)
          b(i) = b(j)
          b(j) = b(0)
       End If
     Next j
    Next i
    ' Подсчет оптимального результата
    sol = 0
    For i = 1 To n - 1
        sol = sol + b(i) * t(i)
        For j = i + 1 To n
            sol = sol + b(j) * t(i)
        Next j
    Next i
    sol = sol + b(n) * t(n)
' Запись результата в файл
    fout = "c:\vba\crane0.sol"
    Open fout For Output As #2
    Print #2, sol
    Close #2
' Запись результатов в ячейки Microsoft Excel
    Range("B" & (num + 1)).Select
    ActiveCell.FormulaR1C1 = num
    Range("C1" & (num + 1)).Select
    ActiveCell.FormulaR1C1 = sol

 

Именно от искусства нахождения критерия оптимальности Беллмана и зависит использование метода динамического программирования. Найдем принцип оптимальности для задачи Paint3D, сформулированной выше. Научимся строить сначала произвольную цепочку Ах1х1 за минимальное количество шагов. Для этого снова используем лист таблицы Microsoft Excel Paint3D.

 

Станем в ячейку А13 и внесем для удобства соответствующие заголовки (Рис. 8). Пусть мы построили 1 новый кубик - для этого нам нужно осуществить 4 операции. Этот кубик уже размещен у нас в памяти, поэтому, строя любую цепочку и имея в памяти кубик 1х1х1, мы используем столько операций, как это указано в столбце С. Но, построив цепочку 2х1х1, мы можем все дальнейшие построения выполнять, исходя из неё, поэтому: для построения цепочки 2х1х1 нам нужно 6 операций. Для цепочек 3х1х1 и 4х1х1 мы используем на 4 операции больше, поскольку нужно выполнить на первом этапе две дополнительные операции - выделить и скопировать, а все дальнейшие построения будут опять увеличивать количество шагов на 2 - столбик D. Будем строить все возможные цепочки по 3, 4, …, 50 (N div 2), а в столбике А будем выбирать минимальное количество шагов, нужное для построения. Собственно говоря, мы уже словесно-формально описали алгоритм решения задачи для построения цепочки произвольной длины Ах1х1.

 

 

A

B

C

D

E

F

G

H

I

AY

AZ

13

Min

N

1

2

3

4

5

6

7

49

50

14

4

1

4

 

 

 

 

 

 

 

 

 

15

6

2

6

 

 

 

 

 

 

 

 

 

16

8

3

8

10

 

 

 

 

 

 

 

 

17

10

4

10

10

12

 

 

 

 

 

 

 

18

12

5

12

12

12

14

 

 

 

 

 

 

19

12

6

14

12

12

14

16

 

 

 

 

 

20

14

7

16

14

14

14

16

16

 

 

 

 

21

14

8

18

14

14

14

16

16

18

 

 

 

22

14

9

20

16

14

16

16

16

18

 

 

23

16

10

22

16

16

16

16

16

18

 

 

111

28

98

198

104

74

60

52

46

42

28

28

112

28

99

200

106

74

60

52

46

44

30

28

113

28

100

202

106

76

60

52

46

44

30

28

 

Рис. 8

 

Можно доказать строго математически (попробуйте сделать это самостоятельно, если вас это заинтересовало), что оптимальное количество для параллелепипеда АхВхС становит k = fmin(a) + fmin(b) - 2 + fmin(c) - 2, при условии, что переменные А, В и С упорядочены за не возрастанием.

Дополним наш макрос следующими строками, добавив при необходимости необъявленные переменные в соответствующем месте.

 


    t(1) = a: t(2) = b: t(3) = c ' Для удобства  сортировки
    '  Сортировка
    For i = 1 To 2
        For j = i To 3
            If t(j) > t(i) Then
                d = t(i)
                t(i) = t(j)
                t(j) = d
            End If
        Next j
    Next i
    a = t(1): b = t(2): c = t(3)
    ' Начальная инициализация
    mn(0) = 2: fl(0) = 2
    For i = 1 To maxN
       fl(i) = fl(i - 1) + 2
       mn(i) = fl(i)
    Next i
    mn(0) = 0 ' Если кто-то  захочет 0х0х0 :)
    m = 2
    ' Главный цикл
    For j = m To maxN / 2
        k = 2 * j
        d = mn(j)
        For i = j + 1 To k
            fl(i) = d + 4
        Next i
        For i = k + 1 To maxN
            fl(i) = fl(i - j) + 2
        Next i
        For i = 0 To maxN
            If mn(i) > fl(i) Then
              mn(i) = fl(i)
            Else
              mn(i) = mn(i)
            End If
        Next i
    Next j
   ' Подсчет по формуле
    If c > 1 Then
     sol = mn(a) + mn(b) - 2 + mn(c) - 2
    Else
      If b > 1 Then
        sol = mn(a) + mn(b) - 2
      Else
        sol = mn(a)
      End If
    End If
    ' Вывод  результатов
    …

 

Если организовать еще один, внешний цикл, то можно сразу обработать все тесты, которые можете придумать самостоятельно. Попробуйте дополнить написанный макрос соответствующим образом, поскольку подготовительные шаги для этого мы уже сделали в процессе написания макросов.

Теперь мы готовы вернуться в VB.Net, где, уже имея первоначальные навыки работы с массивами, и попробуем написать игрушку. Но это уже в следующей статье…

+ Добавить в избранное
    ? Помощь
Об авторе

Анатолий Присяжнюк

Присяжнюк Анатолий Васильевич - учитель информатики специализированной, с углубленным изучением информатики школы 17 г. Бердичев (Украина). Автор книг: "Вступление в программирование. Язык Паскаль", "Олимпиады Житомирской области до 2000 г.", "Уроки по Visual Basic". Специализируется на вопросах, связанных с преподаванием информатики, изучением программирования, организацией и проведением олимпиад по программированию.

См. также:
Профиль автора
Анатолий Присяжнюк
Последние комментарии (всего: 0)

Добавлять комментарии могут только зарегистрированные пользователи сайта.
Если у Вас уже есть учетная запись на Kbyte.Ru, пройдите процедуру авторизации OpenID.
Если Вы еще не зарегистрированы на Kbyte.Ru - зарегистрируйтесь.


Нет комментариев...

Авторизация
 
OpenID
Зарегистрируйся и получи 10% скидку на добавление своего сайта в каталоги! Подробнее »
Поиск по сайту
Реклама
Счетчики