Excel財(cái)務(wù)與會(huì)計(jì)應(yīng)用精粹

1、第二章第二章Excel 的數(shù)據(jù)處理的數(shù)據(jù)處理第一節(jié)第一節(jié)Excel 運(yùn)算公式運(yùn)算公式第二節(jié)第二節(jié)Excel 函數(shù)函數(shù)第三節(jié)數(shù)據(jù)排序與分類匯總第三節(jié)數(shù)據(jù)排序與分類匯總第四節(jié)數(shù)據(jù)篩選第四節(jié)數(shù)據(jù)篩選第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式一、建立與編輯數(shù)據(jù)區(qū)域一、建立與編輯數(shù)據(jù)區(qū)域l1.數(shù)據(jù)區(qū)域的建立。數(shù)據(jù)區(qū)域是數(shù)據(jù)區(qū)域的建立。數(shù)據(jù)區(qū)域是 Excel 工作表中進(jìn)行數(shù)工作表中進(jìn)行數(shù)據(jù)處理的單元區(qū)域。據(jù)處理的單元區(qū)域。Excel 作為一作為一個(gè)電子表格軟件，本個(gè)電子表格軟件，本身具有一個(gè)完備的表結(jié)構(gòu)，通?？蓪?shù)據(jù)區(qū)域中的列稱為身具有一個(gè)完備的表結(jié)構(gòu)，通?？蓪?shù)據(jù)區(qū)域中的列稱為字段，將行稱字段，將行稱為

2、記錄。建立數(shù)據(jù)區(qū)域的要求是最上面一行為記錄。建立數(shù)據(jù)區(qū)域的要求是最上面一行或兩行必須包含字段名（數(shù)據(jù)區(qū)域的列名稱），數(shù)據(jù)區(qū)域或兩行必須包含字段名（數(shù)據(jù)區(qū)域的列名稱），數(shù)據(jù)區(qū)域中應(yīng)盡量不要出現(xiàn)空行或空列，并保證每列中包含的是同中應(yīng)盡量不要出現(xiàn)空行或空列，并保證每列中包含的是同類的信息。建立數(shù)據(jù)區(qū)域的方法是先建立一個(gè)表格，再一類的信息。建立數(shù)據(jù)區(qū)域的方法是先建立一個(gè)表格，再一條一條地錄入數(shù)據(jù)，既可在工作表中錄入，也可通過(guò)記錄條一條地錄入數(shù)據(jù)，既可在工作表中錄入，也可通過(guò)記錄單錄入。單錄入。返回下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l【案例案例 21】遠(yuǎn)華公司的人員、部門(mén)及職務(wù)工資等數(shù)據(jù)，

3、遠(yuǎn)華公司的人員、部門(mén)及職務(wù)工資等數(shù)據(jù)，如如表表 21 所示。除此之外，還有的工資表項(xiàng)目是：效益所示。除此之外，還有的工資表項(xiàng)目是：效益工資、交通補(bǔ)貼、住房補(bǔ)貼、應(yīng)發(fā)合計(jì)、養(yǎng)老保險(xiǎn)金、醫(yī)工資、交通補(bǔ)貼、住房補(bǔ)貼、應(yīng)發(fā)合計(jì)、養(yǎng)老保險(xiǎn)金、醫(yī)療保險(xiǎn)金、代扣稅、扣款合計(jì)、實(shí)發(fā)合計(jì)。要求建立數(shù)據(jù)療保險(xiǎn)金、代扣稅、扣款合計(jì)、實(shí)發(fā)合計(jì)。要求建立數(shù)據(jù)區(qū)域。區(qū)域。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l【操作提示操作提示】l（1）在）在 Excel 工作表的最上面一行或兩行輸入字段名工作表的最上面一行或兩行輸入字段名的內(nèi)容，如人員編號(hào)、姓名、部門(mén)名稱、養(yǎng)老保險(xiǎn)金等，的內(nèi)容，如人員編號(hào)、姓名、

4、部門(mén)名稱、養(yǎng)老保險(xiǎn)金等，如圖如圖 21 第第 2 行所示。行所示。l（2）移動(dòng)到數(shù)據(jù)區(qū)域下第一個(gè)空行（即）移動(dòng)到數(shù)據(jù)區(qū)域下第一個(gè)空行（即圖圖 21 第第 3 行），選取一個(gè)單元格。行），選取一個(gè)單元格。l（3）選擇）選擇“數(shù)據(jù)數(shù)據(jù)記錄單記錄單”菜單命令，打開(kāi)如菜單命令，打開(kāi)如圖圖 22 所所示的記錄單對(duì)話框，左邊是建立的數(shù)據(jù)區(qū)域的各字段，在示的記錄單對(duì)話框，左邊是建立的數(shù)據(jù)區(qū)域的各字段，在其編輯框中錄入相應(yīng)的數(shù)據(jù)。其編輯框中錄入相應(yīng)的數(shù)據(jù)。l（4）一條記錄錄入完畢后，單擊）一條記錄錄入完畢后，單擊“新建新建”按鈕后再錄入按鈕后再錄入第二條記錄。錄入完畢后單擊第二條記錄。錄入完畢后單擊“關(guān)閉關(guān)閉

5、”按鈕，即可完成數(shù)按鈕，即可完成數(shù)據(jù)區(qū)域的建立。據(jù)區(qū)域的建立。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l2.數(shù)據(jù)區(qū)域的編輯。錄入的數(shù)據(jù)有誤，可打開(kāi)數(shù)據(jù)區(qū)域數(shù)據(jù)區(qū)域的編輯。錄入的數(shù)據(jù)有誤，可打開(kāi)數(shù)據(jù)區(qū)域所在的工作表，選擇要修改的單元格，可以在編輯欄中直所在的工作表，選擇要修改的單元格，可以在編輯欄中直接修改，也可以在接修改，也可以在“記錄單記錄單”中對(duì)某條記錄進(jìn)行修改。要中對(duì)某條記錄進(jìn)行修改。要?jiǎng)h除數(shù)據(jù)區(qū)域中的行（記錄），可直接在工作表中選定要?jiǎng)h除數(shù)據(jù)區(qū)域中的行（記錄），可直接在工作表中選定要?jiǎng)h除的行，然后右擊選擇刪除的行，然后右擊選擇“刪除刪除”命令即可。也可在命令即可。也

6、可在“記記錄單錄單”中，通過(guò)中，通過(guò)“上一條上一條”、“下一條下一條”或或“條件條件”按鈕，按鈕，選出要?jiǎng)h除的行（即要?jiǎng)h除的一條記錄），單擊選出要?jiǎng)h除的行（即要?jiǎng)h除的一條記錄），單擊“刪除刪除”按鈕，即可刪除所選的行。必須注意，用記錄單上的按鈕，即可刪除所選的行。必須注意，用記錄單上的“刪刪除除”按鈕刪除的行是不能恢復(fù)的。要?jiǎng)h除數(shù)據(jù)區(qū)域中的列，按鈕刪除的行是不能恢復(fù)的。要?jiǎng)h除數(shù)據(jù)區(qū)域中的列，則應(yīng)在工作表中選擇所要?jiǎng)h除的列，用則應(yīng)在工作表中選擇所要?jiǎng)h除的列，用“編輯編輯”菜單或者菜單或者列的列的“快捷菜單快捷菜單”中的中的“刪除刪除”命令進(jìn)行。命令進(jìn)行。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel

7、 運(yùn)算公式運(yùn)算公式二、公式構(gòu)成與運(yùn)算類型二、公式構(gòu)成與運(yùn)算類型l工作表中的數(shù)據(jù)往往需要進(jìn)行大量的計(jì)算，可以根據(jù)單工作表中的數(shù)據(jù)往往需要進(jìn)行大量的計(jì)算，可以根據(jù)單元格之間的鉤稽關(guān)系，使用一些簡(jiǎn)單的運(yùn)算公式或較為元格之間的鉤稽關(guān)系，使用一些簡(jiǎn)單的運(yùn)算公式或較為復(fù)雜的函數(shù)，復(fù)雜的函數(shù)，Excel 將自動(dòng)完成這些運(yùn)算。將自動(dòng)完成這些運(yùn)算。1.公式的構(gòu)成。公式的構(gòu)成。Excel規(guī)定，公式必須以等號(hào)開(kāi)始，是由規(guī)定，公式必須以等號(hào)開(kāi)始，是由單元格引用、運(yùn)算符、數(shù)值、字符、函數(shù)等組成的能夠單元格引用、運(yùn)算符、數(shù)值、字符、函數(shù)等組成的能夠進(jìn)行計(jì)算的式子。進(jìn)行計(jì)算的式子。2.2. Excel 中運(yùn)算符有四類，即算

