Excel函數(shù)數(shù)據(jù)有效性例題大全

1、*Excel函數(shù)與數(shù)據(jù)有效性配合快速填通知書用Excel函數(shù)中的vlookup查詢函數(shù)和數(shù)據(jù)有效性功能配合來填寫通知書,可以免去老師們一個一個寫的繁瑣勞動，這下不用寫到手抽筋了！第一步：處理學(xué)生成績把學(xué)生的期末考試成績放在Sheetl表中，算出每個學(xué)生的成績總分，為了在后面輸函數(shù)公式時方便，我在前面加了一列序號"。把Sheetl表重命名為 考試成績?nèi)鐖D1所示。A.藩CDEFGOilI1f期末考試成績2序號姓名語文數(shù)學(xué)外語物理初學(xué)生物總分31張110811788988259142張2111132863S7658653張3109127S139的7857864褊11487i:3SB8557

2、675張6124118803491771-7486隹&122134r 8s73827157097張工_115132313491655比1081201127299?可口 yes成二 hd-p119,電107116799737弋告試成績Sheets/ Sheets /IIUJL第二步：設(shè)置通知書”模版在考試成績”表旁的空白表Sheet2中，設(shè)置好 通知書”的基本格式和文字內(nèi)容，頁面設(shè)置為 B5紙，底色可以設(shè)置為默認。如圖 2所示。A & C D 1 E F G通知書. I2 .3 一賈家歸：,. I ,.H 般們好.根據(jù)教育局規(guī)定，我校于6月羽日開始放假,時間為8月29日 )至g月

3、3日中假期期間，里各位家長看護好孩子的安全，同時還要密切配合 卷好學(xué)校.督促募子技時定應(yīng)限期作也ri sail同學(xué)的成演版一I23 到 2?評 語S長答空*.目匚璉物右擊表 通知書”的A1單元格，選擇 設(shè)置單元格格式”命令，彈出 單元格格式”對話框，選擇 字體”選項卡，把字體顏色設(shè)置為白色“，確定” 即可。如圖3所示。它的作用在后面就會體現(xiàn)出來。設(shè)置好后把此表表名重命名為通知書”。第三步：插入查詢函數(shù)”在通知書”表的C3單元格輸入函數(shù) “=Vlookup（A1,考試成績！A3 : J43, 2, FALSE）”,如圖 4 所示。23212224第三言統(tǒng)申羋5TJMX 2 * ElboJnjpl

4、,考試度繳1A3: J4工 Z fal*)B i C ) '廣 q f通知書貴家長您們好，根據(jù)教育局規(guī)定. 我校于6月28日開始放假，時間為6月29日 至9月3日-假期期間.望各位家長看樂好我子的妄全.同時還要密切配合 好學(xué)校，督促技子按時完成假期作業(yè).S二 3班 |=vio心upGl,考試A3: J4X 2, ialse)|9語文數(shù)學(xué)英語物理化學(xué)）生物總分in111213P_*15 16_ ：叵20此公式的含義是：使用Vlookup查詢函數(shù)，根據(jù) A1單元格的內(nèi)容，在考試成績”表白A A3到J43單元格中進行查詢，把查詢到相同內(nèi)容的這行 的第2個單元格的內(nèi)容顯示在 C3單元格中。即根

5、據(jù)A1單元格的內(nèi)容，把 考試成績表中與之相同內(nèi)容的這行的第2個單元格的姓名提取到此單元格。由此在A10單元格中輸入函數(shù)“=Vlookup（A1,考試成績！A3 : J43, 3,FALSE）”，理解了 C3、A10單元格的函數(shù)后，根據(jù)同樣的原理我們分別如 法設(shè)置 B10、C10、D10、E10、F10、G10 就可以了。第四步：設(shè)置評語的 有效性選中B11單元格，即 評語”左邊的空白單元格，選擇菜單欄中的 數(shù)據(jù)'-有效性”命令，彈出 數(shù)據(jù)有效性”對話框，選擇 設(shè)置”選項卡，設(shè)置 允許' 條件為 序列“，在 來源”的內(nèi)容框中輸入你對學(xué)生的評語，注意在每個評 語后面用半角逗號（,）

