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

4 則留言:

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

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

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

    回覆刪除
  3. Slot Machines - JtmHub
    JTM Sports Lounge. JTM 광주 출장마사지 Sports 과천 출장안마 Lounge. JTM Sports Lounge. JTM Sports Lounge. JTM Sports Lounge. JTM Sports Lounge. JTM Sports 제천 출장안마 Lounge. 당진 출장안마 JTM Sports 서울특별 출장마사지

    回覆刪除
  4. r2e=(a*10^b+2^52-2^52)/10^b

    回覆刪除

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