8、術(shù)運(yùn)算、字符運(yùn)算、中運(yùn)算符有四類，即算術(shù)運(yùn)算、字符運(yùn)算、比較運(yùn)算和引用運(yùn)算。比較運(yùn)算和引用運(yùn)算。l（1）算術(shù)運(yùn)算。算術(shù)運(yùn)算是指加、減、乘、除等運(yùn)算。）算術(shù)運(yùn)算。算術(shù)運(yùn)算是指加、減、乘、除等運(yùn)算。運(yùn)算的優(yōu)先次序?yàn)椋豪ㄌ?hào)運(yùn)算的優(yōu)先次序?yàn)椋豪ㄌ?hào)指數(shù)指數(shù)乘除乘除加減。同級(jí)運(yùn)加減。同級(jí)運(yùn)算符按從左到右的順序進(jìn)行。算術(shù)運(yùn)算符如算符按從左到右的順序進(jìn)行。算術(shù)運(yùn)算符如表表 22 所所示。示。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l（2）字符運(yùn)算。字符運(yùn)算的運(yùn)算符是）字符運(yùn)算。字符運(yùn)算的運(yùn)算符是“&”，它表示連，它表示連接、合并運(yùn)算兩邊的字符。加入的文本字符要使用雙引號(hào)，接、合并運(yùn)

9、算兩邊的字符。加入的文本字符要使用雙引號(hào)，且應(yīng)是英文狀態(tài)下的雙引號(hào)。且應(yīng)是英文狀態(tài)下的雙引號(hào)。l（3）比較運(yùn)算。比較運(yùn)算是用來(lái)比較兩個(gè)數(shù)值的關(guān)系，）比較運(yùn)算。比較運(yùn)算是用來(lái)比較兩個(gè)數(shù)值的關(guān)系，并產(chǎn)生邏輯值并產(chǎn)生邏輯值 TRUL（邏輯真值）和（邏輯真值）和 FALSE（邏輯假（邏輯假值）。比較運(yùn)算符如值）。比較運(yùn)算符如表表 23 所示。所示。l（4）引用運(yùn)算。引用運(yùn)算可以將單元格區(qū)域合并運(yùn)算）引用運(yùn)算。引用運(yùn)算可以將單元格區(qū)域合并運(yùn)算,引用運(yùn)算一般都要涉及到單元格引用。引用運(yùn)算一般都要涉及到單元格引用。“單元格引用單元格引用”是是指公式中輸入單元格地址時(shí)，表示該單元格中的內(nèi)容參加指公式中輸入單元

10、格地址時(shí)，表示該單元格中的內(nèi)容參加運(yùn)算。當(dāng)引用的單元格中的數(shù)據(jù)發(fā)生了變化時(shí)，公式則自運(yùn)算。當(dāng)引用的單元格中的數(shù)據(jù)發(fā)生了變化時(shí)，公式則自動(dòng)重新進(jìn)行計(jì)算并自動(dòng)更新計(jì)算結(jié)果，這種方式下，可以動(dòng)重新進(jìn)行計(jì)算并自動(dòng)更新計(jì)算結(jié)果，這種方式下，可以隨時(shí)觀察到數(shù)據(jù)之間的相互關(guān)系。隨時(shí)觀察到數(shù)據(jù)之間的相互關(guān)系。表表 24 所示。所示。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式三、公式中使用引用三、公式中使用引用l引用的作用在于標(biāo)識(shí)工作表上的單元格或單元格區(qū)域，引用的作用在于標(biāo)識(shí)工作表上的單元格或單元格區(qū)域，并告知并告知 Excel 在何處查找公式中所使用的數(shù)值或數(shù)據(jù)。在何處查找公式中所使用的數(shù)

11、值或數(shù)據(jù)。單元格地址是用列標(biāo)和行號(hào)來(lái)標(biāo)示某一單元格的位置，單元格地址是用列標(biāo)和行號(hào)來(lái)標(biāo)示某一單元格的位置，所以若要引用某個(gè)單元格，則在公式中輸入單元格的行所以若要引用某個(gè)單元格，則在公式中輸入單元格的行號(hào)、列標(biāo)，即單元格的地址即可。這樣在被引用單元格號(hào)、列標(biāo)，即單元格的地址即可。這樣在被引用單元格的數(shù)據(jù)變化時(shí)，公式所在單元格的數(shù)據(jù)也隨之變化。的數(shù)據(jù)變化時(shí)，公式所在單元格的數(shù)據(jù)也隨之變化。l1. 單元引用的類型。在單元引用的類型。在 Excel 中有以下三種基本的單中有以下三種基本的單元格引用類型。元格引用類型。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l（1）相對(duì)引用。是指在

12、公式中直接對(duì)單元格的行號(hào)、）相對(duì)引用。是指在公式中直接對(duì)單元格的行號(hào)、列標(biāo)這種相對(duì)位置進(jìn)行的引用。如果公式所有的單元格列標(biāo)這種相對(duì)位置進(jìn)行的引用。如果公式所有的單元格位置調(diào)整，被引用單元格的地址也相應(yīng)變化；如果多行位置調(diào)整，被引用單元格的地址也相應(yīng)變化；如果多行或多列復(fù)制或填充公式，引用的單元格地址也隨之改變?；蚨嗔袕?fù)制或填充公式，引用的單元格地址也隨之改變。例如：例如：E1 單元格中有公式單元格中有公式“=A1+B1C1”；向后拖；向后拖動(dòng)動(dòng) E1 單元格填充柄到單元格填充柄到 F1 時(shí)，則時(shí)，則 F1 單元格中的公式單元格中的公式為為“=B1+C1D1”；向下拖動(dòng)；向下拖動(dòng) E1 單元格填

13、充柄到單元格填充柄到 E2 時(shí)，則時(shí)，則 E2 單元格中的公式為單元格中的公式為“=A2+B2C2”。l（2）絕對(duì)引用。是指在公式中總是對(duì)特定位置的單元）絕對(duì)引用。是指在公式中總是對(duì)特定位置的單元格地址進(jìn)行引用。如果公式所在單元格的位置改變，被格地址進(jìn)行引用。如果公式所在單元格的位置改變，被引用單元格的地址不變；如果多行或多列復(fù)制或填充公引用單元格的地址不變；如果多行或多列復(fù)制或填充公式，引用的單元格地址不會(huì)改變。其方法是在單元格地式，引用的單元格地址不會(huì)改變。其方法是在單元格地址的行號(hào)、列標(biāo)前均加上符號(hào)址的行號(hào)、列標(biāo)前均加上符號(hào)“$”，如，如“$A$1”表示表示對(duì)對(duì) A1 單元格進(jìn)行絕對(duì)引用

14、。單元格進(jìn)行絕對(duì)引用。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l（3）混合引用。是指在公式中對(duì)單元格的行號(hào)進(jìn)行絕）混合引用。是指在公式中對(duì)單元格的行號(hào)進(jìn)行絕對(duì)引用而對(duì)列標(biāo)進(jìn)行相對(duì)引用，或?qū)π刑?hào)進(jìn)行相對(duì)引用對(duì)引用而對(duì)列標(biāo)進(jìn)行相對(duì)引用，或?qū)π刑?hào)進(jìn)行相對(duì)引用而對(duì)列標(biāo)進(jìn)行絕對(duì)引用。如果公式所在單元格的位置改而對(duì)列標(biāo)進(jìn)行絕對(duì)引用。如果公式所在單元格的位置改變，則相對(duì)引用的隨之改變，而絕對(duì)引用的不會(huì)改變。變，則相對(duì)引用的隨之改變，而絕對(duì)引用的不會(huì)改變。其方法是在絕對(duì)引用的行號(hào)或列標(biāo)前加其方法是在絕對(duì)引用的行號(hào)或列標(biāo)前加“$”符號(hào)。符號(hào)。2.引用類型的轉(zhuǎn)換。為將公式中用到的地址表示為絕對(duì)

