Манипулирование данными в таблицах Google с помощью arrayformula и лямбда-помощников

Мне нужно решить довольно сложную ситуацию с помощью Google Таблиц. Я уже решил это очень неэлегантным способом, и мне нужна ваша помощь, чтобы понять, почему другие решения, которые я пробовал, не увенчались успехом, и, возможно, найти лучшее решение. Ссылку на лист я помещу в конце поста.

Вот сценарий:

Мои данные состоят из списка сотрудников (A2:A), столбца для центра затрат (B2:B) и столбца для каждого месяца (C2:N), содержащего процент вменения для этого сотрудника в этом месяце за этот центр затрат. Это позволяет мне распределять затраты на сотрудников по разным центрам затрат с помощью другого файла. Это означает, что один и тот же сотрудник может встречаться несколько раз, но с разными центрами затрат (например, имя сотрудника 1 в моих данных).

Исходные данные

Результат, который мне нужен, — это массив, в котором для каждой комбинации сотрудника и центра затрат должно быть 12 строк (по одной на каждый месяц) с процентом вменения. Мне нужен следующий вывод:

Желаемый результат

Чтобы получить этот результат, я использовал несколько именованных функций (да будет благословен Google за эту замечательную функцию):

Функция TUPLEGENERATION(сотрудник): она генерирует все строки для одного сотрудника для одного центра затрат. Это код, в котором ссылкой на сотрудника может быть любая ячейка в диапазоне A2:A:

=TOROW({
transpose(_MONTHS) \                                      //_MONTHS is a defined range just containing an array of months from january to december
transpose(split(rept(employee&"|";12);"|")) \             //this allows me to repeat 12 times the name of the employee, 1 time for each month
transpose(split(rept(offset(employee;0;1)&"|";12);"|")) \ //this does the same as above, but with the cost centre (its' the cell next to the employee, so I use offset)
transpose(offset(employee;0;2;1;12))})                    //this gets the 12 imputation values (next 12 columns)

выходные данные этой функции, примененные к первой строке данных, следующие: ВЫХОД а вот транспонированная версия, чтобы вам было лучше ее понять: ТРАНСПОНСИРОВАННЫЙ ВЫХОД я не включаю сюда вывод таблицы, так как он не очень важен

Функция MULTITUPLEGEN: она просто применяет BYROW к функции TUPLEGENERATION, поэтому вместо одной ячейки в качестве параметра требуется диапазон. Вот код:

=BYROW(range;TUPLEGENERATION)

вот результат, примененный к первым трем строкам моих данных

МУЛЬТИТУПЛЕГЕННЫЙ ВЫХОД

Функция MONTHGEN: здесь происходит волшебство. Он использует выходные данные MULTITUPLEGEN для извлечения данных за конкретный месяц для этого диапазона сотрудников. первый параметр — это число от 1 до 12, соответствующее месяцу, который вы хотите сгенерировать. второй параметр — это диапазон полномочий сотрудников, которые необходимо генерировать. Вот код:

=CHOOSECOLS(MULTITUPLEGEN(range);sequence(4;1;month*4-3;1))

и вот результат, примененный к первым трем строкам моих данных

ВЫХОД МЕСЯЦА

Пока что нет ничего слишком неэлегантного (да, его можно было бы оптимизировать лучше, но меня это не особо беспокоит. Я разбил код на несколько функций в серии проб и ошибок, связанных с использованием Arrayformula (я думал я мог бы обмануть движок, чтобы избежать определенных проблем), но единственное решение, которое я нашел для достижения желаемого результата, заключается в следующем:

Уродливая функция

=sort({MONTHGEN(1;attrib!A2:A4);MONTHGEN(2;attrib!A2:A4);MONTHGEN(3;attrib!A2:A4);MONTHGEN(4;attrib!A2:A4);
MONTHGEN(5;attrib!A2:A4);MONTHGEN(6;attrib!A2:A4);MONTHGEN(7;attrib!A2:A4);MONTHGEN(8;attrib!A2:A4);
MONTHGEN(9;attrib!A2:A4);MONTHGEN(10;attrib!A2:A4);MONTHGEN(11;attrib!A2:A4);MONTHGEN(12;attrib!A2:A4)})

вот результат, примененный к тем же трем строкам:

выход

Результат является желаемым, и в моей последней версии диапазон является статическим диапазоном, поэтому он вообще не отображается в формуле, но я действительно не могу смириться с тем, что мне пришлось вручную 12 раз объединять функцию с возрастающим числом. параметр, не имея возможности использовать ARRAYFORMULA или другие подобные функции.

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

Я потратил часы, пробуя разные подходы к проблеме, включая разные стартовые функции и манипулирование данными, но вот основные проблемы, с которыми я столкнулся:

1. WRAPROWS — очень полезная функция, примененная к моей функции TUPLEGENERATION, но она принимает в качестве диапазона только одну строку. Он не может накладывать несколько диапазонов поверх другого, поэтому он бесполезен с функцией MULTITUPLEGEN, и я не смог выполнить итерацию с помощью ARRAYFORMULA или BYROW.

2. VSTACK полезен, если вы вручную вставляете его в несколько выходных данных WRAPROWS(TUPLEGENERATION), но мне не удалось перебрать его с помощью BYROW или ARRAYFORMULA для генерации моего вывода.

3. В общем, при повторении определенного диапазона я обнаружил, что довольно сложно расширить его динамически, генерируя больше строк в его середине. стартовый диапазон. Я пробовал большинство вспомогательных функций LAMBDA, таких как SCAN, MAKEARRAY, LET и т. д., но основная проблема заключается в том, что большинству из них разрешено возвращать только 1 строку за исходную итерацию, и попытка обойти это с помощью транспозиции и укладки просто не дает результатов. Это не работает из-за порядка выполнения, так как транспонирование происходит в неподходящее время, возвращая неверные данные. Признаюсь, я не особо долго погружался в MAKEARRAY, поэтому не могу исключить, что просто не использовал его в полной мере.

вот лист: https://docs.google.com/spreadsheets/d/e/2PACX-1vS7Vi43KDQigiBlAR9HnTsfK9TSo7PoSPTZZuys9GaczADUSXv_qY_uCSuwy7jeq3m9JGcqDACs_ywi/pubhtml

Заранее спасибо за помощь!