excel表格怎么把汉字转换成拼音简码 excel怎么把汉字转换成拼音
\u5982\u4f55\u5728excel\u4e2d\u628a\u6c49\u5b57\u8f6c\u6362\u6210\u62fc\u97f31.\u542f\u52a8Excel 2003\uff08\u5176\u5b83\u7248\u672c\u8bf7\u4eff\u7167\u64cd\u4f5c\uff09\uff0c\u6253\u5f00\u76f8\u5e94\u7684\u5de5\u4f5c\u8868\uff1b
2.\u6267\u884c\u201c\u5de5\u5177\u2192\u5b8f\u2192Visual Basic\u7f16\u8f91\u5668\u201d\u547d\u4ee4\uff08\u6216\u8005\u76f4\u63a5\u6309\u201cAlt+F11\u201d\u7ec4\u5408\u952e\uff09\uff0c\u8fdb\u5165Visual Basic\u7f16\u8f91\u72b6\u6001\uff1b
3.\u6267\u884c\u201c\u63d2\u5165\u2192\u6a21\u5757\u201d\u547d\u4ee4\uff0c\u63d2\u5165\u4e00\u4e2a\u65b0\u6a21\u5757\u3002\u518d\u53cc\u51fb\u63d2\u5165\u7684\u6a21\u5757\uff0c\u8fdb\u5165\u6a21\u5757\u4ee3\u7801\u7f16\u8f91\u72b6\u6001\uff1b
4.\u5c06\u4ee3\u7801\u8f93\u5165\u5176\u4e2d\uff1b\u5b57\u6570\u592a\u591a\uff0c\u4f60\u5728\u4e0b\u9762\u94fe\u63a5\u533a\u590d\u5236\u5b8c\u6574\u4ee3\u7801
http://jingyan.baidu.com/article/cd4c2979ee7808756e6e6085.html
5.\u4ee3\u7801\u8f93\u5165\u5b8c\u6210\u540e\uff0c\u76f4\u63a5\u5173\u95edVisual Basic\u7f16\u8f91\u7a97\u53e3\uff0c\u8fd4\u56deExcel\u7f16\u8f91\u72b6\u6001\uff1b
6.\u81ea\u5b9a\u4e49\u51fd\u6570\u5c31\u53ef\u4ee5\u7528\u4e86\uff0c\u5982\uff1a\u9009\u4e2dA2\u5355\u5143\u683c\uff0c\u8f93\u5165\u516c\u5f0f\uff1a=getpy(A2)\u3002
Excel中可以利用自定义函数来实现将汉字转换成拼音。
所用工具：Office2007
举例说明如下：
1.将A列数据转换成拼音：
2.alt+F11插入模块，在模块中输入如下代码，代码很多（由于篇幅限制，无法将全部代码发上来，可以搜索汉字ASC码来获取），但原理很简单，利用每个汉字的ASC码来得到这个汉字的拼音，然后依次提取单元格中的每一个汉字，分别获得其拼音。
Function pinyin(p As String) As String
i = Asc(p)
Select Case i
Case -20319 To -20318: pinyin = "a "
Case -20317 To -20305: pinyin = "ai "
Case -20304 To -20296: pinyin = "an "
Case -20295 To -20293: pinyin = "ang "
Case -20292 To -20284: pinyin = "ao "
Case -20283 To -20266: pinyin = "ba "
Case -20265 To -20258: pinyin = "bai "
Case -20257 To -20243: pinyin = "ban "
Case -20242 To -20231: pinyin = "bang "
Case -20230 To -20052: pinyin = "bao "
Case -20051 To -20037: pinyin = "bei "
Case -20036 To -20033: pinyin = "ben "
Case -20032 To -20027: pinyin = "beng "
Case -20026 To -20003: pinyin = "bi "
Case -20002 To -19991: pinyin = "bian "
Case -19990 To -19987: pinyin = "biao "
Case -19986 To -19983: pinyin = "bie "
……
Case -11055 To -11053: pinyin = "za "
Case -11052 To -11046: pinyin = "zai "
Case -11045 To -11042: pinyin = "zan "
Case -11041 To -11039: pinyin = "zang "
Case -11038 To -11025: pinyin = "zao "
Case -11024 To -11021: pinyin = "ze "
Case -11020 To -11020: pinyin = "zei "
Case -11019 To -11019: pinyin = "zen "
Case -11018 To -11015: pinyin = "zeng "
Case -11014 To -10839: pinyin = "zha "
Case -10838 To -10833: pinyin = "zhai "
Case -10832 To -10816: pinyin = "zhan "
Case -10815 To -10801: pinyin = "zhang "
Case -10800 To -10791: pinyin = "zhao "
Case -10790 To -10781: pinyin = "zhe "
Case -10780 To -10765: pinyin = "zhen "
Case -10764 To -10588: pinyin = "zheng "
Case -10587 To -10545: pinyin = "zhi "
Case -10544 To -10534: pinyin = "zhong "
Case -10533 To -10520: pinyin = "zhou "
Case -10519 To -10332: pinyin = "zhu "
Case -10331 To -10330: pinyin = "zhua "
Case -10329 To -10329: pinyin = "zhuai "
Case -10328 To -10323: pinyin = "zhuan "
Case -10322 To -10316: pinyin = "zhuang "
Case -10315 To -10310: pinyin = "zhui "
Case -10309 To -10308: pinyin = "zhun "
Case -10307 To -10297: pinyin = "zhuo "
Case -10296 To -10282: pinyin = "zi "
Case -10281 To -10275: pinyin = "zong "
Case -10274 To -10271: pinyin = "zou "
Case -10270 To -10263: pinyin = "zu "
Case -10262 To -10261: pinyin = "zuan "
Case -10260 To -10257: pinyin = "zui "
Case -10256 To -10255: pinyin = "zun "
Case -10254 To -10254: pinyin = "zuo "
Case Else: pinyin = p
End Select
End Function
Function py(str)
For i = 1 To Len(str)
py = py & pinyin(Mid(str, i, 1))
Next i
End Function
3.这样，返回Excel利用自定义的py函数即可：
4.下拉填充，就可以得到全部汉字的拼音：
用=getpy(单元格)公式。因为姓名在哪个上，就要引用哪个单元格。
绛旓細1. 棣栧厛锛屽湪WPS Excel涓墦寮浣犻渶瑕佽浆鎹㈡嫾闊崇殑琛ㄦ牸銆2. 閫変腑鍖呭惈姹夊瓧鐨勫崟鍏冩牸鎴栧尯鍩銆備綘鍙互閫氳繃榧犳爣鐐瑰嚮骞舵嫋鎷芥潵閫夋嫨澶氫釜鍗曞厓鏍笺3. 鍦╓PS Excel鐨勮彍鍗曟爮涓紝鎵惧埌鈥滃紑濮嬧濋夐」鍗★紝骞剁偣鍑烩滄嫾闊虫寚鍗椻濇寜閽傝繖涓寜閽氬父浣嶄簬鈥滃瓧浣撯濈粍鐨勫彸涓嬭銆4. 鍦ㄥ脊鍑虹殑鈥滄嫾闊虫寚鍗椻濆璇濇涓紝浣犲彲浠ョ湅鍒伴夊畾鐨勬眽...
绛旓細1銆佹墦寮闇瑕佹暣鐞嗙殑琛ㄦ牸鏁版嵁,濡備笅鍥2銆佸揩鎹烽敭alt+f11,璋冨嚭瀹忚缃〉闈,閫夋嫨鎻掑叆涓殑妯″潡閫夐」3銆佸湪鍑虹幇鐨勬ā鍧楅潰鏉夸腑,瀹氫箟涓涓猤etpy鐨勬柟娉,灏嗗涓嬩唬鐮佺矘璐磋繘鍘籉unction pinyin(p As String) As Stringi = Asc(p)Select Case iCase -20319 To -20318: pinyin = a Case -20317 To -20305: pinyin = ai Case -...
绛旓細1銆侀鍏堝湪鑷繁鐢佃剳涓婏紝鎵撳紑Excel涓浠借〃鏍笺2銆佹垜浠氨鍦ㄦ墦寮鐨勮〃鏍糄1鍗曞厓鏍间笂锛岃緭鍏ヨ浆鎷奸煶鐨勬眽瀛楋紝鐒跺悗鍦―2鍗曞厓鏍奸噷杈撳叆=getpy(A2)鍏紡銆傚洜涓哄鍚嶅湪A鍒椾笂锛屾墍浠ラ渶瑕佸紩鐢ㄥ崟鍏冩牸閫夋嫨A2銆3銆佽緭鍏ュ畬鍏紡閫変腑濂藉紩鐢ㄥ悕瀛楃殑鍗曞厓鏍间箣鍚庯紝鎸変笅纭畾閿湪D2鍗曞厓鏍煎氨鍙互鐪嬪埌濮撳悕宸茬粡杞崲鎴愭嫾闊充簡銆4銆佺劧鍚庨変腑D2...
绛旓細鎵撳紑闇瑕杞崲鐨excel琛ㄦ牸鍥1鍗曞嚮excel琛ㄦ牸鐨勪换鎰忎綅缃紝鐒跺悗鎸変笅ALT+F11锛岃繘鍏ヨ繖涓伆鑹茬殑缂栬緫鐣岄潰鍥2鎻掑叆--妯″潡鍥3鎵撳紑濡備笅鎵绀虹殑鐣岄潰銆傚浘4鎶婁笅闈㈢殑鍐呭澶嶅埗绮樿创鍒扮鍥涗釜姝ラ鎵撳紑鐨勭晫闈腑锛屽苟淇濆瓨锛屽弬鑰冧笅鍥炬搷浣溿侳unctionpinyin(pAsString)AsStringi=Asc(p)SelectCaseiCase-20319To-20318:pinyin=aCaseElse:...
绛旓細01 棣栧厛鎵撳紑excel琛ㄦ牸锛屽湪閲岄潰杈撳叆涓浜涜嚜宸辫鎷奸煶鐨姹夊瓧锛屾湁浜涗汉灏辨槸鎯崇洿鎺ュ湪琛ㄦ牸涓緭鍏ユ嫾闊筹紝鐒跺悗鐐瑰嚮鍒楋紝鐐瑰嚮榧犳爣鍙抽敭锛岀劧鍚庨夋嫨澶嶅埗锛岃繖鏃跺欏氨澶嶅埗濂戒簡锛岀劧鍚庢墦寮word鏂囨。锛灏澶嶅埗鍐呭榛忚创杩涙潵鍗冲彲銆02 鎺ョ潃鐐瑰嚮寮濮嬪锛岀劧鍚庢壘鍒版嫾闊虫寚鍗楋紝濡傚浘鎵绀烘槸涓涓鏂囧瓧锛岃繕鏈夋嫾闊崇殑绗﹀彿锛岀洿鎺ョ偣鍑昏繖涓嵆鍙03...
绛旓細1.濡備笅鍥撅紝瑕佹妸杩欏垪鍗曞厓鏍肩殑鏂囧瓧杞崲涓鸿〃鏍鐨勮瘽锛岄変腑鍗曞厓鏍煎悗鍏堢偣鍑婚《閮ㄨ彍鍗曟爮鐨勩愬伐鍏风銆戞寜閽紱2. 鐒跺悗鍦ㄤ笅鎷夎彍鍗曚腑閫夋嫨銆愭嫾闊崇瑪鐢汇戯紝濡傚浘鎵绀猴紱3. 鍦ㄥ脊鍑虹殑閫夐」涓紝鐐瑰嚮銆愭眽瀛楄浆鎷奸煶銆戯紝濡傚浘鎵绀猴紱4. 鍦ㄥ脊鍑虹殑绐楀彛涓紝鐐瑰嚮纭畾鎸夐挳锛5. 閫夋嫨瀛樻斁鎷奸煶鐨勫崟鍏冩牸鍚庯紝鐐瑰嚮纭畾鎸夐挳锛6. 濡備笅鍥撅紝...
绛旓細棣栧厛鐢╫ffice Excel 2007鎵撳紑鏂囨。锛2 鐒跺悗榧犳爣鏍囧畾鍦―2鍗曞厓鏍硷紝杈撳叆鍏紡鈥=getpy(C2)鈥濓紝鎸夊洖杞﹂敭纭锛3 鎴戜滑灏辩湅鍒板鍚嶄竴鏍姹夎鎷奸煶灏辫嚜鍔ㄥ嚭鐜颁簡锛4 閫変腑D2鍗曞厓鏍硷紝榧犳爣鏀惧湪鍗曞厓鏍煎彸涓嬭,褰撳嚭鐜扳+鈥濆悗锛屽悜涓嬫嫋鍔ㄩ紶鏍囷紱5 杩欐牱锛屽鍚嶄竴鏍忔墍鏈夌殑姹夊瓧閮戒互鎷奸煶褰㈠紡鏄剧ず浜嗭紱6 濡傛灉鎴戜滑闇瑕灏鎷奸煶鐨勯...
绛旓細1銆佹墦寮瑕杞崲鎴鎷奸煶鐨excel琛ㄦ牸 2銆佹寜鈥淎lt+F11鈥濈粍鍚堥敭锛岃繘鍏isual Basic缂栬緫鐘舵併備篃灏辨槸鐪嬪埌鐨勮繖涓伆鑹茬殑缂栬緫鐣岄潰 3銆佹墽琛屸滄彃鍏モ啋妯″潡鈥濆懡浠わ紝鎻掑叆涓涓柊妯″潡銆傚啀鍙屽嚮鎻掑叆鐨勬ā鍧楋紝杩涘叆妯″潡浠ｇ爜缂栬緫鐘舵侊紱4銆佺湅鍒板涓嬬晫闈 5銆佹妸涓嬮潰鐨勬墍鏈夊唴瀹瑰鍒讹紝绮樿创鍒版楠4涓殑绌虹櫧澶 澶嶅埗鍐呭鍒板壀璐存澘 Functio...
绛旓細1.灏A鍒楁暟鎹杞崲鎴鎷奸煶锛2.alt+F11鎻掑叆妯″潡锛屽湪妯″潡涓緭鍏ュ涓嬩唬鐮侊紝浠ｇ爜寰堝锛堢敱浜庣瘒骞呴檺鍒讹紝鏃犳硶灏嗗叏閮ㄤ唬鐮佸彂涓婃潵锛屽彲浠ユ悳绱姹夊瓧ASC鐮佹潵鑾峰彇锛夛紝浣嗗師鐞嗗緢绠鍗曪紝鍒╃敤姣忎釜姹夊瓧鐨凙SC鐮佹潵寰楀埌杩欎釜姹夊瓧鐨勬嫾闊筹紝鐒跺悗渚濇鎻愬彇鍗曞厓鏍间腑鐨勬瘡涓涓眽瀛楋紝鍒嗗埆鑾峰緱鍏舵嫾闊炽侳unction pinyin(p As String) As String i...
绛旓細鎯宠鍦╓PS琛ㄦ牸涓鎶婃眽瀛楄浆鎹㈡垚鎷奸煶鎴栧皬鍐欏瓧姣嶏紝鍙渶瑕佽繍鐢ㄦā鍧椾唬鐮侊紝缂栬緫鍔熻兘灏辫兘杞绘澗瑙ｅ喅鍏蜂綋鎿嶄綔鏂规硶锛屽涓嬫楠や竴銆佹墦寮锛岃杞崲鎴愭嫾闊崇殑Excel琛ㄦ牸鎸堿lt鍔燜11缁勫悎閿繘鍏isual basic缂栬緫鐘舵併備篃灏辨槸鐪嬪埌鐨勮繖涓伆鑹茬殑缂栬緫鐣岄潰銆
