善用Copilot與Gemini，打造會思考的工作表

講師:王玠瑛老師

Excel資料管理與分析實戰

關於王玠瑛老師

關於玠瑛老師的三種身份

跑者玠瑛的戰鬥力分析

無法順利完賽的原因分析

收到玠瑛老師傳的Line訊息

下載上課教材

b4teacher

搜尋Google或輸入網址

請搜尋關鍵字:

b4teacher

關於Copilt in Excel

使用Copilt in Excel的優點:

1.Copilot可以直接幫你處理 Excel的檔案

2.其他的生成式 AI，你需要手動將資料貼到AI的對話框交由 AI 處理之後，再手動將資料貼回來

使用Copilt in Excel的缺點:

1.不是每個版本的 Office 都有 Copilot in Excel

2. Microsoft 365 個人版一年的費用是 3,090 元,而且一個月的 AI 點數只有 60 點。

3.如果希望有無限的 AI 點數，需升級 Microsoft Copilot 的帳號，每月訂閱費用為 $20 美元(630元台幣)

如果要使用Copilt in Excel這個功能:

1.需要使用Microsoft 365

2.Office 2016、2019、2024都沒有喔

3.Microsoft 365個人版、企業版以上才有喔

4.Microsoft 365教育版可能沒有

5.使用時需要開啟自動儲存，將檔案存到 OneDrive，同時聊天模式需要切換成編輯資料模式

Microsoft 365教育版(沒有Copilot)

Microsoft 365個人版(有Copilot)

試用Microsoft 365個人版

https://www.microsoft.com/zh-tw/microsoft-365/try-prs

AI點數每個月60點

要安裝 Microsoft 365 的話，需要先移除之前你已經安裝的其他版本 Office

取消訂閱在這裡

使用Microsoft 365版Excel

的Copilot來做資料處理

這份 Excel 檔裡含三個工作表：

1.報名清單

2.簽到清單

3.AI提示詞整理

報名清單為活動前就已經有報名的人員名單；

簽到清單為上課當天實際有來上課簽到的人員名單。

我們將使用 Copilot in Excel 來下提示詞，進行檔案的處理

範例01-使用Copilt進行資料比對

報名清單為活動前就已經有報名的人員名單

簽到清單為上課當天實際有來上課簽到的人員名單

問題處理

1.報名清單為活動前就已經有報名的人員名單；

2.簽到清單為上課當天實際有來上課簽到的人員名單。

​

我們想要比較：「報名清單」與「簽到清單」這兩份工作表，如果有出席就在「報名清單」的「比對」欄位裡面做一個打勾的記號；如果沒有記錄，就顯示「缺席」。

提示詞

請在「報名清單」工作表增加一個欄位，欄位名稱叫做「比對」。只要在「簽到清單」工作表裡面有姓名記錄的，就幫我在「報名清單」的「比對」欄位裡面做一個打勾的記號；如果沒有記錄，就顯示「缺席」。

問題處理

1.報名清單為活動前就已經有報名的人員名單；

2.簽到清單為上課當天實際有來上課簽到的人員名單。

​

我們想要：在「簽到清單」工作表的 D 欄新增一個欄位，欄位名稱叫做「是否報名」

如果姓名有在報名清單裡，就寫「有報名」；如果沒有在報名清單裡，就幫我寫「現場報名」。

提示詞

請在「簽到清單」工作表的 D 欄新增一個欄位，欄位名稱叫做「是否報名」。請幫我比對「報名清單」工作表，如果姓名有在報名清單裡，就寫「有報名」；如果沒有在報名清單裡，就幫我出「現場報名」。

提示詞

請幫我把「簽到清單」工作表裡面，所有標記為「現場報名」的學員資料直接抽離出來，新增並獨立成一張新的工作表，並將該工作表取名為「現場報名」。

請幫我建立一個統計工作表，把相關的數據都統計在這裡。請自動計算並填入：

1. 預先報名的人數

