求销售订单的产品组合

H20D

如图求出所有订单的商品组合，用透视要很麻烦，函数的话想了很久都没想到好的办法，请各位大神出手

Dean.shi

=IFERROR(INDEX(OFFSET($A$1,MATCH(E2,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12,E2)),1),"")&IFERROR("/"&INDEX(OFFSET($A$1,MATCH(E2,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12,E2)),2),"")&IFERROR("/"&INDEX(OFFSET($A$1,MATCH(E2,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12,E2)),3),"")&IFERROR("/"&INDEX(OFFSET($A$1,MATCH(E2,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12,E2)),4),"")

libo5563

透视表做不了，power pivot 就简单了，透视表比函数简单多了。

gykg-excel

=TEXTJOIN("/",,IF($A$2:$A$12=F2,$B$2:$B$12,""))

gykg-excel

普通版本=MID(SUBSTITUTE(PHONETIC(OFFSET($A$1:$B$1,MATCH(F2,$A$2:$A$12,),,COUNTIF($A$2:$A$12,F2))),F2,"/"),2,99)

yhe1989

=SUBSTITUTE(CONCAT(IF(A2:A12=F2,B2:B12&"/",))&" ","/ ","")

=TEXTJOIN("/",1,IF(A$2:A$12=F2,B$2:B$12,))
数组函数 按三键

liyanghu

假设相同的订单号排到一起倒是可以
=MID(SUBSTITUTE(PHONETIC(OFFSET(B$1,MATCH(F2,A:A,)-1,,COUNTIF(A:A,F2),-2)),F2,"/"),2,4^8)