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

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

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

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

4、貼時，數(shù)據(jù)表必須頂格放置。（8）根據(jù)Sheetl中的“停車情況記錄表“，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet3中。顯示各種車型所收費(fèi)用的匯總；x坐標(biāo)設(shè)置為“車型”；求和項為“應(yīng)付金額”；將對應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。操作說明1. 設(shè)置單元格限定錄入字?jǐn)?shù)-數(shù)據(jù)有效性根據(jù)操作要求（1）,打開Excel例3-02文件，選擇Sheet4工作表，選擇A1單元格，在“數(shù)據(jù)”選項卡的“數(shù)據(jù)工具”功能組，點擊“圈”（數(shù)據(jù)有效性）打開數(shù)據(jù)有效性對話框?qū)υ挻?。如圖3- 1所示。開培 /入布耳般溫 云網(wǎng) 裾沿回I喻如國豳尸尸曷 亞 業(yè)甌篙二產(chǎn)rtu?霹態(tài)"=：二,三鈾?quán)冒糭卜打郭舐君-，-8

5、 M力國J里復(fù)山等也畤無St崢D ”運(yùn)技里序五確送蚓方酒除無璉致有套舊曲面SZ1圖3- 1選擇數(shù)據(jù)有效性在“數(shù)據(jù)有效性”對話窗口選擇“設(shè)置”選項卡，設(shè)置“有效性條件”允許 “文本長度”數(shù)據(jù)“等于”長度“ 5”，如圖3- 2所示。點擊選擇“出錯警告 項目選項卡，設(shè)置“出錯樣式”為“警告”，在“錯誤信息”欄下輸入“只能錄 入5位數(shù)字或文本”。圖3- 2設(shè)置數(shù)據(jù)有效性2.判斷閏年根據(jù)操作要求(2),對于閏年需表達(dá)兩個可能分別成立的條件，所以使用OR函數(shù)。而條件之一 “年數(shù)能被 4整除而不能被100整除”為兩個并列條件，需要使用 AND函數(shù)，這 里表達(dá)整除需要用到取余函數(shù) MOD ,而其中的當(dāng)年年份需

6、要用到年函數(shù) YEAR和當(dāng)日日期 函數(shù)TODAY。選才i Sheet4工作表的B1單元格，在“公式”選項卡下“函數(shù)庫”功能組中點擊“ 婕'(邏輯)，在彈出的邏輯函數(shù)列表中選擇“ OR”函數(shù)，在OR “函數(shù)參數(shù)”對話窗中，條件Logcal1的文本框中輸入閏年的第一種判斷方式，即“年數(shù)能被4整除而不能被100整除”，由于是一組并列條件，嵌入AND函數(shù)，將轉(zhuǎn)到 AND “函數(shù)參數(shù)”對話窗，分別在 Logical1條件文本框中輸入條件式“ MOD(YEAR(TODAY(),4)=0 "(年份能被 4整除)，在Logical2條件文本框中輸入條件式“MOD(YEAR(TODAY(),1

7、00)>0 "(年份不能能被 100整除)。如圖3- 3所示。圖3- 3閏年的并列條件“被4整除而不能被100在編輯欄點擊 AND函數(shù)的結(jié)尾處(最后一個“)”符號前)，回到OR “函數(shù)參數(shù)”對話窗，在Logical2條件文本框中輸入第二個判斷條件“能被 400整除的年份”的條件式，MOD(YEAR(TODAY(),400)=0 ” 如圖 3- 4 所示。ORSS朋cd L JJ4U四仙口上忠汗血0X <)=0.«Lop cd.2 Itm i：YEAX CTHDA.Y口1a0拿電42 | Lopc-il4=可美的如果任一第超值由mrEi囿返回與肓當(dāng)所有軍教值均為F

