excel數(shù)組公式創(chuàng)建無(wú)重復(fù)項(xiàng)的數(shù)據(jù)有效性下拉列表

excel數(shù)組公式創(chuàng)建無(wú)重復(fù)項(xiàng)的數(shù)據(jù)有效性下拉列表

有時(shí)需要以一列數(shù)據(jù)為來(lái)源制作數(shù)據(jù)有效性下拉列表，但當(dāng)該列數(shù)據(jù)包含重復(fù)值時(shí)，下拉列表中也會(huì)包含這些重復(fù)的項(xiàng)目，如下圖中A列包含一些水果名稱，在C3單元格中直接用數(shù)據(jù)有效性創(chuàng)建的下拉列表就包含了多個(gè)重復(fù)項(xiàng)，顯得不夠簡(jiǎn)潔。

要在數(shù)據(jù)有效性下拉列表中僅顯示這列數(shù)據(jù)中的唯一值，可以先將該列數(shù)據(jù)中的唯一值提取到輔助列中，再創(chuàng)建下拉列表，具體方法如下：

1.用數(shù)組公式提取唯一值。

從一列數(shù)據(jù)中獲取唯一值的公式有很多，如下面的數(shù)組公式。假如數(shù)據(jù)在A2:A21區(qū)域，以B列為輔助列，在B2單元格輸入：

=INDEX($A:$A,MATCH(0,COUNTIF($B:B1,$A:$A),0))

公式輸入完畢后按Ctrl+Shift+Enter結(jié)束，然后拖動(dòng)填充柄向下填充公式，直到出現(xiàn)“#N/A“為止，這樣就將A列中的唯一值提取到B列。

2.定義名稱。

由于B列中還包含錯(cuò)誤值“#N/A“，用下面的數(shù)組公式可動(dòng)態(tài)引用B列中不包含錯(cuò)誤值的區(qū)域B2:B9：

=OFFSET($B,,,MATCH(TRUE,ISERROR($B:$B),0)-1,1)

但如果直接將該公式用于數(shù)據(jù)有效性，會(huì)出現(xiàn)重新打開(kāi)工作簿后無(wú)法打開(kāi)下拉列表的問(wèn)題，因而需將其定義為一個(gè)名稱后再應(yīng)用于數(shù)據(jù)有效性。

Excel 2003：?jiǎn)螕舨藛巍安迦搿Q→定義”，彈出“定義名稱”對(duì)話框，在“在當(dāng)前工作簿中的名稱”下的文本框中輸入一個(gè)名稱，如“weiyizhi”，在“引用位置”下輸入上述公式，單擊“確定”。

Excel 2007/2010：在“公式”選項(xiàng)卡的“定義的名稱”組中單擊“名稱管理器”，分別輸入一個(gè)名稱和上述公式，如圖：

單擊“確定”，關(guān)閉“名稱管理器”。

3.設(shè)置數(shù)據(jù)有效性。

假如需要在C3單元格中設(shè)置數(shù)據(jù)有效性下拉列表，選擇C3單元格：

Excel 2003：?jiǎn)螕舨藛巍皵?shù)據(jù)→有效性”；

Excel 2007/2010：在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)有效性→數(shù)據(jù)有效性”；

在彈出的“數(shù)據(jù)有效性”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，在“允許”下選“序列”，在“來(lái)源”下的文本框中輸入公式：

=weiyizhi

單擊“確定”。這樣C3單元格中的數(shù)據(jù)有效性下拉列表就只顯示A列中的唯一值。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。