У меня есть два диапазона в Excel. Я ищу более простой способ объединить каждую строку Range1 с каждой строкой Range2. Количество столбцов и строк в каждом диапазоне может меняться. См. изображение ниже
Можно ли это сделать только с помощью макроса или есть способ сделать это в формулах обычного или динамического массива.
Или вы предлагаете динамический массив UDF
Вы можете попробовать:
=INDEX(
(A1:B2,D1:F3),
LET(x,SEQUENCE(6),IF(SEQUENCE(1,5)<3,1+FLOOR((x-1)/3,1),1+MOD(x-1,3))),
{1,2,1,2,3},
{1,1,2,2,2}
)
Последовательность строк LET равна
который осуществляет вложение.
Затем повторяются параметры column и area.
Вы можете сделать:
=LET( repliRange, D1:F3,
byRange, A1:B2,
rpR, ROWS( repliRange ),
rpC, COLUMNS( repliRange ),
byC, COLUMNS( byRange ),
rIdx, SEQUENCE( rpR * ROWS( byRange ), 0 ),
cIdx, SEQUENCE( 1, rpC + byC, 0 ),
mux, INDEX( repliRange, MOD( rIdx, rpR ) + 1, SEQUENCE( 1, rpC, 0 )+1 ),
noVBA, IF( cIdx < byC, INDEX( byRange, rIdx/rpR+1, cIdx + 1),
INDEX( mux, MOD(rIdx,rpC)+1, cIdx-1) ),
noVBA )
Требуется Excel 365.
Где D1:F3 — правый массив (repliRange), который необходимо перекрестно соединить с левой стороной byRange A1:B2. Это займет практически любое количество строк и столбцов с обеих сторон. Возможно, другой порядок переменных был бы более логичным, но я предположил, что логика предложения будет следующей: "реплицировать D1:F3 по A1:B2".
Версия без LET
=IF( SEQUENCE( 1, COLUMNS( D1:F3 ) + COLUMNS( A1:B2 ), 0 ) < COLUMNS( A1:B2 ),
INDEX( A1:B2,
SEQUENCE( ROWS( D1:F3 ) * ROWS( A1:B2 ), 0 )/ROWS( D1:F3 )+1,
SEQUENCE( 1, COLUMNS( D1:F3 ) + COLUMNS( A1:B2 ), 0 ) + 1),
INDEX( INDEX( D1:F3,
MOD( SEQUENCE( ROWS( D1:F3 ) * ROWS( A1:B2 ), 0 ), ROWS( D1:F3 ) ) + 1,
SEQUENCE( 1, COLUMNS( D1:F3 ), 0 )+1 ),
MOD(SEQUENCE( ROWS( D1:F3 ) * ROWS( A1:B2 ), 0 ),COLUMNS( D1:F3 ))+1,
SEQUENCE( 1, COLUMNS( D1:F3 ) + COLUMNS( A1:B2 ), 0 )-1) )
Спасибо ... К сожалению, мой Excel 365 еще не получил обновление, касающееся функции «Позволить».
@TheKing Quel dommage. Это действительно полезно, а иногда даже необходимо. Если это чем-то поможет, я опубликую версию без LET, но было бы безумно сложно обновить ее с вашими данными.
В LET-версии ваш MOD во втором диапазоне индексов NoVBA: MOD(rIdx,byC)+1 делится на количество столбцов byRange (byC) вместо количества столбцов repliRange (rpC) . MOD(rIdx,rpC)+1 вернет желаемый результат разлива.
Привет @P.b - Хороший улов! Как вы можете видеть на скриншоте, изначально их было 4, что было остатком моего тестирования и отладки. Опубликовал с остатком и забыл - потом увидел свою ошибку и "исправил" с... другой ошибкой. Я исправлю это сейчас с вашим предложением - вы попали в точку. Ваше здоровье!
Спасибо за решение. Я думаю, что лучше всего использовать VBA и написать UDF :)
Конечно, @TheKing - там, где VBA может работать, он предлагает множество возможностей. LET (и, в конечном итоге, LAMBDA) расчистит путь для решений, отличных от VBA. Они не обязательно проще, но VBA довольно часто является неприемлемым методом. Этот путь будет, но он не будет широким. VBA (или Python, JS и т. д.) станет конечной опорой. Спасибо за отзыв и вопрос. Это было хорошо.
vba будет лучшим. Используйте два вложенных цикла, которые перебирают строки и создают выходные данные.