Подсчёт количества дат по году и месяцу в Excel

При работе с листом Excel иногда нужно подсчитать ячейки, содержащие даты определённого года или месяца, как показано на снимке экрана ниже. Чтобы решить эту задачу в Excel, используйте функции СУММПРОИЗВ, ГОД и МЕСЯЦ для создания формулы, которая подсчитает количество дат, относящихся к указанному году или месяцу.

• Подсчёт количества дат заданного года
• Подсчёт количества дат заданного месяца
• Подсчёт количества дат по году и месяцу одновременно

Подсчёт количества дат заданного года

Чтобы подсчитать количество дат в указанном году, используйте комбинацию функций СУММПРОИЗВ и ГОД. Общий синтаксис формулы:

• date_range: список ячеек, содержащих даты, которые необходимо подсчитать;
• year: значение или ссылка на ячейку, содержащую год, за который нужно выполнить подсчёт.

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку, где должен появиться результат:

Примечание: в этой формуле A2:A14 — диапазон ячеек, содержащих даты, а C2 содержит конкретный год, за который необходимо выполнить подсчёт.

2. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам, и вы получите количество дат за указанный год (см. снимок экрана):

Объяснение формулы:

=SUMPRODUCT(--(YEAR($A$2:$A$14)=C2))

• YEAR($A$2:$A$14)=C2: функция ГОД извлекает значения года из списка дат и возвращает массив: {2020;2019;2020;2021;2020;2021;2021;2021;2019;2020;2021;2019;2021}.
  Затем каждый год сравнивается со значением в ячейке C2, и в результате получается массив логических значений ИСТИНА и ЛОЖЬ: {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.
• --(YEAR($A$2:$A$14)=C2)=--{ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}: двойной минус (--) преобразует значение ИСТИНА в 1, а ЛОЖЬ — в 0. В результате вы получите массив: {0;1;0;0;0;0;0;0;1;0;0;1;0}.
• SUMPRODUCT(--(YEAR($A$2:$A$14)=C2))= SUMPRODUCT({0,1,0,0,0,0,0,0,1,0,0,1,0}): наконец, функция СУММПРОИЗВ суммирует все элементы массива и возвращает результат — 3.

Подсчёт количества дат заданного месяца

Чтобы подсчитать количество дат, относящихся к заданному месяцу, используйте комбинацию функций СУММПРОИЗВ и МЕСЯЦ. Общий синтаксис формулы:

• date_range: список ячеек, содержащих даты, которые необходимо подсчитать;
• month: значение или ссылка на ячейку, указывающая месяц, за который нужно выполнить подсчёт.

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку, где должен отобразиться результат:

Примечание: в этой формуле A2:A14 — диапазон ячеек, содержащих даты, а C2 содержит конкретный месяц, за который необходимо выполнить подсчёт.

2. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам, и вы получите количество дат за указанный месяц (см. снимок экрана):

Объяснение формулы:

=SUMPRODUCT(--(MONTH($A$2:$A$14)=C2))

• MONTH($A$2:$A$14)=C2: функция МЕСЯЦ извлекает номера месяцев из списка дат, получая следующий массив: {12;3;8;4;8;12;5;5;10;5;7;12;5}.
  Затем каждый из этих номеров сравнивается с номером месяца в ячейке C2, и в результате формируется массив значений ИСТИНА и ЛОЖЬ: {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
• --(MONTH($A$2:$A$14)=C2)= --{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}: двойной минус (--) преобразует значение ИСТИНА в 1, а ЛОЖЬ — в 0. В результате вы получите массив: {0;0;0;0;0;0;1;1;0;1;0;0;1}.
• SUMPRODUCT(--(MONTH($A$2:$A$14)=C2))= SUMPRODUCT({0,0,0,0,0,0,1,1,0,1,0,0,1}): функция СУММПРОИЗВ суммирует все элементы массива и возвращает результат — 4.

Подсчёт количества дат по году и месяцу одновременно

Чтобы подсчитать количество дат, относящихся одновременно к определённому году и месяцу — например, к маю 2021 года.

В этом случае для получения результата можно использовать комбинацию функций СУММПРОИЗВ, МЕСЯЦ и ГОД. Общий синтаксис формулы:

• date_range: список ячеек, содержащих даты, которые необходимо подсчитать;
• month: значение или ссылка на ячейку, представляющая месяц, за который необходимо выполнить подсчёт;
• year: значение или ссылка на ячейку, представляющая год, за который необходимо выполнить подсчёт.

Введите или скопируйте приведённую ниже формулу в пустую ячейку, чтобы получить результат, а затем нажмите клавишу Enter, чтобы выполнить расчёт (см. снимок экрана):

Примечание: в приведённой выше формуле A2:A14 — диапазон ячеек, содержащих даты, D2 содержит конкретный месяц, а C2 — номер года, за который необходимо выполнить подсчёт.

Используемые связанные функции:

Другие статьи: