![查找值返回多个连接的值](https://linux22.com/image/1571234/%E6%9F%A5%E6%89%BE%E5%80%BC%E8%BF%94%E5%9B%9E%E5%A4%9A%E4%B8%AA%E8%BF%9E%E6%8E%A5%E7%9A%84%E5%80%BC.png)
答案1
答案2
我有一个可行的解决方案。按Alt+F11进入 Visual Basic 编辑器,创建一个新模块并将此代码粘贴到其中。您将能够CAT
在任何单元格中使用该函数,如下所示:=CAT(category,qualities)
。
' Function that expects a quality and returns the respective code
' Important: Tick check box beside "Microsoft Scripting Runtime" in Tools > References
Function lookup_code(ByRef quality)
Dim dict As New Scripting.Dictionary
dict.Add "Arboricultural", "1"
dict.Add "Landscape", "2"
dict.Add "Cultural_and_Conservation", "3"
lookup_code = dict(quality)
End Function
' Function to output the concatenated CAT code
' Use in any Excel cell as `=CAT(category, qualities)`
' Expects a category (e.g. "A") and a string of one or more qualities, separated by a comma
Function CAT(category, qualities)
Dim code As String
If InStr(qualities, ",") > 0 Then ' Check for commas
Dim QualityArray() As String
QualityArray = Split(qualities, ",") ' Split string at commas
For Each q In QualityArray
code = code & "," & lookup_code(q) ' match code to quality
Next
Else
code = "," & lookup_code(CStr(qualities))
End If
CAT = category & code ' return category and codes (e.g. "B,2,3,1")
End Function