Как стать автором
Обновить

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

Время на прочтение 6 мин
Количество просмотров 6.4K
Всего голосов 4: ↑3 и ↓1 +2
Комментарии 12

Комментарии 12

В VBA тип каждой переменной нужно указывать отдельно, поэтому:
• либо d_begin — это Variant (её можно и не объявлять в таком случае),
• либо «Dim d_begin As Date, d_end As Date».

Согласен. Но в данном случае переменной d_begin присваивается значение даты и она становится с типом Date
Я бы здесь обошёлся без VBA по многим причинам, задача вполне решается и без этого.
Ниже пример как можно посчитать количество високосных дней в периоде, включая даты начала и окончания.
Допустим в ячейках A1 и A2 — даты начала и окончания периода соответственно.

Разбиваем расчёт на две части:
1) считаем високосные годы в полных годах интервала (если они есть);
2) считаем високосные годы на границах интервала, даты в пределах разных лет.
3) считаем високосный год, когда даты в пределах одного года.

1. Високосные годы в полных годах интервала (если они есть).
Условие расчёта: =ГОД(A2)-ГОД(A1)>1
Расчёт количества високосных лет: =ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1)+1;1;1)+1-(ГОД(A2)-ГОД(A1)-1)*365, где
  • ДАТА(ГОД(A1)+1;1;1) и ДАТА(ГОД(A2)-1;12;31) — интервал, содержащий полные годы
  • (ГОД(A2)-ГОД(A1)-1)*365 — количество дней в интервале, пересчитанное без високосных лет

Принцип расчёта: посчитать в интервале количество дней с учётом високосных и вычесть количество дней без учёта високосных.
Результат: =ЕСЛИ(ГОД(A2)-ГОД(A1)>1; ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1)+1;1;1)+1-(ГОД(A2)-ГОД(A1)-1)*365;0)

2. Високосные годы на границах интервала, даты в пределах разных лет.
Первый год — високосный: =МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2
Дата начала захватывает 29 февраля: =A1<=ДАТА(ГОД(A1);2;29)
Второй год — високосный: =МЕСЯЦ(ДАТА(ГОД(A2);2;29))=2
Дата окончания захватывает 29 февраля: =A2<=ДАТА(ГОД(A2);2;29)
Условие для границы интервала слева: =И(МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2;A1<=ДАТА(ГОД(A1);2;29))
Условие для границы интервала справа: И(МЕСЯЦ(ДАТА(ГОД(A2);2;29))=2;A2>=ДАТА(ГОД(A2);2;29))
Результат для интервала слева: =ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2;A1<=ДАТА(ГОД(A1);2;29));1;0)
Результат для интервала справа: =ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A2);2;29))=2;A2>=ДАТА(ГОД(A2);2;29));1;0)
Результат:
=ЕСЛИ(ГОД(A2)<>ГОД(A1); ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2;A1<=ДАТА(ГОД(A1);2;29));1;0)+ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A2);2;29))=2;A2>=ДАТА(ГОД(A2);2;29));1;0);0)


3. Даты в пределах одного года.
Условие: =ГОД(A2)=ГОД(A1)
Год — високосный: =МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2
29 февраля находится в интервале: =И(A1<=ДАТА(ГОД(A1);2;29);A2>=ДАТА(ГОД(A2);2;29))
Результат: =ЕСЛИ(И(ГОД(A2)=ГОД(A1); МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2; И(A1<=ДАТА(ГОД(A1);2;29);A2>=ДАТА(ГОД(A2);2;29)));1;0)

Конечный результат:
=ЕСЛИ(ГОД(A2)-ГОД(A1)>1; ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1)+1;1;1)+1-(ГОД(A2)-ГОД(A1)-1)*365;0)+ЕСЛИ(ГОД(A2)<>ГОД(A1); ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2;A1<=ДАТА(ГОД(A1);2;29));1;0)+ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A2);2;29))=2;A2>=ДАТА(ГОД(A2);2;29));1;0);0)+ЕСЛИ(И(ГОД(A2)=ГОД(A1); МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2; И(A1<=ДАТА(ГОД(A1);2;29);A2>=ДАТА(ГОД(A2);2;29)));1;0)
Спасибо за развёрнутый комментарий. Действительно, многое можно упростить.
На Гитхабе я обновил код VBA на более простой по логике, расчёта високосных лет в промежутке между годом начала и годом конца периода (не включая эти годы), которую Вы описали.
Реализация логики в формуле Excel мне не нравится, так как делает формулу нечитаемой, поэтому я решил реализовать её как пользовательскую функцию на VBA.
В пунктах 2 и 3 я не совсем понял, что Вы рассчитываете.
Моя функция возвращает именно количество дней, а не лет.
Напротив, VBA является «нечитаемой» для подавляющего большинства пользователей. Более того, нужно ещё уметь внедрять код в книги/шаблоны. Также не следует забывать о макросах и политике безопасности (в некоторых компаниях использование макросов и вовсе может быть запрещено). VBA действительно очень полезна (и даже нужна) в некоторых случаях. В статье поднят вопрос скорости работы как приоритетный. Не думаю, что здесь важна скорость. Разве что если нужно часто использовать расчёт на больших таблицах с периодами.
Постом просто описал другое решение и свою точку зрения. Т.е. именно в этом случае я бы VBA не использовал.

