Excel數(shù)據(jù)有效性實(shí)例

1、它有如下功能什么是數(shù)據(jù)有效性？數(shù)據(jù)有效性一個包含幫助你在工作表中輸入資料提示信息的工具 -給用戶提供一個選擇列表-限定輸入內(nèi)容的類型或大小-自定義設(shè)置防止輸入重復(fù)值防止在工作表一定范圍輸入重復(fù)值本例中，在單元格B3:B10中輸入的是員工編號1. 選擇單元格 B3:B102. 選擇數(shù)據(jù)|有效性3. 在“允許”下拉框中選擇“自定義”4. 在“公式”框中，使用COUNTIF函數(shù)統(tǒng)計B3出現(xiàn)次數(shù)，在$B$3:$B$10范圍內(nèi).結(jié)果必須是1或0:=COUNTIF($B$3:$B$10,B3)<=1限定總數(shù)防止一個范圍數(shù)據(jù)總數(shù)超過指定值.本例中,預(yù)算不能超過$3500.預(yù)算總額統(tǒng)計的單元格在C3:C

2、7范圍內(nèi)1. 選擇單元格C3:C72. 選擇數(shù)據(jù)|有效性3. 在“允許”下拉框中選擇“自定義”4. 在“公式”框中，使用SUM函數(shù)統(tǒng)計$C$3:$C$7合計值.結(jié)果必須小于或等于$3500:=SUM($C$3:$C$7)<=350SAB 11fffiS”|輸入信息出櫓警告輸入法摸式2 |1 1有效性條件3允許(A)：jL自總文三|P掘略空值匹15II數(shù)據(jù)6II階于7g|=B2=TRIM （B2）丸g沒有前置或后置間隔防止用戶在輸入文本前面或后面加入空白間隔.TRIM函數(shù)移除文本前后空白間隔.1. 選擇單元格B22. 選擇數(shù)據(jù)|有效性3. 在“允許”下拉框中選擇“自定義”4. 在“公式”框

3、中,輸入：=B2=TRIM(B2)防止輸入周末日期|防止輸入的日期為星期六或星期日.WEEKDAY 將輸入的日期返回到星期，并且不允許其值為 1 (星期日)和7(星期六).1. 選擇單元格B22. 選擇數(shù)據(jù)|有效性3. 在“允許”下拉框中選擇“自定義”在“公式”框中，輸入：=AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)AB |1設(shè)置j輸入信息i出錯雪吉|輛入法模式i21 1皙效性據(jù)件3 0允許®:d r1自定義二w愈數(shù)據(jù)助：56e二17慫式迥8|=AKD （WEEKDAY（B2 ） <>1, WEKDAy（02）仝J9創(chuàng)建下

4、拉列表選項(xiàng)使用數(shù)據(jù)有效性可以為一個單元格創(chuàng)建一個選擇輸入內(nèi)容的下拉列表.列表數(shù)據(jù)項(xiàng)可以在工作表的行或列中輸入，也可以直接在數(shù)據(jù)有效性對話框中輸入.1. 創(chuàng)建列表數(shù)據(jù)項(xiàng)a.在一個半單行或單列中輸入你想在下拉列表中看到的條目2. 命名列表范圍如果你在一個工作表中輸入了一個有效性列表?xiàng)l目，并且給它定義了名稱，你就可以在同一工作簿的其它工作表的數(shù)據(jù)有效性對話框中引用這個名稱1. 選擇列表單元格范圍12 3 4-56 2. 點(diǎn)擊公式編輯欄左邊的名稱框(Name Box)3. 定義一個名稱，如：FruitList.4. 按回車鍵.3. 應(yīng)用數(shù)據(jù)有效性a.選擇你想應(yīng)用數(shù)據(jù)有效性的單元格b“數(shù)據(jù)有效性”.c.

