【Excel工具教學】一鍵搞定！將Excel資料依指定欄位拆分成多個工作表 (Worksheets)

行政人員在處理資料時，常遇到長官要求：「把這份總表依照各個單位（或是部門、類別）拆開，做成一個單位一個工作表，方便查看。」如果手動複製貼上，不僅耗時且容易出錯。

今天介紹一支我自己寫的 Excel 輔助程式，可以自動幫您完成這項繁瑣的工作！

工具功能簡介

這支程式可以讀取您指定的 Excel 檔案，根據您設定的「欄位」，將資料自動拆分，並以該欄位的內容作為名稱，儲存成獨立的工作表（Sheet）。

範例情境：
假設有一份「常用英文單字1200字」的列表，我們希望依照單字的「首字母（A-Z）」將其拆分成26個工作表。

---

使用步驟教學

第一步：下載與開啟工具

1. 請先至部落格下載範例程式：sheet_split_sheets.xlsm。

2. 開啟下載的工具檔。

3. 注意： 欲處理的資料檔案，必須與此工具檔放在同一個資料夾內。

第二步：資料準備與預處理 (重要！)

在執行自動拆分前，必須先整理您的原始資料檔。以下以影片中的英文單字表為例：

1. 建立分類欄位：

   • 由於原始資料只有單字，我們需要提取首字母來分類。

   • 使用 Excel 函數 =LEFT(欄位, 1) 取出首字母。

   • 技巧提示： 工作表名稱不分大小寫（例如 'A' 和 'a' 視為相同），若資料混合大小寫會導致錯誤。建議配合 =LOWER() 函數將字母統一轉為小寫。

   • 公式範例：=LOWER(LEFT(C2,1))

   • 完成後，將公式複製到所有列，並建議使用「複製」->「貼上值」將公式轉為純文字。

2. 資料排序 (最關鍵的一步)：

   • 請務必針對「要拆分的欄位」進行排序！

   • 原因： 程式是逐行執行的，如果資料是 A -> B -> A，程式建立完 A 工作表後，處理到 B，接著遇到 A 時會試圖再次建立名為 A 的工作表，這會導致程式報錯（名稱重複）。

   • 所以在執行前，請將該欄位依照 A 到 Z (或數值大小) 排序好。

第三步：設定參數

回到工具檔 (sheet_split_sheets.xlsm)，在綠色區塊輸入對應的參數：

1. 輸入檔案名稱： 輸入要處理的檔案主檔名（例如：常用英文單字1200字，不需副檔名）。

2. 輸入副檔名： 輸入該檔案的格式（例如：xlsx 或 xls）。

3. 要分類的欄位在第幾欄： 填入數字（例如分類欄在第 2 欄，就填 2）。

4. 輸入開始列數： 資料從第幾列開始（通常第 1 列是標題，資料從第 2 列開始）。

第四步：開始執行

1. 確認原始資料檔已關閉（或保持開啟皆可，視版本而定，建議存檔後依照程式指示）。

2. 點擊工具檔中的 「開始執行」 按鈕。

3. 程式會自動運作，您會看到螢幕閃動，這是正在建立各個分頁。

4. 執行完畢後，會跳出「處理完畢」的視窗。

---

常見問題與除錯

Q：執行到一半出現錯誤訊息，說名稱重複？

• A： 這通常是因為您的原始資料沒有排序，或者分類欄位中有大小寫視為不同但在Excel工作表命名視為相同的情況（如 T 和 t）。

  • 解法： 請回到原始資料，確認分類欄位已統一（如全小寫），並且務必重新執行「排序」，確認相同類別的資料都聚在一起，存檔後再重新執行程式即可。

成果展示

執行成功後，您原本的 Excel 檔案中，下方的工作表分頁就會依照您指定的欄位（如 a, b, c... z）整齊排列，每個分頁裡只包含該類別的資料。

希望這個小工具能幫助各單位的行政人員，提早下班！

[AppSheet教學] 如何解決圖表顯示「月平均」時，月份排序不正確的問題 (TEXT函數應用)

在 AppSheet 製作統計圖表時，我們常會需要將每日的資料合併成「每月」的平均值或總和來檢視。但你是否遇過一個狀況：當你將「年」與「月」合併顯示時，圖表的排序卻亂掉了？例如 2022年的1月 竟然排在 2021年的10月之前？

這通常是因為欄位被視為數字或文字排序時，單數月份（1-9月）沒有「補零」所造成的。今天這篇教學將透過 TEXT() 函數，教大家如何讓月份自動補零，完美解決圖表排序問題。

