【原】制作智能下拉菜單，自動(dòng)判斷想要輸入的數(shù)據(jù)，快速提高工作效率

Excel從零到一 2022-08-20 發(fā)布于河南

展開全文

Hello,大家好,大家工作中有沒有遇過這樣的情況,下拉菜單非常的項(xiàng)目很多多,想手動(dòng)輸入還被提示輸入錯(cuò)誤,這個(gè)時(shí)候我們只能一個(gè)一個(gè)的點(diǎn)選非常的麻煩,效率很低,今天就跟大家分享下如何制作智能式下拉菜單,可以根據(jù)我們輸入的數(shù)據(jù)給出下拉的列表,非常的方便,如下圖當(dāng)我們?cè)谥悄芟吕休斎胄∶?下拉中僅僅會(huì)出現(xiàn)小米的選項(xiàng),而普通下拉無法輸入內(nèi)容,只能在下拉中選擇

一、3個(gè)函數(shù)

制作智能下拉菜單我們是使用函數(shù)完成的，在這里我們需要用到3個(gè)函數(shù)：offset、match以及countif函數(shù)，對(duì)于match以及countif函數(shù)都是我們常用的函數(shù)，在這里就不多介紹了，我們來了解下這offset函數(shù)的作用以及參數(shù)

Offset函數(shù):offset是一個(gè)偏移函數(shù)，它以一個(gè)基點(diǎn)為原點(diǎn)進(jìn)行偏移得到一個(gè)新的偏移區(qū)域

第一參數(shù)：參照區(qū)域，以選擇的區(qū)域作為偏移基點(diǎn)

第二參數(shù)：行數(shù)，將基點(diǎn)區(qū)域在行方向移動(dòng)多少行單元格

第三參數(shù)：列數(shù)，將以行方向移動(dòng)過的區(qū)域，再以列方向移動(dòng)多少個(gè)單元格

第四參數(shù)：高度，將第一第二參數(shù)移動(dòng)過后的新區(qū)域取多少列

第五參數(shù)：寬度，將第一第二參數(shù)移動(dòng)過后的新區(qū)域取多少行

第2到第5參數(shù)如果不填寫則需省略

offset函數(shù)會(huì)根據(jù)一個(gè)單元格的位置，移動(dòng)得到另一個(gè)新的數(shù)據(jù)區(qū)域，它返回的結(jié)果是一個(gè)區(qū)域，并不是一個(gè)單元格，所以常與函數(shù)進(jìn)行嵌套使用，這個(gè)函數(shù)經(jīng)常用于制作動(dòng)態(tài)圖表

二、制作智能下拉

首先我們需要對(duì)數(shù)據(jù)進(jìn)行排序，這一點(diǎn)非常重要，如果不排序是不能達(dá)到這樣的效果的，然后我們點(diǎn)擊想要制作智能下拉的單元格，點(diǎn)擊數(shù)據(jù)找到數(shù)據(jù)驗(yàn)證，在允許中找到序列，然后輸入函數(shù)：

=OFFSET($A$1,MATCH(C2&"*",$A:$A,0)-1,,COUNTIF($A:$A,C2&"*"))

緊接著我們點(diǎn)擊出錯(cuò)警告，將輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告前面的對(duì)勾去掉，點(diǎn)擊確定，這樣的話智能下拉就完成了

下面跟大家簡單的介紹下函數(shù)

=OFFSET($A$1,MATCH(C2&"*",$A:$A,0)-1,,COUNTIF($A:$A,C2&"*"))

第一參數(shù)：基點(diǎn)，$A$1，基點(diǎn)單元格，也就是我們表頭的位置，需絕對(duì)引用

第二參數(shù)：移動(dòng)的行數(shù) ，MATCH(C2&"*",$A:$A,0)-1，在這里match函數(shù)的作用是查找在下拉中輸入的數(shù)據(jù)在A列的位置，第一參數(shù)：C2&"*"，在這里星號(hào)是通配符代表任意多個(gè)字符，比如我們?cè)趩卧裰休斎雟ivo。就是查找以vivo開頭的單元格的位置，因?yàn)橛兄貜?fù)值的存在，函數(shù)僅僅會(huì)返回都一個(gè)查找的結(jié)果，在這里我們需要將查找結(jié)果減去1是因?yàn)橛斜眍^的存在，如果沒有表頭的話在這里就不用減去1了

第三參數(shù)：移動(dòng)的列數(shù)，以為這個(gè)僅有一列，所以我們可以將第三參數(shù)省略

第四參數(shù)：偏移后區(qū)域的高度，COUNTIF($A:$A,C2&"*")，在這里我們使用countif計(jì)數(shù)同樣的在這里我們也使用了C2&"*"，我們假設(shè)單元格中輸入vivo，他就會(huì)統(tǒng)計(jì)以vivo開頭的單元格的個(gè)數(shù)

第五參數(shù)：偏移后區(qū)域的寬度，僅為數(shù)據(jù)僅有1列，所以可以省略第五參數(shù)

因?yàn)閛ffset獲得是一個(gè)數(shù)據(jù)區(qū)域，當(dāng)我們輸入不同的數(shù)據(jù)，函數(shù)就會(huì)返回不同的數(shù)據(jù)區(qū)域，從而達(dá)到智能下拉的效果