excel典型試題操作步驟

1、前言1296.2 典型試題典型試題 1打開素材庫中的“典型試題 6-1.xls”文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數據表進行更改。一、操作要求1. 在Sheet5的A1單元格中設置為只能錄入5位數字或文本。當錄入位數錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數字或文本”。2. 使用數組公式，對 Sheet1 中“教材訂購情況表”的訂購金額進行計算，并將結果保存在該表的“金額”列當中。計算方法：金額 = 訂數 * 單價3. 使用統(tǒng)計函數，對 Sheet1 中“教材訂購情況表”的結果按以下條件進行統(tǒng)計，并 將結果保存在 Sheet1 中的相

2、應位置。要求：（1）統(tǒng)計出版社名稱為“高等教育出版社”的書的種類數,并將結果保存在 sheet1中 L2 單元格中。（2）統(tǒng)計訂購數量大于 110 且小于 850 的書的種類數,并將結果保存在 sheet1 中L3 單元格中。4. 使用函數，計算每個用戶所訂購圖書所需支付的金額總數，并將結果保存在 Sheet1中“用戶支付情況表”的“支付總額”列中。5. 使用函數，判斷 Sheet2 中的年份是否為閏年，如果是，結果保存“閏年”，如果不是，則結果保存“平年”，并將結果保存在“是否為閏年”列中。說明：閏年定義：年數能被 4 整除而不能被 100 整除，或者能被 400 整除的年份。6. 將 Sh

3、eet1 中的“教材訂購情況表”復制到 Sheet3 中，對 Sheet3 進行高級篩選.要求：（1）篩選條件為“訂數>=500，且金額總數<=30000”。（2）將結果保存在 Sheet3 中。注意：（1）無需考慮是否刪除或移動篩選條件。（2）復制過程中，將標題項“教材訂購情況表”連同數據一同復制。（3）數據表必須頂格放置。7. 根據 Sheet1 中“教材訂購情況表”的結果，在 Sheet4 中新建一張數據透視表。 要求：（1）顯示每個客戶在每個出版社所訂的教材數目。（2）行區(qū)域設置為“出版社”。（3）列區(qū)域設置為“客戶”。（4）求和項為“訂數”。（5）數據區(qū)域設置為“訂數”。

4、二、解答步驟 1：選中 Sheet5 工作表中的 A1 單元格，選擇菜單“數據”“有效性”命 令（如圖 6-13 所示），在打開的“數據有效性”對話框中，選擇“設置”選項卡， 再選擇“允許”為“文本長度”，“數據”為“等于”，在“長度”文本框中輸入“5”， 如圖 6-14 所示。選擇“出錯警告”選項卡，再選擇“樣式”為“警告”，在“錯誤信息”文本框中輸入“只能錄入 5 位數字或文本”，如圖 6-15 所示，單擊“確定”按鈕。圖 6-13 選擇“有效性”命令圖 6-14 “設置”選項卡圖 6-15 “出錯警告”選項卡步驟 2 ：在 Sheet1 工作表中，先選中 I3:I52 單元格區(qū)域，再輸入

5、公式“=G3:G52*H3:H52”，然后同時按組合鍵 Shift+Ctrl+Enter，此時，公式編輯欄顯示“=G3:G52*H3:H52”。說明：公式中單元格或單元格區(qū)域也可用鼠標選擇輸入，下同。步驟 3：選中 L2 單元格，單擊插入函數按扭，打開“插入函數”對話框，在 “選擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數”列表框中選擇“COUNTIF” 函數，如圖 6-16 所示。單擊“確定”按鈕，彈出“函數參數”對話框，輸入“Range” 參數為“D3：D52”，“Criteria”參數為“高等教育出版社”，如圖 6-17 所示。再按 “確定”按鈕，即可統(tǒng)計出出版社名稱為“高等教育出版社

6、”的書的種類數（6）。此 時，公式編輯欄顯示“=COUNTIF(D3:D52,"高等教育出版社")”。圖 6-16 “插入函數”對話框圖 6-17 “函數參數”對話框選 中L3單 元 格 ， 在 公 式 編 輯 欄 中 輸 入 公 式 “=COUNTIF(G3:G52,"<850")-COUNTIF(G3:G52,"<=110")”，再按回車鍵確認，即可統(tǒng) 計出訂購數量大于 110 且小于 850 的書的種類數（28）。說明：COUNTIF 函數的功能和用法，請參見上一節(jié)的相關內容。步驟 4：選中 L8 單元格，單擊插入函

