[數據分析#11] Google Sheet FLATTEN 教學 – Unpivot 反向樞紐

總結一下 Google Sheet 很強的新函數 FLATTEN() 的應用場景，幾個應用場景在我們日常處理表單時，應該都很容易遇到：

• 你拿到了一份樞紐分析過的表格，想要把它反向樞紐 (unpivot) 成資料分析很好處理的資料型態
• 你拿到一份 email 名單，但是散落在很多不同欄、或是散落在同一欄裡面但是不同格子，想要把他整理在同一個欄位，並且按照順序排好、也不要有多餘的空格

Google Sheet Unpivot 反向樞紐

Flatten() 函數功用

Flatten() 函數我覺得主要有兩個功用：

1. 把在不同欄位、不同儲存格裡面的值變成在同一欄
2. 如果你有 A, B, C & 1, 2，你想要把他排列組合成 A1, A2, B1, B2, C1, C2

這邊可以 點擊 Google Sheet 範例檔案參考！

Unpivot = Flatten()+Split()+Arrayformula()

在 Google Sheet 內使用 Unpivot 會需要用到三個函數，flatten、split、arrayformula，總共會需要以下步驟來完成：

1. 首先，先用 Flatten() 取出
   • 樞紐分析的縱軸
   • 然後橫軸
   • 最後把數值區域框起來
2. 外面再用 split() & arrayformula() 包起來就可以了！
3. 最後再幫每個欄位加上表頭

={"品項", "日期", "營業額";ARRAYFORMULA(
     split(
        flatten(
           B5:B10&"|"&C4:G4&"|"&C5:G10
        ),"|"
     )
)} 

將散落的名單整理成井然有序的清單

如果你有一份散落在各個欄位的 email 清單，裡面還有包含一些無意義的數字或是其他資訊，flatten 可以很輕鬆地幫你整理、變成一個完整的 email 清單！

這裡用到以下幾個步驟：

1. 將有值的表格用 flatten 包起來
2. 用 unique 取出唯一值
3. 接著用 sort 排序
4. 最後再用 query 做模糊比對，把有 ＠ 的辨識成 email (如果不懂 query 可以點擊 這裡 延伸學習)

-- 將散落的名單整理到同一欄位並排序
=sort(unique(FLATTEN(B5:D9)))
-- 去除非 email 的值
=query(I4:I,"select I where I like'%@%'")