第一題 學(xué)生成績(jī)

【數(shù)組公式】3. 使用數(shù)組公式，根據(jù)Sheet1中的數(shù)據(jù)，計(jì)算總分和平均分，將其結(jié)果保存到表中的“總分”列和“平均分”列。

總分列選中整一列后，在公式編輯欄輸入=C2:C39+D2:D39+E2:E39，然后同時(shí)摁Ctrl+Shift+Enter。

平均分列同樣操作，選中整一列后在公式編輯欄輸入=(C2:C39+D2:D39+E2:E39)/3，然后同時(shí)摁Ctrl+Shift+Enter。

【排序函數(shù)】4. 使用RANK函數(shù)，根據(jù)Sheet1中的“總分”列對(duì)每個(gè)同學(xué)排名情況進(jìn)行統(tǒng)計(jì)，并將排名結(jié)果保存到表中的“排名”列。（如果多個(gè)數(shù)值排名相同，則返回該組數(shù)值的最佳排名）

F2單元格中輸入=RANK(F2,$F$2:$F$39)，然后進(jìn)行自動(dòng)填充，注意排序區(qū)域的絕對(duì)引用。

最佳排名使用的函數(shù)本應(yīng)是RANK.EQ，但使用RANK函數(shù)也可得到相應(yīng)分?jǐn)?shù)。

RANK(number,ref,[order])

RANK.EQ(number,ref,[order])

【邏輯函數(shù)】5. 使用邏輯函數(shù)，判斷Sheet1中每個(gè)同學(xué)的每門功課是否均高于全班單科平均分。

I2單元格中輸入=IF(AND(C2>=AVERAGE($C$2:$C$39),D2>AVERAGE($D$2:$D$39),E2>=AVERAGE($E$2:$E$39)),TRUE,FALSE)，然后進(jìn)行自動(dòng)填充，注意排序區(qū)域的絕對(duì)引用。

IF(Logical_test,Value_if_true,Value_if_false)

【統(tǒng)計(jì)函數(shù)】6. 根據(jù)Sheet1中的結(jié)果，使用統(tǒng)計(jì)函數(shù)，統(tǒng)計(jì)“數(shù)學(xué)”考試成績(jī)各個(gè)分?jǐn)?shù)段的同學(xué)人數(shù)，將統(tǒng)計(jì)結(jié)果保存到Sheet2中的相應(yīng)位置。

在Sheet2中選中B2單元格，輸入=COUNTIF(Sheet1!D2:D39,"<20")，B3中輸入=COUNTIF(Sheet1!D2:D39,"<40")-COUNTIF(Sheet1!D2:D39,"<20")，B4中輸入=COUNTIF(Sheet1!D2:D39,"<60")-COUNTIF(Sheet1!D2:D39,"<40")，B5中輸入=COUNTIF(Sheet1!D2:D39,"<80")-COUNTIF(Sheet1!D2:D39,"<60")，B6中輸入=COUNTIF(Sheet1!D2:D39,"<100")-COUNTIF(Sheet1!D2:D39,"<80")。

COUNTIF(Range,Criteria)

第二題 采購(gòu)表

【四舍五入】1. 在Sheet5中，使用函數(shù)，將A1單元格中的數(shù)四舍五入至整百，存放在B1單元格中。

在B1單元格輸入=ROUND(A1,-2)，摁回車。

ROUND(number, num_digits)

【查詢函數(shù)】3. 使用VLOOKUP函數(shù)，對(duì)Sheet1中“采購(gòu)表”的“單價(jià)”列進(jìn)行填充。

在D11單元格中輸入=VLOOKUP(A11,$F$2:$G$5,2,0)，然后進(jìn)行自動(dòng)填充，注意排序區(qū)域的絕對(duì)引用。

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

【邏輯函數(shù)】4. 使用IF函數(shù)，對(duì)Sheet1“采購(gòu)表”中的“折扣”列進(jìn)行填充。

在E11單元格中輸入=IF(B11>$A$6,$B$6,IF(B11>$A$5,$B$5,IF(B11>$A$4,$B$4,$B$3)))，然后進(jìn)行自動(dòng)填充，注意排序區(qū)域的絕對(duì)引用。

【數(shù)組公式】5. 使用數(shù)組公式，對(duì)Sheet1中“采購(gòu)表”的“合計(jì)”列進(jìn)行計(jì)算。

選中合計(jì)列，在公式編輯欄中輸入=B11:B43*D11:D43*(1-E11:E43)，然后同時(shí)摁Ctrl+Shift+Enter。

【求和函數(shù)】6. 使用SUMIF函數(shù)，計(jì)算各種商品的采購(gòu)總量和采購(gòu)總金額，將結(jié)果保存在Sheet1中的“統(tǒng)計(jì)表”當(dāng)中相應(yīng)位置。

在J12單元格中輸入=SUMIF($A$11:$A$43,I12,$B$11:$B$43)，然后向下進(jìn)行自動(dòng)填充，注意排序區(qū)域的絕對(duì)引用。

在K12單元格中輸入=SUMIF($A$11:$A$43,I12,$F$11:$F$43)，然后向下進(jìn)行自動(dòng)填充，注意排序區(qū)域的絕對(duì)引用。

SUMIF(range, criteria, [sum_range])

第三題 教材訂購(gòu)情況表

【數(shù)組公式】3. 使用數(shù)組公式，對(duì)Sheet1中“教材訂購(gòu)情況表”的訂購(gòu)金額進(jìn)行計(jì)算。

選中訂購(gòu)金額列，在公式編輯欄輸入=G3:G52*H3:H52，然后同時(shí)摁Ctrl+Shift+Enter。

