excel汉字转换成拼音简拼只取首字母
Excel汉字中文转换为拼音(含代码)
Excel汉字中文转换为拼音（含代码）一、无法使用getpy()函数解决方法1.添加拼音转换函数打开Excel→工发工具→Viaual Basic2.点击 Viaual Basic编辑器→选择“VBAproject”点右键→插入→模块3.将以下内容复制粘贴到“模块”里→保存→出现对话框→选择“否”→“选择保存类型”为“excel启用宏工作簿”→保存。
设置完成，现在可以使用getpy()等函数了。
4.或将本文件直接另存为“excel启用宏工作簿”， 就可以使用getpy()转换函数。
二、汉字转换为拼音函数公式三、代码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 "。
excel中把汉字转换成拼音的方法
excel中把汉字转换成拼音的方法在excel中把的汉字转换成拼音，拼音在字的上面，在word和EXCEL中，都可以，再粘贴到别的地方，用起来不如word，那么下面小编就教你具体怎么怎么把汉字转换成拼音吧。
希望对你有帮助！excel中把汉字转换成拼音的方法打开需要转换的excel表格单击excel表格的任意位置，然后按下ALT+F11，进入这个灰色的编辑界面excel中把汉字转换成拼音的方法图2插入--模块excel中把汉字转换成拼音的方法图3打开如下所示的界面。
excel中把汉字转换成拼音的方法图4把下面的内容复制粘贴到第四个步骤打开的界面中，并保存，参考下图操作。
Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -20319 To -20318: pinyin = "a "Case Else: pinyin = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Functionexcel中把汉字转换成拼音的方法图5保存，参考图片进行操作。
excel中把汉字转换成拼音的方法图6插入一列，用于存放转换后的拼音excel中把汉字转换成拼音的方法图7这里放在如果所示的这一列，在这一列中输入=getpy(转换源头所在的列)excel中把汉字转换成拼音的方法图8这是转成拼音后的效果excel中把汉字转换成拼音的方法图9这里的D2是数据源头excel中把汉字转换成拼音的方法图10。
EXCEL中直接把中文转换成拼音全拼
1.第一种方法：打开Excel-〉工具->宏->Viaual Basic编辑器在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1"，点击在右边的空白栏中粘贴以下内容：Fun cti on getpychar(char)tmp = 65536 + Asc(char)If (tmp >= 45217 And tmp <= 45252) Thengetpychar = "A"ElseIf (tmp >= 45253 And tmp <= 45760) Then getpychar = "B"ElseIf (tmp >= 45761 And tmp <= 46317) Then getpychar = "C"ElseIf (tmp >= 46318 And tmp <= 46825) Then getpychar = "D"ElseIf (tmp >= 46826 And tmp <= 47009) Then getpychar = "E"ElseIf (tmp >= 47010 And tmp <= 47296) Then getpychar = "F"ElseIf (tmp >= 47297 And tmp <= 47613) Then getpychar = "G"ElseIf (tmp >= 47614 And tmp <= 48118) Then getpychar = "H"ElseIf (tmp >= 48119 And tmp <= 49061) Then getpychar = "J"ElseIf (tmp >= 49062 And tmp <= 49323) Then getpychar = "K"ElseIf (tmp >= 49324 And tmp <= 49895) Then getpychar = "L"ElseIf (tmp >= 49896 And tmp <= 50370) Then getpychar = "M"Elself (tmp >= 50371 And tmp <= 50613) Then getpychar = "N"ElseIf (tmp >= 50614 And tmp <= 50621) Then getpychar = "O"ElseIf (tmp >= 50622 And tmp <= 50905) Then getpychar = "P"ElseIf (tmp >= 50906 And tmp <= 51386) Then getpychar = "Q"ElseIf (tmp >= 51387 And tmp <= 51445) Then getpychar = "R"ElseIf (tmp >= 51446 And tmp <= 52217) Then getpychar = "S"ElseIf (tmp >= 52218 And tmp <= 52697) Then getpychar = "T"ElseIf (tmp >= 52698 And tmp <= 52979) Then getpychar = "W"ElseIf (tmp >= 52980 And tmp <= 53640) Then getpychar = "X"ElseIf (tmp >= 53689 And tmp <= 54480) Then getpychar = "Y"ElseIf (tmp >= 54481 And tmp <= 62289) Then getpychar = "Z"Else '如果不是中文，则不处理getpychar = charEnd IfEnd Fun cti onFun cti on getpy(str)For i = 1 To Len( str)getpy = getpy & getpychar(Mid(str, i, 1))Next iEnd Fun cti on现在转换函数已编写完成！关掉此编缉的窗口要在Excel中使用，方法如下：A1 A2中国=getpy(A1)2.第二种方法更简单些，但只能转单个字: =IF(LEN(A1)>0,LOOKUP(CODE(A1),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,776 3,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R ","S","T","W","X","Y","Z"}),"")。
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->工具->宏->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********复制到此结束,本行不复制*******现在转换函数已编写完成!关掉此编缉的窗口。
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中将大量姓名或名称转换为拼音首字母的简写形式，例如将“周伯通”转换为“ZBT”。
用定义名称和公式的方法可以轻松实现转换，在Windows XP中，以Excel 2003为例方法如下：1.定义名称：单击菜单“插入→名称→定义”，在弹出的对话框中“在当前工作簿中的名称”下的文本框中输入一个名称，如“py”，在“引用位置”下输入下列代码后确定：={"","吖","八","嚓","咑","鵽","发","猤","铪","夻","咔","垃","嘸","旀","噢","妑","七","囕","仨","他","屲","夕","丫","帀";"","A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y"," Z"}2.假如名单在A2:A100区域中，在B2单元格中输入公式：=LOOKUP(LEFT(A2,1),py)&LOOKUP(MID(A2,2,1),py)&LOOKUP(MID(A2,3,1), py)&LOOKUP(MID(A2,4,1),py)然后拖动填充柄向下填充公式即可。
excel汉字转换成拼音简拼（只取首字母）
名字转换为首字母（VBA自定义函数）1、打开EXCE L2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口3、输入公式=hztopy(A1)，填充所有单元格4、选中所有缩写，复制-选择性粘贴-数值（因为是自定义函数，只有在当前文件中可以显示结果，其他电脑因没有公式而显示错误）5、使用条件格式，检索出含有汉字的单元格:先点中一个单元格（设为D5），条件格式-新建格式规则-使用公式确定要设置格式的单元格：在下面对话框中输入公式=LENB(D5)>LEN(D5)，自己设置格式，确定后退出，再把该单元格的格式用格式刷刷所有单元格。
有汉字的出现所设格式6、选择自定义排序-排序依据“单元格颜色”-次序“无单元格颜色”“在底端”-确定。
7、将汉字手动改写为相应的字母。
8、核对多音字。
注：以上方法只可以在当前文件夹使用，如何将宏保存，使自定义函数可以在所有文件夹中使用1、新建一个EX CEL2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口3、另存为-其他格式-文件名改为宏名称、保存类型选择“EXCEL加载宏（*,xlam）”,文件将自动保存在C:\Users\111\AppDat a\Roamin g\Micros oft\AddIns文件夹。
4、打开需要工作的EXLE L，点击开始-EXCEL选项-加载项-转到（G）...-选中上面保存的宏名称-确定。
这样，在该电脑的所有EXCE L中，都可以直接使用自定义公式。
代码(只复制星号内的内容)******************************************************************************* Functi on hztopy(hzpy As String) As StringDim hzstri ng As String, pystri ng As StringDim hzpysu m As Intege r, hzi As Intege r, hzpyhe x As Intege rhzstri ng = Trim(hzpy)hzpysu m = Len(Trim(hzstri ng))pystri ng = ""For hzi = 1 To hzpysu mhzpyhe x = "&H" + Hex(Asc(Mid(hzstri ng, hzi, 1)))Select Case hzpyhe xCase &HB0A1To &HB0C4: pystri ng = pystri ng + "A"Case &HB0C5To &HB2C0: pystri ng = pystri ng + "B"Case &HB2C1To &HB4ED: pystri ng = pystri ng + "C"Case &HB4EETo &HB6E9: pystri ng = pystri ng + "D"Case &HB6EATo &HB7A1: pystri ng = pystri ng + "E"Case &HB7A2To &HB8C0: pystri ng = pystri ng + "F"Case &HB8C1To &HB9FD: pystri ng = pystri ng + "G"Case &HB9FETo &HBBF6: pystri ng = pystri ng + "H"Case &HBBF7To &HBFA5: pystri ng = pystri ng + "J"Case &HBFA6To &HC0AB: pystri ng = pystri ng + "K"Case &HC0ACTo &HC2E7: pystri ng = pystri ng + "L"Case &HC2E8To &HC4C2: pystri ng = pystri ng + "M"Case &HC4C3To &HC5B5: pystri ng = pystri ng + "N"Case &HC5B6To &HC5BD: pystri ng = pystri ng + "O"Case &HC5BETo &HC6D9: pystri ng = pystri ng + "P"Case &HC6DATo &HC8BA: pystri ng = pystri ng + "Q"Case &HC8BBTo &HC8F5: pystri ng = pystri ng + "R"Case &HC8F6To &HCBF9: pystri ng = pystri ng + "S"Case &HCBFATo &HCDD9: pystri ng = pystri ng + "T"Case &HEDC5: pystri ng = pystri ng + "T"Case &HCDDATo &HCEF3: pystri ng = pystri ng + "W"Case &HCEF4To &HD1B8: pystri ng = pystri ng + "X"Case &HD1B9To &HD4D0: pystri ng = pystri ng + "Y"Case &HD4D1To &HD7F9: pystri ng = pystri ng + "Z"Case Elsep ystri ng = pystri ng + Mid(hzstri ng, hzi, 1)End SelectNexthztopy = pystri ngEnd Functi on*******************************************************************************。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
e x c e l汉字转换成拼音
简拼只取首字母
集团文件版本号：（M928-T898-M248-WU2669-I2896-DQ586-M1988）
名字转换为首字母（VBA自定义函数）
1、打开EXCEL
2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口
3、输入公式=hztopy(A1)，填充所有单元格
4、选中所有缩写，复制-选择性粘贴-数值（因为是自定义函数，只有在当前文件中可以显示结果，其他电脑因没有公式而显示错误）
5、使用条件格式，检索出含有汉字的单元格:
先点中一个单元格（设为D5），条件格式-新建格式规则-使用公式确定要设置格式的单元格：在下面对话框中输入公式
=LENB(D5)>LEN(D5)，自己设置格式，确定后退出，再把该单元格的格式用格式刷刷所有单元格。
有汉字的出现所设格式
6、选择自定义排序-排序依据“单元格颜色”-次序“无单元格颜色”“在底端”-确定。
7、将汉字手动改写为相应的字母。
8、核对多音字。
注：以上方法只可以在当前文件夹使用，如何将宏保存，使自定义函数可以在所有文件夹中使用
1、新建一个EXCEL
2、按 ALT+F11 ，点击插入-插入模块，在右面窗口中粘贴下面代码，直接关闭窗口
3、另存为-其他格式-文件名改为宏名称、保存类型选择“EXCEL加载宏（*,xlam）”,文件将自动保存在
C:\Users\111\AppData\Roaming\Microsoft\AddIns文件夹。
4、打开需要工作的EXLEL，点击开始-EXCEL选项-加载项-转到（G）...-选中上面保存的宏名称-确定。
这样，在该电脑的所有EXCEL中，都可以直接使用自定义公式。
代码(只复制星号内的内容)
*************************************************************** ****************
Function hztopy(hzpy As String) As String
Dim hzstring As String, pystring As String
Dim hzpysum As Integer, hzi As Integer, hzpyhex As Integer
hzstring = Trim(hzpy)
hzpysum = Len(Trim(hzstring))
pystring = ""
For hzi = 1 To hzpysum
hzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))
Select Case hzpyhex
Case &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 Else
pystring = pystring + Mid(hzstring, hzi, 1)
End Select
Next
hztopy = pystring
End Function
*************************************************************** ****************。