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

1、計(jì)算機(jī)應(yīng)用基礎(chǔ)課程教學(xué)任務(wù)參考步驟設(shè)計(jì)者：計(jì)算機(jī)基礎(chǔ)教研室例3-2打開(kāi)素材文件，按下列要求操作。>操作要求（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）使用HLooKUP函數(shù)，對(duì)Sheet1 “停車情況記錄表”中的“單價(jià)”列進(jìn)行填充。要求：根據(jù)SheetI中的“停車價(jià)目表”價(jià)格，使用HLOOKUP函數(shù)對(duì)“停

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

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

4、數(shù)據(jù)表必須頂格放置。（8）根據(jù)SheetI中的“停車情況記錄表”，創(chuàng)建一個(gè)數(shù)據(jù)透視圖，保存在Sheet3中。顯示各種車型所收費(fèi)用的匯總；X坐標(biāo)設(shè)置為“車型”； 求和項(xiàng)為“應(yīng)付金額”；將對(duì)應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。操作說(shuō)明1.設(shè)置單元格限定錄入字?jǐn)?shù)-數(shù)據(jù)有效性根據(jù)操作要求（1），打開(kāi)Excel例3-02文件，選擇Sheet4工作表，選擇A1單元格，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”功能組，點(diǎn)擊“”（數(shù)據(jù)有效性）打開(kāi)數(shù)據(jù)有效性對(duì)話框 對(duì)話窗。如圖3- 1所示。I<ACrebat-.U»?! Bft-gJsfieSKtIIy.ALADEGI1圖3- 1選擇數(shù)據(jù)有效性在“數(shù)據(jù)有效

5、性”對(duì)話窗口選擇“設(shè)置”選項(xiàng)卡，設(shè)置“有效性條件”允許“文本長(zhǎng)度”數(shù)據(jù)“等于”長(zhǎng)度“ 5”，如圖3- 2所示。點(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。選擇Sheet4工作表的B1單元格，在“公式”選項(xiàng)卡下“函數(shù)庫(kù)”功能組中

6、點(diǎn)擊“診(邏輯)，在彈出的邏輯函數(shù)列表中選擇“ OR”函數(shù)，在OR “函數(shù)參數(shù)”對(duì)話窗中，條件LogCaII的文本框中輸入閏年的第一種判斷方式，即“年數(shù)能被4整除而不能被100整除”，由于是一組并列條件，嵌入條件文本框中輸入條件式條件文本框中輸入條件式AND函數(shù)，將轉(zhuǎn)到 AND “函數(shù)參數(shù)”對(duì)話窗，分別在LOgiCal1MOD(YEAR(TODAY(),4)=0 ”(年份能被 4 整除)，在 LOgiCal2MOD(YEAR(TODAY(),100)>0”(年份不能能被 100 整除)。如圖3- 3所示。圖3- 3閏年的并列條件“被4整除而不能被100整除”在編輯欄點(diǎn)擊 AND函數(shù)的結(jié)尾

7、處(最后一個(gè)“)”符號(hào)前)，回到OR “函數(shù)參數(shù)”對(duì) 話窗，在LOgiCal2條件文本框中輸入第二個(gè)判斷條件“能被 400整除的年份”的條件式，“MOD(YEAR(TODAY(),400)=0 ”如圖 3- 4 所示。ZlDgSS的ff1齢馭亡L XMD CEEiMI4)=Cli MLOfiic<12 WyIy白EW¾CTffDAX 0).M)=DLD 於ILoeiC<14=可去的如案f-l -. ffl0 TmI貝有當(dāng)所有魁尅值均為F心E jgffl TJiiSEI>Elc13: LciclL.lcickl2j. I 勿 225 弁Ii綁是 Tm FL5I 的煌知