7、數按扭，打開“插入函數”對話框，在 “選擇類別”下拉列表框中選擇“數學與三角函數”，在“選擇函數”列表框中選擇 “SUMIF”函數。單擊“確定”按鈕，彈出“函數參數”對話框，輸入“Range”參數 為“A3：A52”，“Criteria”參數為“"=c1"”，“Sum_range”參數為“I3:I52”，如圖6-18 所示。再按“確定”按鈕，即可計算出 c1 用戶所訂購圖書需支付的金額總數（721301）。此時，公式編輯欄顯示“=SUMIF(A3:A52,"=c1",I3:I52)”。圖 6-18 “函數參數”對話框在 L9 單元格中輸入公式“=SUMI

8、F(A3:A52,"=c2",I3:I52)”，按回車鍵確認，即可計算出 c2 用戶所訂購圖書需支付的金額總數（53337）。在 L10 單元格中輸入公式“=SUMIF(A3:A52,"=c3",I3:I52)”，按回車鍵確認，即可計算出 c3 用戶所訂購圖書需支付的金額總數（65122）。在 L11 單元格中輸入公式“=SUMIF(A3:A52,"=c4",I3:I52)”，按回車鍵確認，即可計算出 c4 用戶所訂購圖書需支付的金額總數（71253）。說明：SUMIF 函數的功能和用法，請參見上一節(jié)的相關內容。步驟 5：在 Shee

9、t2 工作表中，選擇 B2 單元格，在公式編輯欄中輸入公式 “=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),"閏年","平年")”， 按回車鍵確認，即可判斷出該年份是“閏年”還是“平年”。雙擊 B2 單元格的填充柄，即可判斷出其余年份是“閏年”還是“平年”。說明：IF、OR、AND、MOD 函數的功能和用法，請參見上一節(jié)的相關內容。步驟 6：選中 Sheet1 工作表中的 A1：I52 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復制”命令。單擊 Sheet3 工作表中的 A1 單元格

10、，單擊右鍵在快捷菜單中選擇“粘 貼”命令。在 Sheet3 工作表的空白區(qū)域創(chuàng)建篩選條件，如圖 6-19 所示，在 K2：L3 單元格 區(qū)域中輸入篩選條件。單擊 Sheet3 工作表中 A1：I52 單元格區(qū)域中的任一單元格， 選擇菜單“數據”“篩選”“高級篩選”命令（如圖 6-20 所示），打開“高級篩 選”對話框，“列表區(qū)域”文本框中已自動填入數據清單所在的單元格區(qū)域（如圖 6-21 所示）。將光標定位在“條件區(qū)域”文本框內，用鼠標拖選前面創(chuàng)建的篩選條件單元格區(qū)域 K2：L3，“條件區(qū)域”文本框內會自動填入該區(qū)域地址（如圖 6-22 所示）。再單擊“確定”按鈕。圖 6-19 創(chuàng)建篩選條件圖

11、 6-20 選擇“高級篩選”命令圖 6-21 設置“列表區(qū)域” 圖 6-22 設置“條件區(qū)域”步驟 7：單擊 Sheet1 工作表中 A1：I52 單元格區(qū)域中的任一單元格，選擇菜單“數 據”“數據透視表和數據透視圖”命令（如圖 6-23 所示），打開“數據透視表和數 據透視圖向導3 步驟之 1”對話框（如圖 6-24 所示），選中“數據透視表”單選 鈕后，再單擊“下一步”按鈕，打開“數據透視表和數據透視圖向導3 步驟之 2” 對話框，“選定區(qū)域”文本框中已自動填入數據清單所在的單元格區(qū)域（如圖 6-25 所 示），單擊“下一步”按鈕，打開“數據透視表和數據透視圖向導3 步驟之 3”對 話框，