15、引引用類型的轉(zhuǎn)換。為將公式中用到的地址表示為絕對(duì)引用，需加上用，需加上“$”符號(hào)。所以可以通過(guò)修改公式中符號(hào)。所以可以通過(guò)修改公式中“$”符號(hào)的位置、或刪除符號(hào)的位置、或刪除“$”符號(hào)，來(lái)改變單元格的引用符號(hào)，來(lái)改變單元格的引用類型；也可按類型；也可按 F4 功能鍵，改變公式中單元格的引用類功能鍵，改變公式中單元格的引用類型。型。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式2.【案例案例 22】遠(yuǎn)華公司的職務(wù)工資數(shù)據(jù)見(jiàn)案例遠(yuǎn)華公司的職務(wù)工資數(shù)據(jù)見(jiàn)案例 21。根據(jù)該公司的分配方案，效益工資為職務(wù)工資的根據(jù)該公司的分配方案，效益工資為職務(wù)工資的 10%；住房補(bǔ)貼為職務(wù)工資的住房補(bǔ)貼

16、為職務(wù)工資的 5%；應(yīng)發(fā)合計(jì)為職務(wù)工資、效；應(yīng)發(fā)合計(jì)為職務(wù)工資、效益工資、交通補(bǔ)貼、住房補(bǔ)貼之和；養(yǎng)老保險(xiǎn)金為應(yīng)發(fā)益工資、交通補(bǔ)貼、住房補(bǔ)貼之和；養(yǎng)老保險(xiǎn)金為應(yīng)發(fā)合計(jì)的合計(jì)的 5%；醫(yī)療保險(xiǎn)金為職務(wù)工資、效益工資之和的；醫(yī)療保險(xiǎn)金為職務(wù)工資、效益工資之和的 2%；扣款合計(jì)為養(yǎng)老保險(xiǎn)金、醫(yī)療保險(xiǎn)金、代扣稅之；扣款合計(jì)為養(yǎng)老保險(xiǎn)金、醫(yī)療保險(xiǎn)金、代扣稅之和；實(shí)發(fā)合計(jì)為應(yīng)發(fā)合計(jì)減扣款合計(jì)。要求和；實(shí)發(fā)合計(jì)為應(yīng)發(fā)合計(jì)減扣款合計(jì)。要求l完成以上相關(guān)項(xiàng)目的計(jì)算（交通補(bǔ)貼、代扣稅暫不計(jì)完成以上相關(guān)項(xiàng)目的計(jì)算（交通補(bǔ)貼、代扣稅暫不計(jì)算）。算）。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l【操作

17、提示操作提示】l（1）效益工資的計(jì)算：選中）效益工資的計(jì)算：選中 F3 單元格，然后輸入單元格，然后輸入“=”；單擊一次；單擊一次 E3 單元格，則該單元格被相對(duì)引用到單元格，則該單元格被相對(duì)引用到公式中；再輸入公式中；再輸入“*10%”；公式輸入完畢后按回車(chē)鍵，；公式輸入完畢后按回車(chē)鍵，則則 F3 單元格自動(dòng)顯示計(jì)算結(jié)果，上部編輯框中顯示其單元格自動(dòng)顯示計(jì)算結(jié)果，上部編輯框中顯示其公式為公式為“=E3*10%”。如。如圖圖 23 所示。所示。l（2）應(yīng)發(fā)合計(jì)計(jì)算：選中）應(yīng)發(fā)合計(jì)計(jì)算：選中 I3 單元格，輸入單元格，輸入“=”號(hào)；號(hào)；單擊單擊 E3，輸入，輸入“+”號(hào)；單擊號(hào)；單擊 F3，輸入

18、，輸入“+”號(hào)；單號(hào)；單擊擊“G3”，輸入，輸入“+”號(hào)；單擊號(hào)；單擊 H3，按下回車(chē)鍵即可。，按下回車(chē)鍵即可。其編輯框中顯示的公式將為其編輯框中顯示的公式將為“=E3+F3+G3+H3”。也可在選中也可在選中I3單元格后，在英文狀態(tài)下直接從鍵單元格后，在英文狀態(tài)下直接從鍵盤(pán)上輸入盤(pán)上輸入“=E3+F3+G3+H3”，然后按下鍵盤(pán)上的，然后按下鍵盤(pán)上的回車(chē)鍵回車(chē)鍵 Enter。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l（3）醫(yī)療保險(xiǎn)金的公式為）醫(yī)療保險(xiǎn)金的公式為 K3=(E3+F3)*2%，操作，操作方法是：選定方法是：選定 K3，輸入，輸入“=”號(hào)；輸入左括號(hào)號(hào)；輸入左括

19、號(hào)“（”，單擊單擊 E3 單元格；輸入單元格；輸入“+”號(hào)，單擊號(hào)，單擊 F3 單元格；輸入單元格；輸入右括號(hào)右括號(hào)“）”，輸入，輸入“*2%”；按下鍵盤(pán)上的回車(chē)鍵。；按下鍵盤(pán)上的回車(chē)鍵。l（4）其他單元格的公式分別為：）其他單元格的公式分別為：H3=E3*5%，J3=I3*5%，M3=J3+K3+L3，N3=I3M3。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l（5）選定）選定 F3:N3 單元格區(qū)域，如單元格區(qū)域，如圖圖 23 所示；然后所示；然后將鼠標(biāo)指向?qū)⑹髽?biāo)指向 N3 單元格右下角的填充柄，此時(shí)鼠標(biāo)變?yōu)閱卧裼蚁陆堑奶畛浔?，此時(shí)鼠標(biāo)變?yōu)榇执蟮拇执蟮摹?”字狀，按下鼠

20、標(biāo)垂直向下拖動(dòng)到第字狀，按下鼠標(biāo)垂直向下拖動(dòng)到第 14 行，行，然后釋放鼠標(biāo)；實(shí)現(xiàn)單元格公式的復(fù)制。然后釋放鼠標(biāo)；實(shí)現(xiàn)單元格公式的復(fù)制。Excel 自動(dòng)計(jì)自動(dòng)計(jì)算出本表各單元格的數(shù)值。由于在公式中使用了單元格引算出本表各單元格的數(shù)值。由于在公式中使用了單元格引用，若修改用，若修改 E 列中某單元格的職務(wù)工資，則引用這一單列中某單元格的職務(wù)工資，則引用這一單元格的其他單元格的值也會(huì)隨之改變。同時(shí)，由于第元格的其他單元格的值也會(huì)隨之改變。同時(shí)，由于第 3 行相關(guān)單元格的公式均是對(duì)其他單元格的相對(duì)引用，所以行相關(guān)單元格的公式均是對(duì)其他單元格的相對(duì)引用，所以用填充柄自動(dòng)復(fù)制的用填充柄自動(dòng)復(fù)制的 F4:

21、N14 區(qū)域的單元公式，也會(huì)自區(qū)域的單元公式，也會(huì)自動(dòng)調(diào)整為對(duì)本行各單元格的引用，從而保證計(jì)算結(jié)果的正動(dòng)調(diào)整為對(duì)本行各單元格的引用，從而保證計(jì)算結(jié)果的正確性。確性。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式四、公式返回的錯(cuò)誤值四、公式返回的錯(cuò)誤值l在輸入公式時(shí)，有不符合要求的輸入，就無(wú)法在在輸入公式時(shí)，有不符合要求的輸入，就無(wú)法在 Excel工作表的單元格中顯示正常的運(yùn)算結(jié)果，該單元格中將工作表的單元格中顯示正常的運(yùn)算結(jié)果，該單元格中將會(huì)出現(xiàn)提示性的錯(cuò)誤值信息。了解這些錯(cuò)誤信息的含義有會(huì)出現(xiàn)提示性的錯(cuò)誤值信息。了解這些錯(cuò)誤信息的含義有助于用戶檢查、修改公式，直到公式正確。助于

