16.6 C
Taipei
星期二, 9 7 月, 2024

透過 ChatGPT 輔助資料分析與視覺化工作?Excel 的 14 項指令測試|天地人學堂

本文探討了一些 Excel 與 ChatGPT 的基礎與進階的協助語法!而由於自己同時有數據教學工作,對於生成資料集的方式也非常實用!然而,在撰寫此文的同時,ChatGPT 等 AI 生成工具依然持續在演進中,不禁好奇未來又可能會往怎樣的方向前進呢?

彭 其捷
彭 其捷https://tdr.la/3r0GREN
彭其捷|資料分析師與數據專家 業師專注探討數據與視覺化、UIUX領域,曾參與多項大型資料科學、人工智慧等專案。在超過40個機構進行過演講、教學課程,包含人工智慧學校、天地人學堂、KKday、中研院、工研院、資策會、臺灣大學、清華大學、商業教育平台等。業師熱愛分享,無論是擔任顧問服務或業師,都致力推廣資料應用、資料視覺化與UX使用者體驗設計。

 

ChatGPT 是近期超火紅的 AI 生成工具,其對資料分析或是相關數據工作也非常有幫助!本篇先 Excel 的基本指令探索為主,關於 Excel + ChatGPT,來聽聽它自己的說法吧:

Prompt:ChatGPT 如何與 Excel 搭配?

其中 ChatGPT 回答的「函數」跟「VBA」聽起來蠻合理的,也是本文撰寫主題,不過「數據連接」或是「插件」等相關內容,應該許多還在持續演進當中。本文共列出共 14 項問答的試驗結果如下,可供大家參考:

Q1:基本函式說明
Q2:撰寫邏輯判斷語法
Q3:協助文字處理
Q4:協助地址處理
Q5:查詢日期與時間函數
Q6:判斷時間格式
Q7:生成統計函式
Q8:生成隨機資料的指令
Q9:生成多重條件函式
Q10:提問 Excel 進階操作方式
Q11:生成模擬資料加上處理函式
Q12:協助生成 VLOOKUP 函式語法
Q13:說明 VBA 與其基礎用法
Q14:提供 VBA 之複雜語法

 

Q1:基本函式說明

假設是對於 Excel 函式可以如何運用還不熟悉的讀者,可以請 ChatGPT 先列舉一些基本函式並進行說明與提供範例。

Prompt:生成一些 Excel 函數的範例

可以看到 ChatGPT 幫忙提供了非常實用的 SUM 函數(計算總和)、AVERAGE 函數(計算平均值)、MAX 函數(尋找最大值)及 MIN 函數(尋找最小值)。這些都算是蠻基礎的函式,實務上很常使用。

 

Q2:撰寫邏輯判斷語法

除了數值的計算以外,不論日常生活或工作上也很常運用邏輯判斷,例如:若今天的日期大於某一商品的有效期限,表示該商品已過期了、需要特別處理。
我們來問問 ChatGPT 邏輯判斷函式可以怎麼撰寫公式吧:

Prompt:可否提供 Excel 邏輯判斷函式,如果 B 欄數值大於 100,則在 C 欄位放入 True,不然就放 False

ChatGPT 提供的邏輯判斷函式是 IF,是個實用的選擇,這一題 ChatGPT 也成功通過考驗了!大家腦中可以思考更多可能的邏輯判斷函式應用。

 

Q3:協助文字處理

Excel 除了數據處理之外,也可以用來進行文字相關的資料處理。我們來問問 ChatGPT 可以如何使用 Excel 進行文字處理:

Prompt:可以怎麼使用 excel 做文字處理?

ChatGPT 推薦了可以用來合併字串的 CONCATENATE 、用來轉換大小寫的 UPPER 和 LOWER 函數應用及說明。但可能我們對於某些語法並不熟悉,可與 ChatGPT 追問進一步的語法。

Prompt:可以多說明一下 CONCATENATE,大小寫轉換函數UPPER和LOWER的應用與範例嗎?

 

Q4:協助地址處理

資料處理情境中,也蠻常會需要處理地址資料,例如:萃取或整併某些文字、或是欄位的相關清理,來問問看 ChatGPT 能不能幫幫我們,以下語法直接用口語的方式提出了可能的地址處理情境。

