《Excel數(shù)據(jù)處理與可視化》教案3-數(shù)據(jù)規(guī)范

《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)規(guī)范授課類(lèi)型理實(shí)一體教學(xué)目標(biāo)利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入，包括規(guī)范的日期、時(shí)間、單位等利用函數(shù)規(guī)范數(shù)據(jù)重點(diǎn)1、利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入2、利用函數(shù)規(guī)范數(shù)據(jù)難點(diǎn)利用函數(shù)規(guī)范數(shù)據(jù)教學(xué)過(guò)程一利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入1規(guī)范性別輸入利用數(shù)據(jù)驗(yàn)證輸入性別，不僅規(guī)范而且快速。單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在打開(kāi)的“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“序列”，在“來(lái)源”中選擇“男,女”。特別注意：（1）（2）只要對(duì)一個(gè)單元格設(shè)置了數(shù)據(jù)驗(yàn)證，就可通過(guò)鼠標(biāo)拖動(dòng)單元格右下角填充柄，將數(shù)據(jù)驗(yàn)證的設(shè)置填充到其他單元格。2.限定輸入內(nèi)容在很多時(shí)候，要求只在某些特定值內(nèi)選擇輸入單元格的內(nèi)容。例如，如圖182所示的“評(píng)定等級(jí)”只有優(yōu)秀、良好、合格、不合格，所有姓名對(duì)應(yīng)的等級(jí)必須出自其中之一，而再無(wú)其他值，這時(shí)就可以利用數(shù)據(jù)驗(yàn)證來(lái)規(guī)范等級(jí)輸入。在“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“序列”，在“來(lái)源”中選擇4個(gè)等級(jí)所在的L2:L5區(qū)域，如圖182所示。設(shè)置了數(shù)據(jù)驗(yàn)證后，輸入等級(jí)時(shí)只要選擇其中之一就可以了。3.限定數(shù)值范圍可限定輸入數(shù)值范圍。（1）靜態(tài)限制輸入數(shù)值范圍。直接輸入“最大值”“最小值”，如圖183所示。（2）動(dòng)態(tài)限制輸入數(shù)值范圍。在設(shè)置了數(shù)據(jù)驗(yàn)證以后，可以通過(guò)修改“最小值”“最大值”單元格的數(shù)值，動(dòng)態(tài)調(diào)整數(shù)據(jù)的允許輸入數(shù)值范圍，如圖184所示。4.限定文本長(zhǎng)度在“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“文本長(zhǎng)度”，“數(shù)據(jù)”設(shè)為“等于”，“長(zhǎng)度”設(shè)為“11”，如圖185所示。5.限制輸入重復(fù)值單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“自定義”，在“公式”中輸入“=COUNTIF(H:H,H1)=1”，如圖1-86所示，單擊“確定”按鈕可以禁止輸入重復(fù)值。其中，公式的含義：H列中H1單元格的內(nèi)容只出現(xiàn)1次。如果H列中H1單元格的內(nèi)容出現(xiàn)次數(shù)超過(guò)1，則被禁止輸入。6.限定身份證號(hào)碼單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在打開(kāi)的“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“自定義”，在“公式”中輸入“=AND(LEN(D1)=18,COUNTIF(D:D,D1&"*")=1)”，如圖187所示。二、設(shè)置只能輸入規(guī)范的日期（1）設(shè)置日期格式。選中要填充日期的單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，在打開(kāi)的“設(shè)置單元格格式”對(duì)話框中，將“類(lèi)型”設(shè)置為要求輸入的格式，這里選擇常用的“*2012/3/14”格式，如圖189所示。（2）利用數(shù)據(jù)驗(yàn)證規(guī)范日期區(qū)間。選中要填充日期的單元格區(qū)域，在“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“日期”，“數(shù)據(jù)”設(shè)為“介于”，“開(kāi)始日期”設(shè)為“2000/1/1”，“結(jié)束日期”設(shè)為“=today()”，如圖190所示。選中“出錯(cuò)警告”選項(xiàng)卡，將“樣式”設(shè)為“停止”，“標(biāo)題”設(shè)為“請(qǐng)重新輸入：”，“錯(cuò)誤信息”設(shè)為“請(qǐng)輸入格式如2012/03/14的日期，并且起始日期介于2000/1/1與今天之間。”，如圖191所示。通過(guò)以上步驟的設(shè)置，單元格中就只允許輸入規(guī)范格式、特定區(qū)域的日期。三巧用數(shù)據(jù)驗(yàn)證規(guī)范時(shí)間格式（1）（2）單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在打開(kāi)的“數(shù)據(jù)驗(yàn)證”對(duì)話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“序列”，“來(lái)源”設(shè)為“=$E$2”，如圖1-93所示。在“輸入信息”選項(xiàng)卡中，在“輸入信息”中輸入“請(qǐng)輸入當(dāng)前時(shí)間”，如圖194所示。四數(shù)量單位“分”屬于文本，不能參與計(jì)算。所以，在寫(xiě)公式時(shí)，首先要把單位去除。去除數(shù)量單位“分”文本要用SUBSTITUTE函數(shù)，即{=SUBSTITUTE(B2:B10,"分",)}。因?yàn)橐M(jìn)行的是數(shù)組計(jì)算，所以按Ctrl+Shift+Enter組合鍵執(zhí)行計(jì)算。五一鍵添加“能計(jì)算”的數(shù)量單位選中數(shù)據(jù)單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框。在“分類(lèi)”中選擇“自定義”，直接在“類(lèi)型”的“G/通用格式”后輸入數(shù)量單位六使用多級(jí)聯(lián)動(dòng)菜單規(guī)范數(shù)據(jù)輸入（1）分級(jí)數(shù)據(jù)整理。（2）自定義名稱(chēng)。（3）建立各級(jí)菜單。七處理不能計(jì)算的“數(shù)值”1）選擇性粘貼2）數(shù)據(jù)分列3）VALUE函數(shù)4）SUMPRODUCT函數(shù)八規(guī)范全角、半角數(shù)據(jù)8規(guī)范全角、半角數(shù)據(jù)（1）在E3單元格中輸入公式“=ASC(D3)”，按Enter鍵執(zhí)行計(jì)算，然后將公式向下填充。ASC函數(shù)的作用是將全角（雙字節(jié)）字符轉(zhuǎn)換成半角（單字節(jié)）字符，如圖1-118所示。（2）在F3單元格中輸入公式“=WIDECHAR(D3)”，按Enter鍵執(zhí)行計(jì)算，然后將公式向下填充。WIDECHAR函數(shù)的作用是將半角（單字節(jié)）字符轉(zhuǎn)換成全角（雙字節(jié)）字符，如圖1-119所示。九數(shù)字與文本分離的方法1）函數(shù)法2）分列法3）快速填充法作業(yè)練習(xí)利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入教學(xué)小結(jié)本節(jié)知識(shí)：利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入設(shè)置只能輸入規(guī)范的日期巧用數(shù)據(jù)驗(yàn)證規(guī)范時(shí)間格式數(shù)據(jù)輸入不規(guī)范，部分?jǐn)?shù)據(jù)