淺談 Python 與 Excel 的整合術

摘要

現代職場上,只要需要做資料處理,我們難免都要和 Excel 這個市佔率最高的試算表軟體打交道,而 Python 這個語言最大的應用場景就是做資料處理,也因此,了解 Python 與 Excel 的串接與整合就成為一個遲早需要克服的挑戰,是一個值得化時間研究的議題,而身為一個同時開設 Excel 與 Python 課程的講師,我希望能用一系列的文章與大家分享一些我個人在這個議題上的一些經驗與洞見。

Excel 最重要的功能之一:VBA語言

Excel 本身除了一些內建的強大功能,像是錄制巨集、規劃求解以外,就是使用者能夠用 VBA 這個程式語言客製化出符合你個人需求的一些巨集或是函數。這個功能之所以重要,是因為它能幫助使用者自動化很多和 Excel 相關的工作流程。試想一下,今天你若需要在 Excel 上執行一連串複雜的操作才能達成的效果,若能透過執行一個簡單的 VBA 小程式瞬間完成,是件多麼美好的事!

問題是,和 VBA 程式語言相關的文件、書籍、與訓練課程非常少,當然這不意外,畢竟 VBA 是個冷門的程式語言、它除了開發和客製化與微軟應用程式相關的功能外,完全沒有其他的用處,無法吸引更多優秀的開發者,大幅的限制了 VBA 這個語言本身和 Excel 生態圈的發展。

微軟的回應

當然,微軟是個重視使用者回饋的公司,所以近期在 Excel 用戶反饋平台中, Python 作為 Excel 的腳本語言成為了該平台上最熱門的話題,而微軟也對此議題做出了正面的回應:微軟發起了一個投票來收集更多的用戶反饋信息,並調查用戶想要如何在 Excel 中使用 Python。

從微軟的積極程度來看,Python 在未來成為 Excel 官方的腳本語言已成了定局。意味著未來若學會 Python 這個語言後,也可以直接為 Excel 寫自動化的程式,無需另外學 VBA。 而未來 Excel 用戶也能夠像目前使用 VBA 腳本一樣,透過 Python 寫出來的腳本與 Excel 檔、數據以及一些 Excel 核心函數進行串接。

但是在微軟正式將 Python 變成 Excel 的官方腳本語言以前,要是稍為搜尋一下,其實 Python 社群已經有許多讓 Python 串接 Excel 的套件,這些套件能讓我們透過 Python 實作出類似 VBA 巨集的功能,允許我們在未來還沒發生前就已經可以開始用 Python 為 Excel 寫程式。

來實戰吧

這邊就先和大家分享一個在 Python 社群裡處理 Excel 常用的套件:openpyxl

Python 的安裝我就不在這篇文章裡多做解釋,這邊若你是使用 Python 3,請在指令列執行:

pip install openpyxl

若你是使用 Anaconda 的,請在指令列執行:

conda install openpyxl

OK 環境安裝好了! 接下來我們就可以開始串接 Python 與 Excel 了!

在繼續下去之前,先很快的與各位講解一下用 VBA 為 Excel 寫自動化程式所需要的知識:

Excel 是一個以為基本結構的工具,這個表可以是一個儲存格(Cell)、或是多個儲存格,也就是一個陣列 (Range) ,這樣的一個陣列就適合用來表示一個數列、向量、或是矩陣。

這是一個儲存格 (Cell)
這是一個 Excel 的陣列 (Range)

而儲存在 Excel 的資料必須遵循橫豎結構;因為必須遵循橫豎結構,所以資料的維度不會超過二,因為這樣的特性,讓 Excel 變成一個嚴謹但又十分簡單的工具。若用一句話來總結為 Excel 寫程式,就是用程式針對一個或多個儲存格進行操作。

這是一個 Excel 的二維陣列

Hello World

接下來就來用 Python 與 openpyxl 寫一個簡單的 Hello World 吧,這邊請先下載我爲各位讀者准備的範例 Excel 檔案

若你在執行這個程式時出現類似像 permission denied 的錯誤:

表示 python 沒有權限寫入一個正在被 Excel 開啓的 .xlsx 檔案,因此,別忘了先把你的 Excel ,或是開啟你的 .xlsx 檔案的應用程式關掉!

若成功執行的話,你的試算表應該會顯示:

恭喜你完成了你的第一個 Python 與 Excel 串接的程式!

接下來我們來處理一個比較實際的例子

請各位打開範例 Excel 檔案裡面名為 2330 的試算表,該試算表裡面記錄了從去年10月到現在的臺積電股票的收盤價,接下來我們來嘗試算出每一天的日報酬率。這邊只要在 C3 儲存格輸入以下公式即可:

=(B3-B2)/B2 

這樣看起來只算出了一筆日報酬率對不對?但是大家都知道 Excel 有個帥氣的 “往下拉” 功能,因此,你只需按住C3儲存格的右下角,往下拉動到 C99 再放開即可:

看起來很簡單不是嗎?但試想一下:今天這個檔案如果有 10 到 20 個不同的試算表,每一個試算表都記錄了不同的股票,而你每天都需要計算每一支股票的報酬率,而這個時候輸入 10 到 20 次同樣的公式並且下拉一次就變成了一件重複又繁瑣的事,我們不應該將自己寶貴的時間和注意力浪費在這樣的事情上! 尤其是對於會寫程式的你我來説,更應該用程式把這件事自動化!

很完美的,這次我們就透過程序化的方式,把所有的日報酬率算出來了!

在我們用 Python 完成這支程式後,今天無論你有多少個試算表,只要試算表上資料的格式相同,這支程式就能自動幫你算出該試算表上所有的日報酬率,無須另外做 N 次的公式輸入與下拉!

希望這個簡單的範例能讓大家見識到用 Python 自動化和 Excel 相關工作流程的魔力。在接下來的文章,我會繼續和大家分享 Python 與 Excel 還有哪一些好用的整合方案以及應用場景。

小結

現代職場上不管在任何領域或職位、只要做資料處理都很難離開 Excel,而在使用 Excel 時由於很容易遇上重複又繁瑣的地方,也因此寫程式把和 Excel 相關的工作自動化就成爲一件時間報酬率極高的事務,另外,由於 VBA 語言的局限,微軟未來將 Python 變成 Excel 的脚本語言幾乎成了定局,因此 Python 與 Excel 的串接也成了一件值得我們投資時間研究的議題。我們在這個文章裡介紹了 openpyxl 這個串接 Python 與 Excel 的套件,它十分簡單好用,但是它的限制就是無法寫入資料到一個正在被其他應用程式開啟的 Excel 檔。

如果您喜歡這篇文章,請多按下方的「拍手」圖像幾次、分享到社群網站以及訂閱 Pyradise追蹤更多 Python 資訊,請關注 Pyradise 的臉書粉絲專頁!🙏想看影音教學?🎥
我們將在 Pyradise 的 Youtube 頻道,定期跟大家直播分享相關心得!
訂閱我們!👇👇👇

Pyradise

在 Py 樂園輕鬆學習 Python。

Eugene Chang (張有勁)

Written by

Pyrate of Pyradise 一個碼農,講師,與創業者的自白

Pyradise

Pyradise

在 Py 樂園輕鬆學習 Python。

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade