Excel表格數據分析及處理教程

1、Excel 2007Excel 2007 數據分析及應用數據分析及應用4321第一章第一章 常用函數常用函數u 函數的概述函數的概述u 數學函數數學函數u 邏輯函數邏輯函數u 日期和時間函數日期和時間函數u 文本函數文本函數u查找和引用函數查找和引用函數第第1節(jié)節(jié) 函數的概述函數的概述u 函數的基本概念函數的基本概念u 函數的分類函數的分類u 使用函數使用函數u 使用函數的優(yōu)勢使用函數的優(yōu)勢u 獲得獲得Excel 函數的幫助函數的幫助預先定義，執(zhí)行計算、分析預先定義，執(zhí)行計算、分析等處理數據任務的特殊公式等處理數據任務的特殊公式分為分為1212類、類、356356個函數個函數直接使用、利用直接

2、使用、利用“插入函數插入函數”對話框對話框簡化公式、實現特殊運算、實簡化公式、實現特殊運算、實現智能判斷、提高工作效率現智能判斷、提高工作效率使用使用“函數參數函數參數”對話框對話框使用使用ExcelExcel幫助幫助如果函數以公式的形式出現，請在函數名稱前面鍵入等號 u 函數的結構函數的結構 SUM(A10,B5:B10,50,37)1.1 函數的基本概念函數的基本概念函數名函數名參數，可以是常量、邏輯值、參數，可以是常量、邏輯值、數組、錯誤值或單元格引用等數組、錯誤值或單元格引用等 Excel 2007的函數是指預先定義好，執(zhí)行計算、分析等處理數據任務的特殊公式1.2 函數的分類函數的分類

3、 Excel 2007提供了的函數?？煞譃?2類：分分 類類功功 能能 簡簡 介介數據庫函數數據庫函數對數據清單中的數據進行分析、查找、計算等對數據清單中的數據進行分析、查找、計算等日期與時間日期與時間對日期和時間進行計算、設置及格式化處理對日期和時間進行計算、設置及格式化處理工程函數工程函數用于工程數據分析與處理用于工程數據分析與處理信息函數信息函數對單元格或公式中數據類型進行判定對單元格或公式中數據類型進行判定財務函數財務函數進行財務分析及財務數據的計算進行財務分析及財務數據的計算邏輯函數邏輯函數進行邏輯判定、條件檢查進行邏輯判定、條件檢查統計函數統計函數對工作表數據進行統計、分析對工作表

4、數據進行統計、分析查找函數查找函數查找特定的數據或引用公式中的特定信息查找特定的數據或引用公式中的特定信息文本函數文本函數對公式、單格中的字符、文本進行格式化或運算對公式、單格中的字符、文本進行格式化或運算數學函數數學函數進行數學計算等進行數學計算等外部函數外部函數進行外部函數調用及數據庫的鏈接查詢等功能進行外部函數調用及數據庫的鏈接查詢等功能自定義函數自定義函數用戶用用戶用vba編寫，用于完成特定功能的函數編寫，用于完成特定功能的函數u利用利用“插入函數插入函數”對話框使用函數對話框使用函數 1.3 使用函數使用函數選中要顯示結果的單元格在“公式”選項卡“函數庫”組，單擊“插入函數”u簡化公

5、式簡化公式 函數可以使一些復雜的公式更易于使用，使復雜的數學表達式輸入更加簡化。比如，要計算B3:D7區(qū)域中數據的平均值時，使用公式計算量大，需要寫成：“=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/15”而使用函數將相當簡單，只要寫成：“=AVERAGE(B3:D7)” 1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢u實現特殊運算實現特殊運算 在日常的工作中，有一些計算使用公式不但復雜，而且不具有特定的專業(yè)知識還無法表達，那么就可以使用現有的函數進行特殊運算 例如，按揭貸款的等額分期償還額的計算必須用到高等數學的知識，而使用PMT函數(計算在固定利率

6、下，貸款的等額分期償還額)就相當簡單 PMT(rate,nper,pv,fv,type) 1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢貸款利率貸款利率付款期總數付款期總數未來值未來值現值現值付款時間付款時間u實現特殊運算實現特殊運算 1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢貸款利率貸款利率付款期總數付款期總數現值現值u實現智能判斷實現智能判斷 用函數還可以實現智能判斷 例如，在學校，老師統計學生的考試成績，如果，其成績大于等于80則為“優(yōu)秀”,如果成績大于等于59且小于80則為“及格”,否則為“不及格”。這類問題用人工計算很不方便，可以使用函數來進行自動化處理 1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢u實現智

