Sheets - 資料反轉 (上)
Java 班剛開訓時，班上需要做一份座位表給老師看。大家各自選定座位後，在一份模擬教室格局的 Sheets 表格裡，於適當位置填進名字，範例如下：
綠色是前門和後門；藍色是老師的位置
好了，我們現在有這張表，但是我還有個問題：如果我想透過它認識班上同學，表得反過來看，但我腦袋常常轉不過來，想按照自己的視角，那該怎麼辦？
以這張資料量很小的表而言，我從結尾 Front 複製到新的起始位置，如此反覆動作也不會花上太多時間。但要是今天遇到的資料量很大，就不適合這麼做了。那 Google 試算表有內建方便的反轉功能嗎？就我所知，應該沒有。內建的排序不符合直接倒轉的需求，因為它是根據規則來排；函式 TRANSPOSE() 也不適合，它是以整列、整欄反轉，並不是反轉一格一格儲存格的順序。
一如往常，我又動手寫公式來解決這問題。
作法
我們知道在試算表裡每一格儲存格都有它的參照位址，即所處列 (Row) 和欄 (Column)。如果想在某一格顯示另一個儲存格的值，我們用這樣的方式： =A1 ，或 =B2:F8 。但若要反轉順序，我們勢必要設一些條件操縱它們的位址，而在 Sheets 裡，這個可以幫我操縱位址再進而得到值的函式就是 INDIRECT()。例如 =INDIRECT("A1", true) 或
=INDIRECT("R2C2:R8C6", false) ，回傳結果分別對應到一開始的基本用法。第一個參數是參照字串；第二個參數 true/false 代表要使用哪種參照表示法，false 即是採用 R1C1 表示法：列欄都使用數字排序，以英文字 R 跟 C 區分兩者。我的經驗裡，多半是用 R1C1 比較好發揮。
1. ROW()、COLUMN()
現在我們有一個方向了，接下來的問題就是，該如何操縱、變化參照字串，進而得到我們希望的結果？INDIRECT() 使用 R1C1 參照必須經由數字，而 Sheets 裡有一組函式：ROW() 和 COLUMN()，正好就是輸入一個儲存格，得到該格的列數或欄數為輸出。例如 =ROW(B2) 回傳 2， =COLUMN(F8) 回傳 6。透過它們，我們可以觀察資料裡所有儲存格的位址：
公式： ="("&ROW(A1)&", "&COLUMN(A1)&")" ，套用 A1 到 E7
而既然我們的目標是得到反轉的資料，那麼可以想像最後資料對應到原始範圍的位址，是從尾到頭，一個一個倒過來的。以下我先貼上結果，就可以一目了然了：
我們把焦點放在右手邊反轉過的位址和上面的原始位址進行比較，相信你一定看得出它們之間的關係：每一對相同位置的新舊列欄位址相加，都等於 8 和 6。1 + 7 = 8、2 + 6 = 8、3 + 5 =8 ... 然後 1 + 5 = 6、2 + 4 = 6，依此類推。以左上角起第一個位置為例，再進一步推導，將 1 移到等式另一邊，列數的新式子為 7 = 8 - 1，欄數的新式子則為 5 = 6 - 1。換句話說，我們分別以 8 - 1 和 6 - 5 即可得到反轉後第一個位置該擺的資料的來源位址，正是 (7, 5) ！
2. INDIRECT()
了解這個規律後就很容易，運用 INDIRECT()，在組合 R1C1 參照字串時列的數字為 8 - ROW()，欄的數字為 6 - COLUMN()。完整公式： INDIRECT("R"&8-ROW(A1)&"C"&6-COLUMN(A1), false) 。輸入第一格的公式後，往右且往下拉套用到空欄位直到完整資料，完成後即成功反轉。
有了這樣的公式，理應已經十分方便。不過，我還想把它再改得更自動、更強大 (更炫砲？)，就留到下集來說明了。
留言
張貼留言