二級(jí)excel高級(jí)操作-停車--解題步驟

1、計(jì)算機(jī)應(yīng)用基礎(chǔ)課程教學(xué)任務(wù)參考步驟設(shè)計(jì)者：計(jì)算機(jī)基礎(chǔ)教研室例3-2打開素材文件，按下列要求操作。舊A操作要求(1)在Sheet4的A1單元格中設(shè)置為只能錄入 5位數(shù)字或文本。當(dāng)錄入位數(shù)錯(cuò)誤時(shí)， 提示錯(cuò)誤原因，樣式為“警告”，錯(cuò)誤信息為“只能錄入 5位數(shù)字或文本”。(2)在Sheet4的B1單元格中輸入公式，判斷當(dāng)前年份是否為閏年，結(jié)果為TRUE或FALSE閏年定義：年數(shù)能被 4整除而不能被100整除，或者能被400整除的年份。(3)使用HLOOKU函數(shù)，對(duì)Sheet1 "停車情況記錄表”中的“單價(jià)”列進(jìn)行填充。要求：根據(jù)Sheet1中的“停車價(jià)目表”價(jià)格，使用 HLOOKU函數(shù)對(duì)“停

2、車情況記錄表” 中的“單價(jià)”列根據(jù)不同的車型進(jìn)行填充。注意：函數(shù)中如果需要用到絕對(duì)地址的請(qǐng)使用絕對(duì)地址進(jìn)行計(jì)算，其他方式無效。(4)在Sheet1中，使用數(shù)組公式計(jì)算汽車在停車庫中的停放時(shí)間。要求：計(jì)算方法為：“停放時(shí)間二出庫時(shí)間-入庫時(shí)間”格式為：“小時(shí)：分鐘：秒”例如：一小時(shí)十五分十二秒在停放時(shí)間中的表示為：“1: 15: 12”將結(jié)果保存在“停車情況記錄表”中的“停放時(shí)間”列中。(5)使用函數(shù)公式，對(duì)“停車情況記錄表”的停車費(fèi)用進(jìn)行計(jì)算。要求：根據(jù)Sheet1停放時(shí)間的長短計(jì)算停車費(fèi)用，將計(jì)算結(jié)果填入到“停車情況記錄 表”的“應(yīng)付金額”列中。注意：停車按小時(shí)收費(fèi)，對(duì)于不滿一個(gè)小時(shí)的按照一

3、個(gè)小時(shí)計(jì)費(fèi)； 對(duì)于超過整點(diǎn)小時(shí)數(shù)十五分鐘(包含十五分鐘)的多累積一個(gè)小時(shí)。例如1小時(shí)23分，將以2小時(shí)計(jì)費(fèi)(6)使用統(tǒng)計(jì)函數(shù)，對(duì) Sheet1中的“停車情況記錄表”根據(jù)下列條件進(jìn)行統(tǒng)計(jì)。統(tǒng)計(jì)停車費(fèi)用大于等于40元的停車記錄條數(shù)，并將結(jié)果保存在 J8單元 格中；統(tǒng)計(jì)最高的停車費(fèi)用，并將結(jié)果保存在 J9單元格中。(7)將Sheet1中的“停車情況記錄表”復(fù)制到 Sheet2中，對(duì)Sheet2進(jìn)行高級(jí)篩選。要求：篩選條件為：“車型”小汽車，“應(yīng)付金額”>=30;將結(jié)果保存在Sheet2中注意:無需考慮是否刪除篩選條件；復(fù)制過程中，將標(biāo)題項(xiàng)“停車情況記錄表”連同數(shù)據(jù)一同復(fù)制；復(fù)制數(shù)據(jù)表后，粘貼時(shí)

4、，數(shù)據(jù)表必須頂格放置。（8）根據(jù)Sheetl中的“停車情況記錄表“，創(chuàng)建一個(gè)數(shù)據(jù)透視圖，保存在 Sheet3中。顯示各種車型所收費(fèi)用的匯總；x坐標(biāo)設(shè)置為“車型”；求和項(xiàng)為“應(yīng)付金額”；將對(duì)應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。o操作說明1 .設(shè)置單元格限定錄入字?jǐn)?shù)-數(shù)據(jù)有效性根據(jù)操作要求（1）,打開Excel例3-02文件，選擇Sheet4工作表，選擇 A1單元格, 在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”功能組，點(diǎn)擊“圈”（數(shù)據(jù)有效性）打開數(shù)據(jù)有效性對(duì)話框?qū)υ挻啊Ｈ鐖D3- 1所示。在“數(shù)據(jù)有效性”對(duì)話窗口選擇“設(shè)置”選項(xiàng)卡，設(shè)置“有效性條件”允許 “文本長度”數(shù)據(jù)“等于”長度“ 5”，如圖3- 2所示。

