計算機二級ms-office公式excel題目(難題)

計算機二級ms-office公式excel匯總題目(難題匯總)第十二套3.

在

“產(chǎn)品銷售匯總表”中,分別計算各型號產(chǎn)品的一、二季度銷量、銷售額及合計數(shù)，填入相應(yīng)列中。所有銷售額均設(shè)為數(shù)值型、小數(shù)位數(shù)0，使用千位分隔符，右對齊。步驟2：

在C2單元格中插入公式“=SUMIF(一季度銷售情況表!$B$2:$B$44,

B2,一季度銷售情況表!$D$2:$D$44)”，按Enter鍵，雙擊右下角的填充柄自動填充。

[Criteria_range2，Criteria2]…）

主要功能：統(tǒng)計指定單元格區(qū)域中符合多組條件的單元格的個數(shù)。

參數(shù)說明：Criteria_range1

必需的參數(shù)。第1組條件中指定的區(qū)域。

Criteria1

必需的參數(shù)。第1組條件中指定的條件，條件的形式可以為數(shù)字、表達式、單元格地址或文本。

Criteria_Range2,

Criteria2

可選參數(shù)。第2組條件，還可以有其他多組條件。

本題中公式表示統(tǒng)計同時滿足以下條件的單元格的個數(shù)：成績單工作表A2:A950區(qū)域中等于按班級匯總工作表A2單元格內(nèi)容（即濱海市第一中學(xué)），并且成績單工作表B2:B950區(qū)域中等于按班級匯總工作表B2單元格內(nèi)容（即1班）。

步驟2：在工作表中選擇D2單元格，在該單元格中輸入公式“=MAX((成績單!$A$2:$A$950=按班級匯總!$A2)*(成績單!$B$2:$B$950=按班級匯總!$B2)*成績單!$D$2:$D$950)”，按Ctrl+Shift+Enter組合鍵完成輸入。

提示：最大值函數(shù)MAX(number1,[number2],…)

主要功能：返回一組值或指定區(qū)域中的最大值

參數(shù)說明：參數(shù)至少有一個，且必須是數(shù)值，最多可以有255個。

本題中：成績單!$A$2:$A$950=按班級匯總!$A2

返回值為邏輯值(True或False)，

成績單!$B$2:$B$950=按班級匯總!$B2

返回值也為邏輯值(True或False)，

將兩個表達式進行乘運算時，邏輯值True轉(zhuǎn)換為1，邏輯值False轉(zhuǎn)換為0參加運算。

也即，同時滿足兩個條件時，MAX中的求值區(qū)域才為非0數(shù)，最終形成的表達式只有是MAX(1*1*成績單!$D$2:$D$950)，才會統(tǒng)計這個區(qū)域的最大值。

當(dāng)編輯完第一個公式后，此時注意，必須同時按下Ctrl+Shift+Enter組合鍵，才能顯示出統(tǒng)計結(jié)果，之后就可以使用填充柄進行公式填充。

步驟3：選擇E2單元格，在該單元格中輸入公式“=MIN(IF((成績單!$A$2:$A$950=按班級匯總!$A2)*(成績單!$B$2:$B$950=按班級匯總!$B2),成績單!$D$2:$D$950))”，按Ctrl+Shift+Enter組合鍵完成輸入。

提示：最小值函數(shù)MIN(number1,[number2],…)

主要功能：返回一組值或指定區(qū)域中的最小值

參數(shù)說明：參數(shù)至少有一個，且必須是數(shù)值，最多可以有255個

邏輯判斷函數(shù)IF(logical_test,[value_if_true],[value_if_false])

主要功能：如果指定條件的計算結(jié)果為TRUE，IF函數(shù)將返回某個值；如果該條件的計算結(jié)果為FALSE，則返回另一個值。

參數(shù)說明：

logical_test

必須的參數(shù)，作為判斷條件的任意值或表達式

value_if_true

可選的參數(shù)。Logical_test參數(shù)的計算結(jié)果為TRUE時所要返回的值

value_if_false

可選的參數(shù)。Logical_test參數(shù)的計算結(jié)果為FALSE時所要返回的值

本題中：成績單!$A$2:$A$950=按班級匯總!$A2

返回值為邏輯值(True或False)，

