導(dǎo)讀背景不知道大家在工作中是否碰到過這樣的情況：進(jìn)行表格填寫時(shí)，為了規(guī)范填表人的填寫內(nèi)容，方便統(tǒng)計(jì)和數(shù)據(jù)處理，會(huì)在表格相應(yīng)位置設(shè)置下拉菜單，提供可選項(xiàng)。接下來主....背景

不知道大家在工作中是否碰到過這樣的情況：進(jìn)行表格填寫時(shí)，為了規(guī)范填表人的填寫內(nèi)容，方便統(tǒng)計(jì)和數(shù)據(jù)處理，會(huì)在表格相應(yīng)位置設(shè)置下拉菜單，提供可選項(xiàng)。接下來主要介紹兩類情況，一種是單項(xiàng)無關(guān)聯(lián)的下拉菜單，列出所有可選項(xiàng)即可；另一種是關(guān)聯(lián)下拉菜單，即后一列可選項(xiàng)會(huì)根據(jù)前一列所填內(nèi)容發(fā)生變化。

操作步驟-無關(guān)聯(lián)下拉菜單

首先選擇你要設(shè)置下拉菜單的單元格，在excel上方的菜單欄中，進(jìn)入“數(shù)據(jù)”菜單欄，找到“數(shù)據(jù)驗(yàn)證”，鼠標(biāo)左鍵單擊，即可出來如下對話框

在對話框“設(shè)置”頁面，“允許（A）”選擇“序列”，對話框?qū)⒊霈F(xiàn)如下變化：

接下來在“來源（S）”中填寫你需要設(shè)置下拉菜單中所包含的所有可選項(xiàng)即可，填寫有兩種方式：

1）方式一：直接手寫輸入，但必須注意，所有選項(xiàng)之間的分隔逗號必須是英文輸入法鍵入的，如果是在中文輸入法鍵入的，系統(tǒng)無法識別分隔，會(huì)出現(xiàn)如下情況：

如果按照在英文輸入法正確鍵入分隔逗號的話，下拉菜單才可設(shè)置成功，呈現(xiàn)效果如下：

2）方式二：首先在excel列舉所有可選項(xiàng)，點(diǎn)擊“來源（S）”下方框右側(cè)的箭頭進(jìn)行框選即可。本例中所有可選項(xiàng)的列項(xiàng)（C2-C5）與下拉菜單（A2）設(shè)置在同一張sheet中，實(shí)際工作中，為了美觀和避免誤導(dǎo)填表人，一般可將列項(xiàng)單獨(dú)放在一張新的sheet中，最后將列有所有列項(xiàng)的sheet進(jìn)行隱藏。

通過以上兩種方式設(shè)置好下拉菜單后，便可限制填表人在所有下拉菜單中進(jìn)行選擇，而不是隨意填寫了，如果填寫內(nèi)容并非下拉菜單中的列項(xiàng)，則系統(tǒng)會(huì)提示錯(cuò)誤，且無法填寫不匹配內(nèi)容：

操作步驟-關(guān)聯(lián)下拉菜單

接下來介紹關(guān)聯(lián)下拉菜單的操作步驟，這種情況主要用到無關(guān)聯(lián)下拉菜單設(shè)置方式中的方式二。

因此，我們首先把所有可選項(xiàng)的列項(xiàng)在新的sheet中（sheet2）列示：

關(guān)聯(lián)下拉菜單想要呈現(xiàn)的效果是：如果前一個(gè)單元格選擇蘋果，那后一個(gè)單元格只能選擇3、5、7、9四個(gè)數(shù)字中的一個(gè)，其余同理，即后一個(gè)單元格能選擇的內(nèi)容與前一個(gè)單元格所填內(nèi)容相關(guān)。

接下來，首先在A列設(shè)置第一個(gè)單元格的下拉菜單，與無關(guān)聯(lián)下拉菜單設(shè)置方式二步驟一致，不再詳述，如下所示：

然后在B列設(shè)置與A列相關(guān)的第二個(gè)下拉菜單，還是先打開“數(shù)據(jù)驗(yàn)證”，選擇“序列”，在“來源”下方框中輸入如下函數(shù)：

=OFFSET(Sheet2!$A$1,1,MATCH($A8,Sheet2!$A$1:$D$1,0)-1,4)

關(guān)于OFFSET和MATCH函數(shù)的介紹，在文章末尾添加了兩個(gè)鏈接，百度中均有非常詳細(xì)的解說，在此不再詳述。這里說明幾個(gè)參數(shù)的選擇，方便大家根據(jù)實(shí)際情況修改。

OFFSET參數(shù)1：所有可選項(xiàng)內(nèi)容左上角第一個(gè)元素所在的單元格。如前所述，本例將所有可選項(xiàng)內(nèi)容單獨(dú)在sheet2中列明，且從A1開始列示，故參數(shù)1選擇為“Sheet2!$A$1”；

OFFSET參數(shù)2：根據(jù)關(guān)聯(lián)下拉菜單的可選項(xiàng)開始行數(shù)確定，本例在關(guān)聯(lián)下拉菜單中所需要的可選項(xiàng)就是首行名稱下面對應(yīng)的該列數(shù)字，即從第2行開始，相對于參數(shù)1而言，就是往下移動(dòng)1行，故參數(shù)2設(shè)置為“1”；

OFFSET參數(shù)3：往右移動(dòng)的單元數(shù)，這個(gè)與前一個(gè)下拉菜單所填內(nèi)容相關(guān)，假如前一個(gè)單元格填寫為“菠蘿”，則關(guān)聯(lián)下拉菜單所需要的可選項(xiàng)就是第3列，菠蘿下方的所有數(shù)據(jù)，那往右移動(dòng)列數(shù)就是3，這個(gè)數(shù)字的確認(rèn)用到了MATCH函數(shù)。

MATCH參數(shù)1：前一個(gè)下拉菜單所在的單元格；

MATCH參數(shù)2：搜索的范圍，本例需要搜索前一個(gè)下拉菜單所填內(nèi)容在所有可選項(xiàng)名稱中的位置，即搜索范圍是sheet2第一行（名稱所在行），注意MATCH的搜索范圍只能是單行或單列；

MATCH參數(shù)3：0表示精準(zhǔn)匹配。

也就是說，假如前一個(gè)下拉菜單所填內(nèi)容是“菠蘿”，MATCH函數(shù)反饋的值就是3，那相對于OFFSET參數(shù)1而言，就是向右移動(dòng)2列，因此OFFSET參數(shù)3在MATCH函數(shù)的反饋值上減1；

OFFSET參數(shù)4：所需要返回的行數(shù)，這里所需數(shù)據(jù)為4行，因此需要返回4行，參數(shù)4設(shè)置為“4”；

OFFSET參數(shù)5：所需要返回的列數(shù)，這里需要數(shù)據(jù)所占列數(shù)為1，因此參數(shù)5為1，（為1時(shí)可以省略，所以本例函數(shù)中未寫）。

最后，可以把sheet2進(jìn)行隱藏，鼠標(biāo)在sheet2上右擊選擇隱藏即可。

至此便完成了關(guān)聯(lián)下拉菜單的設(shè)置，如果大家有什么更好的辦法希望可以互相交流。

---

OFFSET函數(shù)詳細(xì)介紹可參考以下鏈接：

https://jingyan.baidu.com/article/11c17a2c71716cf446e39dfb.html

MATCH函數(shù)詳細(xì)介紹可參考以下鏈接：

https://jingyan.baidu.com/article/b7001fe17db4ac4f7382dd5e.html