Сценарий: у меня есть две именованные таблицы в Excel с общим столбцом "дата":
- "Ежемесячный лист [дата]"
- "РабочаяСменаВсе[дата]"
давайте взглянем на пример набора данных:
Я пытаюсь динамически индексировать все записи из WorkShiftAll[date]
, которые находятся между двумя датами, определенными в двух ячейках с разными именами: PayPeriodStartDate
и PayPeriodEndDate
в MonthlySheet[date]
, используя эту формулу (Shift+Ctrl+Enter):
=IF(ROWS(C$10:C10)>(SUMPRODUCT((WorkShiftAll[date]>=PayPeriodStartDate)*(WorkShiftAll[date]<=PayPeriodEndDate))),"",INDEX(workShiftsDateRange,SMALL(IF((WorkShiftAll[date]>=PayPeriodStartDate)*(WorkShiftAll[date]<=PayPeriodEndDate),ROW(WorkShiftAll[date])-ROW(WorkShiftAll[date])+1),ROWS(C$10:C10))))
где C10
— первое пустое место в таблице MonthlySheet
. И (SUMPRODUCT((WorkShiftAll[date]>=PayPeriodStartDate)*(WorkShiftAll[date]<=PayPeriodEndDate)))
— это формула, которую я использовал для подсчета количества записей, соответствующих моим критериям (находящихся между двумя датами).
ПРОБЛЕМА: Две записи успешно перечислены для октября, поскольку они записаны в наборе данных, однако МАЛЕНЬКАЯ (первая запись) повторяется дважды! не пятница 26 октября и 29 октября.
Когда я использую диапазон вместо столбца для адресации ИНДЕКС, это работает!! например, если я использую:
INDEX(**WorkShifts!A2:A14**,SMALL(IF((WorkShiftAll[date]>=PayPeriodStartDate)
... Работает и появляются две записи, 26 и 29 октября. Но мне нужно, чтобы мой диапазон был динамическим, поэтому я хочу использовать таблицу. Должен ли я преобразовать его в диапазон? а другого выхода нет?