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

1、1,第二章Excel 的數(shù)據(jù)處理,第一節(jié)Excel 運(yùn)算公式 第二節(jié)Excel 函數(shù) 第三節(jié)數(shù)據(jù)排序與分類匯總 第四節(jié)數(shù)據(jù)篩選,2,第一節(jié) Excel 運(yùn)算公式,一、建立與編輯數(shù)據(jù)區(qū)域 1.數(shù)據(jù)區(qū)域的建立。數(shù)據(jù)區(qū)域是 Excel 工作表中進(jìn)行數(shù)據(jù)處理的單元區(qū)域。Excel 作為一個(gè)電子表格軟件，本身具有一個(gè)完備的表結(jié)構(gòu)，通?？蓪?shù)據(jù)區(qū)域中的列稱為字段，將行稱為記錄。建立數(shù)據(jù)區(qū)域的要求是最上面一行或兩行必須包含字段名（數(shù)據(jù)區(qū)域的列名稱），數(shù)據(jù)區(qū)域中應(yīng)盡量不要出現(xiàn)空行或空列，并保證每列中包含的是同類的信息。建立數(shù)據(jù)區(qū)域的方法是先建立一個(gè)表格，再一條一條地錄入數(shù)據(jù)，既可在工作表中錄入，也可通過記錄單

2、錄入,返回,下一頁,3,第一節(jié) Excel 運(yùn)算公式,案例 21】遠(yuǎn)華公司的人員、部門及職務(wù)工資等數(shù)據(jù)，如表 21 所示。除此之外，還有的工資表項(xiàng)目是：效益工資、交通補(bǔ)貼、住房補(bǔ)貼、應(yīng)發(fā)合計(jì)、養(yǎng)老保險(xiǎn)金、醫(yī)療保險(xiǎn)金、代扣稅、扣款合計(jì)、實(shí)發(fā)合計(jì)。要求建立數(shù)據(jù)區(qū)域,返回,上一頁,下一頁,4,第一節(jié) Excel 運(yùn)算公式,操作提示】 （1）在 Excel 工作表的最上面一行或兩行輸入字段名的內(nèi)容，如人員編號、姓名、部門名稱、養(yǎng)老保險(xiǎn)金等，如圖 21 第 2 行所示。 （2）移動(dòng)到數(shù)據(jù)區(qū)域下第一個(gè)空行（即圖 21 第 3 行），選取一個(gè)單元格。 （3）選擇“數(shù)據(jù)記錄單”菜單命令，打開如圖 22 所示的

3、記錄單對話框，左邊是建立的數(shù)據(jù)區(qū)域的各字段，在其編輯框中錄入相應(yīng)的數(shù)據(jù)。 （4）一條記錄錄入完畢后，單擊“新建”按鈕后再錄入第二條記錄。錄入完畢后單擊“關(guān)閉”按鈕，即可完成數(shù)據(jù)區(qū)域的建立,返回,上一頁,下一頁,5,第一節(jié) Excel 運(yùn)算公式,2.數(shù)據(jù)區(qū)域的編輯。錄入的數(shù)據(jù)有誤，可打開數(shù)據(jù)區(qū)域所在的工作表，選擇要修改的單元格，可以在編輯欄中直接修改，也可以在“記錄單”中對某條記錄進(jìn)行修改。要?jiǎng)h除數(shù)據(jù)區(qū)域中的行（記錄），可直接在工作表中選定要?jiǎng)h除的行，然后右擊選擇“刪除”命令即可。也可在“記錄單”中，通過“上一條”、“下一條”或“條件”按鈕，選出要?jiǎng)h除的行（即要?jiǎng)h除的一條記錄），單擊“刪除”按鈕

4、，即可刪除所選的行。必須注意，用記錄單上的“刪除”按鈕刪除的行是不能恢復(fù)的。要?jiǎng)h除數(shù)據(jù)區(qū)域中的列，則應(yīng)在工作表中選擇所要?jiǎng)h除的列，用“編輯”菜單或者列的“快捷菜單”中的“刪除”命令進(jìn)行,返回,上一頁,下一頁,6,第一節(jié) Excel 運(yùn)算公式,二、公式構(gòu)成與運(yùn)算類型 工作表中的數(shù)據(jù)往往需要進(jìn)行大量的計(jì)算，可以根據(jù)單元格之間的鉤稽關(guān)系，使用一些簡單的運(yùn)算公式或較為復(fù)雜的函數(shù)，Excel 將自動(dòng)完成這些運(yùn)算。 公式的構(gòu)成。Excel規(guī)定，公式必須以等號開始，是由單元格引用、運(yùn)算符、數(shù)值、字符、函數(shù)等組成的能夠進(jìn)行計(jì)算的式子。 2. Excel 中運(yùn)算符有四類，即算術(shù)運(yùn)算、字符運(yùn)算、比較運(yùn)算和引用運(yùn)算

5、。 （1）算術(shù)運(yùn)算。算術(shù)運(yùn)算是指加、減、乘、除等運(yùn)算。運(yùn)算的優(yōu)先次序?yàn)椋豪ㄌ栔笖?shù)乘除加減。同級運(yùn)算符按從左到右的順序進(jìn)行。算術(shù)運(yùn)算符如表 22 所示,返回,上一頁,下一頁,7,第一節(jié) Excel 運(yùn)算公式,2）字符運(yùn)算。字符運(yùn)算的運(yùn)算符是“&”，它表示連接、合并運(yùn)算兩邊的字符。加入的文本字符要使用雙引號，且應(yīng)是英文狀態(tài)下的雙引號。 （3）比較運(yùn)算。比較運(yùn)算是用來比較兩個(gè)數(shù)值的關(guān)系，并產(chǎn)生邏輯值 TRUL（邏輯真值）和 FALSE（邏輯假值）。比較運(yùn)算符如表 23 所示。 （4）引用運(yùn)算。引用運(yùn)算可以將單元格區(qū)域合并運(yùn)算,引用運(yùn)算一般都要涉及到單元格引用。“單元格引用”是指公式中輸入單元格地址時(shí)

