Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 12
В VBA тип каждой переменной нужно указывать отдельно, поэтому:
• либо d_begin — это Variant (её можно и не объявлять в таком случае),
• либо «Dim d_begin As Date, d_end As Date».
Я бы здесь обошёлся без VBA по многим причинам, задача вполне решается и без этого.
Ниже пример как можно посчитать количество високосных дней в периоде, включая даты начала и окончания.
Допустим в ячейках A1 и A2 — даты начала и окончания периода соответственно.
Разбиваем расчёт на две части:
1) считаем високосные годы в полных годах интервала (если они есть);
2) считаем високосные годы на границах интервала, даты в пределах разных лет.
3) считаем високосный год, когда даты в пределах одного года.
1. Високосные годы в полных годах интервала (если они есть).
Условие расчёта: =ГОД(A2)-ГОД(A1)>1
Расчёт количества високосных лет: =ДАТА(ГОД(A2)-1;12;31)-ДАТА(ГОД(A1)+1;1;1)+1-(ГОД(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)
Ниже пример как можно посчитать количество високосных дней в периоде, включая даты начала и окончания.
Допустим в ячейках 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 на более простой по логике, расчёта високосных лет в промежутке между годом начала и годом конца периода (не включая эти годы), которую Вы описали.
Реализация логики в формуле 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 не использовал.
Описанный мною пример тоже возвращает количество выскокосных дней в периоде.
Насчёт п. 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) пользователей, также ничего не поймут в этой длинной формуле.
Ваша формула, указанная в конечном результате, видимо, не полная и, как мне кажется, возвращает только количество лет, а не дней.
Про «читаемость». Я понимаю, что многие не разбираются в коде 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)
Теперь должно быть всё правильно, извиняюсь, делал быстро.
Или я вообще неправильно понял задачу?
«Посчитать количество високосных дней» я понял так: посчитать сколько раз встречается дата 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)
Я имел ввиду, сколько дней календарных в периоде приходится на високосные года.
Теперь я понял, что вы высчитывали в п.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)
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Функция количества високосных дней в периоде