Лекция 14 ПРОГРАММИРОВАНИЕ В СРЕДЕ VBA Создание пользовательских функций в ТП MS Excel

Файл Параметры Настройка ленты Разработчик Visual Basic Insert Module

ФОРМАТ ФУНКЦИИ ПОЛЬЗОВАТЕЛЯ Описание: Function {(Cписок форм. параметров)} Оператор 1 Оператор 2... Оператор N End Function Обращение: Имя{(Cписок фактических параметров)}

Пример 1. Пример 1. Ежемесячная премия сотрудников равна проценту от тарифного оклада. Составить функцию, вычисляющую процент от числа. Function Премия(Оклад As Single, Процент_премии As Single) Премия = Оклад * (Процент_премии / 100) End Function

Пример 2Пример 2. Составить пользовательскую функцию, которая рассчитывала бы размер премии в зависимости от оклада на основании шкалы: Function Премия_С(Оклад As Single) If Оклад < Then Премия_С = "Премии нет" If (Оклад >= ) And (Оклад < ) Then Премия_С= Оклад* 0.1 If (Оклад >= ) And (Оклад < ) Then Премия_С = Оклад* 0.15 If Оклад > Then Премия_С = Оклад * 0.2 End Function

Пример 3. Пример 3. Разработать пользовательскую функцию Y = f(a,b) на VBA для вычисления значения функции и построить в Excel график функции на интервале [m,n]. Function Y(a As Integer, b As Integer) If a < 0 Then Y = "Y не определен" If (a >= 0) And (a < b) Then Y = Sqr(a) If a >= b Then Y = Sqr(b) End Function

Формула с использованием встроенной функции EСЛИ: вводится в ячейку В2 и копируется в ячейки B3:B15: =ЕСЛИ (A2

Пример 4. Пример 4. В книге MS Excel создать список студентов (ФИО, Форма обучения (бюджет, внебюджет), Cредний балл, Cтипендия). Стипендия начисляется только студентам- бюджетникам и рассчитывается следующим образом: Стипендия = Базовая величина * Коэффициент. Коэффициент зависит от среднего балла и устанавливается в соответствии с таблицей:

Пользовательская функция на VBA Стипендия (Базовая величина, Средний балл) Стипендия Function СТИПЕНДИЯ(Базовая_величина As Double, Средний_балл As Double) If Средний_балл < 6 Then СТИПЕНДИЯ = "---" If Средний_балл >= 6 And Средний_балл < 7 Then СТИПЕНДИЯ = 3 * Базовая_величина If Средний_балл >= 7 And Средний_балл < 8 Then СТИПЕНДИЯ = 3.5 * Базовая_величина If Средний_балл >= 8 And Средний_балл < 10 Then СТИПЕНДИЯ = 4 * Базовая_величина If Средний_балл = 10 Then СТИПЕНДИЯ = 5 * Базовая_величина End Function

Пример 5. Оценить кредитоспособность потенциальных заемщиков банка на основе методики Альтмана 1. Рассчитывается статистическая модель Z, составляемая по данным балансового отчета фирмы: Z = 1,2*X1 + 1,4*X2 +3,3*X3 + 0,6*X4 +0,999*X5, Х1 – отношение собственных оборотных средств к сумме активов; Х2 – отношении балансовой прибыли к сумме активов; Х3 – отношение операционных доходов к сумме активов; Х4 – отношение рыночной стоимости акций фирмы к итогу пассива, т.е. собственного капитала к привлеченному капиталу; Х5 – отношение выручки от реализации к сумме активов.

2. Принимается решение в соответствии с классификационным правилом, приведенным в таблице ZВывод Z < 1,81Вероятность банкротства высока 1,81 Z 2,99Предприятие работает успешно

Функция Функция АЛЬТМАНА Function АЛЬТМАН (x1 As Double, x2 As Double, x3 As Double, x4 As Double, x5 As Double) АЛЬТМАН = (1.2 * x1) + (1.4 * x2) + (3.3 * x3) + (0.6 * x4) + (0.999 * x5) End Function

Функция Функция РЕЗУЛЬТАТ Function РЕЗУЛЬТАТ (z As Double) If z < 1.81 Then РЕЗУЛЬТАТ = "Банкротство наступило" If (z 1.81) Then РЕЗУЛЬТАТ = "Вероятность высокая" If (z 2.675) Then РЕЗУЛЬТАТ = "Вероятность средняя" If z > 2.99 Then РЕЗУЛЬТАТ = "Банкротство не угрожает« End Function