雙寶媽咪坯機的生活魔法百寶箱~@MotherTwins娃寶趣鋪

我想做零售做訂價的計算時候難免都會遇到一些奇奇怪怪的尾數

例如 成本是442.8571元， 要疊上1成的 利潤時，公式計算會是 442.8571*1.1=487.143

無條件捨去後為 487  基本上你不會用487這個數字當作賣價 ，總是會要多加一點點讓數字好一點 通常會想調整成 489

或者是如果計算出來回 364 你應該往往會調整成 365 

今天如果只有幾個那算一算 改一改就好了，假設今天遇到成千上百個要算呢?

簡單地無條件進位公式已經不好用，你要更進化 希望直接計算出來就是  5或是9 

那這種情況下該如何設定公式呢? 以下跟大家分享幾種公式設定

設定前提先假設 我們要計算的成本在 A1 欄位 ，B1欄位則是公式設定的位置

1.單純的設定公式

以下請整段複製

=CEILING(A1*1.1,1) + CHOOSE(MATCH(MOD(CEILING(A1*1.1,1),10),{0,1,2,3,4,5,6,7,8,9},0),5,4,3,2,1,0,9,8,7,6)

解釋如下

• A1*1.1：先計算折扣後的金額。
• CEILING(...,1)：先無條件進位成整數。
• MOD(...,10)：取得個位數。
• CHOOSE + MATCH：決定要補多少上去，使結果變成尾數為 5 或 9。

2.使用巨集 VBA 的模式

• 先按下 Alt + F11 開啟 VBA 編輯器。
• 點選「插入」→「模組（Module）」
• 貼上以下程式碼：

     Function RoundUpTo5or9(ByVal val As Double) As Double
    Dim result As Double
    result = WorksheetFunction.Ceiling(val, 1)
    
    Do While (Right(result, 1) <> "5" And Right(result, 1) <> "9")
        result = result + 1
    Loop
    
    RoundUpTo5or9 = result
End Function

•    接著在 B1的欄位上 輸入  

                  =RoundUpTo5or9(A1*1.1)

依照以上模式 就可以達成 加成後將尾數改成指定數字的計算了

再更精進一點如果要排除計算過後已經是 0、5、9的數字該怎樣設定呢?

請參考以下公式

1.巨集(VBA)的公式請複製以下文字，再依照巨集使用方式即可

 Function RoundUpTo5or9(ByVal val As Double) As Double
    Dim result As Double
    result = WorksheetFunction.Ceiling(val, 1)
    
    Do While (Right(result, 1) <> "0" And Right(result, 1) <> "5" And Right(result, 1) <> "9")
        result = result + 1
    Loop
    
    RoundUpTo5or9 = result
End Function

2.如果是使用一般公式請貼以下文字

=LET(
  val, CEILING(A1*1.1, 1),
  lastDigit, MOD(val,10),
  IF((lastDigit=0)+(lastDigit=5)+(lastDigit=9), val,
     val + XLOOKUP(TRUE, {1,1,1,1,1,1,1}, {1,2,3,4,5,6,7}, , , 1)))

解說：

• val：先把折扣價進位到整數。
• lastDigit：抓尾數（個位數）。
• 如果尾數是 0、5 或 9 就直接回傳。
• 否則加上最少的數字讓尾數變成 5 或 9（利用 XLOOKUP 逐一判斷）。

以上公式 我將欄位的部分 變成藍色，整段複製的文字是紅色的

各位使用上再依照自己的欄位調整囉

應該不困難 可以輕鬆解決 這個計算上的問題

另外提醒如果使用VBA 要保留計算等等  請記得存檔成 啟用巨集的活頁簿喔!!