Excel財務與會計應用培訓教材.ppt

第二章 Excel 的數據處理,第一節(jié)Excel 運算公式 第二節(jié)Excel 函數 第三節(jié)數據排序與分類匯總 第四節(jié)數據篩選,第一節(jié) Excel 運算公式,一、建立與編輯數據區(qū)域 1.數據區(qū)域的建立。數據區(qū)域是 Excel 工作表中進行數據處理的單元區(qū)域。Excel 作為一個電子表格軟件，本身具有一個完備的表結構，通常可將數據區(qū)域中的列稱為字段，將行稱為記錄。建立數據區(qū)域的要求是最上面一行或兩行必須包含字段名（數據區(qū)域的列名稱），數據區(qū)域中應盡量不要出現空行或空列，并保證每列中包含的是同類的信息。建立數據區(qū)域的方法是先建立一個表格，再一條一條地錄入數據，既可在工作表中錄入，也可通過記錄單錄入。,第一節(jié) Excel 運算公式,【案例 21】遠華公司的人員、部門及職務工資等數據，如表 21 所示。除此之外，還有的工資表項目是：效益工資、交通補貼、住房補貼、應發(fā)合計、養(yǎng)老保險金、醫(yī)療保險金、代扣稅、扣款合計、實發(fā)合計。要求建立數據區(qū)域。,第一節(jié) Excel 運算公式,【操作提示】 （1）在 Excel 工作表的最上面一行或兩行輸入字段名的內容，如人員編號、姓名、部門名稱、養(yǎng)老保險金等，如圖 21 第 2 行所示。 （2）移動到數據區(qū)域下第一個空行（即圖 21 第 3 行），選取一個單元格。 （3）選擇“數據記錄單”菜單命令，打開如圖 22 所示的記錄單對話框，左邊是建立的數據區(qū)域的各字段，在其編輯框中錄入相應的數據。 （4）一條記錄錄入完畢后，單擊“新建”按鈕后再錄入第二條記錄。錄入完畢后單擊“關閉”按鈕，即可完成數據區(qū)域的建立。,第一節(jié) Excel 運算公式,2.數據區(qū)域的編輯。錄入的數據有誤，可打開數據區(qū)域所在的工作表，選擇要修改的單元格，可以在編輯欄中直接修改，也可以在“記錄單”中對某條記錄進行修改。要刪除數據區(qū)域中的行（記錄），可直接在工作表中選定要刪除的行，然后右擊選擇“刪除”命令即可。也可在“記錄單”中，通過“上一條”、“下一條”或“條件”按鈕，選出要刪除的行（即要刪除的一條記錄），單擊“刪除”按鈕，即可刪除所選的行。必須注意，用記錄單上的“刪除”按鈕刪除的行是不能恢復的。要刪除數據區(qū)域中的列，則應在工作表中選擇所要刪除的列，用“編輯”菜單或者列的“快捷菜單”中的“刪除”命令進行。,第一節(jié) Excel 運算公式,二、公式構成與運算類型 工作表中的數據往往需要進行大量的計算，可以根據單元格之間的鉤稽關系，使用一些簡單的運算公式或較為復雜的函數，Excel 將自動完成這些運算。 公式的構成。Excel規(guī)定，公式必須以等號開始，是由單元格引用、運算符、數值、字符、函數等組成的能夠進行計算的式子。 2. Excel 中運算符有四類，即算術運算、字符運算、比較運算和引用運算。 （1）算術運算。算術運算是指加、減、乘、除等運算。運算的優(yōu)先次序為：括號指數乘除加減。同級運算符按從左到右的順序進行。算術運算符如表 22 所示。,第一節(jié) Excel 運算公式,（2）字符運算。字符運算的運算符是“&”，它表示連接、合并運算兩邊的字符。加入的文本字符要使用雙引號，且應是英文狀態(tài)下的雙引號。 （3）比較運算。比較運算是用來比較兩個數值的關系，并產生邏輯值 TRUL（邏輯真值）和 FALSE（邏輯假值）。比較運算符如表 23 所示。 （4）引用運算。引用運算可以將單元格區(qū)域合并運算,引用運算一般都要涉及到單元格引用?！皢卧褚谩笔侵腹街休斎雴卧竦刂窌r，表示該單元格中的內容參加運算。當引用的單元格中的數據發(fā)生了變化時，公式則自動重新進行計算并自動更新計算結果，這種方式下，可以隨時觀察到數據之間的相互關系。表 24 所示。,第一節(jié) Excel 運算公式,三、公式中使用引用 引用的作用在于標識工作表上的單元格或單元格區(qū)域，并告知 Excel 在何處查找公式中所使用的數值或數據。單元格地址是用列標和行號來標示某一單元格的位置，所以若要引用某個單元格，則在公式中輸入單元格的行號、列標，即單元格的地址即可。這樣在被引用單元格的數據變化時，公式所在單元格的數據也隨之變化。 1. 單元引用的類型。在 Excel 中有以下三種基本的單元格引用類型。,第一節(jié) Excel 運算公式,（1）相對引用。是指在公式中直接對單元格的行號、列標這種相對位置進行的引用。如果公式所有的單元格位置調整，被引用單元格的地址也相應變化；如果多行或多列復制或填充公式，引用的單元格地址也隨之改變。例如：E1 單元格中有公式“=A1+B1C1”；向后拖動 E1 單元格填充柄到 F1 時，則 F1 單元格中的公式為“=B1+C1D1”；向下拖動 E1 單元格填充柄到 E2 時，則 E2 單元格中的公式為“=A2+B2C2”。 （2）絕對引用。是指在公式中總是對特定位置的單元格地址進行引用。如果公式所在單元格的位置改變，被引用單元格的地址不變；如果多行或多列復制或填充公式，引用的單元格地址不會改變。其方法是在單元格地址的行號、列標前均加上符號“$”，如“$A$1”表示對 A1 單元格進行絕對引用。,第一節(jié) Excel 運算公式,（3）混合引用。是指在公式中對單元格的行號進行絕對引用而對列標進行相對引用，或對行號進行相對引用而對列標進行絕對引用。如果公式所在單元格的位置改變，則相對引用的隨之改變，而絕對引用的不會改變。其方法是在絕對引用的行號或列標前加“$”符號。 引用類型的轉換。為將公式中用到的地址表示為絕對引用，需加上“$”符號。所以可以通過修改公式中“$”符號的位置、或刪除“$”符號，來改變單元格的引用類型；也可按 F4 功能鍵，改變公式中單元格的引用類型。,第一節(jié) Excel 運算公式,【案例 22】遠華公司的職務工資數據見案例 21。根據該公司的分配方案，效益工資為職務工資的 10%；住房補貼為職務工資的 5%；應發(fā)合計為職務工資、效益工資、交通補貼、住房補貼之和；養(yǎng)老保險金為應發(fā)合計的 5%；醫(yī)療保險金為職務工資、效益工資之和的 2%；扣款合計為養(yǎng)老保險金、醫(yī)療保險金、代扣稅之和；實發(fā)合計為應發(fā)合計減扣款合計。要求 完成以上相關項目的計算（交通補貼、代扣稅暫不計算）。,第一節(jié) Excel 運算公式,【操作提示】 （1）效益工資的計算：選中 F3 單元格，然后輸入“=”；單擊一次 E3 單元格，則該單元格被相對引用到公式中；再輸入“*10%”；公式輸入完畢后按回車鍵，則 F3 單元格自動顯示計算結果，上部編輯框中顯示其公式為“=E3*10%”。如圖 23 所示。 （2）應發(fā)合計計算：選中 I3 單元格，輸入“=”號；單擊 E3，輸入“+”號；單擊 F3，輸入“+”號；單擊“G3”，輸入“+”號；單擊 H3，按下回車鍵即可。其編輯框中顯示的公式將為“=E3+F3+G3+H3”。也可在選中 I3單元格后，在英文狀態(tài)下直接從鍵盤上輸入“=E3+F3+G3+H3”，然后按下鍵盤上的回車鍵 Enter。,第一節(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。,返回,第一節(jié) Excel 運算公式,（5）選定 F3:N3 單元格區(qū)域，如圖 23 所示；然后將鼠標指向 N3 單元格右下角的填充柄，此時鼠標變?yōu)榇执蟮摹?”字狀，按下鼠標垂直向下拖動到第 14 行，然后釋放鼠標；實現單元格公式的復制。Excel 自動計算出本表各單元格的數值。由于在公式中使用了單元格引用，若修改 E 列中某單元格的職務工資，則引用這一單元格的其他單元格的值也會隨之改變。同時，由于第 3 行相關單元格的公式均是對其他單元格的相對引用，所以用填充柄自動復制的 F4:N14 區(qū)域的單元公式，也會自動調整為對本行各單元格的引用，從而保證計算結果的正確性。,第一節(jié) Excel 運算公式,四、公式返回的錯誤值 在輸入公式時，有不符合要求的輸入，就無法在 Excel 工作表的單元格中顯示正常的運算結果，該單元格中將會出現提示性的錯誤值信息。了解這些錯誤信息的含義有助于用戶檢查、修改公式，直到公式正確。表 25 中列出了 Excel 中的錯誤信息及其含義。 【案例 23】某公司的產品銷售額如表 26 所示，要求計算各產品的銷售比重。,第一節(jié) Excel 運算公式,【操作提示】A 產品25 800B 產品36 900C 產品 37 300合計100 000 （1）在工作表的 A1、A2:B6、C2、E2、G1、G2:K3、G4、G6 單元格中，輸入相關的文字或數值；如圖 24 所示。 （2）選定 A1:E1 單元格區(qū)域，單擊格式工具欄的合并居中F1:F6、G1:K1、G4:G5 單元格區(qū)域。 （3）在 C3 單元格中輸入公式“=B3/B6”，即分母為相對引用 B6 單元格；而在 D3 單元格中輸入公式“=B3/$B$6”，即分母為絕對引用 B6 單元格；選定 C3:D3，鼠標指向 D3 右下角的填充柄，待變?yōu)椤?”字狀態(tài)時，按下鼠標垂直向下拖動到第 6 行，然后釋放鼠標。,第一節(jié) Excel 運算公式,（4）同樣的，輸入第二種分析方法的公式 H4=H3/K3、H5=H3/$K$3；選定 H4:H5，通過 填充柄水平向右拖動，自動填充 I4:K5 單元區(qū)域公式。 （5）分析：由于 D3 單元格公式的分母是絕對引用 B6 單元格的數值，通過自動填充方式復制的 D4:D6 單元格公式中的分母始終引用 B6 單元格的值；D3 單元格公式的分子相對引用B3單元格的值，自動填充后的分子將隨單元格而改變；所以公式將變D4=B4/$B$6、 D5=B5/$B$6、D6=B6/$B$6，計算結果是正確的。 相反C3單元格的分子、分母均為相對引用；當自動填充復制公式時，引用的單元均會隨之改變，即其公式將會變?yōu)?C4=B4/B7、C5=B5/B8、C6=B6/B9。,第一節(jié) Excel 運算公式,同理，H5 單元公式的分母為絕對引用 K3，所以自動填充后的 I5:K5 的公式是正確的；而 H4 單元公式為相對引用 K3 的值，自動填充后出現“分母為 0”的錯。 （6）選定 A1:K6 單元格區(qū)域，單擊格式工具欄的邊框按鈕中的下拉箭頭，在其中選擇“所有框線”選項，給工作表中的有效表格（即數據區(qū)域）加上邊框線。 （7）單擊工作表左上角行列交叉處的全選按鈕，然后通過格式工具欄，設置字體。（8）選定 B3:B6，按下鍵盤上的 CTRL 鍵，再選定 H3:K3 單元區(qū)域；單擊格式工具欄的千位分隔按鈕、單擊兩次減少小數位數按鈕。選定 C3:D6，按下鍵盤上的 CTRL鍵，再選定 H4:K5 單元區(qū)域；單擊格式工具欄的百分比按鈕,第二節(jié) Excel 函數,在 Excel中，函數實際上是一個預先定義的特定計算公式。 按照這個特定的計算公式對一個或多個參數進行計算，并得出一個或多個計算結果，叫做函數值。,第二節(jié) Excel 函數,一、函數的使用與構成 1. 函數的使用。Excel 提供了財務函數、日期與時間函數、數學與三角函數、統(tǒng)計函數、查找與引用函數、數據庫函數、文本函數、邏輯函數、信息函數九大類兩百多個函數。使用這些函數不僅可以完成許多復雜的計算，而且還可以簡化公式的繁雜程度。 【案例 24】某公司生產經營 46 種產品，2009 年各季度銷售收入如表 27 所示。要求用函數計算各季度、各產品的合計收入（其他指標以后介紹）。,第二節(jié) Excel 函數,（1）設計表格。錄入 A 列和第 2 行的相關文字；輸入 B3:G6 的已知金額；合并 A1:L1單元格區(qū)域；設置字體字號。如圖 25 所示。 （2）常用工具欄求和法。操作方法如下： a. 選定 H3 單元格，單擊常用工具欄的自動求和按鈕，或單擊該按鈕邊的下拉箭頭，在彈出的下拉菜單中，選擇“求和”命令。 b. 此時該單元格中將反白顯示求和公式“=SUM(B3:G3)”，且 B3:G3 單元格將以閃爍的虛框顯示該公式所引用的單元格區(qū)域，同時編輯框中顯示其公式“=SUM(B3:G3)”。,第二節(jié) Excel 函數,c. 由于公式無誤，所以再次單擊自動求和按鈕，以確認并鎖定該公式；也可按下鍵盤上的回車鍵 ENTER，或編輯框前的輸入按鈕鎖定公式。 若雙擊自動求和按鈕，可快速實現上述求和公式的輸入。 d. 選定 H3 單元格，鼠標指向右下角的填充柄，待變?yōu)椤?”字狀態(tài)時，按下鼠標向下拖動至 H6 單元格，以實現自動復制公式。 （3）向導求和法。操作方法如下。 a. 選定 B7 單元格，單擊編輯框前的插入函數按鈕，或單擊自動求和鈕邊的下拉箭頭選擇“其他函數”命令，或選擇“插入/函數”菜單命令，均會彈出“插入函數”界面，如圖 26 所示。,第二節(jié) Excel 函數,b. 單擊該界面的類別后的下拉箭頭，在彈出的下拉框中選擇“數學與三角函數”；再通過右部的滾動條，找到求和函數“SUM”；選定“SUM”函數后單擊“確定”按鈕，或雙擊該函數，進入“函數參數”界面，如圖 27 所示。 c. 在函數參數界面的 NUMBER1 參數后部的輸入框中輸入“B3:B6”；或者單擊后部的引用按鈕，用鼠標選擇“B3:B6”單元格區(qū)域，再單擊引用按鈕回到函數參數界面，實現單元格的引用。 d. 單擊“確定”按鈕回到工作表界面，此時 B7 單元格中將顯示計算結果，編輯框中將顯示函數公式“=SUM(B3:B6)”。,第二節(jié) Excel 函數,（4）直接輸入法。 2. Excel函數的構成。函數是由函數名及參數構成，函數名說明函數所要進行的運算，而參數是指定函數使用的數值或單元格引用。函數公式如下：=函數（參數 1,參數 2,參數 3,）例如，公式“=SUM(C3:C6)”，式中 SUM 為函數名，是求和。C3:C6 單元格區(qū)域是參數，該公式表示為對 C3 到 C6 單元格區(qū)域內的數據求和。使用函數時，函數的參數必須滿足以下規(guī)定：函數參數應用括號括起來，且括號前后不能有空格；函數中參數多于一個時，必須用逗號分隔他們；參數除了是單元格的引用外，還可以是數、文本、邏輯值、數值、公式或其他函數；給定的參數必須能產生一個有效的值。,第二節(jié) Excel 函數,二、常用函數的使用 1. 求和函數 SUM。函數公式為：= SUM(Number1,Number2,) 式中，Number1,Number2, 為 130 個需要求和的參數。 2. 平均數函數 AVERAGE。函數公式為： =AVERAGE(Number1,Number2,) 3. 計數函數 COUNT。函數公式為：= COUNT(Value1,Value2,) Value1, Value2,表示包含或引用各種類型數據的參數（130 個），但只有數字類型的數據才被計算。,第二節(jié) Excel 函數,4. 條件函數 IF。其功能是判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值。函數公式為： =IF(Logical_test,Value_if_true,Value_if_false) 式中，Logical_test 表示計算結果為 TRUE（邏輯真值）或 FALSE（邏輯假值）的任意值或表達式；Value_if_true 表示為 TRUE 時的返回值；Value_if_false 表示為 FALSE 時的返回值。 IF 在進行多次判斷中，最多可以嵌套七層。,第二節(jié) Excel 函數,【案例 25】承案例 22，計算交通補貼：經理為 300 元、科員為 150 元。 【操作提示】 （1）選中 G3 單元格；在其中輸入公式“=IF(D3=“經 理“,300,150)”。該公式的含義為： D3 單元格中如果是文本“經理”，就返回其邏輯真值 300，否則就返回假值 150。其結果如圖28 所示。 （2）拖動 G3 單元格填充柄向下填充公式即可。,第二節(jié) Excel 函數,5. 求最大值函數 MAX。其功能是返回一組數值中的最大值。函數公式為： =MAX(Number1,Number2,) 式中，Number1,Number2,，表示要從中找出最大值的 130 個數字參數。 6. 求最小值函數 MIN。其功能是返回一組數值中的最小值。函數公式為： =MIN(Number1,Number2,) 【案例 26】承前案例 24，計算各季品種數、平均銷售額、各季最大銷售額、最小銷售額、判斷哪些產品全年銷售額超過了 2 000 萬元。,第二節(jié) Excel 函數,【操作提示】 （1）平均數。方法是：選定 I3 單元格，輸入“=AVERAGE(B3:E3)”；選定 I4 單元格，輸入“=AVERAGE(B4:F4)”；選定 I5 單元格，輸入“=AVERAGE(B5:G5)”；選定 I6 單元格，輸入“=AVERAGE(B6:G6)”。 （2）各季最大銷售額。輸入其函數公式：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)。,第二節(jié) Excel 函數,（4）各季經營品種。輸入公式 L3=COUNT(B3:G3)；然后自動填充 L4:L7 單元區(qū)域公式。以上的計算均可使用向導法，選擇“統(tǒng)計”類中的對應函數進行計算。 （5）是否超 2 000 萬元判斷。輸入公式 B8=IF(B72000,“是“,“否“)；然后自動填充 C8:G8單元區(qū)域公式。 7. 取絕對值函數 ABS。其功能是取表達式的絕對值。函數公式為：=ABS(Number)式中，Number 表示需要計算其絕對值的實數。 8. 條件計數 COUNTIF。其功能是計算某個區(qū)域中滿足給定條件的單元格數目。函數公式為： =COUNTIF(Range,Criteria),第二節(jié) Excel 函數,式中：Range 表示需要計算其中滿足條件的單元格數目的單元格區(qū)域；Criteria 表示確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。 9.求模函數 MOD。其功能是返回兩數相除的余數。函數公式為：=MOD(Number,Divisor)式中，Number 表示被除數；Divisor 表示除數。 10.條件求和函數 SUMIF。其功能是對滿足條件的單元格求和。函數公式為： =SUMIF(Range,Criteria,Sum_range) 式中，Range 表示用于條件判斷的單元格區(qū)域；Criteria 表示確定哪些單元格將被相加求和的條件，其形式可以為數字、表達式或文本；Sum_range 表示需要求和的實際單元格。,第二節(jié) Excel 函數,11. 排位函數 RANK。其函數公式如下： =RANK(Number,Ref,Order) 式中，Number 表示需要排位的數字；Ref 表示包含一組數字的數組或引用，即需排位的全部總體，非數值型參數將被忽略；Order 表示排位方式，如果 Order 為 0 或省略則按降序排列，否則按升序排列。 【案例 27】承案例 22 計算個人所得稅。個人所得稅的起征點和稅率如表 28 所示。 工資、薪金所得稅的計算公式是： 應納個人所得稅稅額 =應納稅所得額適用稅率速算扣除數,第二節(jié) Excel 函數,【操作提示】 （1）選中L3 單元格；在單元格中輸入公式（這些字符均應在英文輸入法下 =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),第二節(jié) Excel 函數,公式含義為：按國家規(guī)定，如果應納稅稅額員工的應發(fā)合計法定免征項(養(yǎng)老保險金+醫(yī)療保險金) 起征點(現行為 2000 元)小于 0，則個人所得稅為 0；否則如果應納稅稅額在 0500 元，為應納稅稅額5%；否則如果應納稅稅額在 5002 000 元，為應納稅稅額10%25元的速算扣除數，以此類推；由于該公司沒有人的收入超過 20 000 元，所以，條件函數 IF 只判斷到 5 00020 000 即可。 （2）拖動 L3 單元格的填充柄向下填充即可，結果如圖 29 所示。,第三節(jié) 數據排序與分類匯總,一、數據排序 1.排序的概念。排序是將數據區(qū)域中的記錄，按其某字段名的數據值由小到大（升序）或由大到小（降序）的順序進行排列，用來排序的字段或條件稱為排序鍵。排序后，將會對整頓秩序各工作表或選定區(qū)域的內容進行調整。 2.排序操作。排序的操作方法有以下幾種。 （1）用工具按鈕排序。若只按一個排序標志進行排序， 用常用工具欄上的排序按鈕更為方便。其操作是：在數據區(qū)域中選定要排序的單元區(qū)域。根據需要單擊常用工具欄上的“降序”或“升序”，這時會彈出排序的警告對話框（如圖 210 所示）。,第三節(jié) 數據排序與分類匯總,在“排序警告”對話框中一定注意，選擇“擴展選定區(qū)域”以某個字段進行排序時，對應的列會隨排序位置的變化而變化。而“以當前選定區(qū)域排序”，其他的列將不參與排序。Excel 中進行升序排序時，數字和日期按由小到大排列；字母按 AZ、az 進行排列；漢字要選轉化為漢語拼音，再按字符比較規(guī)則進行比較。 （2）用菜單命令進行排序。使用“數據排序”命令，可以按多個排序鍵來進行排序，還可以按列、或者按行來排序。但排序鍵最多不能超過三個，分別稱為主要關鍵字、次要關鍵字和第三關鍵字；當主關鍵字值相同時，就根據次關鍵字排序，如果次關鍵字也一致，則根據第三關鍵字來排序。,第三節(jié) 數據排序與分類匯總,【案例 28】承案例 27，進行工資表的排序。 【操作提示】 a.選擇數據區(qū)域中一個單元格；選擇“數據排序”命令，打開如圖 211 所示的“排序”對話框。 b.單擊對話框中“主要關鍵字”列表框的向下箭頭，選取主要關鍵字。若需要還可以選取次要關鍵字和第三關鍵字，并在“升序”、“降序”上作出相應的選擇。 c.單擊“確定”按鈕，按職務工資的升序排序，排序后圖 212 中人員編號的順序變化。 如果要按行進行排序，或者對文本要進行“按字母排序”或“按筆畫排序”，或者排序時字母要區(qū)分大小寫等，請在圖 211 所示的“排序”對話框選擇“選項”，打開“選項”對話框并在其中進行選擇。,第三節(jié) 數據排序與分類匯總,3.返回排序前的表格。當反復對表格進行排序后，表格的原有次序被打亂，若需返回原有表格次序，可使用常用工具欄的撤銷或恢復按鈕。但是排序的次數較多，或排序后還進行了其他的操作，此時恢復就不容易了。所以，在排序前最好是給表格增加一個表示原有表格次序的字段，如“序號”、“編號”、“NO.”等字段名，置于 A 列、或表格的有效數據的最后一列，對原表格的所有記錄（即每行）進行編號。這樣，排序后要返回原表格的次序只需對該字段進行重新排序即可。,第三節(jié) 數據排序與分類匯總,二、分類匯總 分類匯總就是將很多數據按同類別歸類放到一起，然后再進行同類數據求和、計數、求平均等匯總運算。通過分類匯總，得到需要的統(tǒng)計信息，諸如商品銷售按部門、按人員匯總等重要的統(tǒng)計信息。在分類匯總前必須對工作表指定字段進行排序；如果沒有排序，匯總結果很混亂，無法達到真正的分類匯總的目的。分類匯總的形式有單字段分類匯總和多字段分類匯總。 1.單字段分類匯總。Excel能夠快速的以某一個字段為分類項，對數據區(qū)域中其他字段的數值進行各種匯總統(tǒng)計。分類匯總時將分類項稱為分類字段；將其他需統(tǒng)計值的字段稱為選定匯總項，匯總項可以多選；匯總的方式可以是求和、乘積、最大值、最小值、計數、計數值、標準差、方差等。,第三節(jié) 數據排序與分類匯總,【案例 29】承案例 27，對遠華公司 2008 年 8 月工資表，按“部門”分類匯總顯示實發(fā)工資的部門和人數。 【操作提示】 （1）選取數據區(qū)域中的任一單元格,選擇“數據分類匯總”命令，打開如圖 213 所示的“分類匯總”對話框。 （2）在“分類字段”中選擇“部門名稱”；在“匯總方式”中選擇“求和”；在“選定匯總項”中選擇“實發(fā)合計”；單擊“確定”按鈕，完成分類匯總操作。 2.多字段分類匯總。若需要從數據區(qū)域中得到更多的信息，以便于分析數據，則多次執(zhí)行匯總功能即可。即選按某一個字段進行分類匯總，然后再按另一個字段進行分類匯總，但要在圖 213 的分類匯總對話框中取消“替換當前分類匯總”選項。,第三節(jié) 數據排序與分類匯總,【操作提示】 在上例的基礎上，還需按“部門”分類匯總“部門人數”，繼續(xù)其操作如下： （1）選取數據區(qū)域中的任一單元格，選擇“數據分類匯總”，打開“分類匯總”對話框。 （2）在“分類字段”中選擇“部門名稱”；在“匯總方式”中選擇“計數”；在“選定匯總項”中選擇“姓名”，圖 214 。 （3）清除“替換當前分類匯總”選項，單擊“確定”按鈕。 3. 大綱功能的使用。Excel 在分類匯總的同時，也建立了大綱圖標于工作表的左上角。,第三節(jié) 數據排序與分類匯總,單擊“1”則只顯示“總計”一行，單擊“3”則顯示全部數據及匯總結果。 4. 清除分類匯總。清除分類匯總的方法很簡單，只要在“分類匯總”對話框中單擊“全部刪除”按鈕，就能將分類匯總刪掉。在匯總的要求較簡單時，分類匯總功能是得力的工具。但如果數據區(qū)域較龐大，并且匯總要求較為復雜時，更好的分類匯總工具是數據透視表、數據透視圖，詳見本書后面章節(jié)的介紹。,第四節(jié) 數 據 篩 選,使用Excel 工作表時，常常會根據需要去查找所需的某項信息，數據篩選能方便快速的找到所需的信息。執(zhí)行數據篩選后只顯示出包含符合某一值或一組條件的行，而其他行將會被隱藏。Excel 2003 提供了自動篩選和高級篩選功能。,第四節(jié) 數 據 篩 選,一、自動篩選 使用自動篩選可以快速而方便地查找和使用單元格區(qū)域或工作表的所需信息，隱藏那些不希望顯示的行。 1. 使用自動篩選列表進行篩選。篩選條件比較簡單時，通過“自動篩選”命令產生自動篩選器，該篩選器下部列出了某一個字段下所有可供篩選的子項列表，選擇列表項即可篩選。 【案例 210】從遠華公司 2008 年 8 月工資表中篩選顯示“銷售部”職工的工資情況。 【操作提示】 （1）選取數據區(qū)域中的任一單元格，選擇“數據篩選”命令中的“自動篩選”子命令，這時可以看到各字段邊的篩選器（帶下拉箭頭的標記），如圖 215 所示。,第四節(jié) 數 據 篩 選,（2）單擊要進行篩選列（字段）的篩選箭頭（篩選器），下拉出一個清單框，從中選擇“銷售部”。從圖 216 中可以看到，除了“銷售部”的職工外，其他所有行（記錄）的數據都在數據區(qū)域中被隱藏了（表中的行號不連續(xù)、有一部分不可見，表明未顯示行仍然存在，因不符合篩選條件而被隱藏）。 （3）若要取消所選子項的篩選，而按另一子項進行篩選，要先通過“篩選器”清單上的“全部”命令恢復所有數據，然后再從列表中選另一子項進行篩選。如查看了“銷售部”職工的情況后，單擊“部門名稱”字段邊的篩選器，選“全部”命令，則所有隱藏行（記錄）均全部顯示；然后再單擊篩選器，從列表中選“采購部”，則除采購部職工外的其他記錄被全部隱藏。,第四節(jié) 數 據 篩 選,（4）不同字段邊的篩選器之間是“且”（即“與”）的關系。如：在部門名稱篩選器中選擇“采購部”，又在職務篩選器中選擇“經理”，則只顯示采購部經理的相關記錄，除此之外的其他記錄將被全部隱藏。 （5）取消篩選操作。從“數據”菜單中再次選擇“篩選”命令后，單擊“自動篩選”子命令，則工作表中的篩選箭頭消失，篩選操作被取消。 2. 自定義篩選。在進行自動篩選時，可對某一字段設置一些特殊的篩選條件，如大于、等于、小于、不大于、始于、止于、包含、不包含等條件；可以進行兩個條件的設定，條件之間的關系，可以是“與”、也可以是“或”；這些條件的值，既可以從列表中選擇、也可輸入特定值，還可使用通配符“*”或“？”，其中“*”表示任意多個字符、“？”表示單個字符。,第四節(jié) 數 據 篩 選,【案例 211】從遠華公司 2008 年 8 月工資表中，篩選出代扣稅大于 0 而小于 200 元的職工情況。 【操作提示】 （1）選取數據區(qū)域中的任一單元格，選擇“數據篩選/自動篩選”子命令，則工作表的所有字段邊均出現篩選器；若原已進行了篩選而沒有取消其篩選操作，則應保證所有已使用的篩選器均已選擇了“全部”命令，即工作表的各行（記錄）均沒有被隱藏。 （2）單擊代扣稅篩選器，在下拉清單框中選擇“(自定義.)”項，如圖 215 所示，進入圖 217 所示的“自定義自動篩選方式”界面。,第四節(jié) 數 據 篩 選,（3）在該對話框中選擇“大于”，右欄選擇“0”（也可直接輸入）；選擇中部為“與”的關系；選擇第二個條件為“小于”，右欄輸入“200”（本例不能直接選擇）。 （4）單擊“確定”，完成自定義自動篩選，結果將會只顯示該公司代扣稅大于0且小于200 元的職工記錄情況。 【案例 212】從遠華公司 2008 年 8 月工資表中，篩選顯示所有姓“陳”的職工。 【操作提示】 （1）對工作表使用“自動篩選”命令（或保證所有已使用的篩選器均已單擊了“全部”命令）。 （2）單擊姓名篩選器，從下拉列表中選擇“自定義”項，進入圖 218 所示的“自定義自動篩選方式”界面。,第四節(jié) 數 據 篩 選,（3）在其中選擇“始于”條件，在右部輸入“陳*”；單擊“確定”按鈕后，將只顯示“陳”姓職工的信息，其他記錄將被隱藏。需說明的是，若需篩選姓名中最后一個字為“紅”的職工，應選擇“止于”條件；若要選擇姓名中的任意一個字為“兵”的職工，則應選擇“包含”條件。 3. 前 10個篩選。需統(tǒng)計顯示某個字段中相對值靠前、或靠后的記錄時，可使用自動篩選器提供的“前 10 個”功能。,第四節(jié) 數 據 篩 選,【案例 213】從遠華公司 2008 年 8 月工資表中，篩選顯示職務工資處于后 3 位的職工。 （1）對工作表使用“自動篩選”命令（或保證所有已使用的篩選器均已單擊了“全部”命令）。 （2）單擊職務工資篩選器，選擇“前 10 個”命令進入“自動篩選前 10 個”界面，如圖219 所示。 （3）選擇“最小”條件，輸入值為“3”，選擇“項”為統(tǒng)計數量；單擊“確定”按鈕即可。 需說明的是，在條件中還可選擇“最大”；統(tǒng)計數量中還可選擇“百分比”為計量依據，如圖 220 所示。,第四節(jié) 數 據 篩 選,二、高級篩選 與自動篩選相比，高級篩選可以選用更多的條件，并且可以不使用邏輯運算符而將多個篩選條件加以邏輯運算；高級篩選還可將篩選結果從數據區(qū)域中抽取出來，復制到當前工作表的指定位置上。要使用高級篩選，需要有 3 個區(qū)域：數據區(qū)域，即需要進行篩選的數據區(qū)域或整個工作表。條件區(qū)域，即用來指定篩選數據時必須滿足的條件。顯示篩選結果區(qū)域，即用于存放滿足篩選條件結果的