[Excel]按照合约匹配交易所

数据源：

某软件导出的合约明细

规则：

国内期货合约对应的交易场所
SHF = {'AG', 'AL', 'AU', 'BU', 'CU', 'FU', 'HC', 'NI', 'PB', 'RB', 'RU', 'SN', 'WR', 'ZN'};  
DCE = {'A', 'C', 'I', 'J', 'JD', 'JM', 'L', 'M', 'P', 'Y'};
CZC = {'CF', 'FG', 'MA', 'SR', 'TA', 'ZC'};
CFE = {'IF', 'IH', 'IC'};

如上图，A列存储了合约简称，需要在C列判断交易所

本来打算在vba中进行判断，后来从数组公式中得到启发，改写如下：

=IF(SUM(({"AG","AL","AU","BU","CU","FU","HC","NI","PB","RB","RU","SN","WR","ZN"}=LEFT(A3,FIND(" ",A3)-3))*1),"SHF"

,IF(SUM(({"A","C","I","J","JD","JM","L","M","P","Y"}=LEFT(A3,FIND(" ",A3)-3))*1),"DCE",

IF(SUM(({"CF","FG","MA","SR","TA","ZC"}=LEFT(A3,FIND(" ",A3)-3))*1),"CZC",

IF(SUM(({"IF","IH","IC"}=LEFT(A3,FIND(" ",A3)-3))*1),"CFE",""
))))

利用判断的true or false 转化为0,1相加然后判断匹配。

其中 LEFT(A3,FIND(" ",A3)-3)),  是根据简称的特点，空格前，去掉月份的两个数字。

后发现白糖SAX特殊,上面简称的获取需要再截取一次Left( LEFT(A3,FIND(" ",A3)-3)), 2)

套入公式中发现，存在嵌套超过7层，解决方法，用辅助列，或者改为min方法

=IF(SUM(({"AG","AL","AU","BU","CU","FU","HC","NI","PB","RB","RU","SN","WR","ZN"}=LEFT(A3,min(FIND(" ",A3)-3,2)))*1),"SHF"
,IF(SUM(({"A","C","I","J","JD","JM","L","M","P","Y"}=LEFT(A3,min(FIND(" ",A3)-3,2)))*1),"DCE"
,IF(SUM(({"CF","FG","MA","SR","TA","ZC"}=LEFT(A3,min(FIND(" ",A3)-3,2)))*1),"CZC"
,IF(SUM(({"IF","IH","IC"}=LEFT(A3,min(FIND(" ",A3)-3,2)))*1),"CFE",""
))))