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

1、二級(jí)excel高級(jí)操作-停車-解題步驟計(jì)算機(jī)應(yīng)用基礎(chǔ)課程教學(xué)任務(wù)參考步驟設(shè)計(jì)者：計(jì)算機(jī)基礎(chǔ)教研室例3-2打開素材文件，按下列要求操作。y操作要求（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à)

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

3、對(duì)于不滿一個(gè)小時(shí)的按照一個(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ì) 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中。注意:無需考慮是否刪除篩選條件； 復(fù)制過程中，將標(biāo)題項(xiàng)“停車情況記錄表”連同數(shù)據(jù)

4、一同復(fù)制； 復(fù)制數(shù)據(jù)表后，粘貼時(shí)，數(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中。操作說明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所示。圖3- 1選擇數(shù)據(jù)有效性7 I全e抖g H 11AlBCDBJKHj-inw>t咀 >

5、fil4KtT!i:9 / 8在“數(shù)據(jù)有效性”對(duì)話窗口選擇“設(shè)置”選項(xiàng)卡，設(shè)置“有效性條件”允許“文本長度”數(shù)據(jù)“等于”長度“ 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單

6、元格，在“公式”選項(xiàng)卡下“函數(shù)庫”功能組中點(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整除而不能被

7、100整除”在編輯欄點(diǎn)擊 AND函數(shù)的結(jié)尾處(最后一個(gè)“)”符號(hào)前)，回到OR “函數(shù)參數(shù)”對(duì) 話窗，在LOgiCal2條件文本框中輸入第二個(gè)判斷條件“能被 400整除的年份”的條件式，“MOD(YEAR(TODAY(),400)=0 ”如圖 3- 4 所示。圖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.計(jì)算車輛停放收費(fèi)“單價(jià)”-水平查詢函數(shù)Hlookup單擊“停車情況記錄表”中的C9單元格，使其成

8、為活動(dòng)單元格。在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(diǎn)擊“邈|”（查詢與引用），在彈出的函數(shù)列表中選擇水平查詢函數(shù)“HLooKUP ”，在彈出的如圖3- 5所示“函數(shù)參數(shù)”對(duì)話框中編輯函數(shù)。在“函數(shù)參數(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鍵來絕對(duì)引用），用于確定要搜 索的數(shù)組或數(shù)據(jù)表所

9、在的區(qū)域。在第3行“ Row_index_num ”（搜尋之目標(biāo)數(shù)據(jù)所在行序號(hào)）框輸入數(shù)字“2 ”，用以確定在數(shù)組區(qū)域首行搜索到滿足搜索值時(shí)，要取的值位于該列的第幾行，本題是取第二行的值。在第4行“ RangeOokUP”（查詢結(jié)果的匹配方式）框輸入“FALSE ”以設(shè)定查找精確匹配的邏輯值。單擊“確定”按鈕，在“停車情況記錄表”第一條記錄的“單價(jià)”單元格C9得到使用了 HLOOKUP函數(shù)后小汽車的小時(shí)停車單價(jià)為 “ 5”。雙擊C9單元格填充柄，完成停車單價(jià) 的自動(dòng)填充。圖3- 5使用HLOOKUP填入單價(jià)數(shù)據(jù)4計(jì)算汽車停放時(shí)間將光標(biāo)定位于“停車情況記錄表”第一條記錄的“停放時(shí)間”單元格F9,

10、在編輯欄輸入“ =E9-D9 ”后按回車鍵；雙擊F9單元格填充柄完成“停放時(shí)間”列的自動(dòng)填充。5 計(jì)算停車費(fèi)將光標(biāo)定位于“停車情況記錄表”第一條記錄的“應(yīng)付金額”單元格G9 ,先計(jì)算停車計(jì)費(fèi)時(shí)間（小時(shí)），在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(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_tru

11、e ”框輸入當(dāng)邏輯表達(dá)式“HoUR(F9)<0 ”為“ true”的值，根據(jù)題意，在該欄輸入數(shù)字“1”。如圖3- 6所示。圖3- 6判斷不足I小時(shí)收費(fèi)計(jì)時(shí)在第3行“ Value_if_ false ”框應(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行“ LOgiCaI_test ”，輸入MINUTE(F9)<15 ,

12、以判斷是否超過整點(diǎn)15分鐘，其中MINUTE()將提取F9單元格內(nèi)的分鐘數(shù)。在第2行“Value_if_true ” 輸入沒超過15分鐘的計(jì)費(fèi)時(shí)間“ HOUR(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í)

13、要求的停車小時(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單元格右下角的填充柄，完成停車應(yīng)付金額的自動(dòng)填充。6.統(tǒng)計(jì)停車記錄數(shù)和最高停車費(fèi)將光標(biāo)定位于J9單元格，在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(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:

14、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元的記錄將光標(biāo)定位于J10單元格，在功能區(qū)選擇“公式”選項(xiàng)卡，在“函數(shù)庫”功能組中點(diǎn)擊 第1組函數(shù)（“£”）的列表展開鍵“”，在彈出的函數(shù)組列表中選擇“最大值（M） ”，在函數(shù)括號(hào)中輸入統(tǒng)計(jì)數(shù)據(jù)區(qū)域“G9:G39 ”，單擊“確定”命令按鈕，在J10單元格得到最高停車費(fèi)用為“ 50”。7.記錄數(shù)據(jù)的高級(jí)篩選選擇SheetI工作表，選擇“停車情況記錄

15、表”所在單元格區(qū)域，復(fù)制該數(shù)據(jù)（包括標(biāo) 題）。選擇Sheet2工作表，右擊 A1單元格，在彈出菜單的“粘貼選項(xiàng)：”下選擇“囲”（粘貼數(shù)值），將相關(guān)數(shù)據(jù)粘貼到 Sheet2工作表，在“開始”選項(xiàng)卡“字體”功能組中點(diǎn)擊“_ ”為數(shù)據(jù)區(qū)添加表格線。選擇A1 : G1”單元格，點(diǎn)擊“開始”選項(xiàng)卡“對(duì)齊方式”功能組中的“二”（合并并居中），恢復(fù)原表中標(biāo)題欄的設(shè)置，如圖3- 9所示。A.BG' 1z4典d型單/*7ft LLMK51; . AT! 0. S4I596 0.4；I35S:-J-57DES ,3i3T75.-75dT ,93百IBeer: 2)¾ > 1£ U

16、典C. G価TffiBl.Z71屮昔OK LL 1Q99 IX HrZBd4fisi4尢冉車IU I. 43956 0.5315§汕舸I :沖小X豐5 OL <i22 0,-t53-ICUi-.n-n-ff.-.-« lbt>il、&. SiMr Jr-ErL. rI -*B,ErF12ITL"伺a. TJ aff-HJfi)ifU IijLI2 :+55:U5ftQt h¾5OB12T194 iJL5l丈喜豐LoI. S'J :0L37Td30.lUfat丄D,.4ktJl'-亂BOL S7&417Q6238

17、（L咖期R .1hiE"l5Cj 6Oii 634 583OS 73HI03307 JiiMznECl 4'->30>. 43B 0TIL '!10T3.31?0. UIM0. 53l-5Lfi0. >9562Q5C. t53220. 4fiSB7TClBCII 3C5G530 EI- ； JJ緯f*店0. tll0. 55-'549O-19132制圖3- 9復(fù)制工作表數(shù)據(jù)在Shet2工作表數(shù)據(jù)區(qū)的右側(cè)空白區(qū)域設(shè)置篩選條件區(qū)（不要緊挨數(shù)據(jù)區(qū)），單擊I3單 元格，設(shè)置條件區(qū)如圖 3- 10所示。GH:J一伽15Ir型ljRif10jl'

18、A*>304830圖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ū)域“在打開的“高級(jí)篩選”對(duì)話框中“列表區(qū)域”已自$A$2:$G$33 ”，點(diǎn)擊“條件區(qū)域”右側(cè)的文本框，Sheet2!$l$3:$J$4”，勾選“高級(jí)篩選”對(duì)話窗底部的“選擇不重復(fù)記錄”，單擊“確定”按鈕完成高級(jí)篩選。如圖3- 11所示。¾*El!l)-tAi2. WDO固條 H區(qū)Kl ¢).EheeI2 序葩3. JJll 區(qū)復(fù)制封;醛>匚硝左IR: 萬式5,1制到甘他伍遙切2'.< I=出匱It曰5l=L宦忖條SSE3fl4Cn.e345S5UL 2* B1S334 E C4 7:d 5Cfl7丨