從數(ㄕㄨˇ)數(ㄕㄨˋ)的概念學習陣列公式～
這是一位學員的問題：
有多個品項的產品，例如品項A、品項B、品項C、...等等，從公司的系統中，匯出了年度各月份各品項的進貨數量，例如下圖所示：儲存格範圍A3:M8。其中，有些儲存格是空的，沒有任何數據資料，有些儲存格是「0」。
我們想要顯示各品項第一次發生進貨(大於0)的進貨數值是多少？以及是發生在哪一個月份？例如：在上述的實例中，
- 品項A第一次發生進貨(大於0)的數量是「28」，是發生在「21704」。
- 品項B第一次發生進貨(大於0)的數量是「30」，是發生在「21702」。
- 品項C第一次發生進貨(大於0)的數量是「98」，是發生在「21701」。
- 品項D第一次發生進貨(大於0)的數量是「99」，是發生在「21705」。
- 品項E第一次發生進貨(大於0)的數量是「8」，是發生在「21702」。
在觀念上，只要能夠找出品項裡第一次發生的進貨數值，那麼第一次發生的進貨月份就不難取得，至於第一次發生的進貨數值，要使用哪一個函數或是哪些個函數的組合呢？
由於每個品項在年度各份月進貨數量裡，皆有12個儲存格內容，只是有些是空格、有些是0、有些是大於0的數值，因此，我們以品項A來舉例說明，若以陣列的角度來看，只要了解品項A的12個年度各月份進貨數量裡，非0也非空格的第一個大於0的數值是誰，不就得了嗎？譬如：B4:M4裡第一個大於0的數值是「28」，而它正位於陣列裡的第「4」個元素。
而各年度月份（儲存格B3:M3）的第4個元素，不就正是「28」所在的月份嗎？因此，這個「4」也正可以做為INDEX函數對(B3:M3)的索引值，而順利取得「201704」。
若以品項B且仍是以陣列的角度來看，品項B的12個年度各月份進貨數量裡，非0也非空格的第一個大於0的數值是誰？正是位於陣列裡的第2個元素「30」啊！
所以，知道陣列裡的第一個大於0的數值是位於陣列裡的第幾個元素將是這個查詢的關鍵，非常重要！在根據這個關鍵數字，便可索引出進貨數量的值，以及該年度月份囉！
我們可以使用IF函數來逐一判斷陣列裡的n個元素內容，是否為0 (空格也是0)，若是，則傳回空字串，若不是，則傳回當下的n值。例如：以品項A的資料而言，可以使用IF函數來判斷陣列裡的第1個值，也就是B4儲存格的內容，是否為0 (空格也是0)，若是，則傳回空字串，若不是，則傳回當下的n值，即數字1。
繼續以IF函數來判斷陣列裡的第2個值，也就是C4儲存格的內容，是否為0 (空格也是0)，若是，則傳回空字串，若不是，則傳回當下的n值，即數字2。
依此類推…使用IF函數來判斷陣列裡的第12個值，也就是M4儲存格的內容，是否為0 (空格也是0)，若是，則傳回空字串，若不是，則傳回當下的n值，即數字12。
如此，經過12次的IF函數來判斷陣列裡的每一個值，所傳回的值，若不是空字串，便是該值在陣列裡的第幾個元素之數字。而這個從1數到12的n值，便可以事先定義在一個數字陣列裡，這是屬於固定不變的常數陣列。所以，以陣列公式的角度來看，就可以寫成：
要注意的是，B4:M4總共12個儲存格，對應到數字陣列｛1,2,…,11,12｝裡也必須是12個數字元素喔！然後，我們再注意一下公式的絕對位址觀念，就可以寫成：
因此，針對此例的品項A，所傳回的陣列結果便是：
那麼這個陣列便存放著空字串以及每一個進貨數量大於0的元素其如同座標般的所在位置，若取最小值，那不就是第一個進貨數量大於0的元素其所在位置嗎？再根據這個位置，透過INDEX函數，參照到儲存格範圍B4:M4，便可以索引出品項A第一個進貨數量大於0的值；再繼續透過INDEX函數，參照到儲存格範圍B3:M3(事先命名為「年度月份」)，便可以索引出品項A第一個進貨數量大於0的年度月份。
我們來整理一下，修飾一下這個陣列公式，首先，若要傳達常數陣列：
{1,2,3,4,5,6,7,8,9,10,11,12}
這個包含12個元素且值為1～12正整數值的陣列，可以寫成：
=COLUMN($A:$L)
因為A欄到L欄這12個欄名的編號，透過COLUMN函數，以陣列的角度來看，即可傳回1～12的正整數值。所以，上述的函數分別可以寫成：
不過，都是以陣列的形式來執行喔！因此，記得輸入完畢後，必須按下Ctrl+Shift+Enter按鍵喔！
額外的小秘訣：
若想要知道各品項第2次發生進貨(大於0)的進貨數值是多少？以及是發生在哪一個月份？那麼，只要將原本想取得陣列裡最小值的MIN函數，改成SMALL函數就好啦！SMALL函數的語法是：
=SMALL(array,k)
可以取得陣列(array)裡第k小的值，因此，
=SMALL(array,1)
傳回陣列裡的最小值，也就是雷同MIN(array)結果。
=SMALL(array,2)
便可以傳回陣列裡的第2小值。
有興趣，去試試看吧！
註：此Excel陣列函數系列文章內文實作活頁簿檔案載點。