計(jì)算機(jī)二級(jí)msoffice公式excel匯總題目難題匯總

1、第十二套3.在“產(chǎn)品銷(xiāo)售匯總表”中,分別計(jì)算各型號(hào)產(chǎn)品的一、二季度銷(xiāo)量、銷(xiāo)售額及合計(jì)數(shù)，填入相應(yīng)列中。所有銷(xiāo)售額均設(shè)為數(shù)值型、小數(shù)位數(shù)0，使用千位分隔符，右對(duì)齊。步驟2：在C2單元格中插入公式“=SUMIF(一季度銷(xiāo)售情況表!$B$2:$B$44,B2,一季度銷(xiāo)售情況表!$D$2:$D$44)”，按Enter鍵，雙擊右下角的填充柄自動(dòng)填充。第十三套2.利用“成績(jī)單”、“小分統(tǒng)計(jì)”和“分值表”工作表中的數(shù)據(jù)，完成“按班級(jí)匯總”和“按學(xué)校匯總”工作表中相應(yīng)空白列的數(shù)值計(jì)算。具體提示如下：“考試學(xué)生數(shù)”列必須利用公式計(jì)算， “平均分”列由“成績(jī)單”工作表數(shù)據(jù)計(jì)算得出；“分值表”工作表中給出了本次考試

2、各題的類型及分值。（備注：本次考試一共50道小題，其中【1】至【40】為客觀題，【41】至【50】為主觀題）；“小分統(tǒng)計(jì)”工作表中包含了各班級(jí)每一道小題的平均得分，通過(guò)其可計(jì)算出各班級(jí)的“客觀題平均分”和“主觀題平均分”。（備注：由于系統(tǒng)生成每題平均得分時(shí)已經(jīng)進(jìn)行了四舍五入操作，因此通過(guò)其計(jì)算“客觀題平均分”和“主觀題平均分”之和時(shí)，可能與根據(jù)“成績(jī)單”工作表的計(jì)算結(jié)果存在一定誤差）；利用公式計(jì)算“按學(xué)校匯總”工作表中的“客觀題平均分”和“主觀題平均分”，計(jì)算方法為：每個(gè)學(xué)校的所有班級(jí)相應(yīng)平均分乘以對(duì)應(yīng)班級(jí)人數(shù)，相加后再除以該校的總考生數(shù)；計(jì)算“按學(xué)校匯總”工作表中的每題得分率，即：每個(gè)學(xué)校所

3、有學(xué)生在該題上的得分之和除以該校總考生數(shù)，再除以該題的分值。所有工作表中“考試學(xué)生數(shù)”、“最高分”、“最低分”顯示為整數(shù)；各類平均分顯示為數(shù)值格式，并保留2位小數(shù)；各題得分率顯示為百分比數(shù)據(jù)格式，并保留2位小數(shù)。(2)【解題步驟】步驟1：切換至“按班級(jí)匯總”工作表中，選擇C2單元格，在該單元格中輸入“=COUNTIFS(成績(jī)單!$A$2:$A$950,按班級(jí)匯總!$A2,成績(jī)單!$B$2:$B$950,按班級(jí)匯總!$B2)”公式，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。提示：多條件計(jì)數(shù)函數(shù)COUNTIFS（Criteria_range1，Criteria1，Criteria_ran

4、ge2，Criteria2）主要功能：統(tǒng)計(jì)指定單元格區(qū)域中符合多組條件的單元格的個(gè)數(shù)。參數(shù)說(shuō)明：Criteria_range1必需的參數(shù)。第1組條件中指定的區(qū)域。Criteria1必需的參數(shù)。第1組條件中指定的條件，條件的形式可以為數(shù)字、表達(dá)式、單元格地址或文本。Criteria_Range2,Criteria2可選參數(shù)。第2組條件，還可以有其他多組條件。本題中公式表示統(tǒng)計(jì)同時(shí)滿足以下條件的單元格的個(gè)數(shù)：成績(jī)單工作表A2:A950區(qū)域中等于按班級(jí)匯總工作表A2單元格內(nèi)容（即濱海市第一中學(xué)），并且成績(jī)單工作表B2:B950區(qū)域中等于按班級(jí)匯總工作表B2單元格內(nèi)容（即1班）。步驟2：在工作表中選擇