6、擱開，如：該生在校能夠尊敬師長，團結(jié)同學(xué)，努力 學(xué)習(xí)。，該生平時熱愛勞動，刻苦學(xué)習(xí)，能夠幫助同學(xué)?！辈粠б枺Ｈ鐖D5所示。單擊確定”命令。第五步：打印通知書在A1單元格中輸入一個序號后按回車健,在表 考試成績”中與之相同序號的這一行的學(xué)生信息就會自動提取在表通知書”的相應(yīng)的各單元格中。然后根據(jù)這個學(xué)生的平時情況，在 評語欄中單擊下列箭頭按鈕選擇合適的評語就可以了。點擊打印”命令，一張完美的學(xué)生通知書就會呈現(xiàn)在眼前。如圖 6所示。通知書敬堂夷唐北爭生物宜分117$858985乳建厚生在棧柳叁蜃麗盤.四結(jié)網(wǎng)季.罌當(dāng)季q._* * .a _ J同學(xué)的成單單悠們皙.嘏幗教育孰在，我枝鳧月盤日月始旅科

7、,時間怎月加日 至q月之日-胃舒解間，里芯枚家K務(wù)融耙核4的史金.同時江雯窘初配合 好事按，爵促候干挨對完成藥期柞業(yè).二、3旺 和1第喘瞿!嘉心”由于在前面對 A1單元格的字體已設(shè)置為白色，與背景色一致，因此在打印時不至于顯出來而影響通知書的美觀。應(yīng)用一下拉菜單輸入的實現(xiàn)例1 :直接自定義序列有時候我們在各列各行中都輸入同樣的幾個值，比如說，輸入學(xué)生的等級時我們只輸入四個值：優(yōu)秀，良好，合格，不合格。我們希望Excel2000 單元格能夠象下拉框一樣，讓輸入者在下拉菜單中選擇就可以實現(xiàn)輸入。操作步驟：先選擇要實現(xiàn)效果的行或列；再點擊"數(shù)據(jù) 有效性 " ，打開" 數(shù)

8、據(jù)有效性"對話框；選擇 "設(shè)置"選項卡，在 "允許"下拉菜單中選擇"序列 " ；在 "數(shù)據(jù)來源"中輸入"優(yōu)秀，良好，合格，不合格 " （注意要用英文輸入狀態(tài)下的逗號分隔?。?；選上"忽略空值"和"提供下拉菜單"兩個復(fù)選框。點擊"輸入信息"選項卡，選上 "選定單元格顯示輸入信息" ， 在"輸入信息 "中輸入 " 請在這里選擇 " 。例 2 ：利用表內(nèi)數(shù)據(jù)作為序列源。有時候

9、序列值較多， 直接在表內(nèi)打印區(qū)域外把序列定義好， 然后引用。操作步驟：先在同一工作表內(nèi)的打印區(qū)域外要定義序列填好（假設(shè)在在Z1 ： Z8 ），如“單親家庭，殘疾家庭，殘疾學(xué)生，特困，低收人，突發(fā)事件，孤兒，軍烈屬 ”等，然后選擇要實現(xiàn)效果的列（資助原因）；再點擊"數(shù)據(jù) 有效性 " ，打開 "數(shù)據(jù)有效性 "對話框；選擇"設(shè)置"選項卡，在"允許"下拉菜單中選擇 "序列 " ； “來源 ”欄點擊右側(cè)的展開按鈕（有一個紅箭頭），用鼠標(biāo)拖動滾動條，選中序列區(qū)域 Z1 ： Z8 （如果記得，可以直接輸入=$

10、Z$1:$Z$8 ；選上 "忽略空值 "和"提供下拉菜單 "兩個復(fù)選框。點擊"輸入信息 "選項卡，選上"選定單元格顯示輸入信息" ，在"輸入信息" 中輸入 "請在這里選擇 " 。例 3 ：橫跨兩個工作表來制作下拉菜單用 INDIRECT 函數(shù)實現(xiàn)跨工作表在例 2 中， 選擇來源一步把輸入 =$Z$1:$Z$8 換成 =INDIRECT（" 表二 !$Z$1:$Z$8"）, 就可實現(xiàn)橫跨兩個工作表來制作下拉菜單。應(yīng)用二 自動實現(xiàn)輸入法中英文轉(zhuǎn)換有時，我們在不

11、同行或不同列之間要分別輸入中文和英文。我們希望Excel 能自動實現(xiàn)輸入法在中英文間轉(zhuǎn)換。操作步驟：假設(shè)我們在 A 列輸入學(xué)生的中文名， B 列輸入學(xué)生的英文名。先選定 B 列，點擊進入"數(shù)據(jù)有效性 " ，打開"數(shù)據(jù)有效性"對話框；選擇 " 輸入法 " 對話框，在 " 模式 " 下拉菜單中選擇" 關(guān)閉（英文模式） " ；然后再" 確定 " ，看看怎么樣。應(yīng)用三 數(shù)據(jù)唯一性檢驗員工的身份證號碼應(yīng)該是唯一的，為了防止重復(fù)輸入，我們用“數(shù)據(jù)有效性”來提示大家。操作步驟：選中需要建立