2.有預先報名且已經報到（打勾）的人數

3. 現場報名的人數

4. 總到場的人數

目前累計已經用了 10 點

使用Office增益集

GPT for Excel Word

請在「報名清單」工作表增加一個欄位，欄位名稱叫做「比對」。只要在「簽到清單」工作表裡面有姓名記錄的，就幫我在「報名清單」的「比對」欄位裡面做一個打勾（✔）的記號；如果沒有記錄，就顯示「缺席」。

用這個增益集來處理，一樣會遇到你的 AI 點數用完之後，就需要購買才有辦法使用的問題。

使用Gemini

幫你寫Excel的VBA程式

資料雜亂：各方匯集的資料格式五花八門，難以在第一時間做到格式一致。

重複抄寫費時：紀錄表等文件，常需要一筆筆手動複製、存檔、發送，耗時費力。

統計整合不易：散落各處的表單極難彙整，增加多餘工作量。

Excel 資料整合的痛點解析

傳統思維的翻轉：以前是盡量使用滑鼠進行機械化的操作，現在要懂得用 AI幫您寫程式，複製貼上更快速。

工具互補的原則：簡單的數學運算請直接使用 [Excel 公式] 快速處理。

VBA最佳的戰場：面對複雜商務邏輯與跨軟體整合（如 Excel 轉 Word / PDF）才用 VBA。

AI工具可以協助自動化

硬體限制：使用者端必須使用「電腦版」的Microsoft Excel(不限版本)。手機與網頁版本目前皆不支援執行巨集。

維持後台純淨：在操作 VBA 前，請先關閉其他不相關的 Excel 檔案，避免執行時程式產生錯誤的動作。

進入【開發人員模式】前的「安全護欄」

巨集不可復原：VBA 執行後的任何更動，是無法透過快捷鍵 Ctrl + Z 進行還原的！

記得先建立副本再進行操作：在開啟巨集或測試任何程式碼之前，務必養成習慣，將原檔案建立一份「副本」再進行操作。

最高安全防護原則

備份提示：建議每次大幅修改巨集前，將檔案複製並命名加上日期後綴，如 _20260530_備份.xlsm。

Windows 系統設定路徑：� 檔案 ➔ 選項 ➔ 自訂功能區 ➔ 勾選「開發人員」 ➔ 確定。

Mac 系統設定路徑：� Excel 功能列 ➔ 喜好設定 ➔ 功能區和工具列 ➔ 勾選「開發人員 」

開啟「開發人員」標籤

一次性設定：此功能(開發人員)在此台電腦上只需設定一次，重開 Excel 依然有效。

1.進入 VBA 後台

(1)點擊 Excel 頂部工具列的「開發人員」標籤。

(2)點選最左側的「Visual Basic」按鈕即可開啟編輯器。

認識後台：VBA 編輯器與插入模組(1)

2. 選擇專案與模組

(1)在左側的「專案總管」中，選中目前操作的活頁簿名稱

(2)點選上方功能列：插入 ➔ 模組，開啟白色程式碼編輯區

(3)系統會自動生成「Module 1」模組

(4)原則上「一個模組放一段獨立程式碼」，避免混合導致執行混亂

認識後台：VBA 編輯器與插入模組(2)

實戰案例 1：

檢查空白欄位並標色

這份 Excel 檔裡含三個工作表：

1.AI 指令與 VBA 程式碼

2.常見語法錯誤與排查

3.個案記錄總表

這份檔案主要是要用AI來整理個案記錄總表

範例02-使用Gemini寫Excel的VBA程式

原始檔案為【個案紀錄總表】，在C欄(身份證字號)的部分有些資料是缺漏的，因此想透過 VBA的程式來檢查是否有空白欄位，並且替空白欄位標上顏色。

提示詞 = 定義 AI 角色 + 最終任務 + 限定範圍 + 輸出格式

​

