iT邦幫忙

0

再次請教excel 陣列問題

請問各位大大,
我有個excel問題請求大大協助解答,如下:
A1~F20區域有1~36數字分布,如何用陣列找出出現次數最少及次少的數字(如有兩個以上,取最大的數字),謝謝!
https://ithelp.ithome.com.tw/upload/images/20200214/20123369RwL0IVzA5G.jpg

要做還是可以做得出來,但一般人應該會看不懂,日後維護會很困難。
你若想學,應該從根本去了解陣列公式(Array Formula)。
wsj560115 iT邦新手 5 級 ‧ 2020-02-14 15:21:45 檢舉
謝謝您的建議

1 個回答

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

我是沒有使用陣列

單純用FOR迴圈也可以達到您的目的

基本上是用函數COUNTIF來實現

實作檔案如下
https://c-t.work/s/bd1ea48376744d

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

但是看您的需求
我會單純把數字串成一欄
用COUNTIF來計數 再用排序來找次數結果

我要發表回答

立即登入回答