Excel三級聯動控件列表框,高級!高級!高級!

Urin的自习室
19粉絲
大家好
這一章呢我們來講多級聯動的列表框
其實呢
它和之前我們所講過的二級下拉清單
三級下拉清單會比較相似
比如說在第一個列表框當中呢
我們可以選擇到湖北省
那在第二級當中呢
只能夠選擇到湖北省的相關地級市
然後呢在第三集當中呢
只能夠選擇到
當前荊州市所對應到的區或者是縣
首先呢可以選擇到開發工具
如果大家沒有開發工具呢
可以選擇到文件選項
在自定義功能區當中呢
找到開發工具勾選起來
點擊到確定
接下來呢
可以選擇到插入
選擇到表單控件當中的列表框
並且呢咱們需要三個啊
所以呢可以把它給複製一下
放置在旁邊
先來看一下數據源
這邊呢
會有三個省份以及旗下的一些地級市
在這些地級市當中呢
譬如說荊州市啊
就會有這樣一些區或者是縣啊
所以這邊呢是第一集
第一集是呢是第二集
區或者是縣呢就應該是第三集
我們先來設置第一個列表框
可能啊大家會這樣去做
選擇到第一個列表框
對它來進行設置
然後在這個區域當中呢
我們可以來選擇到這一行啊
第一集嘛
選擇到確定
你會發現他只會顯示出來一個
因爲在列表框當中呢
我們所需要的數據呢
並不是一行數據
而是一列數據啊
所以呢可以把這一塊來做一個複製
在輔助頁當中呢來做一個轉置好
接下來呢
我們再來設置第一集的列表框
把數據區域呢把它給切換到這
一塊我們再來看一下最後的結果
你看現在呢就應該是沒有問題了
接下來呢我們再一次對它來進行設置
在單元格鏈接啊這個位置呢
可以把它給鏈接
在輔助頁當中的這個單元格當中
然後選擇到確定
當我們選擇到湖南省呢
他在這三個單元格當中的第二個
所以會顯示出2
選擇到江西呢他就會變成3
選擇到湖北省呢就會變成1
接下來呢
就可以使用到函數來做第二集
首先呢使用到index函數
選擇到這個數據源當中
然後這個是d級式系列嘛對吧
好row number呢我們可以不用關心它
在Colon number這個位置呢
剛纔我們所選擇的這個湖北呢
正好就是在第一列對吧
它就會變成一
所以呢我們可以在第三個參數
這個位置來連鏈接到它了
好這個時候你會發現
它確實是湖北省的一些相關的地級市
但是呢你會發現在數據源當中
我們所框選的是這一塊江西省呢
它會有四個地級市
但是在湖北省這塊呢
由於我們所選擇的區域在這塊
所以它會包含最後一個單元格
你會發現這個位置會出現一個0
如果使用到這塊區域
把它給放進列表框當中的時候呢
它也會出現一個0
所以這種做法呢是不對的
正確的做法呢應該是等於啊OFFSET函數
然後呢選擇到這個單元格F4決定用它
然後往下挪動一個單元格
就變成了是5函數
然後往右邊挪動幾個單元格呢就取
決於在一級列表框當中
我們所選擇的值
好現在呢
我們所選擇的是湖南省
所以它是2對不對
好當
我們如果是選擇到這個單元格的時候
大家可以注意到最後的結果啊
我們再來看一下這個公式
以這個單元格爲基準
往下面移動一個單元格
就變成了是我漢式
然後以這個單元格爲基準呢
往右邊挪動了兩個單元格
就變成了是南昌市
而湖南省呢
只需要往右邊挪動一個單元格就行
所以呢
我們就需要在OFFSET函數最後一個參數
這個位置啊
Collins這個位置呢
需要來減去一
它就會變成是長沙
好接下來呢
我們可以把這段公式再來做一個複製
然後呢在這個位置啊
使用到一個cons a函數
然後呢
把剛纔的這段公式把它給複製進來
並且呢在這段公式中間
我們需要來添加第四個參數給到1,000
這是什麼意思啊
我們可以先來看一下
那這一段公式的意思就是說
在自動更新的下拉清單那章當中
我們已經講過了
以這個單元格爲基準
我們移動到了長沙對吧
然後呢從這個單元格
我們把高度調整到了1,000
在這個範圍中間
我們在使用到了count a函數
它算出來的值呢就是3啊
所以大家可以注意到
其實呢在這一段公式中間
count a的結果就是3
然後呢我們就變成了什麼呢
就變成了以這個單元格爲基準
我們走到了長沙市這個位置
然後呢高度呢是3
從這個位置蔓延
到這個位置
當如果是選擇到的是江西省的時候呢
就是以這個單元格爲基準
我們用count a所算出來的結果是4啊
所以它就會從這個位置
蔓延到這個位置
就不會出現多餘的部分
這個呢是在自動更新的下拉清單當中
咱們所講到過的公式好吧
好接下來呢
我們可以把整個這段公式呢
來做一個複製
然後啊選擇到公式名稱管理新建
然後寫到二級
然後呢把剛纔這段公式複製進來
確定關閉好
選擇到列表框
對它來進行設置
然後呢
在中間可以來寫到剛纔的名稱定義啊
叫做二級
然後選擇到確定
你看選擇到江西省
江西省的地級市
湖北省的地級市
湖南省的地級市
在製作第三集之前呢
可以來設置一下第二集啊
同樣單元格鏈接呢
可以選擇到放置在這個位置
選擇到確定
好大家可以注意到啊
現在我們所選擇的是江西省
然後選擇到上饒市
你看這個清單呢
之前是我們做出來的對吧
然後你可以看到這邊的第三第三行
整好呢就是在他
好我們可以再來試一下
選擇到湖北省的宜昌市
那湖北省的相關內容呢
通過之前的函數就可以顯示在這一塊
然後宜昌市呢
就正好是在第二個對不對
好那接下來呢
就可以用到一個index函數
然後呢可以選擇到這塊區域當中
如果你是365用戶呢
直接可以選擇到這塊區域當中
他會根據這個數組的伸縮變化
發生相應的變化
如果是老版本的也沒有關係
我們直接呢可以多拉扯一些些
然後RO number呢
就應該是2
和列沒有關係
直接可以給到確定啊
所以這個值呢
它一定等於我們所選擇的這個值
同時呢如果是老版本的話
你這個位置呢
他壓根就不會溢出啊
他壓根就不會溢出
所以呢你可以把這一段公式呢
把它給嵌套放在我們的這個範圍中間
也是可以的
好吧
好我們現在已經知道荊州市了
就是我們所選擇的位置對不對
好再看數據源荊州市在哪個位置啊
我們是不是可以來做一個match啊
所以呢可以在下方再來做一個match
match這個荊州市
好吧然後呢
在這個區域當中來進行麥序
這個是第三個
下拉清單所在的區域當中啊
然後0精確匹配括戶回來確定
金州市呢
就應該是在整個這一塊的第三列
再往後走呢
其實跟之前的道理就會很相似了
還有首先呢
我們還是到這邊來使用到offset函數
好吧然後
選擇到數據源當中的這個單元格當中
F4決定用它
然後呢往下挪動一個單元格
挪動幾列呢
就應該是來自於
剛纔我們所match出來的結果
金州市啊
大家可以注意到
相對於武漢市所在的位置呢
應該是來偏移兩列對吧
而剛纔呢
在這個位置呢是3啊
所以同樣的
我們需要在這個基礎之上呢
再來減去一個1
至於說它的高度呢
是不是同樣的道理
我們同樣可以把剛纔這段公式
來做一個複製
然後呢Ctrl a函數
然後呢把offset函數把它給複製過來
然後在這個offset函數當中的
最後一個參數呢
可以來輸入到1,0001萬啊
都可以括戶回來
括戶回來
確定確定
那荊州市荊州區這一塊
總共呢會有五個單元格
這邊呢正好是五個單元格
我們再來切換一下
選擇到宜昌市
那這邊呢就是宜昌市啊
總共也是5個單元格
宜昌市好
我們可以來選擇到江西的吉安市
試一下
那這邊就是兩個單元格啊
跟這邊都是一模一樣的對不對
那接下來呢
就可以選擇到這個公式啊
control a全選複製啊
然後再次選擇到公式名稱管理
然後新建這一層呢
可以來寫到是三級對吧
然後呢
可以把剛纔的公式把它給複製進來啊
大家注意複製的時候呢
一定要乾淨啊確定
然後呢可以把它給關閉
我們再次選擇到第三集下拉清單當中
然後選擇到這邊呢
輸入到三級
然後鏈接單元格呢
可以選擇
放在輔助頁當中的這個單元格當中
然後選擇到確定
湖南省岳陽市
長沙市湖北省荊州市
你看
這邊都可以發生一個同步的聯動變化
當製作好了這三個列表之後呢
可能啊
我們就會要把湖南省放在這個位置
然後長沙市放在這個位置
天心區放在這個位置
然後可能還會有一些額外的條件
在這個基礎之上呢
我們可能會要去做多條件查找的數組
啊所以呢
我們就需要把這些內容放進來對吧
應該如何去
做呢index函數啊
啊這個位置就比較簡單了
首先呢
可以選擇到在數據源當中的這一塊
對吧然後呢和行沒有關係
列呢就應該是來自於這一塊的第二列
就應該是湖南省
然後啊我們再來看到第二集
等於index參數
同樣呢可以選擇到在這塊區域當中
然後行應該是第幾行呢
應該是這邊所選擇的第一行
然後應該是第二列確定
長沙市第三集列表當中的內容呢
同樣是index加上match函數啊
等於index函數
然後呢
我們可以選擇到在這塊區域當中
然後我們再來看到行應該是第幾行呢
就應該是大家注意啊
應該是在這個位置的第二行
然後呢就應該是這個第四例括弧
回來確定啊
所以當我們在切換的時候呢
這邊都可以發生一個相應的變化
學習這一招呢
確實是需要大家
對於之前我們所講過的很多知識呢
有一個比較深刻的認識
如果大家啊
對於這些知識
可能會有一些模糊的話呢
最好呢
是把之前我所講過的一些函數
再去看一下
那今天這章呢
咱們就講到這個位置
希望大家呢能夠有所收穫