• 定義 AI 角色：賦予 AI 特定身分 (如：國小老師、程式設計師、心理師)，避免 AI 找不到定位而隨意回答 。
• 明確的說明任務：使用精確動詞，如「改寫」、 「總結」 、 「摘要」 、「翻譯」 。
• 加上量詞/限制：例如「3句話」、「6個重點」、「字數不超過 300 字」 。
• 設定產出格式：指定以「表格」、「條列」、「Markdown」、「中文/英文」顯示 。

精簡版提示詞

你是一個 Excel VBA 大師。

1.請幫我寫一段手動觸發的 Sub 程式，檢查工作表「個案記錄總表」的 C 欄（身份證字號）。

3.檢查範圍為第二列開始，直到 A 欄（個案編號）有文字的最末一列。

4.如果 C 欄為空字串，請將該儲存格填滿為淡紅色（RGB: 250, 219, 216）。

5.請在執行完畢後彈出視窗（MsgBox）提示共發現幾筆空字串。

在Gemini貼上的提示詞

辨識結構：VBA 程式碼必定以 Sub 開頭，並以 End Sub 做為結尾。

代碼貼上：點選 AI 右上角的 Copy 按鈕，貼到剛剛Excel後台(VBA 編輯器)建立的 Module 1 編輯區。

啟動程式：游標點在程式碼內，按上方工具列的「綠色三角形（執行）」按鈕。

程式碼佈署與後台執行示範(1)

確認回饋：Excel前台會跳出提示視窗，C 欄空白儲存格已確實標示淡紅色。

確認代碼開頭的 Sub 後面接的是英文或有意義的英文縮寫（例如：Sub CheckEmptyCells()）。

也可以直接在後台編輯器按下鍵盤上的 F5 鍵，即可快速一鍵運行。

程式碼佈署與後台執行示範(2)

開啟 Gemini

執行該巨集

將 AI 生成的程式碼貼進來

前臺在執行後會跳出一個提示的訊息

儲存格已經標示為淡紅色

插入圖案：在 Excel 工作表，點選 [插入] ➔ [圖例] ➔ [圖案]，拖拉出一個圓角矩形，填入「檢查身分證字號」文字。

指定巨集：對著畫好的圓角矩形按滑鼠右鍵，在選單中點選 [指定巨集]。

完成連結：選取剛剛貼入的 VBA 程式名稱，按下確定。

完美體驗：滑鼠游標移到圖案上時，會自動變成「小手」形狀，按一下即自動啟動巨集。

效率提升：製作「快速執行按鈕」(1)

降低操作門檻：一般行政人員完全不需理解 VBA 後台，只需點按前台按鈕即可完成複雜檢核。

視覺引導優點：可自由設定按鈕的顏色與樣式，大幅提高表單專業度與易用性。

檔案儲存：需要將這個檔案儲存成 XLSM（附有巨集的檔案格式），下次再開啟時才能夠繼續使用這個巨集。

效率提升：製作「快速執行按鈕」(2)

拖曳出一個圓角矩形，上面文字寫「檢查身分證字號」

在圓角矩形上面按右鍵，並且選擇「指定巨集」

將剛剛設定完成的巨集指定到這個按鈕上面。

設定完成之後，將遊標移動到這個圓角矩形上面時，就會出現一個手指的圖案

因為已經有設定了一個巨集，所以需要另存新檔，將檔案的副檔名改成 XLSM，這樣下次啟動該檔案時，巨集功能才不會不見。

實戰案例2：

批量日期格式轉換

混亂資料痛點：D 欄生日資料包含西元斜線（1998/07/14）、短線（1995-04-09）或西元年置後等混雜格式。

統一標準目標：希望能將所有多元西元格式，整齊轉換為標準民國年（如：88/07/14）。

保護原始資料：下達指令時，要求 AI 將轉換後的民國年日期填寫在隔壁的 E 欄，絕對不直接覆蓋原始 D 欄資料。