5、D2單元格，在該單元格中輸入公式“=MAX(成績(jī)單!$A$2:$A$950=按班級(jí)匯總!$A2)*(成績(jī)單!$B$2:$B$950=按班級(jí)匯總!$B2)*成績(jī)單!$D$2:$D$950)”，按Ctrl+Shift+Enter組合鍵完成輸入。提示：最大值函數(shù)MAX(number1,number2,)主要功能：返回一組值或指定區(qū)域中的最大值參數(shù)說(shuō)明：參數(shù)至少有一個(gè)，且必須是數(shù)值，最多可以有255個(gè)。本題中：成績(jī)單!$A$2:$A$950=按班級(jí)匯總!$A2返回值為邏輯值(True或False)，成績(jī)單!$B$2:$B$950=按班級(jí)匯總!$B2返回值也為邏輯值(True或False)，將兩個(gè)表達(dá)式

6、進(jìn)行乘運(yùn)算時(shí)，邏輯值True轉(zhuǎn)換為1，邏輯值False轉(zhuǎn)換為0參加運(yùn)算。也即，同時(shí)滿足兩個(gè)條件時(shí)，MAX中的求值區(qū)域才為非0數(shù)，最終形成的表達(dá)式只有是MAX(1*1*成績(jī)單!$D$2:$D$950)，才會(huì)統(tǒng)計(jì)這個(gè)區(qū)域的最大值。當(dāng)編輯完第一個(gè)公式后，此時(shí)注意，必須同時(shí)按下Ctrl+Shift+Enter組合鍵，才能顯示出統(tǒng)計(jì)結(jié)果，之后就可以使用填充柄進(jìn)行公式填充。步驟3：選擇E2單元格，在該單元格中輸入公式“=MIN(IF(成績(jī)單!$A$2:$A$950=按班級(jí)匯總!$A2)*(成績(jī)單!$B$2:$B$950=按班級(jí)匯總!$B2),成績(jī)單!$D$2:$D$950)”，按Ctrl+Shift+En

7、ter組合鍵完成輸入。提示：最小值函數(shù)MIN(number1,number2,)主要功能：返回一組值或指定區(qū)域中的最小值參數(shù)說(shuō)明：參數(shù)至少有一個(gè)，且必須是數(shù)值，最多可以有255個(gè)邏輯判斷函數(shù)IF(logical_test,value_if_true,value_if_false)主要功能：如果指定條件的計(jì)算結(jié)果為T(mén)RUE，IF函數(shù)將返回某個(gè)值；如果該條件的計(jì)算結(jié)果為FALSE，則返回另一個(gè)值。參數(shù)說(shuō)明：l logical_test必須的參數(shù)，作為判斷條件的任意值或表達(dá)式l value_if_true可選的參數(shù)。Logical_test參數(shù)的計(jì)算結(jié)果為T(mén)RUE時(shí)所要返回的值l value_if_

