2012年12月8日 星期六

Excel VBA 四捨六入五成雙的設計

Excel VBA d005d1四捨六入

現在被廣泛使用的取概數規則,除了四捨五入以外,還有四捨六入五成雙規則。

四捨五入簡化規則中,因逢五就進,會造成結果的系統偏高,誤差偏大。因為它進位的「機率」高於捨棄,在統計學上會產生偏差,解釋如下:

取位位值是:1、2、3、4 →機率 4/9
取位位值是:5、6、7、8 、9→機率 5/9

如上面舉例,一個數字在四捨五入中,捨去的機率比進位還低,這樣子不大公平,所以後來有了四捨五入六成雙規則。所以我們如果要使用四捨六入五成雙的設計,就得自已設計一個,在設計之前,必須先了解這個數值規則,可以參考:http://zh.wikipedia.org/wiki/數值修約規則,我自已理解後,認為規則應該是:

一個數字,取概數到n位的話,那麼一樣是看(n-1)位的數字

1.如果數字是4以下則捨去,6以上則進位

2.如果是5,那麼:
   (1)如果5後面的有效數字中,有非0的數字,進位
   (2)如果5後面的有效數字中,都是0:
        a.如果5的前面是奇數,進位
        b.如果5的前面是偶數或是0,捨去

整個過程還是在對「進位」和「捨去」,不過規則上修改的比較複雜就是了,以下就把過程寫下來

程式碼如下

Public Function 四捨六入(數值1, 位值)
    Dim n, myroundup, myrounddown
    
    n = Right(Fix((數值1 * (10) ^ (位值 + 1))), 1)
    myroundup = Fix((數值1 * (10) ^ 位值) + 1) / (10) ^ 位值
    myrounddown = Fix((數值1 * (10) ^ 位值)) / (10) ^ 位值
    
   
    If n <= 4 Then
        四捨六入 = myrounddown
    End If
    If n >= 6 Then
        四捨六入 = myroundup
    End If
    If n = 5 Then
        If Int(數值1) = 數值1 Then
            四捨六入 = myroundup
        Else
            If Right(Fix((數值1 * (10) ^ 位值)), 1) = 0 Then
                四捨六入 = myrounddown
            Else
                If Right(Fix((數值1 * (10) ^ 位值)), 1) Mod 2 = 0 Then
                    四捨六入 = myrounddown
                Else
                    四捨六入 = myroundup
                End If
            End If
        End If
    End If
End Function

  • 列1:Public Function 四捨六入(數值1, 位值)
    我們是要使用「自訂函數」,方便在excel的儲存格上能套用,所以程序的型態是Function,函數的名稱和後面的引數都可以是中文
    image
  • 列4: n = Right(Fix((數值1 * (10) ^ (位值 + 1))), 1)
    這列的功能是要取最後一位,判斷該位值的數值是多少。這裡我們就實際舉例來說明:
    數值1是 3.1415926,取到小數第3位的位值
    數值1 * (10) ^ (位值 + 1) = 3.1415926 * 10的4次方 = 31415.926。
    Fix(31415.926) 就是把值去掉小數,所以等於31415
    Right(31415,1)就是找出最右邊1位的值,所以等於5
  • 列5:myroundup = Fix((數值1 * (10) ^ 位值) + 1) / (10) ^ 位值
    列5的作用是自定義一個無條件進位的方法,這是一個蠻古老的方法了,這邊就不多說
  • 列6:myrounddown = Fix((數值1 * (10) ^ 位值)) / (10) ^ 位值
    列6的作用是自定義一個無條件捨去的方法
  • 列9~列29:
    這些巢狀的判別式,就是依照「四捨六入」的規則寫成,照著上面我提到的規則,邏輯上還算是蠻好推的。比較難的是要了解規則,建議要自已先舉一些例子,用「人腦」判斷四捨六入的結果,再去歸納一下,我這裡寫的就是我自已整理出來的邏輯,這裡的方法有點「笨」,每個人的邏輯力不同,所以這大段判別式也因人而異。
其他說明
  • 函數的使用除了直接在儲存格上打以外,也可以在功能表中的「插入函數」中找到自已定義的函數。如下圖
    image
    image
  • 這裡只是做「小數取概數」的示範,如果是要「整數以四捨六入取概數」,那麼還要另行設計喔,怎麼設計呢?如果你知道在fx中作四捨五入的round函式裡,如果是要某數取概數到百位,是要寫成 round (某數,-2)的話(如下圖),那麼我們只要在目前的程式裡加些條件判斷就可以達到這個目的了。
    image
  • 這裡有一個地方要特別提一下,在自訂function函數時,我們使用了像right之類的函數,嚴格說起來,這個函數不是我們在excel應用程式上調用的那種fx,它是vbscript中的函數。如果可以調用的是fx,那麼我在上面要取無條件進入就可以使用roundup()、無條件捨去就可以直接拿rounddown來做了。(roundup、rounddown是可以在fx裡找的到的喔)
  • 另外,四捨六入在VB6裡,其實已經有函數了,這個函數大家也不陌生,就是round,如下圖是我自已拿.NET測試的,這個round在excel裡還是四捨五入法吧。這也說明了excel為了顧及單機版本間的互通性,一些舊有的script都不敢亂動。所以此round非彼round
    image

2 則留言:

  1. 據說以下巨集函數就可以達到四捨六入五留雙的目的

    Function bround(c#, d%)
    bround = VBA.Round(CDec(c), d)
    End Function

    回覆刪除
  2. 大大您好~我想請問excel 有沒有可能自訂一個函數 整數位是10進位,小數點後是32進位?
    如509.31+0.01=510
    拜託拜託 謝謝

    回覆刪除

Related Posts Plugin for WordPress, Blogger...
// Dnow Function