2011年11月6日 星期日

Excel-陣列公式的基本用法

在 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)。

沒有留言:

張貼留言

好康東東