Excel數(shù)據(jù)有效性使用方法090516

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

2、元格在C3:C7范圍內(nèi)選擇單元格C3:C7選擇數(shù)據(jù)I有效性在“允許”下拉框中選擇“自定義”在“公式”框中，使用SUM函數(shù)統(tǒng)計(jì)$C$3:$C$7合計(jì)值,結(jié)果必須小于或等于$3500:=SUM($C$3:$C$7)=350匚設(shè)置輸入信息出錯(cuò)警告|輸入法模式| 有效性條件-沒有前置或后置間隔防止用戶在輸入文本前面或后面加入空白間隔.TRIM函數(shù)移除文本前后空白間隔.選擇單元格B2選擇數(shù)據(jù)I有效性在“允許”下拉框中選擇“自定義”在“公式”框中，輸入：=B2=TRIM(B2)防止輸入周末日期防止輸入的日期為星期六或星期日.WEEKDAY將輸入的日期返回到星期，并且不允許其值為1 (星期日)和7 (星期六

3、).選擇單元格B2選擇數(shù)據(jù)|有效性在“允許”下拉框中選擇“自定義”在“公式”框中，輸入:=AND(WEEKDAY(B2)1,WEEKDAY(B2)7)創(chuàng)建下拉列表選項(xiàng)使用數(shù)據(jù)有效性可以為一個(gè)單元格創(chuàng)建一個(gè)選擇輸入內(nèi)容的下拉列表.列表數(shù)據(jù)項(xiàng)可以在工作表的行或列中輸 入，也可以直接在數(shù)據(jù)有效性對(duì)話框中輸入.創(chuàng)建列表數(shù)據(jù)項(xiàng)在一個(gè)半單行或單列中輸入你想在下拉列表中看到的條目.命名列表范圍如果你在一個(gè)工作表中輸入了一個(gè)有效性列表?xiàng)l目，并且給它定義了名稱，你就可以在同一工作簿的其它工作表 的數(shù)據(jù)有效性對(duì)話框中引用這個(gè)名稱.選擇列表單元格范圍.I.FruitList =寸 名贏廠點(diǎn)擊公式編輯欄左邊的名稱框(

4、Name Box)定義一個(gè)名稱，如：FruitList.按回車鍵.應(yīng)用數(shù)據(jù)有效性選擇你想應(yīng)用數(shù)據(jù)有效性的單元格“數(shù)據(jù)”一 “有效性”.點(diǎn)擊“允許”框右側(cè)的下拉箭頭，在列表中選擇“序列”在來源對(duì)話框中輸入一個(gè)等號(hào)和列表名稱，如:=FruitList輸發(fā)法模式點(diǎn)擊確定.數(shù)據(jù)有效性設(shè)置I輸入信息I出錯(cuò)警告有效性條件:允許兔r值 一 何數(shù)數(shù)任整小時(shí)間 文本長(zhǎng)度 自定義IF對(duì)有同樣設(shè)置的所有其他單元格應(yīng)用逮些更改(F) 你可以使用定義一個(gè)范圍和INDIRECT函數(shù)在數(shù)據(jù)有效性列表中根據(jù)前一單元格內(nèi)容 限制選擇條目.本例中，如果你在類別中選擇水果，在名稱下拉列表僅顯示水果類名稱.創(chuàng)建名稱列表 首先命名單元

5、格范圍.本示例中，第一個(gè)列表定義的名稱為農(nóng)產(chǎn)品.它包括的條目有-水果和蔬菜.創(chuàng)建第一個(gè)名稱列表a）在工作簿的空白區(qū)域，輸入你想在下拉列表中看到的條目.它必須是一個(gè)詞條，并 且與所屬的品名名稱相匹配.b）選擇列表包含的單元格（不包括標(biāo)題）.c）點(diǎn)擊公式編輯欄左側(cè)名稱框.d）為列表輸入一個(gè)名稱，例如：農(nóng)產(chǎn)品.e）按回車鍵.名稱框農(nóng)產(chǎn)品列表北果創(chuàng)建對(duì)應(yīng)第一個(gè)名稱列表的名稱列表a）輸入你想在農(nóng)產(chǎn)品列表類別之一下拉列表中看到的詞條b）選擇包含這些詞條的單元格列表.c）點(diǎn)擊公式編輯欄左側(cè)的名稱框.d）為這個(gè)類別所屬的品名列表定義一個(gè)名稱，例如:水果.這個(gè)名稱必須與農(nóng)產(chǎn)品列表中所屬類別名稱正確匹配.e）按回

