|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
再改进一下,直接用数组公式自定义函数。一次计算,全部填充:
工作簿1.7z
(17.31 KB, 下载次数: 1)
- Function GetNumberArray(data As Range, key As Range)
- Dim RegEx, Dic As Object
- Set RegEx = CreateObject("VBScript.RegExp")
- Set Dic = CreateObject("scripting.dictionary")
- RegEx.Pattern = "([^\d\.\+\s]+)([\d\.]+)"
- RegEx.IgnoreCase = True
- RegEx.Global = True
- Dim w, h As Long
- w = key.Columns.Count
- h = data.Rows.Count
- Dim Result(), i, j, datastr, keystr
- Dim Match, Matches As Object
- ReDim Result(1 To h, 1 To w)
- For i = 1 To h
- datastr = Trim(data.Cells(i, 1))
- If datastr <> "" Then
- Set Matches = RegEx.Execute(datastr)
- Dic.RemoveAll
- For Each Match In Matches
- Dic(CStr(Match.submatches(0))) = CDbl(Match.submatches(1))
- Next
- End If
- For j = 1 To w
- keystr = Trim(key.Cells(1, j))
- If datastr <> "" Or keystr <> "" Then
- Result(i, j) = Dic(keystr)
- Else
- Result(i, j) = ""
- End If
- Next j
- Next i
- GetNumberArray = Result
- End Function
复制代码
|
|