在Excel中使用動態範圍名稱進行靈活的下拉菜單

Excel電子表格通常包含單元格下拉列表,以簡化和/或標準化數據輸入。這些下拉菜單是使用數據驗證功能創建的,以指定允許條目的列表。

要設置簡單的下拉列表,請選擇要在其中輸入數據的單元格,然後單擊 資料驗證 (在標籤上 數據 ),選擇數據驗證,然後選擇 清單 (在“允許:”下),然後在字段中輸入列表項(用逗號分隔) 資源 :(請參見圖1)。

在這種類型的基本下拉菜單中,在數據驗證本身中指定了允許條目的列表;因此,要更改列表,用戶必須打開並編輯數據驗證。但是,這對於沒有經驗的用戶或在選項列表很長的情況下可能很困難。

另一個選擇是將列表放在電子表格中的命名範圍內,然後在字段中指定該範圍名稱(以等號開頭) 資源 :來自數據驗證(如圖2所示)。

第二種方法使編輯列表中的選項變得容易,但是添加或刪除項目可能會出現問題。由於命名範圍(在我們的示例中為FruitChoices)是指固定範圍的單元格($ H $ 3:$ H $ 10,如圖所示),因此,如果將更多選項添加到單元格H11或更低的單元格中,它們將不會出現在下拉菜單(因為這些單元格不在FruitChoices範圍內)。

同樣,例如,如果清除了“ Pears”和“ Strawberry”條目,它們將不再出現在下拉列表中,但是該下拉列表將包含兩個“空”選項,因為該下拉列表仍指的是水果選擇,包括空單元格H9和H10。

由於這些原因,當使用常規命名範圍作為下拉菜單的列表源時,如果將條目添加到列表中或從列表中刪除,則必須編輯命名範圍本身以包含更多或更少的單元格。

解決此問題的一種方法是使用範圍名稱 動態 作為下拉選項的來源。動態範圍名稱是一種在添加或刪除條目時自動擴展(或折疊)以完全匹配數據塊大小的名稱。為此,請使用 ,而不是固定範圍的單元格地址,以定義命名範圍。

如何在Excel中設置動態範圍

常規(靜態)範圍名稱是指特定範圍的單元格(在我們的示例中,$ H $ 3:$ H $ 10,請參見下文):

但是動態範圍是由公式定義的(請參閱以下內容,取自使用動態範圍名稱的單獨電子表格):

在開始之前,請確保下載我們的示例Excel文件(分類宏已被禁用)。

讓我們詳細研究這個公式。水果的選項位於標題正下方的單元格中( 水果 )。該標題也被分配了一個名稱: 水果標題

用於定義水果選項動態範圍的完整公式為:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADERO, NDICE (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1) 

水果標題se 指在列表中第一個條目上方一行的標題。數字20(在公式中使用兩次)是列表的最大大小(行數)(可以根據需要進行調整)。

請注意,在此示例中,列表中只有8個條目,但是在它們下方也有空單元格,可以在其中添加其他條目。數字20是指可以進行輸入的整個塊,而不是實際的輸入數。

現在,我們將分解公式(每個顏色代碼),以了解其工作原理:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADERO, NDICE (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1) 

“最內在”的部分是 偏移(FruitsHeading,1、0、20、1) 。這是指20個單元格(在FruitsHeading單元格下面),可以在其中輸入選項。這個SCROLL函數主要說:在 水果標題,向下滾動1行並超過0列,然後選擇20行長和1列寬的區域。這樣就給了我們20排的塊,其中輸入了Fruit選項。

公式的下一部分是函數 伊斯蘭克

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (el anterior), 0, 0), 0) -1, 20), 1) 

此處,“補償”功能(如上所述)已由“以上”代替(以提高可讀性)。但是,ISBLANK函數在由OFFSET函數定義的20行單元格範圍內運行。

然後,ISBLANK創建一組20個TRUE和FALSE值,以指示OFFSET函數引用的20行範圍內的每個單元格是否為空白(空)。在此示例中,集合中的前8個值將為FALSE,因為前8個單元格不為空,而後12個值將為TRUE。

公式的下一部分是INDEX函數:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VERDADERO, NDICE (el anterior, 0, 0), 0) -1, 20), 1) 

同樣,“以上”是指上述的ISBLANK和OFFSET功能。 INDEX函數返回一個數組,其中包含由ISBLANK函數創建的20個TRUE / FALSE值。

通常情況下 指數 它用於從數據塊中選擇某個值(或值的範圍),並指定某個行和列(在該塊內)。但是通過將行和列條目設置為零(如此處所做的那樣),INDEX將返回包含整個數據塊的數組。

公式的下一部分是MATCH函數:

 = OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (VERDADERO, el anterior, 0) -1, 20), 1) 

功能 比賽 返回第一個TRUE值的位置,該值在INDEX函數返回的數組內。由於列表中的前8個條目都不為空,因此數組中的前8個值將為FALSE,第九個值將為TRUE(因為範圍中的第9行為空)。

因此,MATCH函數將返回 9 。但是,在這種情況下,我們真的想知道列表中有多少個條目,因此公式從MATCH值中減去1(給出最後一個條目的位置)。因此最終MATCH(TRUE,前一個,0)-1返回值 8

公式的下一部分是IFERROR函數:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (el anterior, 20), 1) 

如果第一個指定的值產生錯誤,則IFERROR函數將返回備用值。之所以包含此函數,是因為如果整個單元格塊(所有20行)都充滿條目,則MATCH函數將返回錯誤。

這是因為我們告訴MATCH函數尋找第一個TRUE值(在ISBLANK函數的value數組中),但是如果沒有一個單元格為空,則整個數組將填充FALSE值。如果MATCH在您要查找的數組中找不到目標值(TRUE),則會返回錯誤。

因此,如果整個列表完整(因此MATCH返回錯誤),則IFERROR函數將返回值20(知道列表中必須有20個條目)。

最後, 偏移(FruitsHeading,1、0,上一個,1) 返回我們真正尋找的範圍:從FruitsHeading單元格開始,向下移1行並超過0列,然後選擇一個包含多行的區域。列表中有條目(寬1列)。因此,所有公式一起返回僅包含實際條目的範圍(直到第一個空單元格)。

使用此公式定義作為下拉列表來源的範圍意味著您可以自由編輯列表(添加或刪除條目,前提是其餘條目從頂部單元格開始並且是連續的),並且下拉列表始終反映該列表(請參見圖6)。

這裡包含了示例文件(動態列表),可以從該網站下載。但是,宏不起作用,因為WordPress不喜歡帶有宏的Excel工作簿。

作為指定列表塊中行數的替代方法,可以為列表塊分配自己的範圍名稱,然後可以在修改的公式中使用該範圍名稱。在示例文件中,第二個列表(名稱)使用此方法。在此,為整個列表塊(在標題“ NAMES”下,示例文件中的40行)分配了範圍名稱 名稱塊 。定義名稱列表的替代公式如下:

 = OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1) 

哪裡 名稱塊 替換OFFSET(FruitsHeading,1、0、20、1),然後 行(NamesBlock) 替換上面公式中的20(行數)。

因此,對於可輕鬆編輯的下拉列表(即使其他用戶可能沒有經驗),請嘗試使用動態範圍名稱。並請注意,儘管本文著重於下拉列表,但動態範圍名稱可在需要引用大小可能有所不同的範圍或列表的任何地方使用。請享用!