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

С помощью несложного трюка можно быстро вычислить требуемый день недели по отношению к заданной дате (например, когда будет первый понедельник после 27.07.2005). В этом нам поможет функция dhGetNextMonday, код которой приведен в листинге 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.

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.

Function dhBookIsSaved() As Boolean

' Если путь файла рабочей книги не задан, то она _

не сохранена (ThisWorkbook.path равняется "")

dhBookIsSaved = ThisWorkbook.path <> ""

End Function

Данная функция не имеет аргументов. Если после ее запуска в активной ячейке появится значение ИСТИНА, то текущая рабочая книга была ранее сохранена, а если ЛОЖЬ – то книга не сохранялась.

Для быстрого расчета средневзвешенного значения можно применить пользовательскую функцию, код которой приведен в листинге 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).