如何善用 ChatGPT 輔助資料分析與視覺化工作?-(二)Excel 指令初探篇

ChatGPT Basic Prompt for Excel

· ChatGPT,Excel

預計撰寫一系列文章,探討 ChatGPT 與資料分析的協作可能性,這是我的第二篇,主要以 Excel 的基礎問答為主,之後也預計寫作更進階或是實作教學的文章,感謝您的閱讀,有任何意見也歡迎反饋給我。

Tips:本文相關的 ChatGPT 提問(Prompt)也會列出,大家可複製文字使用,自己來提問感受看看

broken image

※ 作者:彭其捷(https://www.visualization.tw/

ps. 以下是本網站 ChatGPT 系列文章,有興趣的話可一併參考:

資料分析與視覺化工作流程中,常會用到許多分析工具,本篇想探索 ChatGPT 與相關工具的協作情境,基於好奇,先問問 ChatGPT:哪些是它認為的主流資料分析工具呢?

broken image

從 ChatGPT 得到的回應是:Excel、R、Python、Tableau、SAS 等等,考量文章篇幅,本篇選擇先以 Excel 的基本指令探索為主,更進階的 Excel 實作或是其他軟體,則會陸續寫在其他篇內容中。

關於 Excel + ChatGPT,來聽聽它自己的說法吧:

Prompt:ChatGPT 如何與 Excel 搭配?

broken image

其中「函數」跟「VBA」聽起來蠻合理的,也是本文撰寫主題,不過「數據連接」或是「插件」等相關內容,應該許多還在持續演進當中,就先留給未來的文章來探討吧!

本文將內容區分為「一、基礎問答」、「二、進階問答」,進行相關的 ChatGPT x Excel 語法試驗,並列出共 14 項問答的試驗結果如下,可供大家參考:

  • Q1:基本函式的說明與範例
  • Q2:協助撰寫邏輯判斷語法
  • Q3:文字處理語法
  • Q4:地址處理情境
  • Q5:日期與時間函數
  • Q6:時間格式判斷
  • Q7:輔助生成統計函式
  • Q8:請 ChatGPT 協助生成隨機資料
  • Q9:生成多重條件函式
  • Q10:延伸提問 Excel 操作方式
  • Q11:同時生成模擬資料與處理函式
  • Q12:協助生成 VLOOKUP 函式語法
  • Q13:介紹 VBA 與其基礎用法
  • Q14:生成 VBA 的複雜語法

一、基礎問答

Q1:基本函式的說明與範例

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

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

broken image

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

Q2:協助撰寫邏輯判斷語法

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

我們來問問 ChatGPT 邏輯判斷函式可以怎麼撰寫公式吧:

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

broken image

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

Q3:文字處理語法

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

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

broken image

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

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

broken image

Q4:地址處理情境

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

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

broken image

ChatGPT 提供了基礎的文字剖析公式 LEFT 與 RIGHT。

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

broken image

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

Q5:日期與時間函數

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

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

broken image
broken image
broken image

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

Q6:時間格式判斷

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

broken image

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

Q7:輔助生成統計函式

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

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

broken image
broken image
broken image

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

Q8:請 ChatGPT 協助生成隨機資料

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

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

broken image
broken image

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

二、進階問答

Q9:生成多重條件函式

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

Prompt:可否提供 Excel 函式

當 B 欄位等於「王小明」時,在 C 欄位輸入「老闆」

當 B 欄位等於「陳大牛」時,在 C 欄位輸入「員工」

當 B 欄位等於「李明明」時,在 C 欄位輸入「助理」

broken image

Q10:延伸提問 Excel 操作方式

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

Prompt:可否提供 Excel 函式

當 C 欄位等於「老闆」時,在 D 欄位輸入「10000」

當 C 欄位等於「員工」時,在 D 欄位輸入「5000」

當 C 欄位等於「助理」時,在 D 欄位輸入「3000」

並且告訴我怎樣設定規則,讓 D 欄位大於 6000 的文字變成紅色的

broken image

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

Q11:同時生成模擬資料與處理函式

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

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

broken image

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

broken image

Q12:協助生成 VLOOKUP 函式語法

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

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

broken image
broken image


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

broken image
broken image

測試結果也非常順利,ChatGPT 直接給出了可以複製貼上使用的指令。

broken image
broken image

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

Q13:介紹 VBA 與其基礎用法

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

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

broken image

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

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

broken image

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

Q14:生成 VBA 的複雜語法

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

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

broken image

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

broken image

小結

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

然而,在撰寫此文的同時,ChatGPT 等 AI 生成工具依然持續在演進中,不禁好奇未來又可能會往怎樣的方向前進呢?我們或許可以從微軟目前釋出的 Copilot 影片來看出一些端倪:

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

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

工商:ChatGPT 生產力與資料課程

文末小工商,因為許多人在詢問是否有相關的課程,也因此促成了我開課的動力,如果您對於以下兩個主題感興趣,歡迎可以參考我預計製作的新課程:

(1) 如何透過 ChatGPT 強化工作生產力

(2) 如何透過 ChatGPT 協作資料分析與視覺化

 

介紹網址:

broken image

歡迎訂閱

感謝您的閱讀,如果您喜歡我的文章,很歡迎於下方輸入 Email 訂閱本站文章,就能收到後續新文章推送。