7、能判斷實現智能判斷 1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢u提高工作效率提高工作效率 在學校的期末總結中，常會需要按學生的考試成績進行排位的問題，當然可以使用“排序”按鈕來完成，但是這樣做比較麻煩，如果使用排位函數RANK就比較方便，尤其是紀錄很多、相同分數很多的情況下，不用RANK函數其工作量不可想像 RANK(number,ref,order)1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢需要排位的數字需要排位的數字引用的區(qū)域，絕對地址引用的區(qū)域，絕對地址排位的方式排位的方式u提高工作效率提高工作效率1.4 使用函數的優(yōu)勢使用函數的優(yōu)勢 由于Excel 2007的函數眾多，功能很強，所以我們要使用Ex

8、cel 的幫助文件。Excel 2007 提供了幫助功能： “函數參數”對話框和Excel的幫助。函數參數對話框是尋找?guī)椭淖詈棉k法。1.5 獲得獲得Excel 函數的幫助函數的幫助幫助的鏈接幫助的鏈接1.5 獲得獲得Excel 函數的幫助函數的幫助u使用使用Excel幫助幫助 有關函數的幫助非常全面。在每個函數的幫助主題中都可以找到下面的內容 函數的語法函數的語法 “注釋注釋”提供了函數的疑難解答提供了函數的疑難解答 實例實例 相關函數相關函數1.5 獲得獲得Excel 函數的幫助函數的幫助u 數學函數數學函數 I.基本數學函數 II. 舍入和取整函數III. 求和函數 第第2節(jié)節(jié) 數學函數

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

10、UND 按指定位數四舍五入數值ROUNDUP 、ROUNDDOWN 按指定位數向上或向下四舍五入數值TRUNC 將數值的小數部分截去，返回整數INT 將數值向下舍入計取整ODD、EVEN 將數值舍入為奇或偶數2.1 數學函數數學函數-求和函數求和函數SUM計算某一單元格區(qū)域中所有數值之和SUBTOTAL返回數據清單或數據庫中的分類匯總SUMIF根據指定條件對若干單元格、區(qū)域或引用求和SUMPRODUCT計算數組間對應的元素相乘，并返回乘積之和SUMSQ計算所有參數的平方和SUMX2MY2計算兩數組中對應數值的平方差之和SUMX2PY2計算兩數組中對應數值的平方和的總和SUMXMY2計算兩數組中

11、對應數值之差的平方和2.1 數學函數數學函數-求和函數求和函數 舉例：舉例：某學校的考試成績統計表，要統計女同學的英語成績總和2.1 數學函數數學函數-求和函數求和函數 舉例：舉例：某學校的考試成績統計表，要統計女同學的英語成績總和。此例問題可以使用SUMIF函數 SUMIF(range，criteria，sum_range)條件判斷的單元格區(qū)域條件判斷的單元格區(qū)域判定條件判定條件需要求和的單元格、區(qū)域或引用需要求和的單元格、區(qū)域或引用2.1 數學函數數學函數-求和函數求和函數 舉例：舉例：某學校的考試成績統計表，要統計女同學的英語成績總和。此例問題可以使用SUMIF函數 在求和的單元格輸入公

