這次要整理八個長得很樸素的數學函式：MOD、ABS、POWER、SQRT、SIGN、PRODUCT、ISEVEN 跟 ISODD。
它們單獨看都很簡單，但常常是別的公式裡的小零件。坦白說我用最多的是 MOD 跟 ABS，剩下的偶爾才出場，所以這篇當工具書，需要的時候回來查就好。四捨五入那一組在 四捨五入函式整理，這篇不重複。
後面的例子都用這張小表，A 欄是品項，B 欄是實際賣出的杯數，C 欄是目標杯數：
| A | B | C | |
|---|---|---|---|
| 1 | 品項 | 實際 | 目標 |
| 2 | 蘋果紅茶 | 37 | 40 |
| 3 | 青茶 | 112 | 100 |
| 4 | 烏龍茶 | 8 | 10 |
| 5 | 蘋果綠茶 | 53 | 50 |
| 6 | 檸檬紅茶 | 26 | 30 |
| 7 | 桂花烏龍 | 91 | 80 |
| 8 | 熟成紅茶 | 14 | 15 |
怎麼選
- 想知道除完剩多少、想每隔幾列做一次事：
MOD。 - 只在乎差多少、不管誰大誰小：
ABS。 - 只想知道是正、負還是零：
SIGN。 - 次方跟開根號：
POWER（也可以用^）、SQRT。 - 一排數字全部乘起來：
PRODUCT。 - 判斷整數是奇數還是偶數：
ISODD、ISEVEN。
餘數與正負
MOD
MOD 回傳兩數相除後剩下的餘數。
語法：
- 被除數：要被除的數字。
- 除數：拿來除的數字。兩個都必填。
我想知道每箱裝 12 杯，B2 的 37 杯裝完整箱後會剩幾杯。手算：12 × 3 = 36，37 − 36 = 1。用函式寫就是：
37 杯每箱裝 12 杯，剩幾杯
=MOD(B2, 12)你應該會得到 1！往下複製到整欄，B2 到 B8 的餘數依序是：
| 實際 | 每箱 12 杯剩幾杯 | |
|---|---|---|
| 2 | 37 | 1 |
| 3 | 112 | 4 |
| 4 | 8 | 8 |
| 5 | 53 | 5 |
| 6 | 26 | 2 |
| 7 | 91 | 7 |
| 8 | 14 | 2 |
幾箱則是 ，37 杯是 3 箱。
MOD 很常拿來做時間換算。125 分鐘是幾小時幾分鐘：
| 公式 | 顯示結果 |
|---|---|
| 2 | |
| 5 |
也就是 2 小時 5 分。
還有隔幾列做一次事。 在第 3、6、9⋯⋯列會是 TRUE，拿去當條件式格式的條件，就能每三列塗一次色。
兩個要注意的地方：
- 除數是 0 會得到
#DIV/0!。 - 負數的餘數，符號會跟著除數走。 是 2， 是 -2。這點跟有些程式語言不一樣，Excel 也是同一個規則。
ABS
ABS 會去掉負號，回傳絕對值。
語法：
我想看每個品項離目標差幾杯，不管是多賣還是少賣。先算「實際減目標」：
實際與目標的差，會有正有負
=B2-C2蘋果紅茶是 -3，青茶是 12，有正有負。包一層 ABS 就只看距離：
只看差多少，不看方向
=ABS(B2-C2)| 實際減目標 | ABS | |
|---|---|---|
| 2 | -3 | 3 |
| 3 | 12 | 12 |
| 4 | -2 | 2 |
| 5 | 3 | 3 |
| 6 | -4 | 4 |
| 7 | 11 | 11 |
| 8 | -1 | 1 |
七個品項的總差距，不讓正負互相抵銷，可以一次算：
七個品項的絕對差距加總
=ARRAYFORMULA(SUM(ABS(B2:B8-C2:C8)))3 + 12 + 2 + 3 + 4 + 11 + 1 = 36，你應該會得到 36。如果直接算 ，正負會互相抵銷，只會得到 16，兩者差很多。
SIGN
SIGN 只問方向：負數回 -1，零回 0，正數回 1。
語法：
多賣是 1，少賣是 -1
=SIGN(B2-C2)| 實際減目標 | SIGN | |
|---|---|---|
| 2 | -3 | -1 |
| 3 | 12 | 1 |
| 4 | -2 | -1 |
| 5 | 3 | 1 |
| 6 | -4 | -1 |
| 7 | 11 | 1 |
| 8 | -1 | -1 |
把 SIGN 的結果放在 D 欄，要數多賣的品項有幾個，就是 ，答案是 3。老實說我很少單獨用 SIGN，通常是想用 -1、0、1 去當成別的函式的選項時才拿來墊一下。
次方與開根號
POWER
POWER 回傳底數的指定次方。
語法：
- 底數：要連乘的數。
- 指數：乘幾次。可以是小數，也可以是負數。
| 公式 | 顯示結果 |
|---|---|
| 1024 | |
| 125 | |
| 0.25 | |
| 3 |
最後一列用 0.5 次方，就是開根號。
^ 運算子做同一件事， 跟 都是 125。我個人幾乎都直接寫 ^，比較短；POWER 的好處是公式比較好讀，尤其是參數是別的函式的時候。
最常見的用途是複利。我有 28,600 元，年利率 1.5%，放 8 年。手算要連乘 8 次，用 POWER：
複利 8 年，四捨五入到整數
=ROUND(28600 * POWER(1.015, 8), 0)結果是 32,218。外面的 ROUND 在 四捨五入函式整理 有講。
SQRT
SQRT 回傳正數的平方根。
語法：
| 公式 | 顯示結果 |
|---|---|
| 12 | |
| #NUM! |
負數開根號會得到 #NUM!。如果你的數字可能是負的，又只想要它的大小，可以先包 ABS： 是 2。
來看一個用途：平面上兩點 (3, 7) 跟 (11, 2) 的直線距離。x 差 8、y 差 5，距離是 8 平方加 5 平方再開根號：
兩點距離，取兩位小數
=ROUND(SQRT(POWER(11-3, 2) + POWER(2-7, 2)), 2)8 × 8 + 5 × 5 = 89，√89 約 9.4340，你應該會得到 9.43。
連乘與奇偶
PRODUCT
PRODUCT 把一串數字全部乘起來。
語法：
可以放單格、範圍，也可以混著放。
| 公式 | 顯示結果 |
|---|---|
| 33152 |
37 × 112 × 8 = 33,152。範圍裡的文字會被略過，所以 只會乘到 B2 的 37。
它真正派上用場的是連續的成長率。假設三個月的月成長率是 +3%、-2%、+5%，也就是 0.03、-0.02、0.05，放在 G2 到 G4。三個月總共成長多少，不能把 3、-2、5 直接加起來，要把「1 加成長率」乘起來：
三個月的成長率連乘
=ARRAYFORMULA(PRODUCT(1 + G2:G4))| 公式 | 顯示結果 |
|---|---|
| 1.05987 |
1.03 × 0.98 × 1.05 = 1.05987，也就是三個月總共成長約 5.99%。
ISEVEN
ISEVEN 判斷數字是不是偶數，是就回 TRUE。
語法：
| 公式 | 顯示結果 |
|---|---|
| TRUE | |
| FALSE | |
| TRUE |
我用它的機會，多半是配 ROW 取偶數列。例如只留下表格的偶數列（第 2、4、6、8 列）：
只留下偶數列
=FILTER(A2:B8, ISEVEN(ROW(A2:A8)))公式放在 E2，結果會展開到 E2:F5：
| E | F | |
|---|---|---|
| 2 | 蘋果紅茶 | 37 |
| 3 | 烏龍茶 | 8 |
| 4 | 檸檬紅茶 | 26 |
| 5 | 熟成紅茶 | 14 |
FILTER 的細節在 FILTER 函式。
ISODD
ISODD 跟 ISEVEN 正好相反，奇數回 TRUE。
語法：
| 公式 | 顯示結果 |
|---|---|
| TRUE | |
| FALSE | |
| TRUE |
負數也能判斷。其實 ISODD(n) 跟 MOD(n, 2) = 1 是同一件事，我個人比較喜歡寫 ISODD，因為一看就知道在問奇偶。
練習
每箱 12 杯的箱數與剩餘
B5 是 53 杯，每箱裝 12 杯，想用兩條公式算出裝滿幾箱、剩幾杯，公式怎麼寫？
最大的差距
七個品項中，實際與目標差最多的是幾杯（不看方向），一條公式怎麼寫？
這些零件放進 IF 或條件式格式的公式裡才會真的好用，IF 可以先看 IF 與布林邏輯。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章數字捨入函式參考：ROUND、INT、TRUNC、CEILING、FLOOR 與負數的方向
ROUND、ROUNDUP、ROUNDDOWN、INT、TRUNC、CEILING、FLOOR、MROUND、CEILING.MATH、FLOOR.MATH、CEILING.PRECISE、FLOOR.PRECISE、ISO.CEILING、ODD、EVEN 共 15 個捨入函式的語法與例子，並整理負數時各函式往哪個方向走，以及用 ROUND 收掉浮點誤差。
IF 函式入門：TRUE / FALSE 與比較運算子
從 TRUE 和 FALSE 開始，認識 =、>、<、>=、<=、<> 六個比較運算子，再用 IF 讓儲存格依條件顯示不同的結果，最後預告巢狀 IF 的一層寫法。
平均數會說謊：用 MEDIAN、MODE 和百分位數看清資料真相
平均數被極端值拉高時，改用 MEDIAN 看中位數、MODE 看眾數，並用 PERCENTILE、QUARTILE 與 IQR 看資料的分布與異常值。附 7 人薪資與 10 筆成績的完整算式。
留言討論
共 0 則留言