學(xué)會(huì)多種動(dòng)態(tài)擴(kuò)展的方式，是熟練玩轉(zhuǎn)WPS表格的基礎(chǔ)！下面請(qǐng)和小編一起，一起學(xué)習(xí)關(guān)于WPS的動(dòng)態(tài)擴(kuò)展知識(shí)吧！

動(dòng)態(tài)擴(kuò)展

1. 圈釋無效數(shù)據(jù)

2. 動(dòng)態(tài)擴(kuò)展的下拉列表

圈釋無效數(shù)據(jù)

使用“圈釋無效數(shù)據(jù)”功能，能夠在已有的數(shù)據(jù)表中對(duì)數(shù)據(jù)的合法性進(jìn)行檢驗(yàn)和標(biāo)識(shí)。圖 3 - 19 所示，是某公司員工信息表的部分內(nèi)容，需要對(duì)C列年齡超出 18 ~ 60 歲范圍的記錄進(jìn)行標(biāo)記。

圖3-19 員工信息表

操作步驟如下。

步驟一：選中C 2 :C 12 單元格區(qū)域，依次單擊【數(shù)據(jù)】→【有效性】按鈕，彈出【數(shù)據(jù)有效性】 對(duì)話框。

步驟二：在【數(shù)據(jù)有效性】對(duì)話框的【設(shè)置】選項(xiàng)卡中，單擊【有效性條件】下方的【允許】下 拉按鈕，在下拉列表中選擇“整數(shù)”。 在【數(shù)據(jù)】下拉列表中選擇“介于”，在【最小值】 文本框中輸入“18”，【最大值】文本框中輸入“60”。單擊【確定】按鈕。

步驟三：保持C 2 :C 12 單元格區(qū)域的選中狀態(tài)，依次單擊【數(shù)據(jù)】→【有效性】下拉按鈕， 在下拉菜單中選擇【圈釋無效數(shù)據(jù)】命令，如圖 3 - 20 所示。

展開全文

圖3-20 圈釋無效數(shù)據(jù)

如果需要去掉驗(yàn)證標(biāo)識(shí)圈，可以依次單擊【數(shù)據(jù)】→【有效性】→【清除驗(yàn)證標(biāo)識(shí)圈】命 令，按組合鍵保存文檔即可。

圖3-21 錯(cuò)誤提示

動(dòng)態(tài)擴(kuò)展的下拉列表

圖 3 - 22 所示，是某公司員工委派計(jì) 劃表的部分內(nèi)容，要根據(jù)F列的對(duì)照表， 在B列生成下拉列表，要求能隨著F列數(shù) 據(jù)的增減，下拉菜單中的內(nèi)容也會(huì)自動(dòng) 調(diào)整。

圖3-22 員工委派計(jì)劃表

操作步驟如下。

步驟一：選中B 2 :B 8 單元格區(qū)域，依次單擊【數(shù)據(jù)】→【有效性】按鈕，打開【數(shù)據(jù)有效性】 對(duì)話框。

步驟二：在【設(shè)置】選項(xiàng)卡下的【允許】下拉列表中選擇“序列”。在【來源】編輯框中輸入以 下公式，單擊【確定】按鈕完成設(shè)置，如圖 3 - 23 所示。

圖3-23 使用公式作為有效性序列來源

OFFSET函數(shù)以F 2 作為基點(diǎn)，向下偏移 0 行，向右偏移 0 列，新引用的行數(shù)為COUNTA 函數(shù)統(tǒng)計(jì)到的F列非空單元格個(gè)數(shù)。結(jié)果減去 1，是因?yàn)镕 1 是表頭，所以在計(jì)數(shù)時(shí)要去掉。

設(shè)置完成后，F(xiàn)列有多少個(gè)非空單元格，在B列的下拉菜單中就顯示多少個(gè)對(duì)應(yīng)的選項(xiàng)，如圖 3 - 24 所示。

圖3-24 動(dòng)態(tài)擴(kuò)展的下拉列表

注意：

由于OFFSET函數(shù)新引用的行數(shù)為COUNTA函數(shù)的統(tǒng)計(jì)結(jié)果，因此在使用此方法時(shí)，F(xiàn)列中的 數(shù)據(jù)之間不能有空白單元格。

動(dòng)態(tài)二級(jí)下拉列表

圖 3 - 25 所示，是某公司客戶信息表的部分內(nèi)容，其中G∶H列是客戶所在城市和區(qū)、縣的 對(duì)照表，并且G列的城市已經(jīng)進(jìn)行了排序處理。

已經(jīng)在C列設(shè)置了客戶所在市的下拉列表，需 要在D列生成二級(jí)下拉列表，要求能隨著C列所選的一級(jí)菜單的不同，D列下拉菜單中的內(nèi)容也會(huì)自動(dòng)調(diào)整。

圖3-25 客戶信息表

操作步驟如下。

步驟一：選中D 2:D 5 單元格區(qū)域，依次單擊【數(shù) 據(jù)】→【有效性】按鈕，打開【數(shù)據(jù)有效 性】對(duì)話框。

步驟二：在【設(shè)置】選項(xiàng)卡下的【允許】下拉列表 中選擇“序列”。在【來源】編輯框中輸 入以下公式，單擊【確定】按鈕完成設(shè) 置，如圖 3 - 26 所示。

圖3-24 動(dòng)態(tài)擴(kuò)展的下拉列表

向右偏移的列數(shù)為 0 列。新引用的行數(shù)為COUNTIF函數(shù)的計(jì)算 結(jié)果 6。也就是先從G列中找到指定城市首次出現(xiàn)的位置，有多少個(gè)與該城市相同的單元格， OFFSET函數(shù)就引用多少行。

當(dāng)C 2 單元格中的城市名稱發(fā)生變化后，相當(dāng)于更改了MATCH函數(shù)和COUNTIF函數(shù)的查 找統(tǒng)計(jì)條件，新的計(jì)算結(jié)果反饋給OFFSET函數(shù)，最終返回不同城市對(duì)應(yīng)的區(qū)、縣單元格區(qū)域 的引用，如圖 3 - 27 所示。

圖3-27 動(dòng)態(tài)二級(jí)下拉列表

注意：

由于在數(shù)據(jù)有效性對(duì)話框中輸入公式時(shí)沒有函數(shù)名稱的屏幕提示，并且函數(shù)編輯框的顯示區(qū)域 較小，使公式的輸入較為不便。實(shí)際工作中，可以在工作表中先單擊要設(shè)置數(shù)據(jù)有效性的首個(gè)單元 格，然后以該單元格為參照輸入公式。

輸入完成后，在編輯欄中選中公式按組合鍵復(fù)制， 再打開【數(shù)據(jù)有效性】對(duì)話框，在序列【來源】編輯框中按組合鍵粘貼即可。在數(shù)據(jù)有效性中針對(duì)活動(dòng)單元格設(shè)置的規(guī)則，會(huì)自動(dòng)應(yīng)用到所選區(qū)域的每個(gè)單元格中。

學(xué)會(huì)了嗎？快來和小編一起，把WPS的動(dòng)態(tài)擴(kuò)展一起運(yùn)用到實(shí)踐中吧！

推薦閱讀

北京大學(xué)出版社

《WPS表格實(shí)戰(zhàn)技巧精粹》