6、車鍵.水果列表水果 =蘋果果蕉槌芋T香檸桃f）用同樣的方法創(chuàng)建類別中其它條目所屬的列表-本例中為蔬菜.應(yīng)用數(shù)據(jù)有效性 在種類（也就是上述的類別）列表的單元格右側(cè)有一個(gè)下拉箭頭顯示可以選擇輸入的 類別.在品名列表單元格數(shù)據(jù)有效性中使用了 INDIRECT函數(shù)創(chuàng)建了一個(gè)下拉列表.應(yīng)用數(shù)據(jù)有效性a）選擇你想在數(shù)據(jù)有效性中應(yīng)用類別列表的單元格b）從“數(shù)據(jù)”菜單中選擇“有效性”.耐s I M100%嗪:e atQ收藏夾.前往妲.國仔冒文件。編輯俱）視圖世）插a a）格式但）工具堡）數(shù)據(jù)云窗口地）幫助電）以排序窈.蔬選史）1=水果i BCDE_.品名n|數(shù)據(jù)透視表和圖表報(bào)告崖）.分列（!）.獲取外部數(shù)據(jù)魚

7、）有效性（!）.c）在“允許”下拉列表中選擇“序列”d）在“來源”框中，輸入一個(gè)等號(hào)和序列名稱，例如：=農(nóng)產(chǎn)品e）點(diǎn)擊“確定”.2.創(chuàng)建所屬的數(shù)據(jù)有效性a）選擇依附類別單元格中已經(jīng)輸入條目（水果或蔬菜）并與這些條目匹配輸入的應(yīng) 用數(shù)據(jù)有效性的單元格b）從“數(shù)據(jù)”菜單中選擇“有效性”.c）在“允許”下拉列表中選擇“序列”d）在“來源”框中，輸入一個(gè)引用到類別列對(duì)應(yīng)單元格的INDIRECT函數(shù)，：=INDIRECT（A2）e）點(diǎn)擊“確定”.測(cè)試數(shù)據(jù)有效性種類列單元格將顯示農(nóng)產(chǎn)品列表.品名列將根據(jù)種類列已經(jīng)輸入的類別顯示水果或蔬菜列表應(yīng)用兩個(gè)詞條 有時(shí)你可能需要在第一個(gè)下拉列表中應(yīng)用兩個(gè)詞條.例如，