22、用戶檢查、修改公式，直到公式正確。表表 25 中列中列出了出了 Excel 中的錯(cuò)誤信息及其含義。中的錯(cuò)誤信息及其含義。l【案例案例 23】某公司的產(chǎn)品銷售額如某公司的產(chǎn)品銷售額如表表 26 所示，要求所示，要求計(jì)算各產(chǎn)品的銷售比重。計(jì)算各產(chǎn)品的銷售比重。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l【操作提示操作提示】A 產(chǎn)品產(chǎn)品25 800B 產(chǎn)品產(chǎn)品36 900C 產(chǎn)品產(chǎn)品l37 300合計(jì)合計(jì)100 000l（1）在工作表的）在工作表的 A1、A2:B6、C2、E2、G1、G2:K3、G4、G6 單元格中，輸入相關(guān)的文字或數(shù)值；單元格中，輸入相關(guān)的文字或數(shù)值；如如圖圖

23、24 所示。所示。l（2）選定）選定 A1:E1 單元格區(qū)域，單擊格式工具欄的合并單元格區(qū)域，單擊格式工具欄的合并居中居中F1:F6、G1:K1、G4:G5 單元格區(qū)域。單元格區(qū)域。l（3）在）在 C3 單元格中輸入公式單元格中輸入公式“=B3/B6”，即分母，即分母為相對(duì)引用為相對(duì)引用 B6 單元格；而在單元格；而在 D3 單元格中輸入公式單元格中輸入公式“=B3/$B$6”，即分母為絕對(duì)引用，即分母為絕對(duì)引用 B6 單元格；選單元格；選定定 C3:D3，鼠標(biāo)指向，鼠標(biāo)指向 D3 右下角的填充柄，待變?yōu)橛蚁陆堑奶畛浔?，待變?yōu)椤?”字狀態(tài)時(shí)，按下鼠標(biāo)垂直向下拖動(dòng)到第字狀態(tài)時(shí)，按下鼠標(biāo)垂直向下拖

24、動(dòng)到第 6 行，然后釋放行，然后釋放鼠標(biāo)。鼠標(biāo)。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Excel 運(yùn)算公式運(yùn)算公式l（4）同樣的，輸入第二種分析方法的公式）同樣的，輸入第二種分析方法的公式 H4=H3/K3、H5=H3/$K$3；選定；選定 H4:H5，通過(guò)，通過(guò)l填充柄水平向右拖動(dòng)，自動(dòng)填充填充柄水平向右拖動(dòng)，自動(dòng)填充 I4:K5 單元區(qū)域公式。單元區(qū)域公式。l（5）分析：由于）分析：由于 D3 單元格公式的分母是絕對(duì)引用單元格公式的分母是絕對(duì)引用 B6 單元格的數(shù)值，通過(guò)自動(dòng)填充方式復(fù)制的單元格的數(shù)值，通過(guò)自動(dòng)填充方式復(fù)制的 D4:D6 單元單元格公式中的分母始終引用格公式中的分母始終引用 B6

25、 單元格的值；單元格的值；D3 單元格公單元格公式的分子相對(duì)引用式的分子相對(duì)引用B3單元格的值，自動(dòng)填充后的分子將單元格的值，自動(dòng)填充后的分子將隨單元格而改變；所以公式將變隨單元格而改變；所以公式將變D4=B4/$B$6、lD5=B5/$B$6、D6=B6/$B$6，計(jì)算結(jié)果是正確的。，計(jì)算結(jié)果是正確的。l相反相反C3單元格的分子、分母均為相對(duì)引用；當(dāng)自動(dòng)填充單元格的分子、分母均為相對(duì)引用；當(dāng)自動(dòng)填充復(fù)制公式時(shí)，引用的單元均會(huì)隨之改變，即其公式將會(huì)變復(fù)制公式時(shí)，引用的單元均會(huì)隨之改變，即其公式將會(huì)變?yōu)闉?C4=B4/B7、C5=B5/B8、C6=B6/B9。返回上一頁(yè) 下一頁(yè)第一節(jié)第一節(jié) Ex

26、cel 運(yùn)算公式運(yùn)算公式l同理，同理，H5 單元公式的分母為絕對(duì)引用單元公式的分母為絕對(duì)引用 K3，所以自動(dòng)填，所以自動(dòng)填充后的充后的 I5:K5 的公式是正確的；而的公式是正確的；而 H4 單元公式為相單元公式為相對(duì)引用對(duì)引用 K3 的值，自動(dòng)填充后出現(xiàn)的值，自動(dòng)填充后出現(xiàn)“分母為分母為 0”的錯(cuò)。的錯(cuò)。l（6）選定）選定 A1:K6 單元格區(qū)域，單擊格式工具欄的邊框單元格區(qū)域，單擊格式工具欄的邊框按鈕中的下拉箭頭，在其中選擇按鈕中的下拉箭頭，在其中選擇“所有框線所有框線”選項(xiàng)，給工選項(xiàng)，給工作表中的有效表格（即數(shù)據(jù)區(qū)域）加上邊框線。作表中的有效表格（即數(shù)據(jù)區(qū)域）加上邊框線。l（7）單擊工作

27、表左上角行列交叉處的全選按鈕，然后通）單擊工作表左上角行列交叉處的全選按鈕，然后通過(guò)格式工具欄，設(shè)置字體。（過(guò)格式工具欄，設(shè)置字體。（8）選定）選定 B3:B6，按下鍵，按下鍵盤(pán)上的盤(pán)上的 CTRL 鍵，再選定鍵，再選定 H3:K3 單元區(qū)域；單擊格式單元區(qū)域；單擊格式工具欄的千位分隔按鈕、單擊兩次減少小數(shù)位數(shù)按鈕。選工具欄的千位分隔按鈕、單擊兩次減少小數(shù)位數(shù)按鈕。選定定 C3:D6，按下鍵盤(pán)上的，按下鍵盤(pán)上的 CTRL鍵，再選定鍵，再選定H4:K5單元區(qū)域；單擊格式工具欄的百分比按鈕單元區(qū)域；單擊格式工具欄的百分比按鈕返回上一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l在在 Excel中，函數(shù)實(shí)際

28、上是一個(gè)預(yù)先定義的特定計(jì)算中，函數(shù)實(shí)際上是一個(gè)預(yù)先定義的特定計(jì)算公式。公式。按照這個(gè)特定的計(jì)算公式對(duì)一個(gè)或多個(gè)參數(shù)進(jìn)行計(jì)算，并按照這個(gè)特定的計(jì)算公式對(duì)一個(gè)或多個(gè)參數(shù)進(jìn)行計(jì)算，并得出一個(gè)或多個(gè)計(jì)算結(jié)果，叫做函數(shù)值。得出一個(gè)或多個(gè)計(jì)算結(jié)果，叫做函數(shù)值。返回下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)一、函數(shù)的使用與構(gòu)成一、函數(shù)的使用與構(gòu)成l1. 函數(shù)的使用。函數(shù)的使用。Excel 提供了財(cái)務(wù)函數(shù)、日期與時(shí)間函提供了財(cái)務(wù)函數(shù)、日期與時(shí)間函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)函數(shù)、查找與引用函數(shù)、數(shù)據(jù)數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫(kù)函數(shù)、文本函數(shù)、邏輯函數(shù)、信息函數(shù)九大類兩百多個(gè)庫(kù)函數(shù)、文本函數(shù)、

29、邏輯函數(shù)、信息函數(shù)九大類兩百多個(gè)函數(shù)。使用這些函數(shù)不僅可以完成許多復(fù)雜的計(jì)算，而且函數(shù)。使用這些函數(shù)不僅可以完成許多復(fù)雜的計(jì)算，而且還可以簡(jiǎn)化公式的繁雜程度。還可以簡(jiǎn)化公式的繁雜程度。l【案例案例 24】某公司生產(chǎn)經(jīng)營(yíng)某公司生產(chǎn)經(jīng)營(yíng) 46 種產(chǎn)品，種產(chǎn)品，2009 年年各季度銷售收入如各季度銷售收入如表表 27 所示。要求用函數(shù)計(jì)算各季度、所示。要求用函數(shù)計(jì)算各季度、各產(chǎn)品的合計(jì)收入（其他指標(biāo)以后介紹）。各產(chǎn)品的合計(jì)收入（其他指標(biāo)以后介紹）。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l（1）設(shè)計(jì)表格。錄入）設(shè)計(jì)表格。錄入 A 列和第列和第 2 行的相關(guān)文字；輸入行的相關(guān)文字；輸入 B

