|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
=IFERROR(INDEX($B$2:$B$999,INDEX(MATCH(--SMALL(IFERROR(CODE(LEFT($B$2:$B$999,1))+RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)/10^LEN(RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)),FALSE),ROW($B$2:$B$9999)-1),--IFERROR(CODE(LEFT($B$2:$B$999,1))+RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)/10^LEN(RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)),FALSE),0),ROW(A1))),"") 下拉根据给的数据排序
首字为A提取排序=IF(ISNUMBER(FIND("A",IFERROR(INDEX($B$2:$B$999,INDEX(MATCH(--SMALL(IFERROR(CODE(LEFT($B$2:$B$999,1))+RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)/10^LEN(RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)),FALSE),ROW($B$2:$B$9999)-1),--IFERROR(CODE(LEFT($B$2:$B$999,1))+RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)/10^LEN(RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)),FALSE),0),ROW(A1))),""),1)),IFERROR(INDEX($B$2:$B$999,INDEX(MATCH(--SMALL(IFERROR(CODE(LEFT($B$2:$B$999,1))+RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)/10^LEN(RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)),FALSE),ROW($B$2:$B$9999)-1),--IFERROR(CODE(LEFT($B$2:$B$999,1))+RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)/10^LEN(RIGHT($B$2:$B$999,LEN($B$2:$B$999)-1)),FALSE),0),ROW(A1))),""),"") |
|