上一節(jié)我們學(xué)習(xí)了基礎(chǔ)下拉列表的制作，我們想再加一下碼，讓我們的下拉列表更加高級(jí)，更加大氣，更加實(shí)用，這就是多級(jí)聯(lián)動(dòng)的下拉列表。我們?cè)谌粘Ｉ钪幸步?jīng)常能夠用到，比如，中國(guó)的行政區(qū)劃，分為多級(jí)，第一級(jí)是省自治區(qū)、直轄市，第二級(jí)是地區(qū)，第三級(jí)是縣(當(dāng)然還可以往下分，我們姑且分到三級(jí))。當(dāng)我們想在表格中選擇一個(gè)**縣時(shí)，我們不會(huì)在全國(guó)上千個(gè)縣里一個(gè)個(gè)的找，那樣得累死，也找不到，我們會(huì)先選擇該縣所在省，在相應(yīng)的省里選擇該縣所在的地區(qū)，然后再選擇**縣。這就是多級(jí)下拉列表，而且還要求它有聯(lián)動(dòng)功能，當(dāng)我選擇河北省時(shí)，在下一級(jí)列表中應(yīng)該顯示河北省的地區(qū)，而不應(yīng)該顯示山東或其他省份的地區(qū)。

A. 用offset制作聯(lián)動(dòng)下拉列表

下面我們以一個(gè)實(shí)例來(lái)講解，數(shù)據(jù)是集團(tuán)公司下屬兩個(gè)分公司的不同部門人員花名冊(cè)，要求制作下拉列表完成公司、部門和職工的原則。下圖是我們的花名冊(cè)數(shù)據(jù)，數(shù)據(jù)放在名為“數(shù)據(jù)源1”的工作表中。

數(shù)據(jù)源

(1)我們首先需要對(duì)數(shù)據(jù)進(jìn)行以下改造，改造后數(shù)據(jù)如下。分出數(shù)據(jù)的等級(jí)，顯然，從數(shù)據(jù)分析出，第一級(jí)為A公司、B公司，第二級(jí)為各個(gè)公司下屬的管理部門，第三級(jí)是隸屬于各部門的員工姓名。如下圖，在“數(shù)據(jù)源1”工作表中進(jìn)行數(shù)據(jù)改造。

數(shù)據(jù)改造

(2)構(gòu)建一級(jí)下拉列表，選擇公司

這一步最簡(jiǎn)單，選中需要設(shè)置下拉列表的區(qū)域(這里以C23單元格為例)，點(diǎn)擊 數(shù)據(jù)—有效性—選擇序列—來(lái)源內(nèi)輸入“=數(shù)據(jù)源1!$F$2:$F$3”

(3)構(gòu)建二級(jí)下拉列表，選擇部門

選中需要設(shè)置下拉列表的區(qū)域(D23單元格)，點(diǎn)擊 數(shù)據(jù)—有效性—選擇序列—來(lái)源內(nèi)輸入公式

=OFFSET(數(shù)據(jù)源1!$H$1,MATCH(C23,數(shù)據(jù)源1!$H$2:$H$7,0),1,COUNTIF(數(shù)據(jù)源1!$H:$H,C23),1)

說(shuō)明：

C22為公司所在單元格；countif是為了計(jì)算出公司內(nèi)部門的數(shù)量；由于A公司和B公司是連續(xù)的，通過(guò)offset計(jì)算出一段連續(xù)的偏移數(shù)據(jù)，得到部門所在的區(qū)域。

(4)構(gòu)建三級(jí)下拉列表，選擇員工

在 有效性—序列—來(lái)源中輸入公式

=OFFSET(數(shù)據(jù)源1!K1,MATCH(C23&D23,數(shù)據(jù)源1!K2:K50,0),1，countif(數(shù)據(jù)源1!K2:K50,C23&D23),1)

通過(guò)公司和部門組合作為查找條件，來(lái)得到部門員工所在的數(shù)據(jù)區(qū)域。

數(shù)據(jù)有效性構(gòu)建多級(jí)列表

