Представьте, что вам нужно пронумеровать недели в таблице, но с условием: если между периодами возникает перерыв в шесть недель, счёт должен начинаться заново. Звучит как головоломка? На самом деле всё решается комбинацией функций – правда, придётся немного повозиться с логикой. Давайте разберёмся, как это сделать без ручного подсчёта.
Подготовка данных и основные формулы
Сначала создадим «скелет» таблицы. В диапазоне B1:I1 генерируем даты каждые 7 дней, начиная с 25 января 2025:
=SEQUENCE(,8,DATE(2025,1,25),7)Этот шаблон можно расширить до 53 столбцов (для всего года) – просто замените 8 на 53 и скорректируйте стартовую дату.
В столбце A вручную вводятся диапазоны дат в форматах вроде 2/2/25-16/2/25. Чтобы превратить их в читаемые даты, используйте:
=DATEVALUE(TEXTSPLIT(CLEAN(A2),"-",","))Кстати, функция CLEAN здесь удаляет невидимые символы (например, переносы строк), а TEXTSPLIT разбивает текст по разделителям «-» и «,».
Для преобразования в начала и концы недель (суббота–пятница) подойдёт модифицированная формула:
=LET(
AllDates, DATEVALUE(TEXTSPLIT(CLEAN(A2),"-",",")),
StartDates, CHOOSECOLS(AllDates,1)+1-WEEKDAY(CHOOSECOLS(AllDates,1)+1,1),
EndDates, CHOOSECOLS(AllDates,2)+1-WEEKDAY(CHOOSECOLS(AllDates,2)+1,1)+6,
HSTACK(StartDates,EndDates)
)Здесь WEEKDAY(...,1) задаёт воскресенье как первый день недели (параметр «1»), а CHOOSECOLS извлекает начальную и конечную даты из диапазона.
Создание пользовательской функции LAMBDA
Основная сложность – автоматическая нумерация с перезапуском. Для этого потребуется функция NumberWeeks, которую нужно сохранить в Advanced Formula Environment (доступно в Excel 365 и новее).
NumberWeeks = LAMBDA(raw_text, header,
LET(
max_gap, 6,
dates, DATEVALUE(TEXTSPLIT(CLEAN(raw_text), "-", ",")),
week_starts, MAP(dates, LAMBDA(d, XLOOKUP(d, header, header, , -1))),
flag_weeks, DROP(
REDUCE(
0,
header,
LAMBDA(a, b,
LET(
position_of_latest_value, XMATCH(TRUE,a<>0,0,-1),
latest_value, IFERROR(INDEX(a,1,position_of_latest_value),TAKE(a,,-1)),
restart, IFERROR(AND(COLUMNS(a)>=max_gap,position_of_latest_value<(COLUMNS(a)+1-max_gap)),FALSE),
increment, MAX(
BYROW(week_starts, LAMBDA(r, IF(AND(b >= INDEX(r, 1, 1), b <= INDEX(r, 1, 2)), 1, 0))
),
HSTACK(a,IF(increment=1,IF(restart,1,latest_value + increment),0))
)
)
),
,
1
),
IF(flag_weeks=0,"",flag_weeks)
)
)Как это работает:
max_gap– параметр для задания допустимого разрыва (6 недель);week_startsсопоставляет даты из столбца A с заголовками таблицы;REDUCEпроходит по каждому заголовку и решает: продолжить счёт или начать заново.
Совет: Если в вашем регионе используется формат дат мм/дд/гггг (как в США), проверьте, чтобы ввод в столбце A соответствовал этому. Иначе DATEVALUE может выдать ошибку.
Настройка и возможные ошибки
После создания функции применяйте её в ячейке B2 формулой:
=NumberWeeks(A2, $B$1#)И растягивайте вниз. Если вместо чисел появляются пробелы – проверьте:
- Нет ли опечаток в формате дат (например, «25/1/25» вместо «1/25/25» при американской локале);
- Пересекаются ли диапазоны из столбца A с заголовками недель;
- Не превышает ли разрыв между активными неделями 6 (параметр
max_gap).
Иногда проблема возникает из-за неявных пробелов в данных. Например, если в A2 записано 02/03/2025-16 Mar 25, функция CLEAN удалит лишнее, но смешанные форматы (числа и текст) лучше избегать.
Пример типичной ошибки: формула возвращает #VALUE! при использовании дат вроде «16 Mar 25». Замените на «16/03/25» или настройте DATEVALUE через SUBSTITUTE:
=DATEVALUE(SUBSTITUTE(TEXTSPLIT(...),"Mar","03"))Кстати, если вам нужно изменить порог перезапуска (например, на 4 недели), просто отредактируйте переменную max_gap в коде LAMBDA. А если столбцы с датами добавляются динамически – убедитесь, что ссылка $B$1# охватывает весь диапазон (символ «#» здесь обозначает динамический массив).
Теперь ваша таблица будет автоматически нумеровать недели, аккуратно перезапуская счёт при длительных перерывах. Главное преимущество этого подхода – гибкость: вы можете менять и исходные данные, и правила нумерации без переписывания формул с нуля.