【統(tǒng)計(jì)函數(shù)】4. 使用統(tǒng)計(jì)函數(shù)，對(duì)Sheet1中“教材訂購(gòu)情況表”的結(jié)果按以下條件進(jìn)行統(tǒng)計(jì)，并將結(jié)果保存在Sheet1中的相應(yīng)位置。

在L2單元格輸入=COUNTIF($A$3:$I$52,"高等教育出版社")，注意絕對(duì)引用。

在L3單元格輸入=COUNTIF($G$3:$G$52,">110")-COUNTIF($G$3:$G$52,">850")，注意絕對(duì)引用。

【求和函數(shù)】5. 使用函數(shù)，計(jì)算每個(gè)用戶所訂購(gòu)圖書所需支付的金額，并將結(jié)果保存在Sheet1中“用戶支付情況表”的“支付總額”列中。

在L8單元格輸入=SUMIF($A$3:$A$52,K8,$I$3:$I$52)，然后向下進(jìn)行自動(dòng)填充，注意求和區(qū)域的絕對(duì)引用。

【閏年公式】6. 使用函數(shù)，判斷Sheet2中的年份是否為閏年，如果是，結(jié)果保存“閏年”；如果不是，則結(jié)果保存“平年”，并將結(jié)果保存在“是否為閏年”列中。

閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet2表B2單元格中輸入=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"閏年","平年")，然后向下進(jìn)行自動(dòng)填充。

第四題 號(hào)碼升級(jí)

【閏年公式】2. 在Sheet5的B1單元格中輸入公式，判斷當(dāng)前年份是否為閏年，結(jié)果為TRUE或FALSE。

閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet5的B1單元格中輸入=IF(OR(MOD(YEAR(NOW()),400)=0,AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)<>0)),TRUE,FALSE)。

MOD(Number,Divisor)

YEAR(Serial_Number)

【時(shí)間函數(shù)】3. 使用時(shí)間函數(shù)，對(duì)Sheet1中用戶的年齡進(jìn)行計(jì)算。

在D2單元格中輸入=YEAR("2013-5-1")-YEAR(C2)，然后向下進(jìn)行自動(dòng)填充。

【號(hào)碼升級(jí)】4. 使用REPLACE函數(shù)，對(duì)Sheet1中用戶的電話號(hào)碼進(jìn)行升級(jí)。

在G2單元格中輸入=REPLACE(F2,5,0,"8")，然后向下進(jìn)行自動(dòng)填充。

REPLACE(Old_text,Start_num,Num_chars,New_text)

【邏輯函數(shù)】5. 在Sheet1中，使用AND函數(shù)，根據(jù)“性別”及“年齡”列中的數(shù)據(jù)，判斷所有用戶是否為大于等于40歲的男性，并將結(jié)果保存在“是否>=40男性”列中。

在H2單元格中輸入=IF(AND(D2>=40,B2="男"),TRUE,FALSE)。

【統(tǒng)計(jì)函數(shù)】6. 根據(jù)Sheet1中的數(shù)據(jù)，對(duì)以下條件，使用統(tǒng)計(jì)函數(shù)進(jìn)行統(tǒng)計(jì)。

在Sheet2表B2單元格中輸入=COUNTIF(Sheet1!B2:B37,"男")。

在Sheet2表B3單元格中輸入=COUNTIF(Sheet1!D2:D37,">40")。

第五題 采購(gòu)情況表

【數(shù)據(jù)查重】2. 在Sheet1中設(shè)定第31行不能輸入重復(fù)的數(shù)值。

“允許”選擇自定義，勾選忽略空值，公式輸入=COUNTIF(31:31,A31)=1。

【數(shù)組公式】3. 使用數(shù)組公式，計(jì)算Sheet1中的每種產(chǎn)品的價(jià)值，將結(jié)果保存到表中的“價(jià)值”列中。

選中“價(jià)值”列，在公式編輯欄中輸入=E2:E17*F2:F17*G2:G17。

【數(shù)據(jù)庫(kù)函數(shù)】4. 在Sheet2中，利用數(shù)據(jù)庫(kù)函數(shù)及已設(shè)置的條件區(qū)域，計(jì)算以下情況的結(jié)果，并將結(jié)果保存在相應(yīng)的單元格中。

在Sheet2表G23單元格中輸入=DAVERAGE(A2:H18,E2,J3:L4)。

DAVERAGE(Database,Field,Critaria)

在Sheet2表G24單元格中輸入=DSUM(A2:H18,G2,J8:L9)。

DSUM(Database,Field,Critaria)

【計(jì)數(shù)函數(shù)】5. 某公司對(duì)各個(gè)部門員工吸煙情況進(jìn)行統(tǒng)計(jì)，作為人力資源搭配的一個(gè)數(shù)據(jù)依據(jù)。對(duì)于調(diào)查對(duì)象，只能回答Y（吸煙）或者N（不吸煙）。根據(jù)調(diào)查情況，制作出Sheet3。請(qǐng)使用函數(shù)，統(tǒng)計(jì)符合以下條件的數(shù)值。

在Sheet3表B14單元格中輸入=COUNTBLANK(B2:E11)。

COUNTBLANK(Range)。

在Sheet3表B15單元格中輸入=COUNTIF(B2:E11,"Y")。

【文本函數(shù)】6. 使用函數(shù)，對(duì)Sheet3中的B21單元格中的內(nèi)容進(jìn)行判斷，判斷其是否為文本，如果是，結(jié)果為“TRUE”；如果不是，結(jié)果為“FALSE”，并將結(jié)果保存在Sheet3中的B22單元格當(dāng)中。

在Sheet3表B22單元格中輸入=ISTEXT(B21)。

ISTEXT(Value)。