Office Space:撰寫 Microsoft Office 應用程式指令碼的秘訣

Office Space

歡迎蒞臨《Office Space》專欄,這裡提供撰寫 Microsoft® Office 應用程式指令碼的秘訣。每週二和週四我們將刊登新的秘訣,若要參閱以前討論過的秘訣,請造訪 Office Space 過往文件。如果您有關於 Microsoft Office 指令碼方面的問題,請將電子郵件傳送到 scripter@microsoft.com。我們無法保證能夠逐一回答每個問題,不過我們會盡力而為。

排序 Microsoft Excel 中的資料

還記得 Scripting Guys 有人的兒子六歲就參加「柯克蘭青少年俱樂部」玩腰旗美式足球 (Flag Football)。話說球隊教練決定讓自己的兒子當明星跑鋒,所以只教其他所有的小朋友怎麼擋球。而且柯克蘭隊在第一場球賽前只練習怎麼擋球,反正其他球員越會擋,就表示教練的兒子越有機會達陣得分。

結果柯克蘭對一開球,對手的回球員一把抓到橄欖球就輕鬆達陣了,完全無人阻擋。不僅毫髮無傷,後面也沒人追他,因為柯克蘭隊的球員全都在努力擋球。原來是教練根本忘了傳授腰旗美式足球的一個重點:敵方拿到球時,要努力追上他們持球進攻的球員,想辦法抓他腰上的旗子來阻止進攻。

真糟糕。

那我為何會想起這段往事呢?其實也沒什麼,只不過是我們《Office Space》專欄邁入第五個月,竟然還沒有教您如何在 Microsoft Excel 裡排序資料,慚愧啊慚愧。這並不表示我們忘了撰寫 Office 指令裡這個很重要、很基本的部分,而是.... 好啦,我們可能真的忽略了,現在就給讀者好好的補償。

舉個簡單的例子好了。假設我們有一個基本的試算表 (C:\Scripts\SortTest.xls),內含資料如下:

Division

Employees

Sales Total

North America

698

44,000,000

Europe

453

51,000,000

Africa

712

33,000,000

Asia

381

48,000,000

South America

833

72,000,000


實際的試算表看起來像這樣:

Microsoft Excel


要怎麼著手呢?有一個方法是:


Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes


別擔心,我們會解釋這全部是如何運作的。先定義 xlAscending 和 xlYes 這兩個常數,並將值分別設為 1 (稍後為您解說這兩個常數的用途)。建立 Excel.Application 物件的執行個體,然後將 Visible 屬性設為 True,設定後者是為了能夠在畫面上看見 Excel。接著我們使用這行指令碼開啟 C:\Scripts\SortTest.xls 活頁簿:


Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")


目前為止都很順利。活頁簿開啟後,我們使用這行指令碼繫結到第一個工作表:


Set objWorksheet = objWorkbook.Worksheets(1)


要排序資料,得建立幾個 Range 物件。首先我們要指出想排序的資料,這個範例指令碼要排序的範圍是整個試算表,而這個指令碼可以簡單快速地選取試算表內使用的儲存格 (請注意,這個範圍的物件參考為 objRange)。


Set objRange = objWorksheet.UsedRange


指定要排序的資料後,要指定將以哪個欄位作為排序依據 (第一輪測試就只依一欄排序)。若要指定排序依據欄位,我們得先選擇該欄內隨便一個儲存格,本例先排序欄 A,所以只要先建立一個只包含儲存格 A1 的範圍。然後按照下面指令碼,替這個範圍的物件指定一個名為 objRange2 的物件參考:


Set objRange2 = objExcel.Range("A1")


現在準備要將資料排序了,只要短短一行指令碼:


objRange.Sort objRange2, xlAscending, , , , , , xlYes


我們只不過呼叫了 Sort 方法,加上一堆五花八門的參數。這些參數依序解說如下 (這個順序是有意義的):

參數

說明

Key 1

這是第一個排序依據欄位。如果您還記得,前面我們建立了一個只包含儲存格 A1 的範圍,並替該範圍指定一個物件參考 objRange2。這個物件參考會傳給 Sort 方法。

Order 1

用來指定想要以遞增或遞減方式替資料排序。要以遞增排序,請使用 xlAscending 常數,值為 1。要以遞減排序,則使用 xlDescending 常數,值為 2。

Key 2

這是要排序的第二個欄位。我們現在使用的第一個指令碼只以 A 欄作為排序依據,所以不用指定這個參數,只要輸入一個空格,加上一個英文逗號即可。

Type

只有在使用樞紐分析表時才會用到這個參數,我們就依照空格加逗號的方式,依樣畫葫蘆即可。這個參數可不能忽略不管,因為前面說過,參數的順序很重要。第五個參數一定得是 Order 2,不然指令碼可能會失敗。您一定得加入第四個參數並留白,Order 2 才會成為第五個參數。

Order 2

Key 2 的排序順序。這次也一樣要留白,因為這個指令碼沒有第二個排序索引鍵。

Key 3

想要排序的第三個欄位,這個指令碼中留白。

Order 3

Key 3 的排序順序。因為沒有第三個欄位,這裡留白。

Header

用來告訴指令碼資料是否有標題列。本例中有,所以我們使用 xlYes 常數,值為 1。如果資料沒有標題,則使用 xlNo 常數,值為 2。


看吧?其實沒有那麼複雜。執行指令碼後,應該會得到一個像下面一樣的結果:

Microsoft Excel


您瞧,資料已經依 Division (欄位 A) 按字母排序。真是一點也不賴。

那如果我們想拿 Sales Total (欄位 C) 作排序依據呢? 沒問題:


Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("C1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes


注意囉,現在我們得執行下列程式碼,把物件參考 objRange2 指向欄位 C:


Set objRange2 = objExcel.Range("C1")


除此之外,程式碼幾乎一樣。跑過這個指令碼後,會得到下列結果:

Microsoft Excel


結果在我們掌握之中。

對了 (真是問不完啊),萬一想要先照欄位 C,再照欄位 A 排序,要怎麼做呢?得出的結果對範例資料集沒啥影響,指令碼如下:


Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

objRange.Sort objRange2, xlAscending, objRange3, , xlAscending, , , xlYes


您看,這份指令碼定義了兩個範圍:objRange2 (參考欄位 C) 和 objRange3 (參考欄位 A)。注意喔,我們呼叫 Sort 方法時在排序順序後頭 (xlAscending) 指定了 objRange。接著指定 objRange3,塞一個空格,然後才放排序順序。別忘了,空格代表 Type 參數,也就是只有 PivotTables 才需要使用的參數。即便這裡不會用上,還是要替它留個位置,不然指令碼無法執行。這也是在 Excel 裡排序資料最容易出錯的地方。

如果我想依照三個欄位排序,也就是 Excel 排序依據的欄位數上限,可以嗎?沒問題,這裡不會列出整個指令碼,我們有信心讀者已經知道怎麼辦了。Sort 指令看起來像這樣:


objRange.Sort objRange2, xlAscending, objRange3, , xlDescending, _
    objRange4, xlDescending, xlYes


三個物件參考,三個排序順序。就是這麼簡單!

今天我們學到兩件事:第一,玩腰旗美式足球要記得抓住敵方持球進攻球員的腰旗。第二,在 Excel 裡可以使用 Sort 方法和一堆參數替資料排序。生命的意義當然不只如此,但是目前我想不出有哪些比這些更重要的了。

顯示: