Методичка по информационным технологиям

81

Задача: сделать в ячейке D2 выпадающий список, чтобы пользователь мог выбирать имена из списка (столбец А). Если нужного имени нет в списке, то пользователь может ввести новое имя прямо в ячейку D2 - оно автоматически добавится к столбцу А и начнет отображаться в выпадающем списке в будущем.

Шаг 1. Создаем именованный диапазон

Сначала создадим именованный диапазон, указывающий на заполненные именами ячейки в столбце А - сколько бы имен в списке не находилось. Для этого идем в меню Вставка - Имя - Присвоить (Insert - Name - Define), вводим имя диапазона (допустим People) и в строку Ссылка (Reference) вводим следующую формулу:

=СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1)

Эта формула ссылается на все заполненные ячейки в столбце А, начиная с А1 и вниз до конца - до последнего имени.

Шаг 2. Создаем выпадающий список в ячейке

Выделяем ячейку D2 и открываем меню Данные - Проверка (Data - Validation). Далее выбираем из выпадающего списка Тип (Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон:

Чтобы Excel позволил нам в будущем ввести в список и новые имена, снимем галочки на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке

(Error Alert) и нажмем ОК. Теперь у нас есть выпадающий список в ячейке D2. Причем, если, например, вручную дописать новое имя в столбце А, то оно автоматически появится в выпадающем списке в ячейке D2, поскольку имена берутся из динамического диапазона People, который автоматически отслеживает изменения в столбце А.

Шаг 3. Добавляем простой макрос

82

Щелкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Исходный текст (View Source). Откроется редактор Visual Basic, куда надо скопировать такой код:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub

If Target.Address = "$D$2" Then

If IsEmpty(Target) Then Exit Sub

If WorksheetFunction.CountIf(Range("People"), Target) = 0 Then

lReply = MsgBox("Добавить введенное имя " & _

Target & " в выпадающий список?", vbYesNo + vbQuestion)

If lReply = vbYes Then

Range("People").Cells(Range("People").Rows.Count + 1, 1) = Target

End If

End If

End If

End Sub

Если Ваш выпадающий список находится не в ячейке D2 или Вы назвали диапазон с именами не People, а как-то еще, то подправьте эти параметры в макросе на свои.

Теперь при попытке ввести новое имя в ячейку D2 Excel будет спрашивать

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

83

Задание 46. Выпадающий календарь для ввода дат

Наша задача - добавить в книгу Excel пользовательскую форму (окно) с автоматическим календарем. Вот такое:

Окошко будет появляться при нажатии сочетания клавиш и после выбора в нем нужной даты - она попадает в текущую ячейку листа.

Откройте редактор Visual Basic через меню Сервис - Макрос - Редактор Visual

Basic (Tools - Macro - Visual Basic Editor).

Создайте в нем новую пустую пользовательску форму, выбрав в меню Insert - User form.

Автоматически должна появиться панель Toolbox:

Если не появилась, перейдите в меню View - Toolbox.

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

84

В нем надо найти и отметить Элемент управления Календарь 11.0 (Calendar

Control 11.0). Версии могут отличаться (11.0, 10.0 и т.д.) в зависимости от версии Microsoft Office, это несущественно. После нажатия на ОК на панели появится новая кнопка - Calendar:

Щелкните по ней, а затем нарисуйте календарь на поле формы, удерживая нажатой левую кнопку мыши:

85

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

Теперь переключитесь в режим ввода программного кода созданной формы (меню View - Code) и скопируйте туда этот текст:

1.Private Sub Calendar1_Click()

2.ActiveCell = Calendar1.Value

3.ActiveCell.NumberFormat="dd/mm/yy"

4.End Sub

5.

6.Private Sub UserForm_Activate()

7.Me.Calendar1.Value = Date

8.End Sub

Теперь надо заставить Excel показывать нам созданную форму, когда мы этого захотим. Для этого вставьте новый модуль (Insert - Module) и скопируйте туда этот текст:

1.Sub ShowCalendar()

2.UserForm1.Show

3.End Sub

Осталось закрыть редактор Visual Basic и, вернувшись в Excel, назначить созданному макросу ShowCalendar любое подходящее сочетание клавиш (меню

Сервис - Макрос - Макросы - кнопка Параметры).

Если Вы хотите, чтобы календарь автоматически появлялся на экране, когда пользователь выделяет определенные ячейки (диапазон) на листе, то щелкните правой кнопкой мыши по ярлычку этого листа и выберите Исходный текст (View Source). В открывшееся окно редактора Visual Basic скопируйте следующий код:

1.Private Sub Worksheet_SelectionChange(ByVal Target As Range)

86

2.If Target.Cells.Count > 1 Then Exit Sub

3.If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then

4.UserForm1.Show

5.End If

6.End Sub

Теперь при выделении любой ячейки из диапазона А1:А20 календарь будет автоматически отображаться на экране.

Тема 10. Основы Visual Basic for Application

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

Задание 47. Расчет температуры окружающей среды по пению сверчка.

Среди животных, прекрасно играющих роль живых термометров, сверчки очень чутко реагируют на изменение температуры окружающего воздуха. Немецкий энтомолог С. Шкаф установил, что, если к количеству стрекотаний сверчка в течение 14 секунд прибавить цифру 40, то в десяти случаях из десяти будет совершенно точно угадана температура воздуха по Фаренгейту. Закономерность эта оказалась на столько точной, что в последствии были даже выведены формулы для определения температуры воздуха по пению сверчка - трубачика и домового сверчка.

Для сверчка - трубачика Для домового сверчка

где: Т - температура воздуха по Фаренгейту; Н - количество стрекотаний поющего насекомого в одну минуту.

Создайте функцию пользователя с именем Сверчок для расчета температуры окружающей среды в зависимости от вида сверчка. Аргументы этой функции назовем Стрекот и Вид. Для аргумента Вид предусмотрены только два допустимых значения: 1 для сверчка - трубачика и 0 для домового сверчка.

Постройте пользовательскую функцию Сверчок следующим образом:

1.Выполните команду Сервис, Макрос, Редактор Visual Basic (Tools, Macro, Visual

Basic Editor), чтобы открыть окно редактора Visual Basic.

2.Выполните команду Вставка, Модуль (Insert, Module) для создания листа модуля.

3.Выберите значок модуля в окне Проект (Project), чтобы активизировать окно редактора кода на листе модуля.

4.Наберите на листе модуля приведенную ниже процедуру.

87

Function Сверчок(Стрекот, Вид)

If Вид = 1 Then

Сверчок = 50 + (Стрекот - 92) / 4.7 Else: Сверчок = 50 + (Стрекот - 40) / 4 End If

End Function

5.Отладьте функцию Debug > Compile.

6.Если нет ошибок, то функцию стоимость можно использовать так же, как и стандартные функции Excel. Она включена в категорию функций, определенных пользователем, мастера функций.

7.Протестируйте работу функции на различных значениях.

САМОСТОЯТЕЛЬНО:

8.Перепишите функцию так, чтобы в случае ввода неверного Вида (значение отлично от 0 или 1), появлялось бы сообщение «Ошибка».

9.Перепишите функцию так, чтобы в случае ввода неверного количества стрекотаний поющего насекомого Стрекот (значение меньше или равно 0), появлялось бы сообщение «Ошибка».

Задание 48. Взаимное влияние двух конкурирующих видов.

Взаимное влияние некоторых двух конкурирующих видов на размер Xn, Yn их популяций в n-м году описывается системой

хn+1 = 2хn — уn, уn+1 = — хn + 2уn.

Пусть X0 = а, Y0 = b (а≠b), где а и b — данные числа.

Создайте функцию пользователя, вычисляющую численность одного из видов через K лет. Аргументы этой функции: A – исходная численность вида X; B – исходная численность вида Y; K – количество лет.

Function ВидX(A As Integer, B As Integer, K As Integer) As Single Dim i, x, y, xi, yi As Single

x = A y = B

For i = 1 To K xi = 2 * x - y

yi = -x + 2 * y x = xi

y = yi Next i

ВидX = x

88

End Function

САМОСТОЯТЕЛЬНО:

Создайте функцию пользователя, вычисляющую численность одного из видов после полного вымирания второго. Аргументы этой функции: A – исходная численность вида X; B – исходная численность вида Y.

Процедуры ввода-вывода через диалоговые окна

Для ввода небольших фрагментов текста Visual Basic предлагает функцию InputBox. Окно ввода – InputBox, состоит из четырёх элементов:

-строка заголовка;

-приглашение к вводу(Promt);

-поле ввода со значением, предлагаемым по умолчанию;

-две кнопки (Ok и Cancel).

Окно ввода

InputBox

Функция вызова окна InputBox имеет следующий синтаксис с соответствующими именованными аргументами:

Возвращаемое_значение = InputBox (prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context] )

