Диапазоны дат в функциях условий

Реальные отчёты почти всегда связаны с временным интервалом: продажи за этот квартал, заказы за прошлый месяц, регистрации между двумя датами. Изученные Вами функции с условиями — SUMIFS, COUNTIFS и AVERAGEIFS — прекрасно справляются с этой задачей, если знать, как задать диапазон дат.

Хитрость в том, что диапазон дат на самом деле представляет собой два условия для одного и того же столбца дат: начиная с даты начала и не позднее даты окончания.

Электронные таблицы хранят даты как порядковые числа — день 1 — это 1 января 1900 года (или 1899 года в Google Таблицах), а каждый следующий день увеличивает число на единицу. Поэтому даты можно сравнивать с помощью > и < точно так же, как обычные числа.

Таким образом, после 1 января означает просто порядковое число, большее числа этой даты. Это ключевая идея, благодаря которой фильтрация по датам работает.

Допустим, в столбце A хранится Дата заказа, а в столбце C — Сумма. Чтобы получить общую сумму продаж за январь 2024 года, столбец A указывается дважды: начиная с 1 января и не позднее 31 января.

Заключайте даты в функцию DATE(year, month, day), чтобы они однозначно распознавались в разных региональных форматах. Два условия образуют логику AND и отбирают только строки за этот месяц.

Возможно, Вы попробуете напрямую ввести ">=1/1/2024". Это часто работает, но такой способ ненадёжен: электронная таблица может распознать значение как текст или неверно определить порядок дня и месяца.

Надёжный вариант выглядит так: ">="&DATE(2024,1,1). Функция DATE создаёт настоящее порядковое число, а & присоединяет к нему оператор. Этот способ надёжен и в Excel, и в Google Таблицах, независимо от региональных настроек.

Фиксированные даты подходят для неизменного отчёта, но гибкий отчёт считывает даты начала и окончания из ячеек. Поместите дату начала в F1, а дату окончания — в F2.

Теперь период определяется листом. Измените F1 или F2 — и все итоги пересчитаются. Как всегда, присоединяйте оператор к ячейке с помощью & — никогда не заключайте имя ячейки в кавычки.

Иногда нужна только одна граница. Всё начиная с определённой даты задаётся одним условием «больше или равно». Всё до определённой даты задаётся одним условием «меньше или равно».

Так можно подсчитать все заказы, размещённые начиная с даты в F1, без верхнего ограничения — это полезно для показателей в стиле «продажи с момента запуска».

Условия по датам свободно сочетаются с текстовыми и числовыми условиями. Чтобы получить общую сумму продаж в восточном регионе за определённый период, добавьте пару условий для региона к двум парам условий для дат.

Порядок не влияет на результат — Excel оценивает все условия как одну большую операцию AND. Здесь три пары условий используют один и тот же диапазон усреднения или диапазон суммирования.

Чтобы получить сумму за весь год, задайте границы как первый и последний день этого года. Для начала используйте DATE, а для окончания — последний день периода.

Для одного месяца используйте первое число месяца как нижнюю границу, а первое число следующего месяца со строгим условием "<" — как верхнюю границу. Это удобный способ не задумываться о том, 28, 30 или 31 день в месяце.

Для регулярно обновляемых отчётов создавайте границы с помощью TODAY(). Чтобы подсчитать заказы за последние 30 дней, нижняя граница — это сегодняшняя дата минус 30, а верхняя — сегодняшняя дата.

Поскольку TODAY() обновляется каждый день при пересчёте листа, период автоматически сдвигается вперёд — ручное редактирование не требуется.

Если в столбце дат на самом деле хранятся дата и время (например, отметка времени), строка с датой позднего времени 31 января будет иметь порядковое число немного больше целого значения этого дня. Поэтому граница "<="&DATE(2024,1,31) исключит такую строку.

Безопасное решение — использовать строгое сравнение «меньше» со следующим днём: "<"&DATE(2024,2,1) охватывает любой момент января, включая отметки времени.

Та же схема с диапазоном дат работает и с AVERAGEIFS. Чтобы найти среднюю стоимость заказа за определённый период, укажите столбец сумм как диапазон усреднения и добавьте два условия для дат в столбце дат.

Помните о проблеме пустого периода: если в заданный диапазон дат не попал ни один заказ, AVERAGEIFS возвращает #DIV/0!. Оборачивание функции в IFERROR сохраняет аккуратный вид панели мониторинга с фильтром по времени, даже если за период нет данных.

Теперь Вы умеете фильтровать SUMIFS, COUNTIFS и AVERAGEIFS по времени:

• Диапазон дат — это два условия для одного и того же столбца дат (>= начала и <= окончания).
• Создавайте даты с помощью DATE(y,m,d) и присоединяйте операторы с помощью ">="&.
• Для месяцев используйте строгую верхнюю границу, равную следующему дню ("<"&DATE(...)), чтобы учитывать отметки времени.
• Используйте TODAY() для скользящих периодов, например последних 30 дней.

На этом завершается семейство функций IFS с несколькими условиями.