12、式：在求和的單元格輸入公式： =SUMIF(B2:B4,”=女女”,F2:F9) 即得到結果即得到結果2.1 數學函數數學函數-求和函數求和函數 舉例：舉例：COUNTIF 功能：為滿足指定條件的單元計數功能：為滿足指定條件的單元計數 格式：格式：Countif( 計數區(qū)域計數區(qū)域, “計數條件計數條件” ) = countif（D2:D9,“80”） 邏輯函數是用來判斷真假值，或者進行復合檢驗的Excel函數。在Excel中提供了七種邏輯函數第第3節(jié)節(jié) 邏輯函數邏輯函數IF 對指定的條件計算結果為 TRUE 或 FALSE，返回不同的結果IFERROR 如果公式計算出錯誤則返回您指定的值；否

13、則返回公式結果AND 當AND的參數全部為TRUE時，返回結果為TRUE，否則為FALSE OR 當OR的參數中，如果任一參數為TRUE，返回結果 TRUE，否則為FALSE NOT NOT函數用于對參數值求反TRUE 返回邏輯值 TRUE FALSE 返回邏輯值 FALSE 第第3節(jié)節(jié) 邏輯函數邏輯函數 舉例舉例：IF 在學校的管理工作中，常常要對學生的考試成績進行統計分析。假定現有某班級的考試成績，我們按照各科平均分對學生的考試成績進行綜合評定。評定規(guī)則：如果平均分超過90時，評定為優(yōu)秀，如果平均分小于90并且超過60則認為是合格的，否則記作不合格第第3節(jié)節(jié) 邏輯函數邏輯函數 舉例舉例：根

