При создании таблиц и различных отчетов возникает необходимость посчитать количество значений между двумя датами. Разберемся, как же это можно сделать при помощи формулы Excel.
Исходные данные
Возьмем в качестве примера такие данные.
Что будут отражать даты в нашем случае не важно. Это могут быть даты рождения, продажи по дням или что другое наша задача посчитать количество значений между двумя датами, суть значений при этом не учитываем.
Для нашей задачи будем использовать две функции Excel, это «СЧЕТЕСЛИМН» и «ДАТА». Хотя использование функции «ДАТА» зависит от того что мы будем считать. Так, например, для подсчета количества элементов за год или полугодие, использовать функцию «ДАТА» нам необходимо. Если же мы считаем количество элементов за квартал или месяц можно обойтись ссылками на значения в ячейках. Можно делать и так и так, всё зависит от представления таблицы.
Считаем за год
Введем в ячейку F3 формулу =СЧЁТЕСЛИМН($C$3:$C$18;”>=”&ДАТА(E3;1;1);$C$3:$C$18;”<=«&ДАТА(E3;12;31))
Что же представляет из себя формула:
Отличие функции «СЧЕТЕСЛИМН» от «СЧЕТЕСЛИ» заключается в возможности указания нескольких условий выборки, именно поэтому мы можем ее использовать для подсчета элементов в диапазоне.
$C$3:$C$18 — задает диапазон в котором будет производится подсчет, в результатах мы хотим посчитать значения за 2016 и 2017 года. Соответственно нам придется вводить формулу и в ячейку F4, а самый простой способ это сделать — скопировать ее из ячейки F3. Но если мы копируем формулу ниже «умный Excel» сдвинет и ссылку на диапазон ячеек, для того что бы при копировании ссылка на исходные данные не изменилась, мы и используем абсолютные ссылки.
После указания диапазона, указывается логическое условие. В нашей формуле используется строковое определение условия с добавлением к нему результата функции «ДАТА». Начало диапазона задается как “>=”&ДАТА(E3;1;1), конец диапазона как “<=«&ДАТА(E3;12;31). Знак «&» в теле формулы используется для соединения двух строковых значений, условия и результата функции «ДАТА».
Для определения двух дат мы используем функцию «ДАТА», первое значение функции ссылается на ячейку с данными, в нашем случае ячейки содержат года «2016» и «2017». Второй параметр функции определяет порядковый номер месяца, третий число месяца. В результате выполнения функций логические условия будут читаться следующим образом, например для 2016 года: >=01.01.2016 — больше или равно 1 января 2016
<=31.12.2016 — меньше или равно 31 декабря 2016
Таким образом подсчитываться будут только те даты, которые попадают в диапазон условий, в текущем примере в целый год.
Считаем за месяц
Для подсчета значений в месяц мы будем ссылаться не только на год, но и на месяц, за который мы подсчитываем данные. В целом формула для подсчета значений за месяц похожа на подсчет за год. Разница только в диапазоне. Если начальную дату месяца определить легко, сославшись в функции «ДАТА» на год и месяц, а день начала месяца всегда 1. То последний день месяца зависит от самого месяца, это может быть и 28, и 29, 30 и 31 день. Поэтому для определения последнего дня проще всего использовать функцию «КОНМЕСЯЦА» указав параметром первый день месяца и значение 0 для второго параметра.
Таким образом, наша формула выглядит как:
=СЧЁТЕСЛИМН($C$3:$C$18;”>=”&ДАТА(E7;F7;1);$C$3:$C$18;”<=«&КОНМЕСЯЦА(ДАТА(E7;F7;1);0))
Любопытное замечание, не все языки программирования имеют в своем распоряжении стандартные функции для определения последнего дня месяца. Однако большинство языков программирования легко поддерживают операции прибавления и вычитания дат. И одним из приемов получения последнего дня месяца, является вычитание одного дня из первого дня следующего месяца. Excel же в своей работе часто прибегает к упрощениям и коррекции ошибок, по этому вместо функции «КОНМЕСЯЦА» мы могли бы написать ДАТА(E7;F7+1;1)-1. Где здесь коррекция ошибки? Да в том, что если Excel будет вычислять функцию с такими параметрами: ДАТА(2016;12+1;1)-1 мы получим в результате «31.12.2016», а не ошибку, хотя 13 месяца и не существует.