5、 點(diǎn)擊"允許”框右側(cè)的下拉箭頭，在列表中選擇"序列”d. 在來源對話框中輸入一個等號和列表名稱，如：=FruitListe. 點(diǎn)擊確定.你可以使用定義一個范圍和INDIRECT函數(shù)在數(shù)據(jù)有效性列表中根據(jù)前一單元格內(nèi)容限制選擇條目本例中，如果你在類別中選擇水果，在名稱下拉列表僅顯示水果類名稱A衛(wèi)J1種棗品名2水臬1香越！*3蕈果45創(chuàng)建名稱列表 首先命名單元格范圍本示例中，第一個列表定義的名稱為農(nóng)產(chǎn)品它包括的條目有-水果和蔬菜1. 創(chuàng)建第一個名稱列表a）在工作簿的空白區(qū)域，輸入你想在下拉列表中看到的條目它必須是一個詞條,且與所屬的品名名稱相匹配 b）選擇列表包含的單元格（不包

6、括標(biāo)題）.c）點(diǎn)擊公式編輯欄左側(cè)名稱框.d）為列表輸入一個名稱，例如：農(nóng)產(chǎn)品.e）按回車鍵農(nóng)產(chǎn)艮 HA|c12衣產(chǎn)品列表3水果 蔬某662. 創(chuàng)建對應(yīng)第一個名稱列表的名稱列表a）輸入你想在農(nóng)產(chǎn)品列表類別之一下拉列表中看到的詞條b）選擇包含這些詞條的單元格列表c）點(diǎn)擊公式編輯欄左側(cè)的名稱框.d）為這個類別所屬的品名列表定義一個名稱，例如：水果.這個名稱必須與農(nóng)產(chǎn)品列表中所屬類別名稱正確匹配e）按回車鍵水果二|=Ja1 C123農(nóng)產(chǎn)品列表水果蔬菜水果列表:瘧5香蕉|檸穢6*Tf）用同樣的方法創(chuàng)建類別中其它條目所屬的列表-本例中為蔬菜襦榮 二I-白萊A K BC I DEFP農(nóng)產(chǎn)品列表水果列表蔬菜列

7、表3朮果蘋果白菜4蔬菜生菜5檸權(quán)6r1桃子應(yīng)用數(shù)據(jù)有效性 在種類（也就是上述的類別）列表的單元格右側(cè)有一個下拉箭頭顯示可以選擇輸入的 類別在品名列表單元格數(shù)據(jù)有效性中使用了INDIRECT函數(shù)創(chuàng)建了一個下拉列表.1. 應(yīng)用數(shù)據(jù)有效性a）選擇你想在數(shù)據(jù)有效性中應(yīng)用類別列表的單元格b）從“數(shù)據(jù)”菜單中選擇“有效性”文件世編視圖M S&A tX）格式工具數(shù)據(jù)窗口幫助100%V有效性QL）.AB| CDE種類品名冰果園數(shù)據(jù)透視表和圖表報告団益©收藏夾前往雄八F71E:M水果A2分列丄104 5 6 7 B荻取外部數(shù)據(jù)）c）在"允許”下拉列表中選擇"序列”d）在“來

8、源”框中，輸入一個等號和序列名稱，例如：=農(nóng)產(chǎn)品e）點(diǎn)擊“確定”.2. 創(chuàng)建所屬的數(shù)據(jù)有效性a）選擇依附類別單元格中已經(jīng)輸入條目（水果或蔬菜）并與這些條目匹配輸入的應(yīng) 用數(shù)據(jù)有效性的單元格b）從“數(shù)據(jù)”菜單中選擇“有效性”c）在“允許”下拉列表中選擇“序列”d）在“來源”框中，輸入一個引用到類別列對應(yīng)單元格的INDIRECT函數(shù)，:=INDIRECT（A2）e）點(diǎn)擊“確定”.AB 1種類品名2水果1香蕉FKSZH輸入信怠|出錯警告|輸入法複式345允許®:"g-|序列數(shù)據(jù)：F7 19910|=INDIRECTU2)N11測試數(shù)據(jù)有效性種類列單元格將顯示農(nóng)產(chǎn)品列表品名列將根據(jù)

