今天工作遇到一个需求,其中有一列的需求是将姓名转变为拼音全称 这个需求有两种解决办法(目前)
直接进入正题:
1、第一种方法,利用自定义函数
要将以上的名字汉语拼音写出来,(举个例子,实际数据比这要多很多) 首先需要编辑自定义函数
打开Excel->工具->宏->Viaual Basic编辑器 在弹出来的窗口中对着VBAproject点右键->插入->模块 下面会出现一个名为"模块1",点击 在右边的空白栏中粘贴以下内容:
我的是在这个位置,每个人或每个版本excel的布局都不太一样 输入代码:
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 -19982 To -19977: pinyin = "bin"
Case -19976 To -19806: pinyin = "bing"
Case -19805 To -19785: pinyin = "bo"
Case -19784 To -19776: pinyin = "bu"
Case -19775 To -19775: pinyin = "ca"
Case -19774 To -19764: pinyin = "cai"
Case -19763 To -19757: pinyin = "can"
Case -19756 To -19752: pinyin = "cang"
Case -19751 To -19747: pinyin = "cao"
Case -19746 To -19742: pinyin = "ce"
Case -19741 To -19740: pinyin = "ceng"
Case -19739 To -19729: pinyin = "cha"
Case -19728 To -19726: pinyin = "chai"
Case -19725 To -19716: pinyin = "chan"
Case -19715 To -19541: pinyin = "chang"
Case -19540 To -19532: pinyin = "chao"
Case -19531 To -19526: pinyin = "che"
Case -19525 To -19516: pinyin = "chen"
Case -19515 To -19501: pinyin = "cheng"
Case -19500 To -19485: pinyin = "chi"
Case -19484 To -19480: pinyin = "chong"
Case -19479 To -19468: pinyin = "chou"
Case -19467 To -19290: pinyin = "chu"
Case -19289 To -19289: pinyin = "chuai"
Case -19288 To -19282: pinyin = "chuan"
Case -19281 To -19276: pinyin = "chuang"
Case -19275 To -19271: pinyin = "chui"
Case -19270 To -19264: pinyin = "chun"
Case -19263 To -19262: pinyin = "chuo"
Case -19261 To -19250: pinyin = "ci"
Case -19249 To -19244: pinyin = "cong"
Case -19243 To -19243: pinyin = "cou"
Case -19242 To -19239: pinyin = "cu"
Case -19238 To -19236: pinyin = "cuan"
Case -19235 To -19228: pinyin = "cui"
Case -19227 To -19225: pinyin = "cun"
Case -19224 To -19219: pinyin = "cuo"
Case -19218 To -19213: pinyin = "da"
Case -19212 To -19039: pinyin = "dai"
Case -19038 To -19024: pinyin = "dan"
Case -19023 To -19019: pinyin = "dang"
Case -19018 To -19007: pinyin = "dao"
Case -19006 To -19004: pinyin = "de"
Case -19003 To -18997: pinyin = "deng"
Case -18996 To -18978: pinyin = "di"
Case -18977 To -18962: pinyin = "dian"
Case -18961 To -18953: pinyin = "diao"
Case -18952 To -18784: pinyin = "die"
Case -18783 To -18775: pinyin = "ding"
Case -18774 To -18774: pinyin = "diu"
Case -18773 To -18527: pinyin =
|