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

Excel2007

數據分析及應用

2021/5/91目錄常用函數4321分類匯總數據透視圖表制作2021/5/92第一章常用函數

函數的概述數學函數邏輯函數日期和時間函數文本函數查找和引用函數2021/5/93第1節(jié)函數的概述

函數的基本概念函數的分類

使用函數

使用函數的優(yōu)勢

獲得Excel函數的幫助預先定義，執(zhí)行計算、分析等處理數據任務的特殊公式分為12類、356個函數直接使用、利用“插入函數”對話框簡化公式、實現特殊運算、實現智能判斷、提高工作效率使用“函數參數”對話框使用Excel幫助2021/5/94如果函數以公式的形式出現，請在函數名稱前面鍵入等號

=函數的結構

SUM(A10,B5:B10,50,37)1.1函數的基本概念函數名參數，可以是常量、邏輯值、數組、錯誤值或單元格引用等Excel2007的函數是指預先定義好，執(zhí)行計算、分析等處理數據任務的特殊公式2021/5/951.2函數的分類Excel2007提供了的函數?？煞譃?2類：分類功能簡介數據庫函數對數據清單中的數據進行分析、查找、計算等日期與時間對日期和時間進行計算、設置及格式化處理工程函數用于工程數據分析與處理信息函數對單元格或公式中數據類型進行判定財務函數進行財務分析及財務數據的計算邏輯函數進行邏輯判定、條件檢查統計函數對工作表數據進行統計、分析查找函數查找特定的數據或引用公式中的特定信息文本函數對公式、單格中的字符、文本進行格式化或運算數學函數進行數學計算等外部函數進行外部函數調用及數據庫的鏈接查詢等功能自定義函數用戶用vba編寫，用于完成特定功能的函數2021/5/96利用“插入函數”對話框使用函數

1.3使用函數選中要顯示結果的單元格在“公式”選項卡“函數庫”組，單擊“插入函數”2021/5/97簡化公式函數可以使一些復雜的公式更易于使用，使復雜的數學表達式輸入更加簡化。比如，要計算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)勢2021/5/98實現特殊運算在日常的工作中，有一些計算使用公式不但復雜，而且不具有特定的專業(yè)知識還無法表達，那么就可以使用現有的函數進行特殊運算

例如，按揭貸款的等額分期償還額的計算必須用到高等數學的知識，而使用PMT函數(計算在固定利率下，貸款的等額分期償還額)就相當簡單

PMT(rate,nper,pv,fv,type)

1.4使用函數的優(yōu)勢貸款利率付款期總數未來值現值付款時間2021/5/99實現特殊運算

1.4使用函數的優(yōu)勢貸款利率付款期總數現值2021/5/910實現智能判斷用函數還可以實現智能判斷

例如，在學校，老師統計學生的考試成績，如果，其成績大于等于80則為“優(yōu)秀”,如果成績大于等于59且小于80則為“及格”,否則為“不及格”。這類問題用人工計算很不方便，可以使用函數來進行自動化處理

1.4使用函數的優(yōu)勢2021/5/911實現智能判斷

1.4使用函數的優(yōu)勢2021/5/912提高工作效率

在學校的期末總結中，常會需要按學生的考試成績進行排位的問題，當然可以使用“排序”按鈕來完成，但是這樣做比較麻煩，如果使用排位函數RANK就比較方便，尤其是紀錄很多、相同分數很多的情況下，不用RANK函數其工作量不可想像

RANK(number,ref,order)1.4使用函數的優(yōu)勢需要排位的數字引用的區(qū)域，絕對地址排位的方式2021/5/913提高工作效率1.4使用函數的優(yōu)勢2021/5/914由于Excel2007的函數眾多，功能很強，所以我們要使用Excel的幫助文件。Excel2007提供了幫助功能：“函數參數”對話框和Excel的幫助。函數參數對話框是尋找?guī)椭淖詈棉k法。1.5獲得Excel函數的幫助2021/5/915幫助的鏈接1.5獲得Excel函數的幫助2021/5/916使用Excel幫助

