2013年11月2日 星期六

Excel-找出1000以內的質數(陣列公式)

兒子在算數學時常會用到那一個數字是質數、那個數字是合數?心想,如果能用 Excel 產生一個 1000 以內的質數表,必要時可以查表,該如何處理呢?(參考下圖)

【輸入公式】

通常要判斷一個數(X)是否為質數,簡單的判斷方式是將該數(X),逐一除以該數(X)平方根(SQR(X))以內的整數,如果有一個餘數為 0 (被除盡),則該數必定不是質數,反之必為質數。

儲存格A1:{=IF(ROW(1:1)=2,2,IF(ROW(1:1)=3,3,IF(PRODUCT(MOD(ROW(1:1),ROW($A$2:INDIRECT("A"&INT((ROW(1:1))^0.5)))))<>0,ROW(1:1),"")))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格A1,貼至儲存格A1:A1000。該公式會將質數 X 顯示在第 X 列,不是質數則不顯示。

IF(ROW(1:1)=2,2,IF(ROW(1:1)=3,3, XXXXXXX ):乃判斷如果在第 2 列或第 3 列時產生 2、3。

接下來解釋:IF(PRODUCT(MOD(ROW(1:1),ROW($A$2:INDIRECT("A"&INT((ROW(1:1))^0.5)))))<>0,ROW(1:1),"")

INT((ROW(1:1))^0.5):向下複製產生ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3、…,以求平方根的值。此公式相當於使用 SQR(ROW(1:1)) 的結果。

ROW($A$2:INDIRECT("A"&INT((ROW(1:1))^0.5))):利用 INDIRECT 函數將位址字串轉換成真的位址。例如:在第 19 列時 ROW($A$2:INDIRECT("A"&INT((ROW(19:19))^0.5))) 產生 ROW($A$2:$A$4)。

MOD(ROW(1:1),ROW($A$2:INDIRECT("A"&INT((ROW(1:1))^0.5)))):找出某數(X)逐一除以該數平方根以內的整數之餘數陣列。

使用 PRODUCT 函數將所有的餘數陣列相乘,如果陣列中有一個餘數為 0,其乘積必為 0,表示該數必不是質數。

接著,將 A 欄中的所有質數轉換至表格中(表格為 24 列 X 7 欄 )。

儲存格B1:=IFERROR(SMALL($A$1:$A$1000,(COLUMN(A:A)-1)*24+ROW(1:1)),"")

複製儲存格B1,貼至儲存格B1:H24。

COLUMN(A:A)-1)*24+ROW(1:1):在 24 列 X 7 欄中產生 1, 2, …, 24, 25, …, 48, … 的數。

透過 SMALL 函數依序由小至大取出儲存格A1:A1000中的數(質數已列出),再由上式顯示在某個儲存格中。

使用 IFERROR 函數將公式中出錯的部分顯示為空白。

沒有留言:

張貼留言

好康東東