數(shù)據(jù)有效性通過(guò)限制單元格的錄入內(nèi)容骤菠，從而提高數(shù)據(jù)錄入的準(zhǔn)確性，且能降低數(shù)據(jù)錄入的便捷性和數(shù)據(jù)校驗(yàn)工作疤孕。
在早些版本中娩怎，數(shù)據(jù)有效性在數(shù)據(jù)菜單下，叫數(shù)據(jù)有效性胰柑。而在Excel2016版在數(shù)據(jù)菜單下叫“數(shù)據(jù)驗(yàn)證”,本次演示使用的是Excel2016截亦，依然采用了“數(shù)據(jù)有效性”的叫法，雖然不夠規(guī)范柬讨，但方便大家理解崩瓤。
如果有條件，建議大家使用最新的版本踩官，其功能相對(duì)更加齊全却桶，相對(duì)于低版本能更加方便的操作我們需要的內(nèi)容，不僅僅是本次教程所用蔗牡，而在于整體會(huì)對(duì)你的學(xué)習(xí)狀態(tài)會(huì)有一個(gè)明顯的提升颖系。
設(shè)置二級(jí)、三級(jí)聯(lián)動(dòng)菜單（二）
聯(lián)動(dòng)菜單也分為兩個(gè)版本：普通版和進(jìn)階版
普通版：
也就是我們網(wǎng)絡(luò)上常見的一些做聯(lián)動(dòng)菜單的方法辩越，其菜單和子項(xiàng)都比較少嘁扼，可以手工對(duì)源數(shù)據(jù)進(jìn)行分組歸類也可也使用其他方法。本篇文章介紹的就是普通版黔攒。
進(jìn)階版：
進(jìn)階版將對(duì)3000多條數(shù)據(jù)進(jìn)行分組歸類趁啸，并做演示，由于數(shù)量級(jí)比較大督惰，部分方法和需要注意的問(wèn)題與普通版不太一樣不傅，適合對(duì)該問(wèn)題有深度需求的用戶。
本次為了更加真實(shí)的展示二級(jí)赏胚、三級(jí)聯(lián)動(dòng)菜單的效果访娶，從網(wǎng)上下載了省級(jí)行政劃分結(jié)構(gòu)數(shù)據(jù)，共計(jì)3130條數(shù)據(jù)觉阅，如果3000條數(shù)據(jù)我們都能搞定崖疤，那么再多的分類再多的子項(xiàng)我們都不要發(fā)愁了。其四級(jí)留拾、五級(jí)乃至更多的聯(lián)動(dòng)戳晌，方法都是一樣的。下載演示文件-簡(jiǎn)書私信回復(fù)3獲取下載密碼
調(diào)整數(shù)據(jù)分組（選學(xué)）
該章節(jié)為拓展學(xué)習(xí)痴柔，如果你的聯(lián)動(dòng)菜單和子項(xiàng)比較少沦偎，可以跳過(guò)該章節(jié)，直接進(jìn)入主題部分。點(diǎn)擊查看詳細(xì)操作步驟豪嚎，提供了兩種分組的方法搔驼，一個(gè)是我自己總結(jié)的方法，通過(guò)數(shù)據(jù)透視加上一些操作實(shí)現(xiàn)的侈询，另一種是通過(guò)Power Query實(shí)現(xiàn)的舌涨，該方法參考了施陽(yáng)的博客。
我們拿到的是左側(cè)的數(shù)據(jù)扔字，但是必須轉(zhuǎn)換為右側(cè)的分組才能進(jìn)行區(qū)域名稱和位置的定義囊嘉，所以我們需要對(duì)數(shù)據(jù)進(jìn)行轉(zhuǎn)換。對(duì)于普通版知道兩個(gè)方法即可革为。1扭粱、手工調(diào)解，如果數(shù)據(jù)不多可以手工進(jìn)行設(shè)置震檩；2琢蛤、通過(guò)大鵬老師教你的利用數(shù)據(jù)透視進(jìn)行轉(zhuǎn)換的方法；其他還有通過(guò)數(shù)組公式和Power Query兩種抛虏，甚至VBA博其，但是對(duì)于普通用戶而言可能稍難，這些方法會(huì)在進(jìn)階篇進(jìn)行講解迂猴。
也就是說(shuō)我們需要把省份歸類到一起慕淡，同時(shí)在把每個(gè)省份作為標(biāo)題，將對(duì)應(yīng)的城市放在對(duì)應(yīng)省份的下面错忱，最后再把所有的城市作為標(biāo)題儡率，將對(duì)應(yīng)的區(qū)縣放在對(duì)應(yīng)的城市下面，如果有更多級(jí)以清，依此類推，并分別對(duì)名稱和區(qū)域進(jìn)行定義后才能使用崎逃。
下面是效果的參考標(biāo)準(zhǔn)掷倔。每一個(gè)一級(jí)的子項(xiàng)都是二級(jí)的標(biāo)題、二級(jí)的子項(xiàng)是三級(jí)的標(biāo)題个绍。這里我們需要注意一個(gè)問(wèn)題勒葱，就是作為標(biāo)題的名稱，不能有重復(fù)巴柿，無(wú)論是哪一級(jí)凛虽，否者你無(wú)法對(duì)名稱進(jìn)行合理的指定。在最末級(jí)中广恢，其不同仔項(xiàng)之間是可以重復(fù)的凯旋。
由于我拿到的源數(shù)據(jù)存在重復(fù)的問(wèn)題，所以我對(duì)名稱少做了調(diào)整。比如北京市和天津市的二級(jí)分類都有叫“市轄區(qū)”至非，如果我把市轄區(qū)作為標(biāo)題在對(duì)對(duì)應(yīng)的區(qū)縣進(jìn)行歸類時(shí)钠署，標(biāo)題就會(huì)重復(fù)。我實(shí)際已經(jīng)對(duì)源數(shù)據(jù)做了調(diào)整荒椭，用以避免掉這個(gè)問(wèn)題谐鼎。
省份分組設(shè)置
將省份列復(fù)制出來(lái)后去重即可得到省份的分組。
城市分組設(shè)置
-
該步驟稍微麻煩一些趣惠，可以通過(guò)數(shù)據(jù)透視再加上一些步驟可以得到我們想要的格式狸棍。首先我們需要透視該源數(shù)據(jù)，可以指定位置到另一個(gè)新的工作表當(dāng)中味悄。
數(shù)據(jù)透視.png -
將省份放入列隔缀、市放入行、將市放入值并計(jì)數(shù)傍菇。
調(diào)整字段位置.png - 點(diǎn)擊設(shè)計(jì)菜單-選擇總計(jì)類型為-對(duì)行和列禁用猾瘸，請(qǐng)注意，此時(shí)只有把活動(dòng)單元格選擇為數(shù)據(jù)透視表中的位置丢习，才會(huì)看到設(shè)計(jì)菜單牵触。
取消總計(jì).png -
將數(shù)據(jù)區(qū)域復(fù)制出來(lái)，找一個(gè)表格粘貼為值咐低，并選中灰色區(qū)域揽思。
復(fù)制粘貼為值.png - 使用快捷鍵Ctrl+G選擇定位條件或者開始菜單-查找和選擇-定位條件，打開定位對(duì)話框见擦，選擇常量并確定钉汗。
圖片.png
圖片.png - 直接輸入公式“=$a2”，在公式編輯狀態(tài)下按鲤屡，Ctrl+Enter(回車)批量填充损痰，即可將省份下面填充對(duì)應(yīng)的城市。
圖片.png
圖片.png -
全選數(shù)據(jù)酒来，原地粘貼為值卢未，并刪除A列
圖片.png - 再次條件定位，選擇空值并確定堰汉，將選中的部分刪除且下方單元格上移辽社，并將處理的結(jié)果粘貼到對(duì)應(yīng)的區(qū)域。
圖片.png
圖片.png
圖片.png
圖片.png
圖片.png
區(qū)縣分組設(shè)置
方法等同于對(duì)城市分組的設(shè)置翘鸭，唯一不同的是滴铅，在數(shù)據(jù)透視時(shí)，將市放入列就乓，縣放入行汉匙，對(duì)縣計(jì)數(shù)拱烁，其他步驟均一樣。
定義名稱及區(qū)域
- 定義名稱及范圍我們可以使用公式菜單下的-定義的名稱組中的名稱管理器盹兢，但使用名稱管理器進(jìn)行定義邻梆，只能手工一個(gè)一個(gè)加，無(wú)法做到批量完成绎秒。所以該方法我不再做詳細(xì)的講解浦妄，僅做了解即可，而名稱管理器的功能不限于此见芹，還有其他用法剂娄，這里不再累贅。
我們使用以下兩種方式：
1玄呛、快捷鍵Ctrl+Shift+F3
2阅懦、公式菜單-定義的名稱-根據(jù)所選內(nèi)容創(chuàng)建
兩種方法其效果是等價(jià)的，都可以打開下面的窗口
-
選中數(shù)據(jù)區(qū)域-并指定首行
圖片.png -
在定義城市部分的時(shí)候徘铝，為了增加效率和準(zhǔn)確性耳胎，我們使用定位功能，定位常量惕它，只選中我們需要定義的范圍怕午。
圖片.png
圖片.png -
對(duì)縣級(jí)的設(shè)置也是一樣。
圖片.png -
但是有時(shí)候會(huì)報(bào)錯(cuò)淹魄，原因參見點(diǎn)擊查看詳細(xì)原因
圖片.png
以上我們操作完了對(duì)數(shù)據(jù)區(qū)域名稱的指定和區(qū)域的指定郁惜。在公式菜單-名稱管理器，可以點(diǎn)擊查看我們剛剛定義完的名稱和區(qū)域甲锡，若后續(xù)有新增的項(xiàng)目兆蕉，則需要在名稱管理器中對(duì)應(yīng)的分類下，修改應(yīng)用的范圍缤沦。
開始設(shè)置聯(lián)動(dòng)菜單
- 先設(shè)置省份-打開數(shù)據(jù)菜單-數(shù)據(jù)有效性/數(shù)據(jù)驗(yàn)證-選擇序列-在數(shù)據(jù)來(lái)源數(shù)據(jù)“=省級(jí)名稱”指定的名稱一定要和我們分組時(shí)指定的名稱一致虎韵，否者無(wú)法返回正確的結(jié)果。確定以后點(diǎn)擊單元格右側(cè)的按鈕就可以選擇我們需要的省份了疚俱。所以建議在設(shè)置城市的時(shí)候劝术，先隨便選擇一個(gè)省份，在設(shè)置區(qū)縣的時(shí)候也需要隨便先選擇一個(gè)城市呆奕。
圖片.png
圖片.png -
我們?cè)賮?lái)設(shè)置城市，選中城市下面的單元格-同樣打開數(shù)據(jù)有效性衬吆，輸入下面的內(nèi)容梁钾。
圖片.png
圖片.png - 這一步設(shè)置有時(shí)候會(huì)報(bào)錯(cuò)，原因就是你在設(shè)置城市的時(shí)候逊抡，省份沒(méi)有選擇任何值姆泻，但是你直接點(diǎn)擊是以后零酪，你再回去先選好省份，那么城市這部分也就正常了拇勃。建議先隨便選擇一個(gè)上級(jí)的內(nèi)容在對(duì)下一級(jí)進(jìn)行操作四苇。
圖片.png -
區(qū)縣的設(shè)置等同于城市。
圖片.png
至此就設(shè)置完畢了方咆。
實(shí)現(xiàn)原理
其實(shí)現(xiàn)原理需要你對(duì)名稱管理器和INDIRECT函數(shù)熟悉月腋，不做太多展開。簡(jiǎn)單來(lái)說(shuō)說(shuō)瓣赂，INDIRECT函數(shù)可以根據(jù)你提供的單元格地址榆骚，比如INDIRECT("A2")，則結(jié)果會(huì)返回A2單元格的內(nèi)容煌集，表面上看和直接“=A2”是一樣的效果妓肢，但其實(shí)現(xiàn)的原理有差別，而且INDIRECT的功能不僅如此苫纤，后續(xù)有需要再給大家詳細(xì)講解碉钠。而關(guān)于我們?cè)趯懯∫患?jí)的時(shí)候，選擇序列后直接寫了=省級(jí)名稱,因?yàn)槊Q管理器定義過(guò)的我們都可以直接拿來(lái)使用卷拘，包括我們可以定義一些常用的公式或者特定的內(nèi)容喊废，也不再展開。我們?cè)诙x二級(jí)恭金、三級(jí)的時(shí)候使用了INDIRECT(J12)操禀，其返回的結(jié)果是根據(jù)我們選擇的省份，返回不同省份對(duì)應(yīng)的城市横腿，而這些關(guān)系我們?cè)诿Q管理器中已經(jīng)定義過(guò)了颓屑。如果你沒(méi)有理解也沒(méi)有關(guān)系，只要能做出來(lái)實(shí)現(xiàn)我們的目的暫時(shí)就足夠了耿焊，以后隨著學(xué)習(xí)的不斷深入揪惦，你會(huì)慢慢理解的。
總結(jié)：
個(gè)人覺得罗侯，聯(lián)動(dòng)菜單的設(shè)置有幾個(gè)關(guān)鍵點(diǎn)需要注意器腋。
1、分組一定要明確钩杰，作為標(biāo)題的名稱不能有重復(fù)的纫塌；
2、如果你的菜單和子項(xiàng)比較多讲弄，那么需要借助一些方法去實(shí)現(xiàn)措左；
3、在我們指定名稱的時(shí)候有時(shí)候會(huì)報(bào)錯(cuò)避除，（請(qǐng)確保粘貼和復(fù)制區(qū)域不要重疊）怎披，原因是由于Excel本身引起的胸嘁，我們也可以通過(guò)一些方法避免掉。
4凉逛、在制作聯(lián)動(dòng)菜單時(shí)性宏，有時(shí)候會(huì)提示源錯(cuò)誤，那是因?yàn)槟阒付ǖ纳霞?jí)的單元格沒(méi)有內(nèi)容状飞，建議先隨便選擇一個(gè)內(nèi)容后再做后面的操作毫胜。
5、學(xué)會(huì)聯(lián)動(dòng)菜單很重要昔瞧，根據(jù)自己的能力選擇合適自己的教程效果最好指蚁。