成績單!$B$2:$B$950=按班級匯總!$B2

返回值也為邏輯值(True或False)，

將兩個表達式進行乘運算時，邏輯值True轉(zhuǎn)換為1，邏輯值False轉(zhuǎn)換為0參加運算。

也即，同時滿足兩個條件時，IF函數(shù)中的條件表達式為TRUE，才能返回表達式“成績單!$D$2:$D$950”，此時使用MIN函數(shù)統(tǒng)計指定區(qū)域中的最小值

步驟4：選擇F2單元格，在該單元格中輸入公式“=AVERAGEIFS(成績單!$D$2:$D$950,成績單!$A$2:$A$950,按班級匯總!$A2,成績單!$B$2:$B$950,按班級匯總!$B2)”，按Enter鍵完成輸入。

提示：多條件平均值函數(shù)AVERAGEIFS(average_range,criteria_range1,criteria1,[

criteria_range2,

criteria2],…)

主要功能：對指定區(qū)域中滿足多個條件的所有單元格中的數(shù)值求算數(shù)平均值。

參數(shù)說明：

average_range

必需的參數(shù)。要計算平均值的實際單元格區(qū)域

criteria_range1，criteria_range2…

在其中計算關(guān)聯(lián)條件的區(qū)域

criteria1，criteria2…求平均值的條件

其中每個criteria_range的大小和形狀必須與average_range相同

本題中公式表示對成績單D2:D950區(qū)域中符合以下條件的單元格的數(shù)值求平均值：成績單工作表A2:A950區(qū)域中等于按班級匯總工作表A2單元格內(nèi)容（即濱海市第一中學(xué)），并且成績單工作表B2:B950區(qū)域中等于按班級匯總工作表B2單元格內(nèi)容（即1班）。

步驟5：選擇G2單元格，在該單元格中輸入公式“=SUM(小分統(tǒng)計!$C2:$AP2)”，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

步驟6：選擇H2單元格，在該單元格中輸入公式“=SUM(小分統(tǒng)計!$AQ2:$AZ2)”，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

步驟7：選擇C、D、E列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】選項，在彈出的對話框中選擇【分類】列表中選擇【數(shù)值】選項，將【小數(shù)位數(shù)】設(shè)置為0，設(shè)置完成后，單擊“確定”按鈕即可。按同樣的方式，將F、G、H列【小數(shù)位數(shù)】設(shè)置為2。

步驟8：切換至“按學(xué)校匯總”工作表中，選擇B2單元格，在該單元格中輸入公式“=COUNTIF(成績單!$A$2:$A$950,按學(xué)校匯總!$A2)”，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

提示：條件計數(shù)函數(shù)COUNTIF(range,criteria)

主要功能：統(tǒng)計指定區(qū)域中滿足單個指定條件的單元格的個數(shù)

參數(shù)說明：

range

必需的參數(shù)。計數(shù)的單元格區(qū)域

criteria

必需的參數(shù)。計數(shù)的條件，條件的形式可以為數(shù)字、表達式、單元格地址或文本

本題中公式表示統(tǒng)計成績單工作表A2:A950區(qū)域中，等于A2單元格內(nèi)容（即濱海市第一中學(xué)）的單元格的個數(shù)。

步驟9：選擇C2單元格，在該單元格中輸入“=MAX((成績單!$A$2:$A$950=按學(xué)校匯總!$A2)*成績單!$D$2:$D$950)”，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

在D2單元格中輸入“=MIN(IF(成績單!$A$2:$A$950=按學(xué)校匯總!$A2,成績單!$D$2:$D$950))”

，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

在E2單元格中輸入“=AVERAGEIFS(成績單!$D$2:$D$950,成績單!$A$2:$A$950,按學(xué)校匯總!$A2)”

，按Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

步驟10：在F2單元格中輸入“=SUM((按班級匯總!$A$2:$A$33=按學(xué)校匯總!$A2)*(按班級匯總!$C$2:$C$33)*(按班級匯總!$G$2:$G$33))/$B2”

，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

在G2單元格中輸入“=SUM((按班級匯總!$A$2:$A$33=按學(xué)校匯總!$A2)*(按班級匯總!$C$2:$C$33)*(按班級匯總!$H$2:$H$33))/$B2”