12、輸入身份證號碼的單元格區(qū)域(如 B2 至 B14歹U),執(zhí)行 數(shù)據(jù)-有效性”命令，打開 數(shù)據(jù)有效性”對話框，在 設(shè)置”標(biāo)簽下，按 “允許 ”右側(cè)的下拉按鈕，在隨后彈出的快捷菜單中，選擇 “自定義 ”選項，然后在下面 “公式 ”方框中輸入公式： =COUNTIF(B:B,B2)=1 ，確定返回。以后在上述單元格中輸入了重復(fù)的身份證號碼時，系統(tǒng)會彈出提示對話框，并拒絕接受輸入的號碼。通過 Excel 數(shù)據(jù)有效性防止重復(fù)輸入數(shù)據(jù)在 Excel 中錄入數(shù)據(jù)時，有時會要求某列或某個區(qū)域的單元格數(shù)據(jù)具有唯一性，如身份證號碼、發(fā)票號碼之類的數(shù)據(jù)。但我們在輸入時有時會出錯致使數(shù)據(jù)相同，而又難以發(fā)現(xiàn)，這時可以通

13、過 “數(shù)據(jù)有效性 ”來防止重復(fù)輸入。例如我們要在B2:B200來輸入身份證號，我們可以先選定單元 格區(qū)域B2:B200,然后單擊菜單欄中的“數(shù)據(jù)”一“有效性 ”命令，打開“數(shù)據(jù)有效性 ”對話框，在 “設(shè)置 ”選項下，單擊 “允許 ”右側(cè)的下拉按鈕，在彈出的下拉菜單中，選 擇 “自定義”選項，然后在下面“公式”文本框中輸入公 式"二COUNTIF（$B$2:$B$200,$B2）=1 "（不包括引號），選“確 定”后返回（如圖1） o以后再在這一單元格區(qū)域輸入重復(fù)的號碼時就會彈出提示對話框了（如圖2）。Excel中的數(shù)據(jù)有效性在Excel中，我們可以約束某個欄位只能輸入某些值

14、，這些值可以是固定的序列，也 可以是某些單元格。下面我們來看看這兩種方式如何設(shè)置 （以下截圖是在 Excle2007中，Excel2003類似）：1、固定的序列【步驟11通過菜單【數(shù)據(jù)】->【數(shù)據(jù)有效性】->【數(shù)據(jù)有效性】進入【數(shù)據(jù)有效性】面板:Rook：- Microsoft fxcelE_ j H I 1 J i【步驟2】【允許】選擇【序列】，然后在來源中輸入固定值“ New,Update,Delete以英文逗號隔開：【步驟3】效果如下:2、來源為某些單元格：【步驟u同方式i【步驟2】將鼠標(biāo)點中來源，然后圈選 A1A3:【步驟3】效果同方式。注意：第一種方式不靈活，但是可以拷貝

15、到其他的Excel中直接使用；第二種方式由于引用了Excel中的單元格，不能拷貝到其他Excel中，也不難拷貝到同一個Excel文檔的其他Sheet中，只能在同一個 Sheet中使用.在excel2003中定義有效性標(biāo)準(zhǔn)要定義允許輸人到單元格或者范圍中數(shù)據(jù)的類型，步驟如下：選擇單元格或者范圍，選擇菜單欄的“數(shù)據(jù)”一一“有效性”，Excel顯示“數(shù)據(jù)有效性”對話框 單擊“數(shù)據(jù)有效性”對話框中的“設(shè)置”選項卡，從“允許”下拉框中選擇個選項， 要定義公式，選擇“自定義”。從“數(shù)據(jù)”下拉框中選擇定義條件。所做的選擇決定 可以訪問的其他控制，如下圖所示：數(shù)據(jù)有效件設(shè)量物入信息出錯警告輸入法模式有旗性條伴