12、選中“現有工作表”單選鈕，再單擊 Sheet4 工作表中的 A1 單元格，該單元格 地址會自動填入“現有工作表”下方的地址文本框中（如圖 6-26 所示），單擊“完成” 按鈕。在彈出的如圖 6-27 所示界面中，將字段“出版社”拖至行字段區(qū)域，將字段“客戶”拖至列字段區(qū)域，將字段“訂數”拖至數據項區(qū)域，結果如圖 6-28 所示。保存文件后退出。圖 6-23 選擇“數據透視表和數據透視圖”命令圖 6-24 “數據透視表和數據透視圖向導3 步驟之 1”對話框圖 6-25 “數據透視表和數據透視圖向導3 步驟之 2”對話框圖 6-26 “數據透視表和數據透視圖向導3 步驟之 3”對話框圖 6-27

13、“數據透視表”布局前言139圖 6-28 數據透視表典型試題 2打開素材庫中的“典型試題 6-2.xls”文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數據表進行更改。一、操作要求1. 在Sheet5的A1單元格中設置為只能錄入5位數字或文本。當錄入位數錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數字或文本”。2. 使用時間函數，對 Sheet1 中用戶的年齡進行計算。要求：假設當前時間是“2008-11-15”，結合用戶的出生年月，計算用戶的年齡，并將其計算結果保存在“年齡”列當中。計算方法為兩個時間年份之差。3. 使用 REPLACE 函數，對

14、Sheet1 中用戶的電話號碼進行升級。 要求：對“原電話號碼”列中的電話號碼進行升級。升級方法是在區(qū)號（0571）后面加上“8”，并將其計算結果保存在“升級電話號碼”列的相應單元格中。例如：電話號碼“05716742808” 升級后為 “057186742808”4. 在 Sheet1 中，使用 AND 函數，根據“性別”及“年齡”列中的數據，判斷所有用戶是否為大于等于 40 歲的男性，并將結果保存在“是否>=40 男性”列中。注意：如果是，保存結果為 TRUE；否則，保存結果為 FALSE。5. 根據 Sheet1 中的數據，對以下條件，使用統(tǒng)計函數進行統(tǒng)計。 要求：（1）統(tǒng)計性別為

15、“男”的用戶人數，將結果填入 Sheet2 的 B2 單元格中。（2）統(tǒng)計年齡為“>40”歲的用戶人數，將結果填入 Sheet2 的 B3 單元格中。6. 將 Sheet1 復制到 Sheet3，并對 Sheet3 進行高級篩選。 要求：（1）篩選條件為：“性別”女、“所在區(qū)域”西湖區(qū)。（2）將篩選結果保存在 Sheet3 中。注意：（1）無需考慮是否刪除或移動篩選條件。（2）數據表必須頂格放置。7. 根據 Sheet1 的結果，創(chuàng)建一個數據透視圖 Chart1。 要求：（1）顯示每個區(qū)域所擁有的用戶數量。（2）x 坐標設置為“所在區(qū)域”。（3）計數項為“所在區(qū)域”。（4）將對應的數據透

16、視表保存在 Sheet4 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：在 Sheet1 工作表中，選中 D2 單元格，在公式編輯欄中輸入公式 “=YEAR("2008-11-15")-YEAR(C2)”，再按回車鍵確認，即計算出該用戶的年齡（41）。 雙擊 D2 單元格的填充柄，即可計算出其余用戶的年齡。說明：YEAR 函數的功能和用法，請參見上一節(jié)的相關內容。步驟 3：選中 G2 單元格，單擊插入函數按扭，打開“插入函數”對話框，在“選 擇類別”下拉列表框中選擇“文本”，在“選擇函數”列表框中選擇“REPLACE”函數， 單擊“確定”按鈕后，

17、彈出“函數參數”對話框，輸入“Old_text”參數為“F2”， “Start_num”參數為“5”，“Num_chars”參數為“0”，“New_text”參數為“8”，如 圖 6-29 所示。再按“確定”按鈕，即可對第一個電話號碼進行升級。此時，公式編 輯欄顯示“=REPLACE(F2,5,0,"8")”。雙擊 G2 單元格的填充柄，即可對其余電話號碼進行升級。說明：REPLACE 函數的功能和用法，請參見上一節(jié)的相關內容。圖 6-29 “函數參數”對話框步驟 4：在 H2 單元格中，輸入公式“=AND(B2="男",D2>=40)”，再按回車

