Excel: как посчитать уникальные даты отсутствия в календарной неделе

Работа с датами в Excel часто превращается в головоломку, особенно когда нужно связать данные из разных таблиц. Представьте: у вас есть список отдельных дней отсутствия сотрудников и календарь рабочих недель с границами (понедельник-воскресенье). Задача – автоматически посчитать, сколько уникальных дат отсутствия попадает в каждую неделю, игнорируя дубликаты. Звучит сложно, но решение есть – и оно не требует макросов.

Подготовка данных: проверяем основы

Перед тем, как бросаться писать формулы, убедитесь, что:

• Все даты в листе Absence имеют формат ГГГГ-ММ-ДД (например, 2025-12-31). Если Excel воспринимает их как текст, формулы не сработают – щёлкните по ячейке и проверьте, отображается ли значение в правом верхнем углу как дата.
• В столбце Status листа Availability достаточно пустых ячеек для заполнения (если тянете формулу вниз, не забудьте про абсолютные ссылки – об этом ниже).

Кстати, если пользователь случайно ввёл дату дважды (например, 2025-01-01 в строках 5 и 10), формула должна считать это как одно отсутствие. Именно поэтому мы используем функцию UNIQUE – она убирает повторы до обработки.

Формула-помощник: разбираем по шагам

Вот вариант, который подойдёт даже новичкам:

=LET(
    _abs, UNIQUE(Absence!$D$2:$D$365),
    _FILTER, FILTER(_abs, (_abs>=C2)*(_abs<=D2), ""),
    COUNT(_FILTER)
)

Давайте «разжуём» каждую часть:

1. LET – объявляет переменные внутри формулы, чтобы не повторять одни и те же вычисления. Как будто говорите Excel: «Запомни это как _abs, а это как _FILTER».
2. UNIQUE(Absence!$D$2:$D$365) – берёт диапазон дат из листа отсутствий и удаляет дубликаты. Знак доллара ($) фиксирует диапазон (чтобы при протягивании формулы он не смещался).
3. FILTER(_abs, (_abs>=C2)*(_abs<=D2), "") – фильтрует уникальные даты, оставляя только те, которые попадают между началом недели (C2) и концом (D2). Умножение (*) здесь работает как оператор И – дата должна быть больше или равна началу И меньше или равна концу.
4. COUNT(_FILTER) – подсчитывает количество оставшихся дат. Если фильтр ничего не нашёл, COUNT вернёт 0.

Если не использовать LET, формула станет длиннее, но тоже будет работать:

=COUNT(FILTER(UNIQUE(Absence!$D$2:$D$365), (UNIQUE(Absence!$D$2:$D$365)>=C2)*(UNIQUE(Absence!$D$2:$D$365)<=D2), ""))

Совет: Если границы недели могут меняться (например, вместо воскресенья – пятница), просто укажите в столбцах C и D актуальные даты начала и конца. Формула динамически подстроится под новые значения.

Тонкости, которые сэкономят нервы

• Диапазон дат в Absence. В примере используется $D$2:$D$365 – предположение, что данные занимают не больше года. Если список длиннее, расширьте диапазон (например, $D$2:$D$1000), но не делайте его избыточным – это замедлит вычисления.
• Ошибка #ЗНАЧ! — возникает, если фильтр не нашёл подходящих дат. Чтобы вместо ошибки отображался 0, добавьте IFERROR:
  

  =IFERROR([ваша_формула], 0)

• Проверка на пустые ячейки. Если в диапазоне Absence есть пустые строки, функция UNIQUE их проигнорирует. Но если там мусор (например, текст), формула сломается – очистите данные заранее.

Пример теста: Возьмите календарную неделю с 2025-01-06 по 2025-01-12. Добавьте в Absence даты 2025-01-06, 2025-01-07 и дважды 2025-01-08. Формула должна вернуть 3 – потому что 06, 07 и 08 (дубликат не считается). Если получили 4 – проверьте, применили ли UNIQUE.

И последнее: не бойтесь экспериментировать с формулой. Например, если хотите выделять недели с отсутствиями цветом, используйте условное форматирование на основе столбца Status (но это уже тема для отдельного разговора). Главное – начать с правильно настроенного подсчёта, и всё остальное приложится.