Описанный мною пример тоже возвращает количество выскокосных дней в периоде.
Насчёт п. 2 и п. 3, на примере периода 15.02.18-15.04.21.
П. 1 считает высокосные дни в периоде 01.01.19-31.12.20.
П. 2 считает высокосные дни в периоде с 15.02.18-31.12.18 и 01.01.21-15.04.21.
П. 3 считает высокосные дни в случае, когда период находится в пределах одного года, например, 15.02.18-15.04.18.
И все это объединяется в одной формуле. Которая, кстати, не такая уж и запутанная, ожидал что будет сложнее. По скорости работы не проверял. Но, повторюсь, именно здесь выбор между формулой и VBA по критерию «скорость работы» сомнителен.
По поводу скорости работы как приоритет я имел ввиду, то, что в других источниках я видел, как аналогичный показатель рассчитывается с помощью итераций (т.е. каждый год в промежутке проверялся на високосность), а мне захотелось это немного оптимизировать (вдруг кому захочется считать много таких формул, разность в скорости тогда может быть очень заметной).
Про «читаемость». Я понимаю, что многие не разбираются в коде VBA, но именно в формуле гораздо удобнее использовать имя формулы с двумя параметрами, чем такую длинную формулу, смысл которой можно забыть, а также сложно проследить целостность. Также могу из личного опыта сказать, что подавляющее большинство (может 99 из 100) пользователей, также ничего не поймут в этой длинной формуле.
Ваша формула, указанная в конечном результате, видимо, не полная и, как мне кажется, возвращает только количество лет, а не дней.
Ваша формула, указанная в конечном результате, видимо, не полная и, как мне кажется, возвращает только количество лет, а не дней.

Потому что в формуле увидели слово «ГОД», но не нашли «РАСЧЕТ_ВЫСОКОСНЫХ_ДНЕЙ»? :)

Ваше решение не оспариваю ни в коем случае.
Раньше понятие «высокосный день» нигде не встречал, поэтому, возможно, я неверно понял задачу.
«Посчитать количество високосных дней» я понял так: посчитать сколько раз встречается дата 29 февраля в интервале.
В периоде 01.01.20-01.01.21 это 29.02.20, 29.02.04, 29.02.08, 29.02.2012 29.02.16. Т.е. 5 дней, а не 6, действительно, есть ошибка:
п. 1 вместо =ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1)+1;1;1)+1-(ГОД(A2)-ГОД(A1)-1)*365 правильно =ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1);1;1)-(ГОД(A2)-ГОД(A1)-1)*365 (добавлен лишний день).

Конечный результат:
=ЕСЛИ(ГОД(A2)-ГОД(A1)>1; ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1);1;1)+1-(ГОД(A2)-ГОД(A1)-1)*365;0)+ЕСЛИ(ГОД(A2)<>ГОД(A1); ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2;A1<=ДАТА(ГОД(A1);2;29));1;0)+ЕСЛИ(И(МЕСЯЦ(ДАТА(ГОД(A2);2;29))=2;A2>=ДАТА(ГОД(A2);2;29));1;0);0)+ЕСЛИ(И(ГОД(A2)=ГОД(A1); МЕСЯЦ(ДАТА(ГОД(A1);2;29))=2; И(A1<=ДАТА(ГОД(A1);2;29);A2>=ДАТА(ГОД(A2);2;29)));1;0)
Теперь должно быть всё правильно, извиняюсь, делал быстро.

Или я вообще неправильно понял задачу?
Видимо, неправильно меня поняли.
Я имел ввиду, сколько дней календарных в периоде приходится на високосные года.
Теперь я понял, что вы высчитывали в п.2.и 3 первого Вашего комментария.
Эта функция нужна мне была для расчёта процентов по займам, где за базу берётся фактическое количество дней в году. То есть если мы выдали займ 01.12.2020, а вернём 01.12.2021, то мы должны проценты следующим образом:
1. Сумма процентов 2020 = СуммаЗайма * %*(30 дней/366)
2. Сумма процентов 2021 = СуммаЗайма*%*(335 дней/365)
3. Сумма процентов = Сумма процентов 2020 + Сумма процентов 2021
Просто хотелось это упаковать в одну формулу:
=СуммаЗайма * Процент*(LEAP_DAYS(ДатаНачала, ДатаОкончания)/366+NON_LEAP_DAYS(ДатаНачала, ДатаОкончания)/365)

Ух, каюсь, виноват. Стоило бы сразу подумать о предметной области.
Но в любом случае тогда формула будет ещё проще! Если есть желание, поиграйтесь, логика та же :)
Тему ИМХО стоило бы переименовать в "Формула подсчёта дней в периоде, которые выпадают на высокосный год".


Удачи! :)

ЗЫ. И подойдёт ли здесь такое решение в принципе, нужно подумать. Тогда на VBA лучше уж сразу результат по процентам считать.

Зарегистрируйтесь на Хабре , чтобы оставить комментарий

Публикации

Истории