本帖最后由 opqazxc 于 2013-9-7 09:18 编辑
AL6输入- =IF(SUM(N(MIN($AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2))+{0;1}=$AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2)))=3+COLUMN(B1)/2,$AF6,"")
复制代码 AM6输入- =IF(AND(SUM(N(MIN($AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2))+{0;1}=$AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2)))=3+COLUMN(B1)/2,OR($AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2)<>$AF6)),MIN(IF($AF6<>$AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2),$AF6:INDEX($AF6:$AK6,3+COLUMN(B1)/2)),9^9),"")
复制代码 都是数组公式
组合键结束后
选择AL6:AM6右拉到AQ6
再下拉即可
或者
AL6:AQ6分别输入下面6个数组公式再下拉吧
AL6- =IF(SUM(N(MIN(AF6:AI6)+{0;1}=AF6:AI6))=4,AF6,"")
复制代码 AM6- =IF(AND(SUM(N(MIN(AF6:AI6)+{0;1}=AF6:AI6))=4,OR(AF6:AI6<>AF6)),MIN(IF(AF6<>AF6:AI6,AF6:AI6),9^9),"")
复制代码 AN6- =IF(SUM(N(MIN(AF6:AJ6)+{0;1}=AF6:AJ6))=5,AF6,"")
复制代码 AO6- =IF(AND(SUM(N(MIN(AF6:AJ6)+{0;1}=AF6:AJ6))=5,OR(AF6:AJ6<>AF6)),MIN(IF(AF6<>AF6:AJ6,AF6:AJ6),9^9),"")
复制代码 AP6- =IF(SUM(N(MIN(AF6:AK6)+{0;1}=AF6:AK6))=6,AF6,"")
复制代码 AQ6- =IF(AND(SUM(N(MIN(AF6:AK6)+{0;1}=AF6:AK6))=6,OR(AF6:AK6<>AF6)),MIN(IF(AF6<>AF6:AK6,AF6:AK6),9^9),"")
复制代码 都是相对改一下``
呵
谢谢笑笑加分哈``
|