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

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

Способ 1 – Функция сохраняется как файл Excel. Для этого после создания функции на листе модуля VBAи ее использования перед выходом из Excel:

1. На рабочем листе Excel выберите команды Файл/Сохранить как … - будет выдано диалоговое окно Сохранение.

2. В раскрывающемся списке Сохранить в: выберите диск (папку), на котором хотите сохранить функцию. Например, дискета 3,5 (или папка Мои документы). В графе Имя файла введите имя функции. Например, G. (Желательно выбирать имена функций, которые напоминали бы о назначении функции, например, Доход, премия и т.д.). В раскрывающемся списке тип файла выберите Книга Microsoft Excel и щелкните Сохранить.

3. Выйдите из Excel.

Для включения функции в библиотеку встроенных функций и использования ее:

1. Войдите в Excel и выберите команды Файл\Открыть. Откройте диск, на котором сохранена функция, выделите функцию и щелкните кнопку Открыть.

2. Щелкните кнопку Включить Макросы (или не отключать макросы) и войдите в редактор Visual Basic – функция будет включена в библиотеку Excel, в категорию Определенные пользователем.

Если созданная вами функция очень важна для вас и используется вами достаточно часто, желательно сократить путь доступа к ней. В этом случае можно использовать для ее сохранения Способ 2, который позволяет включить ее в библиотеку встроенных функций, и она будет доступна при последующих запусках Excel.

Способ 2. – Сохранение пользовательской функции в личной книге макросов (PERSONAL.XLS).

Для этого необходимо предварительно создать проект PERSONAL…, для чего: 1. Выполнить команды Сервис\Макрос\Начать запись… - будет выдано диалоговое окно Запись макроса. 2. В поле Сохранить в: выделить Личная книга макросов и щелкнуть ОК. 3. Выполнить команду Остановить запись. 4. Выполнив команды Сервис\Макрос\Редактор Visual Basic, вы убедитесь, что проект PERSONAL создан. Теперь вы можете создавать функции в проекте PERSONAL, точно так как, например, создали функцию G в проекте Книга1.

1. Создадим, например, функцию Y(x)=X^2 и щелкнем кнопку на панели инструментов ……Сохранить PERSONAL.XLS. 2. выполним команды Файл\Закрыть и вернуться в Microsoft Excel. 3. Щелкните f(x), определенные пользователем, функция Y – созданная функция Y находится в категории Определенные пользователем под именем PERSONAL.XLS!Y. 4. выполните команды Файл\Выход – будет выдано сообщение: сохранить выполненные изменения в личной книге макросов? Если нажать Да, функция Y станет доступной при следующем запуске Microsoft Excel. Таким образом, созданная функция Y сохранена в библиотеке встроенных функций. Функцию Примера1 сохраните на дискете 3,5 (диск G) как файл Excel.

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

Для записи функции на VBA используется строчный оператор

If Then.

Function Z(t)

If t<= -1 Then z=(1+Abs(t))/(1+t+t^2)^(1/3)

If t>-1 And t<0 Then z=2*Application.Ln(1+t^2)+ _

(1+cos(t)^4)/(2+t)

If t>=0 Then z=(1+t)^(3/5)

End Function.

Задания: 1. Реализовать функции примера 1 и примера 2 и освоить способы запуска сохранения и использования пользовательских функций. 2. Задание для самостоятельного выполнения. Разработать функцию для выполнения примера 2 с использованием блочного оператора If Then Else.

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

Пример 3. Создадим функцию пользователя с именем Стоимость, рассчитывающей стоимость партии книг по прогрессивной шкале цен, а именно: если продается от 100 до 200 экземпляров книги, то скидка от ее отпускной цены составляет 7 %, если продается от 201 до 300 экземпляров -10 %, а если свыше 300 экземпляров – 15 %. Кроме того, для постоянных клиентов предусмотрена дополнительная скидка в размере 5 %.

