用Excel進(jìn)行數(shù)據(jù)收集，做好一個(gè)數(shù)據(jù)錄入文件給用戶使用，結(jié)果收回來(lái)的數(shù)據(jù)讓你令人抓狂：

• 手機(jī)號(hào)不是11位
• 面積單位有人寫平方米，有人寫m2
• 張冠李戴，明明寫姓名的地方，寫了性別
• 說(shuō)好寫保留一位小數(shù)，卻提交了整數(shù)
• 說(shuō)好50字以內(nèi)，卻寫了500字……

對(duì)于以上這些問(wèn)題，如果你是用Excel收集數(shù)據(jù)的話，應(yīng)該用Excel里的【數(shù)據(jù)有效性】（2013以后叫【數(shù)據(jù)驗(yàn)證】），提前針對(duì)指定單元格進(jìn)行限制，讓使用者按你的要求去錄入正確的數(shù)據(jù)，這樣收集效果會(huì)好一些。

但還有復(fù)雜一些的情況，可能需要逐級(jí)限制，例如你在一些網(wǎng)頁(yè)，看到可以先選第一級(jí)菜單，然后第二級(jí)的內(nèi)容是僅對(duì)應(yīng)第一級(jí)出現(xiàn)的選項(xiàng)，這樣才會(huì)能滿足更復(fù)雜的錄入需求~

所以只會(huì)做簡(jiǎn)單的數(shù)據(jù)有效性還不夠，還要懂制作多級(jí)聯(lián)動(dòng)下拉菜單了：

可惜的是，Excel并沒(méi)有自帶這樣一種功能，還是只能建立在現(xiàn)有的【數(shù)據(jù)驗(yàn)證】功能基礎(chǔ)上去想辦法。

一級(jí)菜單好辦，直接設(shè)置對(duì)應(yīng)的序列數(shù)據(jù)源就行了：

那二級(jí)、三級(jí)的數(shù)據(jù)驗(yàn)證的數(shù)據(jù)源怎么解決呢？

二級(jí)、三級(jí)的下拉列表是要根據(jù)一級(jí)的內(nèi)容變化而變化的，也就是說(shuō)二三級(jí)的數(shù)據(jù)驗(yàn)證數(shù)據(jù)源必須是一個(gè)動(dòng)態(tài)變化的區(qū)域。

這里就有兩種解決辦法：

1、indirect引用名稱法

2、offset動(dòng)態(tài)區(qū)域法

以下介紹方法1的操作：

Step1：給二級(jí)的內(nèi)容定義名稱，名稱為它對(duì)應(yīng)的一級(jí)內(nèi)容。如給廣州市定義名稱為廣東省，給南京市定義名稱為江蘇省。

Step2：設(shè)置數(shù)據(jù)驗(yàn)證，選擇用indirect函數(shù)動(dòng)態(tài)引用一級(jí)單元格內(nèi)容，即實(shí)現(xiàn)當(dāng)一級(jí)為廣東省時(shí)，二級(jí)的菜單數(shù)據(jù)源就是indirect(“廣東省”)，當(dāng)一級(jí)是江蘇省時(shí)，二級(jí)的下拉數(shù)據(jù)源就是indirect(“江蘇省”)。我們?cè)谏弦徊揭讯x好名稱，名稱“廣東省”=“廣州市”，“江蘇省”=“南京市”，通過(guò)此法實(shí)現(xiàn)了二級(jí)的下拉菜單動(dòng)態(tài)變化，是依賴一級(jí)的選擇結(jié)果變化而變化。

Step3：做好二級(jí)菜單后，三級(jí)的問(wèn)題也是同理可得。先給三級(jí)的內(nèi)容設(shè)置名稱，名稱是對(duì)應(yīng)的二級(jí)，然后也是通過(guò)indirect函數(shù)引用二級(jí)內(nèi)容的單元格，實(shí)現(xiàn)當(dāng)二級(jí)選擇廣州市時(shí)，三級(jí)的下拉數(shù)據(jù)源就是indirect(“廣州市”)。

就這樣，三級(jí)下拉菜單就做完了！其實(shí)也不難對(duì)吧，做出這種多級(jí)聯(lián)動(dòng)的下拉菜單，會(huì)讓數(shù)據(jù)錄入更方便快捷~

但是要注意的是，這也并不是絕對(duì)的限制，具體要注意的問(wèn)題請(qǐng)見下方：

注意事項(xiàng)&總結(jié)

1、用indirect動(dòng)態(tài)引用名稱的辦法，操作是簡(jiǎn)單，但若下級(jí)數(shù)據(jù)有更改時(shí)并不能自動(dòng)擴(kuò)展，后續(xù)維護(hù)麻煩。

解決擴(kuò)展問(wèn)題，可以在方法1基礎(chǔ)上結(jié)合表去操作，或直接把二三級(jí)的序列數(shù)據(jù)源設(shè)置為offset動(dòng)態(tài)引用區(qū)域即可。

2、因?yàn)槎?jí)聯(lián)動(dòng)的下拉菜單，并不是Excel原生的功能，只是一個(gè)數(shù)據(jù)驗(yàn)證的功能；所以當(dāng)你填寫好了各級(jí)內(nèi)容后，反過(guò)來(lái)再更改一級(jí)的選項(xiàng)時(shí)，二三級(jí)的內(nèi)容并不會(huì)被清除或提示錯(cuò)誤。

要解決這個(gè)問(wèn)題，可以用條件格式根據(jù)判斷下級(jí)內(nèi)容是否屬于上級(jí)進(jìn)行提示，如改變填充色或字體色等；再高階的辦法是用VBA去判斷上級(jí)單元格是否有變化，一旦變化，則清空下級(jí)單元格的內(nèi)容。

3、Excel的【數(shù)據(jù)驗(yàn)證】功能比較脆弱，若別人在你設(shè)置了數(shù)據(jù)驗(yàn)證的區(qū)域進(jìn)行粘貼的操作，就會(huì)被破壞掉了！這里又有3個(gè)解決方法：

技術(shù)法：用VBA限制不讓用戶粘貼。

管理法：搞清楚用戶為什么要粘貼，因?yàn)槭遣欢褂妹矗窟€是要填寫的重復(fù)內(nèi)容太多？通過(guò)培訓(xùn)教用戶去好好使用即可。

換工具：若培訓(xùn)不便操作，VBA又不懂的話，那你也就不適合用Excel來(lái)收集數(shù)據(jù)了。下方推薦幾個(gè)在線收集數(shù)據(jù)的工具，在這方面會(huì)有提升，收集好數(shù)據(jù)可以直接導(dǎo)出到Excel，也很方便！甚至也有多級(jí)下拉菜單的，請(qǐng)自行探索~~

• 麥客CRM

• 金數(shù)據(jù)

• 問(wèn)卷星

• 騰訊文檔

要做好一項(xiàng)工作，那就想辦法去推進(jìn)好，方法不行就換方法，工具不行就換工具，自己不行就多學(xué)習(xí)~~~