模組隔離部署：於 VBA 後台點選插入新模組（**Module 2**），不與 Module 1 混在一起，保持專案易讀性。

痛點情境：西元格式轉換民國年

D 欄生日資料包含西元斜線（1998/07/14）、短線（1995-04-09）或西元年置後等混雜格式。

中文字標題衝突：Excel 表格第一列通常是中文字標題（如「生日」）。程式強制將文字轉為日期，會直接引發系統當機報錯。

空白與異常字元：資料中若有漏填的空白格，或是夾雜特殊字元，也會導致執行中斷。

防錯指令追問：請在程式中加入除錯機制：**「若遇非日期格式，請跳過不執行；如遇異常，請彈窗回報出錯行數，其餘欄位仍繼續轉換」**。

完全覆蓋更新：將 AI 給的新程式碼，完全貼上並覆蓋 Module 2 中的舊代碼，一鍵執行即可穩定偵錯。

日期轉換之進階除錯與防錯

你是一個 Excel VBA 大師。

1.請寫一段 Sub 程式，處理「個案記錄總表」D 欄的出生日期。

2.因為資料格式混亂（如西元斜線、西元短線等），請幫我將 D 欄的所有日期，統整轉換為「民國年格式（例如：民國61年6月3日）」並寫入到相鄰的 E 欄（出生的民國年）。

3.如果 D 欄的內容無法識別或非有效日期，請在 E 欄寫入「格式錯誤」，並用淡黃色標記該 D 欄儲存格。

4.請從第2列執行到 A 欄最末列。

在Gemini貼上的提示詞

為了方便快速處理，一樣可以將這個巨集做成一個按鈕

為了方便做管理，也可以新增一個按鈕區的工作表，將所有的按鈕都移動到這個地方。

實戰案例3：

混亂電話與手機整理

電話格式非常混亂

你是一個 Excel VBA 大師。

1.請處理「個案記錄總表」F 欄的電話資料。

2.請將G欄的儲存格屬性改為文字屬性，處理後的資料請放到G 欄。

3.資料常因遺漏字首0或帶有空格符號而混亂。

請寫一段程式：

(1)先去除F欄所有文字中的空格。

(2)請先判斷該筆資料為行動電話還是市話。

(3)如果是行動電話（以 9 開頭且總共 9 碼，表示漏了0），請在開頭補 0 變成 10 碼，並轉換格式為 09XX-XXX-XXX 寫入G欄位。

(4)如果是行動電話(09開頭且總共10碼)請直接轉換格式為 09XX-XXX-XXX 寫入G欄位。

(5)如果是市話（非 9 開頭），請自動在開頭補0變2碼區碼，並轉為 (0X)XXXX-XXXX或(0X)XXX-XXXX 格式(範例:227208889請轉換成(02)2720-8889、範例:037 350811請轉換成(03)735-0811)。

(6)如果遇到不符合以上規則的，請顯示資料有誤。

(7)處理完成後請彈出視窗提示說明處理的結果。

在Gemini貼上的提示詞

Excel 欄位痛點：Excel 會自動將純數字開頭的「0」剔除，導致個案手機變 9 碼（如：912xxxxxx）。

向 AI 下達標準清洗邏輯： � ➔ 邏輯 1：若字串為 9 碼且開頭非 0，**開頭自動補 0**，並統一轉換為標準 10 碼：09xx-xxx-xxx。 � ➔ 邏輯 2：若本來就是以 09 開頭的 10 碼手機號碼，直接標準化為減號格式：09xx-xxx-xxx。

補足消失的「0」與標準化(1)

輸出指定 G 欄：維持防護原則，指定將轉換完的手機號碼輸出在全新的 G 欄，不覆蓋破壞 F 欄原始亂局。

實戰價值：可避免簡訊群發或通訊登錄時因開頭少 0 導致傳送失敗，大幅提升資料正確率。

補足消失的「0」與標準化(2)

實戰案例4：

自動建立縣市下拉選單