8、AISE時牙返回TJHSEL«Elc«13： iLofkd-LlMc:或.U 里I 225 個fiC果是 TftUE 或 FAL5I 的性解計苴造果=可糊白工由用勃官阻鼬住圖3- 4通過OR函數(shù)表達(dá)閏年的兩組判斷方式在B2單元格給出的完整表達(dá)式為：“=OR(AND(MOD(YEAR(TODAY(),4)=0,MOD(YEAR(TODAY(),100)>0),MOD(YEAR (TODAY(),400)=0) ”3 .計算車輛停放收費(fèi)“單價”-水平查詢函數(shù)Hlookup單擊“停車情況記錄表”中的C9單元格，使其成為活動單元格。在功能區(qū)選擇“公式”選項卡，在“函數(shù)庫”功能

9、組中點擊“微”（查詢與引用），在彈出的函數(shù)列表中選擇水平查詢函數(shù)“HLOOKUP ”，在彈出的如圖3- 5所示“函數(shù)參數(shù)”對話框中編輯函數(shù)。在“函數(shù)參數(shù)”對話框中的第一行“ Lookup_value"（查詢參數(shù)值）框輸入搜索值“停車情況記錄表”第一條記錄“車型”的單元格地址“ B9”（確定在數(shù)組區(qū)域首行進(jìn)行搜索時 的搜索值）。在第2行“Table_array”（搜尋數(shù)據(jù)表）框輸入要搜索區(qū)域“停車價目表”數(shù)據(jù)區(qū)的絕 對地址“ $A$2: $C$3"（可選擇A2:C3單元格區(qū)域后按 F4鍵來絕對引用），用于確定要搜 索的數(shù)組或數(shù)據(jù)表所在的區(qū)域。在第3行“ Row_index_n

10、um”（搜尋之目標(biāo)數(shù)據(jù)所在行序號）框輸入數(shù)字“2”，用以確定在數(shù)組區(qū)域首行搜索到滿足搜索值時，要取的值位于該列的第幾行，本題是取第二行的值。在第4行"Range_lookup"（查詢結(jié)果的匹配方式）框輸入" FALSE”以設(shè)定查找精確 匹配的邏輯值。單擊“確定”按鈕，在“停車情況記錄表”第一條記錄的“單價”單元格C9得到使用了 HLOOKUP函數(shù)后小汽車的小時停車單價為 “5”。雙擊C9單元格填充柄，完成停車單價 的自動填充。ITS 新"百日94 LG那目£&K淅網(wǎng)網(wǎng)丁 13；1fiAST412Hl5QC圖3- 5使用HLOOKUP填入

11、單價數(shù)據(jù)11值50用a A生 1= .41X123413 ；ffiA3258L11 :熱工酷"I Z 淅4的日" 1J 而R5il27L小汽車中雪斗卜汽車小汽車i;3：i邑：她J.IJ.A1i4 .計算汽車停放時間將光標(biāo)定位于“停車情況記錄表”第一條記錄的“停放時間”單元格F9,在編輯欄輸入“=E9-D9”后按回車鍵；雙擊F9單元格填充柄完成“停放時間”列的自動填充。5 .計算停車費(fèi)將光標(biāo)定位于“停車情況記錄表”第一條記錄的“應(yīng)付金額”單元格G9,先計算停車計費(fèi)時間（小時），在功能區(qū)選擇“公式”選項卡，在“函數(shù)庫”功能組中點擊 心”（邏輯）， 在彈出的函數(shù)列表中選擇條件函數(shù)

12、“IF”，在彈出的如圖3- 6所示“函數(shù)參數(shù)”對話框中編輯函數(shù)。在第1行“ Logical_test ”（邏輯表達(dá)式）框輸入邏輯表達(dá)式“ HOUR（F9）1 "，判斷停車時間是否不足1小時，使用時間函數(shù) HOUR（）將提取F9單元格內(nèi)的小時數(shù)進(jìn)行判斷是否小 于0。在第2行“ Value_if_true ”框輸入當(dāng)邏輯表達(dá)式“ HOUR(F9)<0 ”為“ true”的值，根據(jù)題意，在該欄輸入數(shù)字“1"。如圖3- 6所示。圖3- 6判斷不足I小時收費(fèi)計時在第3行“ Value_if_ false”框應(yīng)輸入當(dāng)邏輯表達(dá)式“ HOUR(F9)<0 ”為“ false”的

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

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

15、角的填充柄，完成停車應(yīng)付金額的自動填充。6 .統(tǒng)計停車記錄數(shù)和最高停車費(fèi)將光標(biāo)定位于J9單元格，在功能區(qū)選擇“公式”選項卡，在“函數(shù)庫”功能組中點擊（其他函數(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)計收費(fèi)大于 40元的記錄將光

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

17、格線。選擇“ A1 : G1”單元格，點擊“開始”選項卡“對齊方式”功能組 中的“李”（合并并居中），恢復(fù)原表中標(biāo)題欄的設(shè)置，如圖 3- 9所示。A £ C D B3方下增況記可而車牌號 車型至希入庫時間出庫時1ABCDSGk小汽車5 nr 311g55 m 4EP12二他曾羊型里聽入庫H憫出庫”問停缺時間<k 127199應(yīng)句畬轅1544fiA3258：- T57083 C. 39773小汽車50. 3419560. 46915554fiAZ153'5T5417 m 6期1 ；1血22sgi大都于100. S57083CL 3&774S口. M弼10&

18、7浙46的T： j聲套1 七 ><Q, 6345浙器12T1中各A修工1a9次4口強(qiáng)8c5 淅二1弼甲西車20. 375-1170. 6E93290. 25391Z4日6 ；|趾感71小汽車1n 3g64也 6345B3d Z3S18330E浙&54的4 大客車1Q 0, 43g56 01 531r JEA5127180. tMZM凡 4230670.01ST73e9 浙鈍440. 439560.5315160.闞曰56自浙&5E8次小汽車5 0.4553220468E1仆209 加九5面94小汽卓50. 455922fl.4633770. 01305g51Q433

19、221 中喜車3 0r 460417 0i 559EID >ffiA33221中塞車0. 404170 . 5595430.D9913224圖3- 9復(fù)制工作表數(shù)據(jù)在Shet2工作表數(shù)據(jù)區(qū)的右側(cè)空白區(qū)域設(shè)置篩選條件區(qū)（不要緊挨數(shù)據(jù)區(qū)），單擊I3單元格，設(shè)置條件區(qū)如圖3- 10所示。單擊表Sheet2要進(jìn)行高級篩選的數(shù)據(jù)區(qū)中任一單元格，在“數(shù)據(jù)”選項卡中“排序和 篩選”功能組中點擊“ .9”（高級篩選），在打開的“高級篩選”對話框中“列表區(qū)域”已自 動填入當(dāng)前工作表的數(shù)據(jù)區(qū)單元格引用“$A$2:$G$33”，點擊“條件區(qū)域”右側(cè)的文本框，在當(dāng)前工作表選擇上面設(shè)定的條件區(qū)域“Sheet2!$I$3:$J$4”，勾選“高級篩選”對話窗底部的“選擇不重復(fù)記錄”，單擊“確定”按鈕完成高級篩選。如圖 3- 11所示。益級土選方式.在原有囚場顯示赫選結(jié)果口列襄區(qū)域通）：條件區(qū)域心） 復(fù)制國（T）：B C U E F