30、3:G6 的已知金額；合并的已知金額；合并 A1:L1單元格區(qū)域；設(shè)置字單元格區(qū)域；設(shè)置字體字號(hào)。如體字號(hào)。如圖圖 25 所示。所示。l（2）常用工具欄求和法。操作方法如下：）常用工具欄求和法。操作方法如下：la. 選定選定 H3 單元格，單擊常用工具欄的自動(dòng)求和按鈕，單元格，單擊常用工具欄的自動(dòng)求和按鈕，或單擊該按鈕邊的下拉箭頭，在彈出的下拉菜單中，選擇或單擊該按鈕邊的下拉箭頭，在彈出的下拉菜單中，選擇“求和求和”命令。命令。lb. 此時(shí)該單元格中將反白顯示求和公式此時(shí)該單元格中將反白顯示求和公式“=SUM(B3:G3)”，且，且 B3:G3 單元格將以閃爍的虛單元格將以閃爍的虛框顯示該公式

31、所引用的單元格區(qū)域，同時(shí)編輯框中顯示其框顯示該公式所引用的單元格區(qū)域，同時(shí)編輯框中顯示其公式公式“=SUM(B3:G3)”。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)lc. 由于公式無(wú)誤，所以再次單擊自動(dòng)求和按鈕，以確認(rèn)由于公式無(wú)誤，所以再次單擊自動(dòng)求和按鈕，以確認(rèn)并鎖定該公式；也可按下鍵盤(pán)上的回車(chē)鍵并鎖定該公式；也可按下鍵盤(pán)上的回車(chē)鍵 ENTER，或編，或編輯框前的輸入按鈕鎖定公式。輯框前的輸入按鈕鎖定公式。l若雙擊自動(dòng)求和按鈕，可快速實(shí)現(xiàn)上述求和公式的輸入。若雙擊自動(dòng)求和按鈕，可快速實(shí)現(xiàn)上述求和公式的輸入。ld. 選定選定 H3 單元格，鼠標(biāo)指向右下角的填充柄，待變?yōu)閱卧瘢髽?biāo)

32、指向右下角的填充柄，待變?yōu)椤?”字狀態(tài)時(shí)，按下鼠標(biāo)向下拖動(dòng)至字狀態(tài)時(shí)，按下鼠標(biāo)向下拖動(dòng)至 H6 單元格，以單元格，以實(shí)現(xiàn)自動(dòng)復(fù)制公式。實(shí)現(xiàn)自動(dòng)復(fù)制公式。l（3）向?qū)蠛头?。操作方法如下。）向?qū)蠛头?。操作方法如下。la. 選定選定 B7 單元格，單擊編輯框前的插入函數(shù)按鈕，或單元格，單擊編輯框前的插入函數(shù)按鈕，或單擊自動(dòng)求和鈕邊的下拉箭頭選擇單擊自動(dòng)求和鈕邊的下拉箭頭選擇“其他函數(shù)其他函數(shù)”命令，或命令，或選擇選擇“插入插入/函數(shù)函數(shù)”菜單命令，均會(huì)彈出菜單命令，均會(huì)彈出“插入函數(shù)插入函數(shù)”界界面，如面，如圖圖 26 所示。所示。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)lb. 單擊

33、該界面的類別后的下拉箭頭，在彈出的下拉框中單擊該界面的類別后的下拉箭頭，在彈出的下拉框中選擇選擇“數(shù)學(xué)與三角函數(shù)數(shù)學(xué)與三角函數(shù)”；再通過(guò)右部的滾動(dòng)條，找到求；再通過(guò)右部的滾動(dòng)條，找到求和函數(shù)和函數(shù)“SUM”；選定；選定“SUM”函數(shù)后單擊函數(shù)后單擊“確定確定”按按鈕，或雙擊該函數(shù)，進(jìn)入鈕，或雙擊該函數(shù)，進(jìn)入“函數(shù)參數(shù)函數(shù)參數(shù)”界面，如界面，如圖圖 27 所示。所示。lc. 在函數(shù)參數(shù)界面的在函數(shù)參數(shù)界面的 NUMBER1 參數(shù)后部的輸入框中參數(shù)后部的輸入框中輸入輸入“B3:B6”；或者單擊后部的引用按鈕，用鼠標(biāo)選；或者單擊后部的引用按鈕，用鼠標(biāo)選擇擇“B3:B6”單元格區(qū)域，再單擊引用按鈕回到

34、函數(shù)參單元格區(qū)域，再單擊引用按鈕回到函數(shù)參數(shù)界面，實(shí)現(xiàn)單元格的引用。數(shù)界面，實(shí)現(xiàn)單元格的引用。ld. 單擊單擊“確定確定”按鈕回到工作表界面，此時(shí)按鈕回到工作表界面，此時(shí) B7 單元格單元格中將顯示計(jì)算結(jié)果，編輯框中將顯示函數(shù)公式中將顯示計(jì)算結(jié)果，編輯框中將顯示函數(shù)公式“=SUM(B3:B6)”。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l（4）直接輸入法。）直接輸入法。l2. Excel函數(shù)的構(gòu)成。函數(shù)是由函數(shù)名及參數(shù)構(gòu)成，函函數(shù)的構(gòu)成。函數(shù)是由函數(shù)名及參數(shù)構(gòu)成，函數(shù)名說(shuō)明函數(shù)所要進(jìn)行的運(yùn)算，而參數(shù)是指定函數(shù)使用的數(shù)名說(shuō)明函數(shù)所要進(jìn)行的運(yùn)算，而參數(shù)是指定函數(shù)使用的數(shù)值或單元格引用。

35、函數(shù)公式如下：數(shù)值或單元格引用。函數(shù)公式如下：=函數(shù)（參數(shù)函數(shù)（參數(shù) 1,參參數(shù)數(shù) 2,參數(shù)參數(shù) 3,）例如，公式）例如，公式“=SUM(C3:C6)”，式，式中中 SUM 為函數(shù)名，是求和。為函數(shù)名，是求和。C3:C6 單元格區(qū)域是參數(shù)，單元格區(qū)域是參數(shù)，該公式表示為對(duì)該公式表示為對(duì) C3 到到 C6 單元格區(qū)域內(nèi)的數(shù)據(jù)求和。單元格區(qū)域內(nèi)的數(shù)據(jù)求和。使用函數(shù)時(shí)，函數(shù)的參數(shù)必須滿足以下規(guī)定：使用函數(shù)時(shí)，函數(shù)的參數(shù)必須滿足以下規(guī)定：函數(shù)參數(shù)函數(shù)參數(shù)應(yīng)用括號(hào)括起來(lái)，且括號(hào)前后不能有空格；應(yīng)用括號(hào)括起來(lái)，且括號(hào)前后不能有空格；函數(shù)中參數(shù)函數(shù)中參數(shù)多于一個(gè)時(shí)，必須用逗號(hào)分隔他們；多于一個(gè)時(shí)，必須用逗號(hào)

36、分隔他們；參數(shù)除了是單元格參數(shù)除了是單元格的引用外，還可以是數(shù)、文本、邏輯值、數(shù)值、公式或其的引用外，還可以是數(shù)、文本、邏輯值、數(shù)值、公式或其他函數(shù)；他函數(shù)；給定的參數(shù)必須能產(chǎn)生一個(gè)有效的值。給定的參數(shù)必須能產(chǎn)生一個(gè)有效的值。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)二、常用函數(shù)的使用二、常用函數(shù)的使用l1.求和函數(shù)求和函數(shù) SUM。函數(shù)公式為：。函數(shù)公式為：= SUM(Number1,Number2,)l式中，式中，Number1,Number2,為為 130 個(gè)個(gè)需要求和的參數(shù)。需要求和的參數(shù)。l2.平均數(shù)函數(shù)平均數(shù)函數(shù) AVERAGE。函數(shù)公式為：。函數(shù)公式為：l=AVERAG