8、false可選的參數(shù)。Logical_test參數(shù)的計(jì)算結(jié)果為FALSE時(shí)所要返回的值本題中：成績(jī)單!$A$2:$A$950=按班級(jí)匯總!$A2返回值為邏輯值(True或False)，成績(jī)單!$B$2:$B$950=按班級(jí)匯總!$B2返回值也為邏輯值(True或False)，將兩個(gè)表達(dá)式進(jìn)行乘運(yùn)算時(shí)，邏輯值True轉(zhuǎn)換為1，邏輯值False轉(zhuǎn)換為0參加運(yùn)算。也即，同時(shí)滿足兩個(gè)條件時(shí)，IF函數(shù)中的條件表達(dá)式為T(mén)RUE，才能返回表達(dá)式“成績(jī)單!$D$2:$D$950”，此時(shí)使用MIN函數(shù)統(tǒng)計(jì)指定區(qū)域中的最小值步驟4：選擇F2單元格，在該單元格中輸入公式“=AVERAGEIFS(成績(jī)單!$D$2:$

9、D$950,成績(jī)單!$A$2:$A$950,按班級(jí)匯總!$A2,成績(jī)單!$B$2:$B$950,按班級(jí)匯總!$B2)”，按Enter鍵完成輸入。提示：多條件平均值函數(shù)AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,)主要功能：對(duì)指定區(qū)域中滿足多個(gè)條件的所有單元格中的數(shù)值求算數(shù)平均值。參數(shù)說(shuō)明：l average_range必需的參數(shù)。要計(jì)算平均值的實(shí)際單元格區(qū)域l criteria_range1，criteria_range2在其中計(jì)算關(guān)聯(lián)條件的區(qū)域l criteria1，criteria

10、2求平均值的條件l 其中每個(gè)criteria_range的大小和形狀必須與average_range相同本題中公式表示對(duì)成績(jī)單D2:D950區(qū)域中符合以下條件的單元格的數(shù)值求平均值：成績(jī)單工作表A2:A950區(qū)域中等于按班級(jí)匯總工作表A2單元格內(nèi)容（即濱海市第一中學(xué)），并且成績(jī)單工作表B2:B950區(qū)域中等于按班級(jí)匯總工作表B2單元格內(nèi)容（即1班）。步驟5：選擇G2單元格，在該單元格中輸入公式“=SUM(小分統(tǒng)計(jì)!$C2:$AP2)”，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。步驟6：選擇H2單元格，在該單元格中輸入公式“=SUM(小分統(tǒng)計(jì)!$AQ2:$AZ2)”，按Enter鍵完成

11、輸入，雙擊右下角的填充柄填充數(shù)據(jù)。步驟7：選擇C、D、E列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】選項(xiàng)，在彈出的對(duì)話框中選擇【分類】列表中選擇【數(shù)值】選項(xiàng)，將【小數(shù)位數(shù)】設(shè)置為0，設(shè)置完成后，單擊“確定”按鈕即可。按同樣的方式，將F、G、H列【小數(shù)位數(shù)】設(shè)置為2。步驟8：切換至“按學(xué)校匯總”工作表中，選擇B2單元格，在該單元格中輸入公式“=COUNTIF(成績(jī)單!$A$2:$A$950,按學(xué)校匯總!$A2)”，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。提示：條件計(jì)數(shù)函數(shù)COUNTIF(range,criteria)主要功能：統(tǒng)計(jì)指定區(qū)域中滿足單個(gè)指定條件的單元格的個(gè)數(shù)

