辦公小技巧：拒絕出錯製作Excel多級聯動下拉列表

2021-05-19 19:30:37

一個街道經常管轄著許多社群，每個社群又包含多個小區，資料錄入時就需要輸入社群、小區名稱。由於沒有準備規範的資料名稱，對於同一小區，不同統計員會錄入不同名稱，比如下表中的「燕沙·後（東潤楓景）」小區，有人記成「燕沙」，有人則記成「東潤楓景」（圖1）。這樣資料給後期彙總、歸類帶來極大的不便，現在我們可以藉助Excel（本文以2016版為例）函數打造多級聯動選單，這樣使用者只需選擇性輸入即可，從而確保資料欄位名稱的統一性。

圖1 示例資料

從上面的資料可以看到，這裡主要有三級地址，分別是「街道辦」、「社群」和「小區」，每個上級分別包含不同數目的下級，要實現資料選擇性的輸入，這裡我們就要將不同級別的資料分別對應。比如在選擇羅星街道香梨社群時，選擇的列表就是B列的內容，效果和我們平常網購時選擇地址類似。

首先建立一級資料，這裡的一級資料是街道辦名稱。新建一個工作表，按提示在單元格F2及F3處輸入街道辦的名稱，接著定位到A2單元格，點選「資料→資料驗證」，在「允許」項選擇「序列」，在來源處選擇「=$F$2:$F$3」，將A2單元格下拉進行填充（圖2）。

圖2 一級資料驗證

這樣A列資料輸入只能從F2:F3單元格中進行選擇，這是一級選單的內容。如果要新增其他內容，只要在序列中增加內容即可（圖3）。

圖3 資料驗證後選擇性輸入一級選單內容

接下來對二級選單進行設定，這裡的二級選單對應的是各個社群。因為每個街道辦管轄的是不同社群，這樣二級選單就要和相應的一級選單對應。二級選單的設定可以使用INDIRECT函數進行動態引用。

定位到單元格G5和H5，分別輸入「羅星街道辦」和「角美街道辦」，為了方便引用，這裡輸入的名稱一定要和一級選單名稱一致。選中G2:H5區域，切換到選單欄點選「公式→名稱管理器→根據所選內容創建」，在彈出的視窗中勾選「首行」，分別創建名為「羅星街道辦」和「角美街道辦」的兩個新名稱（圖4）。

圖4 創建名稱

這裡需要注意的是，因為每個一級選單（街道辦）包含的下級選單數目可能不同，比如上述例子中，羅星街道辦管轄社群是3個，另一個街道辦則只有2個，這樣我們還需要在名稱管理器中進行設定。開啟名稱管理器，選中「角美街道辦」，將引用位置更改為「=Sheet2!$H$3:$H$4」，因為它的上一級角美街道辦只管轄兩個社群（圖5）。

圖5 編輯名稱

定位到B2單元格，同上開啟資料驗證設定，「允許」項選擇「序列」，在來源處輸入「=INDIRECT($A2)」，這裡B2單元格的輸入使用INDIRECT函數進行引用（圖6）。

圖6 INDIRECT函數設定

在INDIRECT函數中，這裡「($A2)」表示的是對行的相對引用。表示在B2單元格的輸入是引用A2的內容，這樣在A2（一級選單）選擇不同的內容時，B2的序列會顯現對應的二級選單的內容，從而實現動態引用，按提示下拉填充（圖7）。

圖7 動態引用一級選單

三級選單設定類似，先在I2:M2單元格依次輸入「香梨社群、角礫社群、黃雙社群、黃山社群、合和社群」，然後同上根據內容創建名稱，在資料驗證中來源處輸入「=INDIRECT($B2)」，這樣C2單元格的輸入使用INDIRECT函數動態引用B2的內容進行輸入。現在我們在B2選擇不同社群，C2會同步顯示對應社群下的小區名稱（圖8）。

圖8 動態引用二級選單

以後在輸入統計表名稱的時候，資料錄入只能在下拉列表中選擇預置好的標準資料，從而有效確保了資料的統一。為了表格的簡潔，還可以選中F1:M18資料，右擊選擇「隱藏」將其隱藏，或者直接在另一個工作表中輸入預先準備的資料，並將工作表設定為「只讀」、「隱藏」，這樣可以更方便資料錄入操作（圖9）。同理，四級、五級（甚至更多級）選單的設定可依照上述方法進行，對於需要動態引用上一級選單的輸入，只要先根據上一級選單內容建立對應的名稱，最後再使用INDIRECT進行引用即可。

圖9 最終錄入介面

辦公小技巧：拒絕出錯 製作Excel多級聯動下拉列表

熱門文章

辦公小技巧：拒絕出錯製作Excel多級聯動下拉列表