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

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

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

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

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

• диапазон_дат: Список ячеек, содержащих даты, которые нужно подсчитать;
• год: Значение или ссылка на ячейку, представляющую год, который нужно подсчитать.

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

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

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

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

=СУММПРОИЗВ(--(ГОД($A$2:$A$14)=C2))

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

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

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

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

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

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

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

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

=СУММПРОИЗВ(--(МЕСЯЦ($A$2:$A$14)=C2))

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

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

Для подсчета количества дат на основе года и месяца, например, если вы хотите узнать, сколько дат приходится на май 2021 года.

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

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

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

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

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

Больше статей: