25.01.2021 08:06
Fredcapit 12 1300 Источник

Предыстория

Как известно «Лень – двигатель прогресса». В своей работе однажды я столкнулся задачей, когда нужно было составить таблицу расчёта процентов по договору займа, где за базу должно было быть фактическое количество дней в году. Неудобство составляло то, что нужно было не забыть про високосные года и разделять дни, которые относятся к високосному году и дни не високосных лет. Была написана простенькая формула, но позже я выяснил, что расчётом високосных лет не всё так просто.

Описание проблемы

Мне захотелось улучшить формулу. В интернете я нашёл много текстов программ, где вычислялось количество високосных или невисокосных лет и дней в периоде. Но, к сожалению, мен не устраивал тот факт, что скорость работы данных функций зависела от количества лет в периоде. А хотелось, чтобы независимо от того, сколько лет в периоде, функция работала также быстро. Но в ходе разработки мне пришлось ограничить допустимый период работы функции.

Причина 1

Большинство стран живут по Григорианскому календарю, правила високосных лет для которого были определены ещё 1582 году римским папой Григорием XIII:

1.       Год, номер которого кратен 400, - високосный;

2.       Остальные годы, номер которых кратен 100, - невисокосные (например, годы 1700, 1800, 1900, 2100, 2200,2300)

3.       Остальные годы, номер которых кратен 4, - високосные.

Также существует неоднозначность в мнениях определения високосных лет таких как 2900, 3200, 4000, поэтому я решил ограничить функцию максимальной датой 01.01.2900.

Причина 2

Пользовательская функция в Excel создаётся на языке VBA (Visual Basic for Applications). Несмотря на то, что интерпретатор данного языка встроен в MS Office, я обнаружил некоторые отличия в работе с датами.

Excel поддерживает две системы дат, так называемые системы 1900 и 1904. По умолчанию используется система 1900. Это означает, что число 1 введённое в ячейку соответствует 01 января 1900 года, 2 – 2 января и так далее.

В VBA есть функция CDate(expression), которая приводит к типу Date введённое значение. И если этой функции передать число 1, то она вернёт переменную типа Date с датой 31 декабря 1899 года. А вот для числа 60 функция CDate вернёт 28.02.1900, а то же значение введённое в ячейку будет отображать 29.02.1900 (хотя, конечно, 1900 год високосным не является). Далее, начиная с 01 марта 1900 года значения дат выравниваются.

Такое поведение Excel, давно известно компании Microsoft и было принято решение оставить как есть, нужно просто учитывать его. Поэтому и появилось второе ограничение минимальной даты периода 01 марта 1900 года.

Алгоритм решения

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

Так как все делители, с помощью которых мы можем определить високосность года кратны 4, то я решил разбить все годы на блоки по 4 (квартеты) начиная с 1 года. То есть 1-й блок начинается с 1 года и заканчивается 4, 2-й блок с 5 по 8 и так далее.

В каждом блоке год будет иметь свой индекс от 1 до 4 (например, 2021 год это 506-й блок, индекс в блоке 1)

Теперь мы можем разделить вычисление на 3 блока:

Високосных^{всего}_{дней} = В^{от начальной даты}_{дней до конца квартета} + В^{в промеж. квартетах}_{дней} + В^{с начала последнего квартера}_{до дня окончания}

В зависимости от года параметров и индексов квартетов формула расчёта количества дней високосного года будет следующей:

Если год начальной и конечной даты равны и год високосный:

В^{от начальной даты}_{до конца квартета}=Дата_{кон} - Дата_{нач}

Если год начальной даты невисокосный, а конечной високосный и индексы квартета начальной даты и конечной равны, то:

В^{от начальной даты}_{до конца квартета} = Дата_{кон} - 31 дек (Год_{дата кон} -1)

Если год начальной даты високосный, а конечной нет, то:

В^{от начальной даты}_{до конца квартета} = 31 дек Года_{дата нач}- Дата_{нач}

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

Выше указанная логика расчёта количества дней високосных лет для первого квартета реализована следующей функцией на VBA:

Функция високосных дней для первого квартета
Private Function first_quartet_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long

    Dim result As Long
    result = 0
    
    Dim year_diff As Long
    Dim quartet_index_diff As Long
    
    year_diff = year(d_end) - year(d_begin)
    quartet_index_diff = quartet_index(year(d_end)) - quartet_index(year(d_begin))
    
    If year_diff = 0 And is_year_leap(d_begin) Then
        result = DateDiff("d", d_begin, d_end)
        first_quartet_leap_year_days = result
        Exit Function
    End If
    
    If quartet_index_diff = 0 Then
    
        If is_year_leap(d_begin) Then
            result = DateDiff("d", d_begin, CDate(DateSerial(year(d_begin), 12, 31)))
            first_quartet_leap_year_days = result
            Exit Function
        
        End If
        
        If is_year_leap(d_end) Then
            result = DateDiff("d", CDate(DateSerial(year(d_end) - 1, 12, 31)), d_end)
            first_quartet_leap_year_days = result
            Exit Function
        End If
        
    Else
    
        If is_year_leap(d_begin) Then
            result = DateDiff("d", d_begin, CDate(DateSerial(year(d_begin), 12, 31)))
            first_quartet_leap_year_days = result
            Exit Function
        Else
        
            If Not is_quartet_noleap(quartet_index(year(d_begin))) Then
                result = 366
                first_quartet_leap_year_days = result
                Exit Function
            End If
            
        End If
        
    End If

    first_quartet_leap_year_days = result
    