9、種類列已經(jīng)輸入的類別顯示水果或蔬菜列表應(yīng)用兩個詞條有時你可能需要在第一個下拉列表中應(yīng)用兩個詞條例如，你可以選擇紅色水果'綠色 水果'和黃色水果'1. 用上述方法創(chuàng)建第第一個名稱范圍和下拉列表2. 應(yīng)用一個詞條創(chuàng)建對應(yīng)的列表，例如：紅色水果，綠色水果，黃色水果3. 在允許下拉框中選擇序列，在來源框中使用一個公式移除名稱中間隔.例如:=INDIRECT(SUBSTITUTE(A2," "，"")果匱垂3 :456790EFGH1紅色球卑 黃色瑋果 餘色水舉10111213U161010在名稱中使用非法字符有時在第一個名稱范圍下拉列表中

10、名稱中可能你要用到定義名稱不支持的非法字符，比如連接符(&).例如，你選擇的條目分別是紅色水果'，'綠色水果和黃色&橙色水果1. 用上述方法創(chuàng)建第第一個名稱范圍和下拉列表2. 使用一個詞條名稱創(chuàng)建一個支持的名稱列表，例如：紅色水果，綠色水果，黃色或橙色水果3. 創(chuàng)建一個包含第一個下拉列表名稱的查詢表格4. 在毗鄰單元格輸入正確的名稱5. 命名這個表格，女口： NameLookup6. 在允許下拉框中選擇序列，在來源框中使用一個公式查找正確的名稱.例如：=INDIRECT(VLOOKUP(A2,NameLookup,2,0)LAR 1D J1i2綠色水果日314i

11、ff抿秘567紅色?K果黃邑&機(jī)色水果 綠色木果蘋果 草莓普蕉檸嘯游猴桃8耳紅色水果紅色水果10黃色也橙魚水臬董色或擅色水果11綠色*果綠色卄果1* 卸KU?L色水里 劭色僉聊色齊黑 tfeAH1| cD 1聲豈1虹色馭K色或隹M T 伸® nFGH1JWiXi | h岸翳告 | Wi> ；*iaj |陽.|自酋性蓋忤嚴(yán)題_-l-IK'IILEnTviZoElIKMrJluXdj. ? Aj盤醉畫耳LID A C1314使用動態(tài)列表因?yàn)镮NDIRECT函數(shù)的作用僅為引用，并非公式，前面的方法不能工作于動態(tài)列表你可以使用下面的方法替代它：1. 用上述方法 創(chuàng)建第第

12、一個名稱范圍和下拉列表2. 創(chuàng)建支持的名稱列表，并且命名第個范圍的第一個單元格，例如：單元格B1命名為“水果”且單元格C1命名為“蔬菜”.3. 用每個找到的列表命名列，例如：B列命名為“水果 Col”，C列命名為“蔬 菜 Col ”在允許下拉框中選擇序列，在來源框中使用一個公式推算查找范圍.例如，如果第一個下拉列表在單元格E2。則公式為：=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col"),1)Excel -數(shù)據(jù)有效性一在下拉列表中隱藏前面使用過的條目在數(shù)據(jù)有效性列表中你可以限制選擇條目，隱藏前面選擇過的條目例如，某

13、公司分派員工出差，為避免重復(fù)派出，即已經(jīng)派出在外的員工再次派出則會造成失誤。這時，你就可以設(shè)計在有效性下拉列表中，移除已經(jīng)使用過的條目（即已經(jīng)派出的員工不會再出現(xiàn)在侯選列表中）設(shè)置主表首先設(shè)計你想要運(yùn)用數(shù)據(jù)有效性的表格布局.本例中，使用了工作表時間表中的A1:C7單元格范圍.在B列使用了數(shù)據(jù)有效性創(chuàng)建列表?xiàng)l目創(chuàng)建一個你想在數(shù)據(jù)有效性下拉列表看到條目的列表 A1:A6,單元格輸入員工姓名在這里，在工作表“員工”的創(chuàng)建有效列表A）輸入一個統(tǒng)計已經(jīng)派出的員工名單的公式1. 在“員工”工作表的 B1單元格輸入下列公式：=IF（COUNTIF（時間表！$B$2:$B$7,A1）=1,"&quo

