《Excel數(shù)據(jù)處理與可視化》（第二版） 課件全套 韓春玲 第1-4部分 數(shù)據(jù)采集整- 數(shù)據(jù)可視化

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