12、參數(shù)說(shuō)明：l range必需的參數(shù)。計(jì)數(shù)的單元格區(qū)域l criteria必需的參數(shù)。計(jì)數(shù)的條件，條件的形式可以為數(shù)字、表達(dá)式、單元格地址或文本本題中公式表示統(tǒng)計(jì)成績(jī)單工作表A2:A950區(qū)域中，等于A2單元格內(nèi)容（即濱海市第一中學(xué)）的單元格的個(gè)數(shù)。步驟9：選擇C2單元格，在該單元格中輸入“=MAX(成績(jī)單!$A$2:$A$950=按學(xué)校匯總!$A2)*成績(jī)單!$D$2:$D$950)”，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。在D2單元格中輸入“=MIN(IF(成績(jī)單!$A$2:$A$950=按學(xué)校匯總!$A2,成績(jī)單!$D$2:$D$950)”，按Ctrl+

13、Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。在E2單元格中輸入“=AVERAGEIFS(成績(jī)單!$D$2:$D$950,成績(jī)單!$A$2:$A$950,按學(xué)校匯總!$A2)”，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。步驟10：在F2單元格中輸入“=SUM(按班級(jí)匯總!$A$2:$A$33=按學(xué)校匯總!$A2)*(按班級(jí)匯總!$C$2:$C$33)*(按班級(jí)匯總!$G$2:$G$33)/$B2”，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。在G2單元格中輸入“=SUM(按班級(jí)匯總!$A$2:$A$33=按學(xué)校匯總!$A2)*(按班級(jí)匯總

14、!$C$2:$C$33)*(按班級(jí)匯總!$H$2:$H$33)/$B2”，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。提示：本題中按班級(jí)匯總!$A$2:$A$33=按學(xué)校匯總!$A2返回的值為邏輯值(TRUE和FALSE)，參加運(yùn)算時(shí)，TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0。即統(tǒng)計(jì)指定學(xué)校的學(xué)生，使用SUM函數(shù)將數(shù)組按班級(jí)匯總!$C$2:$C$33與數(shù)組按班級(jí)匯總!$G$2:$G$33對(duì)應(yīng)位置的數(shù)據(jù)進(jìn)行乘運(yùn)算，然后求出乘積的和，最后將得到的總成績(jī)與學(xué)生人數(shù)相除，求得平均分。步驟11：在H2單元格中輸入“=SUM(小分統(tǒng)計(jì)!$A$2:$A$33=$A2)*小分統(tǒng)計(jì)!C

15、$2:C$33*按班級(jí)匯總!$C$2:$C$33)/$B2/分值表!B$3”，按Ctrl+Shift+Enter鍵完成輸入，拖動(dòng)填充柄，橫向填充到BE2單元格，緊接著從BE2單元格，拖動(dòng)填充句柄，縱向填充到BE5單元格。步驟12：選擇B、C、D列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】選項(xiàng)，在彈出的對(duì)話框中選擇【分類】列表中選擇【數(shù)值】選項(xiàng)，將【小數(shù)位數(shù)】設(shè)置為0，設(shè)置完成后，單擊“確定”按鈕。按同樣的方式，將E、F、G列【小數(shù)位數(shù)】設(shè)置為2。步驟13：選擇H列到BE列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】選項(xiàng)，在彈出的對(duì)話框中選擇【分類】列表中選擇【百分比】

16、選項(xiàng)，將【小數(shù)位數(shù)】設(shè)置為2，設(shè)置完成后，單擊“確定”按鈕即可。第14套第15套2.在“停車(chē)收費(fèi)記錄”表中，涉及金額的單元格格式均設(shè)置為保留2位的數(shù)值類型。依據(jù)“收費(fèi)標(biāo)準(zhǔn)”表，利用公式將收費(fèi)標(biāo)準(zhǔn)對(duì)應(yīng)的金額填入“停車(chē)收費(fèi)記錄”表中的“收費(fèi)標(biāo)準(zhǔn)”列；利用出場(chǎng)日期、時(shí)間與進(jìn)場(chǎng)日期、時(shí)間的關(guān)系，計(jì)算“停放時(shí)間”列，單元格格式為時(shí)間類型的“XX時(shí)XX分”。步驟4：計(jì)算停放時(shí)間，首先利用DATEDIF計(jì)算日期的差值，乘以24；再加上進(jìn)場(chǎng)時(shí)間和出場(chǎng)時(shí)間的差值。即在J2單元格中輸入“=DATEDIF(F2,H2,”YD”)*24+(I2-G2)”，按Enter，并向下拖動(dòng)將數(shù)據(jù)進(jìn)行填充。說(shuō)明：DATEDIF(

17、start_date,end_date,unit)，返回兩個(gè)日期之間的年月日間隔數(shù)。Start_date為一個(gè)日期，它代表時(shí)間段內(nèi)的第一個(gè)日期或起始日期。參數(shù)End_date為一個(gè)日期，它代表時(shí)間段內(nèi)的最后一個(gè)日期或結(jié)束日期。參數(shù)Unit為所需信息的返回類型。當(dāng)單位代碼為”YD”時(shí),計(jì)算結(jié)果是兩個(gè)日期間隔的天數(shù).忽略年數(shù)差。注：結(jié)束日期必須大于起始日期。3.依據(jù)停放時(shí)間和收費(fèi)標(biāo)準(zhǔn)，計(jì)算當(dāng)前收費(fèi)金額并填入“收費(fèi)金額”列；計(jì)算擬采用的收費(fèi)政策的預(yù)計(jì)收費(fèi)金額并填入“擬收費(fèi)金額”列；計(jì)算擬調(diào)整后的收費(fèi)與當(dāng)前收費(fèi)之間的差值并填入“差值”列。(3)【解題步驟】考點(diǎn)提示：本題主要考核公式和函數(shù)的使用。步驟1