Аргументы этой функции назовем ЦенаОднойКниги, Количество и Скидка. Для аргумента Скидка предусмотрим только два допустимых значения: 1 для постоянных клиентов и 0 в противном случае.

Для построения пользовательской функции Стоимость:

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

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

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

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

Function Стоимость (ЦенаОднойКниги, Количество, Скидка)

‘

'Вычисление стоимости без учета скидки для постоянных клиентов.

'

If количество<100 Then

'

'Продажа до 99 экземпляров

'

СтоимостьБезСкидки = ЦенаОднойКниги * Количество

Else

If Количество <=200 Then

'

'Продажа от 100 до 200 экземпляров

'

СтоимостьБезСкидки = ЦенаОднойКниги* Количество* 0,93

Else

‘

'Продажа от 201 до 300 экземпляров

'

If Количество < = 300 Then

СтоимостьБезСкидки=ЦенаОднойКниги*Количество*0,9

Else

'

'Продажа свыше 300 экземпляров

'

СтоимостьБезСкидки=ЦенаОднойКниги*Количество*0,85

End If

End If

End If

'

'Корректировка стоимости с учетом скидки для постоянных клиентов

'

If Скидка=0 Then

Стоимость=СтоимостьБезСкидки

Else

Стоимость=СтоимостьБезСкидки*0,95

End If

End Function

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

Для возврата в Excel выполните команду Файл, Закрыть и вернуться в Microsoft Excel. Для использования функции щелкните кнопку мастер функций - fx, категория функции - созданные пользователем, функция Стоимость. Выдается диалоговое окно для заполнения параметров функции Стоимость (рис. 3.5.1.1).

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

Рис. 3.5.1.1. Диалоговое окно для заполнения параметров функции Стоимость

При необходимости внести изменения в текст функции выберите команду Сервис, Макрос, Макросы. Нажмите имя макроса Стоимость и нажмите кнопку Изменить. Будет выдан текст макроса.

Задания: 1. Реализовать функцию Стоимость.

2. Создать функцию Стоимость, используя оператор If Then Else блочной структуры.

3. Индивидуальное задание – изменить наименование функции Стоимость на СтоимостьN, где N – номер студента в списке группы.

Пример4. Создание функции пользователя с оператором выбора Select Case

Оператор выбора Select Case удобно использовать, когда в зависимости от значения некоторого выражения, имеющего конечное множество допустимых значений, необходимо выполнить разные действия.

Рассмотрим пример начисления комиссионных на основе оператора Select Case. Здесь размер комиссионных зависит только от объема проданной продукции по правилу:

Объем продаж, тыс.руб.	Комиссионные, %
0-9999 10000-39999 40000 - и более

Задачу начисления комиссионных решает следующая функция:

Function Премия(продажа)

Select Case продажа

Case 0 To 9999

Премия=0.08*продажа

Case 10000 To 39999

Премия=0.1*продажа

Case Is>=40000

Премия=0.14*продажа

End Select

End Function.

Is является ключевым словом VBA, обозначающим выражение в операторе Case. В операторе Case допустимы составные условия, например:

Case 4, 7 То 8, 11 То 12, 15 проверяет, принадлежит ли проверяемое выражение одному из отрезков: от 7 до 8 и от 11 до 12 или равняется одному из значений: 4 и 15.

Case 5, 6, 9 То 10, 13, 14 Is>=16 проверяет, принадлежит ли проверяемое выражение отрезку от 9 до 10 или равняется одному из значений: 5, 6, 13 и 14, или оно меньше, чем 16.

Для сравнения приведем формулу, которую надо ввести в ячейку В1 при решении этой задачи в Excel без использования функций пользователя

=ЕСЛИ(И(А1>=0; A1<10000);a1*0.08;ЕСЛИ(И(A1>=10000;A1<40000);

A1*0.1;ЕСЛИ(А1>=40000;A1*0.14)))