，按Ctrl+Shift+Enter鍵完成輸入，雙擊右下角的填充柄填充數(shù)據(jù)。

提示：本題中按班級匯總!$A$2:$A$33=按學(xué)校匯總!$A2

返回的值為邏輯值(TRUE和FALSE)，參加運算時，TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0。

即統(tǒng)計指定學(xué)校的學(xué)生，使用SUM函數(shù)將數(shù)組按班級匯總!$C$2:$C$33與數(shù)組按班級匯總!$G$2:$G$33對應(yīng)位置的數(shù)據(jù)進行乘運算，然后求出乘積的和，最后將得到的總成績與學(xué)生人數(shù)相除，求得平均分。

步驟11：在H2單元格中輸入“=SUM((小分統(tǒng)計!$A$2:$A$33=$A2)*小分統(tǒng)計!C$2:C$33*按班級匯總!$C$2:$C$33)/$B2/分值表!B$3”，按Ctrl+Shift+Enter鍵完成輸入，拖動填充柄，橫向填充到BE2單元格，緊接著從BE2單元格，拖動填充句柄，縱向填充到BE5單元格。

步驟12：選擇B、C、D列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】選項，在彈出的對話框中選擇【分類】列表中選擇【數(shù)值】選項，將【小數(shù)位數(shù)】設(shè)置為0，設(shè)置完成后，單擊“確定”按鈕。按同樣的方式，將E、F、G列【小數(shù)位數(shù)】設(shè)置為2。

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

說明：DATEDIF(start_date,end_date,unit)，返回兩個日期之間的年\月\日間隔數(shù)。Start_date

為一個日期，它代表時間段內(nèi)的第一個日期或起始日期。

參數(shù)End_date

為一個日期，它代表時間段內(nèi)的最后一個日期或結(jié)束日期。

參數(shù)Unit

為所需信息的返回類型。當(dāng)單位代碼為”YD”時,計算結(jié)果是兩個日期間隔的天數(shù).忽略年數(shù)差。

注：結(jié)束日期必須大于起始日期。3.依據(jù)停放時間和收費標(biāo)準(zhǔn)，計算當(dāng)前收費金額并填入“收費金額”列；計算擬采用的收費政策的預(yù)計收費金額并填入“擬收費金額”列；計算擬調(diào)整后的收費與當(dāng)前收費之間的差值并填入“差值”列。(3)【解題步驟】

考點提示：本題主要考核公式和函數(shù)的使用。

步驟1：計算收費金額，在K2單元格中輸入公式“=ROUNDUP((HOUR(J2)*60+MINUTE(J2))/15,0)*E2”，并向下自動填充單元格。

步驟2：計算擬收費金額，在L2單元格中輸入公式“=INT((HOUR(J2)*60+MINUTE(J2))/15)*E2”

，并向下自動填充單元格。

步驟3：計算差值，在M2單元格中輸入公式“=K2-L2”，，并向下自動填充單元格。

說明：1.Roundup函數(shù)表示：向上舍入數(shù)字，跟四舍五入不一樣，不管舍去的首位數(shù)字是否大于4，都向前進1。

2.Roundup函數(shù)的語法格式

=Roundup(number,num_digits)

Number

表示：用來向上舍入的數(shù)字。

Num_digits

舍入后的數(shù)字的小數(shù)位數(shù)（即保留幾位小數(shù)）。

3.當(dāng)Roundup函數(shù)中參數(shù)

num_digits

大于

0時，則表示向上舍入到指定的小數(shù)位（這里保留一位小數(shù)）。第17套

3."課時費統(tǒng)計表"中的F至I列中的空白內(nèi)容必須采用公式的方式計算結(jié)果。根據(jù)"教師基本信息"工作表和"課時費標(biāo)準(zhǔn)"工作表計算"職稱"和"課時標(biāo)準(zhǔn)"列內(nèi)容，根據(jù)"授課信息表"和"課程基本信息"工作表計算"學(xué)時數(shù)"列內(nèi)容，最后完成"課時費"列的計算?！咎崾荆航ㄗh對"授課信息表"中的數(shù)據(jù)按姓名排序后增加"學(xué)時數(shù)"列，并通過VLOOKUP查詢"課程基本信息"表獲得相應(yīng)的值?！?3)【解題步驟】

