Excel財務(wù)與會計應(yīng)用精粹ppt課件

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

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

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

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

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

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

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

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

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

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

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

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

13、到公式正確。表 25 中列出了 Excel 中的錯誤信息及其含義。 【案例 23】某公司的產(chǎn)品銷售額如表 26 所示，要求計算各產(chǎn)品的銷售比重,返回,上一頁,下一頁,16,第一節(jié) Excel 運算公式,操作提示】A 產(chǎn)品25 800B 產(chǎn)品36 900C 產(chǎn)品 37 300合計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)時，按下鼠標(biāo)垂直向下拖動到第 6 行，然后釋放鼠標(biāo),返回,上一頁,下一頁,17,第一節(jié) Excel 運算公式,4）同樣的，輸入第二種分析方法的公式 H4=H3/K3、H5=H3/$K$3；選定 H4:H5，通過 填充柄水平向右拖動，自動填充 I4:K5 單元區(qū)域公式。 （5）分析：由于 D3 單元格公式的分母是絕對引用 B6 單元格的數(shù)值，通過自動填充方式復(fù)制的 D4:D6 單元格公式中的分母始終引用 B6 單元格的值；D3 單元

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

16、具欄的邊框按鈕中的下拉箭頭，在其中選擇“所有框線”選項，給工作表中的有效表格（即數(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ù)實際上是一個預(yù)先定義的特定計算公式。按照這個特定的計算公式對一個或多個參數(shù)進行計算，并得出一個或多個計算結(jié)果，叫做函數(shù)

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

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

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

20、“函數(shù)參數(shù)”界面，如圖 27 所示。 c. 在函數(shù)參數(shù)界面的 NUMBER1 參數(shù)后部的輸入框中輸入“B3:B6”；或者單擊后部的引用按鈕，用鼠標(biāo)選擇“B3:B6”單元格區(qū)域，再單擊引用按鈕回到函數(shù)參數(shù)界面，實現(xiàn)單元格的引用。 d. 單擊“確定”按鈕回到工作表界面，此時 B7 單元格中將顯示計算結(jié)果，編輯框中將顯示函數(shù)公式“=SUM(B3:B6,返回,上一頁,下一頁,24,第二節(jié) Excel 函數(shù),4）直接輸入法。 2. Excel函數(shù)的構(gòu)成。函數(shù)是由函數(shù)名及參數(shù)構(gòu)成，函數(shù)名說明函數(shù)所要進行的運算，而參數(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ù)必須滿足以下規(guī)定：函數(shù)參數(shù)應(yīng)用括號括起來，且括號前后不能有空格；函數(shù)中參數(shù)多于一個時，必須用逗號分隔他們；參數(shù)除了是單元格的引用外，還可以是數(shù)、文本、邏輯值、數(shù)值、公式或其他函數(shù)；給定的參數(shù)必須能產(chǎn)生一個有效的值,返回,上一頁,下一頁,25,第二節(jié) Excel 函數(shù),二、常用函數(shù)的使用 1.求和函數(shù) SUM。函數(shù)公式為：= SUM(Number1,Number2,) 式中，Number1,Number2,為 130 個需

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

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

24、。 （2）拖動 G3 單元格填充柄向下填充公式即可,返回,上一頁,下一頁,28,第二節(jié) Excel 函數(shù),5. 求最大值函數(shù) MAX。其功能是返回一組數(shù)值中的最大值。函數(shù)公式為： =MAX(Number1,Number2,) 式中，Number1,Number2,，表示要從中找出最大值的 130 個數(shù)字參數(shù)。 6.求最小值函數(shù) MIN。其功能是返回一組數(shù)值中的最小值。函數(shù)公式為： =MIN(Number1,Number2,) 【案例 26】承前案例 24，計算各季品種數(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)；然后自動填充 L4:L7 單元區(qū)域公式。以上的計算均可使用向?qū)Х?，選擇“統(tǒng)計”類中的對應(yīng)函數(shù)進行計算。 （5）是否超 2 000 萬元判斷。輸入公式 B8=IF(B72000,是,否)；然后自動填充 C8:G8單元區(qū)域公式。 7. 取絕對值函數(shù) ABS。其功能是取表達式的絕對值。函數(shù)公式為：=ABS(Number)式中，Number 表示需要計算其絕對值的實數(shù)。 8. 條件計數(shù) COUNTIF。其功能是計算某個區(qū)域中滿足給定條件的單元格數(shù)目。函數(shù)公式為： =COUNTIF(Range,Crit

27、eria,返回,上一頁,下一頁,31,第二節(jié) Excel 函數(shù),式中：Range 表示需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域；Criteria 表示確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。 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ù)字、表達式或文本；Sum_range 表示需要求和的實際單元格,返回,上一頁,下一頁,32,第二節(jié) Excel 函數(shù),11. 排位函數(shù) RANK。其函數(shù)公式如下： =RANK(Number,Ref,Order) 式中，Number 表示需要排位的數(shù)字；Ref 表示包含一組數(shù)字的數(shù)組或引用，即需排位的全部總體，非數(shù)值型參數(shù)將被忽略；Order 表示排位方式，如果 Order 為 0 或省略則按降序排列，否則按升序排列。 【案例 27】承案例 22 計算個人所得稅。個人所得稅的起征點和稅率如表 28 所示。 工資、薪金所得稅的計算公式是： 應(yīng)納個人所得

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ā)合計法定免征項(

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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