8、你可以選擇紅色水果, 綠色 水果和黃色水果用上述方法創(chuàng)建第第一個(gè)名稱范圍和下拉列表.應(yīng)用一個(gè)詞條創(chuàng)建對(duì)應(yīng)的列表，例如：紅色水果，綠色水果，黃色水果在允許下拉框中選擇序列,在來源框中使用一個(gè)公式移除名稱中間隔.例如:=INDIRECT(SUBSTITUTE(A2, ,)在名稱中使用非法字符有時(shí)在第一個(gè)名稱范圍下拉列表中名稱中可能你要用到定義名稱不支持的非法字符比如連接符 (&).例如，你選擇的條目分別是紅色水果，綠色水果和黃色&橙色水果用上述方法創(chuàng)建第第一個(gè)名稱范圍和下拉列表.使用一個(gè)詞條名稱創(chuàng)建一個(gè)支持的名稱列表，例如：紅色水果，綠色水果，黃色或橙色 水果創(chuàng)建一個(gè)包含第一個(gè)下拉列表名稱的查詢表

9、格.在毗鄰單元格輸入正確的名稱命名這個(gè)表格，如：NameLookup在允許下拉框中選擇序列，在來源框中使用一個(gè)公式查找正確的名稱.例如：=INDIRECT(VLOOKUP(A2,NameLookup,2,0)BD1綠色水果庫果I.一一一一一iHLnI拶振僦5紅色水杲蘋果鮮橙日黃色俊橙邑水晃草莓檸臻狒猴桃嫁色水果紅色水果. 一一.紅色水果W黃色&橙色水果黃色或橙色水果11綠色水果綠:色丕果使用動(dòng)態(tài)列表因?yàn)镮NDIRECT函數(shù)的作用僅為引用，并非公式，前面的方法不能工作于動(dòng)態(tài)列表. 你可以使用下面的方法替代它：用上述方法創(chuàng)建第第一個(gè)名稱范圍和下拉列表.創(chuàng)建支持的名稱列表，并且命名第個(gè)范圍的第一個(gè)單

10、元格，例如：?jiǎn)卧馚1 命名為“水果”且單元格C1命名為“蔬菜”.用每個(gè)找到的列表命名列，例如：B列命名為“水果Col”，C列命名為“蔬 菜 Col”在允許下拉框中選擇序列，在來源框中使用一個(gè)公式推算查找范圍.例如，如果 第一個(gè)下拉列表在單元格E2。則公式為：=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&Col),1)Excel -數(shù)據(jù)有效性一在下拉列表中隱藏前面使用過的條目在數(shù)據(jù)有效性列表中你可以限制選擇條目，隱藏前面選擇過的條目.例如，某公司 分派員工出差，為避免重復(fù)派出，即已經(jīng)派出在外的員工再次派出則會(huì)造成失誤。這時(shí)，你就可以設(shè)計(jì)在有效性下拉列

11、表中，移除已經(jīng)使用過的條目（即已經(jīng)派出的員工不會(huì)再出現(xiàn)在侯選列表中）A 冬CD工香龍一 員程程B日 壬 日6 6出發(fā)日期日 6 月 6日日日 6 6 6 月月，月， 6 6- 6 5 6 7這些單元格應(yīng)用 了數(shù)據(jù)有效性，|_口設(shè)置主表首先設(shè)計(jì)你想要運(yùn)用數(shù)據(jù)有效性的表格布局.本例中，使用了工作表時(shí)間表中的 A1:C7單元格范圍.在B列使用了數(shù)據(jù)有效性.創(chuàng)建列表?xiàng)l目創(chuàng)建一個(gè)你想在數(shù)據(jù)有效性下拉列表看到條目的列表.在這里，在工作表“員工”的 A1:A6,單元格輸入員工姓名創(chuàng)建有效列表A）輸入一個(gè)統(tǒng)計(jì)已經(jīng)派出的員工名單的公式.在“員工”工作表的B1單元格輸入下列公式：=IF（COUNTIF（時(shí)間表!$