6、，表示該單元格中的內(nèi)容參加運(yùn)算。當(dāng)引用的單元格中的數(shù)據(jù)發(fā)生了變化時(shí)，公式則自動(dòng)重新進(jìn)行計(jì)算并自動(dòng)更新計(jì)算結(jié)果，這種方式下，可以隨時(shí)觀察到數(shù)據(jù)之間的相互關(guān)系。表 24 所示,返回,上一頁,下一頁,8,第一節(jié) Excel 運(yùn)算公式,三、公式中使用引用 引用的作用在于標(biāo)識工作表上的單元格或單元格區(qū)域，并告知 Excel 在何處查找公式中所使用的數(shù)值或數(shù)據(jù)。單元格地址是用列標(biāo)和行號來標(biāo)示某一單元格的位置，所以若要引用某個(gè)單元格，則在公式中輸入單元格的行號、列標(biāo)，即單元格的地址即可。這樣在被引用單元格的數(shù)據(jù)變化時(shí)，公式所在單元格的數(shù)據(jù)也隨之變化。 1. 單元引用的類型。在 Excel 中有以下三種基本的

7、單元格引用類型,返回,上一頁,下一頁,9,第一節(jié) Excel 運(yùn)算公式,1）相對引用。是指在公式中直接對單元格的行號、列標(biāo)這種相對位置進(jìn)行的引用。如果公式所有的單元格位置調(diào)整，被引用單元格的地址也相應(yīng)變化；如果多行或多列復(fù)制或填充公式，引用的單元格地址也隨之改變。例如：E1 單元格中有公式“=A1+B1C1”；向后拖動(dòng) E1 單元格填充柄到 F1 時(shí)，則 F1 單元格中的公式為“=B1+C1D1”；向下拖動(dòng) E1 單元格填充柄到 E2 時(shí)，則 E2 單元格中的公式為“=A2+B2C2”。 （2）絕對引用。是指在公式中總是對特定位置的單元格地址進(jìn)行引用。如果公式所在單元格的位置改變，被引用單元格

8、的地址不變；如果多行或多列復(fù)制或填充公式，引用的單元格地址不會(huì)改變。其方法是在單元格地址的行號、列標(biāo)前均加上符號“$”，如“$A$1”表示對 A1 單元格進(jìn)行絕對引用,返回,上一頁,下一頁,10,第一節(jié) Excel 運(yùn)算公式,3）混合引用。是指在公式中對單元格的行號進(jìn)行絕對引用而對列標(biāo)進(jìn)行相對引用，或?qū)π刑栠M(jìn)行相對引用而對列標(biāo)進(jìn)行絕對引用。如果公式所在單元格的位置改變，則相對引用的隨之改變，而絕對引用的不會(huì)改變。其方法是在絕對引用的行號或列標(biāo)前加“$”符號。 引用類型的轉(zhuǎn)換。為將公式中用到的地址表示為絕對引用，需加上“$”符號。所以可以通過修改公式中“$”符號的位置、或刪除“$”符號，來改變單

9、元格的引用類型；也可按 F4 功能鍵，改變公式中單元格的引用類型,返回,上一頁,下一頁,11,第一節(jié) Excel 運(yùn)算公式,案例 22】遠(yuǎn)華公司的職務(wù)工資數(shù)據(jù)見案例 21。根據(jù)該公司的分配方案，效益工資為職務(wù)工資的 10%；住房補(bǔ)貼為職務(wù)工資的 5%；應(yīng)發(fā)合計(jì)為職務(wù)工資、效益工資、交通補(bǔ)貼、住房補(bǔ)貼之和；養(yǎng)老保險(xiǎn)金為應(yīng)發(fā)合計(jì)的 5%；醫(yī)療保險(xiǎn)金為職務(wù)工資、效益工資之和的 2%；扣款合計(jì)為養(yǎng)老保險(xiǎn)金、醫(yī)療保險(xiǎn)金、代扣稅之和；實(shí)發(fā)合計(jì)為應(yīng)發(fā)合計(jì)減扣款合計(jì)。要求 完成以上相關(guān)項(xiàng)目的計(jì)算（交通補(bǔ)貼、代扣稅暫不計(jì)算,返回,上一頁,下一頁,12,第一節(jié) Excel 運(yùn)算公式,操作提示】 （1）效益工資的計(jì)

10、算：選中 F3 單元格，然后輸入“=”；單擊一次 E3 單元格，則該單元格被相對引用到公式中；再輸入“*10%”；公式輸入完畢后按回車鍵，則 F3 單元格自動(dòng)顯示計(jì)算結(jié)果，上部編輯框中顯示其公式為“=E3*10%”。如圖 23 所示。 （2）應(yīng)發(fā)合計(jì)計(jì)算：選中 I3 單元格，輸入“=”號；單擊 E3，輸入“+”號；單擊 F3，輸入“+”號；單擊“G3”，輸入“+”號；單擊 H3，按下回車鍵即可。其編輯框中顯示的公式將為“=E3+F3+G3+H3”。也可在選中I3單元格后，在英文狀態(tài)下直接從鍵盤上輸入“=E3+F3+G3+H3”，然后按下鍵盤上的回車鍵 Enter,返回,上一頁,下一頁,13,第