5、點(diǎn)擊選擇“出錯(cuò)警告 項(xiàng)目選項(xiàng)卡，設(shè)置“出錯(cuò)樣式”為“警告”，在“錯(cuò)誤信息”欄下輸入“只能錄 入5位數(shù)字或文本”。圖3- 2設(shè)置數(shù)據(jù)有效性2 . 判斷閏年根據(jù)操作要求(2),對(duì)于閏年需表達(dá)兩個(gè)可能分別成立的條件，所以使用OR函數(shù)。而條件之一 “年數(shù)能被 4整除而不能被100整除”為兩個(gè)并列條件，需要使用 AND函數(shù)，這里 表達(dá)整除需要用到取余函數(shù) MOD而其中的當(dāng)年年份需要用到年函數(shù) YEAR和當(dāng)日日期函數(shù) TODAY選才i Sheet4工作表的B1單元格，在“公式”選項(xiàng)卡下“函數(shù)庫”功能組中點(diǎn)擊“ 學(xué) (邏輯)，在彈出的邏輯函數(shù)列表中選擇 “OR函數(shù)，在OR函數(shù)參數(shù)”對(duì)話窗中，條件Logcal

6、1 的文本框中輸入閏年的第一種判斷方式，即“年數(shù)能被4整除而不能被100整除”，由于是一組并列條件，嵌入 AND函數(shù)，將轉(zhuǎn)到 AND “函數(shù)參數(shù)”對(duì)t窗，分別在 Logical1條件文 本框中輸入條件式“ MOD(YEAR(TODAY(),4)=0"(年份能被4整除)，在Logical2條件文本 框中輸入條件式“ MOD(YEAR(TODAY(),100)>0"(年份不能能被100整除)。如圖3- 3所示。圖3- 3閏年的并列條件“被 4整除而不能被100在編輯欄點(diǎn)擊 AND函數(shù)的結(jié)尾處(最后一個(gè)")”符號(hào)前)，回到OR “函數(shù)參數(shù)”對(duì)話窗,在Logical

7、2 條件文本框中輸入第二個(gè)判斷條件“能被400整除的年份”的條件式,“MOD(YEAR(TODAY(),400)=0” 如圖 3- 4 所示。OS建峪 7 L XND口 11皿 口的94)=fl, QLod cUZ 閻D % M ffWDA.1 1乜 W)=0|LgciiqSS=可蕓的如果任一整故值為工理心即詆回Jam? .有當(dāng)所有整故值均為FAME時(shí)牙返回TKLSE“部©03： Locic.tlL.locic. L 到 225 個(gè)It果是 TftUE 或 FALSI 的性解 件圖3- 4通過OR函數(shù)表達(dá)閏年的兩組判斷方式在B2單元格給出的完整表達(dá)式為：“=OR(AND(MOD(YE

8、AR(TODAY(),4)=0,MOD(YEAR(TODAY(),100)>0),MOD(YEAR(TODAY() ),400)=0) ”3 .計(jì)算車輛停放收費(fèi)“單價(jià)”-水平查詢函數(shù)Hlookup單擊“停車情況記錄表”中的C9單元格，使其成為活動(dòng)單元格。在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(diǎn)擊“微”（查詢與引用），在彈出的函數(shù)列表中選擇水平查詢函數(shù)“HLOOKUP在彈出的如圖 3- 5所示“函數(shù)參數(shù)”對(duì)話框中編輯函數(shù)。在“函數(shù)參數(shù)”對(duì)話框中的第一行“ Lookup_value "（查詢參數(shù)值）框輸入搜索值“停車情況記錄表”第一條記錄“車型”的單元格地址“B9”（確定

9、在數(shù)組區(qū)域首行進(jìn)行搜索時(shí)的搜索值）。在第2行“Table_array ”（搜尋數(shù)據(jù)表）框輸入要搜索區(qū)域“停車價(jià)目表”數(shù)據(jù)區(qū)的絕 對(duì)地址“ $A$2: $C$3"（可選擇A2:C3單元格區(qū)域后按 F4鍵來絕對(duì)引用），用于確定要搜索 的數(shù)組或數(shù)據(jù)表所在的區(qū)域。在第3行“ Row_index_num”（搜尋之目標(biāo)數(shù)據(jù)所在行序號(hào)）框輸入數(shù)字“2”，用以確定在數(shù)組區(qū)域首行搜索到滿足搜索值時(shí)，要取的值位于該列的第幾行，本題是取第二行的值。在第4行"Range_lookup "（查詢結(jié)果的匹配方式）框輸入" FALSE以設(shè)定查找精確匹 配的邏輯值。單擊“確定”按鈕，在

10、“停車情況記錄表”第一條記錄的“單價(jià)”單元格C9得到使用了 HLOOKU函數(shù)后小汽車的小時(shí)停車單價(jià)為“5”。雙擊C9單元格填充柄，完成停車單價(jià)的圖3- 5使用HLOOKU澳入單價(jià)數(shù)據(jù)司A蒐5gL部1,孫為停車情況記錄，自動(dòng)填充。加2 m ；邢512” 那54明土 新"山H94 距*正£$ 尚的21新4 .計(jì)算汽車停放時(shí)間將光標(biāo)定位于“停車情況記錄表”第一條記錄的“停放時(shí)間”單元格 F9,在編輯欄輸入 “=E9-D9”后按回車鍵；雙擊F9單元格填充柄完成“停放時(shí)間”列的自動(dòng)填充。5 .計(jì)算停車費(fèi)將光標(biāo)定位于“停車情況記錄表”第一條記錄的“應(yīng)付金額”單元格G9,先計(jì)算停車計(jì)費(fèi)時(shí)

11、間（小時(shí)），在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(diǎn)擊（邏輯）， 在彈出的函數(shù)列表中選擇條件函數(shù)“IF”，在彈出的如圖3- 6所示“函數(shù)參數(shù)”對(duì)話框中編輯函數(shù)。在第1行“Logical_test ”（邏輯表達(dá)式）框輸入邏輯表達(dá)式“HOUR（F9）1',判斷停車時(shí)間是否不足1小時(shí)，使用時(shí)間函數(shù)HOUR（）B提取F9單元格內(nèi)的小時(shí)數(shù)進(jìn)行判斷是否小于 0。在第2行“ Value_if_true ”框輸入當(dāng)邏輯表達(dá)式“ HOUR（F9）0為“true ”的值，根據(jù)題意，在該欄車入數(shù)字“ 1"。如圖3- 6所示。圖3- 6判斷不足I小時(shí)收費(fèi)計(jì)時(shí)在第3行“Value_if_ f

12、alse ”框應(yīng)輸入當(dāng)邏輯表達(dá)式 “HOUR(F9)<0'為"false ”的值， 由于涉及是否超過整點(diǎn)小時(shí)數(shù)十五分鐘的判斷，會(huì)有兩種可能的結(jié)果，需要嵌入一層IF函數(shù)進(jìn)行再判斷，點(diǎn)擊第三行文本框，點(diǎn)擊工作表編輯區(qū)左上角的名稱欄中的“IF”函數(shù)名(嵌套“IF”函數(shù))，將進(jìn)入新的“函數(shù)參數(shù)”窗口。在第2層“函數(shù)參數(shù)”窗口的第1行“ Logical_test "，輸入 MINUTE(F9)<15,以判斷是否超過 整點(diǎn)15分鐘，其中 MINUTE()將提取 F9單元格內(nèi) 的分鐘數(shù)。在第 2行 “Value_if_true”輸入沒超過15分鐘的計(jì)費(fèi)時(shí)間“ HOUR

13、(F9)',即按實(shí)際的停車小時(shí)數(shù)，不計(jì)分(舍棄)。在第3行“Value_if_ false ”后的文本框輸入超出整點(diǎn)15分以上的計(jì)時(shí)數(shù)(加計(jì)1小時(shí))“HOUR(F9)+1"，如圖3- 7所示。圖3- 7判斷超15分鐘收費(fèi)計(jì)時(shí)單擊“確定”按鈕，完成停車計(jì)費(fèi)小時(shí)數(shù)的統(tǒng)計(jì)，這時(shí)編輯欄上完成的表達(dá)式為“=IF(HOUR(F9)<0,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)"，是按計(jì)時(shí)要求的停車小時(shí)數(shù)。停車小時(shí)數(shù)的表達(dá)式后輸入“ *C9”(乘上收費(fèi)單價(jià))，即得到每小時(shí)停車費(fèi)，整個(gè)表達(dá)式 為 “ =IF(HOUR(F9)<0

14、,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)*C9 ”。雙擊G9單元格右下角的填充柄，完成停車應(yīng)付金額的自動(dòng)填充。6 .統(tǒng)計(jì)停車記錄數(shù)和最高停車費(fèi)將光標(biāo)定位于J9單元格，在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(diǎn)擊“0”（其他函數(shù)組），在彈出的函數(shù)組列表中選擇“統(tǒng)計(jì)”函數(shù)組，在下級(jí)子菜單中選擇 “COUNTIF函數(shù)，在彈出的“函數(shù)參數(shù)”對(duì)話框中輸入?yún)?shù)。在第1行“Range”（要統(tǒng)計(jì)的數(shù)據(jù)區(qū)域）輸入“ G9:G39"，在第2行aCriteria ”（統(tǒng)計(jì) 條件）框輸入“ >=40”（不包括引號(hào)，使用非中文輸入狀態(tài)輸入），如圖3-

