《Excel數(shù)據(jù)處理與可視化》（第二版） 課件 韓春玲 第2部分 函數(shù)與公式

第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、相對引用與絕對引用。（1）概念：A1：相對引用。$A$1：絕對引用。$A1：列絕對引用，行相對引用。A$1：行絕對引用，列相對引用。（2）用途:行前添加$，復(fù)制公式時(shí)，行數(shù)不發(fā)生變化;列前添加$，復(fù)制公式時(shí)，列數(shù)不發(fā)生變化。（3）F4鍵→引用方式之間切換的快捷鍵：按一次：絕對引用。按兩次：對行絕對引用、對列相對引用。按三次：對行相對引用、對列絕對引用。按四次：相對引用。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ù)太長。2、單元格公式所產(chǎn)生的結(jié)果太大，單元格中無法完整顯示。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ù)類型或參數(shù)類型。2、提供正確的參數(shù)；＃REF!刪除了被公式引用的單元格范圍。恢復(fù)被引用的單元格范圍，或是重新設(shè)定引用范圍。＃N/A查找或引用函數(shù)中找不到匹配的值檢查被查找的值，使其存在于查找的數(shù)據(jù)區(qū)域＃NAME?在公式中使用了Excel所不能識(shí)別的文本，比如：1、輸錯(cuò)了函數(shù)名稱。2、使用了已刪除的區(qū)域或名稱。3、引用的文本沒有加英文雙引號(hào)。1、改正函數(shù)名稱。2、修改引用的區(qū)域或名稱。3、引用的文本加英文雙引號(hào)。＃NUM!1、函數(shù)參數(shù)無效。2、公式的結(jié)果太大或太小，無法在工作表中表示。1、確認(rèn)函數(shù)中使用的參數(shù)類型正確。2、修改公式，找到錯(cuò)誒原因并更正。＃NULL！1、使用了不正確的區(qū)域運(yùn)算符。2、引用的單元格區(qū)域的交集為空。1、改正區(qū)域運(yùn)算符使之正確。2、更改引用使之相交。2.1 公式綜述2.1.3使用“追蹤錯(cuò)誤”對公式進(jìn)行檢查2.1 公式綜述2.1.4將公式保護(hù)起來第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、“繞過”乘積直接求和。2.1 公式綜述2.1.7數(shù)組公式——典型應(yīng)用2．計(jì)算連續(xù)數(shù)值和、平均值。2.1 公式綜述2.1.7數(shù)組公式——典型應(yīng)用3．計(jì)算不同產(chǎn)品種類數(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ù)可通過格式代碼對數(shù)字應(yīng)用格式，從而更改數(shù)字的顯示方式。語法：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之類的函數(shù)運(yùn)算。2.3 文本函數(shù)2.3.2TEXT——超級(jí)好用的文本函數(shù)1．格式日期。代碼含義m將月顯示為不帶前導(dǎo)零的數(shù)字。mm根據(jù)需要將月顯示為帶前導(dǎo)零的數(shù)字。mmm將月顯示為縮寫形式（Jan到Dec）。mmmm將月顯示為完整名稱（January到December）。d將日顯示為不帶前導(dǎo)零的數(shù)字。dd根據(jù)需要將日顯示為帶前導(dǎo)零的數(shù)字。ddd將日顯示為縮寫形式（Sun到Sat）。dddd將日顯示為完整名稱（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)過時(shí)間。如果使用了公式，該公式返回小時(shí)數(shù)超過24時(shí)間，請使用類似于[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)過時(shí)間。如果所用的公式返回的分鐘數(shù)超過60，請使用類似于[mm]:ss的數(shù)字格式。mm根據(jù)需要將分鐘顯示為帶前導(dǎo)零的數(shù)字。注釋m或mm代碼必須緊跟在h或hh代碼之后或緊跟在ss代碼之前；否則，Excel會(huì)顯示月份而不是分鐘。s將秒顯示為不帶前導(dǎo)零的數(shù)字。[s]以秒為單位顯示經(jīng)過時(shí)間。如果所用的公式返回的秒數(shù)超過60，請使用類似于[ss]的數(shù)字格式。ss根據(jù)需要將秒顯示為帶前導(dǎo)零的數(shù)字。如果要顯示秒的小數(shù)部分，請使用類似于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è)金額范圍的最低值與需交款工作日寫入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è)月份最后一天語法：EOMONTH(start_date,months)中文語法：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("銷售",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("銷售",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ù)解釋（包含隱藏值）（忽略隱藏值）1101AVERAGE平均值2102COUNT計(jì)數(shù)（對數(shù)值計(jì)數(shù)）3103COUNTA計(jì)數(shù)（對文本計(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ì)算的萬能函數(shù)2.5 數(shù)學(xué)函數(shù)2.5.9ROUND()函數(shù)對數(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í)評定、模糊查找1、區(qū)間查找公式“=VLOOKUP(B2,$E$3:$F$6,2)”2.6 查找與引用函數(shù)2.6.3VLOOKUP函數(shù)應(yīng)用之提升——區(qū)間查找、等級(jí)評定、模糊查找2、等級(jí)評定公式“=VLOOKUP(B2,{0,”不合格“;60,”合格“;70,”良好“;85,”優(yōu)秀“},2)”2.6 查找與引用函數(shù)2.6.3VLOOKUP函數(shù)應(yīng)用之提升——區(qū)間查找、等級(jí)評定、模糊查找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í)篇——一對多查找公式“=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查詢函數(shù)十種用法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”第1種用法：逆向查找。2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”第2種用法：逆向查找2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法公式“=LOOKUP(1,0/(B2:B12=G2)*(E1:E12=H2),C2:C12)”第3種用法：多條件查找。2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法公式“=LOOKUP(1,0/(B2:B10<>""),B2:B10)”第4種用法：查找最后一條記錄。2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法公式“=LOOKUP(B2,$H$2:$H$5,$I$2:$I$5)”第5種用法：區(qū)間查找。2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法公式“=LOOKUP(9^9,FIND(A9,$A$2:$A$5),$B$2:$B$5)”第6種用法：模糊查找。2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法第7種用法：查找最后一次進(jìn)貨日期。公式“=LOOKUP(1,0/(B2:B10<>""),$A$2:$A$10)”2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法第8種用法：關(guān)鍵字提取。入公式“=LOOKUP(9^9,FIND({"路由器","交換機(jī)","打印一體機(jī)","投影儀"},A2),{"路由器","交換機(jī)","打印一體機(jī)","投影儀"})”2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法第9種用法：拆分合并單元格公式“=LOOKUP("座",$A$2:A2)”2.6 查找與引用函數(shù)2.6.7LOOKUP查詢函數(shù)十種用法第10種用法：合并單元格的查詢?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ù)量公