《Excel數據處理與可視化》（第二版） 課件 韓春玲 第1部分 數據采集整；第2部分 函數與公式

第1部分 數據采集整理----1.1數據錄入與基本設置1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式1．“0”數字占位符數字“0”代表占位符，如果單元格的內容大于指定占位符，則顯示實際數字；如果小于占位符的數量，則用0補足。1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式2．“#”數字占位符“#”數字占位符，只顯有意義的零，不顯示無意義的零。小數點后數字如大于“#”的數量,則按“#”的位數四舍五入。1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式3．“?”數字占位符“?”數字占位符，在小數點兩邊為無意義的零添加空格，以實現(xiàn)按小數點對齊。1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式4．“,”千分位分隔符“,”千分位分隔符。在數字中，每隔三位數加進一個逗號，也就是千位分隔符，以免數字太多不好讀取，1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式5.“@”文本占位符使用單個@，作用是引用固定文本。使用多個@，則可以重復文本，1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式6．“!”原樣顯示后面的符號在自定義格式中，“”、#、？等都是有特殊意義的字符，如果想在單元格中顯示這些字符，需要在前符號前加“!”，，1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式7．“*”重復后面的字符重復顯示*后面的字符，直到充滿整個列寬。1.1 數據錄入與基本設置1.1.1使用特殊符號自定義單元格格式8．顏色顯示符代碼：[紅色];[藍色];[黑色];[綠色]，顯示結果為正數為紅色，負數顯示藍色，零顯示黑色1.1 數據錄入與基本設置1.1.2設置數字以小數點對齊，使數值大小一目了然第1步：設置數據右對齊第2步：設置單元格格式，自定義單元格格式為：#.0?????，1.1 數據錄入與基本設置1.1.3單元格數據換行第1種方法：自動換行。第2種方法：Alt+Enter。1.1 數據錄入與基本設置1.1.4設置不能隔行隔列填寫數據1．打開“數據驗證”2．設置驗證條件3、設置出錯警告1.1 數據錄入與基本設置1.1.5設置傾斜列標簽單擊“開始”菜單的“對齊方式”功能區(qū)中的“方向”按鈕，可以沿對角或垂直方向旋轉文字，這是標記窄列的好方式，1.1 數據錄入與基本設置1.1.6單元格數據太多，加滾動條顯示第1步：插入文本控件第3步：關閉設計模式，輸入文字第2步：設置文本框屬性1.1 數據錄入與基本設置1.1.7凍結拆分窗格，輕松查看行列數據1．凍結行2．凍結列3．凍結行和列1.1 數據錄入與基本設置1.1.8輕松繪制單斜線、雙斜線表頭1．單斜線表頭。2．雙斜線表頭。1.1 數據錄入與基本設置1.1.9如何讓數字以“萬”為計數單位來顯示第1種方法：設置單元格格式第2種方法：選擇性粘貼除以10000。1.1 數據錄入與基本設置1.1.10設置僅能修改部分單元格數據第1步：解除“鎖定”。第2步：設置保護1.1 數據錄入與基本設置1.1.11隱藏工作表第1步：打開VBA對話框。第2步：設置要隱藏的工作表屬性。第1部分 數據采集整理----1.2合并單元格1.2 合并單元格1.2.1批量合并單元格第1步：“數據”→“分類匯總”第2步：“定位”→“定位條件”第3步：“開始”→“合并后居中”1.2 合并單元格1.2.2批量拆分合并單元格第1步：取消“合并后居中”第2步：“定位”→“空值”第3步：輸入“=A2”,按Ctrl+Enter組合鍵執(zhí)行計算1.2 合并單元格1.2.3合并單元格填充序號選中整個合并單元格區(qū)域，輸入公式“=MAX($A$1:A1)+1”，按“Ctrl+Enter”組合鍵執(zhí)行計算。1.2 合并單元格1.2.4合并單元格計算1.合并單元格求和3.合并單元格平均值2.合并單元格計數1.2 合并單元格1.2.5合并單元格篩選1. 選擇合并單元格，復制到另一列，備用。3.“定位”→“定位條件”→“空值”2.“開始”→“合并單元格”→“取消合并單元格”4.輸入公式=A2，按“Ctrl+Enter”鍵結束5.備用的合并單元格區(qū)域，單擊“格式刷”1.2 合并單元格1.2.6合并單元格數據查詢公式：=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,0),,3),2,)第1部分 數據采集整理----1.3數據規(guī)范1.3 數據規(guī)范1.3.1利用數據驗證（數據有效性）規(guī)范數據輸入1．規(guī)范性別輸入1.3 數據規(guī)范1.3.1利用數據驗證（數據有效性）規(guī)范數據輸入2．限定輸入內容1.3 數據規(guī)范1.3.1利用數據驗證（數據有效性）規(guī)范數據輸入3．限定數字范圍1.3 數據規(guī)范1.3.1利用數據驗證（數據有效性）規(guī)范數據輸入4．限定文本長度1.3 數據規(guī)范1.3.1利用數據驗證（數據有效性）規(guī)范數據輸入5．限制輸入重復信息1.3 數據規(guī)范1.3.1利用數據驗證（數據有效性）規(guī)范數據輸入6．限定身份證號碼1.3 數據規(guī)范1.3.2設置只能輸入規(guī)范的日期第1步：設置日期格式。第2步：數據驗證規(guī)范日期區(qū)間。1.3 數據規(guī)范1.3.3巧用數據有效性規(guī)范報到時間1.公式“=NOW()”2.“數據”→“數據驗證”→“序列”，“來源”設為“=$E$2”，3.“報到時間”一列單元格格式4.隱藏E列。1.3 數據規(guī)范1.3.4數量單位，怎么計算平均值{=ROUND(AVERAGE(--SUBSTITUTE(B2:B10,"分",)),2)}，1.3 數據規(guī)范1.3.5一鍵添加“能計算”的數量單位“設置單元格格式”對話框，在“分類”中選擇“自定義”，直接在“類型”文本框的“G/通用格式”后輸入數量單位，1.3 數據規(guī)范1.3.6多級聯(lián)動菜單，規(guī)范數據輸入第1步：分級數據整理。第2步：自定義名稱。第3步：建立各級菜單。1.3 數據規(guī)范1.3.7”數值”不能計算，怎么辦第1種方法：選擇性粘貼。第2種方法：數據分列。第3種方法：VALUE函數。1.3 數據規(guī)范1.3.8規(guī)范全角半角數據公式“=ASC(D3)”公式“=WIDECHAR(D3)”1.3 數據規(guī)范1.3.9數字與文本分離的三種方法第1種方法：函數法。第2種方法：分列。第3種方法：快速填充。1.3 數據規(guī)范1.3.10用LOOKUP和FIND函數規(guī)范標準名稱第1步：建立關鍵字與標準名稱對應表。第2步：函數實現(xiàn)公式“=LOOKUP(1,0/FIND($D$2:$D$7,A2),$E$2:$E$7)”1.3 數據規(guī)范1.3.11給同一單元格中姓名和電話號碼中間加分隔符號公式“=REPLACEB(A2,SEARCHB("?",A2),0,"：")第1部分 數據采集整理----1.4行列設置1.4 行列設置1.4.1快速刪除空白行1．純空白行，無其它空白單元格2．既有空白行，又有空白單元格1.4 行列設置1.4.2不管插入行還是刪除行，序號都可自動填寫方法1：ROW函數。方法2：ROW函數+表格。方法3：SUBTOTAL函數。1.4 行列設置1.4.3數據的轉置與跳過單元格復制１．選擇性粘貼，數據轉置的使用。2．跳過單元格復制。1.4 行列設置1.4.4最快捷的一列變多列方式1．將數據復制到C列。2．在D1單元格中輸入公式“=C6”，按Enter鍵執(zhí)行計算。因為從C6開始名字另起一列顯示。3．公式向下填充，再向右填充，在C1：G5區(qū)域會囊括原來的一整列分布的數據。4．選中D1：G5區(qū)域，復制，再進行選擇性粘貼，用數值覆蓋原有的公式。5．刪除C1：G5區(qū)域以外的數據。1.4 行列設置1.4.5聚光燈效果（閱讀模式）改變當前行和列的顏色1．條件格式設計顏色。2．顏色隨單元格改變而移動1.4 行列設置1.4.6同一部門員工合并到同一單元格1.4 行列設置1.4.7同一部門員工，由同一單元格變分行顯示1．打開查詢編輯器。2．姓名的分列顯示3．逆透視列。4．關閉并上載第1部分 數據采集整理----1.5數據維度轉換1.5 數據維度轉換1.5.1為工作表建目錄1．“公式”菜單中“定義名稱”公式“=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())”公式“=IFERROR(HYPERLINK(目錄&"!A1",MID(目錄,FIND("]",目錄)+1,99)),"")”2．公式1.5 數據維度轉換1.5.2旁門左道創(chuàng)建目錄第1步：選定所有工作表。第2步：輸入公式。第3步：自動生成“兼容性報表”。第4步：復制目錄到“目錄”工作表。1.5 數據維度轉換1.5.3鏈接到另一張表的四種姿勢第1種方法：文字。1.5 數據維度轉換1.5.3鏈接到另一張表的四種姿勢第2種方法：形狀。1.5 數據維度轉換1.5.3鏈接到另一張表的四種姿勢第3種方法：圖標。1.5 數據維度轉換1.5.3鏈接到另一張表的四種姿勢第4種方法：ActiveX控件。1.5 數據維度轉換1.5.4單擊訂單名稱即可跳到訂單詳情工作表第1部分 數據采集整理----1.6數據格式轉換1.6 數據格式轉換1.6.1數值取整的9種方式1．INT取整。1.6 數據格式轉換1.6.1數值取整的9種方式2．TRUNC取整。1.6 數據格式轉換1.6.1數值取整的9種方式3．ROUND小數取整。1.6 數據格式轉換1.6.1數值取整的9種方式4．ROUND整數取整。1.6 數據格式轉換1.6.1數值取整的9種方式5．ROUNDUP向上舍入函數。1.6 數據格式轉換1.6.1數值取整的9種方式6．ROUNDDOWN向下舍入函數。1.6 數據格式轉換1.6.1數值取整的9種方式7．MROUND函數。1.6 數據格式轉換1.6.1數值取整的9種方式8．CEILING函數1.6 數據格式轉換1.6.1數值取整的9種方式9．FLOOR函數1.6 數據格式轉換1.6.2數值的特殊舍入方式：舍入到偶數或奇數1．舍入到偶數MROUND四舍五入到偶數CEILING函數FLOOR函數EVEN函數1.6 數據格式轉換1.6.2數值的特殊舍入方式：舍入到偶數或奇數2．舍入到奇數1.6 數據格式轉換1.6.3NUMBERSTRING和TEXT函數：阿拉伯數字和中文數字轉換1．阿拉伯數字轉中文數字2．中文數字轉阿拉伯數字1.6 數據格式轉換1.6.4一串串長短不一的文本算式，怎么算結果第1步：選項設置。第2步：數據分列。第3步：選項設置。1.6 數據格式轉換1.6.5人民幣阿拉伯數字轉為中文大寫格式公式“=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";負")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零元整"),"零角",IF(A2^2<1,,"零")),"零分","整")”第1部分 數據采集整理----1.7數據篩選1.7 數據篩選1.7.1數值篩選基礎1．篩選指定某值。1.7 數據篩選1.7.1數值篩選基礎2．排除指定值篩選。1.7 數據篩選1.7.1數值篩選基礎3．高于指定值篩選。1.7 數據篩選1.7.1數值篩選基礎4．篩選指定范圍數值。1.7 數據篩選1.7.1數值篩選基礎5．篩選前幾位數值。1.7 數據篩選1.7.1數值篩選基礎6．高于平均值篩選。1.7 數據篩選1.7.2高級篩選高級篩選的關鍵點：必須按照篩選要求自己寫一個條件區(qū)域。高級篩選的原數據區(qū)域第一行（列標簽）一定要包含條件區(qū)域的第一行（列標簽）。高級篩選條件區(qū)域，一定要注意各條件之間的關系。1.7 數據篩選1.7.3篩選符合條件人員的兩種方法1.IF+COUNTIF、2.高級篩選1.7 數據篩選1.7.4不用公式的跨表查詢：查詢指定顧客的購買記錄第1步：寫條件。第2步：高級篩選。1.7 數據篩選1.7.5SUMIF+CELL使隱藏列不參與匯總第1步：建立輔助行第2步：函數實現(xiàn)。1.7 數據篩選1.7.6序號經過篩選后仍然不亂第1部分 數據采集整理----1.8排序和排名1.8 排序和排名1.8.1多關鍵字排序，多少個排序條件都可以一起來“數據”→“排序”“添加條件”1.8 排序和排名1.8.2數據也可按行排序第1步：選定除了行標題（首列）以外的所有數據，單擊“數據”→“排序”第2步：選擇“主要關鍵字”為消費記錄所在“行2”，“次序”為“降序”；單擊“添加條件”按鈕，添加“次要關鍵字”，次要關鍵字選擇消費方式所在的“行4”，根據要求選擇升序或者降序，1.8 排序和排名1.8.3按自定義序列排序，誰在前誰在后由你定1.8 排序和排名1.8.4剔除0值排名次，升序降序由你來定1．剔除0值降序排。2．剔除0值升序排名。1.8 排序和排名1.8.5只給有銷量的產品添加序號公式“=IF(C2=0,"",(C2<>0)*(COUNT($C$2:C2)-COUNTIF($C$2:C2,"0")))”1.8 排序和排名1.8.6RANK.EQ引用合并區(qū)域，實現(xiàn)多列數據排名公式“=RANK.EQ(B3,($B$3:$B$11,$E$3:$E$11,$H$3:$H$11,$K$3:$K$11))”1.8 排序和排名1.8.7SUMPRODUCT實現(xiàn)中式排名公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))”1.8 排序和排名1.8.8SUMPRODUCT分組排名公式公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))”1.8 排序和排名1.8.9利用公式自動生成字母序列公式“=CHAR(COLUMN(A1)+64)”公式“=CHAR(ROW(A1)+64)”公式“=CHAR(COLUMN()+96)”公式“=CHAR(ROW(B1)+96)”1.8 排序和排名1.8.10數據透視表實現(xiàn)排名（1）建立數據透視表。（2）添加字段到“行”（3）更改“值顯示方式”第1部分 數據采集整理----1.9數據去重復1.9數據去重復1.9.1刪除重復項，也可以隨你所欲1．只有一列數據。2．數據有多列。1.9數據去重復1.9.2每人報名項目多少不一，計算有多少人報名公式“=SUMPRODUCT(1/COUNTIF($A$2:$A$16,$A$2:$A$16))”1.9數據去重復1.9.3COUNT+MATCH，統(tǒng)計兩列有多少重復值公式“=COUNT(MATCH(A2:A11,B2:B11,0))”，按Ctrl+Shift+Enter組合鍵1.9數據去重復1.9.4利用EXACT函數設置條件格式，標記兩組數據的不同在“新增格式規(guī)則”對話框中，“選擇規(guī)則類型”為“使用公式確定要設置格式的單元格”，并在“為符合此公式的值設計格式”中輸入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”，第1部分 數據采集整理----1.10多工作簿、工作表合并、匯總與拆分1.10 多工作簿、工作表合并、匯總與拆分1.10.1數據查詢功能實現(xiàn)多工作表合并第1步：打開查詢編輯器。第2步：追加查詢。第3步：關閉并上載1.10 多工作簿、工作表合并、匯總與拆分1.10.2PowerQuery編輯器實現(xiàn)多工作簿合并與刷新第1步：將需要合并的工作簿存入一個文件夾中。第2步：新建“合并”工作簿。新建的工作簿不要和需要合并的工作簿放在同一文件夾。第3步：新建查詢。第4步：刪除多余數據列。第5步：添加列選項。第6步：數據加工。第7步：數據加載到表格。第8步：數據刷新。1.10 多工作簿、工作表合并、匯總與拆分1.10.3多工作表數據匯總第1種情況：所有表“姓名”排序一致。公式“=SUM('*'!C2)”1.10 多工作簿、工作表合并、匯總與拆分1.10.3多工作表數據匯總第2種情況：所有表“姓名”排序不一致。公式“=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$12)&"月!$B$2:$B$37"),匯總!B2,INDIRECT(ROW($1:$12)&"月!$c$2:$c$37")))”1.10 多工作簿、工作表合并、匯總與拆分1.10.4結構一致的多個工作表，合并計算是最好的匯總方法1.10 多工作簿、工作表合并、匯總與拆分1.10.5多個工作表，不用合并，直接查詢新建“查詢”第1部分 數據采集整理----1.11圖片處理1.11 圖片處理1.11.1批量導入文件名第1步：生成包含圖片名稱的文本文檔。第2步：生成Excel文件。1.11 圖片處理1.11.2工作表中批量插入照片第1步：公式輸入第2步：粘貼到文本文檔。公式“="<table><imgsrc=""E:\圖片\"&B2&".jpg""width=""100""height=""100"">"”第3步：粘貼Unicode文本。1.11 圖片處理1.11.3批注也可插入圖片第1步：插入批注第2步：設置批注格式第3步：設置填充效果第4步：填充圖片效果1.11 圖片處理1.11.3批注也可插入圖片第1步：將圖片整理到文件夾第2步：復制文件夾路徑第3步：輸入公式1.11 圖片處理1.11.5一次為成百上千幅圖片重命名第1步：顯示已知文件類型的擴展名第2步：新建Excel文件，輸入公式第3步：建立記事本文檔第4步：記事本文檔“重命名”第5步：雙擊運行批處理文檔1.11 圖片處理1.11.6利用Excel照相機自動匹配圖片第1步：打開“照相機”功能第2步：給圖片拍照第3步：定義名稱第4步：輸入名稱第2部分 函數與公式----2.1公式綜述2.1 公式綜述2.1.1必須知道的公式基礎知識1、運算符。2.1 公式綜述2.1.1必須知道的公式基礎知識2、運算符優(yōu)先級（1）如果公式中包含了相同優(yōu)先級的運算符，Excel將從左到右進行計算。（2）如果公式中有不同級別的混合運算，運算符的順序從高到低依次為：引用運算符（:）、負號（如-l）、%（百分比）、^（乘方）、*和/（乘和除）、+和-（加和減）、&（連接符）、比較運算符。2.1 公式綜述2.1.1必須知道的公式基礎知識3、相對引用與絕對引用。（1）概念：A1：相對引用。$A$1：絕對引用。$A1：列絕對引用，行相對引用。A$1：行絕對引用，列相對引用。（2）用途:行前添加$，復制公式時，行數不發(fā)生變化;列前添加$，復制公式時，列數不發(fā)生變化。（3）F4鍵→引用方式之間切換的快捷鍵：按一次：絕對引用。按兩次：對行絕對引用、對列相對引用。按三次：對行相對引用、對列絕對引用。按四次：相對引用。2.1 公式綜述2.1.1必須知道的公式基礎知識4、公式排查錯誤。2.1 公式綜述2.1.1必須知道的公式基礎知識5、公式填充。2.1 公式綜述2.1.1必須知道的公式基礎知識6、公式批量填充。2.1 公式綜述2.1.1必須知道的公式基礎知識7、公式轉換為數值。2.1 公式綜述2.1.1必須知道的公式基礎知識8、公式顯示。2.1 公式綜述2.1.1必須知道的公式基礎知識9、顯示另一單元格公式。2.1 公式綜述2.1.1必須知道的公式基礎知識10、隱藏公式。2.1 公式綜述2.1.1必須知道的公式基礎知識11、保護公式。2.1 公式綜述2.1.1必須知道的公式基礎知識12、顯示公式部分計算結果。2.1 公式綜述2.1.1必須知道的公式基礎知識13、中斷公式編輯。2.1 公式綜述2.1.2公式中常出現(xiàn)的錯誤代碼及修正方法錯誤值含義解決辦法＃＃＃＃1、單元格數據太長。2、單元格公式所產生的結果太大，單元格中無法完整顯示。3、日期和時間格式的單元格相減，出現(xiàn)了負值。1、增加列寬。2、如果是由日期或時間相減產生了負值引起的，可以改變單元格的格式為文本，但結果只能是負時間量。＃DIV/0!1、除數為0。2、公式中除數使用了空單元格或是包含零值單元格的單元格引用。1、修改單元格引用。2、在用作除數的單元格中輸入不為零的值。＃VALUE!1、數值運算時引用文本型的數據。2、使用了不正確的參數或運算符。3、當執(zhí)行自動更正公式功能時不能更正公式。1、更正相關的數據類型或參數類型。2、提供正確的參數；＃REF!刪除了被公式引用的單元格范圍。恢復被引用的單元格范圍，或是重新設定引用范圍。＃N/A查找或引用函數中找不到匹配的值檢查被查找的值，使其存在于查找的數據區(qū)域＃NAME?在公式中使用了Excel所不能識別的文本，比如：1、輸錯了函數名稱。2、使用了已刪除的區(qū)域或名稱。3、引用的文本沒有加英文雙引號。1、改正函數名稱。2、修改引用的區(qū)域或名稱。3、引用的文本加英文雙引號。＃NUM!1、函數參數無效。2、公式的結果太大或太小，無法在工作表中表示。1、確認函數中使用的參數類型正確。2、修改公式，找到錯誒原因并更正。＃NULL！1、使用了不正確的區(qū)域運算符。2、引用的單元格區(qū)域的交集為空。1、改正區(qū)域運算符使之正確。2、更改引用使之相交。2.1 公式綜述2.1.3使用“追蹤錯誤”對公式進行檢查2.1 公式綜述2.1.4將公式保護起來第1步：所有單元格去除“鎖定”。第2步：保護和隱藏“公式”單元格。第3步：設置保護工作表2.1 公式綜述2.1.5數組公式——基礎知識1．數組概念。2．數組的維度。3．數組公式。2.1 公式綜述2.1.6數組公式——應用初步1、行列數相同的數組運算。2．數組與單一數據運算。3．單列數組與單行數組運算。4．單行或單列數組與多行列二維數組運算。5．行列相等的二維數組運算。2.1 公式綜述2.1.7數組公式——典型應用1、“繞過”乘積直接求和。2.1 公式綜述2.1.7數組公式——典型應用2．計算連續(xù)數值和、平均值。2.1 公式綜述2.1.7數組公式——典型應用3．計算不同產品種類數。2.1 公式綜述2.1.7數組公式——典型應用4．多條件運算。2.1 公式綜述2.1.7數組公式——典型應用5．構建新數組運算。第2部分 函數與公式----2.2統(tǒng)計函數2.2 統(tǒng)計函數2.2.1MODE.MULT函數統(tǒng)計出現(xiàn)最多的數字（一）公式“=MODE.MULT($A$2:$G$12)”,2.2 統(tǒng)計函數2.2.2MODE.MULT函數統(tǒng)計出現(xiàn)最多的數字（二）公式“=IFERROR(INDEX(MODE.MULT($A$1:$E$10),ROW(A1)),"")”2.2 統(tǒng)計函數2.2.3COUNTIF函數給眾多班級中相同班級學生編號公式“=COUNTIF($B$2:B2,B2)”2.2 統(tǒng)計函數2.2.4五個常用的“IFS”結尾的多條件統(tǒng)計函數2.2 統(tǒng)計函數2.2.5SUM+COUNTIF函數統(tǒng)計不重復值的個數公式“=SUM(1/COUNTIF(B2:B16,B2:B16))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算2.2 統(tǒng)計函數2.2.6FREQUENCY函數分段計數公式“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算2.2 統(tǒng)計函數2.2.7COUNTIFS函數統(tǒng)計滿足多個條件的單元格數量COUNTIFS函數統(tǒng)計滿足多個條件的單元格數量2.2 統(tǒng)計函數2.2.8TRIMMEAN函數去掉最高分和最低分求平均值=TRIMMEAN（B3:B8,2/9）=TRIMMEAN(F3:F8,4/9)第2部分 函數與公式----2.3文本函數2.3 文本函數2.3.1MID與FIND函數結合提取括號內數據公式“=MID(A2,FIND("（",A2)+1,FIND("）",A2)-1-FIND("（",A2))”2.3 文本函數2.3.2TEXT——超級好用的文本函數功能：TEXT函數可通過格式代碼對數字應用格式，從而更改數字的顯示方式。語法：TEXT(Value,Format_text)。參數：Value：數值，或是計算結果為數字值的公式，也或對包含數字值的單元格的引用。Format_text：文本形式的數字格式。注意：text返回的一律都是文本形式的數據。如果需要計算，可以先將文本轉換為數值，然后再計算。文本型數值遇到四則運算會自動轉為數值。但文本會不參與sum之類的函數運算。2.3 文本函數2.3.2TEXT——超級好用的文本函數1．格式日期。代碼含義m將月顯示為不帶前導零的數字。mm根據需要將月顯示為帶前導零的數字。mmm將月顯示為縮寫形式（Jan到Dec）。mmmm將月顯示為完整名稱（January到December）。d將日顯示為不帶前導零的數字。dd根據需要將日顯示為帶前導零的數字。ddd將日顯示為縮寫形式（Sun到Sat）。dddd將日顯示為完整名稱（Sunday到Saturday）。yy將年顯示為兩位數字。yyyy將年顯示為四位數字。2.3 文本函數2.3.2TEXT——超級好用的文本函數2．格式時間。代碼含義h將小時顯示為不帶前導零的數字。[h]以小時為單位顯示經過時間。如果使用了公式，該公式返回小時數超過24時間，請使用類似于[h]:mm:ss的數字格式。hh根據需要將小時顯示為帶前導零的數字。如果格式含有AM或PM，則基于12小時制顯示小時；否則，基于24小時制顯示小時。m將分鐘顯示為不帶前導零的數字。注釋m或mm代碼必須緊跟在h或hh代碼之后或緊跟在ss代碼之前；否則，Excel會顯示月份而不是分鐘。[m]以分鐘為單位顯示經過時間。如果所用的公式返回的分鐘數超過60，請使用類似于[mm]:ss的數字格式。mm根據需要將分鐘顯示為帶前導零的數字。注釋m或mm代碼必須緊跟在h或hh代碼之后或緊跟在ss代碼之前；否則，Excel會顯示月份而不是分鐘。s將秒顯示為不帶前導零的數字。[s]以秒為單位顯示經過時間。如果所用的公式返回的秒數超過60，請使用類似于[ss]的數字格式。ss根據需要將秒顯示為帶前導零的數字。如果要顯示秒的小數部分，請使用類似于h:mm:ss.00的數字格式。AM/PM、am/pm、A/P、a/p基于12小時制顯示小時。時間介于午夜和中午之間時，Excel會使用AM、am、A或a表示時間；時間介于中午和午夜之間時，Excel會使用PM、pm、P或p表示時間。2.3 文本函數2.3.2TEXT——超級好用的文本函數3．千分位分隔符。代碼含義"#,###"只保留整數"#,###.00"保留兩位小數"#,"顯示為1,000的整倍數"#,###.0,"顯示為1,000的整倍數，且保留一位小數"0.0,,"顯示為1,000,000的整倍數，且保留一位小數2.3 文本函數2.3.2TEXT——超級好用的文本函數4．格式數字、貨幣。代碼含義"0.00"只保留整數"#,##0"千分位分隔符，只保留整數"#,##0.00"千分位分隔符，保留整數兩位小數"$#,##0"只保留整數"$#,##0.00"保留兩位小數"$#,##0.00_);($#,##0.00)"兩位小數，負數"$*#,##0"只保留整數，$與數字間一個空字符"$*#,##0.00"兩位小數，$與數字間一個空字符2.3 文本函數2.3.2TEXT——超級好用的文本函數5．加0前導符補充位數。6．百分比7．特殊格式。2.3 文本函數2.3.2TEXT——超級好用的文本函數8．條件區(qū)段判斷。（1）四個條件區(qū)段。（2）三個條件區(qū)段。（3）兩個條件區(qū)段。2.3 文本函數2.3.2TEXT——超級好用的文本函數9、自定義條件區(qū)段。（1）四個自定義條件區(qū)段。（2）三個自定義條件區(qū)段。（3）兩個自定義條件區(qū)段。2.3 文本函數2.3.3&——文本連接符的使用1．基本用法。2．合并后換行。2.3 文本函數2.3.3&——文本連接符的使用3．合并帶格式的內容。4．合并列實現(xiàn)多條件查找。2.3 文本函數2.3.4CONCATENATE文本連接函數（1）基本用法。（2）合并后換行。（3）合并帶格式的內容。2.3 文本函數2.3.5CONCAT文本連接函數（1）合并區(qū)域（2）加分隔符合并區(qū)域。（3）條件篩選區(qū)域合并。2.3 文本函數2.3.6TEXTJOIN文本連接函數公式“=TEXTJOIN("、",1,IF($A$2:$A$15=D2,$B$2:$B$15,""))”,按Ctrl+Shift+Enter組合鍵執(zhí)行計算2.3 文本函數2.3.6TEXTJOIN文本連接函數同行合并公式“=TEXTJOIN("、",1,A1:F1)”行列區(qū)域合并公式：“=TEXTJOIN("",1,A1:L4)”2.3 文本函數2.3.7CLEAN函數清除非打印字符CLEAN函數可以快速去除各種非打印字符2.3 文本函數2.3.8SUBSTITUTE文本替換函數的使用公式“=SUBSTITUTE(C2,MID(C2,11,4),"****")”SUBSTITUTE函數四個特點舉例2.3 文本函數2.3.8SUBSTITUTE文本替換函數的使用例1：統(tǒng)一替換部分字符。例2：計算字符串內特定字符的個數。例3：帶單位的數值計算。例4：同一單元格中最大值。2.3 文本函數2.3.9REPLACE函數——隱藏身份證號碼部分數字公式“=REPLACE(C3,7,8,"********")第2部分 函數與公式----2.4時間與日期函數2.4 時間與日期函數2.4.1根據身份證計算退休時間公式“=EDATE(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),55*12+MOD(MID(A2,17,1),2)*5*12)”2.4 時間與日期函數2.4.2根據續(xù)費月數，計算到期日公式“=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))”2.4 時間與日期函數2.4.3DATEDIF函數計算精確到年月天的賬齡公式“=DATEDIF(B2,C2,"Y")&"年"&DATEDIF(B2,C2,"YM")&"個月"&DATEDIF(B2,C2,"MD")&"天"”2.4 時間與日期函數2.4.3DATEDIF函數計算精確到年月天的賬齡DATEDIF函數應用：2.4 時間與日期函數2.4.4WORKDAY函數計算幾個工作日之后的日期第1步：將每個金額范圍的最低值與需交款工作日寫入E3:F7區(qū)域。第2步：在C2單元格中輸入公式“=WORKDAY(B2,VLOOKUP(A2,$E$3:$F$7,2))”，按Enter鍵執(zhí)行計算，再將公式向下填充，即可得所有金額交款日期。2.4 時間與日期函數2.4.5EOMONTH函數取某月最后一天公式“=B2/DAY(EOMONTH(A2,0))”EOMONTH函數功能：返回某個月份最后一天語法：EOMONTH(start_date,months)中文語法：EOMONTH(日期,月份數)2.4 時間與日期函數2.4.6NETWORKDAYS.INTL函數，工作日的花樣算法怎么算兩個日期之間的工作日？2.4 時間與日期函數2.4.6NETWORKDAYS.INTL函數，工作日的花樣算法Weekend四種應用第2部分 函數與公式----2.5數學函數2.5 數學函數2.5.1SUMIF函數應用——單條件、多條件、模糊條件求和2.5 數學函數2.5.2SUMIF函數應用——非空條件、排除錯誤值、日期區(qū)間求和2.5 數學函數2.5.3SUMIF函數應用——隔列求和、查找引用公式“=SUMIF($B$2:$G$2,H$2,$B3:$G3)”2.5 數學函數2.5.3SUMIF函數應用——隔列求和、查找引用1．SUNIF還可以實現(xiàn)查找與引用。2．多列區(qū)域查找引用。2.5 數學函數2.5.4SUMIFS多字段多條件求和2.5 數學函數2.5.5SUMPRODUCT用法解析1．基本用法。公式“=SUMPRODUCT(B2:B9,C2:C9)”2.5 數學函數2.5.5SUMPRODUCT用法解析2．單條件求和。公式“=SUMPRODUCT((B2:B11="女")*C2:C11)”2.5 數學函數2.5.5SUMPRODUCT用法解析3．多條件求和。公式“=SUMPRODUCT((B2:B11="女")*(C2:C11>15),C2:C11)”2.5 數學函數2.5.5SUMPRODUCT用法解析4．模糊條件求和。公式“=SUMPRODUCT(ISNUMBER(FIND("銷售",A2:A11))*(C2:C11="女"),D2:D11)”2.5 數學函數2.5.5SUMPRODUCT用法解析5．單條件計數。公式“=SUMPRODUCT(N(B2:B11="女"))”2.5 數學函數2.5.5SUMPRODUCT用法解析6．多條件計數。公式“=SUMPRODUCT((B2:B11="女")*(C2:C11>15))”2.5 數學函數2.5.5SUMPRODUCT用法解析7．模糊條件計數。公式“=SUMPRODUCT(ISNUMBER(FIND("銷售",A2:A11))*(C2:C11="女"))”2.5 數學函數2.5.5SUMPRODUCT用法解析8．按月份統(tǒng)計數據。公式“=SUMPRODUCT((MONTH($A$2:$A$13)=D2)*($B$2:$B$13))”,2.5 數學函數2.5.5SUMPRODUCT用法解析9．跨列統(tǒng)計。公式“=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3)”2.5 數學函數2.5.5SUMPRODUCT用法解析10．多權重統(tǒng)計。公式“=SUMPRODUCT(B$2:D$2,B3:D3)”2.5 數學函數2.5.5SUMPRODUCT用法解析11．二維區(qū)域統(tǒng)計。公式“=SUMPRODUCT(($B$2:$B$13=$E2)*($A$2:$A$13=F$1)*$C$2:$C$13)”2.5 數學函數2.5.5SUMPRODUCT用法解析12．不間斷排名。公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))”2.5 數學函數2.5.6SUMPRODUCT函數注意事項：乘號與逗號有區(qū)別2.5 數學函數2.5.7SUBTOTAL函數實現(xiàn)忽略隱藏行統(tǒng)計公式“=SUBTOTAL(109,B3:B14)”Function_numFunction_num函數函數解釋（包含隱藏值）（忽略隱藏值）1101AVERAGE平均值2102COUNT計數（對數值計數）3103COUNTA計數（對文本計數）4104MAX最大值5105MIN最小值6106PRODUCT計算乘積7107STDEV給定樣本標準偏差8108STDEVP樣本總體標準偏差9109SUM和10110VAR基于給定樣本的方差2.5 數學函數2.5.8AGGREGATE——忽略錯誤值計算的萬能函數2.5 數學函數2.5.9ROUND()函數對數據四舍五入公式“=ROUND(A2,-2)”2.5 數學函數2.5.10QUOTIENT與TRUNC函數——截去小數，保留整數公式“=QUOTIENT(C2,B2)”公式“=TRUNC(C2/B2,0)”第2部分 函數與公式----2.6查找與引用函數2.6 查找與引用函數2.6.1VLOOKUP函數應用之基礎——基本查找公式“=VLOOKUP(F3,$B$2:$D$15,3,0)”2.6 查找與引用函數2.6.2VLOOKUP函數應用之小成——多行多列查找公式“=VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0)”2.6 查找與引用函數2.6.3VLOOKUP函數應用之提升——區(qū)間查找、等級評定、模糊查找1、區(qū)間查找公式“=VLOOKUP(B2,$E$3:$F$6,2)”2.6 查找與引用函數2.6.3VLOOKUP函數應用之提升——區(qū)間查找、等級評定、模糊查找2、等級評定公式“=VLOOKUP(B2,{0,”不合格“;60,”合格“;70,”良好“;85,”優(yōu)秀“},2)”2.6 查找與引用函數2.6.3VLOOKUP函數應用之提升——區(qū)間查找、等級評定、模糊查找3、模糊查找公式“=VLOOKUP("G"&"*",A1:B8,2,0)”2.6 查找與引用函數2.6.4VLOOKUP函數應用之進階——多條件查找、逆向查找1、多條件查找公式“=VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)”2.6 查找與引用函數2.6.4VLOOKUP函數應用之進階——多條件查找、逆向查找2、逆向查找公式“=VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)”2.6 查找與引用函數2.6.4VLOOKUP函數應用之進階——多條件查找、逆向查找2、逆向查找公式“=VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0)”2.6 查找與引用函數2.6.5VLOOKUP函數應用之高級篇——一對多查找公式“=VLOOKUP($B$12&ROW(B1),IF({1,0},$B$2:$B$9&COUNTIF(INDIRECT("b2:b"&ROW($2:$9)),$B$12),$C$2:$C$9),2,0)”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算2.6 查找與引用函數2.6.6HLOOKUP行查找函數公式“=HLOOKUP(B2,$F$2:$I$3,2)”2.6 查找與引用函數2.6.7LOOKUP查詢函數十種用法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”第1種用法：逆向查找。2.6 查找與引用函數2.6.7LOOKUP查詢函數十種用法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”第2種用法：