Prompt:可否提供 Excel 邏輯函式,如果 A 欄放的是中文地址,有哪些可以處理地址的函式可以用呢?

ChatGPT 提供了基礎的文字剖析公式 LEFT 與 RIGHT。
來問問看比較進階一點的組合語句,因為一串地址中包含不少資訊,除了縣市外,有時候我們可能需要針對特定路段進行延伸分析,每個地址的路名字數不同、位置也不同,我們再來請教 ChatGPT 可以怎麼處理:


這次提供的函式就比較進階一點,ChatGPT 告訴我們,可以透過 MID 函式找到指定位置的中間文字、FIND 函式來找到特定關鍵字的位置,或 SUBSTITUTE 來調整特定文字;雖然這些語法對於 Excel 老手來說很基礎,然而對於新手來說,可以同時得到語法的舉例與說明,還是非常有幫助!

 

Q5:查詢日期與時間函數

接著我們請 ChatGPT 介紹一些常用的日期和時間函數及其範例:

Prompt:請介紹幾個日期與時間函數


ChatGPT 馬上就列舉了許多 Excel 好用的日期與時間函數,除了日期外,也可分別針對年/月/日/時/分/秒進行處理。

 

Q6:判斷時間格式

時間相關格式有許多種,如果格式錯誤,會導致資料錯誤,想問一下 ChatGPT 是否有能幫助判斷的方法:

TIMEVALUE 可以用來進行格式轉換,將時間轉換成 Excel 可以識別的時間數字;而透過搭配 IF 以及 ISNUMBER 兩個邏輯判斷函式,則可組成一個判斷時間格式是否正確的函式。

 

Q7:生成統計函式

統計相關數據處理與分析也是很常使用的部分,我們請 ChatGPT 提供一些常用統計函式:

Prompt:請提供我一些統計函式與範例

除了先前已提供過的 SUM、AVERAGE、MAX 與 MIN,還包含了 COUNT(計數)、STDEV(標準差)、MEDIAN(中位數)、MODE(眾數)、CORREL(相關係數) 及 FREQUENCY(頻率分佈)。

 

Q8:生成隨機資料的指令

有時候我們會需要生成一些隨機資料進行輔助,例如用假資料製作產品說明文件、或用於抽籤等。

Prompt:可否說明一下隨機產生資料的函式?

ChatGPT 提供我們可以產生隨機數值的 RAND、RANDBETWEEN 與 INDEX、產生隨機字母的 CHAR、合併產生隨機字串的 CONCATENATE。

 

Q9:生成多重條件函式

來試試看多重條件生成,讓提問句中包括部分的組合式需求以及邏輯判斷文法,例如:當答案不只有對或錯(TRUE / FALSE)兩種答案,而是比較像是 WHICH 的概念的話,可以怎麼撰寫函式呢?

Prompt:可否提供 Excel 函式

當 B 欄位等於「王小明」時,在 C 欄位輸入「老闆」
當 B 欄位等於「陳大牛」時,在 C 欄位輸入「員工」
當 B 欄位等於「李明明」時,在 C 欄位輸入「助理」

 

Q10:提問 Excel 進階操作方式

這次刻意的加入了「並且告訴我怎樣設定規則,讓 D 欄位大於 6000 的文字變成紅色的」的衍伸指令,來看是否可順利完成。

Prompt:可否提供 Excel 函式

當 C 欄位等於「老闆」時,在 D 欄位輸入「10000」
當 C 欄位等於「員工」時,在 D 欄位輸入「5000」
當 C 欄位等於「助理」時,在 D 欄位輸入「3000」
並且告訴我怎樣設定規則,讓 D 欄位大於 6000 的文字變成紅色的

ChatGPT 這次除了提供了 IF 組合而成的多重判斷函式。還同時提供了 Excel 的相關操作步驟!

 

Q11:生成模擬資料加上處理函式

接著我們試試能否請 ChatGPT 在同一個 Prompt 中,同時完成「生成資料」與「函式需求」的任務,而此情境也是順利達成了,我覺得 ChatGPT 已經完全可以擔任 Excel 小老師的輔助角色。

Prompt:如果有4年份,共48個月的銷售報表,可否提供 Excel 的函式,能夠自動完成每月的自動加總,並以年為單位做累計