16、允許的:數(shù)數(shù)列期間本定 些小序日r交目司忽喈空值5)對有同樣設(shè)置的所有其觸單元格厘用這些更改當(dāng)部清除電)確定 取消I單擊“輸入消息”選項卡，并且定義當(dāng)用戶選擇了該單元格，要顯示哪個信息?？梢?使用這個選項逐步告訴用戶需要什么樣的數(shù)據(jù)類型，如圖所示單擊“出錯警告”選項卡，并且定義當(dāng)用戶進行了無效的輸入，要顯示哪個錯誤信息 風(fēng)格的選擇確定當(dāng)輸入了無效項時用戶選擇什么。要防止無效輸入，選擇停止。這一 步是可選的。單擊“確定”按鈕關(guān)閉“數(shù)據(jù)有效性”對話框。執(zhí)行了這些步驟之后，單元格或者范 圍就包吉了所定義的有效性標(biāo)準(zhǔn)。EXCEL關(guān)于數(shù)據(jù)有效性的應(yīng)用使單元格區(qū)域內(nèi)記錄不能重復(fù)輸入控制=COUNTIF(A

17、:A,A2)=1 ( 直接復(fù)制此公式進去即可)禁止單元格輸入數(shù)字控制=ISNUMBER(A1)<>TRUE允許單元格只能輸入數(shù)字控制=ISNUMBER(A1)=TRUE禁止單元格輸入字母和數(shù)字=LENB(A1)=2禁止輸入周末日期=AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7)特定前綴輸入 : 應(yīng)該含某個字開頭=OR(LEFT(A1)="張",LEFT(A1)="李")禁止單元格前后輸入多余空格=A1=TRIM(A1)禁止輸入數(shù)字大于某某值 =A1<=100禁止輸入限定的值>=MAX(

18、A:A) 同 <>"" 同 =""限定區(qū)域輸入的和的最大值=SUM(A1:A10)<100有效性應(yīng)用實例一： 我的 ID 你別用教師經(jīng)常要用 Excel 制作表格， 錄入學(xué)生信息， Excel 強大的制表功能， 給教師工作帶 來了方便，但是在表格數(shù)據(jù)錄入過程中難免會出錯，一不小心就會錄入一些錯誤的數(shù)據(jù)，比如重復(fù)的身份證號碼，超出范圍的無效數(shù)據(jù)等。其實，只要合理設(shè)置“數(shù)據(jù)有效性”規(guī)則，就可以避免錯誤。為了便于管理學(xué)生信息，每個學(xué)生都有屬于自己獨有的ID （學(xué)號），在信息錄入時，學(xué)生ID不允許重復(fù)，如果在Excel錄入重復(fù)的ID,就會給信息

19、管理帶來不便，我們可以對Excel "數(shù)據(jù)有效性”進行設(shè)置，拒絕錄入重復(fù)數(shù)據(jù)。fW的SI粕牌余式切換到“數(shù)據(jù)功UD的列（如A列）.按鈕.在F拉菜單IT含井法爸噌受臺，N打腳，了曲Iki，.清坤g燈阪軍一i設(shè)置 輸入守：出錯警肯_輸入法模式有效性條件 一|打下G允許”下拉框,選擇 允I4:恒定聲一"自定義”.在“公式”一欄中輸入 =countif（a:a?a 1）=1''蜃式3）7rI 1* _»! - I I .一 I一一 » a-= coujit； £ （a,二、al ）= l|E*；對營同祥設(shè)置的魴有復(fù)地單元格應(yīng)用這些更改1

20、）確定取消0在A列中輸入ID.當(dāng)輸入的ID出現(xiàn)重復(fù) 時,Excel立刻彈出錯誤警告.提示我們輸 人有誤.單擊“否” .關(guān)閉提示框,重新 輸入正確的ID,避免數(shù)據(jù)重復(fù)有效性應(yīng)用實例二：快速揪出無效數(shù)據(jù)有些數(shù)據(jù)是有范圍限制的， 比如以百分制記分的考試成績必須是 0100之間的某個數(shù)據(jù)，錄入此范圍之外的數(shù)據(jù)就是無效數(shù)據(jù)，如果采用人工審核的方法，要從浩瀚的數(shù)據(jù)中找到無效數(shù)據(jù)是件麻煩事，我們可以用Excel的“數(shù)據(jù)有效性”，快速揪出表格中的無效數(shù)據(jù)。全部清解應(yīng)）能石同樣設(shè)置2打開“允許”下拉相.選擇 , “小數(shù)”,打開“數(shù)據(jù)”下拉 框.選擇“介于”.最小值設(shè) 為0,最大值設(shè)為100,單擊“確定”按鈕屋小

21、值圓）_0-最*值QP|ioqL_t允許也白費敷據(jù)電）：一汴十 K豆石 ，£一【I”I打開需要進行審核的Excel表格,選中需 要審核的區(qū)域.切換到數(shù)據(jù)”功能區(qū). 單擊“數(shù)據(jù)有效性” 一 “數(shù)據(jù)有效性”胃逑航i峭人頁面布漏公式805'895800199199的橢圓形圈釋出來.錯誤數(shù) 據(jù)一目了然單擊“數(shù)據(jù)有效性一數(shù)據(jù)星會司女性有效性"，稍等片刻，表格 中所有無效數(shù)據(jù)被一個紅色:國輝無皿教病清除無效數(shù)1S樂識因B）現(xiàn)在學(xué)生的身份證號已經(jīng)全部都是18位的新一代身份證了，里面的數(shù)字都是有規(guī)律的。前6位數(shù)字是戶籍所在地的代碼，714位就是出生日期。第17位“2代表的是性別，偶

