Подсчитать количество дат по году, месяцу в Excel
При работе с листом Excel иногда вам может потребоваться подсчитать ячейки, в которых указаны даты за определенный год или месяц, как показано ниже. Чтобы решить эту задачу в Excel, вы можете использовать функции СУММПРОИЗВ, ГОД и МЕСЯЦ для создания формул для подсчета количества дат, принадлежащих определенному году или месяцу, как вам нужно.
- Подсчитать количество дат данного года
- Подсчитать количество дат данного месяца
- Подсчитайте количество дат как по году, так и по месяцу
Подсчитать количество дат данного года
Чтобы подсчитать количество дат в данном году, вы можете объединить функции СУММПРОИЗВ и ГОД вместе, общий синтаксис:
- date_range: Список ячеек содержит даты, которые вы хотите подсчитать;
- year: Значение или ссылка на ячейку, представляющие год, за который нужно вести подсчет.
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:
Внимание: В этой формуле 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.
Подсчитать количество дат данного месяца
Если вы хотите подсчитать количество дат на основе данного месяца, функции СУММПРОИЗВ и МЕСЯЦ могут оказать вам услугу, общий синтаксис:
- date_range: Список ячеек содержит даты, которые вы хотите подсчитать;
- month: Значение или ссылка на ячейку, представляющие месяц, в котором вы хотите вести подсчет.
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:
Внимание: В этой формуле 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 года.
В этом случае вы можете использовать комбинацию функций СУММПРОИЗВ, МЕСЯЦ и ГОД для получения результата, общий синтаксис:
- date_range: Список ячеек содержит даты, которые вы хотите подсчитать;
- month: Значение или ссылка на ячейку, представляющие месяц, в котором вы хотите вести подсчет;
- year: Значение или ссылка на ячейку, представляющие год, за который нужно вести подсчет.
Введите или скопируйте приведенную ниже формулу в пустую ячейку, чтобы вывести результат, а затем нажмите Enter ключ, чтобы получить расчет, см. снимок экрана:
Внимание: В приведенной выше формуле A2: A14 диапазон ячеек содержит даты, D2 содержат конкретный месяц и C2 - это номер года, который вы хотите рассчитать.
Используемая относительная функция:
- SUMPRODUCT:
- Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
- МЕСЯЦ:
- Функция МЕСЯЦ в Excel извлекает месяц из даты и отображает целое число от 1 до 12.
- ГОД:
- Функция ГОД возвращает год на основе заданной даты в формате 4-значного серийного номера.
Другие статьи:
- Подсчитать количество ячеек между двумя значениями / датами
- Вы когда-нибудь пытались получить или подсчитать количество ячеек между двумя заданными числами или датами в Excel, как показано на скриншоте ниже? В этой статье мы расскажем о некоторых полезных формулах для решения этой проблемы.
- Подсчитать количество ячеек даты в диапазоне по дням недели
- Excel предоставляет комбинацию функций СУММПРОИЗВ и ДЕНЬ НЕДЕЛИ, чтобы помочь нам легко подсчитать количество указанных дней недели в пределах диапазона. Это руководство представляет собой пошаговое руководство, которое поможет вам разобраться в этом.
- Подсчитать количество ячеек, содержащих определенный текст
- Предположим, у вас есть список текстовых строк, и вы можете захотеть найти количество ячеек, которые содержат определенный текст как часть своего содержимого. В этом случае вы можете использовать подстановочные знаки (*), которые представляют любые тексты или символы в ваших критериях при применении функции СЧЁТЕСЛИ. В этой статье я расскажу, как использовать формулы для решения этой задачи в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
