0

## 再次請教excel 陣列問題

A1~F20區域有1~36數字分布,如何用陣列找出出現次數最少及次少的數字(如有兩個以上,取最大的數字),謝謝!

wsj560115 iT邦新手 5 級 ‧ 2020-02-14 15:21:45 檢舉

### 1 個回答

0
Neish
iT邦研究生 2 級 ‧ 2020-02-14 14:14:20

``````Option Explicit

Sub test0214()

Dim first_small_num As Integer
Dim first_small_count As Integer
Dim second_small_num As Integer
Dim second_small_count As Integer
Dim i As Integer

'最少
first_small_num = 0
first_small_count = 1000

'次少
second_small_num = 0
second_small_count = 1000

For i = 1 To 36

'最少 - 出現次數 如果比目前的first_small_count還要少(含等於) 就把first_small_num換掉
If Application.CountIf(Range("A1:F20"), i) <= first_small_count Then

first_small_count = Application.CountIf(Range("A1:F20"), i)
first_small_num = i

End If

'次少 - 出現次數 如果比目前的second_small_count還要少(含等於) 但是比first_small_count還要多 就把second_small_num換掉
If Application.CountIf(Range("A1:F20"), i) > first_small_count And _
Application.CountIf(Range("A1:F20"), i) <= second_small_count Then

second_small_count = Application.CountIf(Range("A1:F20"), i)
second_small_num = i

End If

Next

Range("I1") = first_small_num
Range("I2") = second_small_num

End Sub

``````