Пример 5. – В этом примере, использующем оператор Select Case, осуществляется выбор ставки налога (0; 0,05; 0,10; 0,15; 0,20) в зависимости от значения выражения intПараметр. Предусмотрен также вариант выбора, когда значения выражения intПараметр не совпадает ни с одним выражением в предложениях Case.

Function Налог(sngСумма As Single, intПараметр As Integer)

Select Case intПараметр

Сase 0

Налог= sngСумма*0

Case 1

Налог= sngСумма*0,05

Case 2

Налог= sngСумма*0,1

Case 3

Налог= sngСумма*0,15

Case 4

Налог= sngСумма*0,2

Case Else

Налог= sngСумма*0

End Select

End Function

Пример 6. Создание функции пользователя с оператором цикла For-Next

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

где P(1) и d(1) - размер и дата выдачи ссуды, причем P(1) берется со знаком минус, P(j), d(j) - размер и дата j-й выплаты, n-1 - число выплат, i - годовая процентная ставка.

Option Explicit

Option Base 1

Function Доход (процент As Double, платеж As Variant, _

год As Variant) As Double

Dim i, j, n As Integer, s As Double

n=платеж. Rows. Count

S=0

For i=1 To n

S=S+ платеж (i)/ _

(1+процент)^((год(i)-год(1))/365)

Next i

Доход =S

End Function.

Отметим, что метод Rows возвращает строки диапазона платеж, а свойство Count считает число элементов объекта. Таким образом, платеж.Rows.Count определяет число строк в диапазоне платеж. Для того чтобы найти число столбцов диапазона платеж, нужно использовать конструкцию платеж.Columns.Count.

Рис. 3.5.1.2. решение задачи о неравномерных платежах

Решим с помощью функции Доход следующую задачу. Предположим, что 11.01.97 у вас берут в долг 10000 руб. и предлагают вернуть: 20.12.97 - 2000 руб., 18.10.98 - 40000 руб., 12.04.99 - 7000 руб. Имеет ли смысл эта сделка при годовой ставке 10%?

Для решения этой задачи введены данные, как показано на рис. 3.5.1.2.

Ссуда введена в ячейку В2 со знаком минус, т.к. эти деньги у вас забирают. В ячейку В8, где вычисляется разность между текущим объемом вклада и размером ссуды, введем формулу=Доход(В7;В2:В5;D2:D5). В данном случае найденное значение равно 857.91. так как результат положителен, данная сделка выгодна.

Пример 7. Cсоздание функции пользователя с оператором Exit For

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

Function Тест(a As Variant, в As Variant) As Integer

Dim i, n As Integer, t As Boolean

n=a. Rows.Count*a.Columns.Count

t=False

For i=1 To n

If a(i)=в Then

Тест=i

T=True

Exit For

End If

Next i

If t=False Then Тест=-1

End Function

Пример8. Создание функции пользователя с использованием оператора цикла While-Wend

Синтаксис оператора While-Wend приведен на с.

Приведенная ниже функция Доход_2 вычисляет то же значение, что и функция Доход, но с использованием оператора цикла While-Wend:

Option Base 1

Function Доход_2(процент As Double, платеж As Variant, _

год As Variant) As Double

Dim i, j, n As Integer, s As Double

n=платеж.Rows.Count

S=0

I=1

While i<=n

