本文收集整理关于excel如何将汉字变成拼音的相关议题,使用内容导航快速到达。
内容导航:
Q1:如何将Excel表格汉字转换成拼音字母
百度找 有个别人做好的宏可以用
很复杂的,是个查表程序
excel本身没这个功能
建议用那个专门的转换软件,很多的
ps:wps有个拼音转换插件不错
我的搜藏里有那个宏的网址
Q2:如何将EXCEL表格中的汉字转为拼音
在word 和EXCEL中,都可以:
选中内容----格式---拼音指南
但,在EXCEL中,用起来不如word 中方便,可以在word中形成,再粘贴到别的地方,如粘贴到EXCEL中。
如(rú)何(hé)将(jiāng)EXCEL表(biǎo)格(gé)中(zhōng)的(de)汉(hàn)字(zì)转(zhuǎn)为(wéi)拼(pīn)音(yīn)
--------这是在word中形成的,但,在word 和EXCEL中,拼音在字的上面。
Q3:excel怎么批量将汉字转换成拼音
这个要弄一个汉字拼音数据表才行。我这里有一个需要的话请说话。
Q4:excel汉字转换拼音?
Excel如何把汉字转换成拼音
1
2
3
4
5
6
7
我们在工作中有时候会用到Excel做表格,excel录入中文,不能自动转换成拼音,那么怎么样可以快速的把汉字转换成拼音呢?下面跟我来简单的操作一下。
百度经验:jingyan.baidu.com
工具/原料
Excel 2007版本
百度经验:jingyan.baidu.com
方法/步骤
1
首先用officeExcel 2007打开文档;
2
然后鼠标标定在D2单元格,输入公式“=getpy(C2)”,按回车键确认;
3
我们就看到姓名一栏汉语拼音就自动出现了;
4
选中D2单元格,鼠标放在单元格右下角,当出现“+”后,向下拖动鼠标;
5
这样,姓名一栏所有的汉字都以拼音形式显示了;
6
如果我们需要将拼音的首字母变成大写的话,可以将鼠标标定在E2单元格中,
然后输入公式“=PROPER(D2)”,按回车确认;
7
这样,我们需要的拼音的首字母就变成大写的了。
8
然后重复步骤将鼠标放在单元格右下角,当出现“+”后,向下拖动鼠标,就完成了。
Q5:如何在excel中把汉字转换成拼音
Excel中将汉字在另一列转换成拼音的操作方法如下:
一、首先打开Excel工作表;
二、其次执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键),进入Visual Basic编辑状态;
三、然后执行“插入→模块”命令,插入一个新模块。再双击插入的模块,进入模块代码编辑状态;并将代码输入其中;
四、再代码输入完成后,关闭Visual Basic编辑窗口,返回Excel编辑状态;
五、最后选中单元格,输入公式:=getpy( ),并点击确定,最后自动填充即可。
WwW+.yIjItao.COM
Q6:EXCEL里面怎么将汉字转换成拼音
具体比较复杂,VBA
Public Function HzToPy(Hz As String, _
Optional Sep As String = "", _
Optional NotationType As Integer = -1, _
Optional ShowInitialOnly As Boolean = False, _
Optional ShowOnlyOneChar As Boolean = False) As String
Dim hp As HZ2PY
Set hp = New HZ2PY创建类
hp.Seperator = Sep
hp.InitialOnly = ShowInitialOnly
hp.OnlyOneChar = ShowOnlyOneChar
HzToPy = hp.GetPinYin(Hz)
HzToPy = hp.AdjustPhoneticNotation(HzToPy, NotationType)
Set hp = Nothing释放类
End Function
类模块HZ2PY***************************************************************************
*
* Module:HzToPy
* Update:2011-09-23、* Author:tt.t
*
* Description:将中文字符串转换为拼音,就这些。原先这里写了太多废话,删了。
*
* Theory:原理依然是通过IFELanguage接口实现。
*唯一需要解释的是如何解决多音字正确注音的问题。
*IFELanguage接口是能够正确返回很多多音字拼音的,但多音字的读音只有特定词汇中
*才能确认,因此在解析拼音时候不能把词拆成单字,否则多音字返回的拼音就很可能不对。
*之前版本中就是因为把词拆开获取拼音导致多音字拼音错误。
*这次的更新利用接口返回数据中标识每个拼音长度的数组实现了对返回拼音
*的按字拆分,无需再把词拆成字获取单个字的拼音,从而解决了多音字问题。
*需要说明的是,VB_MORRSLT结构就是MS文档中的MORRSLT结构,但是VBA自定义结构
*无法实现不按4字节对齐,使得不得不修改MORRSLT的定义方式,能这样修改只能说运气不错,
*因为被修改的部分刚好获取拼音用不到。
*
*
* Histroy:
*2011-09-23、*● 重写主要代码,支持多音字,提高了运行效率。
*● 取拼音首字时,ao, ai, ei, ou, er作为首字而不是原来的第一个字母。
*● 为函数增加了注音方式选择,hàn可以显示为han或han4。
*● 函数的使用与之前版本兼容,将模块中函数代码和HZ2PY类代码覆盖之前版本即可实现升级,无需修改文档中的公式。
*2011-04-07、*● 更正CoTaskMemFree传递参数错误,消除了Win7等环境下崩溃。
*2007-04-03、*● 更正redim时vba数组默认起始值错误。
*2007-04-02、*● 最初版本,实现了由汉字获取拼音。
*
***************************************************************************
Option Explicit
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
Private Type VB_MORRSLT
dwSize As Long4、pwchOutput As Long4、cchOutput As Integer2+(2),VBA内存对齐闹得,折腾了好一阵才确认问题所在,唉
Block1 As Long4、pchInputPos As Long4、pchOutputIdxWDD As Long 4、pchReadIdxWDD As Long4、paMonoRubyPos As Long4、pWDD As Long4、cWDD As Integer2、pPrivate As Long4、BLKBuff As Long4、End Type
Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, Source As Any, ByVal Length As Long)
Private Declare Function CLSIDFromString Lib "ole32.dll" _
(ByVal lpszProgID As Long, pCLSID As GUID) As Long
Private Declare Function CoCreateInstance Lib "ole32" ( _
rclsid As GUID, ByVal pUnkOuter As Long, _
ByVal dwClsContext As Long, riid As GUID, _
ByRef ppv As Long) As Long
Private Declare Function DispCallFunc Lib "oleaut32" _
(ByVal pvInstance As Long, ByVal oVft As Long, _
ByVal cc As Long, ByVal vtReturn As Integer, _
ByVal cActuals As Long, prgvt As Integer, _
prgpvarg As Long, pvargResult As Variant) As Long
Private Declare Sub CoTaskMemFree Lib "ole32" (pv As Long)
Dim MSIME_GUID As GUIDMSIMEs GUID
Dim IFELanguage_GUID As GUIDIFELanguages GUID
Dim IFELanguage As LongPointer to IFELanguage interface
Dim PinYinArray() As String
Dim HzLen As Integer
Dim pvSeperator As String
Dim pvUseSeperator As Boolean
Dim pvInitialOnly As Boolean
Dim pvOnlyOneChar As Boolean
Dim pvNonChnUseSep As Boolean
Public Function GetPinYin(HzStr As String) As String
Dim i As Integer
Dim Py As String
Dim IsPy As Boolean
GetPinYin = ""
If IFELanguage = 0 Then
GetPinYin = "未发现运行环境,请安装微软拼音2.0以上版本!"
Exit Function
End If
If HzStr = "" Then Exit Function
HzLen = Len(HzStr)
Call IFELanguage_GetMorphResult(HzStr)
For i = 1 To HzLen
Py = PinYinArray(i)
IsPy = Py <> ""
If Not IsPy Then Py = Mid(HzStr, i, 1)
If pvInitialOnly Then Py = GetInitial(Py)
If pvOnlyOneChar Then Py = VBA.Left(Py, 1)
GetPinYin = GetPinYin & Py & IIf(IsPy, pvSeperator, "")
Next i
If IsPy And pvSeperator <> "" Then GetPinYin = Left(GetPinYin, Len(GetPinYin) - 1)
End Function
Property Get Seperator() As String
Seperator = pvSeperator
End Property
Property Let Seperator(Value As String)
pvSeperator = Value
End Property
Property Get InitialOnly() As Boolean
UseSeperator = pvInitialOnly
End Property
Property Let InitialOnly(Value As Boolean)
pvInitialOnly = Value
End Property
Property Get OnlyOneChar() As Boolean
UseSeperator = pvOnlyOneChar
End Property
Property Let OnlyOneChar(Value As Boolean)
pvOnlyOneChar = Value
End Property
Public Function AdjustPhoneticNotation(Py As String, ByVal pn As Integer) As String
Dim i As Integer
Dim c As String
If pn = -1 Then
AdjustPhoneticNotation = Py
Exit Function
Else
For i = 1 To Len(Py)
c = VBA.Mid(Py, i, 1)
Select Case Asc(c)
Case VBA.Asc("ā") To VBA.Asc("à")
c = "a" & IIf(pn = 0, "", (VBA.Asc(c) - VBA.Asc("ā") + 1))
Case VBA.Asc("ē") To VBA.Asc("è")
c = "e" & IIf(pn = 0, "", (VBA.Asc(c) - VBA.Asc("ē") + 1))
Case VBA.Asc("ī") To VBA.Asc("ì")
c = "i" & IIf(pn = 0, "", (VBA.Asc(c) - VBA.Asc("ī") + 1))
Case VBA.Asc("ō") To VBA.Asc("ò")
c = "o" & IIf(pn = 0, "", (VBA.Asc(c) - VBA.Asc("ō") + 1))
Case VBA.Asc("ū") To VBA.Asc("ù")
c = "u" & IIf(pn = 0, "", (VBA.Asc(c) - VBA.Asc("ū") + 1))
Case VBA.Asc("ǖ") To VBA.Asc("ǜ")
c = "u" & IIf(pn = 0, "", (VBA.Asc(c) - VBA.Asc("ǖ") + 1))
Case VBA.Asc("ü")
c = "u"
Case VBA.Asc("ɡ")
c = "g"
End Select
AdjustPhoneticNotation = AdjustPhoneticNotation & c
Next i
End If
End Function
Private Function GetInitial(Py As String) As String
GetInitial = VBA.Mid(Py, 1, 2)
Select Case AdjustPhoneticNotation(GetInitial, 0)
Case "ch", "sh", "zh", "ao", "ai", "ei", "ou", "er"
Case Else
GetInitial = VBA.Left(GetInitial, 1)
End Select
End Function
Private Function IFELanguage_GetMorphResult(HzStr As String) As String
Dim ret As Variant
Dim pArgs(0 To 5) As Long
Dim vt(0 To 5) As Integer
Dim Args(0 To 5) As Long
Dim ResultPtr As Long
Dim TinyM As VB_MORRSLT
Dim Py() As Byte
Dim i As Integer
Dim j As Integer
Dim PinyinIndexArray() As Integer
IFELanguage_GetMorphResult = ""
If IFELanguage = 0 Then Exit Function
Args(0) = &H30000
Args(1) = &H40000100
Args(2) = Len(HzStr)
Args(3) = StrPtr(HzStr)
Args(4) = 0
Args(5) = VarPtr(ResultPtr)
For i = 0 To 5、vt(i) = vbLong
pArgs(i) = VarPtr(Args(i)) - 8、Next
Call DispCallFunc(IFELanguage, 20, 4, vbLong, 6, vt(0), pArgs(0), ret)
Call MoveMemory(TinyM, ByVal ResultPtr, Len(TinyM))
ReDim PinyinIndexArray(0 To HzLen - 1)
ReDim PinYinArray(1 To HzLen)
If TinyM.cchOutput > 0 Then
ReDim Py(0 To TinyM.cchOutput * 2 - 1)
Call MoveMemory(Py(0), ByVal TinyM.pwchOutput, TinyM.cchOutput * 2)
IFELanguage_GetMorphResult = Py
Call MoveMemory(PinyinIndexArray(0), ByVal TinyM.paMonoRubyPos + 2, HzLen * 2)
j = 0
For i = 0 To HzLen - 1、PinYinArray(i + 1) = VBA.Mid(IFELanguage_GetMorphResult, j + 1, PinyinIndexArray(i) - j)
j = PinyinIndexArray(i)
Next i
End If
Call CoTaskMemFree(ByVal ResultPtr)
End Function
Private Sub IFELanguage_Open()
Dim ret As Variant
Call DispCallFunc(IFELanguage, 4, 4, vbLong, 0, 0, 0, ret)
Call DispCallFunc(IFELanguage, 12, 4, vbLong, 0, 0, 0, ret)
End Sub
Private Sub IFELanguage_Close()
Dim ret As Variant
If IFELanguage = 0 Then Exit Sub
Call DispCallFunc(IFELanguage, 8, 4, vbLong, 0, 0, 0, ret)
Call DispCallFunc(IFELanguage, 16, 4, vbLong, 0, 0, 0, ret)
End Sub
Private Function GenerateGUID()
Dim Rlt As Long
MSIME.China GUID = "{E4288337-873B-11D1-BAA0-00AA00BBB8C0}"
Rlt = CLSIDFromString(StrPtr("MSIME.China"), MSIME_GUID)
IFELanguage GUID = "{019F7152-E6DB-11d0-83C3-00C04FDDB82E}"
With IFELanguage_GUID
.Data1 = &H19F7152、.Data2 = &HE6DB
.Data3 = &H11D0
.Data4(0) = &H83、.Data4(1) = &HC3、.Data4(2) = &H0
.Data4(3) = &HC0
.Data4(4) = &H4F
.Data4(5) = &HDD
.Data4(6) = &HB8、.Data4(7) = &H2E
End With
GenerateGUID = Rlt = 0
End Function
Private Sub Class_Initialize()
IFELanguage = 0
pvSeperator = ""
GenerateGUID
If CoCreateInstance(MSIME_GUID, 0, 1, IFELanguage_GUID, IFELanguage) = 0 Then Call IFELanguage_Open
End Sub
Private Sub Class_Terminate()
If IFELanguage <> 0 Then Call IFELanguage_Close
End Sub
Q7:excel怎么把把的汉字转换成拼音
1、打开要转换成拼音的excel表格
2、按“Alt+F11”组合键,进入Visual Basic编辑状态。也就是看到的这个灰色的编辑界面
3、执行“插入→模块”命令,插入一个新模块。再双击插入的模块,进入模块代码编辑状态;
4、看到如下界面
5、把下面的所有内容复制,粘贴到步骤4中的空白处
复制内容到剪贴板
Function pinyin(p As String) As String
i = Asc(p)
Select Case i
Case -20319 To -20318: pinyin = "a "
Case -20317 To -20305: pinyin = "ai "
Case -20304 To -20296: pinyin = "an "
Case -20295 To -20293: pinyin = "ang "
Case -20292 To -20284: pinyin = "ao "
Case -20283 To -20266: pinyin = "ba "
Case -20265 To -20258: pinyin = "bai "
Case -20257 To -20243: pinyin = "ban "
Case -20242 To -20231: pinyin = "bang "
Case -20230 To -20052: pinyin = "bao "
Case -20051 To -20037: pinyin = "bei "
Case -20036 To -20033: pinyin = "ben "
Case -20032 To -20027: pinyin = "beng "
Case -20026 To -20003: pinyin = "bi "
Case -20002 To -19991: pinyin = "bian "
Case -10587 To -10545: pinyin = "zhi "
Case -10544 To -10534: pinyin = "zhong "
Case -10533 To -10520: pinyin = "zhou "
Case -10519 To -10332: pinyin = "zhu "
Case -10331 To -10330: pinyin = "zhua "
Case -10329 To -10329: pinyin = "zhuai "
Case -10328 To -10323: pinyin = "zhuan "
Case -10322 To -10316: pinyin = "zhuang "
Case -10315 To -10310: pinyin = "zhui "
Case -10309 To -10308: pinyin = "zhun "
Case -10307 To -10297: pinyin = "zhuo "
Case -10296 To -10282: pinyin = "zi "
Case -10281 To -10275: pinyin = "zong "
Case -10274 To -10271: pinyin = "zou "
Case -10270 To -10263: pinyin = "zu "
Case -10262 To -10261: pinyin = "zuan "
Case -10260 To -10257: pinyin = "zui "
Case -10256 To -10255: pinyin = "zun "
Case -10254 To -10254: pinyin = "zuo "
Case Else: pinyin = p
End Select
End Function
Function getpy(str)
For i = 1 To Len(str)
getpy = getpy & pinyin(Mid(str, i, 1))
Next i
End Function
6、按下ALT+Q关闭Visual Basic编辑窗口,返回Excel编辑状态
7、选中转换后的拼音需要放在哪个列,例如要把B列的第2行的内容转换成拼音,放在D列的第2个单元格,输入公式:=getpy(B2),这里的B2,是指源头单元格的坐标
8、如果要去除拼音之间的空格。去掉空格的拼音放在E列,如果这个未去掉空格的数据原来在D2单元格,去掉空格之后的拼音放在E2单元格,则在E2单元格输入:
=SUBSTITUTE(D2," ","")