cys888 发表于 2014-2-21 11:53
谢谢!!
用函数有办法吗?
公式长了! AG3单元格复制以下公式,三键回车( 先按住 shift、ctrl 两个键,然后敲enter键 ),
公式右拉。
=IF(COLUMN()=SMALL(IF(($AG$5:$CD$5<>"")*($AF$5:$CC$5=""),COLUMN($AG$5:$CD$5),""),MIN(IF((IFERROR(SMALL(IF(($AG$5:$CD$5<>"")*($AH$5:$CE$5=""),COLUMN($AG$5:$CD$5),""),ROW($1:$10)),0)-IFERROR(SMALL(IF(($AG$5:$CD$5<>"")*($AF$5:$CC$5=""),COLUMN($AG$5:$CD$5),""),ROW($1:$10)),0)+1)=(MAX(IFERROR(SMALL(IF(($AG$5:$CD$5<>"")*($AH$5:$CE$5=""),COLUMN($AG$5:$CD$5),""),ROW($1:$10)),0)-IFERROR(SMALL(IF(($AG$5:$CD$5<>"")*($AF$5:$CC$5=""),COLUMN($AG$5:$CD$5),""),ROW($1:$10)),0)+1)),ROW($1:$10),99))),AG5,IF((AF3<>"")*(AG5<>""),AG5,""))