11、一節(jié) Excel 運(yùn)算公式,3）醫(yī)療保險(xiǎn)金的公式為 K3=(E3+F3)*2%，操作方法是：選定 K3，輸入“=”號；輸入左括號“（”，單擊 E3 單元格；輸入“+”號，單擊 F3 單元格；輸入右括號“）”，輸入“*2%”；按下鍵盤上的回車鍵。 （4）其他單元格的公式分別為：H3=E3*5%，J3=I3*5%，M3=J3+K3+L3，N3=I3M3,返回,上一頁,下一頁,14,第一節(jié) Excel 運(yùn)算公式,5）選定 F3:N3 單元格區(qū)域，如圖 23 所示；然后將鼠標(biāo)指向 N3 單元格右下角的填充柄，此時(shí)鼠標(biāo)變?yōu)榇执蟮摹?”字狀，按下鼠標(biāo)垂直向下拖動(dòng)到第 14 行，然后釋放鼠標(biāo)；實(shí)現(xiàn)單元格公式

12、的復(fù)制。Excel 自動(dòng)計(jì)算出本表各單元格的數(shù)值。由于在公式中使用了單元格引用，若修改 E 列中某單元格的職務(wù)工資，則引用這一單元格的其他單元格的值也會(huì)隨之改變。同時(shí)，由于第 3 行相關(guān)單元格的公式均是對其他單元格的相對引用，所以用填充柄自動(dòng)復(fù)制的 F4:N14 區(qū)域的單元公式，也會(huì)自動(dòng)調(diào)整為對本行各單元格的引用，從而保證計(jì)算結(jié)果的正確性,返回,上一頁,下一頁,15,第一節(jié) Excel 運(yùn)算公式,四、公式返回的錯(cuò)誤值 在輸入公式時(shí)，有不符合要求的輸入，就無法在 Excel工作表的單元格中顯示正常的運(yùn)算結(jié)果，該單元格中將會(huì)出現(xiàn)提示性的錯(cuò)誤值信息。了解這些錯(cuò)誤信息的含義有助于用戶檢查、修改公式，直

13、到公式正確。表 25 中列出了 Excel 中的錯(cuò)誤信息及其含義。 【案例 23】某公司的產(chǎn)品銷售額如表 26 所示，要求計(jì)算各產(chǎn)品的銷售比重,返回,上一頁,下一頁,16,第一節(jié) Excel 運(yùn)算公式,操作提示】A 產(chǎn)品25 800B 產(chǎn)品36 900C 產(chǎn)品 37 300合計(jì)100 000 （1）在工作表的 A1、A2:B6、C2、E2、G1、G2:K3、G4、G6 單元格中，輸入相關(guān)的文字或數(shù)值；如圖 24 所示。 （2）選定 A1:E1 單元格區(qū)域，單擊格式工具欄的合并居中F1:F6、G1:K1、G4:G5 單元格區(qū)域。 （3）在 C3 單元格中輸入公式“=B3/B6”，即分母為相對引用

14、 B6 單元格；而在 D3 單元格中輸入公式“=B3/$B$6”，即分母為絕對引用 B6 單元格；選定 C3:D3，鼠標(biāo)指向 D3 右下角的填充柄，待變?yōu)椤?”字狀態(tài)時(shí)，按下鼠標(biāo)垂直向下拖動(dòng)到第 6 行，然后釋放鼠標(biāo),返回,上一頁,下一頁,17,第一節(jié) Excel 運(yùn)算公式,4）同樣的，輸入第二種分析方法的公式 H4=H3/K3、H5=H3/$K$3；選定 H4:H5，通過 填充柄水平向右拖動(dòng)，自動(dòng)填充 I4:K5 單元區(qū)域公式。 （5）分析：由于 D3 單元格公式的分母是絕對引用 B6 單元格的數(shù)值，通過自動(dòng)填充方式復(fù)制的 D4:D6 單元格公式中的分母始終引用 B6 單元格的值；D3 單元

15、格公式的分子相對引用B3單元格的值，自動(dòng)填充后的分子將隨單元格而改變；所以公式將變D4=B4/$B$6、 D5=B5/$B$6、D6=B6/$B$6，計(jì)算結(jié)果是正確的。 相反C3單元格的分子、分母均為相對引用；當(dāng)自動(dòng)填充復(fù)制公式時(shí)，引用的單元均會(huì)隨之改變，即其公式將會(huì)變?yōu)?C4=B4/B7、C5=B5/B8、C6=B6/B9,返回,上一頁,下一頁,18,第一節(jié) Excel 運(yùn)算公式,同理，H5 單元公式的分母為絕對引用 K3，所以自動(dòng)填充后的 I5:K5 的公式是正確的；而 H4 單元公式為相對引用 K3 的值，自動(dòng)填充后出現(xiàn)“分母為 0”的錯(cuò)。 （6）選定 A1:K6 單元格區(qū)域，單擊格式工

16、具欄的邊框按鈕中的下拉箭頭，在其中選擇“所有框線”選項(xiàng)，給工作表中的有效表格（即數(shù)據(jù)區(qū)域）加上邊框線。 （7）單擊工作表左上角行列交叉處的全選按鈕，然后通過格式工具欄，設(shè)置字體。（8）選定 B3:B6，按下鍵盤上的 CTRL 鍵，再選定 H3:K3 單元區(qū)域；單擊格式工具欄的千位分隔按鈕、單擊兩次減少小數(shù)位數(shù)按鈕。選定 C3:D6，按下鍵盤上的 CTRL鍵，再選定H4:K5單元區(qū)域；單擊格式工具欄的百分比按鈕,返回,上一頁,19,第二節(jié) Excel 函數(shù),在 Excel中，函數(shù)實(shí)際上是一個(gè)預(yù)先定義的特定計(jì)算公式。按照這個(gè)特定的計(jì)算公式對一個(gè)或多個(gè)參數(shù)進(jìn)行計(jì)算，并得出一個(gè)或多個(gè)計(jì)算結(jié)果，叫做函數(shù)