有關函數的幫助非常全面。在每個函數的幫助主題中都可以找到下面的內容

函數的語法

“注釋”提供了函數的疑難解答實例相關函數1.5獲得Excel函數的幫助2021/5/917

數學函數

基本數學函數

舍入和取整函數求和函數

第2節(jié)數學函數2021/5/9182.1數學函數-基本數學函數

舉例：用函數RAND函數為學生隨機排號學校組織考試時，經常需要將學生的學號打并隨機編排考號，使用RAND隨機函數實現以上功能。RAND函數的格式：

RAND()在單元格D2內輸入公式“=RAND()”并向下填充，這樣在D列各單元格內生存了0-1之間的任意數2021/5/9192.1數學函數-基本數學函數

舉例：用函數RAND函數為學生隨機排號按列D

“排序”,可以看到B、C二列已無序排列,再在A2單元格中輸入第一個考號,并按序列向下填充,將D列刪除或清空數據2021/5/9202.1數學函數-舍入和取整函數ROUND按指定位數四舍五入數值ROUNDUP、ROUNDDOWN按指定位數向上或向下四舍五入數值TRUNC將數值的小數部分截去，返回整數INT將數值向下舍入計取整ODD、EVEN將數值舍入為奇或偶數2021/5/9212.1數學函數-求和函數SUM計算某一單元格區(qū)域中所有數值之和SUBTOTAL返回數據清單或數據庫中的分類匯總SUMIF根據指定條件對若干單元格、區(qū)域或引用求和SUMPRODUCT計算數組間對應的元素相乘，并返回乘積之和SUMSQ計算所有參數的平方和SUMX2MY2計算兩數組中對應數值的平方差之和SUMX2PY2計算兩數組中對應數值的平方和的總和SUMXMY2計算兩數組中對應數值之差的平方和2021/5/9222.1數學函數-求和函數舉例：某學校的考試成績統計表，要統計女同學的英語成績總和

2021/5/9232.1數學函數-求和函數舉例：某學校的考試成績統計表，要統計女同學的英語成績總和。此例問題可以使用SUMIF函數SUMIF(range，criteria，sum_range)

條件判斷的單元格區(qū)域判定條件需要求和的單元格、區(qū)域或引用2021/5/9242.1數學函數-求和函數舉例：某學校的考試成績統計表，要統計女同學的英語成績總和。此例問題可以使用SUMIF函數在求和的單元格輸入公式：=SUMIF(B2:B4,”=女”,F2:F9)

即得到結果

2021/5/9252.1數學函數-求和函數舉例：COUNTIF

功能：為滿足指定條件的單元計數格式：Countif(計數區(qū)域,“計數條件”)=countif（D2:D9,“>80”）

2021/5/926邏輯函數是用來判斷真假值，或者進行復合檢驗的Excel函數。在Excel中提供了七種邏輯函數第3節(jié)邏輯函數IF對指定的條件計算結果為

TRUE或

FALSE，返回不同的結果IFERROR如果公式計算出錯誤則返回您指定的值；否則返回公式結果AND當AND的參數全部為TRUE時，返回結果為TRUE，否則為FALSEOR當OR的參數中，如果任一參數為TRUE，返回結果

TRUE，否則為FALSENOTNOT函數用于對參數值求反TRUE返回邏輯值

TRUEFALSE返回邏輯值

FALSE2021/5/927第3節(jié)邏輯函數

舉例：IF

在學校的管理工作中，常常要對學生的考試成績進行統計分析。假定現有某班級的考試成績，我們按照各科平均分對學生的考試成績進行綜合評定。評定規(guī)則：如果平均分超過90時，評定為優(yōu)秀，如果平均分小于90并且超過60則認為是合格的，否則記作不合格2021/5/928第3節(jié)邏輯函數