18、鍵確認，即可判斷該用戶是否為大于等于 40 歲的男性。雙擊 H2 單元格的填充柄，即可判斷其余用戶是否為大于等于 40 歲的男性。步驟 5：在 Sheet2 工作表的 B2 單元格中，輸入公式“=COUNTIF(Sheet1!B2:B37,"男")”，再按回車鍵確認，即可統(tǒng)計出男性用戶人數（18）。在 Sheet2 工作表的 B3 單元格中，輸入公式“=COUNTIF(Sheet1!D2:D37,">40")”，再按回車鍵確認，即可統(tǒng)計出大于 40 歲的用戶人數（14）。步驟 6：選中 Sheet1 工作表中的 A1：H37 單元格區(qū)域，單擊右鍵在

19、快捷菜單中選 擇“復制”命令。單擊 Sheet3 工作表中的 A1 單元格，單擊右鍵在快捷菜單中選擇“粘 貼”命令。在 Sheet3 工作表的空白區(qū)域創(chuàng)建篩選條件，如圖 6-30 所示，在 J1：K2 單元格區(qū)域 中輸入篩選條件。單擊 Sheet3 工作表中 A1：H37 單元格區(qū)域中的任一單元格，選擇 菜單“數據”“篩選”“高級篩選”命令，打開“高級篩選”對話框，“列表區(qū) 域”文本框中已自動填入數據清單所在的單元格區(qū)域。將光標定位在“條件區(qū)域”文 本框內，用鼠標拖選前面創(chuàng)建的篩選條件單元格區(qū)域 J1：K2，“條件區(qū)域”文本框內 會自動填入該區(qū)域地址（如圖 6-31 所示）。再單擊“確定”按鈕

20、。注意：J1 單元格中“性”和“別”之間有空格，原因是 B1 單元格中“性”和“別”之間有空格。圖 6-30 創(chuàng)建篩選條件圖 6-31 設置“條件區(qū)域”步驟 7：單擊 Sheet1 工作表中 A1：H37 單元格區(qū)域中的任一單元格，選擇菜單“數據”“數據透視表和數據透視圖”命令，打開“數據透視表和數據透視圖向導3 步驟之 1”對話框，選中“數據透視圖（及數據透視表）”單選鈕（如圖 6-32 所示）， 再單擊“下一步”按鈕，打開“數據透視表和數據透視圖向導3 步驟之 2”對話 框，“選定區(qū)域”文本框中已自動填入數據清單所在的單元格區(qū)域（如圖 6-33 所示）， 單擊“下一步”按鈕，打開“數據透視

21、表和數據透視圖向導3 步驟之 3”對話框， 選中“現有工作表”單選鈕，再單擊 Sheet4 工作表中的 A1 單元格，該單元格地址會 自動填入“現有工作表”下方的地址文本框中（如圖 6-34 所示）。單擊“布局”按鈕， 在彈出的如圖 6-35 所示界面中，將字段“所在區(qū)域”分別拖至“行”區(qū)域和“數據” 區(qū)域內，然后按“確定”按鈕返回圖 6-34 所示對話框，按“完成”按鈕。這時在 Sheet4 工作表前會插入一張名為 Chart1 的工作表。打開該工作表可看到根據 Sheet1 工作表 創(chuàng)建的數據透視圖（如圖 6-36 所示），打開 Sheet4 工作表有如圖 6-37 所示的相應的 數據透視

22、表。保存文件后退出。圖 6-32 “數據透視表和數據透視圖向導3 步驟之 1”對話框圖 6-33 “數據透視表和數據透視圖向導3 步驟之 2”對話框圖 6-34 “數據透視表和數據透視圖向導3 步驟之 3”對話框圖 6-35 “數據透視表”布局圖 6-36 數據透視圖圖 6-37 數據透視表典型試題 3打開素材庫中的“典型試題 6-3.xls”文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數據表進行更改。一、操作要求1. 在Sheet4的A1單元格中設置為只能錄入5位數字或文本。當錄入位數錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數字或文本”。2.

