Как применять формулы к значениям динамических массивов?
Я не лучший пользователь Excel в мире, поэтому извините, если это простой вопрос.
Я пытаюсь полуавтоматизировать расширение данных, полученных от поставщика, для ввода в программу приема. У меня есть решение, которое в основном работает при обработке данных для одной позиции, но я изо всех сил пытаюсь масштабировать формулы для учета длины списка переменных и размеров динамического массива.
Данные, которые я получаю, будут примерно в таком формате:
Где каждая строка будет содержать информацию об SKU, а поле «TALLY» будет содержать переменную длину подсчитываемых позиций (т. е. строка 1 содержит 1 штуку длиной 10 футов и 1 штуку длиной 11 футов). Формат, в котором я пытаюсь манипулировать данными, примерно такой (для краткости включаю только одну строку):
Столбцы RO, ROID, CONT и RP будут статическими полями. Столбец ITEM будет повторяться для каждой подсчитанной длины + 1 для накопительного столбца. Полученное_количество является продуктом каждого подсчета.
Я считаю, что для повторения информации о продукте можно использовать формулу типа =LEN(F2)-LEN(SUBSTITUTE(F2,"/","")) для анализа столбца TALLY, а затем использовать вспомогательные столбцы для повторения. Я могу использовать формулы LEFT, RIGHT, and FIND для анализа количества штук и длины, а также использовать математические вычисления и конкаты для элементов. Мне удобно применять это к листу с одной позицией, но не адаптировать формулы для масштабирования дополнительных позиций, которые могут иметь от 1 до 30 значений в столбце TALLY.
Ответы 1
Предполагая, что у вас есть Microsoft 365, введите следующее там, где вы хотите получить результат (с достаточным количеством пустых строк ниже). В показанном примере для параметра данных используется динамический диапазон (B6#).
data: обычный или динамический.
ЛАМБДА(данные,
Для обработки строк получите индексы массива
row_indices, ПОСЛЕДОВАТЕЛЬНОСТЬ (СТРОКИ (данные)),
Постройте результат в result, используя REDUCE, складывая построенные строки для каждой строки.
результат, УМЕНЬШИТЬ(
Установите заголовок в качестве начального значения.
TEXTSPLIT("Received_qty,RO,ROID,ITEM,CONT,RP", ",")
Для каждой строки доступ к строке по индексу
row_indices, LAMBDA(acc_res, cur_row,
используя ИНДЕКС соберите столбцы в переменных
LET(ro, INDEX(data, cur_row, 1),
roid, INDEX(data, cur_row, 2),
item, INDEX(data, cur_row, 3),
cont, INDEX(data, cur_row, 4),
rp, INDEX(данные, cur_row, 5),
tally, INDEX(data, cur_row, 6), получить длину количества, разделив столбец подсчета qty_lengths, TEXTSPLIT(TRIM(tally), ",")] В
result_0 постройте строки для текущей строки, используя эти длины (qty_lengths), снова используя REDUCE.
результат_0, УМЕНЬШИТЬ(
для каждого значения в qty_lengths (например 1/10)
qty_lengths, LAMBDA(acc, cur,
получить количество и длину путем разделения
LET(qty_len, TEXTSPLIT(cur, "/"), qty, INDEX(qty_len, , 1), len, INDEX(qty_len, , 2)
постройте ряд в соответствии с вашими потребностями
row_, HSTACK(количество * len, ro, roid, CONCAT(item, ".", len), "" & cont, rp)
сложите построенные ряды
VSTACK(согл., row_)
после обработки подсчета текущей строки добавьте накопительную строку
cumulative_row, HSTACK(SUM(INDEX(result_0, , 1)),ro, roid, item, CONCAT(cont, " ", 1), rp) накапливайте результаты, складывая строки, удаляя начальную «накопительную строку», поскольку она нам не нужна. результат, VSTACK(cumulative_row, DROP(result_0, 1))
=LAMBDA(data,
LET(
row_indices, SEQUENCE(ROWS(data)),
result, REDUCE(
TEXTSPLIT("Received_qty,RO,ROID,ITEM,CONT,RP", ","),
row_indices,
LAMBDA(acc_res, cur_row,
LET(
ro, INDEX(data, cur_row, 1),
roid, INDEX(data, cur_row, 2),
item, INDEX(data, cur_row, 3),
cont, INDEX(data, cur_row, 4),
rp, INDEX(data, cur_row, 5),
tally, INDEX(data, cur_row, 6),
qty_lengths, TEXTSPLIT(TRIM(tally), ","),
result_0, REDUCE(
"cumulative_row",
qty_lengths,
LAMBDA(acc, cur,
LET(
qty_len, TEXTSPLIT(cur, "/"),
qty, INDEX(qty_len, , 1),
len, INDEX(qty_len, , 2),
row_, HSTACK(
qty * len,
ro,
roid,
CONCAT(item, ".", len),
"" & cont,
rp
),
VSTACK(acc, row_)
)
)
),
cumulative_row, HSTACK(
SUM(INDEX(result_0, , 1)),
ro,
roid,
item,
CONCAT(cont, " ", 1),
rp
),
result, VSTACK(
cumulative_row,
DROP(result_0, 1)
),
VSTACK(acc_res, result)
)
)
),
result
)
)(B6#)
Добавлены некоторые пояснения, а также упрощено указание данных в качестве параметра. После тестирования вы можете сделать лямбду именованной функцией, сделав ее доступной на всех листах книги. Здесь нет необходимости в VBA. Пожалуйста, дайте мне знать, если какая-либо часть нуждается в дополнительных разъяснениях :)
Привет! Большое спасибо за предложение. Это работает очень хорошо, но у меня возникла пара вопросов. 1. Не могли бы вы дать объяснение этой формулы? Я думаю, что понимаю большую часть этого. 2. Можно ли передать динамический диапазон в эту формулу, не преобразовывая ее в функцию или подпроцедуру VBA? :)