8、躁件計(jì)=可和的E詢:flft曲SJi叩I薩電消圖3- 4通過(guò)OR函數(shù)表達(dá)閏年的兩組判斷方式在B2單元格給出的完整表達(dá)式為：“ =OR(AND(MOD(YEAR(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ù)庫(kù)”功能組中點(diǎn)擊“”（查詢與引用），在彈出的函數(shù)列表中選擇水平查詢函數(shù)“HLooKUP ”，在彈出的如圖3- 5所示“函數(shù)參數(shù)”對(duì)話框中編輯函數(shù)。在“函數(shù)參

9、數(shù)”對(duì)話框中的第一行“ LookUP_value ”（查詢參數(shù)值）框輸入搜索值“停 車情況記錄表”第一條記錄“車型”的單元格地址“B9”（確定在數(shù)組區(qū)域首行進(jìn)行搜索時(shí)的搜索值）。在第2行“ Table_array”（搜尋數(shù)據(jù)表）框輸入要搜索區(qū)域“停車價(jià)目表”數(shù)據(jù)區(qū)的絕 對(duì)地址“ $A$2 : $C$3”（可選擇A2:C3單元格區(qū)域后按 F4鍵來(lái)絕對(duì)引用），用于確定要搜 索的數(shù)組或數(shù)據(jù)表所在的區(qū)域。在第3行“ Row_index_num ”（搜尋之目標(biāo)數(shù)據(jù)所在行序號(hào)）框輸入數(shù)字“2 ”，用以確定在數(shù)組區(qū)域首行搜索到滿足搜索值時(shí)，要取的值位于該列的第幾行，本題是取第二行的值。在第4行“ Range

10、OokUP”（查詢結(jié)果的匹配方式）框輸入“FALSE ”以設(shè)定查找精確匹配的邏輯值。單擊“確定”按鈕，在“停車情況記錄表”第一條記錄的“單價(jià)”單元格C9得到使用了 HLOOKUP函數(shù)后小汽車的小時(shí)停車單價(jià)為 “ 5”。雙擊C9單元格填充柄，完成停車單價(jià) 的自動(dòng)填充。JhCDL存車協(xié)冃親3&a1<45$7a E=車SlWrT3HF9II347A!1!2Q110 IJfih 325SL丈春I=Fa：Sd:12:11 :fiA2!5&48C0361IZ .fiR7L小汽車59!3!4=11 IffA5 2? L923114 :54G44Ifl10:33:501ITi .iK-J

11、'A5lQ：i：£3Lii A3322l9IL 3W117 A6m牙丄1;和迢1Ia IffiA33547Ifl13!5!311 i*yT4i29L¾!15iC1 -HiJ C J OC小立氐IEI O "2 . CCI圖3- 5使用HLOOKUP填入單價(jià)數(shù)據(jù)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í)間（小時(shí)），在功能區(qū)選擇“

12、公式”選項(xiàng)卡，在“函數(shù)庫(kù)”功能組中點(diǎn)擊“企”（邏輯）， 在彈出的函數(shù)列表中選擇條件函數(shù)“IF”，在彈出的如圖3- 6所示“函數(shù)參數(shù)”對(duì)話框中編輯函數(shù)。在第1行“ LOgiCaI_test ”（邏輯表達(dá)式）框輸入邏輯表達(dá)式“HOUR（F9）1 ”,判斷停車 時(shí)間是否不足1小時(shí)，使用時(shí)間函數(shù) HOUR（）將提取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行“ Value_if_ false ”框應(yīng)輸入當(dāng)邏輯表達(dá)式“HOUR(F9)&l

13、t;0 ”為“ false”的值，由于涉及是否超過(guò)整點(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行“ LOgiCaI_test ”，輸入MINUTE(F9)<15 ,以判斷是否超過(guò)整點(diǎn)15分鐘，其中MINUTE()將提取F9單元格內(nèi)的分鐘數(shù)。在第2行“Value_if_true ” 輸入沒(méi)超過(guò)15分鐘的計(jì)費(fèi)時(shí)間“ HOUR(F9) ”，即按實(shí)際的停車小時(shí)數(shù)，不計(jì)分(舍棄) 。在 第3行“ Value_if_ f

14、alse”后的文本框輸入超出整點(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,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)*C9”。雙擊G9單

15、元格右下角的填充柄，完成停車應(yīng)付金額的自動(dòng)填充。6. 統(tǒng)計(jì)停車記錄數(shù)和最高停車費(fèi)將光標(biāo)定位于J9單元格，在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫(kù)”功能組中點(diǎn)擊直I”（其他函數(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行“ Criteria ”（統(tǒng)計(jì) 條件）框輸入“ >=40”（不包括引號(hào)，使用非中文輸入狀態(tài)輸入），如圖3- 8所示。單擊“確定”按鈕完成統(tǒng)計(jì)，在 J9單元格得到符合條件的記錄條數(shù)為“4”。圖3- 8統(tǒng)計(jì)收費(fèi)大于 40

16、元的記錄將光標(biāo)定位于J10單元格，在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫(kù)”功能組中點(diǎn)擊第1組函數(shù)（“£”）的列表展開(kāi)鍵“”，在彈出的函數(shù)組列表中選擇“最大值（M） ”，在函數(shù)括號(hào)中輸入統(tǒng)計(jì)數(shù)據(jù)區(qū)域“G9:G39 ”，單擊“確定”命令按鈕，在J10單元格得到最高停車費(fèi)用為“ 50”。7. 記錄數(shù)據(jù)的高級(jí)篩選選擇SheetI工作表，選擇“停車情況記錄表”所在單元格區(qū)域，復(fù)制該數(shù)據(jù)（包括標(biāo)題）。選擇Sheet2工作表，右擊 A1單元格，在彈出菜單的“粘貼選項(xiàng)：”下選擇“” （粘貼數(shù)值），將相關(guān)數(shù)據(jù)粘貼到為數(shù)據(jù)區(qū)添加表格線。選擇Sheet2工作表，在“開(kāi)始”選項(xiàng)卡“字體”功能組中點(diǎn)擊A1 :

17、 G1”單元格，點(diǎn)擊“開(kāi)始”選項(xiàng)卡“對(duì)齊方式”功能組中的“ ”（合并并居中），恢復(fù)原表中標(biāo)題欄的設(shè)置，如圖3- 9所示。FBCDTC1ITf2車牌號(hào)車SBnA出陣盯直母!1間riliS3i5fiAi 345小汽車S0,駅T95E0. 469155154>lfi58i大客車100. S570B3CL T74LL105Z15a2U. S7541T0. 6E939O- 591Z4S6IFA6ES71屮汽車LDJ 3BCL 45B36 3fil3307fiA5127180, <02山 4230&7O- OIffT73S9>lfi 51844述0, 4395&0.531

18、516¢. 0&19562Dg乳血5閱945LL «55922fl.aa77(L 01305E5ID4fiAS3221中善車0. J4170 . 559543O-D91224停車4況記錄事Z車鑄； 車型 a-, 人時(shí)間出庫(kù)時(shí)3 浙丄 12陽(yáng)5 屮汽車 Or 3415& ¢, 46914 浙5S:二二'''T570S Ol 3977BnaiZ15B MWaR'TT541T ¢, 69E-浙A6687： J Ln J I：*3 0 -364 ¢, 634T浙A5171-tjs45UrW994U,43S

19、C8 浙A543t4S車IO0,. 43?5&9.531:9 浙j卜汽車S 0” 45532 6 468E10 浙A31=P書車90.4604175535m a.*4C a E MnJr.-TLi j-8 nrJXlj"si圖3- 9復(fù)制工作表數(shù)據(jù)在Shet2工作表數(shù)據(jù)區(qū)的右側(cè)空白區(qū)域設(shè)置篩選條件區(qū)（不要緊挨數(shù)據(jù)區(qū)），單擊I3單元格，設(shè)置條件區(qū)如圖3- 10所示。CHI1_J_倚前15I應(yīng)忖金郃10小汽車>=3030圖3- 10設(shè)置篩選條件區(qū)單擊表Sheet2要進(jìn)行高級(jí)篩選的數(shù)據(jù)區(qū)中任一單元格，在“數(shù)據(jù)”選項(xiàng)卡中“排序和篩選”功能組中點(diǎn)擊“吧”（高級(jí)篩選）動(dòng)填入當(dāng)前工作表的數(shù)據(jù)區(qū)單元格引用“在當(dāng)前工作表選擇上面設(shè)定的條件區(qū)域“在打開(kāi)的“高級(jí)篩選”對(duì)話框中“列表區(qū)域”已自$A$2:$G$33 ”，點(diǎn)擊“條件區(qū)域”右側(cè)的文本框，Sheet2!$l$3:$J$4”，勾選“高級(jí)篩選”對(duì)話窗底部的“選擇不重復(fù)記錄”，單擊“確定”按鈕完成高級(jí)篩選。如圖3- 11所示。方式列 E(L): 棗件區(qū)城(E) lJ(T):如 3S)U PBC0EFG1借車唱況記禾乖2車型6小汽車5 O. 36410. 6345B3OI 3918330舊Q 5Q37旳3圖3- 11高級(jí)