Рис. 2.8. Аргументы функции dhCount
Разработка и применение полезных пользовательских функций
В данном окне в поле Rgn указывается диапазон, содержимое которого нужно проанализировать, а в полях LowBound и UpperBound – границы значений искомых ячеек диапазона. Например, на рис. 2.8 указано, что необходимо найти общее количество ячеек диапазона B5:F12, значения которых находятся в пределах от 900 до 7000. Результат будет выведен в ячейке, в которой расположен курсор. Если вводить функцию с клавиатуры в строку формул либо в ячейку, то она будет выглядеть следующим образом: =dhCount (B5:F12; 900; 7000).
Подсчет количества видимых ячеек в диапазоне
Как известно, в Excel предусмотрена возможность скрытия строк и столбцов. Поэтому иногда в процессе работы может возникать вопрос: а есть ли в том или ином диапазоне скрытые ячейки? В данном подразделе мы рассмотрим прием, который позволяет быстро посчитать количество видимых ячеек в указанном диапазоне (сравнив его с общим количеством ячеек этого же диапазона, легко определить, есть ли в нем скрытые ячейки).
Для подсчета видимых непустых ячеек диапазона удобно применять пользовательскую функцию dhCountVisibleCells. Для создания данной функции нужно в стандартном модуле редактора VBA написать код, представленный в листинге 2.57.
Листинг 2.57. Подсчет количества видимых ячеек
Function dhCountVisibleCells(rgRange As Range)
Dim lngCount As Long
Dim cell As Range
' Проходим по всему диапазону и подсчитываем непустые _
видимые ячейки
For Each cell In rgRange
' Проверка, есть ли данные в ячейке
If Not IsEmpty(cell) Then
' Проверка, видима ли ячейка
If Not cell.EntireRow.Hidden And Not _
cell.EntireColumn.Hidden Then
' Еще одна видимая ячейка
lngCount = lngCount + 1
End If
End If
Next cell
dhCountVisibleCells = lngCount
End Function
Данная функция имеет один аргумент – диапазон, в котором нужно посчитать видимые ячейки. Результат расчета будет помещен в ячейку, в которой установлен курсор. Функцию можно использовать с помощью формулы, которая записывается в строке формул. Пример такой формулы (в ней может изменяться только анализируемый диапазон) следующий:
=dhCountVisibleCells(A1:Е7)
В данном случае будет подсчитано и помещено в активную ячейку количество видимых ячеек, которые находятся в диапазоне А1:Е7.
Поиск ближайшего понедельника
С помощью несложного трюка можно быстро вычислить требуемый день недели по отношению к заданной дате (например, когда будет первый понедельник после 27.07.2005). В этом нам поможет функция dhGetNextMonday, код которой приведен в листинге 2.58.
Листинг 2.58. Ближайший день недели по отношению к дате
Function dhGetNextMonday(datDate As Date) As Date
' Определение даты следующего понедельника (функция Weekday _
возвращает номер дня недели, считая от понедельника, если _
в качестве второго аргумента задавать vbMonday)
If Weekday(datDate, vbMonday) = 1 Then
' Заданная дата и есть понедельник
dhGetNextMonday = datDate
Else
' Расчет даты следующего понедельника
dhGetNextMonday = datDate + 8 – Weekday(datDate,
vbMonday)
End If
End Function
Чтобы получить дату ближайшего понедельника, например, после 27.07.2005, необходимо в окне мастера функций выбрать функцию dhGetNextMonday и в качестве значения аргумента ввести 27.07.2005.
После нажатия Enter в активной ячейке отобразится дата 01.08.2005, то есть ближайший понедельник после 27 июля 2005 года приходится на 1 августа 2005 года. Если воспользоваться строкой формул, то формула будет выглядеть так:
=dhGetNextMonday("27.07.2005")
Аналогичным образом можно вычислить даты остальных дней недели.
Если после применения формулы дата не отображается надлежащим образом (например, 3 8545 вместо 12.07.2005), то необходимо установить формат ячейки Дата.
Подсчет количества полных лет
Трюк, который мы сейчас рассмотрим, позволяет быстро посчитать количество целых лет между заданной датой и текущей. В частности, с помощью данного трюка можно определить возраст человека (с округлением до целых лет), зная дату его рождения. В этом нам поможет пользовательская функция dhCalculateAge, код которой приведен в листинге 2.59.
Листинг 2.59. Функция dhCalculateAge
Function dhCalculateAge(datDate As Date) As Long
Dim lngAge As Long
' Находим разность между текущей датой и указанной (лет)
lngAge = DateDiff("yyyy", datDate, Date)
If DateSerial(Year(datDate) + lngAge, Month(datDate), _
Day(datDate)) > Date Then
' В этом году день рождения еще не наступил
lngAge = lngAge – 1
End If
dhCalculateAge = lngAge
End Function
Если, например, в качестве заданной даты взять 18.08.1972, а сегодняшний день – 28.04.2007, то результатом выполнения данной функции будет число 34. При использовании строки формул в данном случае формула будет выглядеть так:
=dhCalculateAge("18.08.1972")
Проверка, была ли сохранена рабочая книга
В процессе работы с новой книгой может возникать вопрос: а была ли уже сохранена текущая книга? Для ответа на него существуют штатные методы (самый простой – воспользоваться командой Сохранить на панели быстрого доступа). Однако можно применить и нестандартный прием; для этого нужно создать пользовательскую функцию, код которой приведен в листинге 2.60.
Листинг 2.60. Функция dhBooklsSaved
Function dhBookIsSaved() As Boolean
' Если путь файла рабочей книги не задан, то она _
не сохранена (ThisWorkbook.path равняется "")
dhBookIsSaved = ThisWorkbook.path <> ""
End Function
Данная функция не имеет аргументов. Если после ее запуска в активной ячейке появится значение ИСТИНА, то текущая рабочая книга была ранее сохранена, а если ЛОЖЬ – то книга не сохранялась.
Расчет средневзвешенного значения
Для быстрого расчета средневзвешенного значения можно применить пользовательскую функцию, код которой приведен в листинге 2.61.
Листинг 2.61. Расчет средневзвешенного значения
Function dhAverageWithWeight(rgWeights As Range, rgValues As
Range) _
As Double
If (rgWeights.Count <> rgValues.Count) Then
' Количество весов не соответствует количеству аргументов
dhAverageWithWeight = 0
Exit Function
End If
Dim i As Integer
Dim dblSum As Double ' Сумма значений
Dim dblSumWeight As Double ' Взвешенная сумма значений
' Вычисление...
For i = 1 To rgWeights.Count
' Взвешенной суммы значений
dblSumWeight = dblSumWeight + rgWeights(i) * rgValues(i)
' Суммы значений
dblSum = dblSum + rgWeights(i)
Next
' Возвращение средневзвешенного значения
dhAverageWithWeight = dblSumWeight / dblSum
End Function
После выбора данной функции откроется окно, в котором следует заполнить поля RgWeights иRgVaLues, после чего нажать кнопку ОК. Результат отобразится в ячейке, в которой установлен курсор.
Преобразование номера месяца в его название
С помощью небольшой пользовательской функции можно быстро получить название месяца на основании его номера. Данную возможность целесообразно использовать при работе с большими объемами информации, когда нужно быстро преобразовать числовые обозначения месяцев в обычные названия. Код функции выглядит следующим образом (листинг 2.62).
Листинг 2.62. Название месяца
Function dhMonthName(intMonth As Integer) As String
' Возвращение имени месяца по его номеру (intMonth _
является номером элемента в массиве с названиями месяцев)
dhMonthName = Choose(intMonth, "Январь", "Февраль", "Март", _
"Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", _
"Октябрь", "Ноябрь", "Декабрь")
End Function
После выбора данной функции необходимо указать номер месяца – в результате соответствующее ему название отобразится в активной ячейке.