第1个回答 2012-12-19
利用宏:
Function Dict(i As Integer) As String
s = ""
Select Case i
Case 1: s = "玉米"
Case 2: s = "大豆"
Case 3: s = "莲子"
Case 4: s = "红薯"
Case 9: s = "芋头"
Case 35: s = "黄油"
Case 40: s = "蛋卷"
Case 45: s = "青菜"
Case 20: s = "黑米"
Case 19: s = "鸡蛋"
'case 100: s="..."
Case Else: s = "添加你的词条"
End Select
Dict = s
End Function
Sub ReplaceAll()
'MsgBox Worksheets.Count
Dim ws As Worksheet
Set ws = Worksheets(1)
'MsgBox ws.Cells(1, 2).Value
'MsgBox ws.Range("a1:a5").Cells.Count
i = 1 ' 改成“替换前”的首行号
k = 1 ' 改成“替换前”所在的列号
j = 5 ' 改成“替换后”所在的列号
v = ws.Cells(i, k).Value
Do
ws.Cells(i, j) = Dict(CInt(v))
i = i + 1
v = ws.Cells(i, k)
Loop While v <> 0
End Sub
第2个回答 2012-12-19
=INDEX({"玉米";"大豆";"莲子";"红薯";"芋头";"鸡蛋";"黑米";"黄油";"蛋卷";"青菜"},MATCH(A2,{1;2;3;4;9;19;20;35;40;45},0))
下拉
或
=VLOOKUP(A2,{1,"玉米";2,"大豆";3,"莲子";4,"红薯";9,"芋头";19,"鸡蛋";20,"黑米";35,"黄油";40,"蛋卷";45,"青菜"},2,FALSE)
下拉