申明：本公眾號下文章，均為小熙本人原創(chuàng)，每個字都是自已碼，每張圖都是親自制作請尊重原創(chuàng)，如有方法上的異見，┆歡迎留言交流！

構(gòu)建二級聯(lián)動下拉菜單，可以用INDIRECT函數(shù)引用名稱構(gòu)建的區(qū)域來實現(xiàn)二級下拉菜單?？墒侨绻麛?shù)據(jù)量較多，或者數(shù)據(jù)源的數(shù)據(jù)的分布較麻煩時，這種方法就會較麻煩。

這時，我們可以的OFFSET函數(shù)來直接構(gòu)建一個引用的區(qū)域?qū)崿F(xiàn)二級下拉菜單。

怎么完成的呢？以下是數(shù)據(jù)源。

A列有很多所學(xué)校，每所學(xué)校的班級代碼與班主任名字都列出來了，現(xiàn)在要做一個二級聯(lián)動下拉菜單，實現(xiàn)快速查找。效果如下：

如果用以前的方法，也是可以完成二級下拉菜單制作。但是根據(jù)數(shù)據(jù)源數(shù)據(jù)情況，需要花很多時間來整理數(shù)據(jù)，并且還要定義名稱。如何才能快速完成二級下拉菜單的制作呢？

1、制作一級下拉菜單

首先將學(xué)校分類下的所有數(shù)據(jù)復(fù)制后粘貼在右邊空白單元格內(nèi)，并全部選中，單擊數(shù)據(jù)——刪除重復(fù)值，得到如下結(jié)果：

選中F2單元格，單擊數(shù)據(jù)——數(shù)據(jù)驗證——數(shù)據(jù)驗證，在允許下選擇“序列”，在來源里選擇L2：L5

確定后，回到工作表，就制作好了第一級的下拉菜單，此時我們隨意選擇一個數(shù)據(jù)。如“中為”

2、制作二級下拉菜單

在如圖數(shù)據(jù)源排列情況下，用OFFSET函數(shù)來引用區(qū)域，是最好的方法。單擊G2選中，單擊數(shù)據(jù)——數(shù)據(jù)驗證——數(shù)據(jù)驗證。在允許下選擇“序列”，在來源下框內(nèi)輸入公式：=OFFSET(B1,MATCH(F2,A:A,0)-1,0,COUNTIF(A:A,F2),1)，確定返回即可。

確定后返回到工作表內(nèi)，選擇一級菜單下的學(xué)校，你就會發(fā)現(xiàn)，二級菜單下的代碼會自動顯示對應(yīng)的學(xué)校的班級代碼。當(dāng)然其與數(shù)據(jù)源里順序與個數(shù)完全一致。

=OFFSET(B1,MATCH(F2,A:A,0)-1,0,COUNTIF(A:A,F2),1)這個公式如何理解呢？

OFFSET是一個引用函數(shù)，實際上就是引用的一個區(qū)域。這個區(qū)域會隨F2選擇不同對象而發(fā)生變化。它是如何變化的呢？

在數(shù)據(jù)源里，公式=OFFSET(B1,MATCH(F2,A:A,0)-1,0,COUNTIF(A:A,F2),1)的意思是：以B1（第一參數(shù)）為起點，向下移動MATCH(F2,A:A,0)-1行（MATCH函數(shù)是找到F2在A列的位置，其結(jié)果是一個數(shù)字，因為第一行是標(biāo)題，所以最后減去1才是要移動的行數(shù)，如F2是中為，則中為在A列的位置是12，則以要向下移動的行數(shù)是11行才能顯示出中為）。因為是以B1為起點，所以，第三參數(shù)是0，也就是不移動列數(shù)。第四參數(shù)是COUNTIF(A:A,F2)

表示要顯示的個數(shù)，顯示其個數(shù)就是F2在A列的個數(shù)。最后一個參數(shù)是默認(rèn)數(shù)值1。最后兩個參數(shù)的意思就是引用的區(qū)域。公式合起來意思可以這樣說：以B1為起點，向下移動MATCH(F2,A:A,0)-1，向右移動0列，以此開始，總共COUNTIF(A:A,F2)行，總共是1列顯示。

3、用VLOOKUP函數(shù)找到對應(yīng)班主任

在H3單元格內(nèi)輸入公式可以直接顯示出對應(yīng)的班主任。因為代碼與班主任是一一對應(yīng)關(guān)系，所以直接用VLOOKUP函數(shù)可完成：=VLOOKUP(G2,B:C,2,0)。為防止G2空格時的錯誤顯示，在此公式前嵌套一個IFERROR即可。結(jié)果如下 。

最后結(jié)果如下視頻：