Excel表格數(shù)據(jù)分析及處理教程

1、Excel 2007 數(shù)據(jù)分析及應(yīng)用,目 錄,常用函數(shù),4,3,2,1,分類匯總,數(shù)據(jù)透視,圖表制作,第一章 常用函數(shù),函數(shù)的概述 數(shù)學(xué)函數(shù) 邏輯函數(shù) 日期和時間函數(shù) 文本函數(shù) 查找和引用函數(shù),第1節(jié) 函數(shù)的概述,函數(shù)的基本概念 函數(shù)的分類 使用函數(shù) 使用函數(shù)的優(yōu)勢 獲得Excel 函數(shù)的幫助,預(yù)先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式,分為12類、356個函數(shù),直接使用、利用“插入函數(shù)”對話框,簡化公式、實現(xiàn)特殊運算、實現(xiàn)智能判斷、提高工作效率,使用“函數(shù)參數(shù)”對話框 使用Excel幫助,如果函數(shù)以公式的形式出現(xiàn)，請在函數(shù)名稱前面鍵入等號 =,函數(shù)的結(jié)構(gòu) SUM(A10,B5:B10,

2、50,37),1.1 函數(shù)的基本概念,函數(shù)名,參數(shù)，可以是常量、邏輯值、數(shù)組、錯誤值或單元格引用等,Excel 2007的函數(shù)是指預(yù)先定義好，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式,1.2 函數(shù)的分類,Excel 2007提供了的函數(shù)?？煞譃?2類：,利用“插入函數(shù)”對話框使用函數(shù),1.3 使用函數(shù),選中要顯示結(jié)果的單元格,在“公式”選項卡“函數(shù)庫”組，單擊“插入函數(shù)”,簡化公式 函數(shù)可以使一些復(fù)雜的公式更易于使用，使復(fù)雜的數(shù)學(xué)表達式輸入更加簡化。比如，要計算B3:D7區(qū)域中數(shù)據(jù)的平均值時，使用公式計算量大，需要寫成： “=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+

3、D4+D5+D6+D7)/15” 而使用函數(shù)將相當(dāng)簡單，只要寫成：“=AVERAGE(B3:D7)”,1.4 使用函數(shù)的優(yōu)勢,實現(xiàn)特殊運算 在日常的工作中，有一些計算使用公式不但復(fù)雜，而且不具有特定的專業(yè)知識還無法表達，那么就可以使用現(xiàn)有的函數(shù)進行特殊運算 例如，按揭貸款的等額分期償還額的計算必須用到高等數(shù)學(xué)的知識，而使用PMT函數(shù)(計算在固定利率下，貸款的等額分期償還額)就相當(dāng)簡單 PMT(rate,nper,pv,fv,type),1.4 使用函數(shù)的優(yōu)勢,貸款利率,付款期總數(shù),未來值,現(xiàn)值,付款時間,實現(xiàn)特殊運算,1.4 使用函數(shù)的優(yōu)勢,貸款利率,付款期總數(shù),現(xiàn)值,實現(xiàn)智能判斷 用函數(shù)還可

4、以實現(xiàn)智能判斷 例如，在學(xué)校，老師統(tǒng)計學(xué)生的考試成績，如果，其成績大于等于80則為“優(yōu)秀”,如果成績大于等于59且小于80則為“及格”,否則為“不及格”。這類問題用人工計算很不方便，可以使用函數(shù)來進行自動化處理,1.4 使用函數(shù)的優(yōu)勢,實現(xiàn)智能判斷,1.4 使用函數(shù)的優(yōu)勢,提高工作效率 在學(xué)校的期末總結(jié)中，常會需要按學(xué)生的考試成績進行排位的問題，當(dāng)然可以使用“排序”按鈕來完成，但是這樣做比較麻煩，如果使用排位函數(shù)RANK就比較方便，尤其是紀(jì)錄很多、相同分?jǐn)?shù)很多的情況下，不用RANK函數(shù)其工作量不可想像 RANK(number,ref,order),1.4 使用函數(shù)的優(yōu)勢,需要排位的數(shù)字,引用的

5、區(qū)域，絕對地址,排位的方式,提高工作效率,1.4 使用函數(shù)的優(yōu)勢,由于Excel 2007的函數(shù)眾多，功能很強，所以我們要使用Excel 的幫助文件。Excel 2007 提供了幫助功能： “函數(shù)參數(shù)”對話框和Excel的幫助。函數(shù)參數(shù)對話框是尋找?guī)椭淖詈棉k法。,1.5 獲得Excel 函數(shù)的幫助,幫助的鏈接,1.5 獲得Excel 函數(shù)的幫助,使用Excel幫助 有關(guān)函數(shù)的幫助非常全面。在每個函數(shù)的幫助主題中都可以找到下面的內(nèi)容 函數(shù)的語法 “注釋”提供了函數(shù)的疑難解答 實例 相關(guān)函數(shù),1.5 獲得Excel 函數(shù)的幫助,數(shù)學(xué)函數(shù) 基本數(shù)學(xué)函數(shù) 舍入和取整函數(shù) 求和函數(shù),第2節(jié) 數(shù)學(xué)函數(shù),

6、2.1 數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù),舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機排號 學(xué)校組織考試時，經(jīng)常需要將學(xué)生的學(xué)號打并隨機 編排考號，使用RAND隨機函數(shù)實現(xiàn)以上功能。 RAND函數(shù)的格式： RAND(),在單元格D2內(nèi)輸入公式“=RAND()”并向下填充，這樣在D列各單元格內(nèi)生存了0-1之間的任意數(shù),2.1 數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù),舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機排號 按列D “排序”,可以看到B、C二列已無序排列,再在A2單元格中輸入第一個考號,并按序列向下填充,將D列刪除或清空數(shù)據(jù),2.1 數(shù)學(xué)函數(shù)-舍入和取整函數(shù),2.1 數(shù)學(xué)函數(shù)-求和函數(shù),2.1 數(shù)學(xué)函數(shù)-求和函數(shù),舉例：某學(xué)校的考試成

