《Word 2003、Excel 2003案例教程》第14章 EXCEL 2003公式和函數(shù)的使用

第14章EXCEL2003公式和函數(shù)的使用

——制作“期末考試成績(jī)分析表”

建立公式常用函數(shù)單元格引用

RANK函數(shù)

COUNT函數(shù)

COUNTIF函數(shù)1.公式和函數(shù)

用Excel制作的表格往往需要有數(shù)據(jù)的計(jì)算和統(tǒng)計(jì)，如求學(xué)生考試總成績(jī)、求平均成績(jī)、求學(xué)生考試成績(jī)的最高分、最低分等。Excel2003具有非常強(qiáng)的計(jì)算和統(tǒng)計(jì)功能，從簡(jiǎn)單的四則運(yùn)算，到復(fù)雜的財(cái)務(wù)計(jì)算、統(tǒng)計(jì)分析，都能輕松解決。（1）公式

在Excel2003中，所有的計(jì)算都可以利用“公式”來完成，Excel2003的公式就是對(duì)工作表中的數(shù)值進(jìn)行計(jì)算的表達(dá)式，它以“=”開頭，并由一些數(shù)值和運(yùn)算符號(hào)組成，這里所說的數(shù)值，不僅包括普通的常數(shù)，還包括單元格名稱及Excel函數(shù)，而運(yùn)算符號(hào)不僅包括算術(shù)運(yùn)算符（加減乘除），還包括比較運(yùn)算符和文本運(yùn)算符。每個(gè)公式都能根據(jù)參加運(yùn)算的數(shù)據(jù)計(jì)算出一個(gè)結(jié)果。Excel2003中公式的概念與數(shù)學(xué)公式的概念基本上是一致的。通常情況下，一個(gè)公式是由各種運(yùn)算符、常量、函數(shù)以及單元格引用組成的合法運(yùn)算式，而運(yùn)算符則指定了對(duì)數(shù)據(jù)進(jìn)行的某種運(yùn)算處理。

運(yùn)算符根據(jù)參與運(yùn)算數(shù)據(jù)的個(gè)數(shù)分為單目運(yùn)算符和雙目運(yùn)算符。單目運(yùn)算符只有一個(gè)數(shù)據(jù)參與運(yùn)算，雙目運(yùn)算符有兩個(gè)數(shù)據(jù)參與運(yùn)算。運(yùn)算符根據(jù)參與運(yùn)算的性質(zhì)分為算術(shù)運(yùn)算符、比較運(yùn)算符和文字連接符三類。

①算術(shù)運(yùn)算符：算術(shù)運(yùn)算符用來對(duì)數(shù)值進(jìn)行算術(shù)運(yùn)算，結(jié)果還是數(shù)值。算術(shù)運(yùn)算符的優(yōu)先級(jí)由高到低為：-（求負(fù)）、%、^、*和/、+和-。如果優(yōu)先級(jí)相同（如*和/），則按從左到右的順序計(jì)算。

②比較運(yùn)算符：比較運(yùn)算符用來比較兩個(gè)文本、數(shù)值、日期、時(shí)間的大小，結(jié)果是一個(gè)邏輯值（TRUE或FALSE）。比較運(yùn)算的優(yōu)先級(jí)必算術(shù)運(yùn)算的低。各種類型數(shù)據(jù)的比較規(guī)則如下。數(shù)值型數(shù)據(jù)的比較規(guī)則是：按照數(shù)值的大小進(jìn)行比較。日期型數(shù)據(jù)的比較規(guī)則是：昨天<今天<明天。時(shí)間型數(shù)據(jù)的比較規(guī)則是：過去<現(xiàn)在<將來。文本型數(shù)據(jù)的比較規(guī)則是：按照字典順序比較。

③文字連接符：文字連接符只有一個(gè)“&”，是雙目運(yùn)算符，用來連接文本或數(shù)值，結(jié)果是文本類型。文字連接的優(yōu)先級(jí)比算術(shù)運(yùn)算符的低，但是比比較運(yùn)算符的高。如：“計(jì)算機(jī)”&“應(yīng)用”，其結(jié)果是“計(jì)算機(jī)應(yīng)用”；“總分是”&80+70+60，其結(jié)果是“總分是210”。

要判斷一個(gè)單元格的內(nèi)容是不是公式很簡(jiǎn)單，只要看到最前面有一個(gè)“=”號(hào)，那肯定就是公式，同樣，如果要在一個(gè)單元格中創(chuàng)建公式，一定不要忘了應(yīng)以“=”號(hào)開始。輸入公式的方法是：

①單擊將要在其輸入公式的單元格，該單元格將存放公式的計(jì)算結(jié)果。

②輸入一個(gè)“=”號(hào)，然后在其后輸入公式。

③公式輸入完后，單擊回車鍵，該公式的計(jì)算結(jié)果會(huì)出現(xiàn)在選定的單元格中。

如果要編輯和修改公式，只需在編輯欄中修改即可，要?jiǎng)h除公式，也可在編輯欄中進(jìn)行。復(fù)制公式的方法：

