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

For Each Ячейка In Диапазон

' Анализ только видимых ячеек

If Not Ячейка.EntireRow.Hidden And Not _

Ячейка.EntireColumn.Hidden Then

' При расчете учитываются только ячейки _

с численными значениями

If IsNumeric(Ячейка) = True Then

СуммаВид = СуммаВид + Ячейка

End If

End If

Next

End Function

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

Удобный способ суммирования диапазона ячеек реализован в функции Сумма. Она отличается от стандартной функции Excel СУММ тем, что при суммировании курсор может находиться внутри этого же диапазона, но при этом циклическая ошибка не возникнет и соответственно сообщение о ней выводиться не будет. Следует учитывать, что ячейка с функцией в расчетах не участвует. Код функции Сумма выглядит следующим образом (листинг 2.51).

Function Сумма(Диапазон, АдресЯчейки) As Double

' Просмотр всех ячеек диапазона

For Each Ячейка In Диапазон

' Проверка, чтобы в суммировании не участвовала _

ячейка с формулой

If АдресЯчейки.Address <> Ячейка.Address Then

' В суммировании участвуют только ячейки _

с численными значениями

If IsNumeric(Ячейка) = True Then

Сумма = Сумма + Ячейка

End If

End If

Next

End Function

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

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

Допустим, что организация принимает вклады на следующих условиях: на вклады размером до 4999 руб. начисляется 9 %, на вклады размером от 5000 до 9999 руб. – 11 %, а на вклады размером свыше 10 000 руб. – 15 % (все данные условны). Чтобы быстро рассчитать проценты по вкладам в данном случае, можно воспользоваться функцией dhCalculatePercent, код которой приведен в листинге 2.52.

Function dhCalculatePercent(lngSum As Long) As Double

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

Const dblRate1 As Double = 0.09

Const dblRate2 As Double = 0.11

Const dblRate3 As Double = 0.15

' Граничные суммы вкладов (декларация констант)

Const intSum1 As Long = 5000

Const intSum2 As Long = 10000

' Возвращаем сумму, умноженную на соответствующую ставку

If lngSum < intSum1 Then

dhCalculatePercent = lngSum * dblRate1

ElseIf lngSum < intSum2 Then

dhCalculatePercent = lngSum * dblRate2

Else

dhCalculatePercent = lngSum * dblRate3

End If

End Function

Эту же функцию можно записать и в таком виде (листинг 2.53).

Function dhCalculatePercent(lngSum As Long) As Double

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

Const dblRate1 As Double = 0.09

Const dblRate2 As Double = 0.11

Const dblRate3 As Double = 0.15

' Граничные суммы вкладов (декларация констант)

Const intSum1 As Long = 5000

Const intSum2 As Long = 10000

' Возвращаем сумму, умноженную на соответствующую ставку

Select Case lngSum

Case Is < intSum1

dhCalculatePercent = lngSum * dblRate1

Case Is < intSum2

dhCalculatePercent = lngSum * dblRate2

Case Else

dhCalculatePercent = lngSum * dblRate3

End Select

End Function

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

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

Выше мы рассмотрели один из наиболее простых способов расчета процентов в зависимости от суммы вклада (выручки и т. п.). Рассмотрим другой вариант. Предположим, что штатным сотрудникам организации, принимающей вклады, проценты по вкладам начисляются по приведенному в предыдущем подразделе алгоритму. Сторонним же вкладчикам выплачивается 110 % от начисленной суммы. Для решения данной задачи функцию dhCalculatePercent следует написать в таком виде (листинг 2.54).