(完整版)Excel自定义函数方法拼音
如何在excel中把汉字转换成拼音
Excel自定义函数方法：1.启动Excel 2003（其它版本请仿照操作），打开相应的工作表；2.执行“工具→宏→Visual Basic编辑器”命令（或者直接按“Alt+F11”组合键），进入Visual Basic编辑状态；3.执行“插入→模块”命令，插入一个新模块。
再双击插入的模块，进入模块代码编辑状态；4.将代码输入其中；5.代码输入完成后，关闭Visual Basic编辑窗口，返回Excel编辑状态；6.自定义函数就可以用了，如：选中D2单元格，输入公式：=getpy(A2)。
有极个别的汉字不能转换，手动就可以了！很方便了。
Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -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 -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 = "dong " Case -18526 To -18519: pinyin = "fa " Case -18518 To -18502: pinyin = "fan " Case -18501 To -18491: pinyin = "fang " Case -18490 To -18479: pinyin = "fei " Case -18478 To -18464: pinyin = "fen " Case -18463 To -18449: pinyin = "feng "Case -18447 To -18447: pinyin = "fou " Case -18446 To -18240: pinyin = "fu " Case -18239 To -18238: pinyin = "ga " Case -18237 To -18232: pinyin = "gai " Case -18231 To -18221: pinyin = "gan " Case -18220 To -18212: pinyin = "gang " Case -18211 To -18202: pinyin = "gao " Case -18201 To -18185: pinyin = "ge " Case -18184 To -18184: pinyin = "gei " Case -18183 To -18182: pinyin = "gen " Case -18181 To -18013: pinyin = "geng " Case -18012 To -17998: pinyin = "gong " Case -17997 To -17989: pinyin = "gou " Case -17988 To -17971: pinyin = "gu " Case -17970 To -17965: pinyin = "gua " Case -17964 To -17962: pinyin = "guai " Case -17961 To -17951: pinyin = "guan " Case -17950 To -17948: pinyin = "guang " Case -17947 To -17932: pinyin = "gui " Case -17931 To -17929: pinyin = "gun " Case -17928 To -17923: pinyin = "guo " Case -17922 To -17760: pinyin = "ha " Case -17759 To -17753: pinyin = "hai " Case -17752 To -17734: pinyin = "han " Case -17733 To -17731: pinyin = "hang " Case -17730 To -17722: pinyin = "hao " Case -17721 To -17704: pinyin = "he " Case -17703 To -17702: pinyin = "hei " Case -17701 To -17698: pinyin = "hen " Case -17697 To -17693: pinyin = "heng " Case -17692 To -17684: pinyin = "hong " Case -17683 To -17677: pinyin = "hou " Case -17676 To -17497: pinyin = "hu " Case -17496 To -17488: pinyin = "hua " Case -17487 To -17483: pinyin = "huai " Case -17482 To -17469: pinyin = "huan " Case -17468 To -17455: pinyin = "huang " Case -17454 To -17434: pinyin = "hui " Case -17433 To -17428: pinyin = "hun " Case -17427 To -17418: pinyin = "huo " Case -17417 To -17203: pinyin = "ji " Case -17202 To -17186: pinyin = "jia " Case -17185 To -16984: pinyin = "jian "Case -16970 To -16943: pinyin = "jiao " Case -16942 To -16916: pinyin = "jie " Case -16915 To -16734: pinyin = "jin " Case -16733 To -16709: pinyin = "jing " Case -16708 To -16707: pinyin = "jiong " Case -16706 To -16690: pinyin = "jiu " Case -16689 To -16665: pinyin = "ju " Case -16664 To -16658: pinyin = "juan " Case -16657 To -16648: pinyin = "jue " Case -16647 To -16475: pinyin = "jun " Case -16474 To -16471: pinyin = "ka " Case -16470 To -16466: pinyin = "kai " Case -16465 To -16460: pinyin = "kan " Case -16459 To -16453: pinyin = "kang " Case -16452 To -16449: pinyin = "kao " Case -16448 To -16434: pinyin = "ke " Case -16433 To -16430: pinyin = "ken " Case -16429 To -16428: pinyin = "keng " Case -16427 To -16424: pinyin = "kong " Case -16423 To -16420: pinyin = "kou " Case -16419 To -16413: pinyin = "ku " Case -16412 To -16408: pinyin = "kua " Case -16407 To -16404: pinyin = "kuai " Case -16403 To -16402: pinyin = "kuan " Case -16401 To -16394: pinyin = "kuang " Case -16393 To -16221: pinyin = "kui " Case -16220 To -16217: pinyin = "kun " Case -16216 To -16213: pinyin = "kuo " Case -16212 To -16206: pinyin = "la " Case -16205 To -16203: pinyin = "lai " Case -16202 To -16188: pinyin = "lan " Case -16187 To -16181: pinyin = "lang " Case -16180 To -16172: pinyin = "lao " Case -16171 To -16170: pinyin = "le " Case -16169 To -16159: pinyin = "lei " Case -16158 To -16156: pinyin = "leng " Case -16155 To -15960: pinyin = "li " Case -15959 To -15959: pinyin = "lia " Case -15958 To -15945: pinyin = "lian " Case -15944 To -15934: pinyin = "liang " Case -15933 To -15921: pinyin = "liao " Case -15920 To -15916: pinyin = "lie " Case -15915 To -15904: pinyin = "lin "Case -15889 To -15879: pinyin = "liu " Case -15878 To -15708: pinyin = "long " Case -15707 To -15702: pinyin = "lou " Case -15701 To -15682: pinyin = "lu " Case -15681 To -15668: pinyin = "lv " Case -15667 To -15662: pinyin = "luan " Case -15661 To -15660: pinyin = "lue " Case -15659 To -15653: pinyin = "lun " Case -15652 To -15641: pinyin = "luo " Case -15640 To -15632: pinyin = "ma " Case -15631 To -15626: pinyin = "mai " Case -15625 To -15455: pinyin = "man " Case -15454 To -15449: pinyin = "mang " Case -15448 To -15437: pinyin = "mao " Case -15436 To -15436: pinyin = "me " Case -15435 To -15420: pinyin = "mei " Case -15419 To -15417: pinyin = "men " Case -15416 To -15409: pinyin = "meng " Case -15408 To -15395: pinyin = "mi " Case -15394 To -15386: pinyin = "mian " Case -15385 To -15378: pinyin = "miao " Case -15377 To -15376: pinyin = "mie " Case -15375 To -15370: pinyin = "min " Case -15369 To -15364: pinyin = "ming " Case -15363 To -15363: pinyin = "miu " Case -15362 To -15184: pinyin = "mo " Case -15183 To -15181: pinyin = "mou " Case -15180 To -15166: pinyin = "mu " Case -15165 To -15159: pinyin = "na " Case -15158 To -15154: pinyin = "nai " Case -15153 To -15151: pinyin = "nan " Case -15150 To -15150: pinyin = "nang " Case -15149 To -15145: pinyin = "nao " Case -15144 To -15144: pinyin = "ne " Case -15143 To -15142: pinyin = "nei " Case -15141 To -15141: pinyin = "nen " Case -15140 To -15140: pinyin = "neng " Case -15139 To -15129: pinyin = "ni " Case -15128 To -15122: pinyin = "nian " Case -15121 To -15120: pinyin = "niang " Case -15119 To -15118: pinyin = "niao " Case -15117 To -15111: pinyin = "nie " Case -15110 To -15110: pinyin = "nin "Case -14941 To -14938: pinyin = "niu " Case -14937 To -14934: pinyin = "nong " Case -14933 To -14931: pinyin = "nu " Case -14930 To -14930: pinyin = "nv " Case -14929 To -14929: pinyin = "nuan " Case -14928 To -14927: pinyin = "nue " Case -14926 To -14923: pinyin = "nuo " Case -14922 To -14922: pinyin = "o " Case -14921 To -14915: pinyin = "ou " Case -14914 To -14909: pinyin = "pa " Case -14908 To -14903: pinyin = "pai " Case -14902 To -14895: pinyin = "pan " Case -14894 To -14890: pinyin = "pang " Case -14889 To -14883: pinyin = "pao " Case -14882 To -14874: pinyin = "pei " Case -14873 To -14872: pinyin = "pen " Case -14871 To -14858: pinyin = "peng " Case -14857 To -14679: pinyin = "pi " Case -14678 To -14675: pinyin = "pian " Case -14674 To -14671: pinyin = "piao " Case -14670 To -14669: pinyin = "pie " Case -14668 To -14664: pinyin = "pin " Case -14663 To -14655: pinyin = "ping " Case -14654 To -14646: pinyin = "po " Case -14645 To -14631: pinyin = "pu " Case -14630 To -14595: pinyin = "qi " Case -14594 To -14430: pinyin = "qia " Case -14429 To -14408: pinyin = "qian " Case -14407 To -14400: pinyin = "qiang " Case -14399 To -14385: pinyin = "qiao " Case -14384 To -14380: pinyin = "qie " Case -14379 To -14369: pinyin = "qin " Case -14368 To -14356: pinyin = "qing " Case -14355 To -14354: pinyin = "qiong " Case -14353 To -14346: pinyin = "qiu " Case -14345 To -14171: pinyin = "qu " Case -14170 To -14160: pinyin = "quan " Case -14159 To -14152: pinyin = "que " Case -14151 To -14150: pinyin = "qun " Case -14149 To -14146: pinyin = "ran " Case -14145 To -14141: pinyin = "rang " Case -14140 To -14138: pinyin = "rao " Case -14137 To -14136: pinyin = "re "Case -14125 To -14124: pinyin = "reng " Case -14123 To -14123: pinyin = "ri " Case -14122 To -14113: pinyin = "rong " Case -14112 To -14110: pinyin = "rou " Case -14109 To -14100: pinyin = "ru " Case -14099 To -14098: pinyin = "ruan " Case -14097 To -14095: pinyin = "rui " Case -14094 To -14093: pinyin = "run " Case -14092 To -14091: pinyin = "ruo " Case -14090 To -14088: pinyin = "sa " Case -14087 To -14084: pinyin = "sai " Case -14083 To -13918: pinyin = "san " Case -13917 To -13915: pinyin = "sang " Case -13914 To -13911: pinyin = "sao " Case -13910 To -13908: pinyin = "se " Case -13907 To -13907: pinyin = "sen " Case -13906 To -13906: pinyin = "seng " Case -13905 To -13897: pinyin = "sha " Case -13896 To -13895: pinyin = "shai " Case -13894 To -13879: pinyin = "shan " Case -13878 To -13871: pinyin = "shang " Case -13870 To -13860: pinyin = "shao " Case -13859 To -13848: pinyin = "she " Case -13847 To -13832: pinyin = "shen " Case -13831 To -13659: pinyin = "sheng " Case -13658 To -13612: pinyin = "shi " Case -13611 To -13602: pinyin = "shou " Case -13601 To -13407: pinyin = "shu " Case -13406 To -13405: pinyin = "shua " Case -13404 To -13401: pinyin = "shuai " Case -13400 To -13399: pinyin = "shuan " Case -13398 To -13396: pinyin = "shuang " Case -13395 To -13392: pinyin = "shui " Case -13391 To -13388: pinyin = "shun " Case -13387 To -13384: pinyin = "shuo " Case -13383 To -13368: pinyin = "si " Case -13367 To -13360: pinyin = "song " Case -13359 To -13357: pinyin = "sou " Case -13356 To -13344: pinyin = "su " Case -13343 To -13341: pinyin = "suan " Case -13340 To -13330: pinyin = "sui " Case -13329 To -13327: pinyin = "sun " Case -13326 To -13319: pinyin = "suo "Case -13147 To -13139: pinyin = "tai " Case -13138 To -13121: pinyin = "tan " Case -13120 To -13108: pinyin = "tang " Case -13107 To -13097: pinyin = "tao " Case -13096 To -13096: pinyin = "te " Case -13095 To -13092: pinyin = "teng " Case -13091 To -13077: pinyin = "ti " Case -13076 To -13069: pinyin = "tian " Case -13068 To -13064: pinyin = "tiao " Case -13063 To -13061: pinyin = "tie " Case -13060 To -12889: pinyin = "ting " Case -12888 To -12876: pinyin = "tong " Case -12875 To -12872: pinyin = "tou " Case -12871 To -12861: pinyin = "tu " Case -12860 To -12859: pinyin = "tuan " Case -12858 To -12853: pinyin = "tui " Case -12852 To -12850: pinyin = "tun " Case -12849 To -12839: pinyin = "tuo " Case -12838 To -12832: pinyin = "wa " Case -12831 To -12830: pinyin = "wai " Case -12829 To -12813: pinyin = "wan " Case -12812 To -12803: pinyin = "wang " Case -12802 To -12608: pinyin = "wei " Case -12607 To -12598: pinyin = "wen " Case -12597 To -12595: pinyin = "weng " Case -12594 To -12586: pinyin = "wo " Case -12585 To -12557: pinyin = "wu " Case -12556 To -12360: pinyin = "xi " Case -12359 To -12347: pinyin = "xia " Case -12346 To -12321: pinyin = "xian " Case -12320 To -12301: pinyin = "xiang " Case -12300 To -12121: pinyin = "xiao " Case -12120 To -12100: pinyin = "xie " Case -12099 To -12090: pinyin = "xin " Case -12089 To -12075: pinyin = "xing " Case -12074 To -12068: pinyin = "xiong " Case -12067 To -12059: pinyin = "xiu " Case -12058 To -12040: pinyin = "xu " Case -12039 To -11868: pinyin = "xuan " Case -11867 To -11862: pinyin = "xue " Case -11861 To -11848: pinyin = "xun " Case -11847 To -11832: pinyin = "ya " Case -11831 To -11799: pinyin = "yan "Case -11781 To -11605: pinyin = "yao " Case -11604 To -11590: pinyin = "ye " Case -11589 To -11537: pinyin = "yi " Case -11536 To -11359: pinyin = "yin " Case -11358 To -11341: pinyin = "ying " Case -11340 To -11340: pinyin = "yo " Case -11339 To -11325: pinyin = "yong " Case -11324 To -11304: pinyin = "you " Case -11303 To -11098: pinyin = "yu " Case -11097 To -11078: pinyin = "yuan " Case -11077 To -11068: pinyin = "yue " Case -11067 To -11056: pinyin = "yun " 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 -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 = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Function只需要在Excel中使用=getpy(列)使用公式，去掉拼音之间的空格假设你的这个数据在A1单元格，在B1单元格输入：=SUBSTITUTE(A1," ","")。
excel常用函数功能及读法
函数
音标
1、求和函数 SUM
[sʌm]
2、平均值函数 AVERAGE
['ævə]
4、快捷方式或链接创建函数 HYPERLINK ['haipəliŋk]
汉语近似音 1、桑嗯 2、爱我汝已止 3、衣服 4、害泼尔 林可
5、计数函数 COUNT
[kaunt]
5、康te(拼音，第三声）
6、最大(小)值函数 MAX(MIN) 7、条件求和函数 SUMIF 8、货款偿还计算函数 PMT 9、样本的标准偏差函数 STDEV 10、排序函数 RANK 11、四舍五入函数 ROUND 12、条件计数函数 COUNTIF 13、百分排位预测函数 PERCENTILE 14、数值探测函数 ISNUMBER 15、零存整取收益函数 PV 16、内部平均值函数 TRIMMEAN 17、日期年份函数 YEAR
皮vei 16、吃一米恩 17、页尔 18。得ei子 死日亿 稀克斯 就肉 19、创可 20、米的
18、起止天数函数 DAYS360
19、取整函数 TRUNC 20、字符提取函数 MID
[mæ ks] / [sʌmif]
[ræŋk] [raund]
[pə'sentail]
6、迈克斯/命 7、萨米夫 8、皮爱踢 9、爱斯踢待木 10、瑞安可 11、瑞ang的 12、康体夫 13、泼森泰藕
[jə:, jiə] [mid]
excel获得汉字首字母的方法
在日常工作和学习中，我们经常需要对文本进行处理和分析，尤其是对于中文文本。
在处理中文文本时，有时会遇到需要将汉字转换为拼音的需求。
而在Excel中，获得汉字的首字母则成为了一个常见的需求。
那么，如何在Excel中获得汉字的首字母呢？我们可以利用Excel内置的函数来实现这一目标。
在Excel中，可以使用VB脚本编写一个自定义函数来获取汉字的首字母。
具体步骤如下：1. 打开Excel表格，按下ALT + F11，打开VB编辑器。
2. 在VB编辑器中，插入一个新模块。
3. 在新模块中输入以下VB脚本代码：```vbFunction GetFirstLetter(str As String) As StringDim pinyin As StringDim i As IntegerDim ch As Stringpinyin = ""For i = 1 To Len(str)ch = Mid(str, i, 1)pinyin = pinyin &Left(Application.WorksheetFunction.Phonetic(ch), 1)Next iGetFirstLetter = pinyinEnd Function```4. 编译保存后，返回Excel表格界面。
5. 在需要获取汉字首字母的单元格中输入`=GetFirstLetter(A1)`，其中A1为需要转换的汉字所在的单元格。
6. 按下Enter键，即可在目标单元格中获取到相应汉字的首字母。
通过上述方法，我们可以轻松地在Excel中获得汉字的首字母，而且可以将这个函数在多个单元格中批量应用，提高工作效率。
除了使用VB脚本自定义函数外，我们还可以借助Excel内置的函数来实现汉字首字母的获取。
在Excel中，有一个叫做UNICODE的函数，可以返回指定字符的Unicode编码。
而对于汉字来说，其首字母的Unicode编码可以作为其拼音的首字母。
在excel中将名字转换为拼音
在excel中将名字转换为拼音[日期：2012-03-13] 来源：作者：[字体：大中小] 以office2003为例。
首先，自定义函数。
Excel自定义函数方法：1.启动Excel 2003（其它版本请仿照操作），打开相应的工作表；2.执行“工具→宏→Visual Basic编辑器”命令（或者直接按“Alt+F11”组合键），进入Vi sual Basic编辑状态；3.执行“插入→模块”命令，插入一个新模块。
再双击插入的模块，进入模块代码编辑状态；4.将代码输入其中；Function PinYin(Hz As String)Dim PinMa As StringDim MyPinMa As VariantDim Temp As Integer, i As Integer, j As IntegerPinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292,"PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,be n,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,b o,19805,bu,19784,"PinMa = PinMa & "ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng, 19741,cha,19739,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,1952 5,cheng,19515,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19288, chuang,19281,chui,19275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,19 242,cuan,19238,cui,19235,cun,19227,cuo,19224,"PinMa = PinMa & "da,19218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,de ng,19003,di,18996,dian,18977,diao,18961,die,18952,ding,18783,diu,18774,dong,18773, dou,18763,du,18756,duan,18741,dui,18735,dun,18731,duo,18722,"PinMa = PinMa & "e,18710,en,18697,er,18696,"PinMa = PinMa & "fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo,1 8448,fou,18447,fu,18446,"PinMa = PinMa & "ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei, 18184,gen,18183,geng,18181,gong,18012,gou,17997,gu,17988,gua,17970,guai,17964,g uan,17961,guang,17950,gui,17947,gun,17931,guo,17928,"PinMa = PinMa & "ha,17922,hai,17759,han,17752,hang,17733,hao,17730,he,17721,hei, 17703,hen,17701,heng,17697,hong,17692,hou,17683,hu,17676,hua,17496,huai,17487,h uan,17482,huang,17468,hui,17454,hun,17433,huo,17427,"PinMa = PinMa & "ji,17417,jia,17202,jian,17185,jiang,16983,jiao,16970,jie,16942,jin,16 915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,16664,jue,16657,jun,16647," PinMa = PinMa & "ka,16474,kai,16470,kan,16465,kang,16459,kao,16452,ke,16448,ken,16433,keng,16429,kong,16427,kou,16423,ku,16419,kua,16412,kuai,16407,kuan,16403,k uang,16401,kui,16393,kun,16220,kuo,16216,"PinMa = PinMa & "la,16212,lai,16205,lan,16202,lang,16187,lao,16180,le,16171,lei,1616 9,leng,16158,li,16155,lia,15959,lian,15958,liang,15944,liao,15933,lie,15920,lin,15915,lin g,15903,liu,15889,long,15878,lou,15707,lu,15701,lv,15681,luan,15667,lue,15661,lun,156 59,luo,15652,"PinMa = PinMa & "ma,15640,mai,15631,man,15625,mang,15454,mao,15448,me,15436, mei,15435,men,15419,meng,15416,mi,15408,mian,15394,miao,15385,mie,15377,min,15 375,ming,15369,miu,15363,mo,15362,mou,15183,mu,15180,"PinMa = PinMa & "na,15165,nai,15158,nan,15153,nang,15150,nao,15149,ne,15144,nei, 15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121,niao,15119,nie,15117,ni n,15110,ning,15109,niu,14941,nong,14937,nu,14933,nv,14930,nuan,14929,nue,14928,n uo,14926,"PinMa = PinMa & "o,14922,ou,14921,"PinMa = PinMa & "pa,14914,pai,14908,pan,14902,pang,14894,pao,14889,pei,14882,pe n,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pin,14668,ping,14663,p o,14654,pu,14645,"PinMa = PinMa & "qi,14630,qia,14594,qian,14429,qiang,14407,qiao,14399,qie,14384,qi n,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,14170,que,14159,qun,14151, "PinMa = PinMa & "ran,14149,rang,14145,rao,14140,re,14137,ren,14135,reng,14125,ri, 14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,14094,ruo,14092," PinMa = PinMa & "sa,14090,sai,14087,san,14083,sang,13917,sao,13914,se,13910,sen, 13907,seng,13906,sha,13905,shai,13896,shan,13894,shang,13878,shao,13870,she,1385 9,shen,13847,sheng,13831,shi,13658,shou,13611,shu,13601,shua,13406,shuai,13404,sh uan,13400,shuang,13398,shui,13395,shun,13391,shuo,13387,si,13383,song,13367,sou,1 3359,su,13356,suan,13343,sui,13340,sun,13329,suo,13326,"PinMa = PinMa & "ta,13318,tai,13147,tan,13138,tang,13120,tao,13107,te,13096,teng,1 3095,ti,13091,tian,13076,tiao,13068,tie,13063,ting,13060,tong,12888,tou,12875,tu,1287 1,tuan,12860,tui,12858,tun,12852,tuo,12849,"PinMa = PinMa & "wa,12838,wai,12831,wan,12829,wang,12812,wei,12802,wen,12607, weng,12597,wo,12594,wu,12585,"PinMa = PinMa & "xi,12556,xia,12359,xian,12346,xiang,12320,xiao,12300,xie,12120,xin, 12099,xing,12089,xiong,12074,xiu,12067,xu,12058,xuan,12039,xue,11867,xun,11861," PinMa = PinMa & "ya,11847,yan,11831,yang,11798,yao,11781,ye,11604,yi,11589,yin,1 1536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun, 11067,"PinMa = PinMa & "za,11055,zai,11052,zan,11045,zang,11041,zao,11038,ze,11024,zei,1 1020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zhang,10815,zhao,10800, zhe,10790,zhen,10780,zheng,10764,zhi,10587,zhong,10544,zhou,10533,zhu,10519,zhua, 10331,zhuai,10329,zhuan,10328,zhuang,10322,zhui,10315,zhun,10309,zhuo,10307,zi,10 296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,10256,zuo,10254" MyPinMa = Split(PinMa, ",")For i = 1 To Len(Hz)Temp = Asc(Mid(Hz, i, 1))If Temp < 0 ThenTemp = Abs(Temp)For j = 791 To 1 Step -2If Temp <= Val(MyPinMa(j)) ThenPinYin = PinYin & MyPinMa(j - 1) & " "Exit ForEnd IfNextEnd IfNextPinYin = Trim(PinYin)End Function5.代码输入完成后，关闭Visual Basic编辑窗口，返回Excel编辑状态；6.自定义函数就可以用了，如：选中D2单元格，输入公式：=PinYin(A2)。
Excel-VBA自定义函数取姓名的汉语拼音首字母
Excel-VBA自定义函数取姓名的汉语拼音首字母应用场景用函数取汉语拼音的首字母知识要点1：Option Compare语句，设置缺省的字符比较方法，只能在模块级使用，语法Option Compare {Binary | Text | Database} Option Compare Binary ,按排序顺序进行字符串比较A<B<C<a<b<?等，Option Compare text 不区分大小写进行字符串比较A=a2：将所有拼音首字母相同的第一个汉字取出来组成一个字符串，例如拉、洛、梁等，拉=la最靠前，取拉3：将参数中的每个字符与参照字符串中每个比较，统计参照字符串中有多少字符小于参数中的字符，如果有10个，通过chr函数，转换成第9个英文字符J，非汉字保持不变Option Compare Text '比较方式为text'声明函数PY，有一个必选参数，为特引用的单元格或者字符串Function PY(Rng As Range)Application.Volatile '声明为易失性函数Dim i As Integer, k As Integer, Str As StringIf Len(Rng) = 0 Then PY = '': Exit Function '如果引用单元格空白，则返回空文本If Not IsNumeric(Rng) Then '如果不是数值Str = WorksheetFunction.Trim(Rng) '去除空格格后，赋予变量strFor i = 1 To Len(Str) '遍历所有字符If Mid(Str, i, 1) < '啊' Then '如果小于啊，表示不是汉字PY = PY & Mid(Str, i, 1) '保留原字符不变Elsek = 1'将字符串中每个字符与参数中的字符进行比较，统计字符串中大于参数中的字符的个数，得到变量 kDo Until Mid$('八嚓哒妸发旮铪讥讥咔垃妈拿哦妑七然仨他哇哇哇夕丫匝咗', k, 1) > Mid$(Str, i, 1)k = k 1LoopPY = PY & Chr(64 k) '根据K值得到大写的英文字母(chr(65)为第一个字母AEnd IfNextEnd IfEnd Function。
EXCEL中怎么把汉字变成拼音,并取拼音的第一个大写字母
打开Excel->工具->宏->Viaual Basic编辑器在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1"，点击在右边的空白栏中粘贴以下内容：Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -20319 To -20284: pinyin = "A"Case -20283 To -19776: pinyin = "B"Case -19775 To -19219: pinyin = "C"Case -19218 To -18711: pinyin = "D"Case -18710 To -18527: pinyin = "E"Case -18526 To -18240: pinyin = "F"Case -18239 To -17923: pinyin = "G"Case -17922 To -17418: pinyin = "H"Case -17417 To -16475: pinyin = "J"Case -16474 To -16213: pinyin = "K"Case -16212 To -15641: pinyin = "L"Case -15640 To -15166: pinyin = "M"Case -15165 To -14923: pinyin = "N"Case -14922 To -14915: pinyin = "O"Case -14914 To -14631: pinyin = "P"Case -14630 To -14150: pinyin = "Q"Case -14149 To -14091: pinyin = "R"Case -14090 To -13319: pinyin = "S"Case -13318 To -12839: pinyin = "T"Case -12838 To -12557: pinyin = "W"Case -12556 To -11848: pinyin = "X"Case -11847 To -11056: pinyin = "Y"Case -11055 To -2050: pinyin = "Z"Case Else: pinyin = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Function********复制到此结束,本行不复制*******现在转换函数已编写完成!关掉此编缉的窗口。
HR人士要掌握的EXCEL汉字转拼音方法及步骤(精品资料).doc
【最新整理，下载后即可编辑】HR人士要掌握的EXCEL汉字转拼音方法及步骤HR人士在工作中管理员工的数据，如果是在欧美外企，因为国外总部通常会需要将汉字员工的信息转换成拼音，将这些信息录入存储在人事系统中，或者以人事报表形式将拼音信息汇报给总部。
因为员工数据通常会用EXCEL统计，掌握EXCEL汉字转拼音，会大大提高办公效率。
最常用的操作是员工姓名，家属姓名汉字转拼音等。
下面以名字的转换为例子，介绍EXCEL中汉字转拼音方法的两个主要步骤：* 在EXCEL中设定汉字转拼音的自定义函数= Pinyin().* 通过EXCEL中自带函数与自己设定的汉字转拼音自定义函数组合，将目标汉字转换成拼音，再复制粘贴保存到合适位置。
Excel中设定自定义函数= Pinyin().的步骤：1.新建一个新的Excel文件，打开显示出工作表；2.按“Alt+F11”组合键，进入Visual Basic编辑状态；3.再按“F7”键，进入模块代码编辑状态；4.将下面的代码，复制粘贴到模块代码编辑中；Function PinYin(Hz As String)Dim PinMa As StringDim MyPinMa As VariantDim Temp As Integer, i As Integer, j As IntegerPinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292,"PinMa = PinMa &"ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,b en,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,19805,bu,19784,"PinMa = PinMa &"ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ce ng,19741,cha,19739,chai,19728,chan,19725,chang,19715,chao,195 40,che,19531,chen,19525,cheng,19515,chi,19500,chong,19484,cho u,19479,chu,19467,chuai,19289,chuan,19288,chuang,19281,chui,1 9275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,1 9242,cuan,19238,cui,19235,cun,19227,cuo,19224,"PinMa = PinMa &"da,19218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,d eng,19003,di,18996,dian,18977,diao,18961,die,18952,ding,18783,d iu,18774,dong,18773,dou,18763,du,18756,duan,18741,dui,18735,d un,18731,duo,18722,"PinMa = PinMa & "e,18710,en,18697,er,18696,"PinMa = PinMa &"fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo ,18448,fou,18447,fu,18446,"PinMa = PinMa &"ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei ,18184,gen,18183,geng,18181,gong,18012,gou,17997,gu,17988,gua ,17970,guai,17964,guan,17961,guang,17950,gui,17947,gun,17931,g uo,17928,"PinMa = PinMa &"ha,17922,hai,17759,han,17752,hang,17733,hao,17730,he,17721,h ei,17703,hen,17701,heng,17697,hong,17692,hou,17683,hu,17676, hua,17496,huai,17487,huan,17482,huang,17468,hui,17454,hun,17 433,huo,17427,"PinMa = PinMa &"ji,17417,jia,17202,jian,17185,jiang,16983,jiao,16970,jie,16942,jin,1 6915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,16664,jue,166 57,jun,16647,"PinMa = PinMa &"ka,16474,kai,16470,kan,16465,kang,16459,kao,16452,ke,16448,ke n,16433,keng,16429,kong,16427,kou,16423,ku,16419,kua,16412,k uai,16407,kuan,16403,kuang,16401,kui,16393,kun,16220,kuo,1621 6,"PinMa = PinMa &"la,16212,lai,16205,lan,16202,lang,16187,lao,16180,le,16171,lei,16 169,leng,16158,li,16155,lia,15959,lian,15958,liang,15944,liao,1593 3,lie,15920,lin,15915,ling,15903,liu,15889,long,15878,lou,15707,lu, 15701,lv,15681,luan,15667,lue,15661,lun,15659,luo,15652," PinMa = PinMa &"ma,15640,mai,15631,man,15625,mang,15454,mao,15448,me,154 36,mei,15435,men,15419,meng,15416,mi,15408,mian,15394,miao, 15385,mie,15377,min,15375,ming,15369,miu,15363,mo,15362,mo u,15183,mu,15180,"PinMa = PinMa &"na,15165,nai,15158,nan,15153,nang,15150,nao,15149,ne,15144,n ei,15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121, niao,15119,nie,15117,nin,15110,ning,15109,niu,14941,nong,14937 ,nu,14933,nv,14930,nuan,14929,nue,14928,nuo,14926,"PinMa = PinMa & "o,14922,ou,14921,"PinMa = PinMa &"pa,14914,pai,14908,pan,14902,pang,14894,pao,14889,pei,14882,p en,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pi n,14668,ping,14663,po,14654,pu,14645,"PinMa = PinMa &"qi,14630,qia,14594,qian,14429,qiang,14407,qiao,14399,qie,14384, qin,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,1417 0,que,14159,qun,14151,"PinMa = PinMa &"ran,14149,rang,14145,rao,14140,re,14137,ren,14135,reng,14125,ri ,14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,1 4094,ruo,14092,"PinMa = PinMa &"sa,14090,sai,14087,san,14083,sang,13917,sao,13914,se,13910,sen, 13907,seng,13906,sha,13905,shai,13896,shan,13894,shang,13878,s hao,13870,she,13859,shen,13847,sheng,13831,shi,13658,shou,136 11,shu,13601,shua,13406,shuai,13404,shuan,13400,shuang,13398,s hui,13395,shun,13391,shuo,13387,si,13383,song,13367,sou,13359, su,13356,suan,13343,sui,13340,sun,13329,suo,13326,"PinMa = PinMa &"ta,13318,tai,13147,tan,13138,tang,13120,tao,13107,te,13096,teng, 13095,ti,13091,tian,13076,tiao,13068,tie,13063,ting,13060,tong,12 888,tou,12875,tu,12871,tuan,12860,tui,12858,tun,12852,tuo,12849 ,"PinMa = PinMa &"wa,12838,wai,12831,wan,12829,wang,12812,wei,12802,wen,1260 7,weng,12597,wo,12594,wu,12585,"PinMa = PinMa &"xi,12556,xia,12359,xian,12346,xiang,12320,xiao,12300,xie,12120, xin,12099,xing,12089,xiong,12074,xiu,12067,xu,12058,xuan,12039 ,xue,11867,xun,11861,"PinMa = PinMa &"ya,11847,yan,11831,yang,11798,yao,11781,ye,11604,yi,11589,yin, 11536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan, 11097,yue,11077,yun,11067,"PinMa = PinMa &"za,11055,zai,11052,zan,11045,zang,11041,zao,11038,ze,11024,zei ,11020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zh ang,10815,zhao,10800,zhe,10790,zhen,10780,zheng,10764,zhi,105 87,zhong,10544,zhou,10533,zhu,10519,zhua,10331,zhuai,10329,z huan,10328,zhuang,10322,zhui,10315,zhun,10309,zhuo,10307,zi,1 0296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,1 0256,zuo,10254"MyPinMa = Split(PinMa, ",")For i = 1 To Len(Hz)Temp = Asc(Mid(Hz, i, 1))If Temp < 0 ThenTemp = Abs(Temp)For j = 791 To 1 Step -2If Temp <= Val(MyPinMa(j)) ThenPinYin = PinYin & MyPinMa(j - 1) & " "Exit ForEnd IfNextEnd IfNextPinYin = Trim(PinYin)End Function5. 点击右上角的模块编辑十字叉，关闭Visual Basic编辑窗口，再点击左上角的EXCEL图标，返回Excel编辑状态；自定义函数：=PinYin() 在EXCEL中就可以应用了。
excel_汉字转换成拼音简拼(只取首字母)
excel_汉字转换成拼音简拼(只取首字母)名称转换为首字母(VBA自定义函数)1，打开EXCEL2，按ALT+F11，单击插入-插入模块，在右窗口粘贴以下代码，直接关闭窗口3，输入公式=hztopy(A1)，填充所有单元格4，选择所有缩写，复制-粘贴特殊值(因为它是自定义函数，只有结果可以显示在当前文件中，而其他计算机显示错误是因为5.使用条件格式检索包含中文字符的单元格:首先单击一个单元格(设置为D5)。
条件格式-新格式规则-使用公式确定要格式化的单元格:在下面的对话框中输入公式= LEMB(D5)> LEN(D5)，自己设置格式，确认后退出，然后用单元格的格式刷所有单元格汉字外观的格式设置为6。
选择自定义排序-按“单元格颜色”排序-排序“无单元格颜色”和“底部”-确定7，手动将汉字改写成相应的字母8、检查复音字符注意:上述方法只能在当前文件夹中使用。
如何保存宏以便自定义函数可以在所有文件夹中使用？1.创建新的EXCEL2。
按ALT+F11，单击插入-插入模块，并在右侧窗口中粘贴以下代码。
直接关闭窗口3，另存为-其他格式-文件名更改为宏名，保存类型选择” EXCEL加载项(*，xlam)”，文件将自动保存在c: \ \用户\ \ 111 \ \ appdata \ \ road ing \ \ Microsoft \ \ addins文件夹中4.打开需要工作的EXL，然后单击开始-扩展选项-加载项-转到(G)...-选择上面保存的宏名-确定这样，在这台计算机的所有EXCEL中，可以直接使用自定义公式。
代码(只复制星号的内容)* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *函数Hz topy(hzpy As String)As String Dim hzstring As String，py String As StringDim hzpy sum As Integer，hzi As Integer，hzpyhex As Integer hzstring = Trim(hzpy)hzpy sum = Len(Trim(hzstring))py String = \For hzi = 1 To hzpy sumhzpyhex = \ Select Case hzpyhexCase & HB 案例&HB2C1至&HB4ED: pystring = pystring + \案例&HB4EE至&HB6E9: pystring = pystring + \案例&HB6EA至&HB7A1: pystring = pystring + \案例&HB7A2至&HB8C0: pystring = pystring + \案例&HB8C1至&HB9FD: pystring = pystring + \案例&HB9FE至&HBBF6: pystring = pystring + \案例&HBBF7至&HBFA5: pystring = pystring 案例&HC2E8至&HC4C2: pystring = pystring + \案例&HC4C3至&HC5B5: pystring = pystring + \案例&HC5B6至&HC5BD:pystring = pystring + \案例&HC5BE至&HC6D9: pystring = pystring + \案例&HC6DA至&HC8BA: pystring = pystring + \案例&HC8BB至&HC8F5: pystring = pystring + \案例&HC8F6至&HCBF9: pystring = pystringCase & HCDDA To & HCEF3:py string = py string+\ Case & HCEF4 To & HD1B 8:py string = py string+\ Case & HD1B 9 To & HD4D 0:py string = py string+\ Case & HD4D 1 To & HD7F 9:py string = py string+\ Case Elsepy string = py string+Mid(Hz string，hzi，1)结束选择下一个hztopy = pystring结束。
excel中汉字生拼音
excel中根据A列汉字自动在B列生成拼音字母的方法内容提要：本文以一个实例说明在excel中根据A列汉字自动在B列生成拼音字母的方法。
下图所示的excel表格，A列是姓名列，根据A列的汉字姓名，自动在B列和C列分别自动生成姓名列的拼音字母。
下面是小编整理出来的详细的操作步骤，分享给有需要的朋友。
第一步，由于Excel中没有现成的函数套用可以解决此问题，首先自定义一个函数。
方法如下：1. 运行“工具→宏→Visual Basic编辑器”命令（或者直接按“Alt+F11”组合键），进入V isual Basic编辑状态。
2. 运行“插入→模块”命令，插入一个新模块。
再双击插入的模块，进入模块代码编辑状态。
3. 将以下的代码输入其中，保存，关闭Visual Basic编辑窗口，返回Excel编辑状态。
Function PinYin(Hz As String)Dim PinMa As StringDim MyPinMa As VariantDim Temp As Integer, i As Integer, j As IntegerPinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292,"PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,be n,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,b o,19805,bu,19784,"PinMa = PinMa & "ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng, 19741,cha,19739,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,19525,cheng,19515,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19288, chuang,19281,chui,19275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,19 242,cuan,19238,cui,19235,cun,19227,cuo,19224,"PinMa = PinMa & "da,19218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,de ng,19003,di,18996,dian,18977,diao,18961,die,18952,ding,18783,diu,18774,dong,18773, dou,18763,du,18756,duan,18741,dui,18735,dun,18731,duo,18722,"PinMa = PinMa & "e,18710,en,18697,er,18696,"PinMa = PinMa & "fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo,1 8448,fou,18447,fu,18446,"PinMa = PinMa & "ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei, 18184,gen,18183,geng,18181,gong,18012,gou,17997,gu,17988,gua,17970,guai,17964,g uan,17961,guang,17950,gui,17947,gun,17931,guo,17928,"PinMa = PinMa & "ha,17922,hai,17759,han,17752,hang,17733,hao,17730,he,17721,hei, 17703,hen,17701,heng,17697,hong,17692,hou,17683,hu,17676,hua,17496,huai,17487,h uan,17482,huang,17468,hui,17454,hun,17433,huo,17427,"PinMa = PinMa & "ji,17417,jia,17202,jian,17185,jiang,16983,jiao,16970,jie,16942,jin,16 915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,16664,jue,16657,jun,16647,"PinMa = PinMa & "ka,16474,kai,16470,kan,16465,kang,16459,kao,16452,ke,16448,ken, 16433,keng,16429,kong,16427,kou,16423,ku,16419,kua,16412,kuai,16407,kuan,16403,k uang,16401,kui,16393,kun,16220,kuo,16216,"PinMa = PinMa & "la,16212,lai,16205,lan,16202,lang,16187,lao,16180,le,16171,lei,1616 9,leng,16158,li,16155,lia,15959,lian,15958,liang,15944,liao,15933,lie,15920,lin,15915,lin g,15903,liu,15889,long,15878,lou,15707,lu,15701,lv,15681,luan,15667,lue,15661,lun,156 59,luo,15652,"PinMa = PinMa & "ma,15640,mai,15631,man,15625,mang,15454,mao,15448,me,15436, mei,15435,men,15419,meng,15416,mi,15408,mian,15394,miao,15385,mie,15377,min,15 375,ming,15369,miu,15363,mo,15362,mou,15183,mu,15180,"PinMa = PinMa & "na,15165,nai,15158,nan,15153,nang,15150,nao,15149,ne,15144,nei, 15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121,niao,15119,nie,15117,ni n,15110,ning,15109,niu,14941,nong,14937,nu,14933,nv,14930,nuan,14929,nue,14928,n uo,14926,"PinMa = PinMa & "o,14922,ou,14921,"PinMa = PinMa & "pa,14914,pai,14908,pan,14902,pang,14894,pao,14889,pei,14882,pe n,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pin,14668,ping,14663,p o,14654,pu,14645,"PinMa = PinMa & "qi,14630,qia,14594,qian,14429,qiang,14407,qiao,14399,qie,14384,qi n,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,14170,que,14159,qun,14151, "PinMa = PinMa & "ran,14149,rang,14145,rao,14140,re,14137,ren,14135,reng,14125,ri, 14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,14094,ruo,14092,"PinMa = PinMa & "sa,14090,sai,14087,san,14083,sang,13917,sao,13914,se,13910,sen, 13907,seng,13906,sha,13905,shai,13896,shan,13894,shang,13878,shao,13870,she,1385 9,shen,13847,sheng,13831,shi,13658,shou,13611,shu,13601,shua,13406,shuai,13404,sh uan,13400,shuang,13398,shui,13395,shun,13391,shuo,13387,si,13383,song,13367,sou,1 3359,su,13356,suan,13343,sui,13340,sun,13329,suo,13326,"PinMa = PinMa & "ta,13318,tai,13147,tan,13138,tang,13120,tao,13107,te,13096,teng,1 3095,ti,13091,tian,13076,tiao,13068,tie,13063,ting,13060,tong,12888,tou,12875,tu,1287 1,tuan,12860,tui,12858,tun,12852,tuo,12849,"PinMa = PinMa & "wa,12838,wai,12831,wan,12829,wang,12812,wei,12802,wen,12607, weng,12597,wo,12594,wu,12585,"PinMa = PinMa & "xi,12556,xia,12359,xian,12346,xiang,12320,xiao,12300,xie,12120,xin, 12099,xing,12089,xiong,12074,xiu,12067,xu,12058,xuan,12039,xue,11867,xun,11861," PinMa = PinMa & "ya,11847,yan,11831,yang,11798,yao,11781,ye,11604,yi,11589,yin,1 1536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun, 11067,"PinMa = PinMa & "za,11055,zai,11052,zan,11045,zang,11041,zao,11038,ze,11024,zei,1 1020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zhang,10815,zhao,10800, zhe,10790,zhen,10780,zheng,10764,zhi,10587,zhong,10544,zhou,10533,zhu,10519,zhua, 10331,zhuai,10329,zhuan,10328,zhuang,10322,zhui,10315,zhun,10309,zhuo,10307,zi,10 296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,10256,zuo,10254" MyPinMa = Split(PinMa, ",")For i = 1 To Len(Hz)Temp = Asc(Mid(Hz, i, 1))If Temp < 0 ThenTemp = Abs(Temp)For j = 791 To 1 Step -2If Temp <= Val(MyPinMa(j)) ThenPinYin = PinYin & MyPinMa(j - 1) & " "Exit ForEnd IfNextEnd IfNextPinYin = Trim(PinYin)End Function第二步，在空白列，比如F2单元格，输入公式：=PinYin(A2)，就可以在F2单元格得到A2单元格汉字的拼音。
excel：汉字拼音首字母提取
方法一：VBA自定义函数"=hztopy（）"下面是一个VBA自定义函数，工具->宏->Visual Basic编辑器->，插入模块,在VBA编辑器里面->插入->模块->贴入下述代码，（按ALT＋F11，插入模块，在右面窗口中粘贴下面代码）那么，比如在A3中有"进退两难"，则在B3中输入"=hztopy（A3）"，则会得到"JTLN"。
不过此法有时候也会出错，比如"皓、鑫、婷、雯、奕"等，就无法提取汉字拼音首字母。
Function hztopy(hzpy As String) As StringDim hzstring As String, pystring As StringDim hzpysum As Integer, hzi As Integer, hzpyhex As Integerhzstring = Trim(hzpy)hzpysum = Len(Trim(hzstring))pystring = ""For hzi = 1 To hzpysumhzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))Select Case hzpyhexCase &HB0A1 To &HB0C4: pystring = pystring + "A"Case &HB0C5 To &HB2C0: pystring = pystring + "B"Case &HB2C1 To &HB4ED: pystring = pystring + "C"Case &HB4EE To &HB6E9: pystring = pystring + "D"Case &HB6EA To &HB7A1: pystring = pystring + "E"Case &HB7A2 To &HB8C0: pystring = pystring + "F"Case &HB8C1 To &HB9FD: pystring = pystring + "G"Case &HB9FE To &HBBF6: pystring = pystring + "H"Case &HBBF7 To &HBFA5: pystring = pystring + "J"Case &HBFA6 To &HC0AB: pystring = pystring + "K"Case &HC0AC To &HC2E7: pystring = pystring + "L"Case &HC2E8 To &HC4C2: pystring = pystring + "M"Case &HC4C3 To &HC5B5: pystring = pystring + "N"Case &HC5B6 To &HC5BD: pystring = pystring + "O"Case &HC5BE To &HC6D9: pystring = pystring + "P"Case &HC6DA To &HC8BA: pystring = pystring + "Q"Case &HC8BB To &HC8F5: pystring = pystring + "R"Case &HC8F6 To &HCBF9: pystring = pystring + "S"Case &HCBFA To &HCDD9: pystring = pystring + "T"Case &HEDC5: pystring = pystring + "T"Case &HCDDA To &HCEF3: pystring = pystring + "W"Case &HCEF4 To &HD1B8: pystring = pystring + "X"Case &HD1B9 To &HD4D0: pystring = pystring + "Y"Case &HD4D1 To &HD7F9: pystring = pystring + "Z"Case Elsepystring = pystring + Mid(hzstring, hzi, 1)End SelectNexthztopy = pystringEnd Function方法二：VBA自定义函数"=getpy()"下面是一个VBA自定义函数，工具->宏->Visual Basic编辑器->，插入模块,在VBA编辑器里面->插入->模块->贴入上述代码，那么。
EXCEL如何自动将姓名转换为拼音？
EXCEL如何自动将姓名转换为拼音？EXCEL如何自动将姓名转换为拼音，由于EXCEL中没有汉字自动转换拼音的功能，所以要在VBA中自定义一个转换函数。
具体操作步骤第一步，要把EXCEL文档转换为 XLSM格式，启用宏。
第二步，在文件，设置，自定义功能区中勾选开发工具。
第三步，打开VBA编辑器。
第四步，插入模块。
第五步，在模块中输入如下代码：Function pinyin(p As String) As String i = Asc(p)Select Case iCase -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 = 'dong ' Case -18526 To -18519: pinyin = 'fa ' Case -18518 To -18502: pinyin = 'fan ' Case -18501 To -18491: pinyin = 'fang ' Case -18490 To -18479: pinyin = 'fei ' Case -18478 To -18464: pinyin = 'fen ' Case -18463 To -18449: pinyin = 'feng 'Case -18448 To -18448: pinyin = 'fo ' Case -18447 To -18447: pinyin = 'fou ' Case -18446 To -18240: pinyin = 'fu ' Case -18239 To -18238: pinyin = 'ga ' Case -18237 To -18232: pinyin = 'gai ' Case -18231 To -18221: pinyin = 'gan ' Case -18220 To -18212: pinyin = 'gang ' Case -18211 To -18202: pinyin = 'gao ' Case -18201 To -18185: pinyin = 'ge ' Case -18184 To -18184: pinyin = 'gei ' Case -18183 To -18182: pinyin = 'gen ' Case -18181 To -18013: pinyin = 'geng ' Case -18012 To -17998: pinyin = 'gong ' Case -17997 To -17989: pinyin = 'gou ' Case -17988 To -17971: pinyin = 'gu ' Case -17970 To -17965: pinyin = 'gua ' Case -17964 To -17962: pinyin = 'guai ' Case -17961 To -17951: pinyin = 'guan ' Case -17950 To -17948: pinyin = 'guang ' Case -17947 To -17932: pinyin = 'gui ' Case -17931 To -17929: pinyin = 'gun ' Case -17928 To -17923: pinyin = 'guo ' Case -17922 To -17760: pinyin = 'ha ' Case -17759 To -17753: pinyin = 'hai ' Case -17752 To -17734: pinyin = 'han ' Case -17733 To -17731: pinyin = 'hang ' Case -17730 To -17722: pinyin = 'hao ' Case -17721 To -17704: pinyin = 'he ' Case -17703 To -17702: pinyin = 'hei ' Case -17701 To -17698: pinyin = 'hen 'Case -17697 To -17693: pinyin = 'heng ' Case -17692 To -17684: pinyin = 'hong ' Case -17683 To -17677: pinyin = 'hou ' Case -17676 To -17497: pinyin = 'hu ' Case -17496 To -17488: pinyin = 'hua ' Case -17487 To -17483: pinyin = 'huai ' Case -17482 To -17469: pinyin = 'huan ' Case -17468 To -17455: pinyin = 'huang ' Case -17454 To -17434: pinyin = 'hui ' Case -17433 To -17428: pinyin = 'hun ' Case -17427 To -17418: pinyin = 'huo ' Case -17417 To -17203: pinyin = 'ji ' Case -17202 To -17186: pinyin = 'jia ' Case -17185 To -16984: pinyin = 'jian ' Case -16983 To -16971: pinyin = 'jiang ' Case -16970 To -16943: pinyin = 'jiao ' Case -16942 To -16916: pinyin = 'jie ' Case -16915 To -16734: pinyin = 'jin ' Case -16733 To -16709: pinyin = 'jing ' Case -16708 To -16707: pinyin = 'jiong ' Case -16706 To -16690: pinyin = 'jiu ' Case -16689 To -16665: pinyin = 'ju ' Case -16664 To -16658: pinyin = 'juan ' Case -16657 To -16648: pinyin = 'jue ' Case -16647 To -16475: pinyin = 'jun ' Case -16474 To -16471: pinyin = 'ka ' Case -16470 To -16466: pinyin = 'kai ' Case -16465 To -16460: pinyin = 'kan ' Case -16459 To -16453: pinyin = 'kang ' Case -16452 To -16449: pinyin = 'kao 'Case -16448 To -16434: pinyin = 'ke ' Case -16433 To -16430: pinyin = 'ken ' Case -16429 To -16428: pinyin = 'keng ' Case -16427 To -16424: pinyin = 'kong ' Case -16423 To -16420: pinyin = 'kou ' Case -16419 To -16413: pinyin = 'ku ' Case -16412 To -16408: pinyin = 'kua ' Case -16407 To -16404: pinyin = 'kuai ' Case -16403 To -16402: pinyin = 'kuan ' Case -16401 To -16394: pinyin = 'kuang ' Case -16393 To -16221: pinyin = 'kui ' Case -16220 To -16217: pinyin = 'kun ' Case -16216 To -16213: pinyin = 'kuo ' Case -16212 To -16206: pinyin = 'la ' Case -16205 To -16203: pinyin = 'lai ' Case -16202 To -16188: pinyin = 'lan ' Case -16187 To -16181: pinyin = 'lang ' Case -16180 To -16172: pinyin = 'lao ' Case -16171 To -16170: pinyin = 'le ' Case -16169 To -16159: pinyin = 'lei ' Case -16158 To -16156: pinyin = 'leng ' Case -16155 To -15960: pinyin = 'li ' Case -15959 To -15959: pinyin = 'lia ' Case -15958 To -15945: pinyin = 'lian ' Case -15944 To -15934: pinyin = 'liang ' Case -15933 To -15921: pinyin = 'liao ' Case -15920 To -15916: pinyin = 'lie ' Case -15915 To -15904: pinyin = 'lin ' Case -15903 To -15890: pinyin = 'ling ' Case -15889 To -15879: pinyin = 'liu 'Case -15707 To -15702: pinyin = 'lou ' Case -15701 To -15682: pinyin = 'lu ' Case -15681 To -15668: pinyin = 'lv ' Case -15667 To -15662: pinyin = 'luan ' Case -15661 To -15660: pinyin = 'lue ' Case -15659 To -15653: pinyin = 'lun ' Case -15652 To -15641: pinyin = 'luo ' Case -15640 To -15632: pinyin = 'ma ' Case -15631 To -15626: pinyin = 'mai ' Case -15625 To -15455: pinyin = 'man ' Case -15454 To -15449: pinyin = 'mang ' Case -15448 To -15437: pinyin = 'mao ' Case -15436 To -15436: pinyin = 'me ' Case -15435 To -15420: pinyin = 'mei ' Case -15419 To -15417: pinyin = 'men ' Case -15416 To -15409: pinyin = 'meng ' Case -15408 To -15395: pinyin = 'mi ' Case -15394 To -15386: pinyin = 'mian ' Case -15385 To -15378: pinyin = 'miao ' Case -15377 To -15376: pinyin = 'mie ' Case -15375 To -15370: pinyin = 'min ' Case -15369 To -15364: pinyin = 'ming ' Case -15363 To -15363: pinyin = 'miu ' Case -15362 To -15184: pinyin = 'mo ' Case -15183 To -15181: pinyin = 'mou ' Case -15180 To -15166: pinyin = 'mu ' Case -15165 To -15159: pinyin = 'na ' Case -15158 To -15154: pinyin = 'nai ' Case -15153 To -15151: pinyin = 'nan 'Case -15149 To -15145: pinyin = 'nao ' Case -15144 To -15144: pinyin = 'ne ' Case -15143 To -15142: pinyin = 'nei ' Case -15141 To -15141: pinyin = 'nen ' Case -15140 To -15140: pinyin = 'neng ' Case -15139 To -15129: pinyin = 'ni ' Case -15128 To -15122: pinyin = 'nian ' Case -15121 To -15120: pinyin = 'niang ' Case -15119 To -15118: pinyin = 'niao ' Case -15117 To -15111: pinyin = 'nie ' Case -15110 To -15110: pinyin = 'nin ' Case -15109 To -14942: pinyin = 'ning ' Case -14941 To -14938: pinyin = 'niu ' Case -14937 To -14934: pinyin = 'nong ' Case -14933 To -14931: pinyin = 'nu ' Case -14930 To -14930: pinyin = 'nv ' Case -14929 To -14929: pinyin = 'nuan ' Case -14928 To -14927: pinyin = 'nue ' Case -14926 To -14923: pinyin = 'nuo ' Case -14922 To -14922: pinyin = 'o ' Case -14921 To -14915: pinyin = 'ou ' Case -14914 To -14909: pinyin = 'pa ' Case -14908 To -14903: pinyin = 'pai ' Case -14902 To -14895: pinyin = 'pan ' Case -14894 To -14890: pinyin = 'pang ' Case -14889 To -14883: pinyin = 'pao ' Case -14882 To -14874: pinyin = 'pei ' Case -14873 To -14872: pinyin = 'pen ' Case -14871 To -14858: pinyin = 'peng 'Case -14857 To -14679: pinyin = 'pi ' Case -14678 To -14675: pinyin = 'pian ' Case -14674 To -14671: pinyin = 'piao ' Case -14670 To -14669: pinyin = 'pie ' Case -14668 To -14664: pinyin = 'pin ' Case -14663 To -14655: pinyin = 'ping ' Case -14654 To -14646: pinyin = 'po ' Case -14645 To -14631: pinyin = 'pu ' Case -14630 To -14595: pinyin = 'qi ' Case -14594 To -14430: pinyin = 'qia ' Case -14429 To -14408: pinyin = 'qian ' Case -14407 To -14400: pinyin = 'qiang ' Case -14399 To -14385: pinyin = 'qiao ' Case -14384 To -14380: pinyin = 'qie ' Case -14379 To -14369: pinyin = 'qin ' Case -14368 To -14356: pinyin = 'qing ' Case -14355 To -14354: pinyin = 'qiong ' Case -14353 To -14346: pinyin = 'qiu ' Case -14345 To -14171: pinyin = 'qu ' Case -14170 To -14160: pinyin = 'quan ' Case -14159 To -14152: pinyin = 'que ' Case -14151 To -14150: pinyin = 'qun ' Case -14149 To -14146: pinyin = 'ran ' Case -14145 To -14141: pinyin = 'rang ' Case -14140 To -14138: pinyin = 'rao ' Case -14137 To -14136: pinyin = 're ' Case -14135 To -14126: pinyin = 'ren ' Case -14125 To -14124: pinyin = 'reng ' Case -14123 To -14123: pinyin = 'ri ' Case -14122 To -14113: pinyin = 'rong 'Case -14112 To -14110: pinyin = 'rou ' Case -14109 To -14100: pinyin = 'ru ' Case -14099 To -14098: pinyin = 'ruan ' Case -14097 To -14095: pinyin = 'rui ' Case -14094 To -14093: pinyin = 'run ' Case -14092 To -14091: pinyin = 'ruo ' Case -14090 To -14088: pinyin = 'sa ' Case -14087 To -14084: pinyin = 'sai ' Case -14083 To -13918: pinyin = 'san ' Case -13917 To -13915: pinyin = 'sang ' Case -13914 To -13911: pinyin = 'sao ' Case -13910 To -13908: pinyin = 'se ' Case -13907 To -13907: pinyin = 'sen ' Case -13906 To -13906: pinyin = 'seng ' Case -13905 To -13897: pinyin = 'sha ' Case -13896 To -13895: pinyin = 'shai ' Case -13894 To -13879: pinyin = 'shan ' Case -13878 To -13871: pinyin = 'shang ' Case -13870 To -13860: pinyin = 'shao ' Case -13859 To -13848: pinyin = 'she ' Case -13847 To -13832: pinyin = 'shen ' Case -13831 To -13659: pinyin = 'sheng ' Case -13658 To -13612: pinyin = 'shi ' Case -13611 To -13602: pinyin = 'shou ' Case -13601 To -13407: pinyin = 'shu ' Case -13406 To -13405: pinyin = 'shua ' Case -13404 To -13401: pinyin = 'shuai ' Case -13400 To -13399: pinyin = 'shuan ' Case -13398 To -13396: pinyin = 'shuang ' Case -13395 To -13392: pinyin = 'shui 'Case -13391 To -13388: pinyin = 'shun ' Case -13387 To -13384: pinyin = 'shuo ' Case -13383 To -13368: pinyin = 'si ' Case -13367 To -13360: pinyin = 'song ' Case -13359 To -13357: pinyin = 'sou ' Case -13356 To -13344: pinyin = 'su ' Case -13343 To -13341: pinyin = 'suan ' Case -13340 To -13330: pinyin = 'sui ' Case -13329 To -13327: pinyin = 'sun ' Case -13326 To -13319: pinyin = 'suo ' Case -13318 To -13148: pinyin = 'ta ' Case -13147 To -13139: pinyin = 'tai ' Case -13138 To -13121: pinyin = 'tan ' Case -13120 To -13108: pinyin = 'tang ' Case -13107 To -13097: pinyin = 'tao ' Case -13096 To -13096: pinyin = 'te ' Case -13095 To -13092: pinyin = 'teng ' Case -13091 To -13077: pinyin = 'ti ' Case -13076 To -13069: pinyin = 'tian ' Case -13068 To -13064: pinyin = 'tiao ' Case -13063 To -13061: pinyin = 'tie ' Case -13060 To -12889: pinyin = 'ting ' Case -12888 To -12876: pinyin = 'tong ' Case -12875 To -12872: pinyin = 'tou ' Case -12871 To -12861: pinyin = 'tu ' Case -12860 To -12859: pinyin = 'tuan ' Case -12858 To -12853: pinyin = 'tui ' Case -12852 To -12850: pinyin = 'tun ' Case -12849 To -12839: pinyin = 'tuo ' Case -12838 To -12832: pinyin = 'wa 'Case -12829 To -12813: pinyin = 'wan ' Case -12812 To -12803: pinyin = 'wang ' Case -12802 To -12608: pinyin = 'wei ' Case -12607 To -12598: pinyin = 'wen ' Case -12597 To -12595: pinyin = 'weng ' Case -12594 To -12586: pinyin = 'wo ' Case -12585 To -12557: pinyin = 'wu ' Case -12556 To -12360: pinyin = 'xi ' Case -12359 To -12347: pinyin = 'xia ' Case -12346 To -12321: pinyin = 'xian ' Case -12320 To -12301: pinyin = 'xiang ' Case -12300 To -12121: pinyin = 'xiao ' Case -12120 To -12100: pinyin = 'xie ' Case -12099 To -12090: pinyin = 'xin ' Case -12089 To -12075: pinyin = 'xing ' Case -12074 To -12068: pinyin = 'xiong ' Case -12067 To -12059: pinyin = 'xiu ' Case -12058 To -12040: pinyin = 'xu ' Case -12039 To -11868: pinyin = 'xuan ' Case -11867 To -11862: pinyin = 'xue ' Case -11861 To -11848: pinyin = 'xun ' Case -11847 To -11832: pinyin = 'ya ' Case -11831 To -11799: pinyin = 'yan ' Case -11798 To -11782: pinyin = 'yang ' Case -11781 To -11605: pinyin = 'yao ' Case -11604 To -11590: pinyin = 'ye ' Case -11589 To -11537: pinyin = 'yi ' Case -11536 To -11359: pinyin = 'yin ' Case -11358 To -11341: pinyin = 'ying 'Case -11339 To -11325: pinyin = 'yong ' Case -11324 To -11304: pinyin = 'you ' Case -11303 To -11098: pinyin = 'yu ' Case -11097 To -11078: pinyin = 'yuan ' Case -11077 To -11068: pinyin = 'yue ' Case -11067 To -11056: pinyin = 'yun ' 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 = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1)) Next iEnd Function第六步，输入拼音转换函数。
Excel拼音转汉字宏函数完整版
Function pinyin(p As String) As Stringpinyin = pinyin1(p)If pinyin = p Then pinyin = pinyin2(p)If pinyin = p Then pinyin = pinyin3(p)If pinyin = p Then pinyin = pinyin4(p)If pinyin = p Then pinyin = pinyin5(p)End FunctionFunction pinyin1(p As String) As StringDim strPY As StringSelect Case pCase "啊", "阿", "吖", "嗄", "錒", "锕": strPY = "a"Case "乂", "伌", "僾", "凒", "剴", "叆", "啀", "嘊", "噯", "堨", "塧", "壒", "娾", "嬡", "嵦", "愛", "懓", "懝", "敱", "敳", _"昹", "曖", "欬", "毐", "溰", "溾", "濭", "焥", "璦", "皚", "皧", "埃", "挨", "哎", "唉", "哀", "皑", "癌", "蔼", "矮", _"艾", "碍", "爱", "隘", "瞹", "硋", "礙", "薆", "藹", "譪", "譺", "賹", "躷", "捱", "嗳", "嗌", "銰", "鎄", "嫒", "鑀", _"瑷", "閡", "暧", "隑", "靄", "靉", "砹", "锿", "餲", "馤", "騃", "鯦", "霭", "鱫", "鴱": strPY = "ai"Case "侒", "儑", "厰", "唵", "啽", "垵", "垾", "堓", "婩", "媕", "屽", "峖", "晻", "洝", "玵", "痷", "鞍", "氨", "安", "俺", _"按", "暗", "岸", "胺", "案", "盦", "盫", "碪", "罯", "腤", "荌", "萻", "葊", "蓭", "誝", "諳", "豻", "貋", "谙", "埯", _"揞", "遃", "犴", "庵", "銨", "錌", "桉", "闇", "隌", "雸", "鞌", "韽", "頇", "铵", "鹌", "馣", "鮟", "黯", "鴳", "鵪", _"鶕": strPY = "an"Case "卬", "岇", "昻", "枊", "肮", "昂", "盎", "醠", "骯": strPY = "ang"Case "厫", "嗸", "嚻", "垇", "墺", "奡", "奧", "媼", "嫯", "岰", "嶅", "嶴", "慠", "扷", "抝", "摮", "擙", "柪", "梎", "泑", _"滶", "澚", "熝", "爊", "獓", "璈", "凹", "敖", "熬", "翱", "袄", "傲", "奥", "懊", "澳", "眑", "磝", "翶", "翺", "芺", _"蔜", "蝹", "襖", "謷", "謸", "坳", "軪", "郩", "嗷", "岙", "廒", "遨", "媪", "骜", "鏕", "镺", "獒", "隞", "聱", "驁", _"螯", "鰲", "鏊", "鳌", "鏖", "鴁", "鷔", "鼇": strPY = "ao"Case "丷", "仈", "叐", "哵", "坺", "墢", "壩", "夿", "妭", "峇", "弝", "抜", "朳", "柭", "欛", "炦", "犮", "玐", "癹", "皅", _"芭", "捌", "扒", "叭", "吧", "笆", "八", "疤", "巴", "拔", "跋", "靶", "把", "坝", "霸", "罢", "爸", "矲", "紦", "罷", _"羓", "胈", "蚆", "覇", "詙", "豝", "茇", "軷", "菝", "釛", "釟", "岜", "鈀", "灞", "钯", "颰", "魞", "粑", "鮁", "鮊", _"鲅", "鲃", "鲌", "魃", "鼥": strPY = "ba"Case "庍", "拝", "挀", "擺", "敗", "栢", "白", "柏", "百", "摆", "佰", "败", "拜", "稗", "竡", "粨", "粺", "絔", "薭", "襬", _"贁", "捭", "掰", "鞁", "韛": strPY = "bai"Case "姅", "岅", "怑", "搫", "攽", "斒", "昄", "朌", "湴", "瓪", "斑", "班", "搬", "扳", "般", "颁", "板", "版", "扮", "拌", _"伴", "瓣", "半", "办", "绊", "秚", "籓", "粄", "絆", "肦", "蝂", "螁", "螌", "褩", "覂", "阪", "跘", "坂", "辦", "辬", _"鈑", "鉡", "靽", "頒", "钣", "瘢", "癍", "魬", "舨", "鳻": strPY = "ban"Case "垹", "埲", "塝", "嫎", "峀", "幇", "幚", "幫", "徬", "捠", "棓", "牓", "玤", "邦", "帮", "梆", "榜", "膀", "绑", "棒", _"蚌", "镑", "傍", "谤", "硥", "稖", "綁", "縍", "艕", "蚄", "蛖", "蜯", "謗", "蒡", "邫", "浜", "鎊", "鞤", "騯": strPY = "bang"Case "佨", "儤", "剝", "勽", "堢", "報", "媬", "嫑", "寚", "寳", "寶", "忁", "怉", "曓", "枹", "珤", "苞", "胞", "包", "褒", _"剥", "薄", "雹", "保", "堡", "饱", "宝", "抱", "报", "暴", "豹", "鲍", "爆", "窇", "笣", "緥", "瀑", "菢", "蕔", "藵", _"虣", "蚫", "袌", "裦", "襃", "賲", "勹", "葆", "鉋", "宀", "孢", "鑤", "铇", "闁", "靌", "靤", "煲", "飹", "飽", "鸨", _"駂", "褓", "骲", "髱", "鮑", "龅", "鳵", "鴇", "齙": strPY = "bao"Case "萡": strPY = "be"Case "俻", "偝", "偹", "備", "僃", "哱", "唄", "喺", "垻", "愂", "憊", "揹", "昁", "杮", "柸", "桮", "梖", "棑", "椑", "牬", _"犕", "狽", "珼", "琲", "盃", "杯", "碑", "悲", "卑", "北", "辈", "背", "贝", "钡", "倍", "狈", "备", "惫", "焙", "被", _"禙", "糒", "苝", "藣", "蛽", "誖", "諀", "貝", "陂", "邶", "軰", "輩", "蓓", "呗", "鄁", "鉳", "悖", "鋇", "錍", "鐴", _"韝", "碚", "鹎", "褙", "骳", "鐾", "鞴", "鵯": strPY = "bei"Case "倴", "喯", "夲", "奙", "捹", "撪", "桳", "楍", "泍", "渀", "炃", "燌", "犇", "獖", "奔", "苯", "本", "笨", "夯", "翉", _"賁", "畚", "坌", "輽", "逩", "錛", "贲", "锛": strPY = "ben"Case "伻", "傰", "嗙", "埄", "塴", "奟", "嵭", "挷", "揼", "搒", "琣", "琫", "痭", "崩", "绷", "甭", "泵", "蹦", "迸", "祊", _"絣", "綳", "繃", "菶", "跰", "逬", "嘣", "錋", "鏰", "镚", "閍", "甏", "鞛": strPY = "beng"Case "佊", "佖", "偪", "匂", "咇", "啚", "嗶", "坒", "堛", "夶", "奰", "妼", "娝", "嬶", "屄", "崥", "幣", "幤", "廦", "弻", _"彃", "怭", "怶", "悂", "愊", "斃", "旇", "朼", "枈", "柀", "柲", "梐", "楅", "毴", "沘", "湢", "滭", "潷", "煏", "熚", _"獘", "獙", "珌", "畁", "畐", "畢", "疕", "疪", "痺", "皀", "皕", "逼", "鼻", "比", "鄙", "笔", "彼", "碧", "蓖", "蔽", _"毕", "毙", "毖", "币", "庇", "痹", "闭", "敝", "弊", "必", "壁", "臂", "避", "陛", "祕", "禆", "稫", "笓", "筆", "箃", _"箄", "箆", "篳", "粃", "粊", "綼", "縪", "繴", "罼", "翍", "聛", "肶", "肸", "胇", "腷", "苾", "萞", "蓽", "蜌", "螕", _"袐", "襅", "襣", "觱", "詖", "诐", "豍", "貏", "匕", "貱", "俾", "贔", "赑", "踾", "蹕", "躃", "躄", "荜", "荸", "萆", _"薜", "邲", "吡", "哔", "鄨", "鄪", "狴", "鈚", "庳", "鉍", "愎", "滗", "濞", "弼", "妣", "鎞", "婢","嬖", "鏎", "璧", _"閇", "閈", "閉", "閟", "闬", "鞞", "鞸", "韠", "畀", "铋", "飶", "秕", "饆", "馝", "駜", "裨", "驆", "髲", "筚", "箅", _"篦", "魓", "魮", "舭", "襞", "鮅", "鮩", "跸", "鰏", "鲾", "髀", "鴓", "鵖", "鶝", "鷝", "鷩", "鸊", "鼊": strPY = "bi"Case "変", "峅", "惼", "抃", "揙", "昪", "汳", "炞", "牑", "猵", "獱", "甂", "疺", "鞭", "边", "编", "贬", "扁", "便", "变", _"卞", "辨", "辩", "辫", "遍", "稨", "箯", "籩", "糄", "編", "緶", "臱", "艑", "萹", "藊", "覍", "覵", "變", "匾", "貶", _"弁", "苄", "辡", "辧", "辮", "辯", "邉", "邊", "釆", "忭", "汴", "鍽", "缏", "閞", "煸", "砭", "碥", "窆", "褊", "蝙", _"笾", "鯾", "鯿", "鳊", "鴘", "鶣": strPY = "bian"Case "俵", "僄", "儦", "墂", "幖", "徱", "摽", "標", "檦", "淲", "滮", "瀌", "熛", "爂", "猋", "标", "彪", "膘", "表", "穮", _"篻", "脿", "膔", "臕", "蔈", "藨", "褾", "諘", "謤", "贆", "錶", "婊", "骠", "鏢", "鑣", "飑", "飙", "飚", "颷", "飆", _"飇", "飈", "飊", "镖", "镳", "瘭", "裱", "驃", "驫", "骉", "鰾", "鳔", "髟", "麃": strPY = "biao" Case "別", "彆", "徶", "癟", "鳖", "憋", "别", "瘪", "穪", "莂", "虌", "蛂", "蟞", "襒", "蹩", "鱉", "鼈", "龞": strPY = "bie"Case "儐", "擯", "椕", "檳", "殯", "氞", "汃", "濱", "濵", "瀕", "瑸", "璸", "彬", "斌", "濒", "滨", "宾", "摈", "砏", "繽", _"臏", "虨", "蠙", "訜", "豩", "賓", "賔", "傧", "邠", "豳", "缤", "玢", "鑌", "槟", "殡", "膑", "霦", "顮", "镔", "髕", _"髩", "鬂", "鬢", "髌", "鬓": strPY = "bin"Case "並", "仌", "仒", "併", "倂", "偋", "傡", "垪", "寎", "幷", "庰", "怲", "抦", "掤", "昞", "昺", "栟", "栤", "梹", "棅", _"氷", "燷", "琕", "癛", "癝", "眪", "兵", "冰", "柄", "丙", "秉", "饼", "炳", "病", "并", "稟", "窉", "竝", "綆", "苪", _"蛃", "誁", "禀", "邴", "摒", "鈵", "鉼", "鋲", "陃", "靐", "鞆", "餅", "餠", "疒": strPY = "bing" Case "仢", "侼", "僠", "僰", "噃", "嚗", "壆", "孹", "嶓", "帗", "彴", "愽", "懪", "挬", "撥", "桲", "檘", "欂", "浡", "淿", _"湐", "煿", "牔", "犦", "犻", "狛", "猼", "瓝", "瓟", "癶", "盋", "玻", "菠", "播", "拨", "钵", "波", "博", "勃", "搏", _"铂", "箔", "伯", "帛", "舶", "脖", "膊", "渤", "泊", "驳", "卜", "碆", "磻", "礡", "秡", "穛", "箥", "簙", "糪", "紴", _"缽", "肑", "胉", "艊", "苩", "茀", "葧", "蔔", "蔢", "蘗", "蚾", "袚", "袯", "袹", "襎", "襮", "譒", "豰", "孛", "亳", _"蹳", "郣", "啵", "鈸", "饽", "鉑", "鉢", "鋍", "鎛", "鑮", "镈", "檗", "擘", "礴", "钹", "餑", "餺", "鹁", "馎", "馛", _"馞", "駁", "駮", "驋", "髆", "髉", "簸", "趵", "跛", "踣", "鱍", "鵓": strPY = "bo"Case "佈", "勏", "吥", "咘", "埗", "尃", "峬", "庯", "廍", "悑", "抪", "捗", "擈", "柨", "歨", "歩", "獛", "捕", "哺", "补", _"埠", "不", "布", "步", "簿", "部", "怖", "秿", "箁", "篰", "荹", "蔀", "補", "誧", "踄", "輹", "轐", "卟", "郶", "鈈", _"鈽", "逋", "陠", "瓿", "晡", "钚", "钸", "餔", "餢", "鯆", "醭", "鳪", "鵏", "鸔": strPY = "bu" Case "攃", "擦", "礸", "遪", "礤": strPY = "ca"Case "倸", "偲", "埰", "婇", "寀", "戝", "採", "揌", "棌", "猜", "裁", "材", "才", "财", "睬", "踩", "采", "彩", "菜", "蔡", _"綵", "縩", "財", "跴": strPY = "cai"Case "乲": strPY = "cal"Case "傪", "儏", "參", "喰", "噆", "嬠", "嵾", "慘", "慙", "慚", "憯", "殘", "湌", "澯", "燦", "爘", "餐", "参", "蚕", "残", _"惭", "惨", "灿", "薒", "蝅", "蠶", "蠺", "謲", "骖", "璨", "飡", "飱", "驂", "粲", "黪", "黲": strPY = "can"Case "倉", "凔", "嵢", "欌", "滄", "濸", "獊", "苍", "舱", "仓", "沧", "藏", "罉", "艙", "蒼", "螥", "賶", "鑶": strPY = "cang"Case "嶆", "愺", "慅", "慒", "懆", "撡", "曺", "操", "糙", "槽", "曹", "草", "肏", "艸", "蓸", "褿", "襙", "艹", "鄵", "嘈", _"漕", "鏪", "騲", "螬", "艚": strPY = "cao"Case "側", "冊", "厠", "墄", "廁", "惻", "憡", "拺", "敇", "測", "厕", "策", "侧", "册", "测", "筞", "箣", "粣", "荝", "萗", _"萴", "蓛", "刂", "恻": strPY = "ce"Case "梣", "硶", "膥", "岑", "涔": strPY = "cen"Case "層", "层", "蹭", "竲", "曾", "噌", "驓": strPY = "ceng"Case "侘", "偛", "剎", "嗏", "垞", "奼", "嵖", "扠", "査", "疀", "插", "叉", "茬", "茶", "查", "碴", "搽", "察", "岔", "差", _"诧", "紁", "肞", "臿", "艖", "芆", "褨", "訍", "詫", "嚓", "猹", "銟", "汊", "鍤", "鎈", "姹", "鑔", "杈", "槎", "檫", _"靫", "锸", "镲", "衩": strPY = "cha"Case "儕", "喍", "囆", "犲", "拆", "柴", "豺", "祡", "蠆", "袃", "侪", "釵", "钗", "瘥", "虿": strPY = "chai"Case "丳", "儃", "儳", "刬", "剗", "剷", "劖", "嚵", "囅", "墠", "壥", "嬋", "嵼", "巉", "幝", "幨", "懴", "懺", "摌", "摲", _"摻", "攙", "旵", "梴", "棎", "毚", "浐", "湹", "滻", "潹", "瀍", "瀺", "灛", "煘", "燀", "獑", "產", "産", "搀", "掺", _"蝉", "馋", "谗", "缠", "铲", "产", "阐", "颤", "硟", "磛", "禪", "簅", "緾", "繟", "纏", "纒", "脠", "艬", "蕆", "蟬", _"裧", "襜", "覘", "誗", "諂", "讇", "讒", "冁", "谄", "蒇", "辿", "鄽", "酁", "醦", "廛", "忏", "鋋", "鋓", "潺", "澶", _"孱", "羼", "婵", "骣", "鏟", "鑱", "镵", "閳", "闡", "觇", "禅", "韂", "顫", "饞", "蟾", "躔": strPY = "chan"Case "倀", "僘", "償", "兏", "嘗", "嚐", "場", "尙", "廠", "悵", "晿", "暢", "棖", "椙", "淐", "玚", "琩", "瑺", "瓺", "甞", _"畼", "昌", "猖", "场", "尝", "常", "偿", "肠", "厂", "敞", "畅", "唱", "倡", "腸", "膓", "萇", "蟐", "裮", "誯", "伥", _"鬯", "苌", "菖", "徜", "怅", "惝", "阊", "鋹", "鋿", "錩", "娼", "嫦", "鏛", "锠", "閶", "昶", "氅", "韔", "鯧", "鲳", _"鱨", "鲿", "鼚": strPY = "chang"Case "仦", "巐", "巣", "弨", "樔", "欩", "漅", "煼", "牊", "眧", "超", "抄", "钞", "朝", "嘲", "潮", "巢", "吵", "炒", "绰", _"窲", "粆", "綤", "綽", "繛", "罺", "觘", "趠", "轈", "鄛", "鈔", "怊", "晁", "焯", "耖", "麨": strPY = "chao"Case "偖", "勶", "唓", "喢", "徹", "撦", "烢", "爡", "瞮", "硨", "硩", "车", "扯", "撤", "掣", "彻", "澈", "聅", "莗", "蛼", _"坼", "車", "迠", "砗", "頙": strPY = "che"Case "傖", "嚫", "塵", "墋", "夦", "揨", "敐", "曟", "棽", "樄", "瀋", "烥", "煁", "疢", "瘎", "郴", "臣", "辰", "尘", "晨", _"忱", "沉", "陈", "趁", "衬", "磣", "綝", "茞", "莐", "蔯", "薼", "螴", "襯", "訦", "諃", "諶", "讖", "伧", "賝", "贂", _"趻", "谌", "谶", "踸", "抻", "嗔", "宸", "琛", "榇", "陳", "霃", "碜", "龀", "鷐", "麎", "齓", "齔": strPY = "chen"Case "乗", "偁", "堘", "塖", "娍", "宬", "峸", "庱", "悜", "憆", "懲", "挰", "掁", "摚", "撐", "棦", "椉", "橕", "檉", "檙", _"泟", "洆", "浾", "溗", "瀓", "爯", "牚", "珹", "琤", "畻", "睈", "碀", "撑", "称", "城", "橙", "成", "呈", "乘", "程", _"惩", "澄", "诚", "承", "逞", "骋", "秤", "稱", "窚", "竀", "筬", "絾", "緽", "脭", "荿", "蟶", "誠", "丞", "赪", "赬", _"埕", "郕", "鋮", "鏳", "鏿", "鐺", "枨", "柽", "阷", "塍", "靗", "頳", "瞠", "铖", "铛", "饓", "裎", "騁", "騬", "蛏", _"鯎", "酲": strPY = "cheng"Case "侙", "勅", "卶", "叺", "呎", "垑", "妛", "岻", "彨", "彲", "恜", "恥", "慗", "憏", "懘", "抶", "摛", "摴", "攡", "杘", _"欼", "歗", "歯", "淔", "湁", "灻", "烾", "熾", "瓻", "痓", "痸", "癡", "瞝", "吃", "痴", "持", "池", "迟", "弛", "驰", _"耻", "齿", "侈", "尺", "赤", "翅", "斥", "炽", "竾", "筂", "翄", "翤", "翨", "肔", "胣", "腟", "蚇", "蚳", "裭", "訵", _"謘", "貾", "傺", "趩", "墀", "茌", "遅", "遫", "遲", "叱", "哧", "啻", "嗤", "彳", "饬", "鉓", "鉹", "媸", "雴", "敕", _"眵", "飭", "餝", "饎", "鸱", "馳", "瘛", "褫", "蚩", "螭", "笞", "篪", "豉", "踟", "魑", "鴟", "鵄", "鶒", "鷘", "麶", _"黐", "齒", "齝": strPY = "chi"Case "傭", "嘃", "埫", "寵", "崈", "憃", "摏", "沖", "浺", "珫", "充", "冲", "虫", "崇", "宠", "罿", "翀", "蟲", "衝", "褈", _"蹖", "茺", "忡", "銃", "憧", "隀", "铳", "舂", "艟": strPY = "chong"Case "丒", "侴", "儔", "吜", "嬦", "懤", "掫", "擣", "杽", "栦", "殠", "燽", "犨", "犫", "疇", "皗", "矁", "抽", "酬", "畴", _"踌", "稠", "愁", "筹", "仇", "绸", "瞅", "丑", "臭", "篘", "籌", "紬", "絒", "綢", "菗", "讎", "讐", "俦", "跾", "躊", _"遚", "酧", "醜", "惆", "雔", "瘳", "魗", "雠": strPY = "chou"Case "俶", "傗", "儊", "儲", "処", "媰", "岀", "幮", "廚", "懨", "拀", "敊", "斶", "椘", "榋", "橻", "檚", "櫉", "櫥", "歜", _"濋", "犓", "珿", "琡", "璴", "初", "出", "橱", "厨", "躇", "锄", "雏", "滁", "除", "楚", "础", "储",。
excel_汉字转换成拼音简拼(只取首字母)
名字转换为首字母（VBA自定义函数）1、打开EXCEL2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口3、输入公式=hztopy(A1)，填充所有单元格4、选中所有缩写，复制-选择性粘贴-数值（因为是自定义函数，只有在当前文件中可以显示结果，其他电脑因没有公式而显示错误）5、使用条件格式，检索出含有汉字的单元格:先点中一个单元格（设为D5），条件格式-新建格式规则-使用公式确定要设置格式的单元格：在下面对话框中输入公式 =LENB(D5)>LEN(D5)，自己设置格式，确定后退出，再把该单元格的格式用格式刷刷所有单元格。
有汉字的出现所设格式6、选择自定义排序-排序依据“单元格颜色”-次序“无单元格颜色”“在底端”-确定。
7、将汉字手动改写为相应的字母。
8、核对多音字。
注：以上方法只可以在当前文件夹使用，如何将宏保存，使自定义函数可以在所有文件夹中使用1、新建一个EXCEL2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口3、另存为-其他格式-文件名改为宏名称、保存类型选择“EXCEL加载宏（*,xlam）”,文件将自动保存在C:\Users\111\AppData\Roaming\Microsoft\AddIns文件夹。
4、打开需要工作的EXLEL，点击开始-EXCEL选项-加载项-转到（G）...-选中上面保存的宏名称-确定。
这样，在该电脑的所有EXCEL中，都可以直接使用自定义公式。
代码(只复制星号内的内容)******************************************************************************* Function hztopy(hzpy As String) As StringDim hzstring As String, pystring As StringDim hzpysum As Integer, hzi As Integer, hzpyhex As Integerhzstring = Trim(hzpy)hzpysum = Len(Trim(hzstring))pystring = ""For hzi = 1 To hzpysumhzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))Select Case hzpyhexCase &HB0A1 To &HB0C4: pystring = pystring + "A"Case &HB0C5 To &HB2C0: pystring = pystring + "B"Case &HB2C1 To &HB4ED: pystring = pystring + "C"Case &HB4EE To &HB6E9: pystring = pystring + "D"Case &HB6EA To &HB7A1: pystring = pystring + "E"Case &HB7A2 To &HB8C0: pystring = pystring + "F"Case &HB8C1 To &HB9FD: pystring = pystring + "G"Case &HB9FE To &HBBF6: pystring = pystring + "H"Case &HBBF7 To &HBFA5: pystring = pystring + "J"Case &HBFA6 To &HC0AB: pystring = pystring + "K"Case &HC0AC To &HC2E7: pystring = pystring + "L"Case &HC2E8 To &HC4C2: pystring = pystring + "M"Case &HC4C3 To &HC5B5: pystring = pystring + "N"Case &HC5B6 To &HC5BD: pystring = pystring + "O"Case &HC5BE To &HC6D9: pystring = pystring + "P"Case &HC6DA To &HC8BA: pystring = pystring + "Q"Case &HC8BB To &HC8F5: pystring = pystring + "R"Case &HC8F6 To &HCBF9: pystring = pystring + "S"Case &HCBFA To &HCDD9: pystring = pystring + "T"Case &HEDC5: pystring = pystring + "T"Case &HCDDA To &HCEF3: pystring = pystring + "W"Case &HCEF4 To &HD1B8: pystring = pystring + "X"Case &HD1B9 To &HD4D0: pystring = pystring + "Y"Case &HD4D1 To &HD7F9: pystring = pystring + "Z"Case Elsepystring = pystring + Mid(hzstring, hzi, 1)End SelectNexthztopy = pystringEnd Function*******************************************************************************。
excel常用函数功能及读法
皮vei 16、吃一米恩 17、页尔 18。得ei子 死日亿 稀克斯 就肉 19、创可 20、米的
[kaunt]
5、康te(拼音，第三声）
6、最大(小)值函数 MAX(MIN) 7、条件求和函数 SUMIF 8、货款偿还计算函数 PMT 9、样本的标准偏差函数 STDEV 10、排序函数 RANK 11、四舍五入函数 ROUND 12、条件计数函数 COUNTIF 13、百分排位预测函数 PERCENTILE 14、数值探测函数 ISNUMBER 15、零存整取收益函数 PV 16、内部平均值函数 TRIMMEAN 17、日期年份函数 YEAR
excel常用函数功能及读音
函数
函数 AVERAGE
['ævəridʒ]
3、逻辑函数 IF
[if]
4、快捷方式或链接创建函数 HYPERLINK ['haipəliŋk]
汉语近似音 1、桑嗯 2、爱我汝已止 3、衣服 4、害泼尔 林可
5、计数函数 COUNT
18、起止天数函数 DAYS360
19、取整函数 TRUNC 20、字符提取函数 MID
[mæ ks] / [sʌmif]
[ræŋk] [raund]
[pə'sentail]
6、迈克斯/命 7、萨米夫 8、皮爱踢 9、爱斯踢待木 10、瑞安可 11、瑞ang的 12、康体夫 13、泼森泰藕
[jə:, jiə] [mid]
excel2010汉字转拼音的方法
excel2010汉字转拼音的方法在Excel中录入好数据以后，会有一些情况需要汉字转拼音，这个在Excel中也是可以考代码实现的。
下面是由店铺分享的excel2010 汉字转拼音的方法，希望对你有用。
excel2010 汉字转拼音的方法：汉字转拼音步骤1：启动Excel 2003(其它版本请仿照操作)，打开相应的工作表;汉字转拼音步骤2：执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键)，进入Visual Basic编辑状态;汉字转拼音步骤3：执行“插入→模块”命令，插入一个新模块。
再双击插入的模块，进入模块代码编辑状态;汉字转拼音步骤4：将代码输入其中;Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -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 = "dong " Case -18526 To -18519: pinyin = "fa " Case -18518 To -18502: pinyin = "fan " Case -18501 To -18491: pinyin = "fang " Case -18490 To -18479: pinyin = "fei " Case -18478 To -18464: pinyin = "fen " Case -18463 To -18449: pinyin = "feng "Case -18448 To -18448: pinyin = "fo " Case -18447 To -18447: pinyin = "fou " Case -18446 To -18240: pinyin = "fu " Case -18239 To -18238: pinyin = "ga " Case -18237 To -18232: pinyin = "gai " Case -18231 To -18221: pinyin = "gan " Case -18220 To -18212: pinyin = "gang " Case -18211 To -18202: pinyin = "gao " Case -18201 To -18185: pinyin = "ge " Case -18184 To -18184: pinyin = "gei " Case -18183 To -18182: pinyin = "gen " Case -18181 To -18013: pinyin = "geng " Case -18012 To -17998: pinyin = "gong " Case -17997 To -17989: pinyin = "gou " Case -17988 To -17971: pinyin = "gu " Case -17970 To -17965: pinyin = "gua " Case -17964 To -17962: pinyin = "guai " Case -17961 To -17951: pinyin = "guan " Case -17950 To -17948: pinyin = "guang " Case -17947 To -17932: pinyin = "gui " Case -17931 To -17929: pinyin = "gun " Case -17928 To -17923: pinyin = "guo " Case -17922 To -17760: pinyin = "ha " Case -17759 To -17753: pinyin = "hai " Case -17752 To -17734: pinyin = "han " Case -17733 To -17731: pinyin = "hang " Case -17730 To -17722: pinyin = "hao " Case -17721 To -17704: pinyin = "he " Case -17703 To -17702: pinyin = "hei " Case -17701 To -17698: pinyin = "hen "Case -17697 To -17693: pinyin = "heng " Case -17692 To -17684: pinyin = "hong " Case -17683 To -17677: pinyin = "hou " Case -17676 To -17497: pinyin = "hu " Case -17496 To -17488: pinyin = "hua " Case -17487 To -17483: pinyin = "huai " Case -17482 To -17469: pinyin = "huan " Case -17468 To -17455: pinyin = "huang " Case -17454 To -17434: pinyin = "hui " Case -17433 To -17428: pinyin = "hun " Case -17427 To -17418: pinyin = "huo " Case -17417 To -17203: pinyin = "ji " Case -17202 To -17186: pinyin = "jia " Case -17185 To -16984: pinyin = "jian " Case -16983 To -16971: pinyin = "jiang " Case -16970 To -16943: pinyin = "jiao " Case -16942 To -16916: pinyin = "jie " Case -16915 To -16734: pinyin = "jin " Case -16733 To -16709: pinyin = "jing " Case -16708 To -16707: pinyin = "jiong " Case -16706 To -16690: pinyin = "jiu " Case -16689 To -16665: pinyin = "ju " Case -16664 To -16658: pinyin = "juan " Case -16657 To -16648: pinyin = "jue " Case -16647 To -16475: pinyin = "jun " Case -16474 To -16471: pinyin = "ka " Case -16470 To -16466: pinyin = "kai " Case -16465 To -16460: pinyin = "kan " Case -16459 To -16453: pinyin = "kang " Case -16452 To -16449: pinyin = "kao "Case -16448 To -16434: pinyin = "ke " Case -16433 To -16430: pinyin = "ken " Case -16429 To -16428: pinyin = "keng " Case -16427 To -16424: pinyin = "kong " Case -16423 To -16420: pinyin = "kou " Case -16419 To -16413: pinyin = "ku " Case -16412 To -16408: pinyin = "kua " Case -16407 To -16404: pinyin = "kuai " Case -16403 To -16402: pinyin = "kuan " Case -16401 To -16394: pinyin = "kuang " Case -16393 To -16221: pinyin = "kui " Case -16220 To -16217: pinyin = "kun " Case -16216 To -16213: pinyin = "kuo " Case -16212 To -16206: pinyin = "la " Case -16205 To -16203: pinyin = "lai " Case -16202 To -16188: pinyin = "lan " Case -16187 To -16181: pinyin = "lang " Case -16180 To -16172: pinyin = "lao " Case -16171 To -16170: pinyin = "le " Case -16169 To -16159: pinyin = "lei " Case -16158 To -16156: pinyin = "leng " Case -16155 To -15960: pinyin = "li " Case -15959 To -15959: pinyin = "lia " Case -15958 To -15945: pinyin = "lian " Case -15944 To -15934: pinyin = "liang " Case -15933 To -15921: pinyin = "liao " Case -15920 To -15916: pinyin = "lie " Case -15915 To -15904: pinyin = "lin " Case -15903 To -15890: pinyin = "ling " Case -15889 To -15879: pinyin = "liu "Case -15707 To -15702: pinyin = "lou " Case -15701 To -15682: pinyin = "lu " Case -15681 To -15668: pinyin = "lv " Case -15667 To -15662: pinyin = "luan " Case -15661 To -15660: pinyin = "lue " Case -15659 To -15653: pinyin = "lun " Case -15652 To -15641: pinyin = "luo " Case -15640 To -15632: pinyin = "ma " Case -15631 To -15626: pinyin = "mai " Case -15625 To -15455: pinyin = "man " Case -15454 To -15449: pinyin = "mang " Case -15448 To -15437: pinyin = "mao " Case -15436 To -15436: pinyin = "me " Case -15435 To -15420: pinyin = "mei " Case -15419 To -15417: pinyin = "men " Case -15416 To -15409: pinyin = "meng " Case -15408 To -15395: pinyin = "mi " Case -15394 To -15386: pinyin = "mian " Case -15385 To -15378: pinyin = "miao " Case -15377 To -15376: pinyin = "mie " Case -15375 To -15370: pinyin = "min " Case -15369 To -15364: pinyin = "ming " Case -15363 To -15363: pinyin = "miu " Case -15362 To -15184: pinyin = "mo " Case -15183 To -15181: pinyin = "mou " Case -15180 To -15166: pinyin = "mu " Case -15165 To -15159: pinyin = "na " Case -15158 To -15154: pinyin = "nai " Case -15153 To -15151: pinyin = "nan "Case -15149 To -15145: pinyin = "nao " Case -15144 To -15144: pinyin = "ne " Case -15143 To -15142: pinyin = "nei " Case -15141 To -15141: pinyin = "nen " Case -15140 To -15140: pinyin = "neng " Case -15139 To -15129: pinyin = "ni " Case -15128 To -15122: pinyin = "nian " Case -15121 To -15120: pinyin = "niang " Case -15119 To -15118: pinyin = "niao " Case -15117 To -15111: pinyin = "nie " Case -15110 To -15110: pinyin = "nin " Case -15109 To -14942: pinyin = "ning " Case -14941 To -14938: pinyin = "niu " Case -14937 To -14934: pinyin = "nong " Case -14933 To -14931: pinyin = "nu " Case -14930 To -14930: pinyin = "nv " Case -14929 To -14929: pinyin = "nuan " Case -14928 To -14927: pinyin = "nue " Case -14926 To -14923: pinyin = "nuo " Case -14922 To -14922: pinyin = "o " Case -14921 To -14915: pinyin = "ou " Case -14914 To -14909: pinyin = "pa " Case -14908 To -14903: pinyin = "pai " Case -14902 To -14895: pinyin = "pan " Case -14894 To -14890: pinyin = "pang " Case -14889 To -14883: pinyin = "pao " Case -14882 To -14874: pinyin = "pei " Case -14873 To -14872: pinyin = "pen " Case -14871 To -14858: pinyin = "peng "Case -14857 To -14679: pinyin = "pi " Case -14678 To -14675: pinyin = "pian " Case -14674 To -14671: pinyin = "piao " Case -14670 To -14669: pinyin = "pie " Case -14668 To -14664: pinyin = "pin " Case -14663 To -14655: pinyin = "ping " Case -14654 To -14646: pinyin = "po " Case -14645 To -14631: pinyin = "pu " Case -14630 To -14595: pinyin = "qi " Case -14594 To -14430: pinyin = "qia " Case -14429 To -14408: pinyin = "qian " Case -14407 To -14400: pinyin = "qiang " Case -14399 To -14385: pinyin = "qiao " Case -14384 To -14380: pinyin = "qie " Case -14379 To -14369: pinyin = "qin " Case -14368 To -14356: pinyin = "qing " Case -14355 To -14354: pinyin = "qiong " Case -14353 To -14346: pinyin = "qiu " Case -14345 To -14171: pinyin = "qu " Case -14170 To -14160: pinyin = "quan " Case -14159 To -14152: pinyin = "que " Case -14151 To -14150: pinyin = "qun " Case -14149 To -14146: pinyin = "ran " Case -14145 To -14141: pinyin = "rang " Case -14140 To -14138: pinyin = "rao " Case -14137 To -14136: pinyin = "re " Case -14135 To -14126: pinyin = "ren " Case -14125 To -14124: pinyin = "reng " Case -14123 To -14123: pinyin = "ri " Case -14122 To -14113: pinyin = "rong "Case -14112 To -14110: pinyin = "rou " Case -14109 To -14100: pinyin = "ru " Case -14099 To -14098: pinyin = "ruan " Case -14097 To -14095: pinyin = "rui " Case -14094 To -14093: pinyin = "run " Case -14092 To -14091: pinyin = "ruo " Case -14090 To -14088: pinyin = "sa " Case -14087 To -14084: pinyin = "sai " Case -14083 To -13918: pinyin = "san " Case -13917 To -13915: pinyin = "sang " Case -13914 To -13911: pinyin = "sao " Case -13910 To -13908: pinyin = "se " Case -13907 To -13907: pinyin = "sen " Case -13906 To -13906: pinyin = "seng " Case -13905 To -13897: pinyin = "sha " Case -13896 To -13895: pinyin = "shai " Case -13894 To -13879: pinyin = "shan " Case -13878 To -13871: pinyin = "shang " Case -13870 To -13860: pinyin = "shao " Case -13859 To -13848: pinyin = "she " Case -13847 To -13832: pinyin = "shen " Case -13831 To -13659: pinyin = "sheng " Case -13658 To -13612: pinyin = "shi " Case -13611 To -13602: pinyin = "shou " Case -13601 To -13407: pinyin = "shu " Case -13406 To -13405: pinyin = "shua " Case -13404 To -13401: pinyin = "shuai " Case -13400 To -13399: pinyin = "shuan " Case -13398 To -13396: pinyin = "shuang " Case -13395 To -13392: pinyin = "shui "Case -13391 To -13388: pinyin = "shun " Case -13387 To -13384: pinyin = "shuo " Case -13383 To -13368: pinyin = "si " Case -13367 To -13360: pinyin = "song " Case -13359 To -13357: pinyin = "sou " Case -13356 To -13344: pinyin = "su " Case -13343 To -13341: pinyin = "suan " Case -13340 To -13330: pinyin = "sui " Case -13329 To -13327: pinyin = "sun " Case -13326 To -13319: pinyin = "suo " Case -13318 To -13148: pinyin = "ta " Case -13147 To -13139: pinyin = "tai " Case -13138 To -13121: pinyin = "tan " Case -13120 To -13108: pinyin = "tang " Case -13107 To -13097: pinyin = "tao " Case -13096 To -13096: pinyin = "te " Case -13095 To -13092: pinyin = "teng " Case -13091 To -13077: pinyin = "ti " Case -13076 To -13069: pinyin = "tian " Case -13068 To -13064: pinyin = "tiao " Case -13063 To -13061: pinyin = "tie " Case -13060 To -12889: pinyin = "ting " Case -12888 To -12876: pinyin = "tong " Case -12875 To -12872: pinyin = "tou " Case -12871 To -12861: pinyin = "tu " Case -12860 To -12859: pinyin = "tuan " Case -12858 To -12853: pinyin = "tui " Case -12852 To -12850: pinyin = "tun " Case -12849 To -12839: pinyin = "tuo " Case -12838 To -12832: pinyin = "wa "Case -12829 To -12813: pinyin = "wan " Case -12812 To -12803: pinyin = "wang " Case -12802 To -12608: pinyin = "wei " Case -12607 To -12598: pinyin = "wen " Case -12597 To -12595: pinyin = "weng " Case -12594 To -12586: pinyin = "wo " Case -12585 To -12557: pinyin = "wu " Case -12556 To -12360: pinyin = "xi " Case -12359 To -12347: pinyin = "xia " Case -12346 To -12321: pinyin = "xian " Case -12320 To -12301: pinyin = "xiang " Case -12300 To -12121: pinyin = "xiao " Case -12120 To -12100: pinyin = "xie " Case -12099 To -12090: pinyin = "xin " Case -12089 To -12075: pinyin = "xing " Case -12074 To -12068: pinyin = "xiong " Case -12067 To -12059: pinyin = "xiu " Case -12058 To -12040: pinyin = "xu " Case -12039 To -11868: pinyin = "xuan " Case -11867 To -11862: pinyin = "xue " Case -11861 To -11848: pinyin = "xun " Case -11847 To -11832: pinyin = "ya " Case -11831 To -11799: pinyin = "yan " Case -11798 To -11782: pinyin = "yang " Case -11781 To -11605: pinyin = "yao " Case -11604 To -11590: pinyin = "ye " Case -11589 To -11537: pinyin = "yi " Case -11536 To -11359: pinyin = "yin " Case -11358 To -11341: pinyin = "ying "Case -11339 To -11325: pinyin = "yong " Case -11324 To -11304: pinyin = "you " Case -11303 To -11098: pinyin = "yu " Case -11097 To -11078: pinyin = "yuan " Case -11077 To -11068: pinyin = "yue " Case -11067 To -11056: pinyin = "yun " 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 = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Function汉字转拼音步骤5：代码输入完成后，直接关闭Visual Basic编辑窗口，返回Excel编辑状态;汉字转拼音步骤6：自定义函数就可以用了，如：选中A2单元格，输入公式：=getpy(A2)。
excel-汉字转换成拼音简拼(只取首字母)
名字转换为首字母（VBA自定义函数）1、打开EXCEL2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口3、输入公式=hztopy(A1)，填充所有单元格4、选中所有缩写，复制-选择性粘贴-数值（因为是自定义函数，只有在当前文件中可以显示结果，其他电脑因没有公式而显示错误）5、使用条件格式，检索出含有汉字的单元格:先点中一个单元格（设为D5），条件格式-新建格式规则-使用公式确定要设置格式的单元格：在下面对话框中输入公式 =LENB(D5)>LEN(D5)，自己设置格式，确定后退出，再把该单元格的格式用格式刷刷所有单元格。
有汉字的出现所设格式6、选择自定义排序-排序依据“单元格颜色”-次序“无单元格颜色”“在底端”-确定。
7、将汉字手动改写为相应的字母。
8、核对多音字。
注：以上方法只可以在当前文件夹使用，如何将宏保存，使自定义函数可以在所有文件夹中使用1、新建一个EXCEL2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口3、另存为-其他格式-文件名改为宏名称、保存类型选择“EXCEL加载宏（*,xlam）”,文件将自动保存在C:\Users\111\AppData\Roaming\Microsoft\AddIns文件夹。
4、打开需要工作的EXLEL，点击开始-EXCEL选项-加载项-转到（G）...-选中上面保存的宏名称-确定。
这样，在该电脑的所有EXCEL中，都可以直接使用自定义公式。
代码(只复制星号内的内容)******************************************************************************* Function hztopy(hzpy As String) As StringDim hzstring As String, pystring As StringDim hzpysum As Integer, hzi As Integer, hzpyhex As Integerhzstring = Trim(hzpy)hzpysum = Len(Trim(hzstring))pystring = ""For hzi = 1 To hzpysumhzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))Select Case hzpyhexCase &HB0A1 To &HB0C4: pystring = pystring + "A"Case &HB0C5 To &HB2C0: pystring = pystring + "B"Case &HB2C1 To &HB4ED: pystring = pystring + "C"Case &HB4EE To &HB6E9: pystring = pystring + "D"Case &HB6EA To &HB7A1: pystring = pystring + "E"Case &HB7A2 To &HB8C0: pystring = pystring + "F"Case &HB8C1 To &HB9FD: pystring = pystring + "G"Case &HB9FE To &HBBF6: pystring = pystring + "H"Case &HBBF7 To &HBFA5: pystring = pystring + "J"Case &HBFA6 To &HC0AB: pystring = pystring + "K"Case &HC0AC To &HC2E7: pystring = pystring + "L"Case &HC2E8 To &HC4C2: pystring = pystring + "M"Case &HC4C3 To &HC5B5: pystring = pystring + "N"Case &HC5B6 To &HC5BD: pystring = pystring + "O"Case &HC5BE To &HC6D9: pystring = pystring + "P"Case &HC6DA To &HC8BA: pystring = pystring + "Q"Case &HC8BB To &HC8F5: pystring = pystring + "R"Case &HC8F6 To &HCBF9: pystring = pystring + "S"Case &HCBFA To &HCDD9: pystring = pystring + "T"Case &HEDC5: pystring = pystring + "T"Case &HCDDA To &HCEF3: pystring = pystring + "W"Case &HCEF4 To &HD1B8: pystring = pystring + "X"Case &HD1B9 To &HD4D0: pystring = pystring + "Y"Case &HD4D1 To &HD7F9: pystring = pystring + "Z"Case Elsepystring = pystring + Mid(hzstring, hzi, 1)End SelectNexthztopy = pystringEnd Function*******************************************************************************。
如何使用Excel将中文名转换成拼音
方法一：自定义函数法1.创建一个Excel文件2.打开Excel，点击“文件-选项”。
如下图：3.将“开发工具”调出。
如下图：4.点击“开发工具- Visual Basic”。
如下图：5.右击插入模块，复制代码（在文末）。
如下图：6.保存时候会出来这个选项，都可以，不影响后续。
如下图：7.像普通函数一样，使用即可。
代码：Function pinyin(p As String) As String i = Asc(p)Select Case iCase -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 -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 -18952 To -18784: pinyin = "die " Case -18783 To -18775: pinyin = "ding " Case -18774 To -18774: pinyin = "diu " Case -18773 To -18527: pinyin = "dong " Case -18526 To -18519: pinyin = "fa " Case -18518 To -18502: pinyin = "fan " Case -18501 To -18491: pinyin = "fang " Case -18490 To -18479: pinyin = "fei " Case -18478 To -18464: pinyin = "fen " Case -18463 To -18449: pinyin = "feng " Case -18448 To -18448: pinyin = "fo " Case -18447 To -18447: pinyin = "fou " Case -18446 To -18240: pinyin = "fu " Case -18239 To -18238: pinyin = "ga " Case -18237 To -18232: pinyin = "gai " Case -18231 To -18221: pinyin = "gan " Case -18220 To -18212: pinyin = "gang " Case -18211 To -18202: pinyin = "gao " Case -18201 To -18185: pinyin = "ge " Case -18184 To -18184: pinyin = "gei " Case -18183 To -18182: pinyin = "gen " Case -18181 To -18013: pinyin = "geng " Case -18012 To -17998: pinyin = "gong " Case -17997 To -17989: pinyin = "gou " Case -17988 To -17971: pinyin = "gu " Case -17970 To -17965: pinyin = "gua " Case -17964 To -17962: pinyin = "guai " Case -17961 To -17951: pinyin = "guan " Case -17950 To -17948: pinyin = "guang " Case -17947 To -17932: pinyin = "gui " Case -17931 To -17929: pinyin = "gun " Case -17928 To -17923: pinyin = "guo " Case -17922 To -17760: pinyin = "ha " Case -17759 To -17753: pinyin = "hai " Case -17752 To -17734: pinyin = "han " Case -17733 To -17731: pinyin = "hang " Case -17730 To -17722: pinyin = "hao " Case -17721 To -17704: pinyin = "he " Case -17703 To -17702: pinyin = "hei " Case -17701 To -17698: pinyin = "hen " Case -17697 To -17693: pinyin = "heng " Case -17692 To -17684: pinyin = "hong " Case -17683 To -17677: pinyin = "hou "Case -17496 To -17488: pinyin = "hua " Case -17487 To -17483: pinyin = "huai " Case -17482 To -17469: pinyin = "huan " Case -17468 To -17455: pinyin = "huang " Case -17454 To -17434: pinyin = "hui " Case -17433 To -17428: pinyin = "hun " Case -17427 To -17418: pinyin = "huo " Case -17417 To -17203: pinyin = "ji " Case -17202 To -17186: pinyin = "jia " Case -17185 To -16984: pinyin = "jian " Case -16983 To -16971: pinyin = "jiang " Case -16970 To -16943: pinyin = "jiao " Case -16942 To -16916: pinyin = "jie " Case -16915 To -16734: pinyin = "jin " Case -16733 To -16709: pinyin = "jing " Case -16708 To -16707: pinyin = "jiong " Case -16706 To -16690: pinyin = "jiu " Case -16689 To -16665: pinyin = "ju " Case -16664 To -16658: pinyin = "juan " Case -16657 To -16648: pinyin = "jue " Case -16647 To -16475: pinyin = "jun " Case -16474 To -16471: pinyin = "ka " Case -16470 To -16466: pinyin = "kai " Case -16465 To -16460: pinyin = "kan " Case -16459 To -16453: pinyin = "kang " Case -16452 To -16449: pinyin = "kao " Case -16448 To -16434: pinyin = "ke " Case -16433 To -16430: pinyin = "ken " Case -16429 To -16428: pinyin = "keng " Case -16427 To -16424: pinyin = "kong " Case -16423 To -16420: pinyin = "kou " Case -16419 To -16413: pinyin = "ku " Case -16412 To -16408: pinyin = "kua " Case -16407 To -16404: pinyin = "kuai " Case -16403 To -16402: pinyin = "kuan " Case -16401 To -16394: pinyin = "kuang " Case -16393 To -16221: pinyin = "kui " Case -16220 To -16217: pinyin = "kun " Case -16216 To -16213: pinyin = "kuo " Case -16212 To -16206: pinyin = "la " Case -16205 To -16203: pinyin = "lai " Case -16202 To -16188: pinyin = "lan " Case -16187 To -16181: pinyin = "lang "Case -16171 To -16170: pinyin = "le " Case -16169 To -16159: pinyin = "lei " Case -16158 To -16156: pinyin = "leng " Case -16155 To -15960: pinyin = "li " Case -15959 To -15959: pinyin = "lia " Case -15958 To -15945: pinyin = "lian " Case -15944 To -15934: pinyin = "liang " Case -15933 To -15921: pinyin = "liao " Case -15920 To -15916: pinyin = "lie " Case -15915 To -15904: pinyin = "lin " Case -15903 To -15890: pinyin = "ling " Case -15889 To -15879: pinyin = "liu " Case -15878 To -15708: pinyin = "long " Case -15707 To -15702: pinyin = "lou " Case -15701 To -15682: pinyin = "lu " Case -15681 To -15668: pinyin = "lv " Case -15667 To -15662: pinyin = "luan " Case -15661 To -15660: pinyin = "lue " Case -15659 To -15653: pinyin = "lun " Case -15652 To -15641: pinyin = "luo " Case -15640 To -15632: pinyin = "ma " Case -15631 To -15626: pinyin = "mai " Case -15625 To -15455: pinyin = "man " Case -15454 To -15449: pinyin = "mang " Case -15448 To -15437: pinyin = "mao " Case -15436 To -15436: pinyin = "me " Case -15435 To -15420: pinyin = "mei " Case -15419 To -15417: pinyin = "men " Case -15416 To -15409: pinyin = "meng " Case -15408 To -15395: pinyin = "mi " Case -15394 To -15386: pinyin = "mian " Case -15385 To -15378: pinyin = "miao " Case -15377 To -15376: pinyin = "mie " Case -15375 To -15370: pinyin = "min " Case -15369 To -15364: pinyin = "ming " Case -15363 To -15363: pinyin = "miu " Case -15362 To -15184: pinyin = "mo " Case -15183 To -15181: pinyin = "mou " Case -15180 To -15166: pinyin = "mu " Case -15165 To -15159: pinyin = "na " Case -15158 To -15154: pinyin = "nai " Case -15153 To -15151: pinyin = "nan " Case -15150 To -15150: pinyin = "nang "Case -15144 To -15144: pinyin = "ne " Case -15143 To -15142: pinyin = "nei " Case -15141 To -15141: pinyin = "nen " Case -15140 To -15140: pinyin = "neng " Case -15139 To -15129: pinyin = "ni " Case -15128 To -15122: pinyin = "nian " Case -15121 To -15120: pinyin = "niang " Case -15119 To -15118: pinyin = "niao " Case -15117 To -15111: pinyin = "nie " Case -15110 To -15110: pinyin = "nin " Case -15109 To -14942: pinyin = "ning " Case -14941 To -14938: pinyin = "niu " Case -14937 To -14934: pinyin = "nong " Case -14933 To -14931: pinyin = "nu " Case -14930 To -14930: pinyin = "nv " Case -14929 To -14929: pinyin = "nuan " Case -14928 To -14927: pinyin = "nue " Case -14926 To -14923: pinyin = "nuo " Case -14922 To -14922: pinyin = "o " Case -14921 To -14915: pinyin = "ou " Case -14914 To -14909: pinyin = "pa " Case -14908 To -14903: pinyin = "pai " Case -14902 To -14895: pinyin = "pan " Case -14894 To -14890: pinyin = "pang " Case -14889 To -14883: pinyin = "pao " Case -14882 To -14874: pinyin = "pei " Case -14873 To -14872: pinyin = "pen " Case -14871 To -14858: pinyin = "peng " Case -14857 To -14679: pinyin = "pi " Case -14678 To -14675: pinyin = "pian " Case -14674 To -14671: pinyin = "piao " Case -14670 To -14669: pinyin = "pie " Case -14668 To -14664: pinyin = "pin " Case -14663 To -14655: pinyin = "ping " Case -14654 To -14646: pinyin = "po " Case -14645 To -14631: pinyin = "pu " Case -14630 To -14595: pinyin = "qi " Case -14594 To -14430: pinyin = "qia " Case -14429 To -14408: pinyin = "qian " Case -14407 To -14400: pinyin = "qiang " Case -14399 To -14385: pinyin = "qiao " Case -14384 To -14380: pinyin = "qie " Case -14379 To -14369: pinyin = "qin "Case -14355 To -14354: pinyin = "qiong " Case -14353 To -14346: pinyin = "qiu " Case -14345 To -14171: pinyin = "qu " Case -14170 To -14160: pinyin = "quan " Case -14159 To -14152: pinyin = "que " Case -14151 To -14150: pinyin = "qun " Case -14149 To -14146: pinyin = "ran " Case -14145 To -14141: pinyin = "rang " Case -14140 To -14138: pinyin = "rao " Case -14137 To -14136: pinyin = "re " Case -14135 To -14126: pinyin = "ren " Case -14125 To -14124: pinyin = "reng " Case -14123 To -14123: pinyin = "ri " Case -14122 To -14113: pinyin = "rong " Case -14112 To -14110: pinyin = "rou " Case -14109 To -14100: pinyin = "ru " Case -14099 To -14098: pinyin = "ruan " Case -14097 To -14095: pinyin = "rui " Case -14094 To -14093: pinyin = "run " Case -14092 To -14091: pinyin = "ruo " Case -14090 To -14088: pinyin = "sa " Case -14087 To -14084: pinyin = "sai " Case -14083 To -13918: pinyin = "san " Case -13917 To -13915: pinyin = "sang " Case -13914 To -13911: pinyin = "sao " Case -13910 To -13908: pinyin = "se " Case -13907 To -13907: pinyin = "sen " Case -13906 To -13906: pinyin = "seng " Case -13905 To -13897: pinyin = "sha " Case -13896 To -13895: pinyin = "shai " Case -13894 To -13879: pinyin = "shan " Case -13878 To -13871: pinyin = "shang " Case -13870 To -13860: pinyin = "shao " Case -13859 To -13848: pinyin = "she " Case -13847 To -13832: pinyin = "shen " Case -13831 To -13659: pinyin = "sheng " Case -13658 To -13612: pinyin = "shi " Case -13611 To -13602: pinyin = "shou " Case -13601 To -13407: pinyin = "shu " Case -13406 To -13405: pinyin = "shua " Case -13404 To -13401: pinyin = "shuai " Case -13400 To -13399: pinyin = "shuan " Case -13398 To -13396: pinyin = "shuang "Case -13391 To -13388: pinyin = "shun " Case -13387 To -13384: pinyin = "shuo " Case -13383 To -13368: pinyin = "si " Case -13367 To -13360: pinyin = "song " Case -13359 To -13357: pinyin = "sou " Case -13356 To -13344: pinyin = "su " Case -13343 To -13341: pinyin = "suan " Case -13340 To -13330: pinyin = "sui " Case -13329 To -13327: pinyin = "sun " Case -13326 To -13319: pinyin = "suo " Case -13318 To -13148: pinyin = "ta " Case -13147 To -13139: pinyin = "tai " Case -13138 To -13121: pinyin = "tan " Case -13120 To -13108: pinyin = "tang " Case -13107 To -13097: pinyin = "tao " Case -13096 To -13096: pinyin = "te " Case -13095 To -13092: pinyin = "teng " Case -13091 To -13077: pinyin = "ti " Case -13076 To -13069: pinyin = "tian " Case -13068 To -13064: pinyin = "tiao " Case -13063 To -13061: pinyin = "tie " Case -13060 To -12889: pinyin = "ting " Case -12888 To -12876: pinyin = "tong " Case -12875 To -12872: pinyin = "tou " Case -12871 To -12861: pinyin = "tu " Case -12860 To -12859: pinyin = "tuan " Case -12858 To -12853: pinyin = "tui " Case -12852 To -12850: pinyin = "tun " Case -12849 To -12839: pinyin = "tuo " Case -12838 To -12832: pinyin = "wa " Case -12831 To -12830: pinyin = "wai " Case -12829 To -12813: pinyin = "wan " Case -12812 To -12803: pinyin = "wang " Case -12802 To -12608: pinyin = "wei " Case -12607 To -12598: pinyin = "wen " Case -12597 To -12595: pinyin = "weng " Case -12594 To -12586: pinyin = "wo " Case -12585 To -12557: pinyin = "wu " Case -12556 To -12360: pinyin = "xi " Case -12359 To -12347: pinyin = "xia " Case -12346 To -12321: pinyin = "xian " Case -12320 To -12301: pinyin = "xiang " Case -12300 To -12121: pinyin = "xiao "Case -12099 To -12090: pinyin = "xin " Case -12089 To -12075: pinyin = "xing " Case -12074 To -12068: pinyin = "xiong " Case -12067 To -12059: pinyin = "xiu " Case -12058 To -12040: pinyin = "xu " Case -12039 To -11868: pinyin = "xuan " Case -11867 To -11862: pinyin = "xue " Case -11861 To -11848: pinyin = "xun " Case -11847 To -11832: pinyin = "ya " Case -11831 To -11799: pinyin = "yan " Case -11798 To -11782: pinyin = "yang " Case -11781 To -11605: pinyin = "yao " Case -11604 To -11590: pinyin = "ye " Case -11589 To -11537: pinyin = "yi " Case -11536 To -11359: pinyin = "yin " Case -11358 To -11341: pinyin = "ying " Case -11340 To -11340: pinyin = "yo " Case -11339 To -11325: pinyin = "yong " Case -11324 To -11304: pinyin = "you " Case -11303 To -11098: pinyin = "yu " Case -11097 To -11078: pinyin = "yuan " Case -11077 To -11068: pinyin = "yue " Case -11067 To -11056: pinyin = "yun " 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 -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 = pEnd SelectEnd FunctionFunction getpy(str) '得到拼音For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd FunctionFunction gets(str) '得到首字母gets = Left(getpy(str), 1)End FunctionFunction getqs(str) '得到全首字母For i = 1 To Len(str)getqs = getqs & Left(pinyin(Mid(str, i, 1)), 1) Next iEnd Function。
excel汉字转全拼音函数
excel汉字转全拼音函数
Excel汉字转全拼音函数
在日常工作中，我们常常会遇到需要将汉字转换为拼音的情况。
例如，在处理数据时，将中文名称转换为拼音可以方便排序和查询。
为了解决这个问题，我们可以在Excel中使用自定义函数来实现汉字转全拼音的功能。
步骤1：创建一个新的模块
首先，打开Excel并创建一个新的工作簿。
然后，按下Alt + F11打开Visual Basic for Applications（VBA）编辑器。
在VBA编辑器中，选择插入>模块，创建一个新的模块。
步骤2：编写函数代码
在新创建的模块中，我们可以编写函数代码来实现汉字转全拼音的功能。
以下是一个简单的示例函数：
```vba
Function HanziToPinyin(str As String) As String
Dim i As Long
Dim chineseChar As String
Dim pinyin As String
For i = 1 To Len(str)
chineseChar = Mid(str, i, 1)
' 如果是汉字，则转换为拼音
If Asc(chineseChar) >= 19968 And Asc(chineseChar) <= 40869 Then
pinyin = pinyin &
Application.WorksheetFunction.VLookup(chineseChar, Range(\。
汉字转拼音公式
将汉字转换为拼音，可以使用以下两种方法：
方法一（使用Office Excel 2007）：
1. 打开一个新的Excel文档。
2. 在D2单元格中输入“=getpy(C2)”，然后按回车键确认。
3. 将鼠标放在D2单元格右下角，当出现“+”后，向下拖动鼠标。
4. 这样，姓名一栏所有的汉字都以拼音形式显示了。
5. 如果需要将拼音的首字母变成大写，可以将鼠标标定在E2单元格中，然后输入“=PROPER(D2)”，按回车键确认。
6. 重复步骤3~5，即可将所有汉字转换为拼音。
方法二（使用Word文档和拼音指南功能）：
1. 打开一个新的Word文档，并将表格中的员工姓名复制到Word文档中。
2. 按下CTRL+A，选择所有数据，确保整个文档被选中。
3. 在开始选项卡下找到拼音指南，并点击确认。
这样，Word文档中的汉字都会附带上拼音。
4. 将Word文档中的数据全选并复制回到Excel表格的B列中。
5. 在C列中，输入“=PH”，并选择第2个公式，引用B1单元格。
整体公式为：“=PHONETIC(B1)”。
6. 按回车键确认后即可得到转换后的结果。
通过以上方法，您可以轻松地将汉字转换为拼音，并将其导入Excel表格中进行分析和处理。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel自定义函数方法：1.启动Excel 2003（其它版本请仿照操作），打开相应的工作表；2.执行“工具→宏→Visual Basic编辑器”命令（或者直接按“Alt+F11”组合键），进入Visual Basic编辑状态；3.执行“插入→模块”命令，插入一个新模块。
再双击插入的模块，进入模块代码编辑状态；4.将代码输入其中；5.代码输入完成后，关闭Visual Basic编辑窗口，返回Excel编辑状态；6.自定义函数就可以用了，如：选中D2单元格，输入公式：=getpy(A2)。
有极个别的汉字不能转换，手动就可以了！很方便了。
Function pinyin (p As String) As Stringi = Asc(p)Select Case iCase -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 -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 -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 -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 =“dong”Case -18526 To -18519: pinyin =“fa”Case -18518 To -18502: pinyin =“fan”Case -18501 To -18491: pinyin =“fang”Case -18490 To -18479: pinyin =“fei”Case -18478 To -18464: pinyin =“fen”Case -18463 To -18449: pinyin =“feng”Case -18448 To -18448: pinyin =“fo”Case -18447 To -18447: pinyin =“fou”Case -18446 To -18240: pinyin =“fu”Case -18239 To -18238: pinyin =“ga”Case -18237 To -18232: pinyin =“gai”Case -18231 To -18221: pinyin =“gan”Case -18220 To -18212: pinyin =“gang”Case -18211 To -18202: pinyin =“gao”Case -18201 To -18185: pinyin =“ge”Case -18184 To -18184: pinyin =“gei”Case -18183 To -18182: pinyin =“gen”Case -18181 To -18013: pinyin =“geng”Case -18012 To -17998: pinyin =“gong”Case -17997 To -17989: pinyin =“gou”Case -17988 To -17971: pinyin =“gu”Case -17970 To -17965: pinyin =“gua”Case -17964 To -17962: pinyin =“guai”Case -17961 To -17951: pinyin =“guan”Case -17950 To -17948: pinyin =“guang”Case -17947 To -17932: pinyin =“gui”Case -17931 To -17929: pinyin =“gun”Case -17928 To -17923: pinyin =“guo”Case -17922 To -17760: pinyin =“ha”Case -17759 To -17753: pinyin =“hai”Case -17752 To -17734: pinyin =“han”Case -17733 To -17731: pinyin =“hang”Case -17721 To -17704: pinyin =“he”Case -17703 To -17702: pinyin =“hei”Case -17701 To -17698: pinyin =“hen”Case -17697 To -17693: pinyin =“heng”Case -17692 To -17684: pinyin =“hong”Case -17683 To -17677: pinyin =“hou”Case -17676 To -17497: pinyin =“hu”Case -17496 To -17488: pinyin =“hua”Case -17487 To -17483: pinyin =“huai”Case -17482 To -17469: pinyin =“huan”Case -17468 To -17455: pinyin =“huang”Case -17454 To -17434: pinyin =“hui”Case -17433 To -17428: pinyin =“hun”Case -17427 To -17418: pinyin =“huo”Case -17417 To -17203: pinyin =“ji”Case -17202 To -17186: pinyin =“jia”Case -17185 To -16984: pinyin =“jian”Case -16983 To -16971: pinyin =“jiang”Case -16970 To -16943: pinyin =“jiao”Case -16942 To -16916: pinyin =“jie”Case -16915 To -16734: pinyin =“jin”Case -16733 To -16709: pinyin =“jing”Case -16708 To -16707: pinyin =“jiong”Case -16706 To -16690: pinyin =“jiu”Case -16689 To -16665: pinyin =“ju”Case -16664 To -16658: pinyin =“juan”Case -16657 To -16648: pinyin =“jue”Case -16647 To -16475: pinyin =“jun”Case -16474 To -16471: pinyin =“ka”Case -16470 To -16466: pinyin =“kai”Case -16465 To -16460: pinyin =“kan”Case -16459 To -16453: pinyin =“kang”Case -16452 To -16449: pinyin =“kao”Case -16448 To -16434: pinyin =“ke”Case -16433 To -16430: pinyin =“ken”Case -16429 To -16428: pinyin =“keng”Case -16427 To -16424: pinyin =“kong”Case -16423 To -16420: pinyin =“kou”Case -16419 To -16413: pinyin =“ku”Case -16412 To -16408: pinyin =“kua”Case -16407 To -16404: pinyin =“kuai”Case -16403 To -16402: pinyin =“kuan”Case -16401 To -16394: pinyin =“kuang”Case -16220 To -16217: pinyin =“kun”Case -16216 To -16213: pinyin =“kuo”Case -16212 To -16206: pinyin =“la”Case -16205 To -16203: pinyin =“lai”Case -16202 To -16188: pinyin =“lan”Case -16187 To -16181: pinyin =“lang”Case -16180 To -16172: pinyin =“lao”Case -16171 To -16170: pinyin =“le”Case -16169 To -16159: pinyin =“lei”Case -16158 To -16156: pinyin =“leng”Case -16155 To -15960: pinyin =“li”Case -15959 To -15959: pinyin =“lia”Case -15958 To -15945: pinyin =“lian”Case -15944 To -15934: pinyin =“liang”Case -15933 To -15921: pinyin =“liao”Case -15920 To -15916: pinyin =“lie”Case -15915 To -15904: pinyin =“lin”Case -15903 To -15890: pinyin =“ling”Case -15889 To -15879: pinyin =“liu”Case -15878 To -15708: pinyin =“long”Case -15707 To -15702: pinyin =“lou”Case -15701 To -15682: pinyin =“lu”Case -15681 To -15668: pinyin =“lv”Case -15667 To -15662: pinyin =“luan”Case -15661 To -15660: pinyin =“lue”Case -15659 To -15653: pinyin =“lun”Case -15652 To -15641: pinyin =“luo”Case -15640 To -15632: pinyin =“ma”Case -15631 To -15626: pinyin =“mai”Case -15625 To -15455: pinyin =“man”Case -15454 To -15449: pinyin =“mang”Case -15448 To -15437: pinyin =“mao”Case -15436 To -15436: pinyin =“me”Case -15435 To -15420: pinyin =“mei”Case -15419 To -15417: pinyin =“men”Case -15416 To -15409: pinyin =“meng”Case -15408 To -15395: pinyin =“mi”Case -15394 To -15386: pinyin =“mian”Case -15385 To -15378: pinyin =“miao”Case -15377 To -15376: pinyin =“mie”Case -15375 To -15370: pinyin =“min”Case -15369 To -15364: pinyin =“ming”Case -15363 To -15363: pinyin =“miu”Case -15183 To -15181: pinyin =“mou”Case -15180 To -15166: pinyin =“mu”Case -15165 To -15159: pinyin =“na”Case -15158 To -15154: pinyin =“nai”Case -15153 To -15151: pinyin =“nan”Case -15150 To -15150: pinyin =“nang”Case -15149 To -15145: pinyin =“nao”Case -15144 To -15144: pinyin =“ne”Case -15143 To -15142: pinyin =“nei”Case -15141 To -15141: pinyin =“nen”Case -15140 To -15140: pinyin =“neng”Case -15139 To -15129: pinyin =“ni”Case -15128 To -15122: pinyin =“nian”Case -15121 To -15120: pinyin =“niang”Case -15119 To -15118: pinyin =“niao”Case -15117 To -15111: pinyin =“nie”Case -15110 To -15110: pinyin =“nin”Case -15109 To -14942: pinyin =“ning”Case -14941 To -14938: pinyin =“niu”Case -14937 To -14934: pinyin =“nong”Case -14933 To -14931: pinyin =“nu”Case -14930 To -14930: pinyin =“nv”Case -14929 To -14929: pinyin =“nuan”Case -14928 To -14927: pinyin =“nue”Case -14926 To -14923: pinyin =“nuo”Case -14922 To -14922: pinyin =“o”Case -14921 To -14915: pinyin =“ou”Case -14914 To -14909: pinyin =“pa”Case -14908 To -14903: pinyin =“pai”Case -14902 To -14895: pinyin =“pan”Case -14894 To -14890: pinyin =“pang”Case -14889 To -14883: pinyin =“pao”Case -14882 To -14874: pinyin =“pei”Case -14873 To -14872: pinyin =“pen”Case -14871 To -14858: pinyin =“peng”Case -14857 To -14679: pinyin =“pi”Case -14678 To -14675: pinyin =“pian”Case -14674 To -14671: pinyin =“piao”Case -14670 To -14669: pinyin =“pie”Case -14668 To -14664: pinyin =“pin”Case -14663 To -14655: pinyin =“ping”Case -14654 To -14646: pinyin =“po”Case -14645 To -14631: pinyin =“pu”Case -14594 To -14430: pinyin =“qia”Case -14429 To -14408: pinyin =“qian”Case -14407 To -14400: pinyin =“qiang”Case -14399 To -14385: pinyin =“qiao”Case -14384 To -14380: pinyin =“qie”Case -14379 To -14369: pinyin =“qin”Case -14368 To -14356: pinyin =“qing”Case -14355 To -14354: pinyin =“qiong”Case -14353 To -14346: pinyin =“qiu”Case -14345 To -14171: pinyin =“qu”Case -14170 To -14160: pinyin =“quan”Case -14159 To -14152: pinyin =“que”Case -14151 To -14150: pinyin =“qun”Case -14149 To -14146: pinyin =“ran”Case -14145 To -14141: pinyin =“rang”Case -14140 To -14138: pinyin =“rao”Case -14137 To -14136: pinyin =“re”Case -14135 To -14126: pinyin =“ren”Case -14125 To -14124: pinyin =“reng”Case -14123 To -14123: pinyin =“ri”Case -14122 To -14113: pinyin =“rong”Case -14112 To -14110: pinyin =“rou”Case -14109 To -14100: pinyin =“ru”Case -14099 To -14098: pinyin =“ruan”Case -14097 To -14095: pinyin =“rui”Case -14094 To -14093: pinyin =“run”Case -14092 To -14091: pinyin =“ruo”Case -14090 To -14088: pinyin =“sa”Case -14087 To -14084: pinyin =“sai”Case -14083 To -13918: pinyin =“san”Case -13917 To -13915: pinyin =“sang”Case -13914 To -13911: pinyin =“sao”Case -13910 To -13908: pinyin =“se”Case -13907 To -13907: pinyin =“sen”Case -13906 To -13906: pinyin =“seng”Case -13905 To -13897: pinyin =“sha”Case -13896 To -13895: pinyin =“shai”Case -13894 To -13879: pinyin =“shan”Case -13878 To -13871: pinyin =“shang”Case -13870 To -13860: pinyin =“shao”Case -13859 To -13848: pinyin =“she”Case -13847 To -13832: pinyin =“shen”Case -13831 To -13659: pinyin =“sheng”Case -13611 To -13602: pinyin =“shou”Case -13601 To -13407: pinyin =“shu”Case -13406 To -13405: pinyin =“shua”Case -13404 To -13401: pinyin =“shuai”Case -13400 To -13399: pinyin =“shuan”Case -13398 To -13396: pinyin =“shuang”Case -13395 To -13392: pinyin =“shui”Case -13391 To -13388: pinyin =“shun”Case -13387 To -13384: pinyin =“shuo”Case -13383 To -13368: pinyin =“si”Case -13367 To -13360: pinyin =“song”Case -13359 To -13357: pinyin =“sou”Case -13356 To -13344: pinyin =“su”Case -13343 To -13341: pinyin =“suan”Case -13340 To -13330: pinyin =“sui”Case -13329 To -13327: pinyin =“sun”Case -13326 To -13319: pinyin =“suo”Case -13318 To -13148: pinyin =“ta”Case -13147 To -13139: pinyin =“tai”Case -13138 To -13121: pinyin =“tan”Case -13120 To -13108: pinyin =“tang”Case -13107 To -13097: pinyin =“tao”Case -13096 To -13096: pinyin =“te”Case -13095 To -13092: pinyin =“teng”Case -13091 To -13077: pinyin =“ti”Case -13076 To -13069: pinyin =“tian”Case -13068 To -13064: pinyin =“tiao”Case -13063 To -13061: pinyin =“tie”Case -13060 To -12889: pinyin =“ting”Case -12888 To -12876: pinyin =“tong”Case -12875 To -12872: pinyin =“tou”Case -12871 To -12861: pinyin =“tu”Case -12860 To -12859: pinyin =“tuan”Case -12858 To -12853: pinyin =“tui”Case -12852 To -12850: pinyin =“tun”Case -12849 To -12839: pinyin =“tuo”Case -12838 To -12832: pinyin =“wa”Case -12831 To -12830: pinyin =“wai”Case -12829 To -12813: pinyin =“wan”Case -12812 To -12803: pinyin =“wang”Case -12802 To -12608: pinyin =“wei”Case -12607 To -12598: pinyin =“wen”Case -12597 To -12595: pinyin =“weng”Case -12585 To -12557: pinyin =“wu”Case -12556 To -12360: pinyin =“xi”Case -12359 To -12347: pinyin =“xia”Case -12346 To -12321: pinyin =“xian”Case -12320 To -12301: pinyin =“xiang”Case -12300 To -12121: pinyin =“xiao”Case -12120 To -12100: pinyin =“xie”Case -12099 To -12090: pinyin =“xin”Case -12089 To -12075: pinyin =“xing”Case -12074 To -12068: pinyin =“xiong”Case -12067 To -12059: pinyin =“xiu”Case -12058 To -12040: pinyin =“xu”Case -12039 To -11868: pinyin =“xuan”Case -11867 To -11862: pinyin =“xue”Case -11861 To -11848: pinyin =“xun”Case -11847 To -11832: pinyin =“ya”Case -11831 To -11799: pinyin =“yan”Case -11798 To -11782: pinyin =“yang”Case -11781 To -11605: pinyin =“yao”Case -11604 To -11590: pinyin =“ye”Case -11589 To -11537: pinyin =“yi”Case -11536 To -11359: pinyin =“yin”Case -11358 To -11341: pinyin =“ying”Case -11340 To -11340: pinyin =“yo”Case -11339 To -11325: pinyin =“yong”Case -11324 To -11304: pinyin =“you”Case -11303 To -11098: pinyin =“yu”Case -11097 To -11078: pinyin =“yuan”Case -11077 To -11068: pinyin =“yue”Case -11067 To -11056: pinyin =“yun”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 -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 = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1)) Next iEnd Function只需要在Excel中使用=getpy(列)使用公式，去掉拼音之间的空格假设你的这个数据在A1单元格，在B1单元格输入：=SUBSTITUTE(A1,"“,"")。