wps表格教程：三秒鐘獲取字詞對應的第一個字母

sou6發表於2016-09-29

點選下載：能獲取字詞前一、二、三個漢字音序的模板：獲取漢字音序的模板.et
預覽該模板：
<a href=/office/wps/ target=_blank class=infotextkey>wps</a>表格教程：三秒鐘獲取字詞對應的第一個字母 三聯教程


在日常工作過程中，我們常需要獲取漢字對應的第一個字母，比如，學生用音序查字法查字詞典，醫院建立藥品查詢系統，KTV建立歌曲查詢系統……有沒有辦法輕鬆獲取字詞的第一個字母呢？大家都知道ET和EXCLE可以按音序排列中文資料，這就表明電子表格已經能洞悉漢字拼音的第一個字母與其在漢字編碼系統的位置的關係了。只要查詢出漢字在編碼系統中的位置，並告知相同音序的漢字在編碼中的起始位置，電子表格就可以將漢字的音序輕鬆獲取。
想三秒鐘獲取漢字對應的第一個字母就開啟你的WPS表格2012，跟我試試吧！

一、開啟原始工作簿，在準備填充字母的列貼上下列巢狀公式：

=LOOKUP(CODE(A2),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

在這個公式中，CODE函式旨在查詢詞語（字串）第一個字元對應的數字程式碼，這些程式碼其實標明瞭這個漢字在程式碼庫中的具體排位，LOOKP函式就根據不同音序漢字在程式碼庫中的起始位置，獲取到這個漢字的第一個字母了。
其中，A2是引用漢語字詞所在的單元格名稱，大家可獲取字詞前兩個漢字所對應的第一個字母根據實際情況修改。

二、如果你想獲取詞語第二個漢字對應的字母，可以將上述公式中的A2改為MID(A2,2,1)，表示從字串的第二個開始取的第一個，即第二個。具體公式為：

=LOOKUP(CODE(MID(A2,2,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})


如果想取第三個可以以此類推，即：

=LOOKUP(CODE(MID(A2,3,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

三、如果想獲取前兩個漢字音序怎麼辦？用“&”將獲取第一、二個漢字音序的公式合併即可。如果想獲取前三個漢字音序就用“&”將獲取第一、二、三個漢字音序的公式合併。

獲取字串前三個漢字音序的公式：
=LOOKUP(CODE(A2),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(A2,2,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(A2,3,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

相關文章