22、數(shù)為女性，奇數(shù)為男性。我們要做的就是把其中的部分數(shù)字想法提取出來STEpI ,轉(zhuǎn)換身份證號碼格式我們先將學(xué)生的身份證號完整地輸入到Excel2003表格中，這時默認為數(shù)字”格式(單元格內(nèi)顯示的是科學(xué)記數(shù)法的格式)，需要更改一下數(shù)字格式。選中該列中的所有身份證號后，右擊鼠標(biāo)，選擇 設(shè)置單元格格式在彈出對話框中 數(shù)字”標(biāo)簽內(nèi)的 分類” 設(shè)為文本”，然后點擊確定。STEP2 ,提取出”出生日期將光標(biāo)指針放到 出生日期”列的單元格內(nèi)，這里以 C2單元格為例。然后輸入 “=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&a

23、mp;MID(B2,13,2)&"曰""(注意：外側(cè)的雙引號不用輸入，函數(shù)式中的引號和逗號等符號應(yīng)在英文狀態(tài)下輸入)?；剀嚭?，你會發(fā)現(xiàn)在C2單元格內(nèi)已經(jīng)出現(xiàn)了該學(xué)生的出生日期。然后，選中該單元格后拖動填充柄，其它單元格內(nèi)就會出現(xiàn)相應(yīng)的出生日期。如圖 1。:起wMiMTlDC則篩 加0® *A(Xi福武如 TAG 徽變電)-口 如 帚勖咐*vrev出生日朋加8831g痢加二MIDG21 7, 白北"年"&11D(62-1I, 2)修"月"fellD電7,1& 幻也圖1通過上述方法，系統(tǒng)自動獲取

24、了出生年月日信息小提示：MID函數(shù)是EXCEL提供的一個 從字符串中提取部分字符”的函數(shù)命令，具體 使用格式在EXCEL中輸入MID后會出現(xiàn)提示。STEP3 ,判斷性別男女選中 性別'列的單元格，如 D2o 輸入 “=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")"(注意如上)后回車，該生是男還是女”已經(jīng)乖乖地判斷出來了。拖動填充柄讓其他學(xué)生的性別也自動輸入。如圖2r1000 HecnfIfflSU CD 特式叫 I* KJB ® ，«)用初制匕二試 ;，；1r I

25、I 一 二 QWJ .L.樂"11cg,cmWz皿q孩in笈.j男W期網(wǎng)合工01 flk名於翅TT微 姓氏軍-海魅1宜5七票37 口銀前 娥賺< -I_»G H I出生日期，性別 .L的年10月163 mHOHD醉7J“HTR1"除1小“卻"#； ”男”)上外侔L?月11日 一| E%*匕口工M* ganjF.trxL 131旺,#711陰|1烈佯如身疑日1轉(zhuǎn)辟08國09日圖2性別被自動填入指定位置這樣，通過兩個簡單的函數(shù)，我們就可以讓EXCEL從身份證號中自動提取出生日期和性別并填充到單元格內(nèi)，極大地減輕了我們的輸入工作量。最后效果如圖3。:嘮發(fā)

26、悻也產(chǎn)M編輯 視圖也 插入Q)格式工具9 數(shù)據(jù) 窗口阻二 . J一 . 1 - -; 0 -IB18， 啟| 明名著超壓船姓張李王超身份證號951211995Q32*出生日期1995年12月U日 1995年03月26日1995年08月09日圖3用EXECL可以自動識別并填入學(xué)生信息如何不啟用宏就不能使用Excel，不啟用宏就不能用,應(yīng)該怎么達到這種效果？用VBA做了個表個給其他部門用，但頭一次他們使用時都要將 安全級別設(shè)置成低時才能正常使用，有的干脆不啟用宏，填完數(shù)據(jù)就交上來，那些辛苦做的VBA功能根本沒用上。有什么辦法，讓他們不啟用宏就不能使用EXCEL簡單的辦法就是：Private Sub Workbook_BeforeClose(Cancel As Boolean)Sheetl.Visible = xlSheetHiddenEnd SubPrivate Sub Workbook_Open()Sheetl.Visible = xlSheetVisibleEnd Sub意思就是sheetl表默認是隱藏的，只要啟用宏，那么 sheetl表才會自動取消隱藏。復(fù)雜一點的辦法就是用老的宏表函數(shù)，也可以