舉例：根據規(guī)則，我們在綜合評定中使用IF函數IF(logical_test,value_if_true,value_if_false)logical_test為TRUE時返回的值測試表達式logical_test為FALSE時返回的值2021/5/929第3節(jié)邏輯函數

舉例：根據規(guī)則，我們在綜合評定中使用IF函數(以B7為例）：“=IF(B6>60,IF(B6>90,”優(yōu)秀“,”合格“),”不合格“)”2021/5/930不及格E2>60E2>80E2>90E2>70及格中等良好優(yōu)秀為英語成績建立五級分轉換1、if(E2>60,if(E2>70,if(E2>80,if(E2>90,優(yōu)秀，良好)，中等)，及格)，不及格）2021/5/931第3節(jié)邏輯函數日期函數=TODAY()邏輯函數=IF(F4>0,IF(F4>30,"","即將到期"),"過期")2021/5/932第3節(jié)邏輯函數

舉例：ORANDOR(logical1,logical2,……,logicaln)

功能：當只要有一個參數的計算結果為TRUE時,OR函數的計算結果為TRUE,否則，計算結果為FALSEAND(logical1,logical2,……,logicaln)

功能：當所有參數的計算結果都是TRUE時,AND函數的計算結果為TRUE,否則，計算結果為FALSE2021/5/933第3節(jié)邏輯函數

舉例：ORAND2021/5/934第3節(jié)邏輯函數

舉例：ORAND2021/5/935第4節(jié)日期和時間函數DATA返回當前日期和時間NOW返回當前日期和時間TODAY返回當前日期YEAR返回日期中的年份MONTH返回日期中的月份DAY返回日期的天數HOUR返回時間值的小時數2021/5/936第4節(jié)日期和時間函數2021/5/937第5節(jié)文本函數LEFT根據所指定的字符數，返回文本字符串中左邊的幾個字符MID返回文本字符串中從指定位置開始的特定數目的字符RIGHT根據所指定的字符數，返回文本字符串中右邊的幾個字符LOWER將一個文字串中的所有大寫字母轉換為小寫字母，但不改變文本中的非字母的字符UPPER將文本轉換成大寫形式PROPER將文本字符串的首字母及任何非字母字符之后的首字母轉換成大寫。將其余的字母轉換成小寫

Trim清除字符串中的空白。

EXACT比較兩個字符串，如果它們完全相同，則返回

TRUE；否則，返回

FALSE2021/5/938第5節(jié)文本函數商品編號品名單位商品規(guī)格上報數量0101001冠心寧支,10ml3000000101014骨瓜支,2ml:10mg2000000101016醒腦靜10ML支,10ml2000000101017醒腦靜2ML支,2ml1840000103001扶10袋盒,50mg*10袋2477000103002扶6袋盒,50mg*6袋00103010扶16袋盒,50mg*16袋001050091MG注射用胸腺五肽瓶,1mg0010501210MG注射用胸腺五肽瓶,10mg2554000105010胸腺五肽1MG（預充式）支,1ml：1mg2360000105011胸腺五肽10MG（預充式）支,1ml：10mg4521000103005雙丹片（48片）瓶，

00103006雙丹片（72片）瓶，

00203008尼群地平（48粒）盒，

00203009尼群地平（60粒）瓶，

02021/5/939第5節(jié)文本函數尊敬的公司領導您好：今日銷售100筆，銷售金額2000萬9097元,本月累計銷售金額40000萬2321元各藥品本月累計銷售明細如下：全部第一段第二段尊敬的公司領導您好：今日銷售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片,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支,雙丹片（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瓶,2021/5/940

第6節(jié)查找和引用函數-查找函數

LOOKUP返回一行或一列區(qū)域中或者數組中的某個值VLOOKUP在表格數組首列查找指定的值，并返回表格數組當前行中指定列的值HLOOKUP在表格數組首行查找指定的值，并返回表格數組當前行中指定行的值CHOOSE可以根據給定的索引值，從多達254個待選參數中選出相應的值第6節(jié)查找函數2021/5/941

第6節(jié)查找和引用函數-查找函數

把銷量表的各產品銷量快速導入到上報表的上報數據欄一列第6節(jié)查找函數編號品名單位銷量010501210MG注射用胸腺五肽瓶,10001050091MG注射用胸腺五肽瓶,2000103010扶16袋盒,3000103002扶6袋盒,4000101017醒腦靜2ML支,5000103001扶10袋盒,8000101016醒腦靜10ML支,10000101014骨瓜支,20000101001冠心寧支,3000

銷量表2021/5/942商品編號品名單位商品規(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é)查找函數2021/5/943

第6節(jié)查找和引用函數-查找（新函數

第6節(jié)查找函數編號品名單位銷量010501210MG注射用胸腺五肽瓶,10001050091MG注射用胸腺五肽瓶,2000103010扶16袋盒,3000103002扶6袋盒,4000101017醒腦靜2ML支,5000103001扶10袋盒,8000101016醒腦靜10ML支,10000101014骨瓜支,20000101001冠心寧支,3000

銷量表（源）查找區(qū)域商品編號品名單位商品規(guī)格上報數量0101001冠心寧支,10ml30000101014骨瓜支,2ml:10mg20000101016醒腦靜10ML支,10ml10000101017醒腦靜2ML支,2ml5000103001扶10袋盒,50mg*10袋8000103002扶6袋盒,50mg*6袋4000103010扶16袋盒,50mg*16袋30001050091MG注射用胸腺五肽瓶,1mg200010501210MG注射用胸腺五肽瓶,10mg100

上報表指定查找對象在查找區(qū)域中的序列返回值2021/5/944第6節(jié)查找函數2021/5/945

第6節(jié)查找和引用函數-引用函數

ADDRESS按照給定的行號和列號，建立文本類型的單元格地址COLUMN返回指定單元格引用

的列號。ROW返回指定單元格引用

的行號。AREAS返回引用中包含的區(qū)域個數COLUMNS返回數組或引用的列數ROWS返回引用或數組的行數。INDEX返回表格或區(qū)域中的數值或對數值的引用第7節(jié)引用函數2021/5/946第二章分類匯總一、僅對某列進行分類匯總例如上例中需要對各城市的銷售量進行分類匯總，方法如下：

1.首先對數據按需要分類匯總的列（本例為“城市”列）進行排序。

選擇“城市”列中的任意單元格，在Excel2007中，選擇功能區(qū)中“數據”選項卡，在“排序和篩選”組中單擊

2.選擇數據區(qū)域中的某個單元格，Excel2007，則在“數據”選項卡的“分級顯示”組中單擊“分類匯總”。

3.在彈出的“分類匯總”對話框中，在“分類字段”下選擇“城市”，在“匯總方式”中選擇某種匯總方式，可供選擇的匯總方式有“求和”、“計數”、“平均值”等，本例中選擇默認的“求和”。在“選定匯總項”下僅選擇“銷售額”。2021/5/9474.單擊確定，Excel將按城市進行分類匯總。第二章分類匯總2021/5/948二、對多列進行分類匯總先按上述操作對“城市”進行分類匯總，再對“商品名稱”列進行分類匯總（嵌套分類匯總）。

再打開“分類匯總”對話框，在“分類字段”下選擇“商品名稱”，取消選擇“替換當前分類匯總”，單擊“確定”。第二章分類匯總2021/5/949第三章數據透視表數據透視表操作示例ABCDEFGHI3序號日期姓名品名單價數量金額運輸方式4097012003-3-1張三創(chuàng)維

3,0805

15,400汽車5097022003-3-1李四創(chuàng)維

3,6007

25,200