《電子表格Excel4.4數(shù)據(jù)管理與分析 辦公自動EXCEL電子表格》由會員分享，可在線閱讀，更多相關《電子表格Excel4.4數(shù)據(jù)管理與分析 辦公自動EXCEL電子表格（23頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、4.44.4數(shù)據(jù)管理與分析數(shù)據(jù)管理與分析4.4.1使用公式或函數(shù)進行數(shù)據(jù)計算,4.4.2數(shù)據(jù)的排序4.4.3數(shù)據(jù)的篩選4.4.4數(shù)據(jù)的分類匯總4.4.5數(shù)據(jù)的合并計算4.4.6數(shù)據(jù)透視表Excel的主要功能不在于能創(chuàng)建表格、顯示和打印表格，而在于對工作表中數(shù)據(jù)的管理、分析、查詢與統(tǒng)計，如可對工作表進行數(shù)據(jù)運算、數(shù)據(jù)排序、統(tǒng)計篩選、分類匯總、合并計算、建立數(shù)據(jù)透視表等。4.4.1使用公式或函數(shù)進行數(shù)據(jù)計算利用公式和函數(shù)，既可完成像加、減、乘、除這樣的簡單運算，也可以完成像統(tǒng)計、比較等較復雜的運算公式就是一個等式，包括運算的數(shù)據(jù)、函數(shù)、單元格地址、運算符等要素。EXCEL規(guī)定，公式必須是用等號（=

2、）開始，其計算結(jié)果自動顯示在對應的單元格中。函數(shù)則是已經(jīng)建立好的公式，EXCEL將一些常用的公式（如求和、求平均、計數(shù)統(tǒng)計等）內(nèi)置EXCEL系統(tǒng)內(nèi)，并以函數(shù)的形式提供給用戶，當遇到使用這些公式時，可以直接給出對應的函數(shù)名，再添加上相應的自變量即可使用，無須再輸入公式的各種要素。1、單元格地址及表示、單元格地址及表示1）單個單元格地址的表示列號加行號表示單個單元格的地址。如：A62）連續(xù)單元格地址的表示用區(qū)域左上角的單元格地址加冒號“：”，再加上區(qū)域右下角的單元格地址來表示連續(xù)單元格區(qū)域的地址。如：C3：E83）不連續(xù)單元格的地址表示用逗號號分隔開各個單元格地址或者說單元格區(qū)域地址。如：A3，B

3、2：C6，D5：F92、單元格的引用、單元格的引用在公式或函數(shù)中，要涉及到引用單元格的數(shù)據(jù)，其引用方法不是直接給出單元格內(nèi)容，而是給出單元格地址。這種引用辦法可以保證當單元格內(nèi)容改變時，公式或函數(shù)的計算結(jié)果也能及時自動更新，而不需要更改公式或函數(shù)。單元格的引用有相對引用、絕對引用和混合引用三種方法。（另外還有引用其他工作表或工作簿中的單元格（多維引用）1 1）相對引用）相對引用）相對引用）相對引用在輸入公式的過程中。除非我們特別指明，一般是使用相對地址來引用單元格的位置。所謂相對引用是指：當把一個含有單元格地址的公式復制到一個新的位置的時候，公式中引用的單元格地址會隨著改變。如：A1，B4等，

4、表示相對址址的引用。例如：在A4 單元格中輸入“=A1+A2+A3”公式，現(xiàn)將該公式復制到B4單元格中，則B4單元格中的公式變成了“=B1+B2+B3”，即公式中引用的單元格地址發(fā)生了變化，公式內(nèi)容也隨之改變，因而計算結(jié)果也就不同了，這就是相對引用。2 2）絕對引用）絕對引用）絕對引用）絕對引用所謂絕對地址引用是指：當把公式復制到新的位置時，公式中的單元格地址保持不變。也就是被引用的單元格與引用單元格的位置關系是絕對的。在Excel中，是通過地單元格地址的“凍結(jié)”來達到此目的的，也就是在列號和行號的前面添加美元符號“$”。如：$A、$B、$C等，表示絕對引用。例如：在A4單元格中輸入“=$A$

5、1+$A$2+$A$3”公式，現(xiàn)將該公式復制到B4單元格中國，則B4單元格中的公式仍然是“=$A$1+$A$2+$A$3”，即公式內(nèi)容不變，計算結(jié)果也不變，就是絕對引用.3 3）混合引用）混合引用）混合引用）混合引用單元格地址的引用除了上述兩種方法外，還有混合引用?；旌弦檬侵敢玫膯卧刂?，行用相對地址列用絕對地址，或行用絕對地址，列用相對地址。如$A2。D$4這就是混合引用。在混合引用中。相對地址隨公式所以地址的變化而變化，絕對地址不隨公式地址的改變而改變。例如：在A5單元格中輸入“=A$1+$A2+$A2+A$3+A4”公式，現(xiàn)將該公式復制到B5單元格中，則B5單元格中的公式變成了“=B