與數(shù)據(jù)的復(fù)制一樣，單元格中的公式也是可以復(fù)制的，單擊輸入有公式的單元格，鼠標(biāo)指向其右下角的填充柄

，按住左鍵拖動(dòng)，可把該公式的格式復(fù)制到所選擇的單元格里。修改公式的方法：雙擊存放公式的單元格，插入點(diǎn)位于該單元格內(nèi)，單元格和編輯框內(nèi)都可以看到相應(yīng)的公式，可直接在單元格內(nèi)修改其中的公式。（2）函數(shù)

函數(shù)是Excel2003中已經(jīng)定義好的計(jì)算公式，Excel2003提供了大量的、實(shí)用的函數(shù)，基本滿足了財(cái)務(wù)、統(tǒng)計(jì)及各管理部門日常統(tǒng)計(jì)和計(jì)算工作的需要。

一個(gè)函數(shù)由兩部分組成，函數(shù)名和函數(shù)參數(shù)。函數(shù)的格式是：函數(shù)名（參數(shù)1，參數(shù)2，。。。）函數(shù)名表明函數(shù)的功能，函數(shù)的參數(shù)是參與運(yùn)算的數(shù)值及范圍和條件。如SUM是求和的函數(shù)，SUM（10，20，30）的意思是將括號(hào)中的三個(gè)數(shù)求和，SUM（A3:D3）是將A3:D3單元格區(qū)域中的數(shù)值求和。2.常用函數(shù)的使用

在日常的統(tǒng)計(jì)和計(jì)算工作中，用的最多的還是如求和、求平均、求最大最小等計(jì)算，Excel2003為這些常用的計(jì)算設(shè)置了最方便的操作方法。（1）求和（SUM）對(duì)工作表某些單元格中數(shù)據(jù)求和的步驟如下：

①選擇準(zhǔn)備放求和結(jié)果的F3單元格。

②單擊常用工具欄下的“自動(dòng)求和”按鈕

→“求和”命令

。

③用鼠標(biāo)在欲進(jìn)行求和計(jì)算的單元格上拖動(dòng).

④按回車鍵，求和結(jié)果出現(xiàn)在單元格中.

⑤鼠標(biāo)指向F3單元格右下角的填充柄

，向下拖動(dòng)，可將公式復(fù)制到F列的所有單元格中.

（2）求平均值（AVERAGE）求平均值與求和的操作方法類似：

①選擇準(zhǔn)備放平均值結(jié)果的單元格。

②單擊常用工具欄下的“自動(dòng)求和”按鈕

，在彈出的下拉菜單中選擇“平均值”命令。

③用鼠標(biāo)在欲進(jìn)行求平均值的單元格上拖動(dòng)。

④按回車鍵，所求平均結(jié)果出現(xiàn)在單元格中。

（3）計(jì)數(shù)（COUNT）所謂計(jì)數(shù)，是統(tǒng)計(jì)所選單元格的個(gè)數(shù)。在日常的工作中，計(jì)數(shù)也是經(jīng)常要做的操作之一，如統(tǒng)計(jì)學(xué)生人數(shù)，統(tǒng)計(jì)課程門數(shù)等。計(jì)數(shù)的操作步驟如下：選擇準(zhǔn)備放計(jì)數(shù)結(jié)果的單元格。

②單擊常用工具欄下的“自動(dòng)求和”按鈕

，在彈出的下拉菜單中選擇“其他函數(shù)”命令，在“插入函數(shù)”對(duì)話框中選擇“COUNT”函數(shù)，單擊確定按鈕。

③用鼠標(biāo)在欲進(jìn)行計(jì)數(shù)統(tǒng)計(jì)的單元格上拖動(dòng)。

④按回車鍵，所求的計(jì)數(shù)結(jié)果出現(xiàn)在單元格中。

（4）求最大(MAX)或最小值(MIN))求最大(MAX)或最小值(MIN))也是常用的操作，如求學(xué)生考試的最高分、最低分等。求最大（或最?。┲档牟僮鞑襟E如下：選擇準(zhǔn)備放最大（或最?。┲到Y(jié)果的單元格。

②單擊常用工具欄下的“自動(dòng)求和”按鈕

，在彈出的下拉菜單中選擇“最大值（或最小值）”命令。

③用鼠標(biāo)在欲進(jìn)行求最大（或最?。┲档膯卧裆贤蟿?dòng)。

④按回車鍵，所求最大（或最?。┲到Y(jié)果出現(xiàn)在單元格中。

（5）根據(jù)條件確定單元格的取值（IF函數(shù)）

有時(shí)我們需要根據(jù)條件來確定單元格的取值，如根據(jù)學(xué)生的成績(jī)確定是否錄取，這是需要用到條件函數(shù)（IF）。操作步驟如下：

①選擇準(zhǔn)備存放結(jié)果的單元格

②單擊常用工具欄下的“自動(dòng)求和”按鈕

