Файл: Пустовалова_Информатика и компьютерная графика.pdf

2.Заполнить произвольно поля таблицы Фамилия и Должность. Стаж – в диапазоне 0–30 лет. Оклад – 200 000–800 000 руб.

3.Премия составляет 50% от оклада.

4.Надбавку за стаж вычислить на основании стажа. Если стаж больше 10 лет, тогда надбавка равна 20% от оклада, иначе – 0. Здесь используется встроенная функция ЕСЛИ.

5.Налоги составляют 20% от значения поля Итого.

6.Вычислить сумму и среднее значение для каждого столбца.

7.Значение поля Доля рассчитать как значение поля Получить для каждого работника, деленное на значение общей суммы всех значений поля Получить.

8.Построить столбчатую гистограмму для столбцов Фамилия и Получить. В легенде отобразить фамилии работников.

9.На 2-м и 3-м листах создать аналогичные таблицы за февраль и

март.

10.Используя объемные ссылки, увеличить оклад за февраль на 10%, а за март – на 5% по сравнению с февралем.

11.На 4-м листе создать таблицу с полями Месяц и Всего получить для директора (или другого специалиста).

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

35

5.ЯЗЫК VBA. ЛИНЕЙНЫЕ

ÈЦИКЛИЧЕСКИЕ ПРОГРАММЫ

5.1.Общие сведения

îпринципах программирования в Excel

Создание проекта программы на языке VBA (Visual Basic for Application) состоит из двух взаимосвязанных процессов: размещение элементов управления на рабочем листе Excel или на форме (формах) пользователя и написание текста программы в соответствии с алгоритмом решаемой задачи.

1. Для размещения элемента управления на рабочем листе необходимо:

−вызвать панель инструментов Элементы управления;

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

–переход в режим Конструктора (редактора VBA)

–активизация окна Свойства

–переход в окно Исходный текст (программы)

–флажок

–поле

–кнопка

–переключатель

–простой список

–комбинированный список

–выключатель

–счетчик

–полоса прокрутки

–надпись

–рисунок

–другие элементы

Рис. 5.1. Панель Элементы управления

36

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

Для изменения надписи на элементе управления используется свойство Caption, для изменения шрифта надписи – Font, для изменения цвета букв – ForeColor, для изменения цвета кнопки – BackColor и т. д. Значение свойства Name, определяющее имя элемента, задается только латинскими буквами.

При работе в Excel 2007 необходимо придерживаться следующего порядка действия. Вначале надо поместить на ленту вкладку Разработчик. Для этого следует нажать кнопку Оffice в правом верхнем углу приложения, в появившемся окне нажать кнопку Параметры Excel и в следующем раскрывшемся окне поставить галочку у надписи По-

казывать вкладку «Разработчик» на ленте. Затем следует активи-

зировать данную вкладку на ленте, щелкнуть по кнопке Вставить элементы управления и на панели Элементы ActiveX выбрать нуж-

ный элемент управления.

2. Для записи программы, которая связывается с некоторым элементом управления, необходимо:

–удостовериться, что установлен режим конструктора, т. е. нажата кнопка Режим конструктора на панели инструментов Элементы управления;

–активизировать элемент управления, для которого нужно написать процедуру обработки события, и перейти в окно программного кода, щелкнув по кнопке Исходный текст на панели инструментов Элементы управления (при этом откроется окно редактора VBA). Перейти в окно программ также можно, сделав двойной щелчок по элементу управления;

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

Рассмотрим некоторые понятия визуально-событийного, объектноориентированного программирования. Объектами являются элементы управления, формы, рабочие листы и т. п. Объекты можно создавать и программным путем. В приложении Excel объектами являются рабо-

чие книги (Workbooks), листы (Worksheets), ячейки (Cells), обла-

сти (Range), элементы управления на рабочих листах.

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

37

связанное с соответствующим событием. Когда происходит то или иное событие, то выполняется процедура обработки события (программа), которая заключена между ключевыми словами (процедурными скобками) Sub…End Sub.