S=S+ платеж(i)/(1+процент)^((год(i)-год(1))/365

I=i+1

Wend

Доход_2=S

End Function.

Задания: 1. Реализуйте функции 1-8.

2. Индивидуальное задание 1. выполните вычисления по заданной функции Y(Xi) с циклом и разветвлением внутри цикла (вариант 1-12 – см. ниже). Номер варианта равен номеру студента в списке группы до 12 номера, после 12 номера – номер в списке группы минус 12, т.е. номер варианта студента под номером 13 в списке группы равен 13-12=1 и т.д.

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

Пример выполнения задания

Исходная функция

Здесь xi – элементы массива Х(12), Сi изменяются одновременно с Xi от начального значения С=5 c шагом 1,5. Численные значения элементов массива Х, величина a и k выбрать самостоятельно.

Полагаем a=1; k=6; x(1)=1; x(2)=2, … x(12)=12

c(1)=5; c(2)=6,5, … c(12)=21,5

Контрольный просчет

Контрольная точка1 – Х(1)=1 Y(1)=5*1*1=5

Контрольная точка2 – Х(7)=7 Y(7)=1*7+14=21

Последовательность выполнения задания

Для построения пользовательcкой функции Y(x):

1. Войдите в Excel и выполните команду Серсвис,Макрос, Редактор Visual Basic, чтобы открыть окно редактора VBA

2. Выполните команду Вставка,Модуль

3. На листе Модуля наберите текст функции:

Function Y (X, C As Variant) As Variant

If X>6 Then Y=1*X+C Else Y=5*1*X

End Function

4. Для возврата в Excel выполните команду Файл,Закрыть и вернуться в Microsoft Excel.

5. На рабочем листе Excel в ячейки А1:А12 введите значения Х равные 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, а в ячейки В1:В12 введите значения С, равные - 5; 6,5; 8; 9,5; 11; 12,5; 14; 15,5; 17; 18,5; 20; 21,5.

6. Выделите ячейку С1, щелкнув по кнопке Мастера функций - f, далее - по категории Определенные пользователем, Y и OK.

7. В строку Х диалогового окна введите А1, а в строку С В1 и ОК.

8. Повторите п.п. 6-7 для ячейки С2.

9. Установите указатель мыши на маркере заполнения (маленький Å в нижнем углу) и протащите его до ячейки С12.

В ячейках С1:С12 - результаты вычисления функции Y(x).

10. Постройте график (диаграмму) функции Y(x)

Варианты функций

Вариант 1

Здесь х_i - элементы одномерного массива х, i изменяется в пределах от 1 до 12 с шагом 1. Численные значения а, с, k_1, k₂ и x_i выбрать самостоятельно.

Вариант 2

где x_i и c_i -элементы одномерных массивов, соответственно х(12), с(12); i изменяется от 1 до 12 с шагом 1.

Вариант 3

Массив x_i содержит 12 элементов. Численные значения элементов массива и величины а, в, с и k выбрать самостоятельно.

Вариант 4

Массивы Х и С содержат по 12 элементов. Численные значения элементов массивов и а, k₁,k₂ выбрать самостоятельно.

Вариант 5

где массивы Х и С содержат по 12 элементов. Численные значения величин а, b и элементов массивов выбрать самостоятельно.

Вариант 6

Здесь х_i - элементы одномерного массива, с изменяется одновременно с х_i от начального значения с = 5 с шагом 1,5; численные значения элементов массива Х(12), величин b и k - выбрать самостоятельно.

Вариант 7

Здесь с_i и х_i - элементы одномерных массивов соответственно С(15) и Х(15). Численные значения элементов массивов, величин b, k₁, k₂ и k₃ выбрать самостоятельно.

Вариант 8

Здесь х_i - элементы массива Х(12), с изменяется одновременно с х от начального значения с = -8 с шагом 1.5. Численные значения элементов массива, величины b выбрать самостоятельно.

Вариант 9

Здесь х_i - элементы массива Х(12), а изменяется одновременно с х от начального значения а =1.5 с шагом 0.5. Численные значения элементов массива, величин с и k₁ выбрать самостоятельно.

Вариант 10

Здесь х_i - элементы массива Х(12), а изменяется одновременно с х_i от начального значения а = -1.5 с шагом 0.5. Численные значения элементов массива, величин b и k выбрать самостоятельно.

Вариант 11

Здесь массив х содержит 20 элементов, коэффициенты а, c, b, k₁, k₂, k₃ выбрать самостоятельно.

Вариант 12

Здесь х_i - элементы одномерного массива Х(12); k₁ < k₂< k₃; a, b, c, k₁, k₂ , k₃ - коэффициенты выбрать самостоятельно.