顺序

bjg_22000

无论部门顺序次序，能用函数自动排列固定顺序。

良良

E3=INDEX(OFFSET(A$2,MIN(INT(ROW(A4)/4)*4-3),,4),MOD(SMALL(LOOKUP(OFFSET($A$2,MIN(INT(ROW(A4)/4)*4-3),,4),{"解放商场",4;"金丰超市",3;"南极人商超",2;"人民商城",1})/1%+ROW($1:$4),MOD(ROW(A4),4)+1),100))

leepoison

=VSTACK(A2:C2,WRAPROWS(TOCOL(DROP(REDUCE("",VSTACK(A4,A6,A5,A3),LAMBDA(x,y,HSTACK(x,HSTACK(FILTER(A3:C10,A3:A10=y))))),,1)),3))

mykitami

=VSTACK(A2:C2,DROP(GROUPBY(HSTACK(ROUNDUP(SEQUENCE(ROWS(E3:E10))/4,0),FIND(LEFT(A3:A10),"人南金解"),A3:A10),B3:C10,SUM,0,0,),,2))

bjg_22000

良良 发表于 2024-11-7 22:04
E3=INDEX(OFFSET(A$2,MIN(INT(ROW(A4)/4)*4-3),,4),MOD(SMALL(LOOKUP(OFFSET($A$2,MIN(INT(ROW(A4)/4)*4-3) ...

非常谢谢指教

bjg_22000

leepoison 发表于 2024-11-7 22:20
=VSTACK(A2:C2,WRAPROWS(TOCOL(DROP(REDUCE("",VSTACK(A4,A6,A5,A3),LAMBDA(x,y,HSTACK(x,HSTACK(FILTER(A3 ...

非常谢谢指导

bjg_22000

mykitami 发表于 2024-11-7 22:33
=VSTACK(A2:C2,DROP(GROUPBY(HSTACK(ROUNDUP(SEQUENCE(ROWS(E3:E10))/4,0),FIND(LEFT(A3:A10),"人南金解"), ...

非常谢谢指导

bjg_22000

发现将原表稍变动一下，这样用函数VLOOKUP，就简单多了。