【 Excel 與 Ragic 通用公式介紹#1】快速計算贈品數、算出應付費用等於多少鈔票及硬幣

本文出場公式：

Excel 的「INT」與「MOD」函數：

Ragic 的 ROUNDDOWN(value,N)以及MOD(value,divisor)公式

相同與不同： Excel 和 Ragic 都有計算餘數的「MOD」公式，用法相同；關於「取整數」， Excel 有一個專門取整數的函數「INT」， Ragic 則有「無條件捨去到小數點後 N 位」，當 N=0 時就是取整數。

應用情境：舉辦「滿X送X」活動時，自動計算應給的贈品數；需付大量現金如工讀金、講師費時，計算需要多少各面額的鈔票、零錢。

情境一：「買X送X活動」計算贈品數

快樂文具公司辦週年慶促銷， 90 元紙膠帶訂購滿 10 個，即另外贈送 2 個贈品紙膠帶；若滿 20 個，則贈送 5 個，也就是「滿 20 送 5、滿 10 送 2」。那麼，假如客人一口氣訂了 58 個紙膠帶，應該給多少贈品？如何根據客人訂單上的訂購數字，自動算出應該附上多少贈品給客人？

Excel 公式拆解：

以「訂購 58 個紙膠帶」為例，拆解「滿 20 送 5、滿 10 送 2 」的贈品計算邏輯。首先，訂購數量只要達到「滿 20」、「滿 10」任一條件，就要計算贈品，「滿 20」的贈品條件較佳，因此得先計算 58 個紙膠帶能拿到多少「滿 20」的贈品？

58除以20 = 2.9，顯示訂購數量= 58 時，「滿 20」的條件達到兩次，剩下的數量湊不滿 20，不計。因此「滿 20」條件次數 =58/20 無條件捨去小數點以後位數。這部分每達成一次條件可以拿到 5 個贈品，因此可以拿 2x5=10 個贈品。

因此，假設要套公式的「訂購數量」儲存格為D12，此部分贈品數量在 Excel 中的公式會是：=INT(A1/20)*5

接下來，要計算「訂購數量沒有滿 20，但有滿 10 」的條件數量，以確認第二個條件——「滿 10 」可以拿到多少贈品。「訂購數量沒有滿 20 」的數量，就是「 58 除以 20 」的餘數 18，此部分 Excel 公式是：=MOD(D12,20)

因此「滿10」可以拿到的贈品數量為：=INT(MOD(D12,20)/10)*2

將兩個條件加總，「滿20送5、滿10送2」的贈品公式即為：=INT(D12/20)5+INT(MOD(D12,20)/10)2

Ragic 公式拆解

在 Ragic 套公式的方法和 Excel 相似，唯一的小差別是，在 Excel 裡，公式要放在每一個需要公式的儲存格裡，而 Ragic 則是以每一欄的欄位標頭作為參照標準和放公式的地方。

如下圖，假設要套公式的欄位標頭(「數量」欄位)是 D13，要參照的是D11，就是在 D13 放入公式：ROUNDDOWN(A1/20,0)5+ROUNDDOWN(MOD(A1,20)/10,0)2

就可以了，該欄位標頭對應的每一列儲存格都會套用此公式。

同時因為在 Ragic 製作訂單很方便，可以直接在訂單上加上這個「贈品數」欄位，不用額外開檔案算來算去了。

情境二：將特定金額換算成幾張鈔票、多少零錢

上進公司舉辦大型講座活動，為期一天的活動行程滿滿，從早到晚請了六位講師上台。為了讓活動順利進行，也請了大批工讀生，有人做滿八小時，也有人只做上午四小時。上進公司打算活動當天就發放講師費、工讀費，直接以現金支付，因此想預先算出要準備多少鈔票、多少零錢，以便分裝到給每個人的現金袋中。

講師費＆工讀費用如下：

8 位講師中， 4 位資深講師每位 4800 元， 4 位年輕講師每位 3500 元；工讀金每小時 128 元， 10 位工讀生中， 4 位工讀生只做四小時領 512 元，6 位做滿 8 小時領 1024 元。

Excel 函數拆解

結合前述公式，可算出需要準備的現鈔、零錢合計數字：

做法為，先在「千元鈔」欄位套用INT公式例如=INT(C2/1000)，即應付費用除以千，去掉小數點（餘數）：

接著在「五百元鈔」欄位，要看的是「應付費用除以千的餘數」有多少能被五百整除的，公式範例為=INT(MOD(C2,1000)/500)；百元鈔、十元等欄位依此類推，「一元」欄位會是非常多層條件的公式：

雖然公式比較長，但一但套用好就可以算出多筆資料的情形。最後在「總和」的欄位套用加總公式=SUM(value)，就能算出總計需要多少各面額現鈔/零錢了。

Ragic 用法拆解

Ragic 的公式用法基本上跟 Excel 一樣，只要將 Excel 的Int(Number) 換成 Ragic 的 ROUNDDOWN(Number,0) 就可以了。在 Ragic 更方便的地方是，你可以很簡便的設定一些表單連結，因此能直接根據給演講人簽名的收據來計算（不用在不同的文件或工作表複製貼上數字）。

舉例來說：用 Ragic 做的演講收據表單長這樣：

有簽名欄位讓領款人直接透過手機或手寫板簽名，「實收金額」欄位可套用公式（金額-代扣稅額-代扣健保費），收據可以自行設計不同的排版格式，有需要的話這收據也可以列印出來（友善列印或合併列印）。

同時，可以用多版本工作表的功能，將這個收據自動複製出一份內部留存與結算的版本，並在這個版本上增加「計算現金」的欄位（如紅框處），只有內部人看得到。

在這些欄位套用前述的公式。例如下圖是「千元鈔」欄位套用的公式，「一元」欄位套用的公式則是 ROUNDDOWN(MOD(MOD(MOD(MOD(D8,1000),500),100),10),0)

資料都儲存後切換到列表頁，可看到所有講師需要的現金數量，和上面 Excel 例子類似。

點按上圖下方「計算所有資料加總」的灰體小字，就會算出加總結果：

這樣就可以無縫完成整段流程了！

希望我們針對哪種公式撰寫實例教學？想要使用的公式在 Ragic 上還沒有，希望 Ragic 支援？您都可以寫信到 support@ragic.com ，表達意見讓我們知道。