7、績統(tǒng)計表，要統(tǒng)計女同學(xué)的英語成績總和,2.1 數(shù)學(xué)函數(shù)-求和函數(shù),舉例：某學(xué)校的考試成績統(tǒng)計表，要統(tǒng)計女同學(xué)的英語成績總和。此例問題可以使用SUMIF函數(shù) SUMIF(range，criteria，sum_range),條件判斷的單元格區(qū)域,判定條件,需要求和的單元格、區(qū)域或引用,2.1 數(shù)學(xué)函數(shù)-求和函數(shù),舉例：某學(xué)校的考試成績統(tǒng)計表，要統(tǒng)計女同學(xué)的英語成績總和。此例問題可以使用SUMIF函數(shù) 在求和的單元格輸入公式： =SUMIF(B2:B4,”=女”,F2:F9) 即得到結(jié)果,2.1 數(shù)學(xué)函數(shù)-求和函數(shù),舉例：COUNTIF 功能：為滿足指定條件的單元計數(shù) 格式：Countif( 計數(shù)區(qū)

8、域, “計數(shù)條件” ) = countif（D2:D9,“80”）,邏輯函數(shù)是用來判斷真假值，或者進行復(fù)合檢驗的Excel函數(shù)。在Excel中提供了七種邏輯函數(shù),第3節(jié) 邏輯函數(shù),第3節(jié) 邏輯函數(shù),舉例：IF 在學(xué)校的管理工作中，常常要對學(xué)生的考試成績進行統(tǒng)計分析。假定現(xiàn)有某班級的考試成績，我們按照各科平均分對學(xué)生的考試成績進行綜合評定。評定規(guī)則：如果平均分超過90時，評定為優(yōu)秀，如果平均分小于90并且超過60則認(rèn)為是合格的，否則記作不合格,第3節(jié) 邏輯函數(shù),舉例：根據(jù)規(guī)則，我們在綜合評定中使用IF函數(shù),IF(logical_test,value_if_true,value_if_false)

9、,logical_test 為 TRUE 時返回的值,測試表達式,logical_test 為 FALSE時返回的值,第3節(jié) 邏輯函數(shù),舉例：根據(jù)規(guī)則，我們在綜合評定中使用IF函數(shù) (以B7為例）： “ =IF(B660,IF(B690,”優(yōu)秀“,”合格“),”不合格“)”,為英語成績建立五級分轉(zhuǎn)換 1、if(E260,if(E270,if(E280, if(E290,優(yōu)秀，良好)，中等)，及格)，不及格）,第3節(jié) 邏輯函數(shù),日期函數(shù)=TODAY() 邏輯函數(shù)=IF(F40,IF(F430,即將到期),過期),第3節(jié) 邏輯函數(shù),舉例：OR AND OR (logical1,logical2,l

10、ogicaln) 功能：當(dāng)只要有一個參數(shù)的計算結(jié)果為TRUE時,OR函數(shù)的計算結(jié)果為TRUE, 否則，計算結(jié)果為FALSE AND (logical1,logical2,logicaln) 功能：當(dāng)所有參數(shù)的計算結(jié)果都是TRUE時,AND函數(shù)的計算結(jié)果為TRUE, 否則，計算結(jié)果為FALSE,第3節(jié) 邏輯函數(shù),舉例：OR AND,第3節(jié) 邏輯函數(shù),舉例：OR AND,第4節(jié)日期和時間函數(shù),第4節(jié)日期和時間函數(shù),第5節(jié) 文本函數(shù),第5節(jié) 文本函數(shù),第5節(jié) 文本函數(shù),第6節(jié) 查找和引用函數(shù)-查找函數(shù),第6節(jié) 查找函數(shù),第6節(jié) 查找和引用函數(shù)-查找函數(shù),把銷量表的各產(chǎn)品銷量快速導(dǎo)入到上報表的上報數(shù)據(jù)

11、欄一列,第6節(jié) 查找函數(shù),銷量表,上報表,指定查找對象,第6節(jié) 查找函數(shù),第6節(jié) 查找和引用函數(shù)-查找（新函數(shù),第6節(jié) 查找函數(shù),銷量表（源）,查找區(qū)域,上報表,指定查找對象,在查找區(qū)域中的序列,返回值,第6節(jié) 查找函數(shù),第6節(jié) 查找和引用函數(shù)-引用函數(shù),第7節(jié) 引用函數(shù),第二章 分類匯總,一、僅對某列進行分類匯總 例如上例中需要對各城市的銷售量進行分類匯總，方法如下： 1. 首先對數(shù)據(jù)按需要分類匯總的列（本例為“城市”列）進行排序。 選擇“城市”列中的任意單元格，在Excel 2007中，選擇功能區(qū)中“數(shù)據(jù)”選項卡，在“排序和篩選”組中單擊 2. 選擇數(shù)據(jù)區(qū)域中的某個單元格，Excel 2007，則在“數(shù)據(jù)”選項卡的“分級顯示”組中單擊“分類匯總”。 3. 在彈出的“分類匯總”對話框中，在“分類字段”下選擇“城市”，在“匯總方式”中選擇某種匯總方式，可供選擇的匯總方式有“求和”、“計數(shù)”、“平均值”等，本例中選擇默認(rèn)的“求和”。在“選定匯總項”下僅選擇“銷售額”。,4.單擊確定，Excel將按城市