37、E(Number1,Number2,)l3.計(jì)數(shù)函數(shù)計(jì)數(shù)函數(shù) COUNT。函數(shù)公式為：。函數(shù)公式為：= COUNT(Value1,Value2,)lValue1, Value2,表示包含或引用各種類型數(shù)據(jù)的參表示包含或引用各種類型數(shù)據(jù)的參數(shù)（數(shù)（130 個(gè)），但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算。個(gè)），但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l4. 條件函數(shù)條件函數(shù) IF。其功能是判斷一個(gè)條件是否滿足，如果。其功能是判斷一個(gè)條件是否滿足，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值。函數(shù)公式滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值。函數(shù)公式為：為：l=IF(L

38、ogical_test,Value_if_true,Value_if_false)l式中，式中，Logical_test 表示計(jì)算結(jié)果為表示計(jì)算結(jié)果為 TRUE（邏輯真（邏輯真值）或值）或 FALSE（邏輯假值）的任意值或表達(dá)式；（邏輯假值）的任意值或表達(dá)式；Value_if_true 表示為表示為 TRUE 時(shí)的返回值；時(shí)的返回值；Value_if_false 表示為表示為 FALSE 時(shí)的返回值。時(shí)的返回值。lIF 在進(jìn)行多次判斷中，最多可以嵌套七層。在進(jìn)行多次判斷中，最多可以嵌套七層。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l【案例案例 25】承案例承案例 22，計(jì)算交通補(bǔ)貼

39、：經(jīng)理為，計(jì)算交通補(bǔ)貼：經(jīng)理為 300 元、科員為元、科員為 150 元。元。l【操作提示操作提示】l（1）選中）選中 G3 單元格；在其中輸入公式單元格；在其中輸入公式“=IF(D3=經(jīng)經(jīng)理理,300,150)”。該公式的含義為：。該公式的含義為：lD3 單元格中如果是文本單元格中如果是文本“經(jīng)理經(jīng)理”，就返回其邏輯真值，就返回其邏輯真值 300，否則就返回假值，否則就返回假值 150。其結(jié)果如。其結(jié)果如圖圖28 所示。所示。l（2）拖動(dòng)）拖動(dòng) G3 單元格填充柄向下填充公式即可。單元格填充柄向下填充公式即可。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l5. 求最大值函數(shù)求最大值函

40、數(shù) MAX。其功能是返回一組數(shù)值中的最。其功能是返回一組數(shù)值中的最大值。函數(shù)公式為：大值。函數(shù)公式為：l=MAX(Number1,Number2,)l式中，式中，Number1,Number2,，表示要從中找出最，表示要從中找出最大值的大值的 130 個(gè)數(shù)字參數(shù)。個(gè)數(shù)字參數(shù)。l6.求最小值函數(shù)求最小值函數(shù) MIN。其功能是返回一組數(shù)值中。其功能是返回一組數(shù)值中的最小值。函數(shù)公式為：的最小值。函數(shù)公式為：l=MIN(Number1,Number2,)l【案例案例 26】承前案例承前案例 24，計(jì)算各季品種數(shù)、平均銷，計(jì)算各季品種數(shù)、平均銷售額、各季最大銷售額、最小銷售額、判斷哪些產(chǎn)品全年售額、各

41、季最大銷售額、最小銷售額、判斷哪些產(chǎn)品全年銷售額超過(guò)了銷售額超過(guò)了 2 000 萬(wàn)元。萬(wàn)元。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l【操作提示操作提示】l（1）平均數(shù)。方法是：選定）平均數(shù)。方法是：選定 I3 單元格，輸入單元格，輸入“=AVERAGE(B3:E3)”；選定；選定 I4 單元格，輸入單元格，輸入“=AVERAGE(B4:F4)”；選定；選定 I5 單元格，輸入單元格，輸入“=AVERAGE(B5:G5)”；選定；選定 I6 單元格，輸入單元格，輸入“=AVERAGE(B6:G6)”。l（2）各季最大銷售額。輸入其函數(shù)公式：）各季最大銷售額。輸入其函數(shù)公式：J3=M

42、AX(B3:E3)、J4= MAX(B4:F4)、lJ5= MAX(B5:G5)、J6= MAX(B6:G6)。l（3）最小銷售額。）最小銷售額。K3=MIN(B3:E3)、K4 =MIN(B4:F4)、K5 =MIN(B5:G5)、K6 =MIN(B6:G6)。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l（4）各季經(jīng)營(yíng)品種。輸入公式）各季經(jīng)營(yíng)品種。輸入公式 L3=COUNT(B3:G3)；然后自動(dòng)填充然后自動(dòng)填充 L4:L7 單元區(qū)域公式。以上的計(jì)算均可使單元區(qū)域公式。以上的計(jì)算均可使用向?qū)Хǎx擇用向?qū)Хǎx擇“統(tǒng)計(jì)統(tǒng)計(jì)”類中的對(duì)應(yīng)函數(shù)進(jìn)行計(jì)算。類中的對(duì)應(yīng)函數(shù)進(jìn)行計(jì)算。l（5）是

43、否超）是否超 2 000 萬(wàn)元判斷。輸入公式萬(wàn)元判斷。輸入公式 B8=IF(B72000,是是,否否)；然后自動(dòng)填充；然后自動(dòng)填充 C8:G8單元區(qū)域公式。單元區(qū)域公式。l7. 取絕對(duì)值函數(shù)取絕對(duì)值函數(shù) ABS。其功能是取表達(dá)式的絕對(duì)值。函。其功能是取表達(dá)式的絕對(duì)值。函數(shù)公式為：數(shù)公式為：=ABS(Number)式中，式中，Number 表示需表示需要計(jì)算其絕對(duì)值的實(shí)數(shù)。要計(jì)算其絕對(duì)值的實(shí)數(shù)。l8. 條件計(jì)數(shù)條件計(jì)數(shù) COUNTIF。其功能是計(jì)算某個(gè)區(qū)域中滿足。其功能是計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格數(shù)目。函數(shù)公式為：給定條件的單元格數(shù)目。函數(shù)公式為：l=COUNTIF(Range,Crit

44、eria)返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l式中：式中：Range 表示需要計(jì)算其中滿足條件的單元格數(shù)目表示需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域；的單元格區(qū)域；Criteria 表示確定哪些單元格將被計(jì)算表示確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。l9.求模函數(shù)求模函數(shù) MOD。其功能是返回兩數(shù)相除的余數(shù)。函數(shù)。其功能是返回兩數(shù)相除的余數(shù)。函數(shù)公式為：公式為：=MOD(Number,Divisor)式中，式中，Number 表示被除數(shù)；表示被除數(shù)；Divisor 表示除數(shù)。表示除數(shù)。l10.條件求

45、和函數(shù)條件求和函數(shù) SUMIF。其功能是對(duì)滿足條件的單元。其功能是對(duì)滿足條件的單元格求和。函數(shù)公式為：格求和。函數(shù)公式為：l=SUMIF(Range,Criteria,Sum_range)l式中，式中，Range 表示用于條件判斷的單元格區(qū)域；表示用于條件判斷的單元格區(qū)域；Criteria 表示確定哪些單元格將被相加求和的條件，其表示確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本；形式可以為數(shù)字、表達(dá)式或文本；Sum_range 表示需表示需要求和的實(shí)際單元格。要求和的實(shí)際單元格。返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l11. 排位函數(shù)排位函數(shù) RANK。其函數(shù)

46、公式如下：。其函數(shù)公式如下：l=RANK(Number,Ref,Order)l式中，式中，Number 表示需要排位的數(shù)字；表示需要排位的數(shù)字；Ref 表示包含一表示包含一組數(shù)字的數(shù)組或引用，即需排位的全部總體，非數(shù)值型參組數(shù)字的數(shù)組或引用，即需排位的全部總體，非數(shù)值型參數(shù)將被忽略；數(shù)將被忽略；Order 表示排位方式，如果表示排位方式，如果 Order 為為 0 或省略則按降序排列，否則按升序排列?；蚴÷詣t按降序排列，否則按升序排列。l【案例案例 27】承案例承案例 22 計(jì)算個(gè)人所得稅。個(gè)人所得稅計(jì)算個(gè)人所得稅。個(gè)人所得稅的起征點(diǎn)和稅率如的起征點(diǎn)和稅率如表表 28 所示。所示。l工資、薪金

