Excel資料規範輸入技巧 | 二級聯動下拉選單
為規範Excel資料輸入及提高輸入效率,會透過資料有效性設定下拉選單。使用者透過在選單中選擇對應的內容,既節省時間也能確保輸入內容標準規範。
如:
資料有效性的設定
也很簡單
資料 - 資料驗證
驗證條件 - 序列
若手動輸入,各資料分隔符號為英文狀態下的逗號
但工作中也常遇到二級下拉選單情況,且第二個下拉選單的內容由第一個下拉選單決定,也就是二級聯動下拉選單
如下面的示例
不同系統包括不同部門
選定第一列的系統後,第二列的部門選項也對應變化
如選定公共系統,則部門選項顯示行政部與後勤部
如選定營銷系統,則部門顯示銷售部、客服部、公關部
上面這種二級聯動下拉選單如何設定呢?
- - - - - - -- - - - - - - - - - - - - - - -
先梳理下思路
一級下拉選單選定後,二級下拉選單選項也對應變化
以本文中的系統和部門為例
系統變化後,部門選項也對應變化
① 先告訴Excel哪些部門歸屬哪個系統
如何告訴Excel?
使用名稱管理器給不同名稱劃定不同單元格區域
如公共系統包含=Sheet2!$F$2:$F$3即行政部 後勤部所在區域
② 將部門和系統相關聯,二級選單的資料來源取決於一級選單的結果
即從一級選單結果中引用
使用Indirect引用函式
Indirect函式的書面解釋很複雜,化繁為簡,我將它理解為
對單元格結果的引用
函式。
語法=indirect(b2)
b2代指單元格
如本例中部門從系統的結果中引用
=indirect(b2)
思路總結
使用名稱管理器將資料分門別類
分別設定資料有效性
第一個資料有效性常規設定
第二個資料有效性使用引用函式indirect
- - - - - - -- - - - - -- - - - - - - - - -
詳細步驟:
①選擇資料區域
因資料行列不一致,選擇的區域中含空白單元格
② F5定位常量,只選擇資料部分
③ 公式 - 名稱管理器 - 根據所選內容建立
④ 根據大類別所在區域建立名稱
此處公共系統、營銷系統、研發系統為大類別
均在資料區域的首行
選擇首行建立名稱
OK,資料已歸類
效果如下:
⑤ 設定第一個資料有效性
資料 - 資料驗證 - 允許選擇序列,來源選擇類別所在的區域,也可以手動輸入
效果如下:
⑥ 設定第二個資料有效性
資料 - 資料驗證 - 允許選擇序列,來源引用第一個資料有效性的結果
⑦ 將設定向下填充,應用至該列其他單元格
需格外注意indirect函式中的引數b2,
不能絕對引用!!!
若$b$2絕對引用,向下填充後c3單元格引用的還是b2單元格結果
此處用了混合引用$b2,固定b列,但是單元格行號跟隨c列資料變動
以C3單元格為例,點進資料驗證檢視設定
indirect($b3)對應的正是需要的資料