?今天來上一個Excel大招，絕對的大招！——多級下拉菜單。
?多級下拉菜單這個問題，在Excel里面并不容易實現(xiàn)，關(guān)鍵問題在于數(shù)據(jù)有效性驗證環(huán)節(jié)需要對數(shù)據(jù)源的動態(tài)過濾。如果使用了它，那么Excel表格在某些方面上可能會產(chǎn)質(zhì)的飛躍，至少可以起到如下作用：
- 實現(xiàn)數(shù)據(jù)的全局參照驗證，確保數(shù)據(jù)關(guān)聯(lián)和一致。
- 人機(jī)動態(tài)交互效果更好。
- 輸入更加智能，減少了使用人的輸入量。
- 數(shù)據(jù)更加規(guī)整，為后期數(shù)據(jù)的分析提供了極大的便利。
?在此，以二級菜單為例，我們來看需要達(dá)到的效果。
?當(dāng)我們選擇了一級菜單后，根據(jù)選擇的一級菜單項目自動加載二級菜單內(nèi)容。在此，我們先不考慮使用VBA來實現(xiàn)這個問題。
?以下，我們就來看看一個二級下拉菜單是如何實現(xiàn)的：
?1、我們先建立兩個區(qū)域，一個“銷售區(qū)域”，一個“門店信息”，如下圖。然后選擇對應(yīng)的數(shù)據(jù)區(qū)域按“Ctrl+T”，將這幾個區(qū)域分別轉(zhuǎn)化成超級表。然后把銷售區(qū)這個數(shù)據(jù)表名稱改為“銷售區(qū)”，把門店這個數(shù)據(jù)表名稱改為“門店信息”以便在后面引用。這里我就不一步步的去演示了。最終效果如下，形成了兩個超級表區(qū)域。在此，我為了演示方便，全部超級表放在了一個工作表里面了，但在實際使用場景中是需要按工作表來做數(shù)據(jù)表的。
?2、現(xiàn)在我們來建立一級菜單。具體數(shù)據(jù)驗證操作步驟，就不啰嗦了。關(guān)鍵還是數(shù)據(jù)源的問題。這里，我們是引用銷售區(qū)這個超級表的區(qū)域。因為這里是引用超級表，涉及結(jié)構(gòu)化引用的問題，這里需要使用INDIRECT()這個函數(shù)。而里面的“銷售區(qū)”，引用的是“銷售區(qū)”這個超級表。這一步很簡單，并不復(fù)雜。如果銷售區(qū)這個表有很多列，那就需要使用超級表的結(jié)構(gòu)化引用，可以參照第三步的那種語法方式。
?3、同樣的方式在二級菜單列開始建立數(shù)據(jù)驗證。這里其它的都不是問題，比較關(guān)鍵的是序列的來源這里了。具體的函數(shù)用到了Offset、Match、和countif函數(shù)的嵌套。在這里，具體的公式為：
=OFFSET(INDIRECT("門店信息[[#標(biāo)題],[門店]]"),MATCH(H3,INDIRECT("門店信息[[#數(shù)據(jù)],[銷售區(qū)]]"),0),0,COUNTIF(INDIRECT("門店信息[[#數(shù)據(jù)],[銷售區(qū)]]"),H3)）
?大體意思就是使用offset函數(shù)來獲取區(qū)域，但這部分區(qū)域卻是有條件的，這個條件就是只獲取一級菜單選定的，對應(yīng)的內(nèi)容。
?這里，需要特別說明的是，如果沒有采用超級表時，那么indirect函數(shù)這部分，就需要使用區(qū)域了，不能再使用表結(jié)構(gòu)化引用。以上的數(shù)據(jù)源公式，如果用區(qū)域來表示，那么就是：
=OFFSET($D$2,MATCH(H3,$E$3:$E$7,0),0,COUNTIF(($E$3:$E$7),H3))
?對比以上兩種寫法，區(qū)域化引用看上去更簡潔，而結(jié)構(gòu)化引用似乎更要繁雜，可能大家會認(rèn)為為什么還用結(jié)構(gòu)化引用這種方式呢？答案是，這種結(jié)構(gòu)化引用方式具有更強(qiáng)的適應(yīng)性和擴(kuò)展性，不受區(qū)域引用這種絕對或者相對的單元格區(qū)域，“$E$3:$E$7”以及區(qū)域命名這種方式引用無法自動擴(kuò)展區(qū)域。關(guān)于超級表的結(jié)構(gòu)化引用，這個是題外話，在此就不展開說了。
?以上就是全部過程。無論幾級菜單都可以此類推來制作。
寫在最后：
?1、目前，縱觀全網(wǎng)，關(guān)于多級菜單的制作問題，都是使用以下這種列式表結(jié)構(gòu)引用的方式來制作的。這種數(shù)據(jù)管理方式存在很大的問題，因為按照這種方式來做數(shù)據(jù)源的話，隨著數(shù)據(jù)的增加，表會向橫向和縱向兩個方向擴(kuò)展，表格會極具的膨脹和混亂。用專業(yè)的來說就是有違“三范式”。如果用這種方式來管理數(shù)據(jù)，那么就是災(zāi)難。所以，一定要使用標(biāo)準(zhǔn)的關(guān)系數(shù)據(jù)，任何時候都絕對不推薦以下這種處理方法。
?2、在EXCEL里面，極力推薦使用超級表來管理數(shù)據(jù)。實際上，超級表的好處非常多，包括超強(qiáng)的可擴(kuò)展性，超高的智能化、自動化程度等等。只要使用習(xí)慣了，那么很難再切回去使用區(qū)域模式了。關(guān)于超級表的優(yōu)勢，在此先不展開說了。
?3、如果還有其它更好辦法，請不吝賜教！
創(chuàng)作不易，轉(zhuǎn)載請注明來源！
