學不完．教不停．用不盡

在 Excel 中有機會使用陣列公式，但是對些人而言，陣列公式似乎不容易理解。會寫程式的人應該對「陣列」不會陌生，陣列是一塊記憶體，藉由存取陣列，即可使用記憶體中的內容。參考以下的範例(參考下圖)：

【常數陣列】

(1) 儲存格C2：=SUM({1,2,3,4,5,6,7})

{1,2,3,4,5,6,7}：以「{ }」含括的為陣列，以「,」隔開元素。{1,2,3,4,5,6,7}並沒有放在儲存格中，而是直接由記憶體取出來運算。

 

(2) 儲存格C3：=SUM(A1:A7)

如果數值已經置於儲存格(例：儲存格A1:A7)，即可直接取出運算。(2)和(1)的運算結果一樣。

 

【常數陣列運算】

(3) 儲存格C4：{=SUM({1,2,3,4,5,6,7}+3)}

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

可以對陣列的每個元素加以運算，公式結果為：SUM({4,5,6,7,8,9,10})。

本例其實只要輸入「=SUM({1,2,3,4,5,6,7}+3)」即可，不需再按 Ctrl+Shift+Enter 鍵。

 

(4) 儲存格C5：=SUM({1,2,3,4,5,6,7}*2)

原理同(3)，公式結果為：SUM({2,4,6,8,10,12,14})

 

【多重運算與陣列公式】

以下四個運算的結果是相同的，都是要計算儲存格A1:A7中大於 4 的數值和(5+6+7=18)：

(5) 儲存格C6：=SUMIF(A1:A7,">4",A1:A7)

SUMIF 函數以多重運算來取出儲存格範圍中的內容計算。相當於：

{Fasle,Fasle,Fasle,False,True,True,True}*{1,2,3,4,5,6,7}

= {0,0,0,0,5,6,7}  (運算時 Fasle=0，True=1)

SUM({0,0,0,0,5,6,7}) = 18

 

(6) 儲存格C7：=SUMPRODUCT((A1:A7>4)*A1:A7)

SUMPRODUCT 函數以多重運算來取出儲存格範圍中的內容計算。原理同(5)。

 

(7) 儲存格C8：{=SUM((A1:A7>4)*A1:A7)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。原理同(5)。

 

(8) 儲存格C9：{=SUM(IF(A1:A7>4,A1:A7,))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。原理同(5)。