Note: The other languages of the website are Google-translated. Back to English
English English

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

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


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

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

=SUMPRODUCT(--(YEAR(date_range)=year))
  • date_range: Список ячеек содержит даты, которые вы хотите подсчитать;
  • year: Значение или ссылка на ячейку, представляющие год, за который нужно вести подсчет.

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

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

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

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


Пояснение к формуле:

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

  • ГОД (2 доллара: 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}.
  • SUMPRODUCT(--(YEAR($A$2:$A$14)=C2))= SUMPRODUCT({0;1;0;0;0;0;0;0;1;0;0;1;0}): Наконец, эта функция СУММПРОИЗВ суммирует все элементы в массиве и возвращает результат: 3.

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

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

=SUMPRODUCT(--(MONTH(date_range)=month))
  • date_range: Список ячеек содержит даты, которые вы хотите подсчитать;
  • month: Значение или ссылка на ячейку, представляющие месяц, в котором вы хотите вести подсчет.

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

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

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

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


Пояснение к формуле:

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

  • МЕСЯЦ (2 доллара США: 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}.
  • SUMPRODUCT(--(MONTH($A$2:$A$14)=C2))= SUMPRODUCT({0;0;0;0;0;0;1;1;0;1;0;0;1}): Эта функция СУММПРОИЗВ суммирует все элементы в массиве и возвращает результат: 4.

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

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

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

=SUMPRODUCT((MONTH(date_range)=month)*(YEAR(date_range)=year))
  • date_range: Список ячеек содержит даты, которые вы хотите подсчитать;
  • month: Значение или ссылка на ячейку, представляющие месяц, в котором вы хотите вести подсчет;
  • year: Значение или ссылка на ячейку, представляющие год, за который нужно вести подсчет.

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

=SUMPRODUCT((MONTH($A$2:$A$14)=D2)*(YEAR($A$2:$A$14)=C2))

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


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

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

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

  • Подсчитать количество ячеек даты в диапазоне по дням недели
  • Excel предоставляет комбинацию функций СУММПРОИЗВ и ДЕНЬ НЕДЕЛИ, чтобы помочь нам легко подсчитать количество указанных дней недели в пределах диапазона. Это руководство представляет собой пошаговое руководство, которое поможет вам разобраться в этом.
  • Подсчитать количество ячеек, содержащих определенный текст
  • Предположим, у вас есть список текстовых строк, и вы можете захотеть найти количество ячеек, которые содержат определенный текст как часть своего содержимого. В этом случае вы можете использовать подстановочные знаки (*), которые представляют любые тексты или символы в ваших критериях при применении функции СЧЁТЕСЛИ. В этой статье я расскажу, как использовать формулы для решения этой задачи в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места