花幽草木深，學習要認真，我還是我，一個愛聊Excel的胖小夥，廖晨！今天要聊的事就一個，在我入職不久發生的小事，卻成了入職最大的坎，不過現在再看，它卻成為我的工作經驗中的最大財富。

這件小事發生在一個夏天的上午，天還不錯，就是一晚上母蚊子的騷擾，沒有抗住，搞得雖然到了公司，依然睡眼朦朧，不過沒有一會兒就被QQ資訊鈴聲叫醒，開啟一看，是頂頭上司老王發來的一個檔案和一條訊息；

內容大致的意思就是

：

把表中的資訊製作成下拉選單，選擇錄入部門後，選擇錄入部門對應的員工，下班前完成！

我心裡說：什麼就下班前，檔案是什麼我都還沒有看呢。檔案內容如圖：

部門員工表（圖1）

不過看完之後，這也太容易了，就回：下班前肯定發給你！我做夢也不會想到，就是這簡單的一句話，卻成為我在別人手裡的把柄。

不過說到完成這個功能，主要有2點：

1.資料增減，下拉選項會自動增減相應資料

2.當父級內容發生變化，子選項也發生變化

我首先想到的是Excel新功能，智慧表格，能解決第1點，至於第2點就需要用資料驗證+自定義名稱的方法解決；有了思路，剩下的就看操作了。

1.

在J2：K2，分別錄入“部門”，”員工”；

2.

選擇B2：F2，點選公式下的定義名稱，在彈出的自定義視窗，名稱輸入：部門，引用位置（R）：=$B$2：$F$2，點確定

自定義名稱操作步驟示意圖（圖2）

3

。選擇B2：F23，ctrl+t，彈出建立表，勾選包含標題，點選確定；ctrl+shift+f3或點選公式選單下的根據內容建立定義的名稱，彈窗去掉勾選最左列（L）的項，點選確定；

開啟智慧表格的操作示意圖（圖3）

4

。在名稱框錄入：J3：J100，回車，點選資料選單下的資料驗證，允許選擇序列，來源錄入=部門，確定；

5

。重複第4步，名稱框錄入：K3：k100，回車，在資料驗證視窗中，來源錄入為=INDIRECT（OFFSET（$J$1，ROW（）-1，0）），確定。

資料驗證操作步驟示意圖（圖4）

調整文字格式和樣式，就發給你老王，不一會老王過說：

你這做的不行啊，沒有資料的表格不要樣式，還有我在選擇員工的時候，不要有空白選項。

我說：

那是因為智慧表格......

老王打斷我說：

我不需要解釋，我需要的是結果，ok?

說完就走了，我默默想：

我有什麼地方得罪他了嗎？還是先改表吧！

1

。取消

套用表格格式

，選擇B2：F23中的任一單元格，Excel選單會增加一個

[設計]選單

項

，點選

設計

，然後在

表格樣式

中選擇

無

；

2

。點選

[公式]

選單下的

名稱管理

或CTRL+F3，選擇對應的名稱，手動修改引用位置為當前有資料的實際引用範圍；

經過上面的操作，出現新的問題，資料增加，需要手動增加樣式，新的資料也無法自動增加的下拉列表，既然一個智慧表格無法解決的問題，我們將每一列拆分成單獨的智慧表格，是不是就能解決問題。

於是之前自定義的名稱只保留

部門

，其餘全部刪除，然後進行如下操作：

1.

選擇B2：B12，然後ctrl+t，彈出建立表視窗，勾選標題，選擇確定，

2.

然後分別C2：C8，D2：D10，E2：E21，F2：23，重複1的操作；完成所有設定；

3.

ctrl+F3或點選

公式

->

名稱管理器，

依次將名稱“表1”到“表5改為標題的內容，依次：銷售部、營銷部、設計部、採購部、後勤部；

獨立設定智慧表格及修改名稱（圖5）

修改完成，覺的這次應該滿意老王吧，發過去！

這時一看錶，已經下午2點多了，還好已經弄完了，要不，得晚上加班啊。正想下班去哪玩的時候，這做的還是不行啊，我一回頭，還是老王的倒黴臉。

老王：

我要的下拉選單不是在一張表上的，是在另一個表裡，還有就是我刪除表格內容，樣式還保留，而且在下拉列表然後有留白的情況，你再弄弄吧，下班前給我！

我心說：

給你妹兒啊，不在一張表上，怎麼不早說！我轉身坐下，說了一句：好。心裡有氣，但沒有什麼好說的，繼續弄吧！

刪除資料會留白這是智慧表格特性，無法更改，看來智慧上函式解決了，不過思路也比較簡單，就是根據每列的內容，動態生成名稱的實際引用範圍，及每列不為空的單元格數量減1，可以用COUNTA函式來做，然後藉助OFFSET函式生成範圍；

