身為試算表愛好者，你是否也遇過這種令人崩潰的「髒資料」？
當你從計價軟體或訂單系統匯出報表時，所有的品項、金額、運費竟然通通被擠在同一個儲存格裡，只用「換行」簡單隔開。例如一個儲存格內擠了： 產品：蘋果 x 5 金額：500 運費：60
面對這種「一格多項」的資料，如果你還在用傳統的手動複製、貼上，再一列列搬移，這不僅是效率殺手，更是出錯的溫床。今天，我要分享一套強大的「自動化數據管線（Data Pipeline）」，利用五個核心函數的組合，讓這些混亂的資料瞬間自動排好隊！
--------------------------------------------------------------------------------
1. 數據管線的核心邏輯：從拆解到重組
在進入公式前，我們先理解這套「自動化管線」的操作流程：
解構 (SPLIT)： 辨識換行符號，將資料橫向拆開。
引擎 (ARRAYFORMULA)： 讓公式具備處理整欄資料的能力。
壓平 (FLATTEN)： 將橫向的陣列「壓扁」成垂直的一維清單。
過濾 (QUERY)： 排除廢棄雜訊，只留下我們需要的內容。
--------------------------------------------------------------------------------
2. 秘密武器：用 CHAR(10) 辨識看不見的換行
資料拆解的第一步，是要告訴試算表：「在哪裡切開？」
儲存格內的品項通常是用視覺上看不見的「換行」來區分。在 Google 試算表中，換行符號的專屬代碼是 CHAR(10)。
Behind the scenes： 當你下達 =SPLIT(A2, CHAR(10)) 時，試算表就會識別出換行位置，打破儲存格的物理限制，將內容橫向拆分到不同的格位。
--------------------------------------------------------------------------------
3. 全自動化的引擎：ARRAYFORMULA 的範圍魔力
如果每一列都要手動複製公式，那就稱不上自動化。一般的 SPLIT 只能處理單一儲存格，若要處理整個 G2:G 的範圍，我們必須請出 ARRAYFORMULA。
Pro Tip： 在輸入公式時，如果 ARRAYFORMULA 的自動完成選項沒彈出來，我的小撇步是：先在隔壁儲存格打好 ARRAYFORMULA() 的結構，再把內層公式剪下貼進去，確保語法精準無誤。
--------------------------------------------------------------------------------
4. 從橫向到縱向：FLATTEN 讓資料「扁平化」排隊
透過 SPLIT 拆開後的資料會呈「橫向」排列。但為了方便分析，我們需要它們「縱向」垂直排好。這時就是 FLATTEN 大顯神通的時候。
它的魔法： FLATTEN 能將多維陣列「壓平」，變成一維垂直清單，讓原本橫向生長的資料乖乖向下排隊。
⚠️ 避坑指南： 使用 FLATTEN 時要特別小心。如果你直接對整欄（如 A:A）操作，Google 試算表會試圖將每一列（包含空白列）都展開，這極容易觸發「超過 1,000 萬個儲存格」的上限錯誤。建議先限制範圍（例如 G2:G100），或是搭配我們接下來要說的 QUERY 進行過濾。
--------------------------------------------------------------------------------
5. 精確篩選的守門員：QUERY 與那個致命的大寫 "C"
資料壓平後，清單中會充斥著大量的空白列與雜訊。我們需要 QUERY 函數擔任最後的守門員。
為什麼這裡用 Col1 而不是 A？ 這是許多人的疑問。當資料經過 FLATTEN 處理後，它就不再是原本試算表上的實體欄位，而是一個「虛擬陣列」。在處理虛擬陣列時，我們必須使用 Col1, Col2 這種編號方式來指定欄位。
🚨 關鍵筆記： 在撰寫語法時，「Col」的「C」必須是大寫！如果你打成小寫的 col1，試算表會直接罷工並報錯。
範例條件： ... "where Col1 contains '產品'" 這行指令會幫我們精準抓出包含「產品」字樣的列，過濾掉所有的空白與垃圾資訊。
--------------------------------------------------------------------------------
6. 專業級設置：建立「細項表」保持主表整潔
作為一名效率專家，我強烈建議：永遠不要在原始資料表直接做拆解作業。
最佳的做法是建立一個新的工作表（例如命名為「訂單細項」），利用跨表公式來抓取主表的資料。
跨表語法： 工作表名稱!範圍 (例如：'主表'!G2:G)
這樣做能確保原始資料（主表）乾淨無虞，而拆解後的資料（細項表）則擁有獨立的欄寬與格式，不僅視覺上清晰，後續維護更是事半功倍。
--------------------------------------------------------------------------------
7. 最終奧義：五合一神級公式
將以上步驟結合，你將得到這個威力無窮的自動化公式：
=QUERY(FLATTEN(ARRAYFORMULA(SPLIT('工作表1'!G2:G, CHAR(10)))), "where Col1 contains '產品'")
總結與預告
這套流程成功地將擠成一團的髒資料轉化為結構化的數據清單。這是邁向自動化管理的第一步。
不過，雖然我們把資料「拆散」排好了，但現在儲存格裡可能還留著像「產品：蘋果 x 5」這樣的冗長字串。下一次，我將分享如何利用「正規表示法 (Regex)」精準地把「產品名稱」與「數量」單獨提取出來，別忘了持續關注！
最後想問問大家： 你的試算表中是否也有那些「藏在同一個儲存格」卻無法統計的寶貴數據呢？現在就試試這套拆解術，把它們救出來吧！
沒有留言:
張貼留言
注意：只有此網誌的成員可以留言。