Excel下拉選單選一個少一個的教程分享
在B列單元格引用A中的資料,並選擇一個之後,後面的單元格可選內容便就少一個
1思路分析
對原始資料區域進行引用,使得新增資料區域必須在原始資料區域內選擇
那麼,想實現新增資料區域的選一個,少一個
必然需要對原始引用資料進行選一個,少一個同步的變化
即藉助一個輔助D列,對D列進行下拉引用,現在需要做的就是當B列的資料進行更新時,D列的資料更新為A列資料的集合減去B列資料的集合
2操作步驟
1、在D1輸入公式:
=INDEX(A:A,SMALL(IF(COUNTIF($B$1:$B$100,$A$1:$A$100)=0,ROW($A$1:$A$100),2^16),ROW(A1)))&''
按ctrl+shift+enter鍵後向下填充一定數量
2、在E1單元格輸入公式:
=SUM(COUNTIF(D1:D100,A1:A100))
或者使用公式:
=2^20-COUNTBLANK(D:D)
即求出D中有資料顯示的單元格數量
3、建立名稱管理器
在公式中建立名稱管理器,名稱為資料
公式為:=OFFSET(Sheet1!$D$1,0,0,Sheet1!$E$1,1)
即為D列中有資料的區域,該資料會隨變化而變化
4、在B列建立有效性驗證
選單欄資料-資料驗證
將允許修改成序列,將來源設定為名稱管理器中的`資料
這樣就製作成了下拉選單,選一個,少一個的效果。
3思考總結
這裡又運用到了一對多的萬金油公式index+small+if+row的組合
OFFSET製作一個動態的資料區域儲存在公式管理器中
如果下拉選單的引用源直接引用D列,會出現什麼效果呢?
多餘區域及多餘下拉選擇