傳統作法痛點：以往要建立下拉選單，必須手動點進「資料驗證 ➔ 允許清單」，再逐字打入 22 個縣市並用半角逗號隔開，費時且極易漏字。

AI 指令清晰描述：� 「請幫我寫一段 VBA，自動在 **H 欄（縣市）** 設定資料驗證清單，內容需包含台灣全部的 22 個縣市名稱。」

秒級完工：直接利用手動觸發的 Sub 程式碼貼入新模組。執行後，H 欄瞬間建立好 22 縣市下拉選單。

免手動輸入，VBA 一鍵配置資料驗證(1)

資料整潔度提升：全面阻絕輸入錯字（如「臺北」與「台北」不一）、打錯字導致無法篩選分析的窘境。

快速複製擴展：此代碼也可用於個案類型、承辦人名單一鍵生成。

免手動輸入，VBA 一鍵配置資料驗證(2)

你是一個 Excel VBA 大師，

請幫我寫一段 VBA，將 H 欄（住所-縣市）設定為資料驗證清單，清單內容為台灣全部的 22 個縣市的名稱。

在Gemini貼上的提示詞

實戰案例5：

雙層連動選單

在H 欄（住所-縣市） 選單切換時（例如選擇高雄市），隔壁的 I 欄（住所-行政區） 、J欄(住所-郵遞區號)選單必須自動連動。

僅顯示該縣市所屬的行政區域，避免資料填寫出錯。

核心需求解析

自動觸發事件：此功能並非手動按按鈕執行，而是「工作表內容變更時自動偵測執行」。

部署鐵律：這類事件型程式碼絕對不可貼在一般模組（Module）中，否則將完全失效！

特別注意

一般模組無法接收前台儲存格的變更狀態，此功能必須在工作表專屬後台部署。

關鍵例外規則

正確部署路徑：在 VBA 左側專案總管，對著「個案紀錄總表」工作表名稱，滑鼠左鍵快速點擊兩下。

專屬代碼：在右側白色程式區貼入 AI 生成以 Private Sub Worksheet_Change 開頭的專屬程式碼。

注意：貼上後絕對不要按下後台的綠色三角形執行按鈕

回到前台測試：直接關閉後台，回到 Excel 改變 H 欄縣市，I 欄、J欄便會自動連動。

正確部署與測試四步驟

你是一個 Excel VBA 大師，

目前H 欄（住所-縣市）已經被設定為資料驗證清單，清單內容為台灣全部的 22 個縣市的名稱。

現在我想要讓 I 欄(住所-行政區)可以跟前面的 H 欄（住所-縣市）連動。

當 H 欄選定後，I 欄會自動變成資料驗證清單，清單內容為該縣市的行政區名稱。

當 I 欄被選取了之後，J 欄(住所-郵遞區號)會自動帶出該行政區的郵遞區號號碼

在Gemini貼上的提示詞

使用Gemini

替代Copilot in Excel

這份 Excel 檔裡含三個工作表：

1.報名清單

2.簽到清單

3.AI提示詞整理

報名清單為活動前就已經有報名的人員名單；

簽到清單為上課當天實際有來上課簽到的人員名單。

範例03-使用Copilt進行資料比對

報名清單為活動前就已經有報名的人員名單

簽到清單為上課當天實際有來上課簽到的人員名單

因為 Gemini 無法像 Copilot in Excel 一樣進駐到你的 Excel 檔裡面，所以你必須要給它截圖的畫面，讓它知道你在說什麼。

Windows 內建【自訂區域截圖】：

1.按下 Win + Shift + S，可自由框選截圖範圍，圖片會暫存於剪貼簿

2.Ctrl + V 貼上。

先上傳剛剛的兩張截圖，再輸入以下提示詞：�

你是一個 Excel VBA 大師。

請你參考我提供給你的兩張截圖，裡面有兩張工作表，分別是「報名清單」以及「簽到清單」

