合并单元格结构的表格查询

天边2021

如图。如何用函数实现合并单元格结构下的数据查询，请大神帮忙看看

tchh666

1. =SUMIF(B!$3:$3,$D$2,OFFSET(B!$3:$3,MATCH($B4,B!$B:$B,)+MATCH(C$3,B!$C$4:$C$6,)-4,))

复制代码

solextrade

=OFFSET(B!$C$1,MATCH($B4,B!$B:$B,)-2+COLUMN(A1),$D$2)

yfan17

1. =SUMPRODUCT((LOOKUP(ROW($4:$21),ROW($4:$21)/(B!$B$4:$B$21<>""),B!$B$4:$B$21)=$B4)*(B!$C$4:$C$21=C$3)*(OFFSET(B!$C$4:$C$21,,$D$2)))

复制代码

jivy1212

1. =HLOOKUP($D$2,B!$3:$21,MATCH(B4,B!B:B,)-{2,1,0},)

复制代码

区域数组下拉

ericxzhou

=TRANSPOSE(OFFSET(B!$C$3,MATCH(A!B4,B!$B$4:$B$21,),MATCH(A!$D$2,B!$D$3:$AH$3,),3))

jisijie630

=INDEX(B!$D:$AH,MATCH($B4,B!$B:$B,)+COLUMN(A1)-1,$D$2)