二級excel高級操作-停車-解題步驟

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

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

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

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

5、性”對話窗口選擇“設(shè)置”選項卡，設(shè)置“有效性條件”允許“文本長度”數(shù)據(jù)“等于”長度“ 5”，如圖3- 2所示。點擊選擇“出錯警告 項目選項卡，設(shè)置“出錯樣式”為“警告”，在“錯誤信息”欄下輸入“只能錄 入5位數(shù)字或文本”。圖3- 2設(shè)置數(shù)據(jù)有效性2.判斷閏年根據(jù)操作要求(2),對于閏年需表達兩個可能分別成立的條件，所以使用OR函數(shù)。而條件之一“年數(shù)能被 4整除而不能被100整除”為兩個并列條件，需要使用 AND函數(shù)，這 里表達整除需要用到取余函數(shù) MoD，而其中的當年年份需要用到年函數(shù) YEAR和當日日期 函數(shù)TODAY。選擇Sheet4工作表的B1單元格，在“公式”選項卡下“函數(shù)庫”功能組中

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

7、處(最后一個“)”符號前)，回到OR “函數(shù)參數(shù)”對 話窗，在LOgiCal2條件文本框中輸入第二個判斷條件“能被 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貝有當所有魁尅值均為F心E jgffl TJiiSEI>Elc13: LciclL.lcickl2j. I 勿 225 弁Ii綁是 Tm FL5I 的煌知

