想做一个EXCEL表格，实现助记码功能
答案:6 悬赏:0 手机版
解决时间 2021-11-20 05:17
- 提问者网友：难遇难求
- 2021-11-19 06:17
想做一个EXCEL表格，实现助记码功能
最佳答案
- 五星知识达人网友：怙棘
- 2021-11-19 06:43
使用方法：
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(相应的汉字单元格)。
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(相应的汉字单元格)。
全部回答
- 1楼网友：枭雄戏美人
- 2021-11-19 11:01
以文本形式输入，，在格式里选择
- 2楼网友：狂恋
- 2021-11-19 10:29
做一个助记码与药名、单价、计量单位的对照表；如图：
B列公式：=VLOOKUP(A2,$G$3:$J$10,2,0)
C列公式：=VLOOKUP(A2,$G$3:$J$10,3,0)
D列公式：=VLOOKUP(A2,$G$3:$J$10,4,0)
- 3楼网友：不如潦草
- 2021-11-19 09:56
例如我在A1单元格中输入“kb”,然后可以在B1单元格中输入公式：=IF(A1="kb","中文昆布",A1)，在C1单元格中以此类推即可。
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