Re: [算表] VBA移除陣列中元素
想請教各位版大，以下有一function，其作用是將item_range中的項目對number_range
進行累加並回傳第rank_order大之項目名稱，但有時會有兩個項目的累加金額相同，這
時候如果使用match函數則只會取首位，因此會出現第1名和第2名皆為同一項目名稱的問
題。
在下面程式中我if…then的方式進行判斷，雖然可以解決這個問題，不過程式看起來有點
冗長，不知道有沒有更為簡短的寫法? 謝謝!
Function inventory_rank4(item_range As Range, number_range As Range, _
rank_order As Integer) As String
Dim r As Range
Dim ary(10)
Dim w&, dic
Set dic = CreateObject("scripting.dictionary")
For Each r In item_range
If r <> "" Then If Not dic.exists(r.Value) Then _
dic.Add r.Value, r.Value: _
ary(w) = WorksheetFunction.SumIf(item_range, r, number_range): _
w = w + 1
Next
If rank_order = 1 Then
inventory_rank4 = dic.Items()(WorksheetFunction.Match( _
WorksheetFunction.Large(ary, rank_order), ary, 0) - 1)
Else
If WorksheetFunction.Large(ary, rank_order) = _
WorksheetFunction.Large(ary, rank_order - 1) Then
ary(WorksheetFunction.Match(WorksheetFunction.Large(ary, _
rank_order), ary, 0) - 1) = 0
inventory_rank4 = dic.Items()(WorksheetFunction.Match( _
WorksheetFunction.Large(ary, rank_order - 1), ary, 0) - 1)
Else
inventory_rank4 = dic.Items()(WorksheetFunction.Match( _
WorksheetFunction.Large(ary, rank_order), ary, 0) - 1)
End If
End If
End Function
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.237.75.218
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1457606693.A.5C3.html
→
03/10 22:29, , 1F
03/10 22:29, 1F
→
03/10 22:29, , 2F
03/10 22:29, 2F
→
03/11 06:54, , 3F
03/11 06:54, 3F
→
03/11 06:54, , 4F
03/11 06:54, 4F
→
03/11 06:54, , 5F
03/11 06:54, 5F
→
03/11 06:54, , 6F
03/11 06:54, 6F
→
03/11 06:54, , 7F
03/11 06:54, 7F
→
03/11 09:03, , 8F
03/11 09:03, 8F
→
03/11 09:04, , 9F
03/11 09:04, 9F
→
03/11 09:06, , 10F
03/11 09:06, 10F
→
03/12 07:36, , 11F
03/12 07:36, 11F
討論串 (同標題文章)
Office 近期熱門文章
PTT數位生活區 即時熱門文章