12、B$2:$B$7,A1）=1,ROW（）將公式向下復(fù)制到B6單元格.這個(gè)公式統(tǒng)計(jì)程香宙在工作表“時(shí)間表”中B2:B7區(qū)域出現(xiàn)次數(shù).如果返回結(jié) 果大于或等于1,單元格將顯示為空白.否則則返回行數(shù).B）創(chuàng)建一個(gè)沒有使用的名稱列表 下一步將使用一個(gè)數(shù)組公式將空白的單元格移動(dòng)到序列的最后邊在C1單元格輸入數(shù)組公式（這個(gè)公式很長(zhǎng)，但在同一行輸入完成）.=IF aSNUWBER 餌 MALL (IF (JB$1:疝扼ROW (JB$1:ROW (1:1), INDIRECT. 焰 MAIL (IF (JB$1:1使用數(shù)組公式將空白 單元格移動(dòng)到底部。呈香宙ROW 性斑1:$B$6),EOW (1:1),蜜

13、冰楨玲糙新=IF(ISNUMBER(SMALL(IF($B$1:$B$6=，,ROW($B$1:$B$6),ROW(1:1),INDIRECT(A&SMALL(IF($B$1:$B$6=，ROW($B$1:$B$6),ROW(1:1),)按組合鍵Ctrl+Shift+Enter輸入數(shù)組公式向下復(fù)制公式直到C6單元格.定義有效列表名稱選擇插入名稱自定義在名稱框中輸入一個(gè)名稱，例如NameCheck.在引用框中，輸入下面公式（在一行內(nèi)）：=OFFSET（員工 s!$C$1,0,0,COUNTA（員工 s!$C$1:$C$6）-COUNTBLANK （員工 s!$C$1:$C$6）,1）點(diǎn)擊確定應(yīng)用

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

15、典皆1輸人法模式 III咨琶羅元格時(shí)顯示輸入信息窯選迫加澗顯示下列輸入蓿息:e）在標(biāo)題框內(nèi)輸入信息標(biāo)題文本.這個(gè)文本將發(fā)粗體顯示在提示框的頂部.f）在輸入信息框中輸入提示信息.g）點(diǎn)擊確定或進(jìn)行下一項(xiàng)對(duì)出錯(cuò)警告進(jìn)行設(shè)置.選定單元格的顯示下列輸入信息：標(biāo)題要；輸入信息始：認(rèn)卞拉列表中辱一個(gè)類別出錯(cuò)警告a）點(diǎn)擊出錯(cuò)警告標(biāo)簽b）選中輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告教招有效性設(shè)置輸入信息出錯(cuò)警告|輸五法模式|P輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告 辱入無效數(shù)據(jù)時(shí)顯示下列出錯(cuò)警告：一c）從樣式下拉列表中選擇一種出錯(cuò)警告樣式.中止：其作用是防止輸入無效數(shù)據(jù).如果點(diǎn)擊重試按鈕，則輸入的無效數(shù)據(jù)突出顯示，你可以重新輸入.如果

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

17、窗口上部.f）在出錯(cuò)信息框中輸入要顯示的信息.g）點(diǎn)擊確定注解：如果Office助手是打開的，則會(huì)彈出下面的提示Excel -數(shù)據(jù)有效性-使用源于其它工作簿的列表你可以使用其它工作簿中的列表作為數(shù)據(jù)有效性下拉列表?xiàng)l目.要使數(shù)據(jù)有效性能夠正常運(yùn)行，包含列表的工作簿必須是打開的.你可以使這個(gè)工作簿總是打 開，但可以隱藏起來，比如1.xls工作簿.創(chuàng)建數(shù)據(jù)源列表假定有一個(gè)名為1.xls的工作簿，其中有一個(gè)名稱范圍被定義為Name.引用到數(shù)據(jù)源打開你想在其中使用數(shù)據(jù)有效性的工作簿.選擇插入名稱 自定義輸入列表名稱，如顧客名單在引用到框中，輸入名稱范圍的引用.首先是等號(hào)，接著是帶單引號(hào)的工作表名和一個(gè)

18、感嘆號(hào)，最后是定義的名稱，例如=1.xls!name點(diǎn)擊確定創(chuàng)建下拉列表選擇你要使用數(shù)據(jù)有效性的單元格.選擇數(shù)據(jù) 有效性在允許框中，選擇序列在來源框中輸入列表名稱，前面有一個(gè)等號(hào)，例如:=顧客名單點(diǎn)擊確定Excel -數(shù)據(jù)有效性-有效性條件示例整數(shù)設(shè)置或排除一定范圍內(nèi)的數(shù)值，也可以自定義最小值或最大值.在數(shù)據(jù)有效性對(duì)話框中輸入值，或者引用到工作表中的單元格，或者使用公式設(shè)置值小數(shù) 設(shè)置或排除一定范圍內(nèi)的數(shù)值，也可以自定義最小值或最大值.1.2.3.1.2.3.在數(shù)據(jù)有效性對(duì)話框中輸入值，或者 引用到工作表中的單元格，或者 使用公式設(shè)置值日期 設(shè)置或排除一定范圍內(nèi)的日期，也可以自定義最小值或最大值.在數(shù)據(jù)有效性對(duì)話框中輸入日期，或者引用到工作表中的單元格，或者使用公式設(shè)置日期本例中，當(dāng)前日期為起始日期，當(dāng)前日期加7天為結(jié)束日期.時(shí)間 設(shè)置或排除一定范圍內(nèi)的時(shí)間，也可以自定義最小值或最大值.1.1.在數(shù)據(jù)有效性對(duì)話