低版本Excel的多条件查询&拼接

Excel公式问题推荐社区：Excel函数与公式 - ExcelHome技术论坛

【问题】

根据A、B列信息，将C列汇总出拼接结果，即D列中的黄色标记结果。

 “——这种问题方案只有五个，一是升级版本用textjoin，二是VBA（包括自定义函数），三是加辅助列标记分隔符再用phonetic或者用本例的方法，四是power query（2010版可以下载这个插件），五是手动。”

【限制】

1、Office低版本无法使用TEXTJOIN函数（2019版本支持）；

2、EXCEL嵌入开发系统，不能使用VBA和插件；

3、需要自动统计结果，不能手动处理。

【解决】

1、辅助列

通过前台隐藏、文字改色的方式处理。

（1）辅助列函数

原解答函数=IF(AND(A2=A3,B2=B3),C2&","&D3,C2)

后自动化处理为=IF(AND(OFFSET($A$1,ROW()-1,,1,1)=OFFSET($A$1,ROW(),,1,1),OFFSET($B$1,ROW()-1,,1,1)=OFFSET($B$1,ROW(),,1,1)),OFFSET($C$1,ROW()-1,,1,1)&","&OFFSET($D$1,ROW(),,1,1),OFFSET($C$1,ROW()-1,,1,1))

（2）查询最终结果函数

原解答函数=INDEX($D:$D,MATCH(1,($A:$A=具体产品代码)*($B:$B=具体币种种类),))

2、最终方案

思路：通过OFFSET函数匹配条件，获取B列C列方形区域，拼接；再将B列内容替换为逗号；最后将第一个逗号去除

举例：统计“1001”+“1-募集资金币种”。首先获取B2:C4区域，使用PHONETIC拼接内容后，再将“1-募集资金币种”替换为逗号，最后将第一个逗号去除。

=IF(代码单元格="","",SUBSTITUTE((SUBSTITUTE(PHONETIC(OFFSET($B$1,MATCH(代码单元格&"1-募集资金币种",$A:$A&$B:$B,0)-1,,LOOKUP(1,0/(($A:$A=代码单元格)*($B:$B="1-募集资金币种")),ROW($B:$B))-MATCH(代码单元格&"1-募集资金币种",$A:$A&$B:$B,0)+1,2)),"1-募集资金币种",",")),",","",1))

【改进】

（1）用MAX函数替代LOOKUP函数，查找最后一行行号

LOOKUP(1,0/(($A:$A=代码单元格)*($B:$B="1-募集资金币种")),ROW($B:$B))

>>>  MAX(($A:$A=代码单元格)*($B:$B="1-募集资金币种")*ROW($B:$B))

注意：LOOKUP使用的是二分法，数据量高时前台会卡。INDEX(MATCH())函数效率高于LOOKUP函数。

（2）将“代码单元格”定位查找，使其可以自动查询。以“代码单元格”=B列为例：

=OFFSET($B$1,ROW()-1,)

或者=ADDRESS(ROW(),2)