Sheets - 資料反轉 (下)
在上一篇結尾，我們得到一個 INDIRECT() 公式，可以根據儲存格得到我們希望的資料陣列反轉後的值。但每次資料量變多，或是擺放的位置有變，參照的列跟欄隨之變動，我們都會需要修改公式再重新套用。一次兩次沒關係，但三次以上就會想說：我們能不能再進一步省去這樣的麻煩操作呢？拜 Google Sheets 的進步所賜，現在已經做得到了。這篇就來介紹如何透過新的陣列函式提升公式的便利性。
作法
1. MAP()、LAMBDA()
今年 Sheets 新增了一系列新函式跟新功能，使用體驗上的升級我認為是非常有感。這裡要用到的是剛推出不久的 MAP() 和 LAMBDA()。其實它們的作用說起來很簡單，就是把參照範圍裡每一個儲存格都送進設定好的公式裡運算，得到的值依相對位置輸出在新的陣列。依上圖所示，就是將 A1 格代入 LAMBDA() 裡的參數 cell，再依公式運算：假設 A1 值為整數 8， 公式為 cell * 2，那 A1' 的值就是 16，依此類推。我們的 INDIRECT() 公式代入 MAP() 時，記得將 ROW() 和 COLUMN() 裡的參照改成儲存格變數名稱 (自由取名) 即可，如下：
=MAP(A1:E7, LAMBDA(cell, INDIRECT("R"&8-ROW(cell)&"C"&6-COLUMN(cell),false)))
這樣一來，我們只需要輸入一次公式，即可產生反轉過的資料，不需要再一直拖拉來完成套用。看來非常美好，對吧？然而，它還是存在一些美中不足的地方。
2. MIN()、MAX()
你不難發現，INDIRECT() 取得列數和欄數的過程仰賴我們先算出的定值：以 A1:E7 作為來源範圍的話，定值就分別是 8 和 6。記得我們在 INDIRECT() 裡是採用 R1C1 表示的嗎，這裡的 A 和 E 分別就是第 1 欄和第 5 欄。顯然，8 和 6 就是從陣列長寬頂點的列數相加 (1 + 7) 以及欄數相加 (1 + 5) 所得來。
認識到這一點，再來的問題就只是：該如何求得陣列頂點的位址呢？我們知道如何取得儲存格的列和欄，也知道陣列的一端是數字較小的列 / 欄，另一端是數字較大的列 / 欄。我們還缺的，是可以判斷數字大小的方式，MIN() 和 MAX() 恰好能在此幫上忙。
由於它們兩者都接受範圍 / 陣列資料作輸入，我們便可從 MAP() 裡分別設定 ROW() 和 COLUMN()，取得列數組合和欄數組合，再分別代入 MIN() 和 MAX()，共得出四個公式。以下舉例其二：
=MIN(MAP(A1:E7, LAMBDA(c, ROW(c)))) 和 =MAX(MAP(A1:E7, LAMBDA(c, ROW(c))))
兩個公式分別得出 1 和 7，相加等於 8；此時我們已找到方法，把原本的手動輸入改成依據範圍自動計算。將以上列和欄的最小最大相加公式，替換掉第一階段 MAP() 公式裡的 8 和 6，結果會相當嚇人：
3. 已命名函式 (Named functions)
現在得到的公式已經十分強大，有很大的彈性，缺點就是不好維護，還得輸入相同的來源範圍參照五次，著實惱人。而就算是這樣的問題，也在目前的 Sheets 得到解決。我們可以進入選單 資料 > 已命名函式，將那一長串公式封裝成個人的函式，相同的來源參照也得以用變數代表，結果只要在一開始使用函式時輸入一次就好。這項功能使用步驟很容易，或者你也可以參考這篇別人寫的介紹。
成果就是，以後面對任何反轉資料的需求，我們只需要簡單一個函式便能輕鬆搞定。雖然不太清楚有什麼工作情況會經常需要反轉 (一開始還只是為了反轉座位表)，但在建構公式的過程中，我們掌握了多種函式的用法跟組合，想必之後面對各種難題都能信手捻來。是不是還不賴呢？
留言
張貼留言