分布匹配

天涯论剑

要求：怎么根据G列按照A,B自动匹配出H列的效果。
      A有4个。所以G列显示1的要转换成4个A，后续有多余的1就转换成最后一个D

hugeinfo

=INDEX(DROP(REDUCE("",$B$1:$B$4,LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(y),OFFSET(y,,-1),"")))),1),XLOOKUP(COUNTIF($G$1:G1,G1),ROW(INDIRECT("1:"&SUM($B$1:$B$4))),ROW(INDIRECT("1:"&SUM($B$1:$B$4))),,-1))

无关岁月

1. =XLOOKUP(BYROW(IF({1,0},SUM(B1:B4),SCAN(,G1:G21,SUM)),MIN),SCAN(,B1:B4,SUM),A1:A4,,1)

复制代码

solextrade

=LOOKUP(,0/FREQUENCY(SUM(G$1:G1),SUMIF(OFFSET(B$1,,,ROW($1:$3)),">0")),A$1:A$4)

沈默00

=LET(a,DROP(REDUCE("",B1:B4,LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(y)>0,OFFSET(y,,-1))))),1),VSTACK(a,REGEXP(SEQUENCE(ROWS(G1:G21)-ROWS(a)),"\d+",2,"D")))

zwzc

=DROP(TOCOL(TEXTSPLIT(REDUCE("",B1:B4,LAMBDA(x,y,CONCAT(x,REPT(OFFSET(y,,-1)&",",IF(ROW(y)<4,y,y-SUM($B$1:y,-$G$1:$G$21)))))),","),3),-1)

一剑惊心

=EXPAND(TEXTSPLIT(MID(TEXTJOIN(,,REPT(" "&A1:A4,B1:B4)),2,9999),," "),SUM(G:G),1,A4)

hcm19522

{=INDEX(A:A,SMALL(IF(B$1:B$9>=COLUMN(A:Z),ROW($1:$9),99),ROW(A1)))&""