Ragic 部落格
企業電子化的專家 Ragic 教你如何利用各種軟體、
雲端服務讓公司快速升級!
加入 Ragic 企業電子化的行列!
雲端工作術
各類應用示範
案例故事
逃離惡夢
關於 Ragic
Facebook Twitter YouTube
雲端資料庫
部落格
關於Ragic
雲端工作術
各類應用示範
案例故事
逃離惡夢
關於 Ragic

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

作者:Lillian Huang

本文出場公式:

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

公式 語法
Int(number) 將數字無條件捨位至最接近的整數
MOD(number, divisor) 傳回兩數相除後的餘數

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

公式 語法
ROUNDDOWN(value,N) 無條件捨去到小數點 N 位的值
MOD(value,divisor) 除以特定數字(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 ,表達意見讓我們知道。

標籤: 公式, Excel

分類: 雲端工作術, 表格技巧

部落格背後使用 Ragic! : 最強大的 No Code 企業電子化工具
把資料放在Excel上不只是拖累團隊的行政效率,他也很容易出錯並且無法進行任何內控。
當您的團隊成長時,使用Excel管理資料就會越來越痛苦。
建立你們的第一個雲端資料庫!

馬上註冊
免費試用 Ragic!

用 Google 帳號註冊

立即科技 Ragic, Inc.
02-7728-8692
台北市中正區南昌路二段81號9樓