Создание пользовательских функций

 

Пользовательская функция – набор инструкций, предназначенных чаще всего для расчета, анализа и преобразования данных. Функция всегда возвращает результат. Вызов функции можно использовать на рабочем листе, обращаться к ней по имени из других функций и процедур.

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

Function имя ([ аргументы ])

инструкции

имя =<выражение>

End Sub

 

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

· задать свойство объекта;

· вставить, удалить или отформатировать ячейки;

· задать или изменить содержимое ячейки;

· переименовать, удалить, добавить рабочий лист;

· создать, открыть, удалить рабочую книгу и пр.

 

Пример 1. Создать функцию, определяющую сумму или разность двух чисел по желанию пользователя

 

Function выбор (a, b as Currency)

q=MsgBox (“Сложить?”; vbYesNo; “Ваше решение?”)

If q = vbYes Then

vibor = a + b

else

vibor = a - b

End If

End Function

 

Пример 2. Определить, возможно ли одну символьную строку сформировать из символов другой символьной строки.

 

Function проверка (a, s As String)

For i = 1 To Len (a)

For j = 1 To Len (s)

If Mid (a, i, 1) = Mid (s, j, 1) Then fl = fl + 1

Next j

Next i

If fl >= Len (a) Then проверка = "Возможно" else проверка = "Нельзя"

End Function

 

Задание № 1. Создать пользовательскую функцию по обработке символьных строк согласно номеру варианта (табл. 1).

Таблица 1

Пользовательские функции по обработке символьных строк

№	Задача
	Удвоить каждый символ в строке
	Переставить символы попарно
	Прописные буквы заменить на строчные, строчные на прописные
	Заменить каждый четный символ в символьном выражении на прописной
	Заменить каждый нечетный символ на строчный, после него добавить пробел
	Из символьного выражения сделать обратное с пробелами между символами
	В символьной строке заменить символы на соответствующие им ASCII коды, разделенные пробелом
	Преобразовать символьную строку в строку, в которой после каждого символа стоят две звездочки
	Определить число вхождений символа в символьную строку без учета регистра.
	Поменять местами левую и правую части символьного выражения (например «зачет» преобразуется в «етчза»)

Задание № 2. Создать пользовательскую функцию, рассчитывающую показатель, представленный в табл. 2.

Таблица 2

Пользовательские функции

№	Задача
	Комиссионные от продаж, вычисляемые как процент от суммы продаж и премии, зависящей от объема продаж
	Комиссионные от продаж, вычисляемые как процент от суммы продаж и фиксированной премии, начисляемой по желанию пользователя
	Стоимость услуг за телефонные переговоры (фиксированная сумма за установленное время и дополнительная за каждую минуту превышения установленного предельного времени)
	Оплату услуг за телефон с начислением пени за каждый просроченный день
	Оплату услуг за телефон и возможностью начисления пени
	Стоимость приобретенного товара при наличии постоянной скидки
	Стоимость приобретенного товара с возможностью предоставления скидки
	Стоимость приобретенного товара с возможностью предоставления различных скидок
	Сумма ежемесячных выплат за приобретенный в кредит товар по принципу первоначальный взнос 10% от стоимости товара, срок 10 месяцев, процент за весь срок 10% годовых

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

Таблица 3

Типы полей и их количество

№	Количество полей соответствующего типа
Символьное	Числовое	Дата	Логическое

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

Таблица 4

Создание пользовательских функций для обработки числовой информации.

