В данном окне в поле Sales указывается адрес ячейки, на основании которой требуется рассчитать сумму процентов, а в поле IsTemporaL определяется, штатному сотруднику или нет начисляются проценты. Если проценты начисляются штатному сотруднику, то в данном поле следует ввести значение False, а если стороннему вкладчику – следует ввести True (в данном случае проценты будут начислены в размере ПО % от причитающейся суммы).
Рис. 2.7. Аргументы функции dhCalculatePercent
Сводный пример расчета комиссионного вознаграждения
Рассмотрим трюк, который включает в себя простой расчет комиссионного вознаграждения внештатным сотрудникам, расчет вознаграждения штатным сотрудникам с учетом выслуги лет, а также возможность быстро рассчитывать комиссионное вознаграждение в диалоговом режиме.
Предположим, что внештатные сотрудники получают комиссионное вознаграждение в зависимости от объема продаж по следующей шкале:
до 4999 руб. – 9 %;
от 5000 до 9999 руб. -11 %;
свыше 10 000 руб.– 15 %.
При расчете сумм комиссионного вознаграждения штатным сотрудникам учитывается стаж их работы в компании: за каждый отработанный год к сумме причитающегося вознаграждения добавляется 1 %.
Для решения поставленной задачи напишем код, представленный в листинге 2.55 (этот код следует поместить в стандартный модуль редактора VBA).
Листинг 2.55. Расчет комиссионного вознаграждения
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 имеет два аргумента: объем реализации и количество отработанных сотрудником лет. При выборе функции указывается адрес ячейки с объемом реализации по требуемому сотруднику и адрес ячейки, в которой отображается количество отработанных лет. Результат расчета будет помещен в ячейку, в которой установлен курсор.
Для удобства работы рекомендуется привязать макрос ComCalculator к кнопке и поместить ее в какое-либо место интерфейса. После вызова макроса откроется диалоговое окно Расчет комиссионного вознаграждения (название окна и его элементов можно корректировать путем внесения соответствующих изменений в код макроса), гдев поле Сумма реализации следует с клавиатуры ввести сумму, от которой требуется рассчитать объем комиссионного вознаграждения, и нажать кнопку ОК либо клавишу Enter. Результат расчета отобразится в открывшемся информационном окне – в нем будет показана сумма реализации (введенная пользователем) и сумма причитающегося комиссионного вознаграждения. В этом же окне будет сформирован запрос о продолжении расчета либо в выходе из данного режима. При положительном ответе вновь отобразится окно Расчет комиссионного вознаграждения, в котором нужно будет ввести сумму реализации, и т. д.
В данном примере макрос ComCalculator рассчитывает комиссионное вознаграждение без учета выслуги лет сотрудников компании. Для учета выслуги лет следует внести соответствующие изменения в код макроса.
Подсчет количества ячеек, содержащих указанные значения
В процессе работы иногда возникает необходимость быстро узнать, какое количество ячеек с указанным значением содержится в том или ином диапазоне. Это бывает нужно, например, чтобы узнать, сколько раз торговая выручка превышала определенную сумму либо находилась в пределах некоторого интервала сумм. Решить эту задачу поможет функция, код которой приведен в листинге 2.56.
Листинг 2.56. Количество ячеек с определенным значением
Function dhCount(rgn As Range, LowBound As Double, _
UpperBound As Double) As Long
Dim cell As Range
Dim lngCount As Long
' Проходим по всем ячейкам диапазона rgn и подсчитываем значения, _
попадающие в интервал от LowBound до UpperBound
For Each cell In rgn
If cell.Value >= LowBound And cell.Value <= UpperBound
Then
' Значение попадает в заданный интервал
lngCount = lngCount + 1
End If
Next
dhCount = lngCount
End Function
Данная функция содержит три аргумента. Если выбрать ее в окне Мастер функций, то откроется окно, показанное на рис. 2.8.