，在彈出的下拉菜單中選擇“其他函數(shù)”命令，在“插入函數(shù)”對(duì)話框中選擇“IF”函數(shù)③在“選擇類別”中選擇“常用函數(shù)”，在“選擇函數(shù)”中選擇“IF”，單擊“確定”后，打開IF函數(shù)的“函數(shù)參數(shù)”對(duì)話框④在Logical_test中輸入判定條件“F3>=280”，意為總分大于或等于280分，在Value_IF_true中輸入條件為真時(shí)的值“錄取”；在Value_IF_False中輸入條件為假時(shí)的值“不錄取”，如圖所示。

⑤單擊“確定”按鈕，此時(shí)根據(jù)判斷在F3單元格中填為“錄取”，⑥鼠標(biāo)指向G3單元格右下角的填充柄

，向下拖動(dòng)，可將公式復(fù)制到G列的所記錄的“是否錄取”一列中填入錄取或不錄取，如圖所示。（6）統(tǒng)計(jì)滿足條件的單元格數(shù)目（COUNTIF函數(shù)）

在工作中我們經(jīng)常需要統(tǒng)計(jì)出滿足條件的單元格的數(shù)量，如統(tǒng)計(jì)出錄取的人數(shù)是多少，這就需要用到條件統(tǒng)計(jì)函數(shù)。操作步驟如下：選擇準(zhǔn)備存放結(jié)果的單元格。

②單擊常用工具欄下的“自動(dòng)求和”按鈕

，在彈出的下拉菜單中選擇“其他函數(shù)”命令，彈出“插入函數(shù)”對(duì)話框。

③在“選擇類別”中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”中選擇“COUNTIF”，單擊“確定”后，打開“COUNTIF”函數(shù)的“函數(shù)參數(shù)”對(duì)話框，在Range中輸入統(tǒng)計(jì)范圍“G3:G7”，在Criteria中輸入條件“錄取”，如圖所示。④單擊“確定”按鈕后，即可統(tǒng)計(jì)出在G3:G7單元格區(qū)域內(nèi)值為“錄取”的單元格是多少，如圖中G9單元格中的數(shù)字2。3．單元格地址的引用

（1）A1引用類型Excel2003中默認(rèn)的狀態(tài)是A1引用類型，它使用行標(biāo)和列標(biāo)的組合方式表示單元格的引用名稱。如“B5”表示引用了B列5行交叉處的單元格，“A1:C7”表示引用了A1單元格至C7單元格所構(gòu)成的矩形區(qū)域。單元格區(qū)域引用可用公式表示為“區(qū)域左上角的單元格名稱+英文半角冒號(hào)（：）+區(qū)域右下角單元格名稱”（其中“+”號(hào)不輸入）。（2）相對(duì)引用相對(duì)引用也稱為相對(duì)地址，它用列標(biāo)與行標(biāo)直接表示單元格，如果某個(gè)單元格內(nèi)的公式被復(fù)制到另一個(gè)單元格，原來單元格內(nèi)的地址在新單元格中發(fā)生相應(yīng)的變化，就需要用相對(duì)引用來實(shí)現(xiàn)。如果在D4單元格中輸入“=C4”，那么將D4向下拖動(dòng)到D5時(shí)，D5中的公式是“=C5”，將D4向右拖動(dòng)到E4，E4中的公式變成了“=D4”

（3）絕對(duì)引用如果希望在移動(dòng)或復(fù)制公式后，仍然使用原來單元格或單元格區(qū)域中的數(shù)據(jù)（如給班級(jí)中每個(gè)同學(xué)排名次），就需要使用絕對(duì)引用。在使用單元格的絕對(duì)引用時(shí)須在單元格的列標(biāo)與行標(biāo)前加“$”符號(hào)。如在D4單元格中輸入“=$C4”，將D4向右拖動(dòng)到E4，E4中的公式還是“=$C4”，而向下拖動(dòng)到D5時(shí)，D5中的公式就成了“=$C5”。（4）混合引用單元格如果將相對(duì)引用與絕對(duì)引用混合使用，即為混合引用。在混合引用中絕對(duì)引用部分保持不變，而相對(duì)引用的部分將發(fā)生相應(yīng)的變化。如在D4單元格中輸入“=C$4”，那么將D4向右拖動(dòng)到E4時(shí)，E4中的公式變?yōu)椤?D$4”，而將D4向下拖動(dòng)到D5時(shí)，D5中的公式還是“=C$4”。4.使用工作表的引用進(jìn)行多張工作表計(jì)算

在實(shí)際工作中，有時(shí)需要引用多張工作表中的數(shù)據(jù)進(jìn)行計(jì)算。比如，在“匯金電腦科技有限公司員工工資表”中，要求計(jì)算1月至12月每月實(shí)發(fā)工資的總和，具體操作步驟如下：（1）打開“匯金電腦科技有限公司員工工資表”，如圖所示。（2）選擇存放12個(gè)月實(shí)發(fā)工資總和的單元格（“一月”工作表的L24單元格），單擊菜單“插入”→“函數(shù)”命令，在彈出的“