Пример 5.1. Разработать процедуру, связанную с элементом управления Кнопка, которая будет выполняться при совершении события «Щелчок по командной кнопке». Программа выводит в окно сообщения слово «Привет».

На панели Элементы управления надо нажать элемент управления Кнопка и разместить его на рабочем листе (появится кнопка с надписью CommandButton1. Затем следует дважды щелкнуть по кнопке и откроется редактор VBA. Текст программы:

Sub CommandButton1_Click() MsgBox ("Привет")

End Sub

Здесь функция MsgBox () активизирует диалоговое окно с сообщением, записанным в кавычках.

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

Редактор VBA можно иначе открыть командой Сервис/Ма-

крос/Редактор Visual Basic.

5.2. Основные конструкции VBA

При вводе текста процедур могут применяться символы как строчные, так и прописные. В любом месте программы может стоять комментарий, который начинается с апострофа «’». Длинный оператор можно разбить на несколько строк, используя пробел и символ подчеркивания «_». В одной строке можно помещать несколько операторов, разделяя их двоеточием «:».

Имена переменных на языке VBA задаются латинскими/русскими буквами, для них нельзя использовать ключевые слова и имена стандартных объектов. Различают следующие типы переменных:

−Integer – для целых чисел (диапазон значений от –32 768 до 32 767), требует объем памяти в 2 байта;

38

−Single – для вещественных (дробных) чисел одинарной точности (диапазон значений от 3.402823Е + 38 до 1.401298Е – 45), требует объем памяти в 4 байта. Большие по модулю и близкие к нулю числа записываются с порядком при округлении их до 7-значащих цифр и определении их порядка в виде символа Е с числом, что эквивалентно умножению на 10 в указанной после символа Е степени (например, 4.2Е-3 – это 0.0042);

−String – для хранения символьных (строковых) значений, каждый символ требует объем памяти в 1 байт. Длина строки символов от

1до 64 килобайтов;

−Variant – для хранения любых данных, требует объем памяти в

8байтов. Этот тип присваивается переменной по умолчанию. Обычно перед использованием переменной производится ее объ-

явление в операторе Dim со следующим синтаксисом:

Dim <имя переменной> As <тип переменной>

Допускается в одном операторе Dim определять несколько переменных и различные типы переменных. Например:

Dim i As Integer, x As Single, y As Single

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

В арифметических выражениях на языке VBA записываются константы, переменные, встроенные функции, соединенные знаками арифметических операций. Арифметические операции задаются следующими символами: + (сложение), – (вычитание), * (умножение),

/(деление), ^ (возведение в степень), а сами выражения записываются

водну строку.

Втабл. 5.1 приведена запись некоторых встроенных математических функций (полный перечень функций языка VBA можно найти в справочной системе).

Таблица 5.1

Запись математических функций на языке VBA

Например, запись арифметического выражения:

39

на языке VBА будет выглядеть следующим образом:

Sin(2 * x) + (Log(x - 1) + Exp(x + 3)) / ((x ^ 4 + 5 * x ^ 2 - 3) ^ (1 / 3) + + (Tan(x ^ 2)) ^ 3

Оператор присваивания имеет следующий синтаксис: <имя переменной> = <арифметическое выражение> Пример записи оператора присваивания:

х = 2 * a + Log(a + 0.5)

Ввод значений переменных может производиться в диалоговом режиме или из ячеек таблицы.

1. Диалоговый ввод осуществляется с помощью встроенной функции InputBox, которая при работе программы выводит на экран свое окно. Например, при выполнении строки программного кода a = InputBox("Введите фамилию") на экране появится диалоговое окно, в котором будет записан текст, заключенный в кавычки, а в полосе ввода числа будет мигать курсор (рис. 5.2). Нужно ввести запрашиваемое в окне значение и нажать клавишу ввода или щелкнуть мышью по кнопке <ОK>.

Полосадляввода

значения

Рис. 5.2. Окно диалогового ввода функции InputBox

При этом возвращаемое функцией InputBox значение имеет тип String. Поэтому для ввода значения числовой переменной в программе следует дополнительно преобразовать получаемое значение к числовому типу функцией Val. Например, для ввода значения числа 5.25 в переменную b необходимо записать следующую строку программного кода:

b = Val(InputBox("Введите значение b"))

При вводе вещественного числа целая часть отделяется от дробной точкой. Число в степенной форме, например 1.2 10 –6, записывается при вводе как 1.2Е-6.

40

2. Можно ввести значения с листа Excel. Например, чтобы поместить значение в переменную х из ячейки A2, можно записать:

x = Worksheets("Лист1").Range("A2")

или

x = Worksheets("Лист1").Cells(2, 1)

где Worksheets("Лист1") – рабочий лист Excel с именем "Лист1"; Range("A2") – ячейка с именем A2; Cells(2, 1) – ячейка с номером строки 2 и номером столбца 1 (т. е. ячейка A2).

Вывод результатов может осуществляться в окна сообщений и в ячейки таблицы.

1. Вывести значение переменной в окно сообщений можно с помощью функции MsgBox. Ее использование позволяет выводить и числа и произвольные текстовые сообщения на экран, при этом выводимое сообщение должно быть заключено в кавычки. Например, в результате выполнения строки MsgBox ("Деление на нуль") на экране появится окно c текстом, приведенным в кавычках.

Число, выводимое в окно, можно форматировать (указывать количество знаков до и после запятой). Для этого используется функция Format(x, "##.##"), где x – переменная; ##.## – формат числа.

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

MsgBox ("Значение x=" & Format(x, "##.#"))

2. Можно вывести результаты в ячейки листа Excel. Например:

Worksheets("Лист1").Range("A3") = x

Здесь значение х выводится в ячейку A3:

Worksheets("Лист1").Cells(3, 1) = x

Значение х выводится в ячейку c номером строки 3 и номером столбца 1, т. е. в ячейку А3.

5.3.Линейные программы. Расчет по формулам

Влинейных программах выполняются расчеты по формулам и

другие действия, не требующие разветвления процессов и их повторов. Пример 5.2. Для значений x = 15, a = 3.75 и m = 0.5 · 10–4 вычис-

лить значение z по следующим формулам:

41

w = 0.1xa(1−m2 ), z = sin 2 +ww .

В первом варианте программы ввод исходных данных организован с клавиатуры, вывод – в окно сообщений:

Sub CommandButton1_Click()

Dim x As Single, a As Single, m As Single Dim w As Single, z As Single

x = Val(InputBox("Введите x")) a = Val(InputBox("Введите a")) m = Val(InputBox("Введите m")) w = 0.1 * x * a * (1 - m ^ 2)

z = Sin(w / (2 + w))

MsgBox (w) : MsgBox (z) ' Два оператора на одной строчке

End Sub

Во втором варианте программы ввод данных организован из ячеек листа Excel, вывод – в ячейки листа. Используются объекты

Range и Cells:

Sub CommandButton1_Click()

Dim x As Single, a As Single, m As Single Dim w As Single, z As Single

a = Worksheets("Лист1").Range("B3")

m = Worksheets("Лист1").Range("B4") w = 0.1 * x * a * (1 - m ^ 2)

z = Sin(w / (2 + w)) Worksheets("Лист1").Range("B8") = w Worksheets("Лист1").Cells(2, 9) = z End Sub

5.4. Циклические программы

Если в программе надо повторить один оператор или последовательность операторов несколько раз, используются операторы циклов. Операторы циклов заключают такой оператор или группу операторов между ключевыми словами начала и конца цикла (For...Next или Do...Loop) и определяют условия повтора выполнения.

Циклы For…Next используются, когда заранее определено, сколько раз должно выполняться повторение группы операторов:

42