本帖最后由 cutecpu 于 2019-10-16 16:41 编辑
因為不知道您的 Excel 版本,所以用了 PHONETIC 函數來替您解答:
E2 陣列公式:
=IF(ISTEXT(A2),IF(B2<>"",TRIM(LEFT(
SUBSTITUTE(
PHONETIC(OFFSET(B2,,,MAX((SUBTOTAL(3,OFFSET(A2,,,ROW($1:$10)))<=2)*ROW($1:$10)))),
"|",REPT(" ",200)),
200)),"无"),"")
F2 陣列公式:
=IF(ISTEXT(A2),IF(B2<>"",TRIM(RIGHT(
SUBSTITUTE(
PHONETIC(OFFSET(B2,,,MAX((SUBTOTAL(3,OFFSET(A2,,,ROW($1:$10)))<=2)*ROW($1:$10)))),
"|",REPT(" ",200)),
200)),"无"),"")
※陣列公式,請記得按Ctrl+Shift+Enter
※如果有解決您的問題,再請將帖子設為[已解決]
祝順心,南無阿彌陀佛!
|