Hello,大家好,大家工作中有沒有遇過這樣的情況,下拉菜單非常的項目很多多,想手動輸入還被提示輸入錯誤,這個時候我們只能一個一個的點選非常的麻煩,效率很低,今天就跟大家分享下如何制作智能式下拉菜單,可以根據我們輸入的數(shù)據給出下拉的列表,非常的方便,如下圖當我們在智能下拉中輸入小米,下拉中僅僅會出現(xiàn)小米的選項,而普通下拉無法輸入內容,只能在下拉中選擇

一、3個函數(shù)

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

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

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

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

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

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

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

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

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

二、制作智能下拉

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

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

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

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

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

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

第二參數(shù)：移動的行數(shù) ，MATCH(C2&"*",$A:$A,0)-1，在這里match函數(shù)的作用是查找在下拉中輸入的數(shù)據在A列的位置，第一參數(shù)：C2&"*"，在這里星號是通配符代表任意多個字符，比如我們在單元格中輸入vivo。就是查找以vivo開頭的單元格的位置，因為有重復值的存在，函數(shù)僅僅會返回都一個查找的結果，在這里我們需要將查找結果減去1是因為有表頭的存在，如果沒有表頭的話在這里就不用減去1了

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

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

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

因為offset獲得是一個數(shù)據區(qū)域，當我們輸入不同的數(shù)據，函數(shù)就會返回不同的數(shù)據區(qū)域，從而達到智能下拉的效果

智能下拉的制作還是需要一定的函數(shù)基礎的，如果你覺得難的話，可以直接使用上面的函數(shù)，替換相應的單元格位置即可

我是excel從零到一，關注我持續(xù)分享更多excel技巧