6、$1+$A2+$A2+$A$3+B4”即公式中有$的絕對引用地址沒有改變，而相對引用地址發(fā)生了變化，因而計算結(jié)果也就不同了，這就是混合引用。4 4）引用其他工作表中的單元格（多維引用）引用其他工作表中的單元格（多維引用）引用其他工作表中的單元格（多維引用）引用其他工作表中的單元格（多維引用）在當前工作表中可以引用其他工作表中的單元格內(nèi)容，其方法有兩種：方法一：直接鍵入非當前工作表的單元格在引用時必須加上工作表名稱。再加上“！”，這就是二維引用。如sheet3!B2表示引用sheet3工作表的B2單元格。如果引用的工作表不是當前的工作簿中的表格，引用時還須加上工作簿的路徑、名稱等信息。這就是三維

7、引用，如：“D：Mydocumentsbook2.xlssheet2!$a$1+a1”表示引用book2工作簿中的sheet2表的A1單元格和當前工作表的A1單元格，并且求該兩個單元格內(nèi)容之和。方法二：用鼠標選擇要引用的單元格直接打開被引用的工作簿，選中要引用的工作表，單擊要引用的單元格或單元格區(qū)域即可。建立公式建立公式1）鍵入公式用鍵盤創(chuàng)建公式的步驟如下（1）選定要輸入公式的單元格（2）先鍵入等號“=”。然后輸入計算公式（3）按回車鍵確認，計算結(jié)果自動顯示在選定的單元格中2）使用公式選項板建立公式使用公式選項板來輸入公式可以預覽計算結(jié)果，還可以創(chuàng)建包含函數(shù)的復雜公式。公式選項板如下：要顯示公

8、式選項板，可單擊編輯欄中的“=”，要在公式中輸入函數(shù)，可在選項板左邊的函數(shù)下拉列表中選?。J為求和函數(shù)SUM），公式的計算結(jié)果顯示在選項板下部，輸入完畢按“確定”按鈕。要使公式選項板輸入公式，也需要先選中存放計算結(jié)果的單元格，然后單擊編輯欄中的“=”，接著輸入所需要的計算公式，最后按“確定”按鈕。公式的修改、移動與復制公式的修改、移動與復制1）修改已有的公式單擊包含要修改公式的單元格，則該單元格中的公式會顯示在編輯欄中，在編輯欄中進行公式的修改，最后按回車鍵結(jié)束2）公式的移動與復制公式的移動與復制，其方法類似于單元格內(nèi)容的移動與復制使用函數(shù)使用函數(shù)1）函數(shù)的分類及組成Excel2000提供了

9、200多個函數(shù)，按功能可分為如下幾大類，即數(shù)學和三角函數(shù)、文本函數(shù)、邏輯函數(shù)、數(shù)據(jù)庫函數(shù)、統(tǒng)計函數(shù)、查找和引用函數(shù)、日期和時間函數(shù)、信息函數(shù)和財務函數(shù)。函數(shù)包括函數(shù)名、參數(shù)（常數(shù)、單元格地址）及函數(shù)值（結(jié)果）三部分。一個典型函數(shù)的示例如圖所示。2）輸入函數(shù)）輸入函數(shù)輸入函數(shù)同輸入公式的操作類似，如果能記住函數(shù)的名稱、參數(shù)，可直接在單元格中鍵入函數(shù)及參數(shù)，否則可以使用函數(shù)向?qū)碚迟N函數(shù)。使用函數(shù)向?qū)碚迟N函數(shù)的操作方法如下：（1）選定要輸入函數(shù)的單元格（即存放函數(shù)值的單元格）（2）單擊“插入”菜單，選“函數(shù)”命令，或者單擊“常用”工具欄上的“粘貼函數(shù)”按鈕，系統(tǒng)彈出如圖所示的“粘貼函數(shù)”對話框。

