Excel很強大。如果您經常使用它,您可能已經知道了許多使用公式或自動格式化的技巧,但是利用VBA中的單元格和範圍函數,您可以將Excel分析提升到一個全新的水平。
在VBA中使用Cells和Range函數的問題是,在高級階段,大多數人很難理解這些函數實際上是如何工作的。使用它們會讓人非常困惑。以下是你如何以你可能從未想象過的方式利用它們。
“單元格”和“範圍”函數允許您準確地告訴VBA腳本要在工作表上獲取或放置數據的位置。兩個單元格之間的主要區別是它們引用的內容。
單元格通常一次引用一個單元格,而Range一次引用一組單元格。此函數的格式為單元格(行、列)。
這將引用整個工作表中的每個單元格。這是Cells函數不引用單個單元格的一個示例:
Worksheets("Sheet1").Cells這引用了第一行左側的第三個單元格。單元格C1:
Worksheets("Sheet1").Cells(3)以下代碼引用單元格D15:
Worksheets("Sheet1").Cells(15,4)如果您願意,還可以使用“Cells(15,D)”引用單元格D15——您可以使用列字母。
使用列和單元格的數字引用單元格具有很大的靈活性,特別是使用腳本,這些腳本可以很快地循環通過大量單元格(並對它們執行計算)。我們將在下面更詳細地討論。
在許多方面,Range函數比使用單元格功能強大得多,因為它允許您同時引用單個單元格或特定範圍的單元格。您不會希望循環遍歷Range函數,因為單元格的引用不是數字(除非您將cells函數嵌入其中)。
此函數的格式為範圍(單元格#1,單元格ා2)。每個單元格可以用字母編號指定。
讓我們看幾個例子。
這裡,range函數引用單元格A5:
Worksheets("Sheet1").Range("A5")這裡,range函數引用A1到E20之間的所有單元格:
Worksheets("Sheet1").Range("A1:E20")如上所述,您不必使用數字字母單元格賦值。實際上,可以使用Range函數中的兩個Cells函數來標識工作表上的範圍,如下所示:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5))End With上面的代碼引用的範圍與range(“A1:E20”)函數所引用的範圍相同。使用它的價值在於,它允許您使用循環編寫動態處理範圍的代碼。
既然您瞭解瞭如何格式化單元格和範圍函數,那麼讓我們深入瞭解如何在VBA代碼中創造性地使用這些函數。
當您要在多個單元格區域中執行復雜公式時,Cells函數最有用。這些範圍也可以存在於多張圖紙中。
讓我們舉一個簡單的例子。假設你管理一個由11人組成的銷售團隊,每個月你都想看看他們的表現。
你可能會有一張表,記錄他們的銷售額和銷售量。
在表2中,您可以跟蹤公司客戶過去30天的反饋評級。
如果要使用兩張表中的值計算第一張表上的獎金,有多種方法可以做到這一點。您可以在第一個單元格中編寫一個公式,使用兩張工作表上的數據執行計算,然後將其向下拖動。那就行了。
另一種方法是創建VBA腳本,您可以在打開工作表時觸發運行該腳本,也可以通過工作表上的命令按鈕觸發運行該腳本,以便控制它的計算時間。您可以使用VBA腳本從外部文件中提取所有銷售數據。
那麼為什麼不在同一個腳本中觸發獎金列的計算呢?
如果以前從未在Excel中編寫過VBA,則需要啟用“開發人員”菜單項。為此,請轉到“文件”>;“選項”。單擊“自定義功能區”。最後,從左窗格中選擇Developer,將其添加到右窗格,並確保選中複選框。
現在,當您單擊OK並返回到主工作表時,您將看到Developer菜單選項。
可以使用“**”菜單**命令按鈕,也可以單擊“查看代碼”開始編碼。
在本例中,我們將使腳本在每次打開工作簿時運行。為此,只需單擊“開發人員”菜單中的“查看代碼”,然後將以下新函數粘貼到“代碼”窗口中。
Private Sub Workbook_Open() End Sub您的代碼窗口將如下所示。
現在您可以編寫代碼來處理計算了。使用單個循環,您可以單步遍歷所有11個僱員,並使用Cells函數拉入計算所需的三個變量。
記住Cells函數有行和列作為參數來標識每個單元格。我們將行設為“x”,使用一個數字來請求每列的數據。行數是僱員數,所以這是從1到11。列標識符為2表示銷售計數,3表示銷售量,2來自第2頁表示反饋分數。
最後的計算使用以下百分比加起來總獎金分數的100%。它是基於一個理想的銷售計數是50,銷售額為50000美元,反饋分數為10。
這種簡單的方法可以為銷售人員提供加權獎金。50分,5萬美元,10分,他們就可以得到當月的最高獎金。然而,任何因素下的完美都會減少獎金。任何比理想更好的東西都會增加獎金。
現在讓我們看看如何在一個非常簡單、簡短的VBA腳本中實現所有邏輯:
Private Sub Workbook_Open()For x = 2 To 12Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _Next xEnd Sub這就是這個腳本的輸出。
如果您想讓獎金列顯示實際的美元獎金而不是百分比,您可以將其乘以最大獎金金額。更好的方法是,將該數量放在另一個工作表的單元格中,並在代碼中引用它。這將使以後更容易更改值,而無需編輯代碼。
Cells函數的妙處在於,您可以構建一些非常有創意的邏輯,從多個不同表的多個單元格中提取數據,並用它們執行一些非常複雜的計算。
您可以使用cells函數對單元格執行各種操作,例如清除單元格、更改字體格式等等。
要進一步瞭解您可以做的一切,請查看Cells對象的microsoftsdn頁面。
一次循環通過多個細胞,細胞的功能是完美的。但是如果你想一次將某個東西應用到整個細胞範圍,那麼range函數的效率要高得多。
如果滿足某些條件,可以使用腳本格式化一系列單元格。
例如,假設所有銷售員工的銷售總量總數超過40萬美元,那麼您希望在“獎金”欄中以綠色突出顯示所有單元格,以表示團隊已獲得額外的團隊獎金。
讓我們看看如何使用IF語句來實現這一點。
Private Sub Workbook_Open()If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4End IfEnd Sub運行時,如果單元格超出團隊目標,則範圍內的所有單元格都將填充為綠色。
這只是使用Range函數可以對多組單元格執行的許多操作的一個簡單示例。您可以做的其他事情包括:
確保閱讀Range對象的microsoftsdn頁面以查看所有可能性。
既然您瞭解了單元格和Range函數之間的區別,現在就可以將VBA腳本提升到下一個級別了。Dann關於在Excel中使用計數和添加函數的文章將允許您構建更高級的腳本,這些腳本可以非常快速地在所有數據集中累積值。
如果您剛剛開始使用Excel中的VBA,請不要忘記我們也為您提供了一個極好的excelvba入門指南。
... 這將用相應的文字字串填充Range命令後面括號中的單元格。當然,您可以根據需要切換出標題,並根據需要展開列表。 ...
... 現在我們需要向下跳三行,在一個單元格中輸入單詞“Dataset”,在右邊的單元格中輸入佔位符編號。現在,它們只是佔位符,但很快它們就會成為我們下拉選單的基礎。 ...
...cel的瞭解越多,它的功能就越強大,因此學習新的方法和理解新的函式總是有益的。不要沉湎於你的榮譽!花點時間擴充套件你的知識。你會得到瘋狂的Excel公式,可以做驚人的事情。 ...
...本指南將透過一個簡單的專案幫助您嘗試VBA:一個將所選單元格的值從英鎊轉換為美元的按鈕。我們將向您介紹VBA和Excel的互動方式。這個簡短的教程將使您走上建立自己的更復雜的專案的道路。 ...
... [表\陣列]用於指定函式將在其中查詢查詢和返回值的單元格。選擇範圍時,請確保陣列中包含的第一列將包含查詢值! [col\u index\u num]是包含返回值的列的編號。 [range\u lookup]是可選引數,取1或0...
...每個商店的季度銷售額,並將這些總額寫入電子表格中的單元格(如果您不確定如何訪問VBA對話方塊,請檢視此處的VBA演練): ...
當您想在Excel中將兩個單元格合併為一個單元格時,有幾個選項。合併單元格是最好的方法之一。但它也有一些缺點。即便如此,您可能會發現它對於在Excel中處理文字很有用。 ...