在說用法之前，先簡單介紹一下COUNTA和OFFSET函式用法：

COUNTA(引用範圍)：統計出引用範圍內，不為空的單元格數量；常用的引用範圍格式：B1:D5，寫法=COUNTA(B1:D5)

OFFSET(開始位置,上下移的行數,左右移的列數,[行高],[列寬])，比如=OFFSET(A2,1,0,3,2) =A3:B4,

公式解讀

：A2為開始位置，向下移動1個單元格及A3，左右移動為0，行高為3表示引用範圍每一列的行數，列寬為2，引用範圍共為2列；

有思路，就接著弄吧：

1.

刪除原表Sheet1中的J列，F列的資料；

2.

選擇B2：B2，然後單擊滑鼠右鍵，在右鍵選單中選擇【表格】→【轉化為區域】，接下來會彈出一個提示框，點選【是】

取消智慧表格的操作步驟示意圖（圖6）

3

。將依次C2：C8，D2：D10，E2：E21，F2：23，重複2的操作，即可刪除自定義名稱；

4

.

重新自定義名稱：CTRL+F3調出名稱管理器，點選【新建（N）】按鈕，彈出新建名稱視窗，名稱輸入：銷售部，只需修改引用位置（R）公式為=OFFSET（Sheet1$B$2，1，0，COUNTA（Sheet1$B：$B）-1，1），點選確定

5.

重複第4步操作，定義名稱為：營銷部，設計部，採購部，後勤部，將引用位置公式中的所有B， 依次改為：C，D，E，F

配圖7

6.

shift + f11，新建工作表Sheet2，A1，B1，錄入部門和員工；

7.

在名稱框錄入：B2：B100，點選資料選單下的資料驗證，允許選擇序列，來源錄入=部門，確定；

當給C2：C100，加資料驗證時，卻無法彈出內容，這時一看錶，已經快6點，都下班半個小時啦！

這時

老王

又過來

：

弄完了，答應別人的事，一定要完成，這是做人的基本信用！

我心說

：還不是一直在改需求，還有臉說。他看我沒有理他，接著說：弄好了，發給我，我就先回去了，等我讓你下班，才回去啊！

嗯了一聲，就趕緊在網上查資料，終於找到解決方法，需要用宏函式EVALUATE來代替INDIRECT函式，而且宏函式不能直接在單元格中使用，不過可以在自定義中執行，然後就定義名稱為menu，具體操作步驟如下：

8.

ctrl+f3，點選新建（N）按鈕，彈出新建名稱視窗，錄入名稱：menu，引用位置（R），公式為：=EVALUATE（OFFSET（Sheet2！$A$1，ROW（）-1，0））；（操作可參考圖6）

9.

在名稱框輸入B2：B100，回車，點選【資料】->［資料驗證］，彈出視窗中，允許（A），選序列，來源（S），錄入=menu，確定

注：

使用了宏函式，檔案必須儲存成XLSM格式，否則設定的名稱menu不會被儲存；

最後整理一下樣式，就發給了老王，在等回信的時間裡，仔細看一下，為什麼INDIRECT函式無法調出資料？

INDIRECT函式的功能是讀取引數指定單元格地址的值，名稱若是個實際的引用範圍或是實際的值，這都沒有問題，當名稱是表示式，就會返回錯誤編碼，而資料驗證時，會自動過濾掉錯誤編碼，就顯示為空，是不是不好理解，舉個簡單的例子，比如公式=INDIRECT（SUM（3，5）），結果為#REF！。

一看錶過去快半個小時，怎麼還沒有信呢？終於看見

老王

的頭像閃爍，點開一看

：

嗯，還行吧，你先回去吧，有什麼問題我明天再給你說吧！

什麼！還不滿意嗎？去你的，反正明天再說，先回家了。

第二天上班，

老王

找到我

說：

樣式上有點不喜歡，需要調整字型大小和顏色。看著用的時間步長的份上，就又給他調整幾次，直到後來，他沒有再找過我。

後來才知道，原來當時他家親戚要來，想讓我提辭職走人，好把位置留給他親戚，結果，我沒有按他的套路走，現在想多虧當時的忍耐，不過在這件事裡，我總結了3點：

1。在開始工作前，一定要確認要你做什麼，別怕麻煩；

2。在跟領導彙報進度時，別說滿，給自己留有餘地；。

3。只要你技術好，即使沒有關係，也還是混的住，別人想趕走還是趕走不你的！

不是有句話說的好：爺就靜靜的看著你想幹掉我卻幹不掉我的樣子！文章的最後，花葉草木深，鐵杵磨成針，我就是我，一個愛聊Excel的胖小夥，廖晨，喜歡我就關注吧！你的點贊和留言，都是對我鼓勵！也歡迎你吧生活中遇到的Excel相關的問題，私信給我，看到後必第一時間回覆！