10、（3）在“函數(shù)分類”列表框中選擇所需要的函數(shù)類型，則該函數(shù)類型所對應的所有函數(shù)出現(xiàn)在“函數(shù)名”列表框中，在該列表框中選定需要的函數(shù).在兩個列表框的下部有選定函數(shù)的文字說明，通過這些說明可以了解所選函數(shù)的作用。(4)單擊“確定”按扭，系統(tǒng)又彈出了一個與所選函數(shù)對應的對話框，如圖所示：SUM函數(shù)的對話框，在對話框的下部，有該函數(shù)所用的參數(shù)說明和函數(shù)的計算結(jié)果，通過參數(shù)說明可以了解參數(shù)的數(shù)量、意義及用法。（5）在該對話框中，用戶可以輸入函數(shù)的參數(shù)（常數(shù)、單元格引用地址等），也可以使用鼠標選定函數(shù)的單元格引用參數(shù)，通過單擊對話框的折疊按鈕，可以將該對話框折疊起來，只顯示一行參數(shù)輸入?yún)^(qū)，以便完整地顯示出

11、當前工作表，然后使用鼠標選定單元格或單元格區(qū)域，被選定的單元格區(qū)域有一個閃爍的虛線框，系統(tǒng)自動將所選定的單元格地址添加到參數(shù)輸入?yún)^(qū)中。單元格選定結(jié)束，再單擊展開按鈕，則對話框又展開在屏幕上，如上圖，函數(shù)參數(shù)為A2：D2，即計算A2，B2，C2，D2四個單元格的數(shù)值之和。4.4.2數(shù)據(jù)的排序（一）數(shù)據(jù)的排序（一）在數(shù)據(jù)的管理工作中，經(jīng)常遇到對某些數(shù)據(jù)進行排序處理，以便直觀地對比各個記錄。1、數(shù)據(jù)排序原則在對工作表進行數(shù)據(jù)排序時，根據(jù)單元格不同的數(shù)據(jù)類型，Excel有其默認的排序原則。在按升序排序時，Excel使用如下次序（降序除了空格在最后，其它排序次序反轉(zhuǎn)）1）數(shù)值從最小的負數(shù)到最大的正數(shù)進行

12、排序2）字母按字母先后順序排序。在按字母先后順序?qū)ξ谋卷椷M行排序時，Excel從左到右一個字符一個字符地進行排序。如：A100。將排在A1后面A11前面。3）文本以及包含數(shù)字的文本，按下列順序排序：0123456789（空格）！“#￥%（）*，。/：；？-|+|ABCDEFGHIJKLMNOPQRSTUVWXYZ4)邏輯值在邏輯值中，F(xiàn)ALSE（假或不成立）排在TRUE（真或成立）之前。5）錯誤值所有的錯誤值的優(yōu)先級相同6）空格空格始終排在最后。4.4.2數(shù)據(jù)的排序（二）數(shù)據(jù)的排序（二）2、使用工具欄按鈕對單列數(shù)據(jù)進行排序根據(jù)工作表中某一列數(shù)據(jù)進行排序，可以使用菜單命令，但使用工具按鈕是一種最

13、快捷的方法。步驟如下：（1）在待排序數(shù)據(jù)列中單擊任一單元格。（2）單擊“常用”工具欄中的排序按鈕若要按“升序”排序，單擊“升序”按鈕，反之按“降序”按鈕3、使用排序?qū)υ捒驅(qū)Χ嗔袛?shù)據(jù)組合排序按照單列數(shù)據(jù)排序時，往往會出現(xiàn)某些數(shù)據(jù)完全相同的（即并列）的情況,這時可以選擇按多列數(shù)據(jù)進行組合排序，以進一步區(qū)分并列的數(shù)據(jù)。使用菜單命令對多列數(shù)據(jù)進行組合排序的操作步驟如下：（1）在需要排序的工作表中，單擊任一單元格。（2）單擊“數(shù)據(jù)”菜單，選“排序”命令，出現(xiàn)如圖所示的“排序”對話框：（3）在“排序”對話框中，從“主關鍵字”下拉列表框中選擇排序的主關鍵（主數(shù)據(jù)列），并從對應的排序順序單選框中，確定該關鍵字

14、是升序還是降序。再依次從“次關鍵字”和“第三關鍵字”下拉列表中選定排序依據(jù)的第二關鍵字或第三關鍵字，并確定出它們4.4.3數(shù)據(jù)的篩選數(shù)據(jù)的篩選4.4.4數(shù)據(jù)的分類匯總（一）數(shù)據(jù)的分類匯總（一）分類匯總就是按類別對數(shù)據(jù)進行求和、計數(shù)、求平均、求最大值、求最小值、求方差等的統(tǒng)計運算。在Excel2000中，使用專用的分類匯總命令，不需要人工創(chuàng)建公式，Excel自動創(chuàng)建公式、插入分類匯總行與總和行，并且可以對匯總結(jié)果或明細數(shù)據(jù)分級進行顯示。數(shù)據(jù)的分類匯總（二）數(shù)據(jù)的分類匯總（二）1、創(chuàng)建分類匯總要創(chuàng)建分類匯總，可按如下步驟進行。（1）選定需要分類匯總的數(shù)據(jù)列，并對該列數(shù)據(jù)進行排序（升序或降序均可）（