步驟1：選擇F3單元格，單擊【公式】|【函數(shù)庫】組中的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”下拉列表中找到VLOOKUP函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框。

步驟2：在第1個參數(shù)框中用鼠標(biāo)選擇“E3”；第2個參數(shù)框中選擇“教師基本信息”工作表中的D3：E22區(qū)域；第3個參數(shù)框中輸入“2”；第4個參數(shù)框中輸入“FALSE”或者“0”；單擊“確定”按鈕即可。

步驟3：本題也可直接在“課時費統(tǒng)計表”工作表的F3單元格中輸入公式“=VLOOKUP(E3,教師基本信息!$D$3:$E$22,2,

FALSE)”，按Enter鍵，雙擊F3單元格右下角的填充柄完成職稱的自動填充。

步驟4：同理，使用VLOOKUP函數(shù)計算“課時標(biāo)準(zhǔn)”。第1個參數(shù)框中用鼠標(biāo)選擇“G3”；第2個參數(shù)框中選擇“課時費標(biāo)準(zhǔn)”工作表中的A3：B6區(qū)域；第3個參數(shù)框中輸入“2”；第4個參數(shù)框中輸入“FALSE”或者“0”；單擊“確定”按鈕即可。

步驟5：本題也可直接在“課時費統(tǒng)計表”工作表的G3單元格中輸入公式“=VLOOKUP(F3,課時費標(biāo)準(zhǔn)!$A$3:$B$6,2,FALSE)”，按Enter鍵，雙擊G3單元格右下角的填充柄完成課時標(biāo)準(zhǔn)的自動填充。

步驟6：根據(jù)題目提示，先在“授課信息表”中增加“學(xué)時數(shù)”列。在“授課信息表”的F2單元格輸入“學(xué)時數(shù)”，回車選擇“F3”，同理使用VLOOKUP函數(shù)計算“學(xué)時數(shù)”。第1個參數(shù)框中用鼠標(biāo)選擇“E3”；第2個參數(shù)框中選擇“課程基本信息”工作表中的B3：C16區(qū)域；第3個參數(shù)框中輸入“2”；第4個參數(shù)框中輸入“FALSE”或者“0”；單擊“確定”按鈕，雙擊F3單元格右下角的填充柄完成學(xué)時數(shù)的自動填充。

步驟7：根據(jù)題目提示，再計算“課時費統(tǒng)計表”中的“學(xué)時數(shù)”列。切換到“課時費統(tǒng)計表”工作表，選擇H3單元格，單擊【公式】|【函數(shù)庫】組中的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”下拉列表中找到SUMIF函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框。

步驟8：在第1個參數(shù)框中用鼠標(biāo)選擇“授課信息表”的“D3：D72”數(shù)據(jù)區(qū)域；第2個參數(shù)框中輸入“E3”；第3個參數(shù)框中輸入選擇“授課信息表”的“F3：F72”數(shù)據(jù)區(qū)域；單擊“確定”按鈕，雙擊H3單元格右下角的填充柄自動填充到最后一行。

步驟9：在I3單元格中輸入公式“=G3*H3”完成課時費的計算。第18套2

.輸入并填充公式：在"余額"列輸入計算公式，余額=上期余額+本期借方-本期貸方，以自動填充方式生成其他公式。

3."方向列中"只能有借、貸、平三種選擇，首先用數(shù)據(jù)有效性控制該列的輸入范圍為借、貸、平三種中的一種，然后通過IF函數(shù)輸入"方向"列內(nèi)容，判斷條件如下所列：

余額

大于0

等于0

小于0

方向

借

平

貸第20套4.

學(xué)號第

4、5

位代表學(xué)生所在的班級，例如："C120101"代表12級1班。請通過函數(shù)提取每個學(xué)生所在的專業(yè)并按下列對應(yīng)關(guān)系填寫在"班級"列中：

"學(xué)號"的4、5位

對應(yīng)班級

01

1班

02

2班

03

3班

第22套2.

根據(jù)身份證號，請在"員工檔案表"工作表的"出生日期"列中，使用MID函數(shù)提取員工生日，單元格式類型為"yyyy