№	Задача
	Определить, делится ли натуральное трехзначное число на три без остатка по признаку делимости
	Перевести натуральное трехзначное число, представленное в определенной системе счисления, в десятичную систему счисления.
	Дано натуральное двухзначное число. Определить количество натуральных делителей данного числа
	Определить, делится ли натуральное двухзначное число без остатка на сумму своих цифр.
	Дано натуральное четырехзначное число. Определить, является ли оно числом Армстронга. Числом Армстронга считается число, равное сумме степеней всех цифр. Степень определяется числом цифр в числе.
	Дано натуральное четырехзначное число. Определить, какая цифра больше, первая или последняя.
	Дано натуральное трехзначное число, цифры в котором не повторяются. Переставить цифры числа в порядке их увеличения
	Определить, сколько раз в натуральном четырехзначном числе встречается данная цифра
	Дано натуральное трехзначное число. Верно ли, что произведение его цифр больше пятидесяти
	Дано натуральное трехзначное число. Верно ли, что сумма его цифр – четное число
	Даны два натуральных числа (трехзначное и двузначное). Верно ли, что сумма цифр первого числа равна второму числу
	Дано натуральное трехзначное число. Определить разность между максимальной и минимальной цифрой
	Дано натуральное четырехзначное число. Определить, какая цифра расположена левее – максимальная или минимальная
	Дано натуральное четырехзначное число. Определить, сколько раз в нем встречается максимальная цифра
	Дано натуральное четырехзначное число. Определить сумму двух его максимальных цифр
	Определить, есть ли в натуральном четырехзначном числе хотя бы две одинаковые цифры.
	Дано натуральное четырехзначное число. Определить сумму трех минимальных цифр
	Дано натуральное четырехзначное число. Определить, что больше – произведение цифр, стоящих на четных или нечетных позициях
	Дано натуральное четырехзначное число. Определить сумму двух последних цифр
	Определить количество чисел из указанного диапазона двузначных чисел, в которых имеется цифра семь
	Дано натуральное четырехзначное число. Определить порядковый номер максимальной цифры, считая с начала числа
	Дано натуральное четырехзначное число. Записать его наоборот.
	Дано натуральное двухзначное число. Определить наибольший делитель, отличный от самого числа
	Даны два натуральных двузначных числа. Определить количество общих делителей
	Определить, может ли существовать данное натуральное число в представленной системе счисления.
	Найти сумму всех цифр натурального числа
	Определить количество неповторяющихся цифр в натуральном числе
	Дано натуральное число. Определить, является ли оно числом Армстронга. Числом Армстронга считается число, равное сумме степеней всех цифр. Степень определяется числом цифр в числе
	Дано натуральное число. Определить, есть ли в нем хотя бы две одинаковые цифры
	Определить, есть ли в натуральном числе хотя бы одна цифра ноль

ТЕМА № 2

ПОЛЬЗОВАТЕЛЬСКИЕ ПРОЦЕДУРЫ

 

Процедура - последовательность инструкций, выполняемых EXCEL.

Процедуры составляют большую часть кода VBA. При ее объявлении соблюдаются правила описания процедур. Упрощенный синтаксис является следующим:

Sub имя ([ аргументы ])

инструкции

End Sub

Следует обратить внимание на следующее:

· ключевые слова Sub и End Sub используются в описании любой процедуры Sub;

· параметр имя определяет имя создаваемой процедуры;

· необязательный параметр [аргументы] позволяет передать в процедуру требуемые значения, многие процедуры параметров не требуют.

 

Тема 2.1

Ввод записей в базу данных

На рабочем листе создается таблица (база данных). Для этого вводятся названия столбцов (полей). С помощью процедуры предполагается дополнение имеющихся записей в первую пустую строку базы данных.

Пример 1.

Требуется создать программу по вводу новой записи в таблицу.

 

 

	Фамилия	Должность	Оклад
	Иванов	инженер
	Петров	бухгалтер
	.....	....
N	Сидоров	инженер
N+1

 

Алгоритм решения задачи и программа методом явного перехода по ячейкам (рис. 1).

 

Sub Ввод_1 ()

Dim q As Integer, prod as Variant

Range("A1").Select

ActiveCell.CurrentRegion.Select

q = Selection.Rows.Count

ActiveCell.Cells(q + 1, 1).Activate

m1: ActiveCell.Formula = InputBox(prompt:="Введите фамилию ")

ActiveCell.Offset(0, 1).Activate

ActiveCell.Formula = InputBox(prompt:="Введите должность")

ActiveCell.Cells(1, 2).Activate

ActiveCell.Formula = InputBox(prompt:="Введите оклад")

Prod = MsgBox("Продолжим?", vbYesNo, "Ваши действия?")

If Prod = vbYes Then

ActiveCell.Offset(1, -2).Activate

GoTo m1

End If

Range("A1").Select

End Sub

 

Алгоритм решения задачи (рис. 2) и программа методом ссылок на ячейки

 

Sub ввод_2 ()

i = 1

While Cells(i, 1) <> Empty

i = i + 1

Wend

prod = vbYes

While prod = vbYes

Cells(i, 1) = InputBox("Ведите фамилию сотрудника")

Cells(i, 2) = InputBox("Введите должность")

Cells(i, 3) = Val(InputBox("Введите оклад"))

prod = MsgBox("Продолжите ввод?", vbYesNo, "Выбор")

i = i + 1

Wend

End Sub

Тема 2.2