18、：計(jì)算收費(fèi)金額，在K2單元格中輸入公式“=ROUNDUP(HOUR(J2)*60+MINUTE(J2)/15,0)*E2”，并向下自動(dòng)填充單元格。步驟2：計(jì)算擬收費(fèi)金額，在L2單元格中輸入公式“=INT(HOUR(J2)*60+MINUTE(J2)/15)*E2”，并向下自動(dòng)填充單元格。步驟3：計(jì)算差值，在M2單元格中輸入公式“=K2-L2”，并向下自動(dòng)填充單元格。說(shuō)明：1.Roundup函數(shù)表示：向上舍入數(shù)字，跟四舍五入不一樣，不管舍去的首位數(shù)字是否大于4，都向前進(jìn)1。2.Roundup函數(shù)的語(yǔ)法格式=Roundup(number,num_digits)Number表示：用來(lái)向上舍入的數(shù)字。

19、Num_digits舍入后的數(shù)字的小數(shù)位數(shù)（即保留幾位小數(shù)）。3.當(dāng)Roundup函數(shù)中參數(shù)num_digits大于0時(shí)，則表示向上舍入到指定的小數(shù)位（這里保留一位小數(shù)）。第17套3.課時(shí)費(fèi)統(tǒng)計(jì)表中的F至I列中的空白內(nèi)容必須采用公式的方式計(jì)算結(jié)果。根據(jù)教師基本信息工作表和課時(shí)費(fèi)標(biāo)準(zhǔn)工作表計(jì)算職稱和課時(shí)標(biāo)準(zhǔn)列內(nèi)容，根據(jù)授課信息表和課程基本信息工作表計(jì)算學(xué)時(shí)數(shù)列內(nèi)容，最后完成課時(shí)費(fèi)列的計(jì)算?！咎崾荆航ㄗh對(duì)授課信息表中的數(shù)據(jù)按姓名排序后增加學(xué)時(shí)數(shù)列，并通過(guò)VLOOKUP查詢課程基本信息表獲得相應(yīng)的值。】(3)【解題步驟】步驟1：選擇F3單元格，單擊【公式】|【函數(shù)庫(kù)】組中的“插入函數(shù)”按鈕，彈出“插

20、入函數(shù)”對(duì)話框，在“選擇函數(shù)”下拉列表中找到VLOOKUP函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框。步驟2：在第1個(gè)參數(shù)框中用鼠標(biāo)選擇“E3”；第2個(gè)參數(shù)框中選擇“教師基本信息”工作表中的D3：E22區(qū)域；第3個(gè)參數(shù)框中輸入“2”；第4個(gè)參數(shù)框中輸入“FALSE”或者“0”；單擊“確定”按鈕即可。步驟3：本題也可直接在“課時(shí)費(fèi)統(tǒng)計(jì)表”工作表的F3單元格中輸入公式“=VLOOKUP(E3,教師基本信息!$D$3:$E$22,2,FALSE)”，按Enter鍵，雙擊F3單元格右下角的填充柄完成職稱的自動(dòng)填充。步驟4：同理，使用VLOOKUP函數(shù)計(jì)算“課時(shí)標(biāo)準(zhǔn)”。第1個(gè)參數(shù)框中用鼠標(biāo)選擇“G3

