本帖最后由 micch 于 2020-2-27 21:51 编辑
- =OFFSET(A$1,,MIN(COUNTIF(B2:F2,">"&B2:F2)/1%+COLUMN(A:E)))&
- OFFSET(A$1,,SMALL(COUNTIF(B2:F2,">"&B2:F2)/1%+COLUMN(A:E),2))&
- OFFSET(A$1,,SMALL(COUNTIF(B2:F2,">"&B2:F2)/1%+COLUMN(A:E),3))&
- OFFSET(A$1,,SMALL(COUNTIF(B2:F2,">"&B2:F2)/1%+COLUMN(A:E),4))&
- OFFSET(A$1,,SMALL(COUNTIF(B2:F2,">"&B2:F2)/1%+COLUMN(A:E),5))
复制代码
再换一个replace的写法能少复制一次
- =REPLACE(REPLACE(REPLACE(REPLACE(PHONETIC(B$1:F$1),
- SMALL((COUNTIF(B2:F2,">"&B2:F2)=0)/1%+COLUMN(A:E),4),1,),
- SMALL((COUNTIF(B2:F2,">"&B2:F2)=0)/1%+COLUMN(A:E),3),1,),
- SMALL((COUNTIF(B2:F2,">"&B2:F2)=0)/1%+COLUMN(A:E),2),1,),
- MIN((COUNTIF(B2:F2,">"&B2:F2)=0)/1%+COLUMN(A:E)),1,)
复制代码
|