Excel函数 姓名批量生成拼音

前言

系统开发前期需要导入甲方公司的组织机构、人员信息、员工岗位、用户信息等。 甲方提供的是中文姓名，登录账号和工号默认使用姓名的全部拼音字母。 这篇文章提供了excel姓名生成拼音的脚本和使用教程。

VBA代码自定义函数法

VBA即Visual Basic宏语言是新一代标准宏语言，VBA是一种编程语言，它依赖于Excel而存在，不能独立于Excel之外运行，通过VBA可以实现各种Excel操作的自动化。

使用VBA代码自定义函数法实现汉字转拼音

1、首先找到「开发工具」选项卡；

没有此选项卡的，可以通过「快速访问工具栏」中的「自定义快速访问工具栏」下的「其他命令」调出「Excel选项」对话框。

也可以通过「文件」选项卡下的「选项」调出「Excel选项」对话框。

点击「自定义功能区」，主选项卡勾选「开发工具」。

2、点击「开发工具」选项卡「代码」命令组下的「Visual Basic」，即会弹出VBA开发窗体，接着在「VBAProject」→「Microsoft Excel 对象」下面选择当前工作表，并右击鼠标选择「插入」下的「模块」。

此时「VBAProject」下面会多出一个「模块」，并且右边灰色区域也会多出一个空白编辑器。

并把文末的代码复制到空白编辑器中.

然后点击保存，弹出的对话框点击是，然后关闭VBA窗体。

3、然后再C4单元格中输入公式：「=getpy(B4)」，并选中C4单元格，鼠标移到C4单元格右下角，当鼠标变成黑色十字，然后按住鼠标左键向下快速填充C5:C10区域，到此整个操作就完成了

汉字转拼音VBA代码：

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 "