14、t;,ROW（）BDEa910111213AA丄|程香宙3程花A張鳳玲5赫建新王強(qiáng)在數(shù)據(jù)有 皴性下拉 列表申便 用的條目 列董用佇式判斷名稱 是百在時間青滬 出現(xiàn)過使用斂組公式將空白 單元格移動到底咅亂Bl=IFCOUMTIJ 耐間S?!SB$2 $前1 AL )>=!., : ROW 0)k 1變香宙2345嘩新王幅VC2. 將公式向下復(fù)制到 B6單元格.這個公式統(tǒng)計”程香宙”在工作表“時間表”中B2:B7區(qū)域出現(xiàn)次數(shù).如果返回結(jié)果大于或等于1,單元格將顯示為空白.否則則返回行數(shù).B）創(chuàng)建一個沒有使用的名稱列表下一步將使用一個數(shù)組公式將空白的單元格移動到序列的最后邊1. 在C1單元格輸

15、入數(shù)組公式（這個公式很長，但在同一行輸入完成）.【=TF dSNUMB?R 倡 MALL (JF CfB$l: tB$E= "”JUW(SE$l;)膽W1; H) 1IWDIRECT ("A" ASM ALL EH ($時1: $B$E="“,ROW »B$1:$M8)X BDK Q：l)£*a1便用叛組泌式將仝白 單元格移動到底部。=IF(ISNUMBER(SMALL(IF($B$1:$B$6="","",ROW($B$1:$B$6),ROW(1:1),INDIRECT("A&quo

16、t;&SMALL(IF($B$1:$B$6="","",ROW($B$1:$B$6),ROW(1:1),"")2. 按組合鍵Ctrl+Shift+Enter輸入數(shù)組公式3. 向下復(fù)制公式直到 C6單元格.定義有效列表名稱1. 選擇插入名稱自定義2. 在名稱框中輸入一個名稱，例如NameCheck.3. 在引用框中，輸入下面公式(在一行內(nèi)):=OFFSET員工 s!$C$1,O,O,COUNTA(員工 s!$C$1:$C$6)-COUNTBLANK(員工 s!$C$1:$C$6),1)4. 點(diǎn)擊確定應(yīng)用數(shù)據(jù)有效性1. 選擇你要應(yīng)

17、用數(shù)據(jù)有效性的單元格2. 選擇數(shù)據(jù)菜單下的有效性.3. 從允許下拉列表中，選擇序列4. 在來源框中，輸入一個等號和序列名稱，例如：=NameCheck5. 點(diǎn)擊確定.測試數(shù)據(jù)有效性B列下拉列表中僅顯示沒有使用過的姓名已經(jīng)使用過的姓名已經(jīng)被移除（即已經(jīng)派出的員工不會再次被派出）Excel - 數(shù)據(jù)有效性-添加提示信息你可以給使用電子表格的人員提示信息在選擇有數(shù)據(jù)有效性的單元格時顯示輸入信息如果輸入無效數(shù)據(jù)則顯示出錯警告輸入信息a）選擇你要應(yīng)用數(shù)據(jù)有效性的單元格b）在設(shè)置標(biāo)簽下應(yīng)用需要的數(shù)據(jù)有效性（什么是數(shù)據(jù)有效性?）c）點(diǎn)擊輸入信息標(biāo)簽d）選中選定單元格時顯示輸入作息祓畳II 1出譜誓骨輸入法槿

18、式（7迤洋單元格時顯示輸入?yún)栂⑦x定|$格時顯示下列輸入信息：e）在標(biāo)題框內(nèi)輸入信息標(biāo)題文本.這個文本將發(fā)粗體顯示在提示框的頂部f）在輸入信息框中輸入提示信息g）點(diǎn)擊確定或進(jìn)行下一項(xiàng)對出錯警告進(jìn)行設(shè)置選定單元格時顯示下列輸入信息: 鞋題：輸入信息（D:從下拉列表中選擇一平類別31出錯警告a）點(diǎn)擊出錯警告標(biāo)簽b）選中輸入無效數(shù)據(jù)時顯示出錯警告設(shè)置輸入信息出情警告輸入法模式|輸入無效數(shù)據(jù)時顯示岀錯警告 卞入無效數(shù)據(jù)時昱示下列出諸警告；c）從樣式下拉列表中選擇一種出錯警告樣式.1. 中止：其作用是防止輸入無效數(shù)據(jù).如果點(diǎn)擊 重試按鈕，則輸入的無效數(shù)據(jù)突出顯示，你可以重新輸入如果點(diǎn)擊取消按鈕，則自動刪除

19、無效數(shù)據(jù)，單元格恢復(fù)原始的內(nèi)容 這樣可禁止使用者在單元格中輸入無效數(shù)據(jù)2. 警告：為輸入無效數(shù)據(jù)設(shè)置一個障礙如果點(diǎn)擊是按鈕,則接受無效數(shù)據(jù)輸入,并選擇下一個單元格 如果點(diǎn)擊否按鈕,無效數(shù)據(jù)突出顯示,可以重新輸入.如果點(diǎn)擊取消按鈕,無效數(shù)據(jù)被自動刪除，單元格恢復(fù)原始內(nèi)容 這樣可使使用者選擇是否在單元格中輸入無效數(shù)據(jù)A I BCDE456789103. 作息：輸入無效數(shù)據(jù)時給出提示信息如果點(diǎn)擊確定按鈕，則接受無效數(shù)據(jù)輸入，并選擇下一個單元格 如果點(diǎn)擊 取消按鈕,無效數(shù)據(jù)被自動刪除，單元格恢復(fù)原始內(nèi)容 這樣可使使用者選擇是否在單元格中輸入無效數(shù)據(jù)d）在標(biāo)題框中輸入標(biāo)題文本.這個文本將以粗體形式顯示在

20、彈出的信息窗口上部f）在出錯信息框中輸入要顯示的信息g）點(diǎn)擊確定設(shè)置I輸入信息岀諸善告：I筍入芾模式丨輸入無效數(shù)據(jù)時顯示出錯警告 輸入無奴數(shù)據(jù)時顯示下列出措警告：樣式:信息標(biāo)題：I無效類別 出楮信息:你輸入的類別在列表中不存在Jd注解：如果Ofice助手是打開的，則會彈出下面的提示Excel -數(shù)據(jù)有效性-使用源于其它工作簿的列表你可以使用其它工作簿中的列表作為數(shù)據(jù)有效性下拉列表?xiàng)l目要使數(shù)據(jù)有效性能夠正常運(yùn)行，包含列表的工作簿必須是打開的 開,但可以隱藏起來，比如1.xls 工作簿你可以使這個工作簿總是打創(chuàng)建數(shù)據(jù)源列表假定有一個名為1.xls的工作簿,其中有一個名稱范圍被定義為Name.引用到

21、數(shù)據(jù)源1. 打開你想在其中使用數(shù)據(jù)有效性的工作簿2. 選擇插入名稱 自定義3. 輸入列表名稱，如顧客名單4. 在引用到框中，輸入名稱范圍的引用首先是等號，接著是帶單引號的工作表名和一個 感嘆號,最后是定義的名稱,例如='1.xls'!name5. 點(diǎn)擊確定在當(dāng)前工作髯中的名稱®:馬創(chuàng)建下拉列表1. 選擇你要使用數(shù)據(jù)有效性的單元格2. 選擇數(shù)據(jù)有效性3. 在允許框中，選擇序列4. 在來源框中輸入列表名稱，前面有一個等號，例如：=顧客名單5. 點(diǎn)擊確定數(shù)基砂性廠對有同樣設(shè)直的所有耳世單元格應(yīng)用這些更改（1）取消 |Excel -數(shù)據(jù)有效性-有效性條件示例整數(shù) 設(shè)置或排除一定范圍內(nèi)的數(shù)值 ，也可以自定義最小值或最大值1. 在數(shù)據(jù)有效性對話框中輸入值,或者2. 引用到工作表中的單元格，或者3. 使用公式設(shè)置值設(shè)直j輸入信怠岀錯警告1輸入注模式肖效性龜牛