綜述：通過(guò)以上步驟，我們可以得到一個(gè)多級(jí)聯(lián)動(dòng)的下拉列表，關(guān)鍵是對(duì)數(shù)據(jù)區(qū)域進(jìn)行改造，要保證同公司的部門連續(xù)，同部門的員工連續(xù)。

注意：設(shè)置下一級(jí)下拉列表時(shí)，上一級(jí)下拉列表必須有選擇數(shù)據(jù)，不然的話可能會(huì)出錯(cuò)誤，下一級(jí)的下拉列表依靠上一級(jí)具體選擇的數(shù)據(jù)。

B. 利用工作表控件構(gòu)建多級(jí)菜下拉單

下面我們用兩個(gè)組合框制作二級(jí)下拉列表，數(shù)據(jù)源放在名為“數(shù)據(jù)源2”的工作表中

(1)進(jìn)行數(shù)據(jù)改造

(2)制作組合框

在名為“2.b”工作表中繪制2個(gè)組合框

組合框

(3)定義2個(gè)名稱

點(diǎn)擊 公式-名稱管理器-新建

分別新建2個(gè)名稱

部門：=數(shù)據(jù)源2!$D$2:$D$4

員工：

=OFFSET(數(shù)據(jù)源2!$E$1,1,'2.b'!$C$4,COUNTA(OFFSET(數(shù)據(jù)源2!$E$1,1,'2.b'!$C$4,100,1)),1)

定義名稱

(4)設(shè)置組合框格式

第一個(gè)組合框?yàn)椴块T選擇組合框，數(shù)據(jù)源區(qū)域：部門(剛才定義的部門列表)，單元格鏈接區(qū)域：$C$4(顯示選擇條目的索引號(hào))

設(shè)置組合框格式

第二個(gè)組合框?yàn)閱T工選擇組合框，數(shù)據(jù)源區(qū)域：?jiǎn)T工(根據(jù)第一個(gè)組合框選擇不同的部門，顯示不同員工列表)

C. 制作下拉聯(lián)想式列表

本例的數(shù)據(jù)源放在名為“數(shù)據(jù)源3”的工作表中，如圖為各省地級(jí)市列表。

數(shù)據(jù)

選中想要設(shè)置聯(lián)想式列表的單元格，點(diǎn)擊 數(shù)據(jù)—有效性—選擇 序列，在來(lái)源 框中輸入如下公式：

=OFFSET(數(shù)據(jù)源3!$A$1,MATCH($C5&"*",數(shù)據(jù)源3!$A:$A,0),0,COUNTIF(數(shù)據(jù)源3!$A:$A,C5&"*"),1)

然后，點(diǎn)擊 出錯(cuò)警告，將“輸入無(wú)效數(shù)據(jù)時(shí)顯示出錯(cuò)警告”前面√點(diǎn)掉。

這種聯(lián)想式下拉列表要求相近的數(shù)據(jù)必須連續(xù)排列，比如說(shuō)河北省的各地級(jí)市要連續(xù)排列，不能中間隔著山東省的地級(jí)市，那樣的話數(shù)據(jù)下拉列表就會(huì)顯示不全。還有一種聯(lián)想式下拉列表之前的文章有過(guò)介紹，(“工作表中創(chuàng)建帶有聯(lián)想功能的下拉菜單，純公式實(shí)現(xiàn)”)有感興趣的請(qǐng)移步到去研究，

回顧：

用了兩篇文章的篇幅把主要的下拉列表的做法給大家介紹一下，不能涵蓋全部，但是，應(yīng)付一般的日常應(yīng)用應(yīng)該是沒(méi)有什么問(wèn)題的。對(duì)于多級(jí)列表，原理大概都是以上講的那些，掌握了這些，無(wú)論用數(shù)據(jù)有效性還是工作表控件，都能制作出二級(jí)、三級(jí)甚至更多級(jí)的下拉列表。講了這么多，感覺(jué)用圖片和文字做講解，還真是很累，有些地方還是說(shuō)不明白，希望大家多多聯(lián)系，多多思考研究。