Excel在當今商業中的使用非常普遍。在Dataquest，出于很多原因，我們通常推薦使用代碼處理數據，并且我們的許多數據科學課程的目標是教授數據分析和數據科學的高效編碼。但是，無論您多么喜歡使用Python，在一天結束時，有時您還是需要使用Excel來展示您的發現或共享您的數據。

但這并不意味著我們就不能繼續享受Python的一些效率了!實際上，使用一個名為xlwings的庫，您可以使用Python使Excel運行得更快。

在這個xlwings教程中，我們將介紹如何在Excel中使用Python來執行和使用一些常見的操作，比如根據特定的條件刪除行、使用Excel函數和公式、自動填充、創建表單、圖表等。為了跟隨這篇文章，你應該熟悉基本的Python概念(對象、方法、屬性、函數)和Python的語法，并對Excel和VBA有一定的了解。

我們將使用一個數據集，其中包含有關歐洲彩票抽獎的信息，稱為EuroMillions。這個數據集是從這個鏈接下載的，它包含了截止到9月20日的所有EuroMillions彩票信息。當你閱讀這篇文章的時候，這個鏈接上的數據應該會被更新為最新的信息，但是以防它是不可用的，這里是一個CSV文件，其中包含了來自該鏈接的9月20日的數據。

Euromillions是一種跨國彩票，在一些歐洲國家有售，特別是在安道爾、奧地利、比利時、法國(包括海外地區和集體)、愛爾蘭、馬恩島、列支敦士登、盧森堡、摩納哥、葡萄牙、西班牙、瑞士和英國(來源)。

在寫這篇文章的時候，抽獎由從50個數字(從1到50)池中抽出的5個數字和從12個數字池中抽出的兩個稱為幸運之星的數字組成。為了贏得頭獎，參加者必須正確選擇所有抽獎號碼和幸運之星。史上最大的頭等獎是1.9億歐元。(請注意，我們的數據集是以英鎊而不是歐元來計算獎金的)。

在本教程中，我們將使用Python和xlwings來與Excel一起清理一個數據集，然后生成一些圖形來可視化哪些數字最常贏得EuroMillions。

第一列是抽獎號碼，第N1-L2列是已經抽到的號碼和幸運星(按抽獎順序排列)，Jackpot列是歐元的頭獎，Wins列告訴我們有多少下注中了頭獎。

遇見 xlwings

