excel典型試題操作步驟

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

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

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

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

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

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

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

8、F(A3:A52,"=c2",I3:I52)”，按回車(chē)鍵確認(rèn)，即可計(jì)算出 c2 用戶(hù)所訂購(gòu)圖書(shū)需支付的金額總數(shù)（53337）。在 L10 單元格中輸入公式“=SUMIF(A3:A52,"=c3",I3:I52)”，按回車(chē)鍵確認(rèn)，即可計(jì)算出 c3 用戶(hù)所訂購(gòu)圖書(shū)需支付的金額總數(shù)（65122）。在 L11 單元格中輸入公式“=SUMIF(A3:A52,"=c4",I3:I52)”，按回車(chē)鍵確認(rèn)，即可計(jì)算出 c4 用戶(hù)所訂購(gòu)圖書(shū)需支付的金額總數(shù)（71253）。說(shuō)明：SUMIF 函數(shù)的功能和用法，請(qǐng)參見(jiàn)上一節(jié)的相關(guān)內(nèi)容。步驟 5：在 Shee

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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