教大家Excel中自適應(yīng)下拉菜單怎么設(shè)置

近日有關(guān)于Excel中自適應(yīng)下拉菜單怎么設(shè)置的問題受到了很多網(wǎng)友們的關(guān)注，大多數(shù)網(wǎng)友都想要知道Excel中自適應(yīng)下拉菜單怎么設(shè)置的具體情況，那么關(guān)于到Excel中自適應(yīng)下拉菜單怎么設(shè)置的相關(guān)信息，小編也是在網(wǎng)上進(jìn)行了一系列的信息，那么接下來就由小編來給大家分享下小編所收集到與Excel中自適應(yīng)下拉菜單怎么設(shè)置相關(guān)的信息吧(以下內(nèi)容來自于網(wǎng)絡(luò)非小編所寫，如有侵權(quán)請與站長聯(lián)系刪除)

Excel中自適應(yīng)下拉菜單怎么設(shè)置

本文所要介紹的自適應(yīng)的下拉菜單，就是可以根據(jù)用戶在單元格里輸入的字符，在下拉菜單的顯示項目中自動篩選出以這些字符開頭的項目，縮小下拉菜單中的項目選擇范圍，使目標(biāo)更精準(zhǔn)，方便用戶選取。這是一種對數(shù)據(jù)有效性序列的智能化改造手段。

完成后的效果如下：

具體設(shè)置方法如下：

步驟1：將需要作為選擇項目的原始數(shù)據(jù)進(jìn)行排序。

排序以后，相同字符開頭的字符串將分布在連續(xù)的單元格中，便于創(chuàng)建數(shù)據(jù)有效性的引用序列。

步驟2：選中需要設(shè)置下拉菜單的單元格，打開【數(shù)據(jù)有效性】對話框，選擇【序列】，并且在【來源】中使用以下公式：

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

其中，其中A列是之前排過序的項目數(shù)據(jù)源所在列，C2 則是當(dāng)前選中的單元格。

上述公式的具體含義如下：

MATCH(C2&"*",$A:$A,0)

這部分可以在A列中查找以C2當(dāng)中字符打頭的項目，返回其中找到的第一個項目的行號

COUNTIF($A:$A,C2&"*")

這部分公式在A列中統(tǒng)計以C2當(dāng)中字符打頭的項目的個數(shù)

以上面圖中的數(shù)據(jù)情況為例，

MATCH(C2&"*",$A:$A,0) = 4

COUNTIF($A:$A,C2&"*") = 12

整個公式等效于：

=OFFSET($A$1,4-1,,12)

這個OFFSET函數(shù)公式的作用是形成一個引用區(qū)域，即以A1單元格向下偏移3行(A4單元格)，以此單元格起始的12行單元格區(qū)域為引用范圍。

這個公式的整體作用就是在A列數(shù)據(jù)源中提取出了以C2單元格當(dāng)中字符開始的所有項目。以這個提取出來的區(qū)域作為數(shù)據(jù)有效性序列的引用源，就可以形成一個可以動態(tài)變化、自動適應(yīng)單元格輸入內(nèi)容的下拉菜單。

步驟3：選中【數(shù)據(jù)有效性】的【出錯警告】選項卡，取消勾選【輸入無效數(shù)據(jù)時顯示出錯警告】選項。

這個操作步驟的目的是為了在單元格當(dāng)中輸入不完整的項目字符串時，系統(tǒng)不會因為數(shù)據(jù)有效性的錯誤警告而阻止用戶的輸入。

最終完成效果如下：