提高輸入時的準確性和便利性，辦公軟件數據表的數據有效性驗證
2022-04-04 12:16:25
很多人可(ke)(ke)能知道(dao)，使用Excel【數據(ju)有效(xiao)性】當中的(de)【序(xu)列】功(gong)能，可(ke)(ke)以在(zai)單(dan)元(yuan)格內創(chuang)建一(yi)個(ge)下拉(la)(la)菜單(dan)，在(zai)進行輸入時可(ke)(ke)以在(zai)下拉(la)(la)菜單(dan)中選擇項目。這(zhe)樣(yang)的(de)下拉(la)(la)菜單(dan)可(ke)(ke)以提高用戶輸入時的(de)準(zhun)確性和便(bian)利性。
本文所(suo)要介紹的(de)【自適應】的(de)下(xia)拉(la)(la)菜單(dan)，就是(shi)可以根據用戶(hu)在(zai)單(dan)元格里輸(shu)入的(de)字符，在(zai)下(xia)拉(la)(la)菜單(dan)的(de)顯示(shi)項(xiang)(xiang)目中自動(dong)篩選(xuan)出以這(zhe)些字符開(kai)頭的(de)項(xiang)(xiang)目，縮(suo)小(xiao)下(xia)拉(la)(la)菜單(dan)中的(de)項(xiang)(xiang)目選(xuan)擇范圍，使目標更精準(zhun)，方便(bian)用戶(hu)選(xuan)取。這(zhe)是(shi)一種對數據有(you)效性序列的(de)智能(neng)化改造(zao)手(shou)段。
具(ju)體(ti)設(she)置方(fang)法如(ru)下：
步驟1，將需要(yao)作(zuo)為選擇項目(mu)的原(yuan)始數據進(jin)行排序。排序以后，相同字符開頭的字符串將分布在連(lian)續的單(dan)元(yuan)格中，便(bian)于創建數據有效性的引(yin)用序列。
步驟(zou)2，選(xuan)中需要(yao)設置下(xia)拉菜(cai)單的單元(yuan)格，打開(kai)【數據有效性(xing)】對話框，選(xuan)擇【序列】，并且在【來源】中使用以下(xia)公(gong)式：
=OFFSET($A$1,MATCH(C2&"*",$A:$A,0)-1,,COUNTIF($A:$A,C2&"*"))
其中，其中A列是之前排(pai)過(guo)序的項目數據源(yuan)所在列，C2 則是當前選中的單元(yuan)格。
上述公式的(de)具(ju)體含義如下：
MATCH(C2&"*",$A:$A,0)
這部分可以(yi)(yi)在(zai)A列(lie)中查找以(yi)(yi)C2當中字符打頭的(de)項目，返回其中找到(dao)的(de)第一(yi)個項目的(de)行號
COUNTIF($A:$A,C2&"*")
這(zhe)部分(fen)公式在A列(lie)中統計(ji)以C2當中字符打頭的(de)項目的(de)個(ge)數
以(yi)上面(mian)圖中的數據情(qing)況為例，
MATCH(C2&"*",$A:$A,0) = 4
COUNTIF($A:$A,C2&"*") = 12
整個公(gong)式(shi)等效于：
=OFFSET($A$1,4-1,,12)
這個(ge)OFFSET函數公式的作用(yong)是(shi)形成一個(ge)引用(yong)區域(yu)，即以A1單元格向下(xia)偏移3行（A4單元格），以此(ci)單元格起始的12行單元格區域(yu)為引用(yong)范圍。
這個公式(shi)的(de)整體作(zuo)用(yong)就是在A列數(shu)據源中(zhong)提(ti)取出了(le)以(yi)C2單(dan)元(yuan)格當中(zhong)字(zi)符(fu)開始的(de)所(suo)有項目。以(yi)這個提(ti)取出來(lai)的(de)區(qu)域作(zuo)為數(shu)據有效(xiao)性序列的(de)引用(yong)源，就可(ke)以(yi)形成一個可(ke)以(yi)動(dong)(dong)態(tai)變化、自動(dong)(dong)適應單(dan)元(yuan)格輸入(ru)內容的(de)下拉菜單(dan)。
步驟3，選中【數(shu)據(ju)有效性】的【出(chu)錯警(jing)告】選項(xiang)(xiang)卡，取消勾選【輸入無(wu)效數(shu)據(ju)時顯示出(chu)錯警(jing)告】選項(xiang)(xiang)。
這(zhe)個操作(zuo)步驟的目(mu)的是為了在單元(yuan)格當中輸(shu)入(ru)不完整的項目(mu)字符串時，系(xi)統(tong)不會因為數據(ju)有效性(xing)的錯誤(wu)警告而阻(zu)止用(yong)戶的輸(shu)入(ru)。
本文所(suo)要介紹的(de)【自適應】的(de)下(xia)拉(la)(la)菜單(dan)，就是(shi)可以根據用戶(hu)在(zai)單(dan)元格里輸(shu)入的(de)字符，在(zai)下(xia)拉(la)(la)菜單(dan)的(de)顯示(shi)項(xiang)(xiang)目中自動(dong)篩選(xuan)出以這(zhe)些字符開(kai)頭的(de)項(xiang)(xiang)目，縮(suo)小(xiao)下(xia)拉(la)(la)菜單(dan)中的(de)項(xiang)(xiang)目選(xuan)擇范圍，使目標更精準(zhun)，方便(bian)用戶(hu)選(xuan)取。這(zhe)是(shi)一種對數據有(you)效性序列的(de)智能(neng)化改造(zao)手(shou)段。
具(ju)體(ti)設(she)置方(fang)法如(ru)下：
步驟1，將需要(yao)作(zuo)為選擇項目(mu)的原(yuan)始數據進(jin)行排序。排序以后，相同字符開頭的字符串將分布在連(lian)續的單(dan)元(yuan)格中，便(bian)于創建數據有效性的引(yin)用序列。
步驟(zou)2，選(xuan)中需要(yao)設置下(xia)拉菜(cai)單的單元(yuan)格，打開(kai)【數據有效性(xing)】對話框，選(xuan)擇【序列】，并且在【來源】中使用以下(xia)公(gong)式：
=OFFSET($A$1,MATCH(C2&"*",$A:$A,0)-1,,COUNTIF($A:$A,C2&"*"))
其中，其中A列是之前排(pai)過(guo)序的項目數據源(yuan)所在列，C2 則是當前選中的單元(yuan)格。
上述公式的(de)具(ju)體含義如下：
MATCH(C2&"*",$A:$A,0)
這部分可以(yi)(yi)在(zai)A列(lie)中查找以(yi)(yi)C2當中字符打頭的(de)項目，返回其中找到(dao)的(de)第一(yi)個項目的(de)行號
COUNTIF($A:$A,C2&"*")
這(zhe)部分(fen)公式在A列(lie)中統計(ji)以C2當中字符打頭的(de)項目的(de)個(ge)數
以(yi)上面(mian)圖中的數據情(qing)況為例，
MATCH(C2&"*",$A:$A,0) = 4
COUNTIF($A:$A,C2&"*") = 12
整個公(gong)式(shi)等效于：
=OFFSET($A$1,4-1,,12)
這個(ge)OFFSET函數公式的作用(yong)是(shi)形成一個(ge)引用(yong)區域(yu)，即以A1單元格向下(xia)偏移3行（A4單元格），以此(ci)單元格起始的12行單元格區域(yu)為引用(yong)范圍。
這個公式(shi)的(de)整體作(zuo)用(yong)就是在A列數(shu)據源中(zhong)提(ti)取出了(le)以(yi)C2單(dan)元(yuan)格當中(zhong)字(zi)符(fu)開始的(de)所(suo)有項目。以(yi)這個提(ti)取出來(lai)的(de)區(qu)域作(zuo)為數(shu)據有效(xiao)性序列的(de)引用(yong)源，就可(ke)以(yi)形成一個可(ke)以(yi)動(dong)(dong)態(tai)變化、自動(dong)(dong)適應單(dan)元(yuan)格輸入(ru)內容的(de)下拉菜單(dan)。
步驟3，選中【數(shu)據(ju)有效性】的【出(chu)錯警(jing)告】選項(xiang)(xiang)卡，取消勾選【輸入無(wu)效數(shu)據(ju)時顯示出(chu)錯警(jing)告】選項(xiang)(xiang)。
這(zhe)個操作(zuo)步驟的目(mu)的是為了在單元(yuan)格當中輸(shu)入(ru)不完整的項目(mu)字符串時，系(xi)統(tong)不會因為數據(ju)有效性(xing)的錯誤(wu)警告而阻(zu)止用(yong)戶的輸(shu)入(ru)。
- 上一篇：圖紙中的尺寸標注數字太小看不清楚，修改標注樣式將文字高度值調大 [2022-04-04]
- 下一篇：什么是平面設計？就是用一些特殊的操作來處理一些已經數字化的圖像的過程 [2022-04-03]