23、 使用 VLOOKUP 函數，對 Sheet1 中的“三月份銷售統(tǒng)計表”的“產品名稱”列和“產 品單價”列進行填充。要求：根據“企業(yè)銷售產品清單”，使用 VLOOKUP 函數，將產品名稱和產品單價填充到“三月份銷售統(tǒng)計表”的“產品名稱”列和“產品單價”列中。3. 使用數組公式，計算 Sheet1 中的“三月份銷售統(tǒng)計表”中的銷售金額，并將結果填入到該表的“銷售金額”列中。計算方法：銷售金額 = 產品單價 * 銷售數量4. 使用統(tǒng)計函數，根據“三月份銷售統(tǒng)計表”中的數據，計算“分部銷售業(yè)績統(tǒng)計表”中的總銷售額，并將結果填入該表的“總銷售額”列。5. 在 Sheet1 中，使用 RANK 函數，在

24、“分部銷售業(yè)績統(tǒng)計”表中，根據“總銷售額”對各部門進行排名，并將結果填入到“銷售排名”列中。6. 將 sheet1 中的“三月份銷售統(tǒng)計表”復制到 Sheet2 中，對 Sheet2 進行高級篩選。 要求：（1）篩選條件為：“銷售數量” >3、“所屬部門” 市場 1 部、“銷售金額”->1000。（2）將篩選結果保存在 Sheet2 中。注意：（1）無需考慮是否刪除或移動篩選條件。（2）復制過程中，將標題項“三月份銷售統(tǒng)計表”連同數據一同復制。（3）數據表必須頂格放置。7. 根據 Sheet1 的“三月份銷售統(tǒng)計表”中的數據，新建一個數據透視圖 Chart1。 要求：（1）該圖形顯

25、示每位經辦人的總銷售額情況。（2）x 坐標設置為“經辦人”。（3）數據區(qū)域設置為“銷售金額”。（4）求和項為“銷售金額”。（5）將對應的數據透視表保存在 Sheet3 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：選中 Sheet1 工作表中的 G3 單元格，單擊插入函數按扭，打開“插入函數” 對話框，在“選擇類別”下拉列表框中選擇“查找與引用”，在“選擇函數”列表框 中選擇“VLOOKUP”函數，單擊“確定”按鈕后，彈出“函數參數”對話框。輸入 “ Lookup_value ” 參 數為 “ F3 ”，“ Table_array ” 參 數為 “ $A$3:$B$

26、10 ”， “Col_index_num”參數為“2”，“Range_lookup”參數為“FALSE”，如圖 6-38 所示。 再按“確定”按鈕，即可查找到產品型號為“A01”的產品名稱（卡特掃描槍）。此時， 公式編輯欄顯示“=VLOOKUP(F3,$A$3:$B$10,2,FALSE)”。雙擊 G3 單元格的填充柄，即可查找到其余產品型號的產品名稱。在 H3 單元格中，輸入公式“=VLOOKUP(F3,$A$3:$C$10,3,FALSE)”，再按回車鍵確認， 即可查找到產品型號為“A01”的產品單價（368）。雙擊 H3 單元格的填充柄，即可查 找到其余產品型號的產品單價。說明：VLOO

27、KUP 函數的功能和用法，請參見上一節(jié)的相關內容。圖 6-38 “函數參數”對話框步驟 3：先選中 L3:L44 單元格區(qū)域，再輸入公式“=H3:H44*I3:I44”，然后同時按組合鍵 Shift+Ctrl+Enter，此時，公式編輯欄顯示“=H3:H44*I3:I44”。前言149步驟 4：選中 O3 單元格，單擊插入函數按扭，打開“插入函數”對話框，在“選擇類別”下拉列表框中選擇“數學與三角函數”，在“選擇函數”列表框中選擇“SUMIF”函數。單擊“確定”按鈕，彈出“函數參數”對話框，輸入“Range”參數 為“K3：K44”，“Criteria”參數為“"=市場 1 部&qu

28、ot;”，“Sum_range”參數為“L3:L44”， 如圖 6-39 所示。再按“確定”按鈕，即可計算出市場 1 部的總銷售額（35336）。此 時，公式編輯欄顯示“=SUMIF(K3:K44,"=市場 1 部",L3:L44)”。在 O4 單元格中，輸入公式“=SUMIF(K3:K44,"=市場 2 部",L3:L44)”后，按回車鍵確認。在 O5 單元格中，輸入公式“=SUMIF(K3:K44,"=市場 3 部",L3:L44)”后，按回車鍵確認。圖 6-39 “函數參數”對話框步驟 5：選中 P3 單元格，單擊插入函數按扭，

