Excel陣列函數入門
例如:A1:A12這12個儲存格裡,每一個儲存格內都有一串文字內容,若想要計算出這12個儲存格裡總字元數,公式要怎麼下呢?
如果想知道一個儲存格裡的字元數,那麼=LEN()函數是最適合不過了!例如函數=LEN(A1)可以傳回的結果便是「4」;再譬如函數=LEN(A6)可以傳回的結果便是「27」。如今,若想要知道A1:A12這個範圍裡的總字元數,則只要透過如下圖所示的B欄,將每一個儲存格皆以LEN函數計算出各個結果,再透過SUM函數(例如:儲存格B13),進行上述12個LEN函數結果的加總,也就是輸入函數=SUM(B1:B12)不就好了!的確如此!
但是,仔細想想,先決條件,是不是要先運用B1:B12這個輔助範圍事先以LEN函數計算出結果才可行呢!
如果不需要使用B1:B12事先計算出A欄裡各個儲存格的字元長度,而是直接輸入函數=LEN(A1:A12)不行嗎?想法上是值得鼓勵的,但真的不行!
因為,這涉獵到了陣列(Array)公式的應用。我們就先來認認識一下陣列的概念吧!
目前在維基百科,針對陣列(Array)是這樣解釋的:
在電腦科學中,陣列資料結構(英語:array data structure),簡稱陣列(英語:Array),是由相同類型的元素(element)的集合所組成的資料結構,分配一塊連續的記憶體來儲存。利用元素的索引(index)可以計算出該元素對應的儲存位址。
在程式設計的領域裡,陣列是很重要的技術,撰寫程式的過程中,經常會運用到陣列的宣告,來規劃所需的陣列大小以分配適當的記憶體來儲存資料,進行整批資料的快速運算與處理。而相對應到Excel天生行列結構的試算表中,連續儲存格可以存放資料,因此,就可以視為是一個陣列。所以,雖然我們總是稱呼A1:A12是一個範圍,但也正是一個陣列的概念,是一個垂直方向的12個連續儲存格,算是一個可儲存12個元素的一維陣列。如果是A1:H1,也算是一個陣列的概念,是一個水平方向的8個連續儲存格,算是一個可儲存8個元素的一維陣列。那麼,A1:H12也是一個陣列的概念囉!只是這是一個二維陣列(垂直12列、水平8欄),算是一個可儲存96個元素的陣列。
小學的時候,學過集合的概念,用一對大括弧來描述同一個集合裡的各個元素,例如:
水果集合{鳳梨,香蕉,蘋果,芭樂,柳橙,西瓜}
而在Excel中,也是以大括號的符號,來表示陣列的!
以上述範例為例,先前在儲存格B13裡所輸入的LEN(A1:A12)就是要執行12次的LEN函數,分別計算出A1、A2、A3、….A11、A12等12個連續儲存格的儲存格字元長度,因此,LEN(A1:A12)函數若視為一個陣列集合的運算式來看,其結果應該會是{4,5,8,10,8,27,6,12,4,16,6,8}。在Excel的規則上,您在儲存格裡所輸入的算式若是要以陣列的形式來運算,則在輸入完公式後,必須按下Ctrl+Shift+Enter按鍵做為結束(不能只按下Enter按鍵喔)。因此,依據上例所述,在儲存格B13裡輸入=LEN(A1:A12)函數並按下Ctrl+Shift+Enter按鍵,則在儲存格B13理應是傳回{4,5,8,10,8,27,6,12,4,16,6,8}這個陣列,但基於一個儲存格只能顯示一個結果,而{4,5,8,10,8,27,6,12,4,16,6,8}這個陣列是包含了12元素(12個LEN的結果),因此,最終在儲存格13中只能顯示陣列裡的第一個元素。不過,若在此陣列的外圍,再加上SUM函數,也就是SUM({4,5,8,10,8,27,6,12,4,16,6,8})概念,這不就正是我們不想占用B欄作為輔助欄的理想算式了嗎?也的確如此,因此,以這個角度來看我們可以將=LEN(A1:A12)函數的輸入,再修改成=SUM(LEN(A1:A12)),當然,也要按下Ctrl+Shift+Enter按鍵,讓Excel知道這是一個以陣列形式運算的公式,就可以在儲存格B13中顯示出加總12次LEN函數結果的值。
我們運用了SUM與LEN函數,告知Excel以陣列的形式(按下Ctrl+Shift+Enter按鍵)來運算,這就是陣列公式的道理與魅力囉!
要不要再歷練一下另一個簡單的範例,在下圖的儲存格E2與E3裡,應該輸入什麼陣列公式呢?
註:此Excel陣列函數系列文章內文實作活頁簿檔案載點。