2011年5月23日 星期一

Excel-陣列公式練習

在 Excel 中根據一個編號和金額構成的資料表(參考下圖),試著來練習使用陣列公式。為了方便說明:將A欄中的資料命名為「編號」,將B欄中的資料命名為「金額」。

image

(1) 計算金額於2001~3000的個數

以下三種運算方式的結果一樣:

[例] 儲存格D3:=COUNTIF(金額,">2000")-COUNTIF(金額,">3000")

這是沒有使用陣列的公式。

[例] 儲存格D2:=SUM(COUNTIF(金額,">" & {2000,3000})*{1,-1})

公式意義相當於:

=SUM({COUNTIF(金額,">2000”),COUNTIF(金額,">3000”)}*{1,-1})

=SUM(COUNTIF(金額,">2000”),-COUNTIF(金額,">3000”))

將大於2000的個數減掉大於3000的個數,即為2001~3000的個數。

[例] 儲存格D4:=SUMPRODUCT((金額>2000)*(金額<=3000))

公式意義相當於:

=SUMPRODUCT((金額>2000的True/Fasle陣列)*(金額<=3000的True/False陣列))

公中的「*」,可以藉由運算,將True/Fasle轉換為1/0,其結果相當於執行邏輯AND的運算。

(2) 編號開頭各個字母的個數

[例] 儲存格D7:{=SUM(IF(LEFT(編號,LEN(D7))=D7,1,0))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格D7至儲存格D7:D10。

LEN(D7):求出儲存格D7中字串的長度。

LEFT(編號,LEN(D7)):取出編號最左端和儲存格D7一樣的字串。

IF(LEFT(編號,LEN(D7))=D7,1,0):如果編號最左端和儲存格D7一樣的字串一樣時就會回1,反之傳回0。

透過陣列公式可得一個1/0的陣列,再藉由於SUM函數將1/0加總。

沒有留言:

張貼留言

好康東東