本篇要來介紹一個常見而簡單又好用的函數：MOD。
MOD 函數主要是傳回兩數相除後的餘數（餘數和除數具有相同的正負號）。
MOD 函數如果使用 INT 函數來表示：MOD(n, d) = n-d*INT(n/d)。
【例】利用 MOD 函數來判斷一個是奇數或是偶數
儲存格B3：=IF(MOD(A3,2)=0,"偶數","奇數")
利用 MOD 函數來將 N 除以 2，而偶數除以 2 的餘數應為 0。
【例】利用 MOD 函數讓間隔列顯示不同色彩
如果你想要新增列或刪除列時，都能保持偶數列為淺橙色，則必須要使用設定格式化的條件來處理。假設原資料所有儲存格都是淺綠色。
1. 選取儲存格A3:F12。
2. 設定格式化的條件
(1) 規則類型：使用公式來決定要格式化哪些儲存格
(2) 編輯規則：=MOD(ROW(A3),2))=0
(3) 設定格式：儲存格色彩為淺橙色
其中 ROW 函數會傳回儲存格的列號。
【例】利用 MOD 函數來依訂購數量來計算分裝數量
(1)計算箱數
儲存格B3：=INT(A3/12/12)
(2) 計算打數
儲存格C3：=INT((A3-B3*12*12)/12)
(3) 計算罐數
儲存格D3：=MOD(A3,12)
【例】利用 MOD 函數來計算指定間隔項次的數值加總
本例要計算間隔 4 項(1,5,9,…)的數值總和。
選取儲存格B1:B25，按 Ctrl+Shift+F3 鍵，勾選頂端列，定義名稱：數值。
儲存格D5：=SUMPRODUCT((MOD(ROW(數值)-1,4)=1)*數值)
(1) 公式中的「4」指間隔項次為「4」。
(2) MOD(ROW(數值)-1,4)
其中 ROW 函數會傳回儲存格的列號。
所以本式會產生1,2,3,0,1,2,3,0,.1,...數列，每一個 1 分別對應至1,5,9,13,17,21項。
(3) MOD(ROW(數值)-1,4)=1
判斷第(2)式的傳回值是否為 1，會傳回 TRUE／FALSE 陣列。
(4) (MOD(ROW(數值)-1,4)=1)*數值
運算子「*」相當於執行邏輯 AND 運算，會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
(5) 將第(4)式的傳回值在 SUMPRODUCT 函數中加總。
【例】利用 MOD 函數來做為資料表格式轉換工具
1. 製作轉換位置表
儲存格I6：=(ROW(A1)-1)*5+MOD(COLUMN(A1)-1,5)+1
複製儲存格I6，貼至儲存格I6:M9。
2. 產生資料表轉換
儲存格A6：=INDEX($A$2:$R$2,第(1)式)
儲存格A6：=INDEX($A$2:$R$2,(ROW(A1)-1)*5+MOD(COLUMN(A1)-1,5)+1)
複製儲存格A6，貼至儲存格A6:E9。(已刪除儲存D9,E9)
將第(1)式的傳回值置入 INDEX 函數，即可將對應的儲存格內容轉換為新的格式。
【例】利用 MOD 函數來判斷輸入的 N 是否為質數
儲存格C3：=IF(SUMPRODUCT(1*(MOD(B3,ROW(INDIRECT("2:"&(B3-1))))=0))=0,"是質數","非質數")
(1) ROW(INDIRECT("2:"&(B3-1)))
在 SUMPRODUCT 函函數中藉由 INDIRECT 函數將字串「"2:"&(B3-1)」轉換為位址
再置入 ROW 函數中產生2~N-1的數值陣列。
(2) MOD(B3,ROW(INDIRECT("2:"&(B3-1))))
利用 MOD 函數來產生 N 除以 2~N-1 的數值餘數陣列，餘數有 1 個(含)以上者，表示至少有一個因數（即不是質數）。
(3) 1*MOD(B3,ROW(INDIRECT("2:"&(B3-1))))
「*1」用意在於轉換 TRUE/FALSE 陣列為 1/0 陣列
(4) 在 SUMPRODCUT 函數中加總 1/0 陣列
(5) 若是 SUMPRODCUT 函數加總結果為 0 者表示沒有任何因數，即為質數。
留言列表