求助查找并返回多行值

qyt1005

solextrade 发表于 2023-1-4 16:41

感谢感谢，我看返回值只有每个项目的第一笔流水，好像有点问题，用2楼的办法解决了

hbzql

项目名称=LOOKUP(,0/FREQUENCY(ROW(A1),ROW($1:$9)*4),$A$3:$A$11)
交易时间=LOOKUP(,0/FREQUENCY(ROW(A1),ROW($1:$9)*4),$B$3:$B$11)
员工姓名=OFFSET($F$2,,MATCH(COUNTIFS($F$16:F16,F16,$G$16:G16,G16),COUNTIF(OFFSET($F$2,MATCH(F16,$F$3:$F$6,0),1,1,ROW($1:$11)),">0"),0))
占比=SUM((F16=$F$3:$F$6)*(H16=$G$2:$Q$2)*$G$3:$Q$6)
交易金额=LOOKUP(,0/FREQUENCY(ROW(A1),ROW($1:$9)*4),$C$3:$C$11)*I16

vamiii

附件，表2中筛选占比，把0去掉即可

一杯绿茶来了

=LET(_a,DROP(REDUCE("",A3:A11,LAMBDA(x,y,VSTACK(x,LET(s,COUNT(FILTER(G3:Q6,F3:F6=y)),IFNA(EXPAND(OFFSET(y,,,,2),s),OFFSET(y,,,,2)))))),1),_b,DROP(REDUCE("",A3:A11,LAMBDA(j,k,VSTACK(j,LET(q,TOCOL(IF(FILTER(G3:Q6,F3:F6=k)>0,G2:Q2,\),3),w,TOCOL(IF(FILTER(G3:Q6,F3:F6=k)>0,FILTER(G3:Q6,F3:F6=k),\),3),e,OFFSET(k,,2)*w,HSTACK(q,w,e))))),1),VSTACK(HSTACK("项目名称","交易金额","员工姓名","占比","交易金额"),HSTACK(_a,_b)))
365可用

bjzzxhzl

来凑个热闹，用365函数硬堆。

1. =LET(f,LAMBDA(x,TOCOL(IF(G3:Q6>0,x,a),3)),t,HSTACK(f(F3:F6),f(G2:Q2),f(G3:Q6)),REDUCE(HSTACK(A2:B2,"员工姓名","占比",C2),A3:A11,LAMBDA(x,y,VSTACK(x, LET(s,FILTER(t,TAKE(t,,1)=y),p,OFFSET(y,,,,2),IFNA(HSTACK(p,DROP(s,,1),OFFSET(y,,2)*TAKE(s,,-1)),p))))))

复制代码