Excel-MOD函數的認識與應用

本篇要來介紹一個常見而簡單又好用的函數:MOD

MOD 函數主要是傳回兩數相除後的餘數(餘數和除數具有相同的正負號)。

Excel-MOD函數的認識與應用

MOD 函數如果使用 INT 函數來表示:MOD(n, d) = n-d*INT(n/d)。

 

【例】利用 MOD 函數來判斷一個是奇數或是偶數

儲存格B3:=IF(MOD(A3,2)=0,"偶數","奇數")

利用 MOD 函數來將 N 除以 2,而偶數除以 2 的餘數應為 0。

Excel-MOD函數的認識與應用

 

【例】利用 MOD 函數讓間隔列顯示不同色彩

如果你想要新增列或刪除列時,都能保持偶數列為淺橙色,則必須要使用設定格式化的條件來處理。假設原資料所有儲存格都是淺綠色。

1. 選取儲存格A3:F12。

2. 設定格式化的條件

(1) 規則類型:使用公式來決定要格式化哪些儲存格

(2) 編輯規則:=MOD(ROW(A3),2))=0

(3) 設定格式:儲存格色彩為淺橙色

其中 ROW 函數會傳回儲存格的列號。

Excel-MOD函數的認識與應用

 

【例】利用 MOD 函數來依訂購數量來計算分裝數量

(1)計算箱數

儲存格B3:=INT(A3/12/12)

(2) 計算打數

儲存格C3:=INT((A3-B3*12*12)/12)

(3) 計算罐數

儲存格D3:=MOD(A3,12)

Excel-MOD函數的認識與應用

 

【例】利用 MOD 函數來計算指定間隔項次的數值加總

本例要計算間隔 4 項(1,5,9,…)的數值總和。

選取儲存格B1:B25,按 Ctrl+Shift+F3 鍵,勾選頂端列,定義名稱:數值。

Excel-MOD函數的認識與應用

儲存格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 函數來做為資料表格式轉換工具

Excel-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 是否為質數

Excel-MOD函數的認識與應用

儲存格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 者表示沒有任何因數,即為質數。

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel MOD
    全站熱搜

    vincent 發表在 痞客邦 留言(4) 人氣()