47、所得稅的計(jì)算公式是：工資、薪金所得稅的計(jì)算公式是：l應(yīng)納個(gè)人所得稅稅額應(yīng)納個(gè)人所得稅稅額 =應(yīng)納稅所得額應(yīng)納稅所得額適用稅率適用稅率速算扣速算扣除數(shù)除數(shù)返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l【操作提示操作提示】l（1）選中）選中L3 單元格；在單元格中輸入公式（這些字符單元格；在單元格中輸入公式（這些字符均應(yīng)在英文輸入法下均應(yīng)在英文輸入法下l=IF(I3J3K320000,0,IF(I3J3K32000500,0.05*(I3J3K32000),IF(I3J3K32000l2000,0.1*(I3J3K32000)25,IF(I3J3K320005000,0.15*(I3J3K

48、32000)125,0.2*(I3Jl3K32000)375) 返回上一頁(yè) 下一頁(yè)第二節(jié)第二節(jié) Excel 函數(shù)函數(shù)l公式含義為：按國(guó)家規(guī)定，如果應(yīng)納稅稅額公式含義為：按國(guó)家規(guī)定，如果應(yīng)納稅稅額員工的應(yīng)員工的應(yīng)發(fā)合計(jì)發(fā)合計(jì)法定免征項(xiàng)法定免征項(xiàng)(養(yǎng)老保險(xiǎn)金養(yǎng)老保險(xiǎn)金+醫(yī)療保險(xiǎn)金醫(yī)療保險(xiǎn)金) 起征點(diǎn)起征點(diǎn)(現(xiàn)行為現(xiàn)行為 2000 元元)小于小于 0，則個(gè)人所得稅為，則個(gè)人所得稅為 0；否則；否則如果應(yīng)納稅稅額在如果應(yīng)納稅稅額在 0500 元，為應(yīng)納稅稅額元，為應(yīng)納稅稅額5%；否則如果應(yīng)納稅稅額在否則如果應(yīng)納稅稅額在 5002 000 元，為應(yīng)納稅稅額元，為應(yīng)納稅稅額10%25元的速算扣除數(shù)元的速算

49、扣除數(shù)，以此類推；由于該公司，以此類推；由于該公司沒(méi)有人的收入超過(guò)沒(méi)有人的收入超過(guò) 20 000 元，所以，條件函數(shù)元，所以，條件函數(shù) IFl只判斷到只判斷到 5 00020 000 即可。即可。l（2）拖動(dòng)）拖動(dòng) L3 單元格的填充柄向下填充即可，結(jié)果如單元格的填充柄向下填充即可，結(jié)果如圖圖 29 所示。所示。返回上一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總一、數(shù)據(jù)排序一、數(shù)據(jù)排序l1.排序的概念。排序是將數(shù)據(jù)區(qū)域中的記錄，按其某字排序的概念。排序是將數(shù)據(jù)區(qū)域中的記錄，按其某字段名的數(shù)據(jù)值由小到大（升序）或由大到?。ń敌颍┑捻樁蚊臄?shù)據(jù)值由小到大（升序）或由大到?。ń敌颍┑捻樞蜻M(jìn)行

50、排列，用來(lái)排序的字段或條件稱為排序鍵。排序后，序進(jìn)行排列，用來(lái)排序的字段或條件稱為排序鍵。排序后，將會(huì)對(duì)整頓秩序各工作表或選定區(qū)域的內(nèi)容進(jìn)行調(diào)整。將會(huì)對(duì)整頓秩序各工作表或選定區(qū)域的內(nèi)容進(jìn)行調(diào)整。l2.排序操作。排序的操作方法有以下幾種。排序操作。排序的操作方法有以下幾種。l（1）用工具按鈕排序。若只按一個(gè)排序標(biāo)志進(jìn)行排序，）用工具按鈕排序。若只按一個(gè)排序標(biāo)志進(jìn)行排序，l用常用工具欄上的排序按鈕更為方便。其操作是：在數(shù)據(jù)用常用工具欄上的排序按鈕更為方便。其操作是：在數(shù)據(jù)區(qū)域中選定要排序的單元區(qū)域。根據(jù)需要單擊常用工具欄區(qū)域中選定要排序的單元區(qū)域。根據(jù)需要單擊常用工具欄上的上的“降序降序”或或“升

51、序升序”，這時(shí)會(huì)彈出排序的警告對(duì)話框，這時(shí)會(huì)彈出排序的警告對(duì)話框（如（如圖圖 210 所示）。所示）。返回下一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總l在在“排序警告排序警告”對(duì)話框中一定注意，選擇對(duì)話框中一定注意，選擇“擴(kuò)展選定區(qū)域擴(kuò)展選定區(qū)域”以某個(gè)字段進(jìn)行排序時(shí)，對(duì)應(yīng)的列會(huì)隨排序位置的變化而以某個(gè)字段進(jìn)行排序時(shí)，對(duì)應(yīng)的列會(huì)隨排序位置的變化而變化。而變化。而“以當(dāng)前選定區(qū)域排序以當(dāng)前選定區(qū)域排序”，其他的列將不參與排，其他的列將不參與排序。序。Excel 中進(jìn)行升序排序時(shí)，數(shù)字和日期按由小到大中進(jìn)行升序排序時(shí)，數(shù)字和日期按由小到大排列；字母按排列；字母按 AZ、az 進(jìn)行排列；漢

52、字要選轉(zhuǎn)化為進(jìn)行排列；漢字要選轉(zhuǎn)化為漢語(yǔ)拼音，再按字符比較規(guī)則進(jìn)行比較。漢語(yǔ)拼音，再按字符比較規(guī)則進(jìn)行比較。l（2）用菜單命令進(jìn)行排序。使用）用菜單命令進(jìn)行排序。使用“數(shù)據(jù)數(shù)據(jù)排序排序”命令，命令，可以按多個(gè)排序鍵來(lái)進(jìn)行排序，還可以按列、或者按行來(lái)可以按多個(gè)排序鍵來(lái)進(jìn)行排序，還可以按列、或者按行來(lái)排序。但排序鍵最多不能超過(guò)三個(gè)，分別稱為主要關(guān)鍵字、排序。但排序鍵最多不能超過(guò)三個(gè)，分別稱為主要關(guān)鍵字、次要關(guān)鍵字和第三關(guān)鍵字；當(dāng)主關(guān)鍵字值相同時(shí)，就根據(jù)次要關(guān)鍵字和第三關(guān)鍵字；當(dāng)主關(guān)鍵字值相同時(shí)，就根據(jù)次關(guān)鍵字排序，如果次關(guān)鍵字也一致，則根據(jù)第三關(guān)鍵字次關(guān)鍵字排序，如果次關(guān)鍵字也一致，則根據(jù)第三關(guān)鍵

53、字來(lái)排序。來(lái)排序。返回上一頁(yè) 下一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總l【案例案例 28】承案例承案例 27，進(jìn)行工資表的排序。，進(jìn)行工資表的排序。l【操作提示操作提示】la.選擇數(shù)據(jù)區(qū)域中一個(gè)單元格；選擇選擇數(shù)據(jù)區(qū)域中一個(gè)單元格；選擇“數(shù)據(jù)數(shù)據(jù)排序排序”命令，命令，打開(kāi)如打開(kāi)如圖圖 211所示的所示的“排序排序”對(duì)話框。對(duì)話框。lb.單擊對(duì)話框中單擊對(duì)話框中“主要關(guān)鍵字主要關(guān)鍵字”列表框的向下箭頭，選列表框的向下箭頭，選取主要關(guān)鍵字。若需要還可以選取次要關(guān)鍵字和第三關(guān)鍵取主要關(guān)鍵字。若需要還可以選取次要關(guān)鍵字和第三關(guān)鍵字，并在字，并在“升序升序”、“降序降序”上作出相應(yīng)的選擇。

54、上作出相應(yīng)的選擇。lc.單擊單擊“確定確定”按鈕，按職務(wù)工資的升序排序，排序后按鈕，按職務(wù)工資的升序排序，排序后圖圖 212 中人員編號(hào)的順序變化。中人員編號(hào)的順序變化。l如果要按行進(jìn)行排序，或者對(duì)文本要進(jìn)行如果要按行進(jìn)行排序，或者對(duì)文本要進(jìn)行“按字母排序按字母排序”或或“按筆畫(huà)排序按筆畫(huà)排序”，或者排序時(shí)字母要區(qū)分大小寫(xiě)等，請(qǐng)，或者排序時(shí)字母要區(qū)分大小寫(xiě)等，請(qǐng)?jiān)趫D在圖 211 所示的所示的“排序排序”對(duì)話框選擇對(duì)話框選擇“選項(xiàng)選項(xiàng)”，打開(kāi)，打開(kāi)“選項(xiàng)選項(xiàng)”對(duì)話框并在其中進(jìn)行選擇。對(duì)話框并在其中進(jìn)行選擇。返回上一頁(yè) 下一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總l3.返回排序前的表格。

55、當(dāng)反復(fù)對(duì)表格進(jìn)行排序后，表格返回排序前的表格。當(dāng)反復(fù)對(duì)表格進(jìn)行排序后，表格的原有次序被打亂，若需返回原有表格次序，可使用常用的原有次序被打亂，若需返回原有表格次序，可使用常用工具欄的撤銷或恢復(fù)按鈕。但是排序的次數(shù)較多，或排序工具欄的撤銷或恢復(fù)按鈕。但是排序的次數(shù)較多，或排序后還進(jìn)行了其他的操作，此時(shí)恢復(fù)就不容易了。所以，在后還進(jìn)行了其他的操作，此時(shí)恢復(fù)就不容易了。所以，在排序前最好是給表格增加一個(gè)表示原有表格次序的字段，排序前最好是給表格增加一個(gè)表示原有表格次序的字段，如如“序號(hào)序號(hào)”、“編號(hào)編號(hào)”、“NO.”等字段名，置于等字段名，置于 A 列、列、或表格的有效數(shù)據(jù)的最后一列，對(duì)原表格的所有

56、記錄（即或表格的有效數(shù)據(jù)的最后一列，對(duì)原表格的所有記錄（即每行）進(jìn)行編號(hào)。這樣，排序后要返回原表格的次序只需每行）進(jìn)行編號(hào)。這樣，排序后要返回原表格的次序只需對(duì)該字段進(jìn)行重新排序即可。對(duì)該字段進(jìn)行重新排序即可。返回上一頁(yè) 下一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總二、分類匯總二、分類匯總l分類匯總就是將很多數(shù)據(jù)按同類別歸類放到一起，然后再分類匯總就是將很多數(shù)據(jù)按同類別歸類放到一起，然后再進(jìn)行同類數(shù)據(jù)求和、計(jì)數(shù)、求平均等匯總運(yùn)算。通過(guò)分類進(jìn)行同類數(shù)據(jù)求和、計(jì)數(shù)、求平均等匯總運(yùn)算。通過(guò)分類匯總，得到需要的統(tǒng)計(jì)信息，諸如商品銷售按部門(mén)、按人匯總，得到需要的統(tǒng)計(jì)信息，諸如商品銷售按部門(mén)、按

57、人員匯總等重要的統(tǒng)計(jì)信息。在分類匯總前必須對(duì)工作表指員匯總等重要的統(tǒng)計(jì)信息。在分類匯總前必須對(duì)工作表指定字段進(jìn)行排序；如果沒(méi)有排序，匯總結(jié)果很混亂，無(wú)法定字段進(jìn)行排序；如果沒(méi)有排序，匯總結(jié)果很混亂，無(wú)法達(dá)到真正的分類匯總的目的。分類匯總的形式有單字段分達(dá)到真正的分類匯總的目的。分類匯總的形式有單字段分類匯總和多字段分類匯總。類匯總和多字段分類匯總。l1.單字段分類匯總。單字段分類匯總。Excel能夠快速的以某一個(gè)字段為分能夠快速的以某一個(gè)字段為分類項(xiàng)，對(duì)數(shù)據(jù)區(qū)域中其他字段的數(shù)值進(jìn)行各種匯總統(tǒng)計(jì)。類項(xiàng)，對(duì)數(shù)據(jù)區(qū)域中其他字段的數(shù)值進(jìn)行各種匯總統(tǒng)計(jì)。分類匯總時(shí)將分類項(xiàng)稱為分類字段；將其他需統(tǒng)計(jì)值的字

58、分類匯總時(shí)將分類項(xiàng)稱為分類字段；將其他需統(tǒng)計(jì)值的字段稱為選定匯總項(xiàng)，匯總項(xiàng)可以多選；匯總的方式可以是段稱為選定匯總項(xiàng)，匯總項(xiàng)可以多選；匯總的方式可以是求和、乘積、最大值、最小值、計(jì)數(shù)、計(jì)數(shù)值、標(biāo)準(zhǔn)差、求和、乘積、最大值、最小值、計(jì)數(shù)、計(jì)數(shù)值、標(biāo)準(zhǔn)差、方差等。方差等。返回上一頁(yè) 下一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總l【案例案例 29】承案例承案例 27，對(duì)遠(yuǎn)華公司，對(duì)遠(yuǎn)華公司 2008 年年 8 月月工資表，按工資表，按“部門(mén)部門(mén)”分類匯總顯示實(shí)發(fā)工資的部門(mén)和人數(shù)。分類匯總顯示實(shí)發(fā)工資的部門(mén)和人數(shù)。l【操作提示操作提示】l（1）選取數(shù)據(jù)區(qū)域中的任一單元格）選取數(shù)據(jù)區(qū)域中的任一單

59、元格,選擇選擇“數(shù)據(jù)數(shù)據(jù)分類匯分類匯總總”命令，打開(kāi)如命令，打開(kāi)如圖圖 213 所示的所示的“分類匯總分類匯總”對(duì)話框。對(duì)話框。l（2）在）在“分類字段分類字段”中選擇中選擇“部門(mén)名稱部門(mén)名稱”；在；在“匯總方匯總方式式”中選擇中選擇“求和求和”；在；在“選定匯總項(xiàng)選定匯總項(xiàng)”中選擇中選擇“實(shí)發(fā)合實(shí)發(fā)合計(jì)計(jì)”；單擊；單擊“確定確定”按鈕，完成分類匯總操作。按鈕，完成分類匯總操作。l2.多字段分類匯總。若需要從數(shù)據(jù)區(qū)域中得到更多的信多字段分類匯總。若需要從數(shù)據(jù)區(qū)域中得到更多的信息，以便于分析數(shù)據(jù)，則多次執(zhí)行匯總功能即可。即選按息，以便于分析數(shù)據(jù)，則多次執(zhí)行匯總功能即可。即選按某一個(gè)字段進(jìn)行分類匯

60、總，然后再按另一個(gè)字段進(jìn)行分類某一個(gè)字段進(jìn)行分類匯總，然后再按另一個(gè)字段進(jìn)行分類匯總，但要在匯總，但要在圖圖 213 的分類匯總對(duì)話框中取消的分類匯總對(duì)話框中取消“替換替換當(dāng)前分類匯總當(dāng)前分類匯總”選項(xiàng)。選項(xiàng)。返回上一頁(yè) 下一頁(yè)第三節(jié)第三節(jié)數(shù)據(jù)排序與分類匯總數(shù)據(jù)排序與分類匯總l【操作提示操作提示】l在上例的基礎(chǔ)上，還需按在上例的基礎(chǔ)上，還需按“部門(mén)部門(mén)”分類匯總分類匯總“部門(mén)人數(shù)部門(mén)人數(shù)”，繼續(xù)其操作如下：繼續(xù)其操作如下：l（1）選取數(shù)據(jù)區(qū)域中的任一單元格，選擇）選取數(shù)據(jù)區(qū)域中的任一單元格，選擇“數(shù)據(jù)數(shù)據(jù)分類分類匯總匯總”，打開(kāi)，打開(kāi)“分類匯總分類匯總”對(duì)話框。對(duì)話框。l（2）在）在“分類字段分類字段