Sheets - 資料反轉 (下)
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) 所得來。 認識到這一點,再來的問題就只是:該如何求得陣列頂點的位址呢?我們知道如何取得儲存格的列和欄,也知道陣列的一端是數