ChatGPT 提供了一個簡潔的「年份、月份、銷售額」資料格式,並提供了一個新的公式運用「SUMIFS」(ps. 這一項公式相對複雜一些,同上附上公式說明)。

下方連結提供了一些常用公式,我們挑「CUMIPMT」計算累計利息的公式,請 ChatGPT 說明一下可以怎麼使用吧:

來源:https://support.microsoft.com/zh-tw/office/%E8%B2%A1%E5%8B%99%E5%87%BD%E6%95%B8-%E5%8F%83%E7%85%A7-5658d81e-6035-4f24-89c1-fbf124c2b1d8

 

Q12:協助生成 VLOOKUP 函式語法

VLOOKUP 是踏入 Excel 函式世界時,許多人會碰到的難關,也是實務上常用的函式;我們來請 ChatGPT 說明一下怎麼應用,以下展示兩種情境,第一種是在同一張表的 VLOOKUP,第二種是跨表執行 VLOOKUP。

Prompt:可否說明 VLOOKUP 函式及生成範例

上面的情境比較單純,下面的情境則針對我的預設條件來生成,例如如果我有以下的資料表格(區分 Sheet1 與 Sheet2),而希望在 Sheet2 透過 VLookup 指令查找 Sheet1 資料。

 

Prompt:可否協助生成跨表的 VLOOKUP 函式,請滿足以下條件

(1) 當 Sheet1的 A 欄位與 Sheet2 的 B 欄位 Match 時
(2) 則將 Sheet1 的 C 欄位值,填入 Sheet2 的 D 欄位中
測試結果也非常順利,ChatGPT 直接給出了可以複製貼上使用的指令。

將 ChatGPT 給出的指令,實際貼上使用,真的一字不改就能夠使用,非常方便!

 

Q13:說明 VBA 與其基礎用法

若要讓在 Excel 上進行的工作更進一步自動化,VBA 會是下一個可以精進的技能方向。我們請 ChatGPT 介紹一下什麼是 Excel VBA:

Prompt:請介紹一下什麼是 Excel VBA

再請 ChatGPT 示範一個簡單的 Excel VBA 範例:

Prompt:請示範一個簡單的 Excel VBA 範例

ChatGPT 也確實生成了一個不錯的範例,只需四行程式碼就可以完成自動調整列高與欄寬的 VBA。

 

Q14:提供 VBA 之複雜語法

再請 ChatGPT 示範一個複雜的 Excel VBA 範例,任務也順利執行成功,不過相關內容會需要更多對於 VBA 等語法的概念了解。

Prompt:請示範一個複雜的 Excel VBA 範例

由於程式碼較長,以下直接用另一張完整程式碼截圖顯示生成的 VBA 指令:

結語

本文探討了一些 Excel 與 ChatGPT 的基礎與進階的協助語法,個人覺得非常實用,確實能夠提升不少工作流程的生產力!而由於自己同時有數據教學工作,對於生成資料集的方式也非常實用!

然而,在撰寫此文的同時,ChatGPT 等 AI 生成工具依然持續在演進中,不禁好奇未來又可能會往怎樣的方向前進呢?

我們或許可以從微軟目前釋出的 Copilot 影片來看出一些端倪:

嵌入影片:https://www.youtube.com/watch?v=I-waFp6rLc0

影片中提及,未來 Excel 將會加入 Copilot 對話框,讓操作者直接與它對話,可以執行像是 (1) 請 Copilot 提出可能的分析洞見 (2) 協助生成模擬資料 (3) 自動協助圖表上色凸顯重點 (4) 生成樞紐分析結果等等。

寫作文章的當下,office copilot 的功能,還沒有正式上線,如果想要 follow 最新的訊息,可以追蹤 微軟的部落格


文末小工商,因為許多人在詢問是否有相關的課程,也因此促成了我開課的動力,如果您對於以下兩個主題感興趣,歡迎可以參考我預計製作的新課程:
(1) 如何透過 ChatGPT 強化工作生產力
(2) 如何透過 ChatGPT 協作資料分析與視覺化
彭其捷 介紹網址:https://www.tableau.tw/blog/chatgpt


授權文章來源:透過 ChatGPT 輔助資料分析與視覺化工作?Excel 的 14 項指令測試