29、打開“插入函數”對話框，在 “選擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數”列表框中選擇“RANK”函 數。單擊“確定”按鈕，彈出“函數參數”對話框，輸入“Number”參數為“O3”，“Ref” 參數為“$O$3:$O$5”，“Order”參數為“0”，如圖 6-40 所示。再按“確定”按鈕， 即 可 統(tǒng) 計 出 市場 1 部 的 總 銷售額 排 名 （ 1 ）。 此 時 ，公 式 編 輯 欄顯 示 “=RANK(O3,$O$3:$O$5,0)”。雙擊 P3 單元格的填充柄，可統(tǒng)計出其他部門的總銷售額排名。說明：RANK 函數的功能和用法，請參見上一節(jié)的相關內容。圖 6-40 “函數參數

30、”對話框步驟 6：選中 Sheet1 工作表中的 E1：L44 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復制”命令。單擊 Sheet2 工作表中的 A1 單元格，單擊右鍵在快捷菜單中選擇“選 擇性粘貼”命令，打開如圖 6-41 所示的“選擇性粘貼”對話框，選中“值和數字格 式”單選鈕，再單擊“確定”按鈕。在 Sheet2 工作表的空白區(qū)域創(chuàng)建篩選條件，如圖 6-42 所示，在 J2：L3 單元格 區(qū)域中輸入篩選條件。單擊 Sheet2 工作表中 A1：H44 單元格區(qū)域中的任一單元格， 選擇菜單“數據”“篩選”“高級篩選”命令，打開“高級篩選”對話框，“列 表區(qū)域”文本框中已自動填入數據清單所

31、在的單元格區(qū)域。將光標定位在“條件區(qū)域” 文本框內，用鼠標拖選前面創(chuàng)建的篩選條件單元格區(qū)域 J2：L3，“條件區(qū)域”文本框 內會自動填入該區(qū)域地址（如圖 6-43 所示）。再單擊“確定”按鈕。圖 6-41 “選擇性粘貼”對話框圖 6-42 創(chuàng)建篩選條件圖 6-43 設置“條件區(qū)域”步驟 7：單擊 Sheet1 工作表中 E1：L44 單元格區(qū)域中的任一單元格，選擇菜單“數據”“數據透視表和數據透視圖”命令，打開“數據透視表和數據透視圖向導3 步驟之 1”對話框，選中“數據透視圖（及數據透視表）”單選鈕，再單擊“下一步” 按鈕，打開“數據透視表和數據透視圖向導3 步驟之 2”對話框，“選定區(qū)域”

32、文 本框中已自動填入數據清單所在的單元格區(qū)域，單擊“下一步”按鈕，打開“數據透 視表和數據透視圖向導3 步驟之 3”對話框，選中“現有工作表”單選鈕，再單 擊 Sheet3 工作表中的 A1 單元格，該單元格地址會自動填入“現有工作表”下方的地 址文本框中。單擊“布局”按鈕，在彈出的如圖 6-44 所示界面中，把字段“經辦人” 拖至“行”區(qū)域內，把字段“銷售金額”拖至“數據”區(qū)域內，然后按“確定”按鈕 返回，再按“完成”按鈕。這時在 Sheet3 工作表前會插入一張名為 Chart1 的工作表。 打開該工作表可看到根據 Sheet1 中“三月份銷售統(tǒng)計表”創(chuàng)建的數據透視圖（如圖6-45 所示）

33、，打開 Sheet3 工作表有如圖 6-46 所示的相應的數據透視表。保存文件后退出。圖 6-44 “數據透視表”布局圖 6-45 數據透視圖圖 6-46 數據透視表典型試題 4打開素材庫中的“典型試題 6-4.xls”文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數據表進行更改。一、操作要求1. 在Sheet4的A1單元格中設置為只能錄入5位數字或文本。當錄入位數錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數字或文本”。2. 使用 HLOOKUP 函數，對 Sheet1“停車情況記錄表”中的“單價”列進行填充。 要求：根據 Sheet1 中的“停車