8、躁件計=可和的E詢:flft曲SJi叩I薩電消圖3- 4通過OR函數(shù)表達閏年的兩組判斷方式在B2單元格給出的完整表達式為：“ =OR(AND(MOD(YEAR(TODAY(),4)=0,MOD(YEAR(TODAY(),100)>0),MOD(YEAR(TODAY(),400)=0) ”3.計算車輛停放收費“單價”-水平查詢函數(shù)Hlookup單擊“停車情況記錄表”中的C9單元格，使其成為活動單元格。在功能區(qū)選擇“公式”選項卡，在“函數(shù)庫”功能組中點擊“”（查詢與引用），在彈出的函數(shù)列表中選擇水平查詢函數(shù)“HLooKUP ”，在彈出的如圖3- 5所示“函數(shù)參數(shù)”對話框中編輯函數(shù)。在“函數(shù)參

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

10、OokUP”（查詢結(jié)果的匹配方式）框輸入“FALSE ”以設(shè)定查找精確匹配的邏輯值。單擊“確定”按鈕，在“停車情況記錄表”第一條記錄的“單價”單元格C9得到使用了 HLOOKUP函數(shù)后小汽車的小時停車單價為 “ 5”。雙擊C9單元格填充柄，完成停車單價 的自動填充。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填入單價數(shù)據(jù)4計算汽車停放時間將光標定位于“停車情況記錄表”第一條記錄的“停放時間”單元格F9,在編輯欄輸入“ =E9-D9 ”后按回車鍵；雙擊F9單元格填充柄完成“停放時間”列的自動填充。5 計算停車費將光標定位于“停車情況記錄表”第一條記錄的“應(yīng)付金額”單元格G9 ,先計算停車計費時間（小時），在功能區(qū)選擇“

12、公式”選項卡，在“函數(shù)庫”功能組中點擊“企”（邏輯）， 在彈出的函數(shù)列表中選擇條件函數(shù)“IF”，在彈出的如圖3- 6所示“函數(shù)參數(shù)”對話框中編輯函數(shù)。在第1行“ LOgiCaI_test ”（邏輯表達式）框輸入邏輯表達式“HOUR（F9）1 ”,判斷停車 時間是否不足1小時，使用時間函數(shù) HOUR（）將提取F9單元格內(nèi)的小時數(shù)進行判斷是否小于0。在第2行“ Value_if_true ”框輸入當邏輯表達式“HoUR(F9)<0 ”為“ true”的值，根據(jù)題意，在該欄輸入數(shù)字“1”。如圖3- 6所示。在第3行“ Value_if_ false ”框應(yīng)輸入當邏輯表達式“HOUR(F9)&l

13、t;0 ”為“ false”的值，由于涉及是否超過整點小時數(shù)十五分鐘的判斷，會有兩種可能的結(jié)果，需要嵌入一層IF函數(shù)進行再判斷，點擊第三行文本框，點擊工作表編輯區(qū)左上角的名稱欄中的“IF”函數(shù)名(嵌套“ IF”函數(shù))，將進入新的“函數(shù)參數(shù)”窗口。在第2層“函數(shù)參數(shù)”窗口的第1行“ LOgiCaI_test ”，輸入MINUTE(F9)<15 ,以判斷是否超過整點15分鐘，其中MINUTE()將提取F9單元格內(nèi)的分鐘數(shù)。在第2行“Value_if_true ” 輸入沒超過15分鐘的計費時間“ HOUR(F9) ”，即按實際的停車小時數(shù)，不計分(舍棄) 。在 第3行“ Value_if_ f

14、alse”后的文本框輸入超出整點15分以上的計時數(shù)(加計 1小時)“HOUR(F9)+1 ”，如圖 3- 7 所示。圖3- 7判斷超15分鐘收費計時單擊“確定”按鈕，完成停車計費小時數(shù)的統(tǒng)計，這時編輯欄上完成的表達式為“ =IF(HOUR(F9)<0,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)”，是按計時要求的停車小時數(shù)。停車小時數(shù)的表達式后輸入“ *C9 ”(乘上收費單價)，即得到每小時停車費，整個表達式為“ =IF(HOUR(F9)<0,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)*C9”。雙擊G9單

15、元格右下角的填充柄，完成停車應(yīng)付金額的自動填充。6. 統(tǒng)計停車記錄數(shù)和最高停車費將光標定位于J9單元格，在功能區(qū)選擇“公式”選項卡，在“函數(shù)庫”功能組中點擊直I”（其他函數(shù)組），在彈出的函數(shù)組列表中選擇“統(tǒng)計”函數(shù)組，在下級子菜單中選擇“CoUNTIF ”函數(shù)，在彈出的“函數(shù)參數(shù)”對話框中輸入?yún)?shù)。在第1行“ Range”（要統(tǒng)計的數(shù)據(jù)區(qū)域）輸入“ G9:G39 ”，在第2行“ Criteria ”（統(tǒng)計 條件）框輸入“ >=40”（不包括引號，使用非中文輸入狀態(tài)輸入），如圖3- 8所示。單擊“確定”按鈕完成統(tǒng)計，在 J9單元格得到符合條件的記錄條數(shù)為“4”。圖3- 8統(tǒng)計收費大于 40

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

17、 G1”單元格，點擊“開始”選項卡“對齊方式”功能組中的“ ”（合并并居中），恢復(fù)原表中標題欄的設(shè)置，如圖3- 9所示。FBCDTC1ITf2車牌號車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-, 人時間出庫時3 浙丄 12陽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要進行高級篩選的數(shù)據(jù)區(qū)中任一單元格，在“數(shù)據(jù)”選項卡中“排序和篩選”功能組中點擊“吧”（高級篩選）動填入當前工作表的數(shù)據(jù)區(qū)單元格引用“在當前工作表選擇上面設(shè)定的條件區(qū)域“在打開的“高級篩選”對話框中“列表區(qū)域”已自$A$2:$G$33 ”，點擊“條件區(qū)域”右側(cè)的文本框，Sheet2!$l$3:$J$4”，勾選“高級篩選”對話窗底部的“選擇不重復(fù)記錄”，單擊“確定”按鈕完成高級篩選。如圖3- 11所示。方式列 E(L): 棗件區(qū)城(E) lJ(T):如 3S)U PBC0EFG1借車唱況記禾乖2車型6小汽車5 O. 36410. 6345B3OI 3918330舊Q 5Q37旳3圖3- 11高級