Параметр Prompt определяет текст, отображающийся в диалоговом окне как приглашение.

Title - отвечает за надпись заголовка; если это параметр не указан, то отображается название приложения.

Параметр Default определяет значение по умолчанию, отображаемое в строке ввода.

Параметры xpos и ypos указывают координаты верхнего левого угол окна (параметры xpos и ypos нужно использовать совместно). По умолчанию окно отображается по середине экрана.

Для вывода различных сообщений имеется окно, подобное InputBox, – MessageBox. Его вид может быть различным, но в его состав всегда входят:

-текст сообщения;

-заголовок;

-пиктограмма;

-набор кнопок.

89

Окно вывода MessageBox

MessageBox можно вызвать как процедуру и как функцию: 1.Синтаксис команды:

MsgBox Prompt [, Buttons] [, Title] [, Helpfile, Context]

Пример использования:

MsgBox “Здравствуй, пользователь”, vbExclamation, “Приветствие”

2.Синтаксис функции:

Возвращаемое_значение = MsgBox (Prompt [, Buttons] [, Title] [, Helpfile, Context])

Пример использования:

ret = MsgBox (“Закончить?”, vbCritical, “End”)

Параметры Prompt и Title не требут пояснений. Параметр Buttons определяет внешний вид MessageBox. Значение параметра формируется из нескольких частей, которые можно складывать:

