【Excel】陣列公式使用筆記，參照列資料獲取行頭標題(MATCH, INDEX, OFFSET)
作為工程師其實一般文件處理，常常都想用程式解決，像如果資料來源是 Excel，就會想寫個小程式直接拉 csv 格式資料出來轉成 JSON 解決，畢竟一般文件處理的邏輯換到程式裡大多一兩行就能搞定，輸出也更靈活。
但總歸還是有會需要用 Excel 的場合，前些日子幫忙看處理資料的問題剛好查了幾個函式，因為查的時候的說明都有點不易懂，懂了後覺得「原來就這麼回事」而已，所以寫一篇給其他有需要的人和未來的自己查看。
(文章內都有附上 MS 官方的解釋文章，細節部分可以點進去看，也能比較我的文字與該文章是否相較更好懂)
先講講這篇當初解決的目標問題。
假設 Row Data 如下，每列為每個人的出席表(假設 1 為有出席，空白為缺席)，想要用一個公式找到那個人在本月最早的出席天數是哪天(如 Beck 為 2-Nov)。
我們先拆解一下問題步驟，如何解決這個問題？我們拿 Beck 來看：
- 先找單列中 Beck 往右看去整列格子，找出其中為不為空格的格子，分別為 C3 和 E3
- 假設日期為照順序排好，則要找最左的格子，也就是 C3
- C3 往該行的行頭對去，得到答案是 2-Nov
- 在 G3 的位置寫出公式，把 2-Nov 填上
如果是寫程式，應該會很直覺的用迴圈處理每一列，一旦找到任一日期有出席，就記錄該行的 index 下來，並往下一列走，最後再逐一用 index 對應記錄日期的陣列填上答案，其實 Excel 的邏輯也是這樣，只是不常用的人會不知道怎麼樣在 Excel 寫出這樣的描述句。
陣列公式
先談談什麼是陣列公式，以上面這個例子，判斷是否出席很簡單，就是逐一判斷每格是否為空格(或為1，我這邊用空格舉例)。一樣以 Beck 舉例，不等於得運算元為 <>，""表示空格(雙引號用於括號表示內容文字串，兩個雙引號中間沒有內容，所以為空字串，可以理解為沒有內容的格子)，B3 <> ""，這是一個邏輯運算式，會明確回傳 TRUE 或 FALSE，若 B3 不為空格，則回傳 TRUE，B3 為空格則回傳 FALSE。
B3 — F3 為 Beck 的出席資料，所以我們要知道 Beck 哪些天有出席，我們必須要逐一比較 B3 — F3 是否為空格，在 Excel 中，冒號 : 是用於表示{從}:{到}的概念，所以我們可以寫成 B3 : F3 <>""。可想而知，這個比較式正常必須分五格來寫，因為總共會回傳五個結果，但如果用上面這個寫法把它濃縮在一格，會發生甚麼事？
如果是舊一點版本的 Excel ，這邊直接按 Enter 應該會跳錯誤，如果是 M365 版本以後的，它會自動幫你轉成陣列公式：他會提示你，因為你的式子回傳了多個結果，所以他自動幫你轉換了。而舊版的你要自己意識到這是陣列公式，不是按 Enter，而是改按 Ctrl + Shift + Enter，他會幫你變成 {=B3:F3<>""}。這個大括號象徵這是陣列公式，這個大括號也不能手打，他會跳錯誤，只能在編輯完成內容後按下 Ctrl + Shift + Enter 由系統幫你加上。
透過上面的圖我們可以知道，陣列公式就是處理當你的單一判斷式有多個結果時，他會用他的方式幫你儲存結果，且公式只會佔一格，顯示則還是依回傳結果而定，像上面回傳五個結果，就顯示五格，但表示式能夠被寫在一格中。
好，那麼我們現在有一格能夠記錄所有結果判斷了，再來我們該知道該列第一格出現的是誰。
MATCH
寫程式的人可能這時候有了上面這個像陣列的結果，就會想要拿 IndexOf，IndexAt 之類的函式來用了，因為這類函式的 Default 就是在一個陣列中找某一個元素第一次出現的位置，在 Excel 中，我們用 Match 來做。
MATCH(lookup_value, lookup_array, [match_type])第一個是尋找的值，第二個是去那裡尋找(範圍，陣列)，第三個是符合的規則，最後會回傳去尋找範圍內符合的位置(index in the lookup_array)。
假設尋找的範圍的內容是這樣 a,b,c，而要尋找 b，則最後會回傳的是 2(1-index)，因為是範圍中的第二個元素。
第三個符合的規則的可能值有 1, 0, -1，預設行為為 1：
- 1(Default，只寫兩個參數預設用這個)：往右尋找小於或等於 lookup_value 的最大值(盡可能接近 lookup_value)，限制 lookup_array 要以遞增排列
- 0 ：往右尋找完全等於 lookup_value 的值，不限制 lookup_array 的排列順序
- -1：往右尋找大於或等於 lookup_value 的值，限制 lookup_array 要以遞減排列
像這次命題是要找資料內容為 FALSE 或 TRUE 中第一個 TRUE 出現，則可以使用 0 。
那這下我們知道了，以 Beck 而言，第一次 TRUE 出現的位置是第二行(相對於簽到資料起始)。再來我們需要知道第二行對應到哪個日期。
OFFSET
有寫程式概念的人應該對變數這個概念不陌生，沒經驗的人也可以想成國中程度的代數，像 x，你需要有一個代稱 / 地方來儲存你想要的資料，OFFSET 就是拿來做類似這種事情的。
OFFSET(reference, rows, cols, [height], [weight])這式 OFFSET 的定義，以 reference 為某格，接著偏移 rows, cols 的程度，然後拉出 height 高度、weight寬度的儲存格，所以當我們想要拉出基於某一格的範圍，我們就可以用這個函式來做範圍拉取。
像這樣就能拉出基於 A1，往右偏移一格，拉高度 1，寬度 5 的資料內容，正好就是日期。當然，如果沒有需要特別參照的話，也可以直接寫成 = B1:F1，會得到一樣的結果。
這邊的目的是我們要拿出日期的範圍，剛剛上面知道了，我們要拿日期範圍中的第二個。
INDEX
有陣列，有索引，那當然就是去找陣列中對應索引就得到答案了，沒錯，Excel 也是同樣概念，找一個範圍內第幾個值：交給 Index 這個函數吧。
INDEX(array, row_num, [column_num])Index 的參數，第一個是範圍，第二個是列的第幾個，第三個是行的第幾個，第二個和第三個依據第一個的資料範圍決定是否必要，至少必須有一個。
array 就是資料範圍，如果你只有一列，則你不需要列數，只要寫你需要該列中的第幾行就好，反之亦然，只有一行，則不需要寫行數，寫該行第幾列就好 ─ 當然，如果你的資料有數行數列，則就要寫明你要行列中的第幾行第幾列了。
我們要的就是這些日期中的第二個：
寫出範圍、寫出第幾個，得到答案。
最後我們就把這些式子濃縮成一個：
=INDEX(OFFSET(A1,0,1,1,5),0,MATCH(TRUE,B3:F3<>"",0))
式子的意義再從頭說一次，我們從最裡面的括號開始解讀：找到 B3 — F3 範圍中第一個不等於空格的相對行數，套到日期列中該行，得出該行的日期。
大功告成？
還沒，差還一點，這邊我們會需要用拉右下角的點來讓這個公式套用到每一列中，才不會每列都寫一次，拉起來！
眉頭一皺，好像哪裡不太對？
仔細一看，我們並不希望偏移的資料產生了偏移，導致最後結果不正確！
相對參考與絕對參考
上面的式子中，我們不希望變動的是 日期列要維持內容，也就是 A1 我們希望不管怎麼下拉依舊是 A1，MATCH 那邊因為是直向下拉，只變動了列數，是我們期望的結果。
這時候就要用的 $ 錢字號了，錢字號代表將該列或行的參考作為絕對參考，不管你怎麼拉動資料做邏輯類推，有錢字號的都不會變，以 A1 來說：
- A1 ：行列都是相對參考，往對應方向拉動都會變動
- $A1 ：行絕對列相對，往下拉會變動列的參考
- A$1 ：列絕對行相對，往右拉會變動行的參考
- $A$1 ：行列都是絕對參考，不管怎麼拉動，該範圍會永遠維持參考 A1
以這個例子，不管我們怎麼拉，我們都希望 A1 就是 A1，所以我們應該寫 $A$1。
登登，BINGO，在我們做好確認的絕對參考後，再拉動去讓公式類推產生，所有公式都回傳了正確的值，至此我們的資料告一個段落。
最後我們得到處理這個問題的公式即是：
=INDEX(OFFSET($A$1,0,1,1,5),0,MATCH(TRUE,B3:F3<>"",0))也可以不用 OFFSET，如上面講的，直接拉範圍，則寫成(注意，日期範圍是絕對參考，因為不管哪列，都是參照行頭的日期)：
=INDEX($B$1:$F$1,0,MATCH(TRUE,B3:F3<>"",0))(此公式為處理第三列，所以範圍是拉 B3:F3，其他列類推)
這篇文章到此結束，看起來很複雜的公式，拆解後一一理解，其實會發現並沒有那麼晦澀，這些公式在處理有表格座標參照相關問題時都會挺有幫助的，有程式觀念的人可以很輕鬆地透過這篇文章，從此後用陣列的概念來理解這些公式，沒程式觀念的人，我想這篇也有表達到足夠的細節內容，希望看完這篇能讓這些公式不再困擾你，好好變成你的助力。
如果覺得這篇文章不錯，歡迎給個拍手，也歡迎在留言給任何建議或指教。