1.請寫一段 Sub 程式，在「報名清單」工作表增加一個欄位，欄位名稱叫做「比對」。

2.只要在「簽到清單」工作表裡面有姓名記錄的，就幫我在「報名清單」的「比對」欄位裡面就顯示「出席」；如果沒有記錄，就顯示「缺席」。

在Gemini貼上的提示詞

先上傳剛剛的兩張截圖，再輸入以下提示詞：�

你是一個 Excel VBA 大師。

請你參考我提供給你的兩張截圖，裡面有兩張工作表，分別是「報名清單」以及「簽到清單」

1.請寫一段 Sub 程式，請在「簽到清單」工作表的 D 欄新增一個欄位，欄位名稱叫做「是否報名」。請幫我比對「報名清單」工作表，如果姓名有在報名清單裡，就寫「有報名」；如果沒有在報名清單裡，就幫我寫出「現場報名」。

在Gemini貼上的提示詞

重新上傳新的兩張截圖，再輸入以下提示詞：�

你是一個 Excel VBA 大師。

請你參考我提供給你的兩張截圖，裡面有兩張工作表，分別是「報名清單」以及「簽到清單」

請寫一段 Sub 程式，請幫我把「簽到清單」工作表裡面，所有標記為「現場報名」的學員資料直接抽離出來，新增並獨立成一張新的工作表，並將該工作表取名為「現場報名」。

​

在Gemini貼上的提示詞

重新上傳新的兩張截圖，再輸入以下提示詞：�

你是一個 Excel VBA 大師。

請你參考我提供給你的兩張截圖，裡面有兩張工作表，分別是「報名清單」以及「簽到清單」

請寫一段 Sub 程式，請幫我建立一個統計工作表，把相關的數據都統計在這裡。請自動計算並填入：

1.預先報名的人數

2.有預先報名且已經報到（出席）的人數

3.現場報名的人數

4.總到場的人數

在Gemini貼上的提示詞

使用Gemini

建立成績查詢系統

這份 Excel 檔裡含多個工作表，目前會使用到以下兩個工作表：

1.期末考 (合併列印)

2.成績單查詢系統(練習)

期末考 (合併列印)為期末考的完全的成績

成績單查詢系統(練習)，目前是一個尚未設定任何程式或函數的工作表。

範例檔04：班級成績單

問題處理

我們想要做一個成績查詢系統，使用者只需要在系統裡面輸入學號，系統就會自動帶出其他欄位，例如：國文的成績、英文的成績等資料。

​

為了怕使用者不小心修改到其他的資料，所以除了學號之外，其他欄位都要鎖定。

​

另外，我們還希望可以增加一顆按鈕，點擊之後就可以另存成 PDF 檔。

請參考我提供給你的截圖，是兩張工作表，分別是：期末考 (合併列印)、成績單查詢系統(練習)

​

你是 Excel VBA 高手，請幫我寫一段 Sub 程式碼

1.當我在「成績單查詢系統（練習）」工作表的B2輸入學號時，系統就會自動到「期末考核 (合併列印)」工作表去查詢相關的資料，並且將相關資料帶回這個工作表上面的其他欄位，例如：E2帶入姓名、A5帶入國文成績，依此類推，將這個成績單查詢系統建立完成。

2.為了避免其他人動到不該動的儲存格，請將「成績單查詢系統(練習)」工作表中 B2 之外的儲存格全部鎖定，設定為不能輸入資料。

3.請幫我生成一顆按鈕。當我查詢完成績之後，只要按下那顆按鈕，就會自動幫我生成 PDF 檔，並且詢問我要儲存在哪個位置。

在Gemini貼上的提示詞

https://gemini.google.com/share/a6b34d8c31fe

Gemini對話

測試一下，輸入學號就可以成功帶出其他資料

插入\模組，然後將程式碼貼進去，再按【綠色的三角形】進行執行

目前這張工作表已經被鎖定了。如果需要進行修改，需要點擊:校閱\取消保護工作表。