問題情境

假設我們有一個紀錄「每日喝水紀錄」的 App，我們希望製作一個圖表，顯示每個月的平均喝水量。

原本的做法可能是新增一個虛擬欄位（Virtual Column），將年份和月份串接起來：

• 原始公式： CONCATENATE(YEAR([日期]), MONTH([日期]))

• 結果： 20221 (代表2022年1月)、202110 (代表2021年10月)

問題點：
在電腦排序邏輯中，如果不補零，排序會依照位數或字元比較。這會導致時間軸錯亂，例如 2022年的 1月 (20221) 可能會因為數字比對的關係，顯示位置不符合我們預期的時間順序。

解決方案：使用 TEXT 函數自動補零

要解決這個問題，關鍵在於讓月份變成 兩位數 (例如 1月變成 01)。我們可以利用 AppSheet 的 TEXT() 函數來達成。

步驟一：新增虛擬欄位 (Virtual Column)

1. 進入 AppSheet 編輯器，點選 Data > Columns。

2. 選擇你的資料表，點擊 Add Virtual Column。

3. Column Name (欄位名稱)：取名為「年月」或是「YearMonth」。

步驟二：輸入正確的公式

我們需要修改公式，將原本單純取用 MONTH() 的部分，改為使用 TEXT() 函數並指定格式。

• 修改後的公式：

  codeExcel

  CONCATENATE(YEAR([日期]), TEXT([日期], "MM"))

公式解析：

• YEAR([日期])：取出年份 (例如 2022)。

• TEXT([日期], "MM")：將日期轉換為文字，參數 "MM" 代表強制顯示為兩位數的月份。

  • 如果是 1月，會回傳 "01"。

  • 如果是 10月，會回傳 "10"。

透過這個公式，2022年1月就會變成 202201，而 2021年10月是 202110。這樣在進行文字或數字排序時，順序就絕對正確了。

步驟三：設定圖表 (UX View)

1. 前往 UX 頁籤，新增或是修改你的圖表 View。

2. View type 選擇 Chart。

3. Chart columns (圖表欄位)：加入剛剛建立的「年月」虛擬欄位。

4. Group aggregate (聚合方式)：選擇 AVERAGE (平均值) 並選擇喝水量的欄位。

步驟四：儲存並驗證

按下 Save 儲存後，重新觀察你的圖表。你會發現 X軸的月份已經依照正確的時間順序（202110, 202111, 202112, 202201...）由左至右整齊排列了。

總結

TEXT() 函數在處理日期格式轉換時非常強大。它有兩種用法：

1. 單一參數： 把數字轉文字。

2. 兩個參數： TEXT(時間, 格式)，例如 TEXT([Timestamp], "HH:MM") 或本範例的 TEXT([Date], "MM")。

只要遇到日期排序錯亂，通常都是「位數不統一」造成的，記得使用這個小技巧來補零，就能輕鬆解決！ 

【Google教學】如何讓表單送出時，自動產生「民國日期+流水號」的編號？(使用 AI Studio 寫程式)

今天這篇教學要來解決一個很多人的痛點：當我們製作 Google 表單時，希望在表單送出後，Google 試算表（Sheets）能自動幫我們產生一組「民國日期 + 流水號」的編號。

例如：今天是民國 114 年 12 月 13 日，第一筆資料要是 1141213-0001，第二筆是 1141213-0002，以此類推。

以前要寫這種程式很頭痛，但現在有了 Google AI Studio，我們可以請 AI 幫我們寫 Google Apps Script (GAS)，完全不用自己動腦！這篇文章也會順便分析為什麼推薦用 GAS 而不是 Excel 公式。

---

為什麼不用公式 (Formula) 就好？

很多人第一直覺是：「用 Excel 或 Google Sheets 的公式（如 COUNTIF 或 ArrayFormula）做流水號不就好了嗎？」

這裡有一個很嚴重的隱藏風險：

1. 刪除資料會導致編號亂掉： 如果你使用公式（例如 ROW() 或 COUNTIF），一旦你刪除中間某一行資料，下面的編號全部會重新計算。原本的 0005 號可能會變成 0004 號，這對於訂單或報名序號來說是災難。

2. 新版表格的限制： Google Sheets 最近更新了「表格」功能，對於自動擴展的陣列公式支援度有些改變，可能會導致公式無法自動帶入新的一列。

結論： 想要產生「固定不變」的流水號（刪除資料也不會跑掉），唯一推薦使用 Google Apps Script (GAS)。