17、值,返回,下一頁,20,第二節(jié) Excel 函數(shù),一、函數(shù)的使用與構(gòu)成 1. 函數(shù)的使用。Excel 提供了財(cái)務(wù)函數(shù)、日期與時(shí)間函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫函數(shù)、文本函數(shù)、邏輯函數(shù)、信息函數(shù)九大類兩百多個(gè)函數(shù)。使用這些函數(shù)不僅可以完成許多復(fù)雜的計(jì)算，而且還可以簡化公式的繁雜程度。 【案例 24】某公司生產(chǎn)經(jīng)營 46 種產(chǎn)品，2009 年各季度銷售收入如表 27 所示。要求用函數(shù)計(jì)算各季度、各產(chǎn)品的合計(jì)收入（其他指標(biāo)以后介紹,返回,上一頁,下一頁,21,第二節(jié) Excel 函數(shù),1）設(shè)計(jì)表格。錄入 A 列和第 2 行的相關(guān)文字；輸入 B3:G6 的已知金額；合并 A1:

18、L1單元格區(qū)域；設(shè)置字體字號。如圖 25 所示。 （2）常用工具欄求和法。操作方法如下： a. 選定 H3 單元格，單擊常用工具欄的自動(dòng)求和按鈕，或單擊該按鈕邊的下拉箭頭，在彈出的下拉菜單中，選擇“求和”命令。 b. 此時(shí)該單元格中將反白顯示求和公式“=SUM(B3:G3)”，且 B3:G3 單元格將以閃爍的虛框顯示該公式所引用的單元格區(qū)域，同時(shí)編輯框中顯示其公式“=SUM(B3:G3,返回,上一頁,下一頁,22,第二節(jié) Excel 函數(shù),c. 由于公式無誤，所以再次單擊自動(dòng)求和按鈕，以確認(rèn)并鎖定該公式；也可按下鍵盤上的回車鍵 ENTER，或編輯框前的輸入按鈕鎖定公式。 若雙擊自動(dòng)求和按鈕，可

19、快速實(shí)現(xiàn)上述求和公式的輸入。 d. 選定 H3 單元格，鼠標(biāo)指向右下角的填充柄，待變?yōu)椤?”字狀態(tài)時(shí)，按下鼠標(biāo)向下拖動(dòng)至 H6 單元格，以實(shí)現(xiàn)自動(dòng)復(fù)制公式。 （3）向?qū)蠛头?。操作方法如下?a. 選定 B7 單元格，單擊編輯框前的插入函數(shù)按鈕，或單擊自動(dòng)求和鈕邊的下拉箭頭選擇“其他函數(shù)”命令，或選擇“插入/函數(shù)”菜單命令，均會(huì)彈出“插入函數(shù)”界面，如圖 26 所示,返回,上一頁,下一頁,23,第二節(jié) Excel 函數(shù),b. 單擊該界面的類別后的下拉箭頭，在彈出的下拉框中選擇“數(shù)學(xué)與三角函數(shù)”；再通過右部的滾動(dòng)條，找到求和函數(shù)“SUM”；選定“SUM”函數(shù)后單擊“確定”按鈕，或雙擊該函數(shù)，進(jìn)入

