返回目录：excel表格制作
以Excel2007为例：
1、打开相应的Excel文件,按“Alt+F11”组合键，弹出“Visual Basic编辑器”如下图：
2、鼠标点击“Visual Basic编辑器”菜单栏的“插入”选项，选择下拉列表里的“模块”，插入“模块”，如下图：
3、将下面的代码复制粘贴到里面：
Function getpychar(char)
tmp = 65536 + Asc(char)
If (tmp >= 45217 And tmp <= 45252) Then
getpychar = "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"
ElseIf (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 >= 53679 And tmp <= 54480) Then
getpychar = "Y"
ElseIf (tmp >= 54481 And tmp <= 62289) Then
getpychar = "Z"
Else '如果不是e799bee5baa6e78988e69d83332中文，则不处理
getpychar = char
End If
End Function
'逐个取ASC码
Function getpy(str)
For i = 1 To Len(str)
getpy = getpy & getpychar(Mid(str, i, 1))
Next i
End Function
然后关闭Visual Basic编辑窗口，返回Excel编辑状态。分别如下图：
4、选中B2单元格，输入公式"=getpy(a1)",鼠标移到B2单元格右下角，下拉填充公式即可，如下图：
你可以用Vlookup函数来实现
第一个表里建立如下：
助记码 来品名 价格 单位
KB 昆布 0.50 g
DG 当归 1.50 g
LH 硫磺 2.00 g
LR 鹿茸源 30.00 g
DCXC 冬虫夏草 15.00 g
第二个表里你建立公式
B2==VLOOKUP(A2,Sheet2!A:D,2,0)
C2==VLOOKUP(A2,Sheet2!A:D,3,0)
D2==VLOOKUP(A2,Sheet2!A:D,4,0)
然后你向下拖放一下公式就OK了。
然后在A列输入助记码！就成下面的样子了
助记码 zd品名 价格 单位
KB 昆布 0.5 g
DG 当归 1.5 g
LR 鹿茸 30 g
DCXC 冬虫夏草 15 g
DG 当归 1.5 g
KB 昆布 0.5 g
LR 鹿茸 30 g
DG 当归 1.5 g
LR 鹿茸 30 g
KB 昆布 0.5 g
Wps
方法如下：
1、首先，在WPS中输入文字，比如“北京”，然后选中文知字，然后点击上边有拼音的道“文”标识图标，
2、点击“拼音指南”后会弹出一个对话框，从对话框中可以内看到已经自动给拼上音了，如图感觉拼音太小的话可以把字号加大一些。
3、加上拼音后的效果如容图所示。
使用方法：7a686964616fe78988e69d83337
1、 打开要生成的EXCEL文件，选择工具--宏---Visual Basic编辑器（快捷键：alt+f11）
2、 选择：插入--模块--把下方的代码粘贴到里面，保存
3、 代码部分：
Function PinYin(Hz As String)
Dim PinMa As String
Dim MyPinMa As Variant
Dim Temp As Integer, i As Integer, j As Integer
PinMa = "a,20319,"
PinMa = PinMa & "b,20283,"
PinMa = PinMa & "c,19775,"
PinMa = PinMa & "d,19218,"
PinMa = PinMa & "e,18710,"
PinMa = PinMa & "f,18526,"
PinMa = PinMa & "g,18239,"
PinMa = PinMa & "h,17922,"
PinMa = PinMa & "j,17417,"
PinMa = PinMa & "k,16474,"
PinMa = PinMa & "l,16212,"
PinMa = PinMa & "m,15640,"
PinMa = PinMa & "n,15165,"
PinMa = PinMa & "o,14922,"
PinMa = PinMa & "p,14914,"
PinMa = PinMa & "q,14630,"
PinMa = PinMa & "r,14149,"
PinMa = PinMa & "s,14090,"
PinMa = PinMa & "t,13318,"
PinMa = PinMa & "w,12838,"
PinMa = PinMa & "x,12556,"
PinMa = PinMa & "y,11847,"
PinMa = PinMa & "z,11055,"
MyPinMa = Split(PinMa, ",")
For i = 1 To Len(Hz)
Temp = Asc(Mid(Hz, i, 1))
If Temp < 0 Then
Temp = Abs(Temp)
For j = 45 To 1 Step -2
If Temp <= Val(MyPinMa(j)) Then
PinYin = PinYin & MyPinMa(j - 1)
Exit For
End If
Next
Else
' 保留非汉字字符
PinYin = PinYin & Mid(Hz, i, 1)
End If
Next
PinYin = Trim(PinYin)
End Function
4、 保存后，回到EXCEL内容编辑界面
5、 在需要生成助记码的地方。输入公式 =pinyin(相应的汉字单元格)