34、價目表”價格，使用 HLOOKUP 函數對“停車情況記錄表”中的“單價”列根據不同的車型進行填充。注意：函數中如果需要用到絕對地址的將使用絕對地址進行計算，其他方式無效。3. 在 Sheet1 中，使用時間函數計算汽車在停車庫中的停放時間。 要求：（1）計算方法為：“停放時間 = 出庫時間 -入庫時間”。（2）格式為：“小時：分鐘：秒”。（3）將結果保存在“停車情況記錄表”中的“停放時間”列中。（例如：一小時十五分十二秒在停放時間中的表示為：“1：15：12”）4. 使用函數公式，對“停車情況記錄表”的停車費用進行計算。要求：根據 Sheet1 停放時間的長短計算停車費用，將計算結果填入到“停

35、車情況記錄表”的“應付金額”列中。注意：（1）停車按小時收費，對于不滿一個小時的按照一個小時計費。（2）對于超過整點小時數十五分鐘（包含十五分鐘）的多累積一個小時。（例如 1 小時 23 分，將以 2 小時計費）5. 使用統(tǒng)計函數，對 Sheet1 中的“停車情況記錄表”根據下列條件進行統(tǒng)計。 要求：（1）統(tǒng)計停車費用大于等于 40 元的停車記錄條數，并將結果保存在 J8 單元格中。（2）統(tǒng)計最高的停車費用，并將結果保存在 J9 單元格中。6. 將 Sheet1 中的“停車情況記錄表”復制到 Sheet2 中，對 Sheet2 進行高級篩選。 要求：（1）篩選條件為：“車型” 小汽車，“應付金

36、額”>30。（2）將結果保存在 Sheet2 中。注意：（1）無需考慮是否刪除篩選條件。（2）復制過程中，將標題項“停車情況記錄表”連同數據一同復制。（3）數據表必須頂格放置。7. 根據 Sheet1 中的“停車情況記錄表”，創(chuàng)建一個數據透視圖 Chart1。 要求：（1）顯示各種車型所收費用的匯總。（2）x 坐標設置為“車型”。（3）求和項為“應付金額”。（4）將對應的數據透視表保存在 Sheet3 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：選中 Sheet1 工作表中的 C9 單元格，單擊插入函數按扭，打開“插入函數” 對話框，在“選擇類別”下拉列表框

37、中選擇“查找與引用”，在“選擇函數”列表框 中選擇“HLOOKUP”函數，單擊“確定”按鈕后，彈出“函數參數”對話框。輸入 “ Lookup_value ” 參 數為 “ B9 ”，“ Table_array ” 參 數為 “ $A$2:$C$3 ”， “Row_index_num”參數為“2”，“Range_lookup”參數為“FALSE”，如圖 6-47 所示。 再按“確定”按鈕，即可查找到第一輛車的停車單價（5）。此時，公式編輯欄顯示 “=HLOOKUP(B9,$A$2:$C$3,2,FALSE)”。雙擊 C9 單元格的填充柄，即可查找到其余車輛的停車單價。說明：HLOOKUP 函數的

38、功能和用法，請參見上一節(jié)的相關內容。圖 6-47 “函數參數”對話框步驟 3：在 F9 單元格中，輸入公式“=E9-D9”，再按回車鍵確認，即計算出第一輛車的停放時間（3:03:10）。雙擊 F9 單元格的填充柄，即可計算出其余車輛的停放時間。步 驟4 ： 選 中G9單 元 格 ， 在 公 式 編 輯 欄 中 輸 入 公 式 “=IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9)*C9”，再按回車鍵 確認，即可計算出第一輛車的停車費用（15）。雙擊 G9 單元格的填充柄，即可計算出其余車輛的停車費用。說明：HOUR、MINUTE 函數的功能和用法，請參見上一節(jié)的相關內容。上述公式中 用到了 IF 函數的嵌套（即函數中還有函數）。步驟 5：選中 J8 單元格，單擊插入函數按扭，打開“插入函數”對話框，在“選 擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數”列表框中選擇“COUNTIF”函數，單擊“確定”按鈕后，彈出“函數參數”對話框，輸入“Range”參數為“G9:G39”， “Criteria”參數為“">=40"”，如圖 6-48 所示。再按“確定”按鈕，即可統(tǒng)計出停 車費 用大 于 等 于