學不完．教不停．用不盡

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

(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加總。