在Excel中使用下拉列表的功能，能幫助我們限制填寫的內(nèi)容，保證數(shù)據(jù)的有效無誤，常規(guī)的數(shù)據(jù)有效性（下拉列表）很容易掌握，可是如果要制作更多級別的數(shù)據(jù)有效性，就有點困難了。小編之前分享過數(shù)據(jù)有效性和二級聯(lián)動下拉菜單的制作方法，點擊文字可以查看。
Excel中怎樣實現(xiàn)二級聯(lián)動下拉菜單？
關(guān)于數(shù)據(jù)有效性的幾個鮮為人知的技巧
今天給大家分享多級別的下拉列表的制作方法。
數(shù)據(jù)源按下面的順序排序：
圖 1
在制作多級下拉菜單之前我們先來了解這幾個函數(shù)，其語法和功能分別是：
Match（找什么，在哪里找，0），返回符合特定值特定順序的查詢值在數(shù)組中的相對位置；
Countif（條件范圍，條件），計算區(qū)域中滿足給定條件的單元格的個數(shù)；
Vlookup（找什么，在哪里找，顯示序列，匹配參數(shù)），搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行序號，再進一步返回選定單元格的值；
Offset（參考單元格，偏移的行數(shù)，偏移的列數(shù)，返回引用區(qū)域的行數(shù)，返回引用區(qū)域的列數(shù)），以指定的引用為參照系，通過給定偏移量返回新的應(yīng)用。
了解了函數(shù)的功能，接下來按以下步驟操作：
Step1:在C2單元格借助于Match函數(shù)，計算“廣東省”在A列中的位置，因此該公式為：=MATCH(B2,A:A,0)。隨后將該公式分別復(fù)制至C3、C6、C7、C8、C9單元格即可計算對應(yīng)的項在A列中的起始位置，該數(shù)值用于指導(dǎo)offset函數(shù)往下偏移幾行；
Step2:接下來要計算每個項目共有幾個小項，在D2中利用countif函數(shù)計算個數(shù)，此處的公式為：=COUNTIF(A:A,B2)。該數(shù)值可以用在offset函數(shù)中的返回行數(shù)中；
Step3:最后在G列設(shè)置一級省份下拉列表，如圖2：
圖 2
Step4:對二級“地市”設(shè)置數(shù)據(jù)有效性。因為我們需要根據(jù)一級G2單元格選擇的不同，設(shè)置不一樣的下拉列表，而每個一級“省份”會有不一樣個數(shù)的二級“地市”，所以借助offset函數(shù)來完成。在H2單元格設(shè)置數(shù)據(jù)有效性的“來源”位置，輸入以下公式：=OFFSET($B$1,VLOOKUP(G2,$B:$D,2,0)-1,0,VLOOKUP(G2,$B:$D,3,0),1)。
圖 3
該公式的意思為：
以B1單元格為基準(zhǔn)，往下偏移幾行，往右不偏移列，返回引用區(qū)域的行數(shù)，返回一列的數(shù)據(jù)。那么往下偏移幾行，要根據(jù)前面的G2單元格的內(nèi)容變化，所以利用vlookup函數(shù)來查找G2單元格的內(nèi)容，位于B:D范圍中第二列的結(jié)果，我們便可以從B1單元格往下偏移6行至B7單元格，再減去1，得到“廣州市”的B6單元格；同樣的，返回引用區(qū)域的行數(shù)，也借助vlookup函數(shù)來得到，如此一來，二級下來列表的“地市”也就完成了。
圖4
Step5: 接下來，我們就用同樣的offset函數(shù)來制作三級下拉列表，因此在I2單元格的數(shù)據(jù)有效性的公式為：=OFFSET($B$1,VLOOKUP($H$2,$B:$D,2,0)-1,0,VLOOKUP($H$2,$B:$D,3,0))
圖 5
最后的效果為：
圖 6
那么有了這種方法以后，我們想設(shè)置任意級別的下拉列表都可以實現(xiàn)了。
今天的分享到此結(jié)束，如果想看更多歷史文章，請從菜單所有文章查看。
聯(lián)系客服