---

實作步驟教學

第一步：準備 Google 表單與試算表

1. 建立一個 Google 表單（例如包含姓名、備註）。

2. 點擊「回應」並連結至 Google 試算表。

3. 打開連動的 Google 試算表，在原本的欄位中間（或最後面），插入一個新欄位，命名為「自動編號」。

   • 注意：請記住這個欄位是第幾欄（A=1, B=2, C=3...）。在本範例中，自動編號是在 C 欄（第 3 欄）。

第二步：利用 AI Studio 產生程式碼

我們不需要自己寫程式，直接去問 Google AI Studio。

1. 前往 Google AI Studio。

2. 輸入以下提示詞（Prompt）：

   我想要表單送出時，會自動在「自動編號」這一欄（C欄），依據時間戳記自動產生 1141213-0001 格式的編號。
   前面是民國日期，後面四碼是當日產生的流水號。請給我 Google Apps Script 的程式碼。

3. AI 會告訴你邏輯，並給你一段完整的程式碼。它通常會建議使用 LOCK 機制來避免多人同時送出時編號重複的問題。

第三步：貼上 Google Apps Script

1. 回到你的 Google 試算表。

2. 點選上方選單的 「擴充功能」 -> 「Apps Script」。

3. 會跳出一個程式碼編輯器，將裡面原本的 myFunction 清空。

4. 將剛剛 AI 給你的程式碼全部複製貼上。

5. 關鍵修改： 檢查程式碼中關於「欄位」的設定。如果 AI 寫的是第 1 欄，但你的自動編號在 C 欄，請將該數字改成 3。

6. 點擊上方的磁片圖示「儲存」，專案名稱可以取跟試算表一樣。

第四步：設定「觸發條件」 (最重要的一步！)

程式碼貼上去並不會自己動，我們必須告訴 Google：「當有人填寫表單時，要執行這段程式」。

1. 在 Apps Script 左側選單，點擊一個像鬧鐘的圖示 「觸發條件」 (Triggers)。

2. 點擊右下角的 「新增觸發條件」。

3. 設定如下：

   • 執行功能： 選擇剛剛貼上的函數名稱（通常是 generateAutoID 之類的）。

   • 部署作業： 上端 (Head)。

   • 活動來源： 試算表 (From spreadsheet)。

   • 活動類型： 提交表單時 (On form submit) <--- 這一定要選對！

4. 按「儲存」。

第五步：核對權限

儲存觸發條件時，Google 會跳出視窗要求授權：

1. 選擇你的 Google 帳號。

2. 可能會出現「Google 尚未驗證這個應用程式」的紅字警告（因為這是你自己寫的腳本）。

3. 別擔心，點選左下角的 「進階 (Advanced)」。

4. 點選最下方的 「前往... (不安全)」。

5. 點選 「允許 (Allow)」。

---

測試成果

1. 回到你的 Google 表單預覽頁面。

2. 隨便填寫一筆資料並送出。

3. 回到 Google 試算表觀察。

4. 你會發現，雖然表單原本沒有填寫編號，但在資料進來的瞬間，Script 會自動在 C 欄補上 1141213-0001。

5. 再送出第二筆，就會變成 1141213-0002。

6. 壓力測試： 試著刪除第一筆資料（0001號），你會發現第二筆資料依然維持 0002，不會因為前面被刪除而自動遞補變成 0001。這就是我们要的穩定流水號！

---

補充：如果堅持要用公式 (Formula) 怎麼做？

雖然不推薦，但如果你的資料永遠不會刪除，你可以直接在 Google 試算表的欄位中使用公式。

可以使用類似以下的邏輯（針對民國年與當日流水號）：

codeExcel

==IF(A2="",, TEXT(YEAR(A2)-1911,"000") & TEXT(A2,"mmdd") & "-" & TEXT(COUNTIFS($A$2:A2, ">=" & INT(A2), $A$2:A2, "<" & INT(A2) + 1), "0000"))

(註：這是概念示意，實際公式需依照欄位 A 的時間戳記進行調整)

公式法的缺點再次提醒：
一旦你把中間的某一列刪除，所有的 COUNTIFS 計算都會改變，原本的編號就會全部亂跳。請務必小心使用。

---

總結

使用 Google AI Studio 搭配 Apps Script，我們可以輕鬆解決 Google 表單無法自動產生客製化流水號的問題。這個方法穩定、可靠，且具備真正的資料庫編號特性（不會隨意變動）。