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

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

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

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

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

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

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

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

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

這里就有兩種解決辦法：

1、indirect引用名稱法

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

以下介紹方法1的操作：

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

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

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

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

但是要注意的是，這也并不是絕對的限制，具體要注意的問題請見下方：

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

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

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

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

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

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

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

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

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

• 麥客CRM

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

• 問卷星

• 騰訊文檔

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