End Function

Если разница индексов квартетов начальной и конечной даты >0, то рассчитывается 3-й блок формулы "Количество високосных дней в последнем квартете".

Здесь формула только одна, где при условии, что год конечной даты високосный:

В^{с начала квартета}_{до даты окончания} = Дата_{кон} - 31 дек (Год_{даты окончания}-1)
Функция високосных дней для последнего квартета
Private Function last_quartet_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long
    
    Dim result As Long
    result = 0
     
    Dim quartet_index_diff As Long
       
    quartet_index_diff = quartet_index(year(d_end)) - quartet_index(year(d_begin))
    
    If quartet_index_diff > 0 Then
    
        If is_year_leap(d_end) Then
            result = DateDiff("d", CDate(DateSerial(year(d_end) - 1, 12, 31)), d_end)
        End If
        
    End If
    
     
    last_quartet_leap_year_days = result
    
End Function

Если разница индексов квартетов начальной и конечной даты >1, то рассчитывается 2-й блок формулы "Количество високосных дней в промежуточных квартетах".

В^{в промеж. квартетах}_{дней} = 366*K_{квартетов} - К_{полных 100 летий} + K_{полных 400 летий}

При этом К полных столетий – означает разность индексов столетий между датами. Например 1999 – индекс столетия 19, а 2001 – 20, таким образом разность столетий 1.

Аналогично и 400-летий.

Функция для расчёта високосных дней в промежуточных квартетах
Private Function middle_quartets_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long
    
    Dim quartet_count As Long
    
    quartet_count = middle_quartets_count(d_begin, d_end)
    
    If quartet_count = 0 Then
    
        middle_quartets_leap_year_days = 0
        Exit Function
        
    End If
    
    Dim q_begin, q_end As Long
    
    q_begin = quartet_index(year(d_begin))
    q_end = quartet_index(year(d_end)) - 1
    
    Dim quot_25, quot_100 As Integer
    
    quot_25 = WorksheetFunction.Quotient(q_end, 25) - WorksheetFunction.Quotient(q_begin, 25)
    quot_100 = WorksheetFunction.Quotient(q_end, 100) - WorksheetFunction.Quotient(q_begin, 100)
    
    Dim result As Long
    
    result = (quartet_count - quot_25 + quot_100) * 366
    
    middle_quartets_leap_year_days = result
        
End Function

Реализация функций

Функция вычисления високосных дней для периода:

Public Function LEAP_DAYS(ByVal val_begin As Long, ByVal val_end As Long, Optional count_first_day = 0, Optional count_last_day = 1) As Long
    
    Dim d_begin, d_end As Date
    
    count_first_day = IIf(count_first_day <> 0, 1, 0)
    count_last_day = IIf(count_last_day <> 0, 1, 0)
    
    
    d_begin = CDate(val_begin)
    d_end = CDate(val_end)
    
    Dim check_error As Variant
    check_error = check_constrains(d_begin, d_end)
    
    If IsError(check_error) Then
        LEAP_DAYS = check_error
        Exit Function
    End If
    
    Dim result As Long
    result = 0
    
    If is_year_leap(d_begin) And count_first_day = 1 Then result = result + 1
    If is_year_leap(d_end) And count_last_day = 0 Then result = result - 1
    
    result = result + first_quartet_leap_year_days(d_begin, d_end) _
            + middle_quartets_leap_year_days(d_begin, d_end) _
            + last_quartet_leap_year_days(d_begin, d_end)
    
    LEAP_DAYS = result
    
End Function

В приведённом выше коде мы сначала приводим значения параметров count_first_day и count_last_day к значению 1 или 0. Затем мы объявляем переменные типа Date для даты начала и окончания периода и задаём значения. Далее следует проверка параметров на ограничения.

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

Далее идёт сложение результатов промежуточных функций, которые были выше описаны.

Заключение

Таким образом мы получили формулы расчёта количества дней високосных и невисокосных лет в заданном периоде, скорость которой не зависит от количества дней в периоде. Единственный минус это то, что формула способна корректно работать только в рамках одной тысячи лет (2900 - 1900). Думаю, что до 2900 года у нас есть ещё время усовершенствовать такую функцию.

Ниже ссылка на гитхаб, где выложена полная реализация функций подсчёта високосных и невисокосных дней в периоде на VBA предназначенная для работы в Excel. Вы легко сможете портировать эту функцию на Ваш любимый язык и пользоваться в своих проектах.

Гитхаб

Источники и дополнительные ссылки

  1. Статья из журнала "Главная книга" "Считаем проценты по займу: день первый, день последний"

  2. Excel неправильно предполагает, что 1900 год является високосным годом.

  3. Статья из Википедии "Григорианский календарь"