14、據規(guī)則，我們在綜合評定中使用IF函數IF(logical_test,value_if_true,value_if_false)logical_test 為 TRUE 時返回的值測試表達式測試表達式logical_test 為 FALSE時返回的值第第3節(jié)節(jié) 邏輯函數邏輯函數 舉例舉例：根據規(guī)則，我們在綜合評定中使用IF函數 (以B7為例）： “ =IF(B660,IF(B690,”優(yōu)秀“,”合格“),”不合格“)”E260E280E290E270為英語成績建立五級分轉換為英語成績建立五級分轉換1、if(E260,if(E270,if(E280, if(E290,優(yōu)秀，良好優(yōu)秀，良好)，中等，中

15、等)，及格，及格)，不及格），不及格）第第3節(jié)節(jié) 邏輯函數邏輯函數日期函數日期函數=TODAY()邏輯函數邏輯函數=IF(F40,IF(F430,即將到期即將到期),過期過期)第第3節(jié)節(jié) 邏輯函數邏輯函數 舉例舉例：OR AND OR (logical1,logical2,logicaln) 功能功能：當只要有一個參數的計算結果為TRUE時,OR函數的計算結果為TRUE, 否則，計算結果為FALSE AND (logical1,logical2,logicaln) 功能功能：當所有參數的計算結果都是TRUE時,AND函數的計算結果為TRUE, 否則，計算結果為FALSE第第3節(jié)節(jié) 邏輯函數邏輯

16、函數 舉例舉例：OR AND第第3節(jié)節(jié) 邏輯函數邏輯函數 舉例舉例：OR AND第第4節(jié)日期和時間函數節(jié)日期和時間函數DATA返回當前日期和時間NOW返回當前日期和時間TODAY返回當前日期YEAR返回日期中的年份MONTH返回日期中的月份DAY返回日期的天數HOUR返回時間值的小時數第第4節(jié)日期和時間函數節(jié)日期和時間函數第第5節(jié)節(jié) 文本函數文本函數LEFT根據所指定的字符數，返回文本字符串中左邊的幾個字符MID返回文本字符串中從指定位置開始的特定數目的字符RIGHT根據所指定的字符數，返回文本字符串中右邊的幾個字符LOWER將一個文字串中的所有大寫字母轉換為小寫字母，但不改變文本中的非字母的

17、字符UPPER將文本轉換成大寫形式PROPER 將文本字符串的首字母及任何非字母字符之后的首字母轉換成大寫。將其余的字母轉換成小寫 Trim清除字符串中的空白。 EXACT 比較兩個字符串，如果它們完全相同，則返回 TRUE；否則，返回 FALSE第第5節(jié)節(jié) 文本函數文本函數商品編號品名單位商品規(guī)格上報數量上報數量0101001冠心寧支,10ml3000000101014骨瓜支,2ml:10mg2000000101016醒腦靜10ML支,10ml2000000101017醒腦靜2ML支,2ml1840000103001扶10袋盒,50mg*10袋2477000103002扶6袋盒,50mg*6

18、袋00103010扶16袋盒,50mg*16袋001050091MG注射用胸腺五肽瓶,1mg0010501210MG注射用胸腺五肽瓶,10mg2554000105010胸腺五肽1MG（預充式）支,1ml：1mg2360000105011胸腺五肽10MG（預充式）支,1ml：10mg4521000103005雙丹片（48片）瓶，00103006雙丹片（72片）瓶，00203008尼群地平（48粒）盒，00203009尼群地平（60粒）瓶，0第第5節(jié)節(jié) 文本函數文本函數尊敬的公司領導您好：今日銷售100筆，銷售金額2000萬9097元,本月累計銷售金額40000萬2321元各藥品本月累計銷售明細如

19、下：全部第一段第二段尊敬的公司領導您好：今日銷售100筆，銷售金額2000萬9097元,本月累計銷售金額40000萬2321元各藥品本月累計銷售明細如下：冠心寧330600支,骨瓜822000支,醒腦靜10ML166560支,醒腦靜2ML84000支,扶10袋47700盒,扶6袋0盒,扶16袋0盒,1MG注射用胸腺五肽0瓶,10MG注射用胸腺五肽400瓶,胸腺五肽1MG（預充式）6000支,胸腺五肽10MG（預充式）2100支,雙丹片（48片）0瓶，雙丹片（72片）0瓶，尼群地平（48粒）0盒，尼群地平（60粒）0瓶，腸蠕醫(yī)用膠貼3200袋，5*3.5止血紗1200片，5*7.5止血紗4600

20、片,4UG加壓素106200支,15UG加壓素5400支,胸腺法新6400瓶,奧曲肽0.05MG（中和）9000瓶,奧曲肽0.01MG（中和）0瓶,生長抑素3.0MG（中和）300瓶,尊敬的公司領導您好：今日銷售100筆，銷售金額2000萬9097元,本月累計銷售金額40000萬2321元各藥品本月累計銷售明細如下：冠心寧330600支,骨瓜822000支,醒腦靜10ML166560支,醒腦靜2ML84000支,扶10袋47700盒,扶6袋0盒,扶16袋0盒,1MG注射用胸腺五肽0瓶,10MG注射用胸腺五肽400瓶,胸腺五肽1MG（預充式）6000支,胸腺五肽10MG（預充式）2100支,雙丹

21、片（48片）0瓶，雙丹片（72片）0瓶，尼群地平（48粒）0盒，尼群地平（60粒）0瓶，腸蠕醫(yī)用膠貼3200袋，5*3.5止血紗1200片，5*7.5止血紗4600片,4UG加壓素106200支,15UG加壓素5400支,胸腺法新6400瓶,奧曲肽0.05MG（中和）9000瓶,奧曲肽0.01MG（中和）0瓶,生長抑素3.0MG（中和）300瓶,第第6節(jié)節(jié) 查找和引用函數查找和引用函數-查找函數查找函數 LOOKUP返回一行或一列區(qū)域中或者數組中的某個值 VLOOKUP在表格數組首列查找指定的值，并返回表格數組當前行中指定列的值HLOOKUP在表格數組首行查找指定的值，并返回表格數組當前行中指

22、定行的值CHOOSE可以根據給定的索引值，從多達254個待選參數中選出相應的值第第6節(jié)節(jié) 查找函數查找函數第第6節(jié)節(jié) 查找和引用函數查找和引用函數-查找函數查找函數 把銷量表的各產品銷量快速導入到上報表的上報數據欄一列第第6節(jié)節(jié) 查找函數查找函數編號編號品名品名單位單位銷量銷量010501210MG注射用胸腺五肽瓶,10001050091MG注射用胸腺五肽瓶,2000103010扶16袋盒,3000103002扶6袋盒,4000101017醒腦靜2ML支,5000103001扶10袋盒,8000101016醒腦靜10ML支,10000101014骨瓜支,20000101001冠心寧支,3000

23、 銷量表商品編號商品編號品名品名單位單位商品規(guī)格商品規(guī)格上報數量上報數量0101001冠心寧支,10ml30000101014骨瓜支,2ml:10mg20000101016醒腦靜10ML支,10ml10000101017醒腦靜2ML支,2ml5000103001扶10袋盒,50mg*10袋8000103002扶6袋盒,50mg*6袋4000103010扶16袋盒,50mg*16袋30001050091MG注射用胸腺五肽瓶,1mg200010501210MG注射用胸腺五肽瓶,10mg100 上報表指定查找對象指定查找對象第第6節(jié)節(jié) 查找函數查找函數第第6節(jié)節(jié) 查找和引用函數查找和引用函數-查找（

24、新函數查找（新函數 第第6節(jié)節(jié) 查找函數查找函數編號編號品名品名單位單位銷量銷量010501210MG注射用胸腺五肽瓶,10001050091MG注射用胸腺五肽瓶,2000103010扶16袋盒,3000103002扶6袋盒,4000101017醒腦靜2ML支,5000103001扶10袋盒,8000101016醒腦靜10ML支,10000101014骨瓜支,20000101001冠心寧支,3000 銷量表（源）查找區(qū)域查找區(qū)域商品編號商品編號品名品名單位單位商品規(guī)格商品規(guī)格上報數量上報數量0101001冠心寧支,10ml30000101014骨瓜支,2ml:10mg20000101016醒腦

25、靜10ML支,10ml10000101017醒腦靜2ML支,2ml5000103001扶10袋盒,50mg*10袋8000103002扶6袋盒,50mg*6袋4000103010扶16袋盒,50mg*16袋30001050091MG注射用胸腺五肽瓶,1mg200010501210MG注射用胸腺五肽瓶,10mg100 上報表 指定查找對象指定查找對象在查找在查找區(qū)域中區(qū)域中的序列的序列返回值返回值第第6節(jié)節(jié) 查找函數查找函數第第6節(jié)節(jié) 查找和引用函數查找和引用函數-引用函數引用函數 ADDRESS按照給定的行號和列號，建立文本類型的單元格地址COLUMN返回指定單元格引用 的列號。 ROW 返回

26、指定單元格引用 的行號。AREAS返回引用中包含的區(qū)域個數COLUMNS返回數組或引用的列數 ROWS 返回引用或數組的行數。INDEX返回表格或區(qū)域中的數值或對數值的引用 第第7節(jié)節(jié) 引用函數引用函數第二章第二章 分類匯總分類匯總一、僅對某列進行分類匯總一、僅對某列進行分類匯總例如上例中需要對各城市的銷售量進行分類匯總，方法如下： 1. 首先對數據按需要分類匯總的列（本例為“城市”列）進行排序。 選擇“城市”列中的任意單元格，在Excel 2007中，選擇功能區(qū)中“數據”選項卡，在“排序和篩選”組中單擊 2. 選擇數據區(qū)域中的某個單元格，Excel 2007，則在“數據”選項卡的“分級顯示”組中單擊“分類匯總”。 3. 在彈出的“分類匯總”對話框中，在“分類字段”下選擇“城市”，在“匯總方式”中選擇某種匯總方式，可供選擇的匯總方式有“求和”、“計數”、“平均值”等，本例中選擇默認的“求和”。在“選定匯總項”下僅選擇“銷售額”。4.單擊確定，Excel將按城市進行分類匯總。第二章第二章 分類匯總分類匯總二、對多列進行分類匯總二、對多列進行分類匯總 先按上述操作對“城市”進行分類匯總，再對“商品名稱”列進行分類匯總（嵌套分類匯總）。 再打開“分類匯總”對話框，在“分類字段”下選擇“商品名稱”，取消選擇“替換當前分類匯總”，單擊“確定”。第二章第二章 分類匯總分類匯總第三章第三章