一個(gè)街道經(jīng)常管轄著許多社區(qū)，每個(gè)社區(qū)又包含多個(gè)小區(qū)，數(shù)據(jù)錄入時(shí)就需要輸入社區(qū)、小區(qū)名稱(chēng)。由于沒(méi)有準(zhǔn)備規(guī)范的數(shù)據(jù)名稱(chēng)，對(duì)于同一小區(qū)，不同統(tǒng)計(jì)員會(huì)錄入不同名稱(chēng)，比如下表中的“燕沙·后（東潤(rùn)楓景）”小區(qū)，有人記成“燕沙”，有人則記成“東潤(rùn)楓景”（圖1）。這樣數(shù)據(jù)給后期匯總、歸類(lèi)帶來(lái)極大的不便，現(xiàn)在我們可以借助Excel（本文以2016版為例）函數(shù)打造多級(jí)聯(lián)動(dòng)菜單，這樣用戶(hù)只需選擇性輸入即可，從而確保數(shù)據(jù)字段名稱(chēng)的統(tǒng)一性。
圖1 示例數(shù)據(jù)
從上面的數(shù)據(jù)可以看到，這里主要有三級(jí)地址，分別是“街道辦”、“社區(qū)”和“小區(qū)”，每個(gè)上級(jí)分別包含不同數(shù)目的下級(jí)，要實(shí)現(xiàn)數(shù)據(jù)選擇性的輸入，這里我們就要將不同級(jí)別的數(shù)據(jù)分別對(duì)應(yīng)。比如在選擇羅星街道香梨社區(qū)時(shí)，選擇的列表就是B列的內(nèi)容，效果和我們平常網(wǎng)購(gòu)時(shí)選擇地址類(lèi)似。
首先建立一級(jí)數(shù)據(jù)，這里的一級(jí)數(shù)據(jù)是街道辦名稱(chēng)。新建一個(gè)工作表，按提示在單元格F2及F3處輸入街道辦的名稱(chēng)，接著定位到A2單元格，點(diǎn)擊“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證”，在“允許”項(xiàng)選擇“序列”，在來(lái)源處選擇“=$F$2:$F$3”，將A2單元格下拉進(jìn)行填充（圖2）。
圖2 一級(jí)數(shù)據(jù)驗(yàn)證
這樣A列數(shù)據(jù)輸入只能從F2:F3單元格中進(jìn)行選擇，這是一級(jí)菜單的內(nèi)容。如果要添加其他內(nèi)容，只要在序列中增加內(nèi)容即可（圖3）。
圖3 數(shù)據(jù)驗(yàn)證后選擇性輸入一級(jí)菜單內(nèi)容
接下來(lái)對(duì)二級(jí)菜單進(jìn)行設(shè)置，這里的二級(jí)菜單對(duì)應(yīng)的是各個(gè)社區(qū)。因?yàn)槊總€(gè)街道辦管轄的是不同社區(qū)，這樣二級(jí)菜單就要和相應(yīng)的一級(jí)菜單對(duì)應(yīng)。二級(jí)菜單的設(shè)定可以使用INDIRECT函數(shù)進(jìn)行動(dòng)態(tài)引用。
定位到單元格G5和H5，分別輸入“羅星街道辦”和“角美街道辦”，為了方便引用，這里輸入的名稱(chēng)一定要和一級(jí)菜單名稱(chēng)一致。選中G2:H5區(qū)域，切換到菜單欄點(diǎn)擊“公式→名稱(chēng)管理器→根據(jù)所選內(nèi)容創(chuàng)建”，在彈出的窗口中勾選“首行”，分別創(chuàng)建名為“羅星街道辦”和“角美街道辦”的兩個(gè)新名稱(chēng)（圖4）。
圖4 創(chuàng)建名稱(chēng)
這里需要注意的是，因?yàn)槊總€(gè)一級(jí)菜單（街道辦）包含的下級(jí)菜單數(shù)目可能不同，比如上述例子中，羅星街道辦管轄社區(qū)是3個(gè)，另一個(gè)街道辦則只有2個(gè)，這樣我們還需要在名稱(chēng)管理器中進(jìn)行設(shè)置。打開(kāi)名稱(chēng)管理器，選中“角美街道辦”，將引用位置更改為“=Sheet2!$H$3:$H$4”，因?yàn)樗纳弦患?jí)角美街道辦只管轄兩個(gè)社區(qū)（圖5）。
圖5 編輯名稱(chēng)
定位到B2單元格，同上打開(kāi)數(shù)據(jù)驗(yàn)證設(shè)置，“允許”項(xiàng)選擇“序列”，在來(lái)源處輸入“=INDIRECT($A2)”，這里B2單元格的輸入使用INDIRECT函數(shù)進(jìn)行引用（圖6）。
圖6 INDIRECT函數(shù)設(shè)置
在INDIRECT函數(shù)中，這里“($A2)”表示的是對(duì)行的相對(duì)引用。表示在B2單元格的輸入是引用A2的內(nèi)容，這樣在A2（一級(jí)菜單）選擇不同的內(nèi)容時(shí)，B2的序列會(huì)顯現(xiàn)對(duì)應(yīng)的二級(jí)菜單的內(nèi)容，從而實(shí)現(xiàn)動(dòng)態(tài)引用，按提示下拉填充（圖7）。
圖7 動(dòng)態(tài)引用一級(jí)菜單
三級(jí)菜單設(shè)置類(lèi)似，先在I2:M2單元格依次輸入“香梨社區(qū)、角礫社區(qū)、黃雙社區(qū)、黃山社區(qū)、合和社區(qū)”，然后同上根據(jù)內(nèi)容創(chuàng)建名稱(chēng)，在數(shù)據(jù)驗(yàn)證中來(lái)源處輸入“=INDIRECT($B2)”，這樣C2單元格的輸入使用INDIRECT函數(shù)動(dòng)態(tài)引用B2的內(nèi)容進(jìn)行輸入?，F(xiàn)在我們?cè)贐2選擇不同社區(qū)，C2會(huì)同步顯示對(duì)應(yīng)社區(qū)下的小區(qū)名稱(chēng)（圖8）。
圖8 動(dòng)態(tài)引用二級(jí)菜單
以后在輸入統(tǒng)計(jì)表名稱(chēng)的時(shí)候，數(shù)據(jù)錄入只能在下拉列表中選擇預(yù)置好的標(biāo)準(zhǔn)數(shù)據(jù)，從而有效確保了數(shù)據(jù)的統(tǒng)一。為了表格的簡(jiǎn)潔，還可以選中F1:M18數(shù)據(jù)，右擊選擇“隱藏”將其隱藏，或者直接在另一個(gè)工作表中輸入預(yù)先準(zhǔn)備的數(shù)據(jù)，并將工作表設(shè)置為“只讀”、“隱藏”，這樣可以更方便數(shù)據(jù)錄入操作（圖9）。同理，四級(jí)、五級(jí)（甚至更多級(jí)）菜單的設(shè)置可依照上述方法進(jìn)行，對(duì)于需要?jiǎng)討B(tài)引用上一級(jí)菜單的輸入，只要先根據(jù)上一級(jí)菜單內(nèi)容建立對(duì)應(yīng)的名稱(chēng)，最后再使用INDIRECT進(jìn)行引用即可。
圖9 最終錄入界面
聯(lián)系客服