20、“函數(shù)參數(shù)”界面，如圖 27 所示。 c. 在函數(shù)參數(shù)界面的 NUMBER1 參數(shù)后部的輸入框中輸入“B3:B6”；或者單擊后部的引用按鈕，用鼠標(biāo)選擇“B3:B6”單元格區(qū)域，再單擊引用按鈕回到函數(shù)參數(shù)界面，實(shí)現(xiàn)單元格的引用。 d. 單擊“確定”按鈕回到工作表界面，此時(shí) B7 單元格中將顯示計(jì)算結(jié)果，編輯框中將顯示函數(shù)公式“=SUM(B3:B6,返回,上一頁,下一頁,24,第二節(jié) Excel 函數(shù),4）直接輸入法。 2. Excel函數(shù)的構(gòu)成。函數(shù)是由函數(shù)名及參數(shù)構(gòu)成，函數(shù)名說明函數(shù)所要進(jìn)行的運(yùn)算，而參數(shù)是指定函數(shù)使用的數(shù)值或單元格引用。函數(shù)公式如下：=函數(shù)（參數(shù) 1,參數(shù) 2,參數(shù) 3,）例

21、如，公式“=SUM(C3:C6)”，式中 SUM 為函數(shù)名，是求和。C3:C6 單元格區(qū)域是參數(shù)，該公式表示為對 C3 到 C6 單元格區(qū)域內(nèi)的數(shù)據(jù)求和。使用函數(shù)時(shí)，函數(shù)的參數(shù)必須滿足以下規(guī)定：函數(shù)參數(shù)應(yīng)用括號括起來，且括號前后不能有空格；函數(shù)中參數(shù)多于一個(gè)時(shí)，必須用逗號分隔他們；參數(shù)除了是單元格的引用外，還可以是數(shù)、文本、邏輯值、數(shù)值、公式或其他函數(shù)；給定的參數(shù)必須能產(chǎn)生一個(gè)有效的值,返回,上一頁,下一頁,25,第二節(jié) Excel 函數(shù),二、常用函數(shù)的使用 1.求和函數(shù) SUM。函數(shù)公式為：= SUM(Number1,Number2,) 式中，Number1,Number2,為 130 個(gè)需

22、要求和的參數(shù)。 2.平均數(shù)函數(shù) AVERAGE。函數(shù)公式為： =AVERAGE(Number1,Number2,) 3.計(jì)數(shù)函數(shù) COUNT。函數(shù)公式為：= COUNT(Value1,Value2,) Value1, Value2,表示包含或引用各種類型數(shù)據(jù)的參數(shù)（130 個(gè)），但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算,返回,上一頁,下一頁,26,第二節(jié) Excel 函數(shù),4. 條件函數(shù) IF。其功能是判斷一個(gè)條件是否滿足，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值。函數(shù)公式為： =IF(Logical_test,Value_if_true,Value_if_false) 式中，Logical_test

23、 表示計(jì)算結(jié)果為 TRUE（邏輯真值）或 FALSE（邏輯假值）的任意值或表達(dá)式；Value_if_true 表示為 TRUE 時(shí)的返回值；Value_if_false 表示為 FALSE 時(shí)的返回值。 IF 在進(jìn)行多次判斷中，最多可以嵌套七層,返回,上一頁,下一頁,27,第二節(jié) Excel 函數(shù),案例 25】承案例 22，計(jì)算交通補(bǔ)貼：經(jīng)理為 300 元、科員為 150 元。 【操作提示】 （1）選中 G3 單元格；在其中輸入公式“=IF(D3=經(jīng)理,300,150)”。該公式的含義為： D3 單元格中如果是文本“經(jīng)理”，就返回其邏輯真值 300，否則就返回假值 150。其結(jié)果如圖28 所示

24、。 （2）拖動(dòng) G3 單元格填充柄向下填充公式即可,返回,上一頁,下一頁,28,第二節(jié) Excel 函數(shù),5. 求最大值函數(shù) MAX。其功能是返回一組數(shù)值中的最大值。函數(shù)公式為： =MAX(Number1,Number2,) 式中，Number1,Number2,，表示要從中找出最大值的 130 個(gè)數(shù)字參數(shù)。 6.求最小值函數(shù) MIN。其功能是返回一組數(shù)值中的最小值。函數(shù)公式為： =MIN(Number1,Number2,) 【案例 26】承前案例 24，計(jì)算各季品種數(shù)、平均銷售額、各季最大銷售額、最小銷售額、判斷哪些產(chǎn)品全年銷售額超過了 2 000 萬元,返回,上一頁,下一頁,29,第二節(jié)

25、Excel 函數(shù),操作提示】 （1）平均數(shù)。方法是：選定 I3 單元格，輸入“=AVERAGE(B3:E3)”；選定 I4 單元格，輸入“=AVERAGE(B4:F4)”；選定 I5 單元格，輸入“=AVERAGE(B5:G5)”；選定 I6 單元格，輸入“=AVERAGE(B6:G6)”。 （2）各季最大銷售額。輸入其函數(shù)公式：J3=MAX(B3:E3)、J4= MAX(B4:F4)、 J5= MAX(B5:G5)、J6= MAX(B6:G6)。 （3）最小銷售額。K3=MIN(B3:E3)、K4 =MIN(B4:F4)、K5 =MIN(B5:G5)、K6 =MIN(B6:G6,返回,上一頁

26、,下一頁,30,第二節(jié) Excel 函數(shù),4）各季經(jīng)營品種。輸入公式 L3=COUNT(B3:G3)；然后自動(dòng)填充 L4:L7 單元區(qū)域公式。以上的計(jì)算均可使用向?qū)Х?，選擇“統(tǒng)計(jì)”類中的對應(yīng)函數(shù)進(jìn)行計(jì)算。 （5）是否超 2 000 萬元判斷。輸入公式 B8=IF(B72000,是,否)；然后自動(dòng)填充 C8:G8單元區(qū)域公式。 7. 取絕對值函數(shù) ABS。其功能是取表達(dá)式的絕對值。函數(shù)公式為：=ABS(Number)式中，Number 表示需要計(jì)算其絕對值的實(shí)數(shù)。 8. 條件計(jì)數(shù) COUNTIF。其功能是計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格數(shù)目。函數(shù)公式為： =COUNTIF(Range,Crit

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

28、定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本；Sum_range 表示需要求和的實(shí)際單元格,返回,上一頁,下一頁,32,第二節(jié) Excel 函數(shù),11. 排位函數(shù) RANK。其函數(shù)公式如下： =RANK(Number,Ref,Order) 式中，Number 表示需要排位的數(shù)字；Ref 表示包含一組數(shù)字的數(shù)組或引用，即需排位的全部總體，非數(shù)值型參數(shù)將被忽略；Order 表示排位方式，如果 Order 為 0 或省略則按降序排列，否則按升序排列。 【案例 27】承案例 22 計(jì)算個(gè)人所得稅。個(gè)人所得稅的起征點(diǎn)和稅率如表 28 所示。 工資、薪金所得稅的計(jì)算公式是： 應(yīng)納個(gè)人所得

29、稅稅額 =應(yīng)納稅所得額適用稅率速算扣除數(shù),返回,上一頁,下一頁,33,第二節(jié) Excel 函數(shù),操作提示】 （1）選中L3 單元格；在單元格中輸入公式（這些字符均應(yīng)在英文輸入法下 =IF(I3J3K320000,0,IF(I3J3K32000500,0.05*(I3J3K32000),IF(I3J3K32000 2000,0.1*(I3J3K32000)25,IF(I3J3K320005000,0.15*(I3J3K32000)125,0.2*(I3J 3K32000)375,返回,上一頁,下一頁,34,第二節(jié) Excel 函數(shù),公式含義為：按國家規(guī)定，如果應(yīng)納稅稅額員工的應(yīng)發(fā)合計(jì)法定免征項(xiàng)(

30、養(yǎng)老保險(xiǎn)金+醫(yī)療保險(xiǎn)金) 起征點(diǎn)(現(xiàn)行為 2000 元)小于 0，則個(gè)人所得稅為 0；否則如果應(yīng)納稅稅額在 0500 元，為應(yīng)納稅稅額5%；否則如果應(yīng)納稅稅額在 5002 000 元，為應(yīng)納稅稅額10%25元的速算扣除數(shù)，以此類推；由于該公司沒有人的收入超過 20 000 元，所以，條件函數(shù) IF 只判斷到 5 00020 000 即可。 （2）拖動(dòng) L3 單元格的填充柄向下填充即可，結(jié)果如圖 29 所示,返回,上一頁,35,第三節(jié)數(shù)據(jù)排序與分類匯總,一、數(shù)據(jù)排序 1.排序的概念。排序是將數(shù)據(jù)區(qū)域中的記錄，按其某字段名的數(shù)據(jù)值由小到大（升序）或由大到小（降序）的順序進(jìn)行排列，用來排序的字段或條

31、件稱為排序鍵。排序后，將會(huì)對整頓秩序各工作表或選定區(qū)域的內(nèi)容進(jìn)行調(diào)整。 2.排序操作。排序的操作方法有以下幾種。 （1）用工具按鈕排序。若只按一個(gè)排序標(biāo)志進(jìn)行排序， 用常用工具欄上的排序按鈕更為方便。其操作是：在數(shù)據(jù)區(qū)域中選定要排序的單元區(qū)域。根據(jù)需要單擊常用工具欄上的“降序”或“升序”，這時(shí)會(huì)彈出排序的警告對話框（如圖 210 所示,返回,下一頁,36,第三節(jié)數(shù)據(jù)排序與分類匯總,在“排序警告”對話框中一定注意，選擇“擴(kuò)展選定區(qū)域”以某個(gè)字段進(jìn)行排序時(shí)，對應(yīng)的列會(huì)隨排序位置的變化而變化。而“以當(dāng)前選定區(qū)域排序”，其他的列將不參與排序。Excel 中進(jìn)行升序排序時(shí)，數(shù)字和日期按由小到大排列；字母

32、按 AZ、az 進(jìn)行排列；漢字要選轉(zhuǎn)化為漢語拼音，再按字符比較規(guī)則進(jìn)行比較。 （2）用菜單命令進(jìn)行排序。使用“數(shù)據(jù)排序”命令，可以按多個(gè)排序鍵來進(jìn)行排序，還可以按列、或者按行來排序。但排序鍵最多不能超過三個(gè)，分別稱為主要關(guān)鍵字、次要關(guān)鍵字和第三關(guān)鍵字；當(dāng)主關(guān)鍵字值相同時(shí)，就根據(jù)次關(guān)鍵字排序，如果次關(guān)鍵字也一致，則根據(jù)第三關(guān)鍵字來排序,返回,上一頁,下一頁,37,第三節(jié)數(shù)據(jù)排序與分類匯總,案例 28】承案例 27，進(jìn)行工資表的排序。 【操作提示】 a.選擇數(shù)據(jù)區(qū)域中一個(gè)單元格；選擇“數(shù)據(jù)排序”命令，打開如圖211所示的“排序”對話框。 b.單擊對話框中“主要關(guān)鍵字”列表框的向下箭頭，選取主要關(guān)鍵

33、字。若需要還可以選取次要關(guān)鍵字和第三關(guān)鍵字，并在“升序”、“降序”上作出相應(yīng)的選擇。 c.單擊“確定”按鈕，按職務(wù)工資的升序排序，排序后圖 212 中人員編號的順序變化。 如果要按行進(jìn)行排序，或者對文本要進(jìn)行“按字母排序”或“按筆畫排序”，或者排序時(shí)字母要區(qū)分大小寫等，請?jiān)趫D 211 所示的“排序”對話框選擇“選項(xiàng)”，打開“選項(xiàng)”對話框并在其中進(jìn)行選擇,返回,上一頁,下一頁,38,第三節(jié)數(shù)據(jù)排序與分類匯總,3.返回排序前的表格。當(dāng)反復(fù)對表格進(jìn)行排序后，表格的原有次序被打亂，若需返回原有表格次序，可使用常用工具欄的撤銷或恢復(fù)按鈕。但是排序的次數(shù)較多，或排序后還進(jìn)行了其他的操作，此時(shí)恢復(fù)就不容易了

34、。所以，在排序前最好是給表格增加一個(gè)表示原有表格次序的字段，如“序號”、“編號”、“NO.”等字段名，置于 A 列、或表格的有效數(shù)據(jù)的最后一列，對原表格的所有記錄（即每行）進(jìn)行編號。這樣，排序后要返回原表格的次序只需對該字段進(jìn)行重新排序即可,返回,上一頁,下一頁,39,第三節(jié)數(shù)據(jù)排序與分類匯總,二、分類匯總 分類匯總就是將很多數(shù)據(jù)按同類別歸類放到一起，然后再進(jìn)行同類數(shù)據(jù)求和、計(jì)數(shù)、求平均等匯總運(yùn)算。通過分類匯總，得到需要的統(tǒng)計(jì)信息，諸如商品銷售按部門、按人員匯總等重要的統(tǒng)計(jì)信息。在分類匯總前必須對工作表指定字段進(jìn)行排序；如果沒有排序，匯總結(jié)果很混亂，無法達(dá)到真正的分類匯總的目的。分類匯總的形式

35、有單字段分類匯總和多字段分類匯總。 1.單字段分類匯總。Excel能夠快速的以某一個(gè)字段為分類項(xiàng)，對數(shù)據(jù)區(qū)域中其他字段的數(shù)值進(jìn)行各種匯總統(tǒng)計(jì)。分類匯總時(shí)將分類項(xiàng)稱為分類字段；將其他需統(tǒng)計(jì)值的字段稱為選定匯總項(xiàng)，匯總項(xiàng)可以多選；匯總的方式可以是求和、乘積、最大值、最小值、計(jì)數(shù)、計(jì)數(shù)值、標(biāo)準(zhǔn)差、方差等,返回,上一頁,下一頁,40,第三節(jié)數(shù)據(jù)排序與分類匯總,案例 29】承案例 27，對遠(yuǎn)華公司 2008 年 8 月工資表，按“部門”分類匯總顯示實(shí)發(fā)工資的部門和人數(shù)。 【操作提示】 （1）選取數(shù)據(jù)區(qū)域中的任一單元格,選擇“數(shù)據(jù)分類匯總”命令，打開如圖 213 所示的“分類匯總”對話框。 （2）在“分類

36、字段”中選擇“部門名稱”；在“匯總方式”中選擇“求和”；在“選定匯總項(xiàng)”中選擇“實(shí)發(fā)合計(jì)”；單擊“確定”按鈕，完成分類匯總操作。 2.多字段分類匯總。若需要從數(shù)據(jù)區(qū)域中得到更多的信息，以便于分析數(shù)據(jù)，則多次執(zhí)行匯總功能即可。即選按某一個(gè)字段進(jìn)行分類匯總，然后再按另一個(gè)字段進(jìn)行分類匯總，但要在圖 213 的分類匯總對話框中取消“替換當(dāng)前分類匯總”選項(xiàng),返回,上一頁,下一頁,41,第三節(jié)數(shù)據(jù)排序與分類匯總,操作提示】 在上例的基礎(chǔ)上，還需按“部門”分類匯總“部門人數(shù)”，繼續(xù)其操作如下： （1）選取數(shù)據(jù)區(qū)域中的任一單元格，選擇“數(shù)據(jù)分類匯總”，打開“分類匯總”對話框。 （2）在“分類字段”中選擇“部

37、門名稱”；在“匯總方式”中選擇“計(jì)數(shù)”；在“選定匯總項(xiàng)”中選擇“姓名”，圖 214 。 （3）清除“替換當(dāng)前分類匯總”選項(xiàng)，單擊“確定”按鈕。 3. 大綱功能的使用。Excel 在分類匯總的同時(shí)，也建立了大綱圖標(biāo)于工作表的左上角,返回,上一頁,下一頁,42,第三節(jié)數(shù)據(jù)排序與分類匯總,單擊“1”則只顯示“總計(jì)”一行，單擊“3”則顯示全部數(shù)據(jù)及匯總結(jié)果。 4. 清除分類匯總。清除分類匯總的方法很簡單，只要在“分類匯總”對話框中單擊“全部刪除”按鈕，就能將分類匯總刪掉。在匯總的要求較簡單時(shí)，分類匯總功能是得力的工具。但如果數(shù)據(jù)區(qū)域較龐大，并且匯總要求較為復(fù)雜時(shí)，更好的分類匯總工具是數(shù)據(jù)透視表、數(shù)據(jù)透

38、視圖，詳見本書后面章節(jié)的介紹,返回,上一頁,43,第四節(jié)數(shù)據(jù)篩選,使用Excel 工作表時(shí)，常常會(huì)根據(jù)需要去查找所需的某項(xiàng)信息，數(shù)據(jù)篩選能方便快速的找到所需的信息。執(zhí)行數(shù)據(jù)篩選后只顯示出包含符合某一值或一組條件的行，而其他行將會(huì)被隱藏。Excel 2003 提供了自動(dòng)篩選和高級篩選功能,返回,下一頁,44,第四節(jié)數(shù)據(jù)篩選,一、自動(dòng)篩選 使用自動(dòng)篩選可以快速而方便地查找和使用單元格區(qū)域或工作表的所需信息，隱藏那些不希望顯示的行。 1. 使用自動(dòng)篩選列表進(jìn)行篩選。篩選條件比較簡單時(shí)，通過“自動(dòng)篩選”命令產(chǎn)生自動(dòng)篩選器，該篩選器下部列出了某一個(gè)字段下所有可供篩選的子項(xiàng)列表，選擇列表項(xiàng)即可篩選。 【案

39、例 210】從遠(yuǎn)華公司 2008 年 8 月工資表中篩選顯示“銷售部”職工的工資情況。 【操作提示】 （1）選取數(shù)據(jù)區(qū)域中的任一單元格，選擇“數(shù)據(jù)篩選”命令中的“自動(dòng)篩選”子命令，這時(shí)可以看到各字段邊的篩選器（帶下拉箭頭的標(biāo)記），如圖 215 所示,返回,上一頁,下一頁,45,第四節(jié)數(shù)據(jù)篩選,2）單擊要進(jìn)行篩選列（字段）的篩選箭頭（篩選器），下拉出一個(gè)清單框，從中選擇“銷售部”。從圖 216 中可以看到，除了“銷售部”的職工外，其他所有行（記錄）的數(shù)據(jù)都在數(shù)據(jù)區(qū)域中被隱藏了（表中的行號不連續(xù)、有一部分不可見，表明未顯示行仍然存在，因不符合篩選條件而被隱藏）。 （3）若要取消所選子項(xiàng)的篩選，而按

40、另一子項(xiàng)進(jìn)行篩選，要先通過“篩選器”清單上的“全部”命令恢復(fù)所有數(shù)據(jù)，然后再從列表中選另一子項(xiàng)進(jìn)行篩選。如查看了“銷售部”職工的情況后，單擊“部門名稱”字段邊的篩選器，選“全部”命令，則所有隱藏行（記錄）均全部顯示；然后再單擊篩選器，從列表中選“采購部”，則除采購部職工外的其他記錄被全部隱藏,返回,上一頁,下一頁,46,第四節(jié)數(shù)據(jù)篩選,4）不同字段邊的篩選器之間是“且”（即“與”）的關(guān)系。如：在部門名稱篩選器中選擇“采購部”，又在職務(wù)篩選器中選擇“經(jīng)理”，則只顯示采購部經(jīng)理的相關(guān)記錄，除此之外的其他記錄將被全部隱藏。 （5）取消篩選操作。從“數(shù)據(jù)”菜單中再次選擇“篩選”命令后，單擊“自動(dòng)篩選”

41、子命令，則工作表中的篩選箭頭消失，篩選操作被取消。 2. 自定義篩選。在進(jìn)行自動(dòng)篩選時(shí)，可對某一字段設(shè)置一些特殊的篩選條件，如大于、等于、小于、不大于、始于、止于、包含、不包含等條件；可以進(jìn)行兩個(gè)條件的設(shè)定，條件之間的關(guān)系，可以是“與”、也可以是“或”；這些條件的值，既可以從列表中選擇、也可輸入特定值，還可使用通配符“*”或“？”，其中“*”表示任意多個(gè)字符、“？”表示單個(gè)字符,返回,上一頁,下一頁,47,第四節(jié)數(shù)據(jù)篩選,案例 211】從遠(yuǎn)華公司 2008 年 8 月工資表中，篩選出代扣稅大于 0 而小于 200 元的職工情況。 【操作提示】 （1）選取數(shù)據(jù)區(qū)域中的任一單元格，選擇“數(shù)據(jù)篩選/

42、自動(dòng)篩選”子命令，則工作表的所有字段邊均出現(xiàn)篩選器；若原已進(jìn)行了篩選而沒有取消其篩選操作，則應(yīng)保證所有已使用的篩選器均已選擇了“全部”命令，即工作表的各行（記錄）均沒有被隱藏。 （2）單擊代扣稅篩選器，在下拉清單框中選擇“(自定義.)”項(xiàng)，如圖 215 所示，進(jìn)入圖 217 所示的“自定義自動(dòng)篩選方式”界面,返回,上一頁,下一頁,48,第四節(jié)數(shù)據(jù)篩選,3）在該對話框中選擇“大于”，右欄選擇“0”（也可直接輸入）；選擇中部為“與”的關(guān)系；選擇第二個(gè)條件為“小于”，右欄輸入“200”（本例不能直接選擇）。 （4）單擊“確定”，完成自定義自動(dòng)篩選，結(jié)果將會(huì)只顯示該公司代扣稅大于0且小于200 元的職

43、工記錄情況。 【案例 212】從遠(yuǎn)華公司 2008 年 8 月工資表中，篩選顯示所有姓“陳”的職工。 【操作提示】 （1）對工作表使用“自動(dòng)篩選”命令（或保證所有已使用的篩選器均已單擊了“全部”命令）。 （2）單擊姓名篩選器，從下拉列表中選擇“自定義”項(xiàng)，進(jìn)入圖 218 所示的“自定義自動(dòng)篩選方式”界面,返回,上一頁,下一頁,49,第四節(jié)數(shù)據(jù)篩選,3）在其中選擇“始于”條件，在右部輸入“陳*”；單擊“確定”按鈕后，將只顯示“陳”姓職工的信息，其他記錄將被隱藏。需說明的是，若需篩選姓名中最后一個(gè)字為“紅”的職工，應(yīng)選擇“止于”條件；若要選擇姓名中的任意一個(gè)字為“兵”的職工，則應(yīng)選擇“包含”條件。

44、 3. 前 10個(gè)篩選。需統(tǒng)計(jì)顯示某個(gè)字段中相對值靠前、或靠后的記錄時(shí)，可使用自動(dòng)篩選器提供的“前 10 個(gè)”功能,返回,上一頁,下一頁,50,第四節(jié)數(shù)據(jù)篩選,案例 213】從遠(yuǎn)華公司 2008 年 8 月工資表中，篩選顯示職務(wù)工資處于后 3 位的職工。 （1）對工作表使用“自動(dòng)篩選”命令（或保證所有已使用的篩選器均已單擊了“全部”命令）。 （2）單擊職務(wù)工資篩選器，選擇“前 10 個(gè)”命令進(jìn)入“自動(dòng)篩選前 10 個(gè)”界面，如圖219 所示。 （3）選擇“最小”條件，輸入值為“3”，選擇“項(xiàng)”為統(tǒng)計(jì)數(shù)量；單擊“確定”按鈕即可。 需說明的是，在條件中還可選擇“最大”；統(tǒng)計(jì)數(shù)量中還可選擇“百分比”

45、為計(jì)量依據(jù)，如圖 220 所示,返回,上一頁,下一頁,51,第四節(jié)數(shù)據(jù)篩選,二、高級篩選 與自動(dòng)篩選相比，高級篩選可以選用更多的條件，并且可以不使用邏輯運(yùn)算符而將多個(gè)篩選條件加以邏輯運(yùn)算；高級篩選還可將篩選結(jié)果從數(shù)據(jù)區(qū)域中抽取出來，復(fù)制到當(dāng)前工作表的指定位置上。要使用高級篩選，需要有 3 個(gè)區(qū)域：數(shù)據(jù)區(qū)域，即需要進(jìn)行篩選的數(shù)據(jù)區(qū)域或整個(gè)工作表。條件區(qū)域，即用來指定篩選數(shù)據(jù)時(shí)必須滿足的條件。顯示篩選結(jié)果區(qū)域，即用于存放滿足篩選條件結(jié)果的區(qū)域；可以是原有區(qū)域、也可以是其他指定的區(qū)域,返回,上一頁,下一頁,52,第四節(jié)數(shù)據(jù)篩選,案例 214】根據(jù)遠(yuǎn)華公司 2008 年 8 月工資表，篩選出“醫(yī)療保險(xiǎn)大于 40，且代扣稅大于 0；或養(yǎng)老保險(xiǎn)大于 130 的職工”。 【操作提示】 （1）建立數(shù)據(jù)區(qū)域，如圖 221 的 A1: