Алексей Гладкий - Excel. Трюки и эффекты

Function dhCalculatePercent(Sales As Long, IsTemporal As Boolean)

As Double

' Процентные ставки (декларация констант)

Const dblRate1 As Double = 0.09

Const dblRate2 As Double = 0.11

Const dblRate3 As Double = 0.15

Const dblAdd As Double = 1.1

' Граничные суммы

Const lngSum1 As Long = 5000

Const lngSum2 As Long = 10000

' Рассчет суммы для выплаты (как обычно)

If Sales < lngSum1 Then

dhCalculatePercent = Sales * dblRate1

ElseIf Sales < lngSum2 Then

dhCalculatePercent = Sales * dblRate2

Else

dhCalculatePercent = Sales * dblRate3

End If

If IsTemporal Then

' Для сторонних вкладчиков – надбавка

dhCalculatePercent = dblAdd * dhCalculatePercent

End If

End Function

Теперь функция dhCalculatePercent будет иметь два аргумента. После выбора в окне Мастер функций данной функции откроется окно, показанное на рис. 2.7.

В данном окне в поле Sales указывается адрес ячейки, на основании которой требуется рассчитать сумму процентов, а в поле IsTemporaL определяется, штатному сотруднику или нет начисляются проценты. Если проценты начисляются штатному сотруднику, то в данном поле следует ввести значение False, а если стороннему вкладчику – следует ввести True (в данном случае проценты будут начислены в размере ПО % от причитающейся суммы).

Рис. 2.7. Аргументы функции dhCalculatePercent

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

Предположим, что внештатные сотрудники получают комиссионное вознаграждение в зависимости от объема продаж по следующей шкале:

до 4999 руб. – 9 %;

от 5000 до 9999 руб. -11 %;

свыше 10 000 руб.– 15 %.

При расчете сумм комиссионного вознаграждения штатным сотрудникам учитывается стаж их работы в компании: за каждый отработанный год к сумме причитающегося вознаграждения добавляется 1 %.

Для решения поставленной задачи напишем код, представленный в листинге 2.55 (этот код следует поместить в стандартный модуль редактора VBA).

Function dhCalculateCom(dblSales As Double) As Double

Const dblRate1 = 0.09

Const dblRate2 = 0.11

Const dblRate3 = 0.15

' Расчет комиссионных с продаж (без выслуги) в зависимости _

от суммы

Select Case dblSales

Case 0 To 4999.99: dhCalculateCom = dblSales * dblRate1

Case 5000 To 9999.99: dhCalculateCom = dblSales * dblRate2

Case Is >= 10000: dhCalculateCom = dblSales * dblRate3

End Select

End Function

Function dhCalculateCom2(dblSales As Double, intYears As Double) _

As Double

Const dblRate1 = 0.09

Const dblRate2 = 0.11

Const dblRate3 = 0.15

' Расчет комиссионных с продаж (без учета выслуги лет) _

в зависимости от суммы

Select Case dblSales

Case 0 To 4999.99: dhCalculateCom2 = dblSales * dblRate1

Case 5000 To 9999.99: dhCalculateCom2 = dblSales * dblRate2

Case Is >= 10000: dhCalculateCom2 = dblSales * dblRate3

End Select

' Надбавка за выслугу лет

dhCalculateCom2 = dhCalculateCom2 + _

(dhCalculateCom2 * intYears / 100)

End Function

Sub ComCalculator()

Dim strMessage As String

Dim dblSales As Double

Dim ан As Integer

Calc:

' Отображение окна для ввода данных

dblSales = Val(InputBox(«Сумма реализации:», _

«Расчет комиссионного вознаграждения»))

' Формирование сообщения (с одновременным расчетом _

вознаграждения)

strMessage = «Объем продаж:» & vbTab & Format(dblSales,

«$#,##0») & _

vbCrLf & «Сумма вознаграждения:» & vbTab & _

Format(dhCalculateCom(dblSales), «$#,##0») & _

vbCrLf & vbCrLf & «Считаем дальше?»

' Вывод окна с сообщением (о рассчитанной сумме и вопросом _

о продолжении расчетов)

If MsgBox(strMessage, vbYesNo, _

«Расчет комиссионного вознаграждения») = vbYes Then

' Продолжение расчетов

GoTo Calc

End If

End Sub

В результате написания данного кода будут сформированы две пользовательские функции – dhCalculateCom и dhCalculateCom2 (они будут помещены в категорию Определенные пользователем в окне Мастер функций), а также макрос ComCalculator, доступный в окне выбора макросов. Рассмотрим порядок применения указанных функций и макроса.

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

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