最近火熱話題:西元2020年3月20日若以20200320表示,該數剛好可被1到10的每一個數字整除,被稱為「超級整除日」。

如何利用 Excel 計算某一個數能被 1~10 整除呢?

Excel-判斷數值是否可被1~10同時整除(找尋超級整除日)(MOD,ROW)

 

【公式設計與解析】

這次要以陣列公式同時計算某一個數除 1, 2, 3, … ,10 的餘數總和,若是餘數和為 0 者,即表示能被 1~10 同時整除。

(1) 轉換日期表示

日期:2020/3/15,如何表示為:20200315?

儲存格B2:=TEXT(A2,"yyyymmdd")

利用 TEXT 函數來設定儲存格A2內容的數值格式。

 

(2) 計算除以1-10的餘數

儲存格C2:{=SUM(MOD(B2,ROW($1:$10)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」

複製儲存格C2,貼至儲存格C2:C12。

ROW 函數會傳回儲存格的列號。例如:ROW(A1)=1、ROW(B4)=4、ROW(C7)=7。

在陣列公式中,ROW($1:$10)即表示 1, 2, 3, … ,10。

MOD 函數會傳回兩數相除的餘數。例如:MOD(7, 2)=1、MOD(15,4)=3。

最後再將全部的餘數以 SUM 函數予以加總。

(3) 檢驗全部整除

儲存格D2:{=IF(SUM(MOD(B2,ROW($1:$10))),"","V")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」

複製儲存格D2,貼至儲存格D2:D12。

IF 的判斷條件中,傳回值不等於 0 者代表 TRUE;傳回值等於 0 者代表FALSE

所以當判斷式為 0 者,顯示為「V」;否則顯示「空白」。

Excel-判斷數值是否可被1~10同時整除(找尋超級整除日)(MOD,ROW)

 

利用相同公式找出下一個「超級整除日」為:2025年7月20日。

Excel-判斷數值是否可被1~10同時整除(找尋超級整除日)(MOD,ROW)

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

    文章標籤

    Excel MOD ROW

    全站熱搜

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