|
现有A,B,L,M列数据我设计了一个公式对AB列进行提取,并且提取得到的数据去L,M列进行比对。我所设计的公式如下:C列=IFERROR(VLOOKUP(B2,$O$3:$P$24,2,0)," ")
D列=IF(B2-B3=0," ",C2)
E列=IFERROR(INDEX(A:A,SMALL(IF($D$2:$D$9000<>" ",ROW($D$2:$D$9000)," "),ROW(A1)))," ")
F列=IFERROR(INDEX(C:C,SMALL(IF($D$2:$D$8000<>" ",ROW($D$2:$D$8000)," "),ROW(B1)))," ")
G列=IFERROR(INDEX($W:$W,MATCH(E2&F2,$L$2:$L$9000&$M$2:$M$9000,0)),E2&F2)
H列=IFERROR(INDEX(G:G,SMALL(IF($G$2:$G$9000<>" ",ROW($G$2:$G$9000)," "),ROW(G1)))," ")
I列=IFERROR(INDEX($W:$W,MATCH(L2&M2,$E$2:$E$9000&$F$2:$F$9000,0)),L2&M2)
J列=IFERROR(INDEX(I:I,SMALL(IF($I$2:$I$9000<>" ",ROW($I$2:$I$9000)," "),ROW(I1)))," ")
并且我也设计了相应的宏,但是因为公式太多所以使用起来太卡,而且用宏的话到别的机器上也不一定能用,现在想向各位大声请教精简这个公式或者制作代码来设计一个按钮。
|
|