21、”；第2個(gè)參數(shù)框中選擇“課時(shí)費(fèi)標(biāo)準(zhǔn)”工作表中的A3：B6區(qū)域；第3個(gè)參數(shù)框中輸入“2”；第4個(gè)參數(shù)框中輸入“FALSE”或者“0”；單擊“確定”按鈕即可。步驟5：本題也可直接在“課時(shí)費(fèi)統(tǒng)計(jì)表”工作表的G3單元格中輸入公式“=VLOOKUP(F3,課時(shí)費(fèi)標(biāo)準(zhǔn)!$A$3:$B$6,2,FALSE)”，按Enter鍵，雙擊G3單元格右下角的填充柄完成課時(shí)標(biāo)準(zhǔn)的自動(dòng)填充。步驟6：根據(jù)題目提示，先在“授課信息表”中增加“學(xué)時(shí)數(shù)”列。在“授課信息表”的F2單元格輸入“學(xué)時(shí)數(shù)”，回車(chē)選擇“F3”，同理使用VLOOKUP函數(shù)計(jì)算“學(xué)時(shí)數(shù)”。第1個(gè)參數(shù)框中用鼠標(biāo)選擇“E3”；第2個(gè)參數(shù)框中選擇“課程基本信息”工

22、作表中的B3：C16區(qū)域；第3個(gè)參數(shù)框中輸入“2”；第4個(gè)參數(shù)框中輸入“FALSE”或者“0”；單擊“確定”按鈕，雙擊F3單元格右下角的填充柄完成學(xué)時(shí)數(shù)的自動(dòng)填充。步驟7：根據(jù)題目提示，再計(jì)算“課時(shí)費(fèi)統(tǒng)計(jì)表”中的“學(xué)時(shí)數(shù)”列。切換到“課時(shí)費(fèi)統(tǒng)計(jì)表”工作表，選擇H3單元格，單擊【公式】|【函數(shù)庫(kù)】組中的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對(duì)話框，在“選擇函數(shù)”下拉列表中找到SUMIF函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框。步驟8：在第1個(gè)參數(shù)框中用鼠標(biāo)選擇“授課信息表”的“D3：D72”數(shù)據(jù)區(qū)域；第2個(gè)參數(shù)框中輸入“E3”；第3個(gè)參數(shù)框中輸入選擇“授課信息表”的“F3：F72”數(shù)據(jù)區(qū)域；單

23、擊“確定”按鈕，雙擊H3單元格右下角的填充柄自動(dòng)填充到最后一行。步驟9：在I3單元格中輸入公式“=G3*H3”完成課時(shí)費(fèi)的計(jì)算。第18套2.輸入并填充公式：在余額列輸入計(jì)算公式，余額=上期余額+本期借方-本期貸方，以自動(dòng)填充方式生成其他公式。 3.方向列中只能有借、貸、平三種選擇，首先用數(shù)據(jù)有效性控制該列的輸入范圍為借、貸、平三種中的一種，然后通過(guò)IF函數(shù)輸入方向列內(nèi)容，判斷條件如下所列： 余額大于0等于0小于0 方向借平貸第20套4.學(xué)號(hào)第4、5位代表學(xué)生所在的班級(jí)，例如：C120101代表12級(jí)1班。請(qǐng)通過(guò)函數(shù)提取每個(gè)學(xué)生所在的專業(yè)并按下列對(duì)應(yīng)關(guān)系填寫(xiě)在班級(jí)列中： 學(xué)號(hào)的4、5位對(duì)應(yīng)班級(jí) 011班 022班