Scripting Guy 為您解答問題

Hey, Scripting Guy!

歡迎使用 TechNet 專欄,Microsoft Scripting Guy 會在此為您解答有關系統管理指令碼的常見問題。您有關於系統管理指令碼方面的問題嗎?請將電子郵件傳送到 scripter@microsoft.com。我們無法保證能夠逐一回答每個問題,不過我們會盡力而為。

資源

如何標示在 Excel 試算表中第一次出現的數字?

Hey, Scripting Guy! Question

嗨,Scripting Guy!我想要嘗試判斷並標示在 Excel 工作表中第一次出現的數字。不過,卻始終不得其門而入。您可以幫助我嗎?


        -- BH

Hey, Scripting Guy! Answer

嘿,BH:在開始今天的專欄之前,我們要先宣佈一件事:Scripting Guy 投降了!你們已經擊中我們的要害了。

Scripting Guy 在星期一早上還對 2008 冬季指令碼比賽感到很自豪。不過,我們仗著在星期五 (「指令碼中心」在這一天的流量相對較低) 開始這個比賽的好主意,也仗著過去兩次指令碼比賽中累積的測試和評分經驗,就以為每樣東西都已準備就序,而且我們還以為可以處理完所有提交的指令碼。雖然一開始的那幾天,我們真的做到了。但在星期一傍晚/星期二上午我們就被打敗了。

我們認為我們已經沒辦法可以處理完所有提交的指令碼。你們贏了!

換句話說,提交大量指令碼到指令碼比賽的人,現在可能在想何時會看到張貼的成績。我們現在只能這樣說:總有一天會貼出來的。我們現在嘗試先評分項目 1、2、3 和 4,因為項目 1 和 2 目前已經正式結束了,而項目 3 和 4 會在星期五結束(我們也嘗試隨時更新死亡挑戰,看看每天的變化。唉,又是一個說得比做得容易的點子...) 這表示我們可能在一星期後,才會收到您提交的項目 9 和 10 指令碼。我們的建議?您可以每天進入死亡挑戰來為自己找點事情做。

嗯,您可能會說,這樣不就會增加需要測試的指令碼數量嗎?放心,沒關係!您就每天進來死亡挑戰看一下吧!要不然,就休息一個禮拜,然後把帳單寄給 Microsoft。

注意:雖然您可以將帳單寄給 Microsoft,但是請不要期待真的會有人支付那些帳單啊!

或者您可以在等待時,舒服地躺在「指令碼中心」的休息室。我們這裡跟大部分的休息室一樣,有提供 1947 年開始的國家地理雜誌,還有 Better Homes and Gardens 雜誌 (雜誌內所有的美味食譜都已經被撕掉了)。我們還有一份可識別出 Microsoft Excel 工作表中數字第一次出現的指令碼:


Set objDictionary = CreateObject("Scripting.Dictionary")

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

objExcel.Visible = True

Set objWorksheet = objWorkbook.Worksheets(1)

i = 1

Do Until objExcel.Cells(i, 1) = ""
intValue = objExcel.Cells(i, 1)

If Not objDictionary.Exists(intValue) Then
objDictionary.Add intValue, intValue 
objExcel.Cells(i, 2) = 1
End If

i = i + 1
Loop


BH,在電子郵件中您提到:「這應該是一個容易解決的問題」。是的,沒錯:這個問題的確很簡單。如您所看到的,我們一開始先建立 Scripting.Dictionary 物件的例項。我們將利用 Dictionary 物件來追蹤工作表中已經找到的數字。建立 Dictionary 物件之後,我們再建立 Excel.Application 物件的例項,然後使用 Open 方法和以下程式碼行來開啟試算表 C:\Scritps\Test.xls:


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


開啟檔案之後,我們設定 Visible 屬性為 True。這只是為了在畫面上顯示 Excel,讓我們知道發生什麼事。當然,下一件事就不會顯示在畫面上了。因為我們接著要利用這行程式碼來繫結 Test.xls 中的第一個工作表:


Set objWorksheet = objWorkbook.Worksheets(1)


什麼,您覺得太無聊了?聽著,就快到有趣的部分了。我們只要將名稱為 i 的計數器變數值設定為 1,然後就可以開始行動了。

什麼樣的行動呢?現在 BH 會有一張類似這樣的工作表:

Microsoft Excel

我們想要做的就是依序讀取欄 A 的所有數字,尋找每個數字第一次出現的位置。例如,數字 5 出現在儲存格 A1。它是欄 A 第一個出現的 5,這表示 BH 想要將儲存格 B1 的值設定為 1,表示這是第一次出現:

Microsoft Excel

當所有東西都完成時,BH 想要讓工作表看起來像這樣,每個數字第一次出現的位置都清楚地呈現在欄 B:

Microsoft Excel

我們要怎麼做到這點呢?針對初學者,我們先設定 Do Until 迴圈,這個迴圈會持續執行,直到在欄 A 遇到空白儲存格為止:


Do Until objExcel.Cells(i, 1) = ""
		 

注意:在這個指令碼中,我們假設試算表中沒有空白列。

在該 Do 迴圈之中,我們用這行程式碼來擷取儲存格 A1 的值 (也就是儲存格列 1、行 1,計數器變數 i 代表列數):


If Not objDictionary.Exists(intValue) Then
objDictionary.Add intValue, intValue 
objExcel.Cells(i, 2) = 1
End If


我們在這邊利用 Exists 方法來判斷這個值 (5) 是否「不」在 Dictionary 中。我們假設 Exists 方法傳回 True,表示 Dictionary 中找不到數值 5 這件事是真的。這樣就代表一件事:這是我們在工作表中遇到的第一個數值 5。請記住這點,我們要再執行兩個簡單工作:

  • 我們使用 Add 方法,將此值加入 Dictionary (把 5 當做 Dictionary 項目及 Dictionary 索引鍵)。

  • 我們將儲存格 B1 (列 i,欄 B) 的值設為 1。

從這個地方我們將 i 的值增加 1,然後返回迴圈的頂端,並重複處理列 2,欄 1。假設這個儲存格也是 5,那會發生什麼事?在這種情況下,值 5 將會存在於 Dictionary。因此,我們只要回到迴圈的頂端,再次嘗試欄 A 的下一個儲存格即可。

依此類推。

這樣真的可以得到結果嗎?喔,拜託!問這種問題未免太瞧不起我了!

也就是說...是的,這樣真的可以得到結果。

說到這兒,Scripting Guy 要去忙自己的工作啦!不過,別擔心我們:努力寄指令碼過來吧!因為在 2008 冬季指令碼比賽期間,Scripting Guy 每評分一個指令碼可以得到 $1,000 美元。

至少我們自己這樣想啦。我們的經理告訴我們盡量放手去做,先把所有指令碼評分完畢,然後「只要將帳單寄給 Microsoft 就可以了」。

顯示: