本帖最后由 mmlzh 于 2018-4-24 21:07 编辑
数组公式W6- =MIN(IF(FREQUENCY(IF(OFFSET($A$1,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-MIN(IF(COUNTIF(OFFSET($A$1,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-COLUMN($U:$AYA)),RIGHT(W$4))=20,COLUMN($U:$AYA))))=RIGHT(W$4),OFFSET($A6,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-MIN(IF(COUNTIF(OFFSET($A$1,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-COLUMN($U:$AYA)),RIGHT(W$4))=20,COLUMN($U:$AYA))))),ROW($1:$98))>2,ROW($1:$99)))&"~"&MAX(IF(FREQUENCY(IF(OFFSET($A$1,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-MIN(IF(COUNTIF(OFFSET($A$1,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-COLUMN($U:$AYA)),RIGHT(W$4))=20,COLUMN($U:$AYA))))=RIGHT(W$4),OFFSET($A6,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-MIN(IF(COUNTIF(OFFSET($A$1,,MAX(IF($AC6:$BCN6>0,COLUMN($AC:$BCN)))-1,,-COLUMN($U:$AYA)),RIGHT(W$4))=20,COLUMN($U:$AYA))))),ROW($1:$98))>2,ROW($1:$99)))
复制代码 |