Buttons = Button + Icon + Default + Modal + Extras + Extras

Для категорий Button, Icon, Default, и Modal можно использовать только одно из допустимых значений. А для категории Extras допускается применение комбинации значений.

Функция IsNumeric(Expression) проверяет, может ли указанное значение быть преобразовано в число.

Expression - любая переменная или любое значение.

Возвращает значение типа Boolean. Если аргумент является численным выражением, функция возвращает True, в противном случае - False.

Задание 49. Последовательность Фибоначчи

Последовательность Фибоначчи u0, u1, … образуется по закону:

u0=0, u1=1, ui=ui-1+ui-2 (i=2, 3 ...).

Числа Фибоначчи получили название в честь итальянского математика XIII века Леонардо Фибоначчи, который ввел их для описания численности поколений животных (без учета смертности). Предполагается, что каждая пара животных некоторого вида приносит ежегодно приплод в одну пару животных (самку и самца), которые в свою очередь начинают давать приплод через два года после

90

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

(un+1).

1.Создать процедуру с именем FBN для решения задачи Фибоначчи. Имя: FBN

Входные данные: N – год (целое)

Выходные данные: U – количество пар животных (целое)

2.Организовать ввод и вывод данных через диалоговые окна.

3.При вводе неверных значений повторять ввод.

4.Создать на пустом листе кнопку, вызывающую процедуру FBN.

Sub FBN()

Dim u0, u1, u As Variant

Dim N As Integer

Dim Prom As Variant

Do

Prom = InputBox("Введите значение N=")

If Not IsNumeric(Prom) Then MsgBox ("Повторите ввод!")

Loop Until IsNumeric(Prom)

N = Prom

u0=0

u1=1

For i=2 to N+1 u = u0+u1 u0=u1

u1=u Next i

MsgBox(“Результат U = ”) & CSng(u)

End Sub

Private Sub CommandButton1_Click()

‘ Если нажата кнопка - вызвать процедуру Krb

Call FBN

End Sub

Задание 50. Контрольная работа

1.В одной кладке самки дафнии бывает примерно 60 яиц. Спустя около15 суток вылупляются молодые дафнии, которые способны сами откладывать яйца. Сколько дафний может появиться от одной самки за N месяцев?

2.Колорадских жук в течение суток объедает 7 см2 листьев. За своё развитие личинка съедает приблизительно 50 см2 листьев картофеля. Подсчитайте,