Сумма значений на основе XLOOKUP

Вместо XLOOKUP() используйте SUM() или SUMPRODUCT(). Ниже приведен самый простой вариант, и помните, что функция SUM() всегда будет эффективной и быстрой, ей нет равных.

Если у вас есть выходные данные, где слева указано имя ресурса и уровень ресурса, а заголовки имеют форматирование mmm-e, вы можете просто использовать функцию SUM(), выполняющую логическую операцию, никаких сложных формул или функций не требуется. Его легко понять и легко отладить, кроме того, он будет очень эффективным. Просто попробуй !

В противном случае, если вы пытаетесь использовать вторую формулу динамического массива, используйте второй вариант, который я предложил.

    =SUM((TEXT($C$1:$J$1,"mmm-e")=TEXT(C$8,"mmm-e"))*
     ($A9=$A$2:$A$5)*($B9=$B$2:$B$5)*
     $C$2:$J$5)

ВАРИАНТ ВТОРОЙ:

Вместо использования нескольких итераций LAMBDA() с использованием таких функций, как REDUCE() или Custom LAMBDA(), с комбинацией другого помощника LAMBDA(), например MAP(), вы можете легко преобразовать свои данные в один динамический массив, используя MMULT():

=LET(
     _Data, Table1[#All],
     _Rname, DROP(TAKE(_Data,,1),1),
     _Rlevel, DROP(INDEX(_Data,,2),1),
     _Headers, TEXT(DROP(TAKE(_Data,1),,2),"mmm-e"),
     _Uniq, UNIQUE(_Headers,1),
     _Body, DROP(_Data,1,2),
     _Output, HSTACK(_Rname,_Rlevel,MMULT(_Body,N(_Uniq=TOCOL(_Headers)))),
     VSTACK(HSTACK(TAKE(_Data,1,2),_Uniq),_Output))

Пожалуйста, не забудьте преобразовать свой диапазон в Structured References или Tables. Есть много причин использовать эту функцию Excel. Две такие причины: это облегчает чтение формул, а также автоматически изменяет размер формулы при изменении размеров исходных данных.

Чтобы объяснить более понятно, пожалуйста, прочитайте:

• _Data Переменная относится ко всей таблице Table1[#All]
• Rname Переменная извлекает первые столбцы из источника, исключая заголовки, используя TAKE() и DROP() --> TAKE() помогает получить первый столбец, а DROP() предполагает удаление заголовков.
• _Rlevel Переменная Аналогично извлекает второй столбец из источника, как указано выше, используя ту же методологию, изменяя column_index в INDEX()
• _Headers Переменная извлекает первую строку с помощью функции TAKE(), а DROP() помогает удалить первые два столбца и, наконец, с помощью функции TEXT(), которая форматирует даты как трехбуквенное сокращение месяца, за которым следует тире/дефис и год. Обозначение форматирования для полное название месяца — mmmm, а только для Jul или Apr и т. д. это будет похоже на mmm, которое уже использовалось, e относится к году, который является универсальным.
• _Uniq Переменная относится к уникальному списку заголовков.
• _Body Переменная — это области значений, извлеченные с помощью функции DROP().
• _Output переменная, которая объединяет массивы _Rname и Rlevel с матричным умножением _Body и Binary array created between the _Uniqlist and the transposed_Headers, enclosed within HSTACK()` для возврата одного единственного результата.
• Наконец, используя VSTACK(), чтобы улучшить внешний вид, мы размещаем заголовки!.

Вы можете использовать две XLOOKUP и формулу SUM.

Если вы используете фактические даты в C9:E9 - 07.01.2024, 08.01.2024 и 09.01.2024.
Присвойте этим датам произвольный числовой формат «мммм», чтобы они отображали только название месяца.

Формула =XLOOKUP(C9,$C$1:$J$1,$C2:$J2,,1) будет искать первую ячейку, которая больше или равна значению в C9.
Формула =XLOOKUP(EOMONTH(C9,0),$C$1:$J$1,$C2:$J2,,-1) будет искать первую ячейку, которая меньше или равна последнему дню месяца.

Соедините эти две формулы вместе и добавьте сумму, чтобы получить итоговые суммы за месяц. =SUM(XLOOKUP(C$9,$C$1:$J$1,$C2:$J2,,1):XLOOKUP(EOMONTH(C$9,0),$C$1:$J$1,$C2:$J2,,-1))

В Excel для MS365 одной из возможных формул массива из одной ячейки может быть следующая:

=LET(
    table, A1:J5,
    names, TAKE(DROP(table, 1),, 1),
    levels, INDEX(DROP(table, 1),, 2),
    row_labels, names & "|" & levels,
    col_labels, EOMONTH(--DROP(TAKE(table, 1),, 2), 0),
    values, DROP(table, 1, 2),
    arr, LAMBDA(n, CHOOSE(n, row_labels, UNIQUE(SORT(col_labels,,, 1), 1))),
    results, MAP(arr({1}), arr({2}), LAMBDA(r,c, SUM(FILTER(FILTER(values, row_labels = r), col_labels = c)))),
    VSTACK(
        HSTACK(TAKE(table, 1, 2), TEXT(arr(2), "mmmyy")),
        HSTACK(names, levels, results)
    )
)

При необходимости измените ссылку на диапазон таблицы и измените формат вывода меток конца месяца (например, «мммм» вместо «мммгг»).

Альтернативно, вы можете использовать SUM((row_labels = r)*(col_labels = c)*values) вместо SUM(FILTER(FILTER(values, row_labels = r), col_labels = c)), если предпочитаете.

Кроме того, в этом конкретном сценарии row_labels можно определить как SEQUENCE(ROWS(names)) вместо names & "|" & levels. Тогда вы можете использовать SUM(FILTER(CHOOSEROWS(values, r), col_labels = c)) или SUM(CHOOSEROWS(values, r)*(col_labels = c)).

Обновлено: В простейшей форме вышеупомянутую формулу можно свести к следующему:

=LET(
    table, A1:J5,
    col_labels, EOMONTH(--DROP(TAKE(table, 1),, 2), 0),
    values, DROP(table, 1, 2),
    arr, LAMBDA(n, CHOOSE(n, SEQUENCE(ROWS(values)), UNIQUE(SORT(col_labels,,, 1), 1))),
    results, MAP(arr({1}), arr({2}), LAMBDA(r,c, SUM(FILTER(CHOOSEROWS(values, r), col_labels = c)))),
    HSTACK(TAKE(table,, 2), VSTACK(TEXT(arr(2), "mmmyy"), results))
)

Пояснение к arr({1}):

По сути, CHOOSE использовалась как пользовательская функция LAMBDA для моделирования 3D-массива. Когда объект массива передается аргументу index_num CHOOSE (например, {1} вместо 1), все аргументы-значения автоматически изменяются посредством широковещательной передачи. Аргумент-значение, содержащий один столбец данных (вертикальный вектор), транслируется для заполнения того же количества столбцов, что и аргумент с наибольшим количеством столбцов, тогда как аргумент-значение, содержащий одну строку данных (горизонтальный вектор), транслируется вниз для заполнения. то же количество строк, что и аргумент с наибольшим количеством строк. Затем функция MAP используется для последовательного перебора слоев слева направо и сверху вниз.

Примечание. В сложных сценариях этот метод может быть более эффективным, чем функция MAKEARRAY (с INDEX), которая, как известно, очень плохо работает на больших наборах данных. Однако в этом относительно простом сценарии эквивалентом MAKEARRAY (без необходимости использования INDEX col_labels) может быть:

=LET(
    table, A1:J5,
    col_labels, EOMONTH(--DROP(TAKE(table, 1),, 2), 0),
    periods, UNIQUE(SORT(col_labels,,, 1), 1),
    col_ID, XMATCH(col_labels, periods,, 2),
    values, DROP(table, 1, 2),
    results, MAKEARRAY(ROWS(values), COLUMNS(periods), LAMBDA(r,c, SUM(FILTER(CHOOSEROWS(values, r), col_ID = c)))),
    HSTACK(TAKE(table,, 2), VSTACK(TEXT(periods, "mmmyy"), results))
)

Столбцы соответствия итогов (по месяцам)

В C9:

=LET(data,A1:J5,row_labels_count,2,
    rl,TAKE(data,,row_labels_count),
    d,DROP(data,,row_labels_count),
    h,TAKE(d,1),
    v,DROP(d,1),
    m,MONTH(h),
    mu,UNIQUE(m,1),
    dh,XLOOKUP(mu,m,TEXT(UNIQUE(h,1),"mmmm")),
    dv,DROP(REDUCE("",mu,LAMBDA(rr,r,
        HSTACK(rr,BYROW(FILTER(v,m=r),LAMBDA(r,SUM(r)))))),,1),
    r,HSTACK(rl,VSTACK(dh,dv)),
    r)

В C16

=LET(lookup_data,C1:J5,months,C15:F15,if_no_month,NA(),
    h,TAKE(lookup_data,1),
    v,DROP(lookup_data,1),
    m,TEXT(h,"mmmm"),
    nm,IF(SEQUENCE(ROWS(v)),if_no_month),
    r,DROP(REDUCE("",months,LAMBDA(rr,r,
        HSTACK(rr,IFERROR(BYROW(FILTER(v,m=r),
        LAMBDA(r,SUM(r))),nm)))),,1),
    r)

• Чтобы заставить, например. Американские (английские) месяцы, замените mmmm на [$-409]mmmm.
• Если функции ETA доступны, замените LAMBDA(r,SUM(r)) на SUM.