xlwings是一個Python庫，它使Python的一些數據分析特性可以在Excel實例中使用，包括對numpy數組、pandas Series和DataFrame的支持。與其他任何Python庫一樣，我們可以使用pip或conda等常用方法來安裝它，但是如果您需要更多詳細信息，您可以在這里訪問xlwings的文檔。(地址：https://docs.xlwings.org/en/stable/installation.html )

請注意，您需要在使用本xlwings教程的計算機上安裝一個Microsoft Excel版本。

xlwings 對象

在xlwings中，有四種主要的對象類型，按遞減的層次順序排列:App(代表一個Excel實例)、Book、Sheet和Range。除了這些之外，我們還將處理Chart和Shape對象。您可以在官方文檔中找到關于這些對象和其他對象的有用信息，但是我們將一次查看一個對象。

我們首先創建一個Book實例并將其命名為wb(工作簿)。

當您運行該代碼時，它應該是這樣的。

注意，當代碼單元在Jupyter Notebook中運行時，Excel將自動啟動。

通過實例化一個Book對象，一個屬于我們的Book對象的App對象會被自動創建。下面是檢查所有打開的Excel實例的方法。

注意:我們不打算在本教程中包含每個步驟的gif圖片，因為我們不希望這個頁面對于互聯網連接緩慢或受限的人來說是一個麻煩的加載過程。但是，后續的代碼運行步驟應該與我們上面所看到的類似:當我們在Juypter中運行一個單元格時，Excel電子表格將根據我們所運行的代碼進行更新。

xw.apps對象是一個可迭代對象。要檢查哪些工作簿屬于這個可迭代對象的唯一實例，我們可以像這樣對它調用books方法。

正如預期的那樣，這個可迭代對象的惟一實例是工作簿wb。我們在下面來檢查這個事實。

同樣，我們可以檢查哪些工作表屬于這個工作簿:

我們也可以用它們的名字來引用工作表:

Sheet 對象有一個按預期工作的name屬性。讓我們更改我們唯一的工作表名稱。

我們可以將數據從某些Python對象(例如列表和元組)移入到Excel中。讓我們將我們dataframe中的數據移入到EuroMillions工作表中。為此，我們將使用range創建一個range對象，該對象將我們的DataFrame中的數據存儲在Excel中的一排單元格內，在本例中從單元格A1開始:

>>>今日簽到口令：r7kt<<<

現在運行結果是這樣子：

可以看到，df的索引列也被移動到了Excel中。讓我們清除此工作表的內容并復制不帶索引的數據。

能夠知道我們的表在哪里結束是很有用的。更具體地說，我們需要包含了數據的最后一行。為此，我們可以使用end方法和Range對象的row屬性。

不出所料，row方法會返回Range對象的row。

方法end接受一個方向(“up”(或1)、“right”(或2)、“left”(或3)、“down”(或4))作為參數，并返回另一個range對象。它模仿Excel中常見的CTRL+Shift+箭頭動作。

看，它檢查出來了！

API屬性

并不是所有的Excel功能都可以作為一個本地xlwings特性來使用。有時我們必須找到變通辦法來做我們想做的事。幸運的是，xlwings讓這一切變得非常簡單。來自官方文檔中“缺失的特性”一節:

解決方案:本質上，xlwings只是一個圍繞Windows平臺上的pywin32和Mac平臺上的appscript的靈活的包裝器。你可以通過調用其api屬性來訪問底層對象。底層對象將使用pywin32語法(感覺很像VBA)和appscript語法(感覺不像VBA)為您提供幾乎所有可以用VBA做的事情。但是除了看起來很丑之外，請記住它使您的代碼平臺變成特定的(!)。Excel Visual Basic for Applications是對各種現有Excel對象的一個詳細解釋。

排序就是xlwings中缺少的功能之一。您可能已經注意到，那些記錄是從最近到最遠的抽簽順序排列的。在接下來的幾個步驟中，我們將反轉順序。

對象ws.range(“A2:N{row}”.format(row=last_row))是一個Range對象。將api屬性追加給它之后，會生成一個VBA Range對象，該對象反過來又可以訪問它的VBA特性。

我們將使用這個VBA對象的Sort屬性。在它的最簡單的應用程序中，Sort接受兩個參數:對表排序所使用的列(作為一個VBA Range對象)和排序類型(無論希望按升序還是降序排序)。第二個參數的參數文檔可以在這里看到。我們將按升序進行排序。

把所有這些放在一起看起來就是這樣的:

下面是它運行后在屏幕上顯示的樣子(注意：第一列已經改變，現在是按升序而不是降序進行排序。

分析數據

在嘗試分析這個數據集時，我們將遇到一個問題，即日期分散在三個不同的列中。我們需要把它壓縮到一列中。為此，我們將使用Python將Excel中的列適當地連接起來。我們首先在空的相鄰列中插入一個頭部。

接下來，我們可以插入想要用作字符串的Excel公式。注意:您應該使用什么參數分隔符的具體細節取決于您機器的本地區域設置。在我的示例中，參數分隔符是逗號，這也是我在本教程中使用的，但在您的示例中，它可能是分號。

在第一個單元格中插入公式后，在常規的Excel工作流中，第二個特性是通過表的末尾自動填充其余單元格。Autofill是VBA Range對象的一個方法。它接受兩個參數，即一個將目標單元格作為參數的VBA Range對象和填充類型。我們感興趣的是枚舉為0的默認值。

這是執行這一步后屏幕顯示的大致樣子;注意最右邊的新“Date”列。

我們還可以使用所需填充類型的命名形式。為此，我們需要從模塊xlwings.constants中檢索它，該模塊包含大多數VBA屬性的枚舉參數的命名版本。您可以經常通過打印dir(xlwings.constants)來檢查可用的屬性。

(如果您不熟悉它，dir是一個本地Python函數，可以接受多種參數(模塊、類和常規對象(如列表和字符串))。例如，如果您打印dir(some_list)，它將為您輸出您可以通過列表使用的所有方法和屬性。

我們在上面所做的也可以通過下面的代碼片段來實現。

由于我們將經常使用這個操作，我們將創建一個應用默認填充的函數，給定:

• 一個工作表
• 一個表示工作表中單元格的字符串
• 一個需要填充的末尾行。

為此，我們將引入一個名為get_address的新的Range方法。它接受四個布爾參數并返回一個字符串，該字符串標識不同詳細級別的范圍。下面是這個方法的一個很有啟發性的說明。

現在我們來定義函數。

為了避免Excel進行不必要的計算，我們將使用硬編碼的值替換剛才插入到列O中的公式。在此之前，讓我們花點時間思考一下當Range是一個數組時，Range.value是什么類型的Python對象。

它是一個列表。我們來看它的前十個元素。

如果我們將這個列表插入到任何范圍中，它將會水平放置值，這不是我們想要的。為了將它們垂直放置，我們需要使用Range對象的options方法，并將transpose=True選項作為一個參數，如下所示:

現在我們可以刪除列C到E。

EuroMillions格式多年來經歷了一些溫和的修改，最后一次修改是在2016年9月24日。

從2016年9月24日起，幸運星的數量從11個數字變為12個數字。為了進行有意義的分析，我們只考慮在最后修改之后進行的抽獎。下一個代碼片段將查找修改之前的最后一次抽獎，并將其命名為to_delete。

我們現在可以刪除從第一次抽獎到to_delete的每一行。

這是我們目前的情況，此時:

在完成數據準備之后，我們現在將格式化這個表。我們首先將第一行的字體設置為粗體。

我們可以按此操作將“Jackpot”列以百萬為單位進行格式化。請注意，下面的字符串格式取決于您機器的本地區域設置。如果您的格式看起來很奇怪，試著用圓點替換逗號。更多關于Excel自定義格式的內容，請查看這里。(地址：https://exceljet.net/custom-number-formats )

作為后續工作的輔助步驟，我們將找到與最后一列數據對應的字母。

現在，我們在header單元格的底部添加邊框。與我們所做的類似，我們將使用api屬性。此外，我們還將需要Range對象的Border屬性、邊框對齊枚舉和邊框樣式。我們將在header單元格的底部設置一個雙邊邊框(行樣式-4119，對齊方式9)。

現在讓我們對行和列進行自動調整。

哎呦!這看起來有點壓扁了，讓我們設置所有列的寬度為J列的寬度，這似乎是最大的值。這里(https://docs.microsoft.com/en-us/office/vba/api/excel.range.columnwidth ) 是我們下面正在使用的ColumnWidth的文檔。

那樣看起來更好一點。這個工作表我們就處理完成了!

讓我們add一個名為Frequencies的新空白工作表，并將其分配給Python變量frequencies。

我們將在這個工作表中填入我們剛剛在EuroMillions工作表中處理過的數據集中每個數字和每個幸運星的絕對頻率。

下面，我們將為單元格B1中的頻率插入一個header，在單元格B2中，我們將輸入一個公式，計算A2中的值在C2:G201范圍內出現的次數。換句話說，我們將計算1在N1-N5列中出現的次數。在此之后，我們將自動填充B列上的其余單元格，以對它們各自的行執行相同的操作。

我們對幸運星也執行同樣的操作:

此時，我們的新工作表看起來應該像這樣：

我們正在接近我們的目標。讓我們創建一個名為Graphs的工作表。

現在我們將創建一個Chart對象。這只會產生一個空白的白框，但是不要擔心!我們稍后會用這個方框來圖表化我們的數據。

我們可以像name工作表一樣來name圖表。方法set_source_data允許我們通過傳入一個范圍對象來定義我們圖表的數據源。

Excel將嘗試猜測x軸應該是什么，但我們可以使用VBA Chart方法FullSeriesCollection來強制讓它成為我們在Frequencies上創建的數字。我們可以通過使用nr_freq.api索引1的對象來編輯圖表:

Excel非常擅長猜測用戶想要什么樣的圖表，但是為了防止它猜錯，我們將強制它成為柱狀圖。這里列出了各種類型的圖表。唉，將這些圖表與chart_type屬性的可能值連接起來的唯一文檔就是其源代碼本身。

現在我們將定義圖表的高度和寬度。度量單位為points。

此時，我們應該會看到:

使用SetElement方法和參數2一起設置圖表上方的標題。請在這里查看其它參數。(地址;https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.core.msochartelementtype?view=office-pia )

我們來添加最后的修飾。我們使用HasLegend屬性刪除圖例。

我們將xlCategory類別作為參數1傳入給Axes方法，并將TickLabelSpacing屬性設置為1，這確保了圖表軸上的每個元素都被顯示。

為了完成對這個圖表的格式化，我們通過將Line對象的Visible屬性設置為0來刪除輪廓。

這里我們會看到:

下面我們對幸運星也做了幾乎同樣的事情。

最后，我們創建了一個顯示jackpot演變的時間序列圖。

我們通過將TickLabels的NumberForma屬性設置為期望的外觀來修復垂直軸“labels”的格式。

這樣我們就完成了!現在我們保存該文件并退出我們的Excel實例。

希望本xlwings教程對您有所幫助!

學習xlwings的一些有用資源包括官方文檔、這個格式化備忘錄、用于Excel文檔的VBA和由xlwings的開發者——Felix Zumstein本人設計的《xlwings: 使用Python處理Excel》課程。

英文原文：https://www.dataquest.io/blog/python-excel-xlwings-tutorial/

譯者：好酒不上頭