動態二級菜單，有點燒腦

小夥伴們好哈，今天和大家一起學習一下如何製作動態引用的二級菜單。

先準備好數據源——不同區域的客戶對照表：

需要在銷售匯總表中動態引用區域，以及不同區域的客戶姓名：

接下來就是自定義名稱的環節了：

依次點擊【公式】選項卡，【定義名稱】，自定義名稱「區域」，在引用位置文本框內寫上公式：

=OFFSET($A$1,,,,COUNTA($1:$1))

COUNTA($1:$1)的意思是計算第一行內不為空的單元格個數。

OFFSET函數的基本語法是：

=OFFSET(基點,向下偏移行數,向右偏移列數,新引用行數,新引用列數)

本例中第二至第四參數省略，意思是以A1為基點，向下偏移的行數為0，向右偏移的列數為0，新引用的列數為COUNTA($1:$1)的計算結果。

這樣的話，基礎數據增加，引用範圍就會自動擴展了。

為「銷售匯總表」工作表客戶區域的所在列設置數據有效性：

第一步完成了，依次點擊【公式】選項卡，【定義名稱】，自定義名稱「姓名」，在引用位置文本框內寫上公式：

=OFFSET(客戶對照表!$A$2,,MATCH($B2,客戶對照表!$1:$1,)-1,COUNTA(OFFSET(客戶對照表!$A$2,,MATCH($B2,客戶對照表!$1:$1,)-1,100)))

這個公式有點小複雜，咱們慢慢解析，實在看不懂的話也不用急，可以先收藏下來，以後隨著函數功力不斷增強，理解也就是水到渠成的事兒了：

MATCH($B2,客戶對照表!$1:$1,)部分，返回「銷售匯總表」B2單元格（也就是客戶區域）在「客戶對照表」工作表第一行的位置。

如果B2是「華中區」，則MATCH函數返回結果為2：

MATCH函數返回的結果作為OFFSET函數的列偏移參數。

再來看下面這一部分：

OFFSET(客戶對照表!$A$2,,MATCH($B2,客戶對照表!$1:$1,)-1,100)

意思是：

以客戶對照表!$A$2為基點，

向下偏移行數為0行，

向右偏移列數為MATCH函數計算結果減1，

新引用的行數為100。

這裡的100可以寫成一個較大的數值，只要能保證比你的實際數據最大行數多一些就可以。

這樣就等於引用了客戶區域所在列100行的範圍。

再用COUNTA函數計算客戶區域所在列100行的範圍內有多少個非空單元格。

COUNTA函數得到的結果再作為最外層OFFSET函數的新引用行數。

整個公式的意思是：

以客戶對照表!$A$2為基點，向下偏移行數為0，向右偏移列數為客戶區域在「客戶對照表」工作表第一行的位置減1，新引用的行數為該列實際的不為空單元格個數，這樣如果數據增加，COUNTA的結果也會發生變化，再反饋給OFFSET函數，就得到了動態的引用區域。

最後為「銷售匯總表」工作表客戶姓名的所在列設置數據有效性：

OK，設置完成。

在B列選擇客戶區域，C列就可以選擇對應的客戶姓名了。

有同學可能會說了：這個方法好複雜，有什麼優越性啊？

別急，在客戶對照表中增加數據看看：

回到銷售匯總表中，點擊客戶區域的下拉列表，已經自動添加了華南區的選項：

接下來看看華南區的客戶姓名：

在客戶對照表中增加華南區客戶數據：

回到銷售匯總表中，點擊客戶姓名的下拉列表，已經自動更新了華南區新增客戶姓名：

圖文：祝洪忠

長按下面二維碼圖片，點」識別圖中二維碼「然後再點關注，每天都會收到最新excel教程。

ExcelHome，微軟技術社區聯盟成員

易學寶微視頻教程，1290個Office技巧精粹，每個技巧都與實際工作密切相關。輕鬆學習技巧，練就職場達人。淘寶搜索關鍵字：ExcelHome易學寶

本公眾號回復關鍵字：大禮包立刻免費獲取50集精選易學寶教程

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！