15、8所示。單擊“確定”按鈕完成統(tǒng)計(jì)，在 J9單元格得到符合條件的記錄條數(shù)為“4”。圖3- 8統(tǒng)計(jì)收費(fèi)大于 40元的記錄將光標(biāo)定位于J10單元格，在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(diǎn)擊第1組函數(shù)（“工”）的列表展開鍵“”，在彈出的函數(shù)組列表中選擇“最大值（M）”，在函數(shù)括號(hào)中輸入統(tǒng)1t數(shù)據(jù)區(qū)域“ G9:G39"，單擊“確定”命令按鈕，在 J10單元格得到最高停 車費(fèi)用為“ 50”。7 .記錄數(shù)據(jù)的高級(jí)篩選選才i Sheet1工作表，選擇“停車情況記錄表”所在單元格區(qū)域，復(fù)制該數(shù)據(jù)（包括標(biāo)題）。選擇Sheet2工作表，右擊 A1單元格，在彈出菜單的“粘貼選項(xiàng)：”下選擇“孱”（

16、粘貼數(shù)值），將相關(guān)數(shù)據(jù)粘貼到 Sheet2工作表，在“開始”選項(xiàng)卡“字體”功能組中點(diǎn)擊“田”為數(shù)據(jù)區(qū)添加表格線。選擇“ A1： GT單元格，點(diǎn)擊“開始”選項(xiàng)卡“對(duì)齊方式”功能組中的“串”（合并并居中），恢復(fù)原表中標(biāo)題欄的設(shè)置，如圖 3- 9所示。I B C 口 EABCDS7GL 1C.旦L初曜金2主他曾車隊(duì)單價(jià)入庫富憫出庫”問得應(yīng)歸間nius3 揄1制45小汽車50. 34195604691550-12719915-1 浙Jia 25gl大都于100. 3570630. 3&T74SLL OiOafi105 淅是15日4甲西車20. 375-1170. 6293290. 25391Z