15、2）在要分類的工作表中單擊任一單元格。（3）單擊“數(shù)據(jù)”菜單，選“分類匯總”命令，彈出“分類匯總”對話框。（4）在對話框事，從“分類字段”列表框中選擇要進行分類匯總的分類字段列。如：（5）在“匯總方式”列表框中選擇用來計算分類匯總的函數(shù)（6）在“選定匯總項”列表框中，選定需要分類匯總的數(shù)據(jù)列（7）選擇“分類匯總”對話框中的其他選項若想替換現(xiàn)存的分類匯總，選中“替換當前分類匯總”復選框。若想在每組之前插入分頁，選中“每組數(shù)據(jù)分頁”復選框。若想在數(shù)據(jù)組末顯示分類匯總結(jié)果，選中“匯總結(jié)果顯示在數(shù)據(jù)下方”復選框（8）單擊“確定”按鈕。完成分類匯總操作。如圖：2、匯總結(jié)果的顯示與調(diào)整分類匯總表具有分級顯

16、示分類匯總結(jié)果的功能。共分三級：第一級只顯示全部數(shù)據(jù)的匯總結(jié)果（總的匯總結(jié)果，只有一個；第二級只顯示分類匯總結(jié)果和總的匯總結(jié)果；第三級顯示全部原始數(shù)據(jù)、分類匯總結(jié)果和總的匯總結(jié)果。如果要分級查看分類匯總表中的明細數(shù)據(jù)或匯總結(jié)果，只需要單擊對應的級別符號按鈕之一即可。并通過分級下方的顯示明細數(shù)據(jù)符號按鈕和隱藏顯示數(shù)據(jù)符號按鈕，決定是否顯示明細數(shù)據(jù)。3、刪除分類匯總要清除工作表中的分類匯總，可以按照以下步驟操作：（1）在要清除分類匯總的工作表中，單擊任一單元格（2）單擊“數(shù)據(jù)”菜單，選“分類匯總”命令（3）在“分類匯總”對話框中，單擊“全部刪除”按鈕。則現(xiàn)有的分類匯總即被刪除。4.4.5數(shù)據(jù)的合并

17、計算數(shù)據(jù)的合并計算一個公司內(nèi)可能有很多的銷售地區(qū)或者分公司，各個分公司具有各自的銷售報表和會計報表，為了對整個公司的所有情況進行全面的了解，就要將這些分散的數(shù)據(jù)進行合并，從而得到一份完整的銷售統(tǒng)計報表或者會計報表。在Excel中系統(tǒng)提供了合并計算的功能，來完成這些匯總工作。所謂合并計算是指，可以通過合并計算的方法來匯總一個或多個源區(qū)中的數(shù)據(jù)。MicrosoftExcel提供了兩種合并計算數(shù)據(jù)的方法。一是通過位置，即當我們的源區(qū)域有相同位置的數(shù)據(jù)匯總。二是通過分類，當我們的源區(qū)域沒有相同的布局時，則采用分類方式進行匯總。要想合并計算數(shù)據(jù)，首先必須為匯總信息定義一個目的區(qū)，用來顯示摘錄的信息。此目

18、標區(qū)域可位于與源數(shù)據(jù)相同的工作表上，或在另一個工作表上或工作簿內(nèi)。其次，需要選擇要合并計算的數(shù)據(jù)源。此數(shù)據(jù)源可以來自單個工作表、多個工作表或多重工作簿中。在Excel2000中，可以最多指定255個源區(qū)域來進行合并計算。在合并計算時，不需要打開包含源區(qū)域的工作簿。11.1.1通過位置來合并計算數(shù)據(jù)通過位置來合并計算數(shù)據(jù)是指：在所有源區(qū)域中的數(shù)據(jù)被相同地排列，也就是說想從每一個源區(qū)域中合并計算的數(shù)值必須在被選定源區(qū)域的相同的相對位置上。這種方式非常適用于我們處理日常相同表格的合并工作，例如，總公司將各分公司的合并形成一個整個公司的報表。再如，稅務部門可以將不同地區(qū)的稅務報表合并形成一個市的總稅務報表等等。下面我們以一個實例來說明這一操作過程，建立如圖11-1所示的工作簿文件4.4.6數(shù)據(jù)透視表數(shù)據(jù)透視表