17、4日6 浙 N&63T1小汽車1n 3g64CL 6345B3口zssias307 迫 *51271E百豐8Q, 雙0. 4230675 01ffT73g9 浙0的4*大咨豐100. 439560.5315160.闞曰56209 加一函94小汽卓50. 4559220.4633770. 01305g5ID淅。怎婺21卬客車S0. 4SO4170 . 5595430.D99132241件半雷兄記錄表2車?yán)?hào) 車型 單矯 人侔時(shí)間出庫| 3 1234=5 小河車5 0. 341556 0 4GS14 143258： ''_ 357083 m 39775 帶4樂，*”劇JT5

18、417 0, 62KB刈UM"- 10七K3它入3帕耳（X 634!I T 漸A51 Til 中好心H U. 409294 0l 423C354睚士士 宮豐10 0. 435E 0l 5S1E0 施A 56聘4 小丸車5 0.455822 ；. 468E10 ijiA33221 中客車8 0. 460417 S 559f圖3- 9復(fù)制工作表數(shù)據(jù),單擊I3在Shet2工作表數(shù)據(jù)區(qū)的右側(cè)空白區(qū)域設(shè)置篩選條件區(qū)（不要緊挨數(shù)據(jù)區(qū)）單元格，設(shè)置條件區(qū)如圖單擊表Sheet2要進(jìn)行高級(jí)篩選的數(shù)據(jù)區(qū)中任一單元格，在“數(shù)據(jù)”選項(xiàng)卡中“排序和篩選”功能組中點(diǎn)擊，”（高級(jí)篩選），在打開的“高級(jí)篩選”對(duì)話框中“列表區(qū)域”已自動(dòng)填入當(dāng)前工作表的數(shù)據(jù)區(qū)單元格引用“$A$2:$G$33”，點(diǎn)擊“條件區(qū)域”右側(cè)的文本框,在當(dāng)前工作表選擇上面設(shè)定的條件區(qū)域“Sheet2!$I$3:$J$4 ”，勾選“高級(jí)篩選”對(duì)話窗底部的“選擇不重復(fù)記錄”，單擊“確定”按鈕完成高級(jí)篩選。如圖3- 11所示。益級(jí)土選方式.在原有囚場顯示赫選結(jié)果口,將靖選結(jié)事復(fù)制到其他位置®）列襄區(qū)域通）：確定| 取消3- 11高級(jí)篩選