第11章動(dòng)態(tài)報(bào)表與數(shù)據(jù)查找本章學(xué)習(xí)目標(biāo)1、理解表與結(jié)構(gòu)化引用2、掌握動(dòng)態(tài)報(bào)表的構(gòu)造和數(shù)據(jù)分析方法3、掌握查詢大工作表數(shù)據(jù)的方法4、用lookup/Vlookup函數(shù)查詢數(shù)據(jù)的方法5、用indirect和名稱相結(jié)合查詢數(shù)據(jù)的方法6、index和match相結(jié)合查詢數(shù)據(jù)的方法7、用D函數(shù)查詢數(shù)據(jù)的方法8、字符匹配函數(shù)與文本查詢的方法9、用ADDRESS和OFFSET進(jìn)行定位查詢10、用choose查詢數(shù)據(jù)的方法11、工作表數(shù)據(jù)提取與報(bào)表結(jié)構(gòu)轉(zhuǎn)換11.1表與動(dòng)態(tài)報(bào)表1、工作表的缺限日常工作中，類似于這樣的“日志”工作表很常見(jiàn)，每天都向此工作表添加數(shù)據(jù)，由于數(shù)據(jù)行的不確定性，為統(tǒng)計(jì)工作帶來(lái)了一定的困難！Excel2010的表格可以解決這一難題！11.1.1表格1、表格的概念表格也稱表，是一系列包含相關(guān)數(shù)據(jù)的行和列，這些行和列與工作表中其他行和列中的數(shù)據(jù)分開(kāi)管理。表是Excel2010中的特殊對(duì)象（其實(shí)早就有了，對(duì)應(yīng)于Excel2003中的列表，只是它比列表具有更多的功能），包含有格式化功能以外的許多特性。表中包括的主要內(nèi)容有表區(qū)域、表數(shù)據(jù)區(qū)域、匯總行、標(biāo)題行、列標(biāo)題、調(diào)整大小控制點(diǎn)等，如圖11.2所示。表具有動(dòng)態(tài)特性。當(dāng)表中的數(shù)據(jù)行發(fā)生變化之后，針對(duì)于表的各種計(jì)算公式會(huì)以表中的最新數(shù)據(jù)為依據(jù)進(jìn)行自動(dòng)調(diào)整，重新計(jì)算出正確的結(jié)果。11.1.1表格2、表的建立表與普通工作表區(qū)域可以隨時(shí)轉(zhuǎn)換，可以將普通工作表轉(zhuǎn)換成表，也可以將表轉(zhuǎn)換成普通工作表。1、建立普通工作表，每列有標(biāo)題2、單擊“插入”|“表”按鈕3、彈出“創(chuàng)建表”對(duì)話框，單擊“確定”按鈕就會(huì)建立該數(shù)據(jù)區(qū)域?qū)?yīng)的表，如下頁(yè)所示11.1.1表表名稱標(biāo)題行控制點(diǎn)匯總行表區(qū)域數(shù)據(jù)區(qū)域計(jì)算列11.1.1表2、表的結(jié)構(gòu)表：是指包括標(biāo)題和匯總行在內(nèi)的整個(gè)區(qū)域。表名稱：每個(gè)表都有一個(gè)名稱，應(yīng)用表名稱可以引用表中的數(shù)據(jù)。標(biāo)題行：表區(qū)域的第一行，常用描述性文字表示。在默認(rèn)情況下，表中每一列都在標(biāo)題行中啟用了篩選功能，利用此功能可以快速篩選表中的數(shù)據(jù)或?qū)Ρ磉M(jìn)行排序。數(shù)據(jù)區(qū)域：數(shù)據(jù)區(qū)域是指除開(kāi)表標(biāo)題和匯總行之外的區(qū)域，是表存放數(shù)據(jù)的單元格區(qū)域。11.1.1表2、表的結(jié)構(gòu)表：是指包括標(biāo)題和匯總行在內(nèi)的整個(gè)區(qū)域。表名稱：每個(gè)表都有一個(gè)名稱，應(yīng)用表名稱可以引用表中的數(shù)據(jù)。標(biāo)題行：表區(qū)域的第一行，常用描述性文字表示。在默認(rèn)情況下，表中每一列都在標(biāo)題行中啟用了篩選功能，利用此功能可以快速篩選表中的數(shù)據(jù)或?qū)Ρ磉M(jìn)行排序。數(shù)據(jù)區(qū)域：數(shù)據(jù)區(qū)域是指除開(kāi)表標(biāo)題和匯總行之外的區(qū)域，是表存放數(shù)據(jù)的單元格區(qū)域。11.1.1表2、表的結(jié)構(gòu)匯總行：匯總行位于表的最下方，在最后一個(gè)數(shù)據(jù)行下面。在默認(rèn)情況下，匯總行是不顯示在匯總行中，可以對(duì)相應(yīng)列中的表數(shù)據(jù)進(jìn)行各種類型的匯總計(jì)算，如計(jì)數(shù)、求平均數(shù)、求總和等。大小調(diào)整控制點(diǎn)：在表的右下角，用鼠標(biāo)上下左右拖動(dòng)它，可以可以擴(kuò)大或縮小表所對(duì)應(yīng)的區(qū)域。排序和篩選：Excel會(huì)自動(dòng)將篩選器下拉列表添加在表的標(biāo)題行中，通過(guò)它可以實(shí)現(xiàn)工作表數(shù)據(jù)篩選。顯示和計(jì)算表數(shù)據(jù)總計(jì)：可以快速地對(duì)表中的數(shù)據(jù)進(jìn)行匯總，方法為：在表的末尾顯示一個(gè)總計(jì)行，然后使用在每個(gè)總計(jì)行單元格的下拉列表中提供的函數(shù)11.1.1表2、表的結(jié)構(gòu)使用計(jì)算列：要使用一個(gè)適用于表中每一行的公式，可以創(chuàng)建計(jì)算列。計(jì)算列會(huì)自動(dòng)擴(kuò)展以包含其他行，從而使公式可以立即擴(kuò)展到這些行。動(dòng)態(tài)擴(kuò)展：表中的數(shù)據(jù)區(qū)域具有動(dòng)態(tài)特性，可以靈活地向表中添加或刪除數(shù)據(jù)行。當(dāng)在表下邊相鄰的空行或表右邊相鄰的空列中輸入數(shù)據(jù)時(shí)，Excel就會(huì)自動(dòng)對(duì)表進(jìn)行擴(kuò)展，將輸入了數(shù)據(jù)的相鄰行或列添加到表中。拖動(dòng)表的大小控制點(diǎn)，讓它包括相鄰的工作表行或工作表列，這些被包括的行或列就會(huì)被添加到表中；在表中任意位置插入行或列，插入的行和列就會(huì)成為表的有效組成部分。11.1.1表計(jì)算列和動(dòng)態(tài)擴(kuò)展H列是計(jì)列：在H2中輸入公式：=F2*H2回車后，Excel就會(huì)自動(dòng)填充H列的計(jì)算公式在I2中輸入21，表會(huì)動(dòng)態(tài)擴(kuò)展將I列包括到表中，但I(xiàn)列不是計(jì)算列，計(jì)算列應(yīng)包括公式。在J2中輸入“=2”，這是一個(gè)公式，J2是計(jì)算列，Excel會(huì)將此公式填充J列在第8行任一單元格輸入數(shù)據(jù)，Excel就會(huì)自動(dòng)擴(kuò)展表區(qū)域，將第8行包括到表中11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表1、結(jié)構(gòu)化引用表是一個(gè)自包含對(duì)象，是一個(gè)完整的結(jié)構(gòu)，表區(qū)域、數(shù)據(jù)區(qū)域、匯總行、標(biāo)題行、列標(biāo)題、數(shù)據(jù)行和數(shù)據(jù)列等都是表結(jié)構(gòu)的組成部分。在對(duì)表進(jìn)行計(jì)算的公式中，可以引用表中的單元格，也可以直接引用行、列、數(shù)據(jù)區(qū)域、匯總行或標(biāo)題行等表結(jié)構(gòu)，稱為結(jié)構(gòu)化引用。結(jié)構(gòu)化引用最大的優(yōu)點(diǎn)是對(duì)于動(dòng)態(tài)報(bào)表的自動(dòng)識(shí)別，無(wú)論表的數(shù)據(jù)區(qū)域怎樣變化，結(jié)構(gòu)化引用的單元格區(qū)域都能夠隨之進(jìn)行自動(dòng)調(diào)整。這樣便在最大程度上減少了在表中添加和刪除行或列時(shí)重寫(xiě)公式的需要。11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表【例11.2】在例11.1的銷售工作表中建立統(tǒng)計(jì)報(bào)表，計(jì)算以下數(shù)據(jù)：每位職工銷售各種品牌電視機(jī)的總數(shù)量、總的銷售記錄數(shù)、所有電視機(jī)的平均銷售價(jià)格、所有產(chǎn)品的銷售總數(shù)量以及商店工作人員的名單。表K3單元格中的公式，其中包括結(jié)構(gòu)化引用通過(guò)表，刪除冗余數(shù)據(jù)得到的不重復(fù)名單11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表1、結(jié)構(gòu)化引用語(yǔ)法結(jié)合Page271理解公式中①②③④⑤的含義，弄清楚結(jié)構(gòu)化引用的真實(shí)用法！說(shuō)表如下：常用結(jié)核化引用標(biāo)志①表名稱表名稱實(shí)際上相當(dāng)于表數(shù)據(jù)區(qū)域名稱（若有標(biāo)題行和匯總行，將不包括它們）。例如，在前面的例子中，表3是A2:H10區(qū)域的名稱②列說(shuō)明符從列標(biāo)題演化而來(lái)，由括號(hào)“[]”括起，并引用列數(shù)據(jù)（若有列標(biāo)題和匯總行，將不包括它們），相當(dāng)于列的名稱例如公式中的“[銷售員姓名]”、“[產(chǎn)品名稱]”和“[數(shù)量]”都是列說(shuō)明符，分別代表C2:C10、D2:D10和G2:G10區(qū)域中的數(shù)據(jù)。常用結(jié)核化引用標(biāo)志③特殊項(xiàng)目說(shuō)明符是引用表中的特定部分（如匯總行）的方法。

[#數(shù)據(jù)]是指表的整個(gè)數(shù)據(jù)區(qū)域，即A2:H10?！癧[#數(shù)據(jù)],[產(chǎn)品名稱]]”表示引用數(shù)據(jù)區(qū)域中產(chǎn)品名稱列所對(duì)應(yīng)的數(shù)據(jù)（即D2:D10）?！?全部”表示表對(duì)應(yīng)的全部單元格區(qū)域（A1:H11）“#標(biāo)題”表示表的標(biāo)題行（A1:H1）“#匯總”表示匯總行（A11:H11，處于隱藏狀態(tài)）?！癅-此行”與結(jié)構(gòu)化引用公式所在單元格的行相對(duì)應(yīng)。用法有二：其一，是直接用“[@]”表示表中的一行數(shù)據(jù)。例如，若在I2中輸入公式“=sum(表3[@])”，則其中的@代表A2:H2區(qū)域；第二種用法，是“[@列標(biāo)題]”，表示由指定標(biāo)題列中與公式所在行交叉處的單元格。例如，若在I2中輸入公式“=表3[@單價(jià)]”，表示表3中第2行單價(jià)列的數(shù)據(jù)，即F2，其值為3080。2、常用結(jié)核化引用案例【例11.2】在例11.1的銷售工作表中建立統(tǒng)計(jì)報(bào)表，計(jì)算以下數(shù)據(jù)：每位職工銷售各種品牌電視機(jī)的總數(shù)量、總的銷售記錄數(shù)、所有電視機(jī)的平均銷售價(jià)格、所有產(chǎn)品的銷售總數(shù)量以及商店工作人員的名單。11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表2、H列是計(jì)算列，公中應(yīng)用對(duì)表3的結(jié)構(gòu)化引用，計(jì)算每天的銷售總價(jià)：其中的[#此行]表示公式所在數(shù)據(jù)行，它可用@代替，[單價(jià)]和[數(shù)量]都是表中的數(shù)據(jù)列的結(jié)構(gòu)化引用。例如H2中公式的：“[[#此行]，[單價(jià)]]”代表F2，也可表示為“[@單價(jià)]”“[[#此行]，[數(shù)量]]”代表G2，也可表示為“[@數(shù)量]”即H2中的總價(jià)計(jì)算公式也為表示為：=[@單價(jià)]*[@數(shù)量]1、原始數(shù)據(jù)區(qū)域，將其轉(zhuǎn)換成表11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表3、計(jì)算每們職工的銷售總額K列公中應(yīng)用了對(duì)表3的結(jié)構(gòu)化引用，無(wú)論A：H列的表中是否會(huì)增加或刪除數(shù)據(jù)，K列的計(jì)算公式都會(huì)根據(jù)表中的最新數(shù)據(jù)進(jìn)行計(jì)算，得到正確的結(jié)果。這些計(jì)算公式因?yàn)閼?yīng)用了對(duì)表的結(jié)構(gòu)化引用而具有動(dòng)態(tài)計(jì)算的能力。2、常用結(jié)核化引用案例3、計(jì)算每們職工的銷售總額：在K3中輸入數(shù)組公式：=SUM(IF(表3[銷售員姓名]=$J3,IF(表3[[#數(shù)據(jù)],[產(chǎn)品名稱]]=K$2,表3[數(shù)量])))其中第一個(gè)IF條件首先判斷銷售員姓名列數(shù)據(jù)即（C列）是否為J3（即勞得諾），如果是再通過(guò)第2個(gè)條件語(yǔ)句判斷D列產(chǎn)品名稱是否為K2（創(chuàng)為），如果兩個(gè)IF條件都要成立，就返回同一行數(shù)量列（G列）數(shù)據(jù)。向下復(fù)制此公式可計(jì)算出各員工銷售創(chuàng)為的數(shù)量，向右復(fù)制此公式，可計(jì)算各員工銷售其它產(chǎn)品的總數(shù)量3、通過(guò)結(jié)構(gòu)化引用刪除冗余數(shù)據(jù)行在Excel的普通工作表中，要?jiǎng)h除其中的重復(fù)數(shù)據(jù)行并不容易。將普通工作表轉(zhuǎn)換成表，然后利用表提供的“刪除重復(fù)項(xiàng)”功能，就能輕松地得到數(shù)據(jù)行不重復(fù)的報(bào)表。11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表1、包括重復(fù)數(shù)據(jù)的區(qū)域2、轉(zhuǎn)換成表3、選擇刪除重復(fù)項(xiàng)4、刪除重復(fù)項(xiàng)后的表11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表4、通過(guò)數(shù)組公式引用表表的名稱、列標(biāo)題、各種特殊項(xiàng)（如#全部、匯總等）事實(shí)相當(dāng)于對(duì)應(yīng)區(qū)域的名稱，可以通過(guò)數(shù)組公式在不同的工作表中引用它們。11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表5、在不同工作表中對(duì)表進(jìn)行結(jié)構(gòu)化引用【例11.3】某超市從多家供應(yīng)商處進(jìn)購(gòu)各種食品，進(jìn)購(gòu)的情況如圖（a）所示。計(jì)算超市應(yīng)付給各供應(yīng)商的總訂貨費(fèi)，如圖（b）所示1、將源數(shù)據(jù)區(qū)域轉(zhuǎn)換成表2、復(fù)制源數(shù)據(jù)區(qū)域的供應(yīng)商，將它轉(zhuǎn)換成表表，并通過(guò)表刪除其中的重復(fù)數(shù)據(jù)行3、在B2中輸入公式：=SUM(IF(進(jìn)貨單[供應(yīng)商]=表5[[#此行],[供應(yīng)商]],進(jìn)貨單[單價(jià)]*(進(jìn)貨單[訂購(gòu)量]+進(jìn)貨單[再訂購(gòu)量])))按Ctrl+shift+Enter,就會(huì)自動(dòng)生成B列的計(jì)算公式,此公式的意思是：如果進(jìn)貨單表中供應(yīng)商列數(shù)據(jù)與表5中同行供應(yīng)商相同，就計(jì)算出單價(jià)同進(jìn)貨單表中訂購(gòu)量和再訂購(gòu)量的總和的乘積11.1.2結(jié)構(gòu)化引用和動(dòng)態(tài)報(bào)表6、表的應(yīng)用和普通工作表區(qū)域的轉(zhuǎn)換表能夠方便地構(gòu)造日常工作中的動(dòng)態(tài)報(bào)表，在各種不同的公式中通過(guò)對(duì)表的結(jié)構(gòu)化引用，不僅能夠使公式含義清楚，而且能夠擴(kuò)展公式的計(jì)算能力，實(shí)現(xiàn)對(duì)動(dòng)態(tài)報(bào)表的各類計(jì)算。在日常工作中，應(yīng)該大量用表來(lái)保存各種業(yè)務(wù)數(shù)據(jù)，制作工作報(bào)表。單擊表中任一單元格單擊“轉(zhuǎn)換為區(qū)域”，可將表轉(zhuǎn)換為普通區(qū)域11.2D函數(shù)與動(dòng)態(tài)報(bào)表1、關(guān)于D函數(shù)Excel將每個(gè)數(shù)據(jù)列都有標(biāo)題的數(shù)據(jù)表稱為數(shù)據(jù)庫(kù)，并提供了大約12個(gè)專用函數(shù)來(lái)簡(jiǎn)化這種數(shù)據(jù)表的數(shù)據(jù)統(tǒng)計(jì)和數(shù)據(jù)查找工作，這些函數(shù)都以D開(kāi)頭，所以也稱為D函數(shù)。D函數(shù)有相同的調(diào)用形式，其語(yǔ)法形式如下：Dname(database,field,criteria)

其中，Dname是函數(shù)名；database是一個(gè)單元格區(qū)域，要求該區(qū)域中的每列數(shù)據(jù)都必須有標(biāo)題；field是database區(qū)域中某列數(shù)據(jù)的標(biāo)題（稱為字段，出現(xiàn)在字符串中）；criteria稱為條件區(qū)域，它與高級(jí)篩選條件區(qū)域的含義和構(gòu)造方法完全相同。11.2.1

D函數(shù)函數(shù)名功能DAVERAGE返回所選數(shù)據(jù)庫(kù)列的平均值DCOUNT計(jì)算數(shù)據(jù)庫(kù)中包含數(shù)字的單元格的數(shù)量DCOUNTA計(jì)算數(shù)據(jù)庫(kù)中非空單元格的數(shù)量DGET從數(shù)據(jù)庫(kù)中提取符合指定條件的一行數(shù)據(jù)DMAX返回所選數(shù)據(jù)庫(kù)列中的最大值DMIN返回所選數(shù)據(jù)庫(kù)列的最小值DPRODUCT將數(shù)據(jù)庫(kù)中符合條件的數(shù)據(jù)行值相乘DSTDEV基于選擇的數(shù)據(jù)庫(kù)列的樣本估算標(biāo)準(zhǔn)偏差DSTDEVP基于所選數(shù)據(jù)庫(kù)列的樣本總體計(jì)算標(biāo)準(zhǔn)偏差DSUM對(duì)數(shù)據(jù)庫(kù)中符合條件的數(shù)據(jù)行的數(shù)字求和DVAR基于所選數(shù)據(jù)庫(kù)列的樣本估算方差DVARP基于所選數(shù)據(jù)庫(kù)列的樣本總體計(jì)算方差11.2.1

D函數(shù)簡(jiǎn)介條件區(qū)域數(shù)據(jù)庫(kù)區(qū)域D函數(shù)的調(diào)用11.2.2D函數(shù)與表結(jié)合構(gòu)造動(dòng)態(tài)數(shù)據(jù)分析報(bào)表D函數(shù)具有動(dòng)態(tài)計(jì)算能力用D函數(shù)對(duì)數(shù)據(jù)表進(jìn)行條件統(tǒng)計(jì)非常方便，同時(shí)D函數(shù)還具有動(dòng)態(tài)計(jì)算的能力。在數(shù)據(jù)庫(kù)中應(yīng)用D函時(shí)，其動(dòng)態(tài)計(jì)算能力依賴于第一個(gè)參數(shù)的范圍設(shè)置【例11.4】某商店在工作表中保存庫(kù)存和進(jìn)貨記錄，如圖11.9中A:J列所示。由于隨時(shí)可能會(huì)添加進(jìn)貨記錄，因此工作表中的數(shù)據(jù)行是不確定的。計(jì)算表中各種商品的總庫(kù)存量、第一次訂購(gòu)量和再訂購(gòu)量的總和，以及每類產(chǎn)品的總平均費(fèi)用。11.2.2D函數(shù)與表結(jié)合構(gòu)造動(dòng)態(tài)數(shù)據(jù)分析報(bào)表應(yīng)用D函數(shù)和普通數(shù)據(jù)庫(kù)字段計(jì)算出的匯總數(shù)據(jù)在D函數(shù)應(yīng)用表的結(jié)構(gòu)化引用計(jì)算出的匯總數(shù)據(jù)。無(wú)論表區(qū)域的數(shù)據(jù)如何擴(kuò)展，這些統(tǒng)計(jì)數(shù)據(jù)都會(huì)實(shí)時(shí)計(jì)算，自動(dòng)更新，具有完全的動(dòng)態(tài)數(shù)據(jù)分析能力各單元格的公式設(shè)置請(qǐng)看下頁(yè)，詳見(jiàn)教材277頁(yè)的介紹。11.2.2D函數(shù)與表結(jié)合構(gòu)造動(dòng)態(tài)數(shù)據(jù)分析報(bào)表純數(shù)據(jù)庫(kù)，非表格方式輸入下列公式，并右復(fù)制：N3：=DSUM($A$1:$J$83,"庫(kù)存量",N1:N2)N4：=DSUM($A$1:$J$83,"訂購(gòu)量",N1:N2)N5：=DSUM($A$1:$J$850,"再訂購(gòu)量",N1:N2)N6：=DAVERAGE($A$1:$J$850,$J1,N1:N2)D函數(shù)與表結(jié)合使用方式輸入下列公式，并右復(fù)制：N11=DSUM(表3[#全部],表3[[#標(biāo)題],[庫(kù)存量]],N9:N10)N12=DSUM(表3[#全部],表3[[#標(biāo)題],[訂購(gòu)量]],N9:N10)N13=DSUM(表3[#全部],表3[[#標(biāo)題],[再訂購(gòu)量]],N9:N10)N14=DAVERAGE(表3[#全部],表3[[#標(biāo)題],[訂貨費(fèi)]],N9:N10)11.3查找大工作表的特定數(shù)據(jù)行1、概述當(dāng)工作表數(shù)據(jù)行較多時(shí)，要查看其中的某行數(shù)據(jù)并非易事。利用Excel提供的查找菜單或記錄單功能就能夠很快定位到特定數(shù)據(jù)行，實(shí)現(xiàn)高效的查找。本節(jié)案例【例11.5】某單位有600多名職工，其醫(yī)療檔案表如所示，現(xiàn)要從中查看李大友的醫(yī)療費(fèi)用情況。11.5查找大工作表的特定數(shù)據(jù)行1、精確數(shù)據(jù)查找11.3查找大工作表的特定數(shù)據(jù)行1、單擊“開(kāi)始”選項(xiàng)卡2、單擊“查找和選擇”中的“查找”命令3、在“查找內(nèi)容”中輸入查找內(nèi)容4、單擊“查找全部”可以將光標(biāo)定位到找到的數(shù)據(jù)行上2、模糊數(shù)據(jù)查找如果對(duì)要查找的內(nèi)容不太清楚，或者需要查找含有相近但并不相同的文本的記錄，就可使用通配符查找。通配符查找是指用一個(gè)記號(hào)代替不能確定的符號(hào)進(jìn)行查找。Excel使用的通配符記號(hào)有“*”和“？”，*代表任意多個(gè)任意符號(hào)，？代表一個(gè)任意符號(hào)。例如，bd?cd可以是bdacd，bdecd，bd3cd，bc/cd等，bd*cd則可以是bdee2cd，bdacd，bd9088cd等。11.3查找大工作表的特定數(shù)據(jù)行1、單擊“開(kāi)始”選項(xiàng)卡2、單擊“查找和選擇”中的“查找”命令3、在“查找內(nèi)容”中輸入查找內(nèi)容，*代表任意符號(hào)4、單擊“查找全部”可以將光標(biāo)定位到找到的數(shù)據(jù)行上11.4查找及引用函數(shù)1、概述查找引用函數(shù)能通過(guò)單元格引用地址、行、列對(duì)工作表的單元格進(jìn)行訪問(wèn)，還能夠從單元格的引用地址中求出單元格所在的行或列，進(jìn)而查獲更多的信息。當(dāng)需要從一個(gè)工作表查詢特定的值、單元格內(nèi)容、格式或選擇單元格區(qū)域時(shí)，這類函數(shù)特別有用。在大數(shù)據(jù)表、不同工作薄或工作表之間查詢數(shù)據(jù)時(shí)，這類函數(shù)很有用。有時(shí)，將查詢結(jié)果用于公式計(jì)算，能夠事半功倍。11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)1、行列號(hào)函數(shù)的作用行、列號(hào)函數(shù)能夠生成有規(guī)律的自然數(shù)，在Excel公式中經(jīng)常利用它們來(lái)確定單元格在工作表中的引用位置，構(gòu)造靈活多樣的數(shù)據(jù)查詢。行、列函數(shù)的用法如下：2、行號(hào)/行數(shù)計(jì)算函數(shù)ROW([reference])ROWS(array)Row計(jì)算參數(shù)引用的行編號(hào),如果省略參數(shù)則計(jì)算公式所在單元格的行號(hào)Rows計(jì)算參數(shù)引用中包含的行數(shù)。例如，Row(A1)=1，Row(B8)=8，Rows(A1:E1)=1，Rows(A1:E5)=5；11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)3、列號(hào)/列數(shù)計(jì)算函數(shù)COLUMN([reference])COLUMNS(array)Column計(jì)算出指定單元格引用位置所在的列號(hào)，如果省略參數(shù)則計(jì)算公式所在單元格的列號(hào)；Columns計(jì)算指定單元格區(qū)域中的列數(shù)。例如Column(C10)=3，Column(E1)=5Columns(A1:E1)=5Columns(A1:E5)=5Colunms(A1:A100)=1。11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】用行列號(hào)計(jì)算函數(shù)row，rows，column，columns以及文本函數(shù)mid，left，right，text進(jìn)行數(shù)字的按位提取與轉(zhuǎn)換。如圖11.12所示。1、在N2輸入公式，并向左復(fù)制可生成D2:N2區(qū)域的數(shù)據(jù)“=RIGHT(TEXT($C2*100,"￥000;;"),COLUMNS(N:$N))”公式中的$C2*100將C2中的數(shù)據(jù)轉(zhuǎn)換成整數(shù)1235397，再用Text函數(shù)將它轉(zhuǎn)換成文本形式的金額，注意在￥的左邊有空白符一個(gè)，再得用Right函數(shù)依次從右邊截取1、2、3、4、5……位字符并存入N2、M2、L2、K2……公式利用COLUMNS函數(shù)生成了需要的自然數(shù)1、2、3、……。當(dāng)公式從N2復(fù)制到M2、L2、K2……時(shí)，計(jì)算列數(shù)的函數(shù)將分別變成COLUMNS(N:$N))，COLUMNS(M:$N))，COLUMNS(L:$N))……結(jié)果依次為1、2、3……11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】2、N3輸入的公式為=LEFT(RIGHT(TEXT($C3*100,"￥000;;"),COLUMNS(N:$N)))本公式內(nèi)部的RIGHT函數(shù)與上面分析的N2中的公式相同，可知本公式首先用RIGHT提取文本數(shù)字中的右子串，再用Left函數(shù)提取該子串最左邊的一位數(shù)字。當(dāng)從右至左提取到“￥”位置時(shí)，提取到F3、E3、D3中的是空白字符。11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】(3)在D4中輸入下向右復(fù)制下面的公式：

=MID($C4,COLUMN(A1),1)其中的COLUMN(A1)結(jié)果為1，MID將中C1的第1位開(kāi)始提取1位數(shù)字；將公式復(fù)制到D5時(shí)，將變成“=MID($C4,COLUMN(B1),1)”，因COLUMN(B1)結(jié)果為2，公式將提取C4中的第2位數(shù)字……11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】用行列號(hào)計(jì)算函數(shù)row，rows，column，columns以及文本函數(shù)mid，left，right，text進(jìn)行數(shù)字的按位提取與轉(zhuǎn)換。如圖11.12所示。(4)在A8中輸入并向下復(fù)制下面的公式，生成A8:A13中的數(shù)據(jù)公式：="第"&ROW(A1)&"期"公式利用Row函數(shù)分別生成了數(shù)字1，2，3，4。(5)在N8中輸入公式=LEFT(RIGHT(TEXT($C8*100,"￥000;;"),COLUMNS(N:$N)))將此公式向下復(fù)制到N9，再將N8:N9中的公式向左復(fù)制到D8:D9即可生成D8:N9區(qū)域中的數(shù)字。11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】用行列號(hào)計(jì)算函數(shù)row，rows，column，columns以及文本函數(shù)mid，left，right，text進(jìn)行數(shù)字的按位提取與轉(zhuǎn)換。如圖11.12所示。(6)在D10中輸入下面公式，并將此公式向下復(fù)制到D11，向右復(fù)到到N10:N11。=MID(TEXT($C10*100,REPT("",10-LEN($C10*100))&"￥000"),COLUMNS($A:A),1)D10:N10區(qū)域共11個(gè)單元格，LEN($C10*100)計(jì)算出數(shù)字本身要占據(jù)其中的單元格個(gè)數(shù)，還有11-LEN($C10*100)個(gè)單元格沒(méi)有數(shù)字填，其中一個(gè)單元格填寫(xiě)“￥”符號(hào)，因此還有10-LEN($C10*100)個(gè)單元格需要填寫(xiě)空白字符“REPT("",10-LEN($C10*100))”就重復(fù)生成需要填在數(shù)字前面的空白字符?！癟EXT($C10*100,REPT("",10-LEN($C10*100))&"￥000")”創(chuàng)建了恰好夠填滿D10:N10區(qū)域的11個(gè)字符，Mid函數(shù)則應(yīng)用COLUMNS函數(shù)計(jì)算出每次提出到相應(yīng)單元格中的起始位置，提取恰當(dāng)?shù)淖址⑻顚?xiě)到對(duì)應(yīng)單元格中。11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】(7)在N12中輸入公式：=LEFT(RIGHT(TEXT($C12*100,"[dbnum2]￥000;;"),COLUMNS(N:$N)))將此公式向左復(fù)制到D12，即可生成D12:N12區(qū)域中的數(shù)字。本公式與前面的數(shù)字提取公式含義相同，只是在提取前首先用自定義格式“[dbnum2]”將數(shù)字轉(zhuǎn)換為中文大寫(xiě)，然后再提取。11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】(8)在N13中輸入以下公式，再將其向左復(fù)制到D13，即可計(jì)算出D13:N13的數(shù)據(jù)：=LEFT(RIGHT(TEXT($C13*100,"[dbnum1]￥000;;"),COLUMNS(N:$N)))公式中的“[dbnum1]“將數(shù)字設(shè)置為中文小寫(xiě)11.4.1用行、列號(hào)函數(shù)定位與提取數(shù)據(jù)【例11.6】(9)在O1并向下填充復(fù)制下面的輸入公式，生成O列的編號(hào)“=ROWS($A$1:A1)”本公式利用Rows函數(shù)計(jì)算參數(shù)單元格區(qū)域中的行數(shù)，由于區(qū)域中結(jié)束單元格位置采用了相對(duì)應(yīng)用，因此將它向下復(fù)制時(shí)會(huì)自動(dòng)擴(kuò)展，產(chǎn)生連續(xù)的數(shù)字編號(hào)。其特點(diǎn)是：如果刪除表區(qū)域中的數(shù)據(jù)行，編號(hào)會(huì)自動(dòng)更新。11.4.2用INDIRECT函數(shù)和名稱查詢其他工作表中的數(shù)據(jù)Indirect概述Indirect函數(shù)返回由文字串指定的引用。該函數(shù)能夠?qū)σ眠M(jìn)行計(jì)算，并顯示引用的內(nèi)容。當(dāng)需要更改公式中單元格的引用，而不更改公式本身時(shí)，可使用該函數(shù)。格式Indirect(ref_text,A1)其中ref_text為對(duì)單元格的引用，它可以是單元格的名稱、引用或字符串。A1為一邏輯值，指明包含在單元格ref_text中的引用的類型。如果A1為T(mén)RUE或省略，ref_text被解釋為A1樣式的引用。否則ref_text被解釋為R1C1樣式的引用11.4.2用INDIRECT函數(shù)和名稱查詢其他工作表中的數(shù)據(jù)例若單元格A1包含文本“B2”，且單元格B2包含數(shù)值1.333，則INDIRECT($A$1)=1.333；如果將單元格A1中的文本改為“C5”，而單元格C5中包含數(shù)值45，則INDIRECT($A$1)=45；如果B3包含文本“George”，而名字為George的單元格包含數(shù)值10，則INDIRECT($B$3)=10。Indirect與名稱的妙用用名字作它的參數(shù)，可以構(gòu)造非常靈活而高效的查詢，還能使問(wèn)題簡(jiǎn)化?！纠?1.7】某單位的職稱工資表如圖11.13（a）所示，職工基本檔案表如圖11.13（b）所示。假設(shè)圖11.13（b）中除了E列的職稱工資外，其他數(shù)據(jù)都建立完畢，現(xiàn)在要輸入每位職工的職稱工資。11.4.2用INDIRECT函數(shù)和名稱查詢其他工作表中的數(shù)據(jù)（1）指定圖（a）中A2:B11區(qū)域的最左列為名字。（2）在E2單元格中輸入下述公式=Indirect(D2)向下復(fù)制此公式，就能求出所有職工的職稱工資。11.4.3用ADDRESS和OFFSET函數(shù)進(jìn)行定位查找與數(shù)據(jù)提取1．ADDRESS用指定的行列編號(hào)，生成文本形式單元格引用地址，用INDIRECT可將其轉(zhuǎn)換成可用的引用。用法如下。ADDRESS(r,c,abs_num,a1,sheet_text)r是在單元格引用中使用的行號(hào)；c列號(hào)；abs_num指明引用類型。1（或省略）——絕對(duì)引用；2——絕對(duì)行號(hào)，相對(duì)列號(hào)；3——相對(duì)行號(hào)，絕對(duì)列號(hào)；4相對(duì)引用例：ADDRESS(6,3)=$C$6，ADDRESS(6,1,2)=A$6ADDRESS(2,3,1,false,"[Book1]Sheet1")

=[Book1]Sheet1!R2C3，ADDRESS(2,3,1,true,"[Book1]Sheet1")

=[Book1]Sheet1!$C$2。11.4.3用ADDRESS和OFFSET函數(shù)進(jìn)行定位查找與數(shù)據(jù)提取應(yīng)用情況在實(shí)際工作中，許多時(shí)間要根據(jù)工作表，單元格行、列位置動(dòng)態(tài)引用單元格，在這種情況下應(yīng)用Address和行列計(jì)算函數(shù)制作這類報(bào)表是比較方便的11.4.3用ADDRESS和OFFSET函數(shù)進(jìn)行定位查找與數(shù)據(jù)提取【例11.8】有銀行存折信息如圖11.14所示，其中A列是存款或取款的日期，B列是存款信息，C列是取款信息，D列是每發(fā)生一筆存取款信息后存折上的余額。P282存折余額的計(jì)算公式為：存款余額=本期存款–本期取款+前期余額。D2中的公式為：=SUM(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),-INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT(ADDRESS(ROW()-1,COLUMN())))ADDRESS(ROW(),COLUMN()-2)其中，ROW的結(jié)果為2，COLUMN()-2的結(jié)果為4-2；則ADDRESS（2，2）結(jié)果為文本形式的：$B$2；INDIRECT（$B$2）將它轉(zhuǎn)換成可用的引用并求取B2中的值11.4.3用ADDRESS和OFFSET函數(shù)進(jìn)行定位查找與數(shù)據(jù)提取2．OFFSETOFFSET以指定的引用位置為參照點(diǎn)，再根據(jù)指定的偏移量計(jì)算出新的引用位置。函數(shù)的結(jié)果是單元格引用或區(qū)域。用法：OFFSET(reference,rows,cols,[height],[width])參數(shù)說(shuō)明Reference是參照點(diǎn)單元格或區(qū)域。Rows和Cols用于確定新引用位置偏離Reference單元格（若Reference為單元格區(qū)域，則指該區(qū)域左上角單元格）的行數(shù)和列數(shù)。Rows為正數(shù)時(shí)表示新位置在Reference下方的第Rows行，為負(fù)數(shù)時(shí)表示新位置在Reference上方的第Rows行Cols為正數(shù)時(shí)表示新位置在Reference右邊的第cols列，為負(fù)數(shù)時(shí)表示在Reference左邊的第cols列。OFFSET(reference,rows,cols,[height],[width])Offset參數(shù)說(shuō)明Height用于指定所要返回的引用區(qū)域的行數(shù)Width用于指定所要返回的引用區(qū)域的列數(shù)。Height和Width必須為正數(shù)。如果省略height或width，則假設(shè)其高度或?qū)挾扰creference相同。如果行數(shù)和列數(shù)偏移量超出工作表邊緣，函數(shù)OFFSET返回錯(cuò)誤值#REF!。注意OFFSET實(shí)際上并不移動(dòng)任何單元格或更改選定區(qū)域，它只是返回一個(gè)引用，可用于任何需要將引用作為參數(shù)的函數(shù)。OFFSET(reference,rows,cols,[height],[width])Offset簡(jiǎn)單實(shí)例SUM(OFFSET(C2,1,2,3,1))

OFFSET(C2,1,2,3,1)確定單元格C2靠下1行右2列的3行1列的區(qū)域，即E3:E5，原公式相當(dāng)于“=SUM（E3:E5）”O(jiān)FFSET(A8,4,5)，A8下4行右5列，即

F12，OFFSET(A8,-4,5)，A8上4行右5列，為F4，OFFSET(E8,-4,-2)，E8上4行左2列，為C4，OFFSET(A8:D12,4,5)，A8下4行右5列與A8:D12大小相同的區(qū)域，結(jié)果為

F12:I16OFFSET(A8:D12,-4,5)的結(jié)果為F4:I8，OFFSET(E8:F11,-4,-2)結(jié)果為C4:D7，OFFSET(A1,,)等效于OFFSET(A1,0,0)，結(jié)果為A111.4.3用ADDRESS和OFFSET函數(shù)進(jìn)行定位查找與數(shù)據(jù)提取【例11.9】某單位進(jìn)行職工技能測(cè)試大賽，分為四組，每組6人，每人進(jìn)行兩次測(cè)試，測(cè)試達(dá)標(biāo)通過(guò)率情況如圖11.15中A1:H13所示。計(jì)算每組的平均通過(guò)率，如圖中B15:C18區(qū)域所示；對(duì)數(shù)據(jù)進(jìn)行規(guī)范化處理，如圖中K1:Q13區(qū)域所示；提取每組的參賽職工名單，如圖中K15:Q18區(qū)域所示。1．用Count和OFFSET函數(shù)在合并單元格生成連續(xù)序號(hào)如果需要在大小不同的合并單元格中生成連續(xù)的序號(hào)，可以使用OFFSET函數(shù)計(jì)算單元格位置，再用Count函數(shù)對(duì)偏移的單元格進(jìn)行計(jì)數(shù)，則可生成連續(xù)的編號(hào)本例中選中A2:A13區(qū)域；輸入公式=1+COUNT(OFFSET($A$1,,,ROW()-1,))然后按CTRL+ENTER，即可生成A列的編號(hào)注意：如果要編號(hào)的合并單元格大小不一，如有的由兩個(gè)單元格合并而成，有的由三個(gè)或四個(gè)合并而成，則需要嚴(yán)格按上述兩步驟操作。如果每個(gè)合并單元格大小相同，也可以在第一個(gè)合并單元格中輸入上述公式后，再將其填充復(fù)制到其它單元格，生成需要的序號(hào)。2．用OFFSET從合并單元格提取數(shù)據(jù)在B15中輸入公式=OFFSET($B$1,ROW(B1)*3-2,)向下復(fù)制到B18，即可將B2:B13區(qū)域中的組別提取到B15:B18中。OFFSET函數(shù)B2開(kāi)始，通過(guò)ROW(B1)*3-2中相對(duì)引用單元格的變化，每間隔3行提取B列對(duì)應(yīng)單元格的數(shù)據(jù)，即B2、B5、B8、B11，正好將各個(gè)分組提取完畢。3．用OFFSET提取和計(jì)算動(dòng)態(tài)數(shù)據(jù)區(qū)域在C15中輸入公式=TEXT(AVERAGE(OFFSET($C$1,ROW(C1)*3-1,0,2,6)),"0.00%")其中“OFFSET($C$1,ROW(C1)*3-1,0,2,6)”計(jì)算的結(jié)果為C1向下偏移2行右移0列，取2行高6列寬的區(qū)域，即C3:H4Average計(jì)算C3:H4區(qū)域的平均數(shù)，其結(jié)果為小數(shù)，再用Text函數(shù)將此小數(shù)格式化為小數(shù)點(diǎn)后面保留2位小數(shù)的百分?jǐn)?shù)。將C15中的公式向下復(fù)到C18，在復(fù)制過(guò)程中，ROW中的C1引用分別變成C2、C3、C4，即可計(jì)算出每組的測(cè)試平均數(shù)4．用OFFSET重復(fù)生成合并單元格中的數(shù)據(jù)在K2中輸入公式=IF(B2<>"",B2,OFFSET(K2,-1,))將其向下復(fù)制到K13單元格，即可生成K2:K13區(qū)域中的組別。注意：合并單元格的數(shù)據(jù)在左上角單元格中。公式首先判B2若非空，其計(jì)算結(jié)果即為B2，K2由此變?yōu)椤暗谝唤M”，當(dāng)將此公式復(fù)制到B3時(shí)，即變?yōu)椤?IF(B3<>"",B3,OFFSET(K3,-1,))”，由于B3為空，公式的結(jié)果為OFFSET(K3,-1,)，即K2單元格中的值。將K2:K13區(qū)域中的公式向右復(fù)制到Q2:Q13即可生成K2:Q13區(qū)域的全部數(shù)據(jù)。5．用OFFSET提取工作表中固定間隔行的數(shù)據(jù)在K15中輸入公式=OFFSET($B$1,ROW(B1)*3-2,COLUMN(B2)-2)向右復(fù)制此公式到Q15，再將K15:Q15中的公式向下復(fù)制到K18:Q18即可生成每組的測(cè)試職工名單公式ROW(B1)*3-2的結(jié)果為1，COLUMN(B2)-2為0，因此原公式的結(jié)果相當(dāng)于“=OFFSET($B$1,1,0)”結(jié)果為B2單元格；復(fù)制到L15時(shí)變成“=OFFSET($B$1,ROW(C1)*3-2,COLUMN(C2)-2)”，相當(dāng)于“=OFFSET($B$1,1,1)”，結(jié)果為C2……11.4.4Choose函數(shù)進(jìn)行值查詢Choose函數(shù)格式Choose(n,v1,v2,...v254)其中n是一個(gè)整數(shù)值，用以指明待選參數(shù)的序號(hào)。n必須為1~254之間的數(shù)字或者是包含數(shù)字1~254的公式或單元格引用。如果n為1，函數(shù)的值就為v1；如果為2，函數(shù)返回v2，以此類推v1，v2，…為1~254個(gè)數(shù)值參數(shù)，可以是數(shù)字、單元格引用，區(qū)域，或者已定義的名稱、公式、函數(shù)或文本。功能利用索引從參數(shù)清單中選擇需要的數(shù)值，11.4.4Choose函數(shù)進(jìn)行值查詢案例【例11.10】某學(xué)校為了提高教學(xué)質(zhì)量，讓學(xué)生對(duì)教師的授課情況進(jìn)行評(píng)價(jià)。評(píng)價(jià)采用百分制，如圖的E列所示。現(xiàn)要將學(xué)生評(píng)價(jià)轉(zhuǎn)換成等級(jí)制。轉(zhuǎn)換規(guī)則是：0~60為不及格，60~70為及格，70~80為中，80~90為良，90~100為優(yōu)。用Choose函數(shù)進(jìn)行轉(zhuǎn)換的方法是，在F2單元格輸入公式：=CHOOSE(IF(E2<60,1,INT((E2-50)/10)+1),"不及格","及格","中","良","優(yōu)")11.4.5用match和index函數(shù)構(gòu)造靈活的查詢1、概述Match函數(shù)提供了比lookup（或Vlookup、Hlookup）函數(shù)更多的靈活性，它可以在工作表的一行（或一列）中進(jìn)行數(shù)據(jù)查找，并返回?cái)?shù)據(jù)在行（或列）中的位置。如果需要找出數(shù)據(jù)在某行（或某列）的位置，就應(yīng)該使用Match函數(shù)而不是Lookup函數(shù)在多數(shù)情況下，Match函數(shù)的結(jié)果并不是所需要的最終答案，而是作為lookup（Vlookup，Hlookup）的第3個(gè)參數(shù)，或作為Index函數(shù)的參數(shù)11.4.5用match和index函數(shù)構(gòu)造靈活的查詢Match格式Match(x，r，f)其中x是要查找的數(shù)值，r可以是一個(gè)數(shù)組常量，或某列（或行）連續(xù)的單元格區(qū)域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1，表11-3給出了這幾個(gè)取值的含義。功能Match(x，r，f)表示的意思是：在數(shù)組或連續(xù)的單元格區(qū)域r中查找x，并返回x在r中的位置編號(hào)。當(dāng)f為0是，match進(jìn)行精確查找，當(dāng)f為1（或-1）時(shí)，match進(jìn)行模糊查找。11.4.5用match和index函數(shù)構(gòu)造靈活的查詢?nèi)≈岛瘮?shù)功能-1r必須按降序排列，查找大于或等于x的最小數(shù)值0r不必排序，查找等于x的第一個(gè)數(shù)值1r必須按升序排列，查找小于或等于x的最大數(shù)值表11-3Match查找的方式11.4.5用match和index函數(shù)構(gòu)造靈活的查詢2、Index函數(shù)格式Index(Area，r，c，n)其中，Area是1個(gè)或多個(gè)單元格區(qū)域；r是某行的行序號(hào)，c是某列的列序號(hào)，該函數(shù)返回指定的行與列交叉處的單元格引用。如果r等于0，則返回整行單元格引用，如果c等于0，則返回整列單元格引用。當(dāng)Area包括多個(gè)單元格區(qū)域時(shí)，n=1就表示結(jié)果來(lái)自于Area中的第1個(gè)區(qū)域，n=2表示結(jié)果來(lái)源于第2個(gè)單元格區(qū)域……。如果省略n表示結(jié)果來(lái)源于第1個(gè)單元格區(qū)域。功能Index（Area，r，c，n）的功能是返回Area中第n個(gè)單元格區(qū)域中的r行，c列交叉處的單元格引用。11.4.5用match和index函數(shù)構(gòu)造靈活的查詢3.用INDEX和MATCH進(jìn)行精確查找【例11.11】某地域中各縣的蔬菜銷售單價(jià)表如圖的A4:J18區(qū)域所示，希望能夠快捷地查找到某地某蔬菜的單價(jià)。最好是輸入地名和蔬菜名，就能看到對(duì)應(yīng)的蔬菜單價(jià)，如圖B1:D3區(qū)域所示。2在D3單元格中輸入公式：=INDEX(A5:J18,MATCH(B3,A5:A18,0),MATCH(C3,A5:J5,0))11.4.5用match和index函數(shù)構(gòu)造靈活的查詢4.用INDEX和MATCH進(jìn)行模糊查詢【例11.12】某單位的職工收入表如圖11.18的D～F列所示，現(xiàn)要計(jì)算每個(gè)職工應(yīng)納的個(gè)人所得稅稅率。為了便于數(shù)據(jù)對(duì)比，將各種收入所對(duì)應(yīng)的個(gè)人所得稅稅率列于圖11.18的A2:B10單元格區(qū)域內(nèi)（若這些數(shù)據(jù)處于另一個(gè)獨(dú)立的工作表中，查找方法完全相同）。

在G3單元格中下向下復(fù)制下面公式，模糊查找出G列數(shù)據(jù)=INDEX($A$3:$B$10,MATCH($F3,$A$3:$A$10,1),2)MATCH($F3,$A$3:$A$10,1)在A3:A10中找到最大的那個(gè)小于等于F3的單元格。F3的內(nèi)容為6582，而A3:A10中最大的那個(gè)小于6582的數(shù)是5000，它位于A3:A10區(qū)域內(nèi)的第8個(gè)位置，所以MATCH($F3,$A$3:$A$10,1)結(jié)果是8。因此，原公式就相當(dāng)于INDEX($A$3:$B$10,8,2)，則該函數(shù)將返回A3:B10區(qū)域內(nèi)的第8行、第2列所對(duì)應(yīng)的單元格引用，即B10單元格。B10的稅率為25%，這正好是工資6825所對(duì)應(yīng)的個(gè)人所得稅稅率。11.4.5用match和index函數(shù)構(gòu)造靈活的查詢5.用INDEX和MATCH進(jìn)行重復(fù)數(shù)據(jù)判斷應(yīng)用INDEX、MATCH和其它統(tǒng)計(jì)函數(shù)，可以查找或標(biāo)識(shí)出工作表中的重復(fù)數(shù)據(jù)。例如，在圖11.18中，要判斷D列哪些姓名重復(fù)了，重復(fù)最多的是哪個(gè)姓名？（1）標(biāo)識(shí)重復(fù)出現(xiàn)的姓名在H3中輸入公式，將此公式向下復(fù)制到H12，標(biāo)識(shí)出重復(fù)的姓名。=IF(MATCH(D3,D$3:D$12,0)=ROW(A1),"","重復(fù)")”原理Row(A1)=1，如果D3中的姓名在D3:D12區(qū)域中只出現(xiàn)一次，MATCH(D3,D$3:D$12,0)的結(jié)果為1，IF函數(shù)的結(jié)果為空白串""，在H3中就不會(huì)有顯示；如果D3中的姓名在D3:D12中重復(fù)了，MATCH函數(shù)將返回該姓名最后一次出現(xiàn)的位置，這個(gè)位置一定不等于Row(A1)，IF條件不成立，函數(shù)結(jié)果為“重復(fù)”11.4.5用match和index函數(shù)構(gòu)造靈活的查詢5.用INDEX和MATCH進(jìn)行重復(fù)數(shù)據(jù)判斷應(yīng)用INDEX、MATCH和其它統(tǒng)計(jì)函數(shù)，可以查找或標(biāo)識(shí)出工作表中的重復(fù)數(shù)據(jù)。例如，在圖11.18中，要判斷D列哪些姓名重復(fù)了，重復(fù)最多的是哪個(gè)姓名？（2）找出重復(fù)出現(xiàn)次數(shù)最多的姓名在I3中輸入公式即可找出D3:D12區(qū)域中出現(xiàn)次數(shù)最多的姓名=INDEX(D3:D12,MODE(MATCH(D3:D12,D3:D12,0)))原理MATCH(D3:D12,D3:D12,0)逐個(gè)查找D3:D12區(qū)域中每個(gè)姓名在D3:D12區(qū)域中出現(xiàn)的位置，重復(fù)最多的姓名，MATCH函數(shù)返回它的位置就最多。MODE是取眾數(shù)的函數(shù)，它返回一組數(shù)中出現(xiàn)次數(shù)最多的數(shù)字，它會(huì)返回出現(xiàn)重復(fù)次數(shù)最多的姓名在D3:D12第一次出現(xiàn)的位置，INDEX再據(jù)此位置查詢得取重復(fù)最多的姓名。11.4.6用Lookup函數(shù)進(jìn)行表查找Lookup函數(shù)Lookup函數(shù)在一個(gè)大表中找出特定數(shù)據(jù)，并在其它工作表中引用查找結(jié)果，在工作中應(yīng)用較廣。功能從給定的向量（單行或單列單元格區(qū)域）或數(shù)組中查詢出需要的數(shù)值。格式Lookup(x,r1,r2)其中：x是要查找的內(nèi)容，它可以是數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。r1、r2都是只包含一行或一列的單元格區(qū)域，其值可以是文本、數(shù)字或邏輯值。r2的大小必須與r1相同。Lookup函數(shù)在r1所在的行或列中查找值為x的單元格，找到后返回r2中與r1同行或同列的單元格中的值。1、用lookup函數(shù)在普通工作表中查找數(shù)據(jù)【例11.13】某蔬菜供應(yīng)商在一個(gè)工作表中保存蔬菜的單價(jià)和出產(chǎn)地，如圖（a）所示。在另一工作表中保存銷售記錄，如圖（b）所示。在圖（b）中，蔬菜名和數(shù)量是實(shí)際輸入的數(shù)據(jù)，產(chǎn)地和單價(jià)需要根據(jù)產(chǎn)品名從圖（a）所示的蔬菜單價(jià)表中查詢輸入11.4.6用Lookup函數(shù)進(jìn)行表查找D3中輸入下述公式：=LOOKUP(A3,蔬菜單價(jià)表!$A$2:$A$11,蔬菜單價(jià)表!$B$2:$B$11)向下復(fù)制此公式，查出蔬菜的單價(jià)在B3中輸入下述查找公式：=LOOKUP(A3,蔬菜單價(jià)表!$A$2:$A$11,蔬菜單價(jià)表!$C$2:$C$11)向下復(fù)制此公式，查出蔬菜的產(chǎn)地11.4.6用Lookup函數(shù)進(jìn)行表查找案例解決方法（1）建立圖（a）所示的蔬菜單價(jià)表，并按升序?qū)υ摴ぷ鞅磉M(jìn)行排序，排序主關(guān)鍵字為“蔬菜”。（2）輸入圖（b）的A列數(shù)據(jù)，和第1、2行的標(biāo)題。在B3中輸入下述查找公式，然后向下填充復(fù)制該公式，就可找出各蔬菜的產(chǎn)地。=LOOKUP(A3,蔬菜單價(jià)表!$A$2:$A$11,蔬菜單價(jià)表!$C$2:$C$11)查找蔬菜單價(jià)的方法與此完全類似，只需要在圖（b）的D3單元格中輸入下述公式，然后向下復(fù)制該公式就行了。=LOOKUP(A3,蔬菜單價(jià)表!$A$2:$A$11,蔬菜單價(jià)表!$B$2:$B$11)11.4.6用Lookup函數(shù)進(jìn)行表查找案例解決方法（1）建立圖（a）所示的蔬菜單價(jià)表，并按升序?qū)υ摴ぷ鞅磉M(jìn)行排序，排序主關(guān)鍵字為“蔬菜”。（2）輸入圖（b）的A列數(shù)據(jù)，和第1、2行的標(biāo)題。在B3中輸入下述查找公式，然后向下填充復(fù)制該公式，就可找出各蔬菜的產(chǎn)地。=LOOKUP(A3,蔬菜單價(jià)表!$A$2:$A$11,蔬菜單價(jià)表!$C$2:$C$11)查找蔬菜單價(jià)的方法與此完全類似，只需要在圖（b）的D3單元格中輸入下述公式，然后向下復(fù)制該公式就行了。=LOOKUP(A3,蔬菜單價(jià)表!$A$2:$A$11,蔬菜單價(jià)表!$B$2:$B$11)11.4.6用Lookup函數(shù)進(jìn)行表查找注意：①r1中的內(nèi)容必須按升序排序，查找的字符文本不區(qū)分大小寫(xiě)。否則，Lookup函數(shù)不能返回正確的結(jié)果。②如果Lookup函數(shù)找不到x，則查找r1中小于或等于x的最大數(shù)值。如果x小于r1中的最小值，Lookup函數(shù)返回錯(cuò)誤值“#N/A”。11.4.6用Lookup函數(shù)進(jìn)行表查找2、在lookup函數(shù)通過(guò)對(duì)表的結(jié)構(gòu)化引用查找數(shù)據(jù)對(duì)于例11.13中的數(shù)據(jù)查找問(wèn)題，較好的方法是將蔬菜單價(jià)表保存在表中，然后在LOOKUP函數(shù)中通過(guò)表的結(jié)構(gòu)化引用查找蔬菜單價(jià)，這樣的優(yōu)點(diǎn)是在表中添加新的蔬菜品種時(shí)，LOOKUP函數(shù)不用修改就可以對(duì)新增加的蔬菜進(jìn)行單價(jià)查詢，而且意義更清楚11.4.7用Vlookup函數(shù)進(jìn)行表查找Vlookup函數(shù)功能Vlookup按列查找的方式從指定數(shù)據(jù)表區(qū)域的最左列查找特定數(shù)據(jù)，它能夠返回查找區(qū)域中與找到單元格位于相同行不同列的單元格內(nèi)容。格式Vlookup(x,table,n,f)其中，x是要查找的值；table是一個(gè)單元格區(qū)域；n中table區(qū)域中要返回的數(shù)據(jù)所在列的序號(hào)。n=1時(shí)，返回table第1列中的數(shù)值；n=2時(shí)，返回table第2列中的數(shù)值；以此類推。f是一個(gè)邏輯值，表示查找的方式。當(dāng)其為true（或1）時(shí)，表示模糊查找；當(dāng)它為false（或0）時(shí)，表示精確查找。11.4.7用Vlookup函數(shù)進(jìn)行表查找說(shuō)明：Vlookup函數(shù)在table區(qū)域的第1列中查找值為x的數(shù)值，如果找到，就返回與找到數(shù)據(jù)同行第n列單元格中的數(shù)據(jù)。當(dāng)f為true時(shí)，table的第1列數(shù)據(jù)必須按升序排列，否則找不到正確的結(jié)果；當(dāng)f為false時(shí)，table的第1列數(shù)據(jù)不需要排序。注意①如果Vlookup函數(shù)找不到x，且f=true，則返回小于等于x的最大值。②如果x小于table第1列中的最小值，Vlookup函數(shù)返回錯(cuò)誤值“#N/A”。③如果Vlookup函數(shù)找不到x且f=FALSE，Vlookup函數(shù)返回錯(cuò)誤值“#N/A”。11.4.7用Vlookup函數(shù)進(jìn)行表查找1、用Vlookup進(jìn)行模糊查找模糊查找也就是常說(shuō)的近似查找，常用于數(shù)據(jù)轉(zhuǎn)換或數(shù)據(jù)對(duì)照表中的數(shù)據(jù)查找。案例【例11.14】假設(shè)所得稅的稅率如圖的A1:B10區(qū)域所示。其中的含義是：0~500的稅率為0%，500~1000的稅率為1%，1000~1500的稅率為3%……，4000以上的稅率為20%。某公司的職工收入數(shù)據(jù)如圖的D1:J11所示，現(xiàn)在計(jì)算每位職工應(yīng)繳的所得稅。11.4.7用Vlookup函數(shù)進(jìn)行表查找I列的所得稅率的計(jì)算方法如下。在I3單元格中輸入下述公式，然后向下復(fù)制此公式，就能夠計(jì)算出每位職工的所得稅率。=Vlookup(H3,$A$3:$B$10,2,1)公式的含義是：在A3:B10區(qū)域中的第1列數(shù)據(jù)中（即A3:A10），查找與單元格H3內(nèi)容（即11454）最接近的單元格，然后返回A3:B10區(qū)域第2列（即B列）與找到單元格（即A10）同行單元格的內(nèi)容（即B10）。11.4.7用Vlookup函數(shù)進(jìn)行表查找2、用Vlookup進(jìn)行精確查找概述精確查找就是指查找數(shù)據(jù)完全匹配的查找，Vlookup函數(shù)具有此項(xiàng)功能。在大表中查找特定數(shù)據(jù)，或查找不同工作表中的數(shù)據(jù)，特別是工作表數(shù)據(jù)較多，Vlookup函數(shù)顯得非常有效11.4.7用Vlookup函數(shù)進(jìn)行表查找案例【例11.15】某學(xué)校職工收入由基本工資、獎(jiǎng)金、課時(shí)費(fèi)等部分組成，這些金額分別保存在同名的基本工資、獎(jiǎng)金和課時(shí)費(fèi)表中，如圖11.22(b)(c)(d)所示?，F(xiàn)在要形成圖11.22(a)所示的匯總表，并計(jì)算職工的總收入。在四張數(shù)據(jù)表中，只有職工收入表和基本工資表的數(shù)據(jù)包括了全部職工，有些職工沒(méi)有獎(jiǎng)金，有些職工沒(méi)有課時(shí)費(fèi)，所以獎(jiǎng)金表和課時(shí)費(fèi)表中只有部分職工信息11.4.2用Vlookup函數(shù)進(jìn)行表查找案例解決方法（1）用Vlookup精確查找基本工資、獎(jiǎng)金和課時(shí)費(fèi)在圖11.22(a)單位列的C2單元格輸入下面的公式，然后向下填充復(fù)制此公式，即可查詢出每位職工的單位。=VLOOKUP(A2,基本工資!$A$1:$D$279,4,0)在11.22(a)的E2中輸入查找獎(jiǎng)金的公式：=VLOOKUP(A2,獎(jiǎng)金!$A$1:$D$279,4,0)在11.22(a)的F2中輸入查找課時(shí)費(fèi)的公式：=VLOOKUP(A2,課時(shí)費(fèi)!$A$1:$D$279,4,0)11.4.2用Vlookup函數(shù)進(jìn)行表查找案例解決方法2）處理Vlookup錯(cuò)誤如果沒(méi)有找到相應(yīng)的數(shù)據(jù)，Vlookup函數(shù)將返回#N/A錯(cuò)誤信息，如果不處理這些錯(cuò)誤數(shù)據(jù)，還會(huì)導(dǎo)致其它計(jì)算錯(cuò)誤。例如，圖11.23中G列通過(guò)Sum函數(shù)計(jì)算D、E、C三列數(shù)據(jù)的總和，從中可以看出F列的錯(cuò)誤也導(dǎo)致了G列的#N/A錯(cuò)誤，無(wú)法算出對(duì)應(yīng)職工的總收入。在H2中輸入公式：=if(isna(vlookup($a2,indirect(f$1&"!$a$1:$d$500"),4,0)),0,

vlookup($a2,indirect(f$1&"!$a$1:$d$500"),4,0))其意思是如果Vlookup查詢有錯(cuò)誤，if函數(shù)的值為0，否則IF函數(shù)就取Vlookup查詢的結(jié)果。即用0代替了Vlookup的錯(cuò)誤值11.4.2用Vlookup函數(shù)進(jìn)行表查找（3）Vlookup和Indirect相結(jié)合構(gòu)造通用查詢由于基本工資、獎(jiǎng)金和課時(shí)費(fèi)三張數(shù)據(jù)表的結(jié)構(gòu)完全相同（見(jiàn)圖11.22(b)(c)(d)），這些表的第四列數(shù)據(jù)是要查詢提取的數(shù)據(jù)。此外，圖11.22(a)中D1:F1對(duì)應(yīng)列的標(biāo)題名稱與其數(shù)據(jù)所在的工作表名稱對(duì)應(yīng)相同，可用Indirect函數(shù)將此區(qū)域中的列標(biāo)題取出構(gòu)造出要查詢出的工作表名稱和區(qū)域，創(chuàng)建通用的數(shù)據(jù)表查詢公式。因此，圖11.23中基本工資、獎(jiǎng)金和課時(shí)費(fèi)的查詢和提取，可用下面的方法實(shí)現(xiàn)。在D2中輸入公式，向右復(fù)制此公式到F2，向下復(fù)制此公式到最后一個(gè)職工的數(shù)據(jù)行，即可查詢出所有職工的基本工資、獎(jiǎng)金和課時(shí)費(fèi)=VLOOKUP($A2,INDIRECT(D$1&"!$A$1:$D$500"),4,0)公式的巧妙在于用INDIRECT函數(shù)生成了工作表名稱和數(shù)據(jù)區(qū)域：INDIRECT(D$1&"!$A$1:$D$500")”的計(jì)算結(jié)果為“基本工資!$A$1:$D$500”11.4.2用Vlookup函數(shù)進(jìn)行表查找（4）Vlookup、Match結(jié)合構(gòu)造動(dòng)態(tài)列序的查詢?cè)S多時(shí)間需要從原數(shù)據(jù)表中查詢提取多列數(shù)據(jù)，形成報(bào)表或構(gòu)造綜合信息查詢。如果查詢結(jié)果數(shù)據(jù)的次序與原始數(shù)據(jù)表中的順序相同，則可以通過(guò)查詢公式的復(fù)制快速完成；如果次序不同，則需要對(duì)復(fù)制后的查詢公式進(jìn)行修改。在Vlookup函數(shù)中應(yīng)用Match函數(shù)動(dòng)態(tài)變通數(shù)據(jù)表行列位置，可以構(gòu)造滿足這一需求的動(dòng)態(tài)序列查詢。【例11.16】某學(xué)校職工收入明細(xì)數(shù)據(jù)如圖11.24中A11:G289區(qū)域所示。由于人數(shù)太多，需要按照職工編號(hào)查詢?cè)撀毠さ母黜?xiàng)收入明細(xì)。如圖中A2:G4區(qū)域所示。要求在C2中輸入職工的編號(hào)后，就查詢顯示出該職工的姓名、單位、資金、基本工資等數(shù)據(jù)。原始數(shù)表在C2中輸入職工編號(hào)，就會(huì)從原數(shù)據(jù)表中查詢到該職工的各項(xiàng)數(shù)據(jù)在B8、C8中分別輸入職工姓名、單位，從原數(shù)據(jù)表中查詢到該職工的各項(xiàng)數(shù)據(jù)在C4中輸入下面的公式，將它從C4向右復(fù)制到G4，即可查詢出C2中職工編號(hào)對(duì)應(yīng)的單位、獎(jiǎng)金、基本工資、課時(shí)費(fèi)和收入總計(jì)。=VLOOKUP($C2,$B$11:$G$289,MATCH(C3,$B$11:$G$11,0),0)公式含義不難理解：在B11:G289區(qū)域中查詢C2中的職工編號(hào)，但找到后的數(shù)據(jù)列由MATCH(C3,$B$11:$G$11,0)決定，C3中為獎(jiǎng)金，它位于B11:G11區(qū)域的第4列，所以結(jié)果為4，因此VLOOLUP函數(shù)將返回B11:G289區(qū)域中找到職工號(hào)同行第4列的數(shù)據(jù)，即該職工的獎(jiǎng)金；當(dāng)將C4中的公式復(fù)制到D4后，MATCH中的相對(duì)引用會(huì)改變，變成MATCH(D3,$B$11:$G$11,0)，其結(jié)果為3……Vlookup、Match結(jié)合構(gòu)造動(dòng)態(tài)列序查詢職工的獎(jiǎng)金、基本工資、課時(shí)費(fèi)、收入總計(jì)問(wèn)題：通過(guò)觀察可知，查詢報(bào)表頭B3:G3和原數(shù)據(jù)表頭B11:G11的次序不同，但是B3:G3區(qū)域中的標(biāo)題都在源數(shù)據(jù)表中職工編號(hào)的右邊，而職工編號(hào)是從原數(shù)據(jù)表中查詢數(shù)據(jù)的依據(jù)。像這樣的查詢可以用在Vlookup函數(shù)中用Match定位要查詢的數(shù)據(jù)列，實(shí)現(xiàn)動(dòng)態(tài)查詢。但是姓名列數(shù)據(jù)在原數(shù)據(jù)表中位于查詢依據(jù)列（職工編號(hào)）左邊，就不能用這種方式查詢。因?yàn)閂LOOKUP函數(shù)要求要查詢的數(shù)據(jù)必須位于原表中查詢依據(jù)列的右邊。（5）Vlookup和Choose結(jié)合構(gòu)造向左的列查詢問(wèn)題：如何根據(jù)職工編號(hào)查詢職工姓名呢？VLOOUP函數(shù)只能在查詢數(shù)據(jù)區(qū)域的第1列（最左列）查找指定的數(shù)據(jù)，但在原數(shù)據(jù)A11:G289區(qū)域中，姓名位于職工編號(hào)的左邊，不能用Vlookup函數(shù)直接查詢。對(duì)于這種情況，可以用Choose函數(shù)通過(guò)數(shù)組的方式，對(duì)原數(shù)據(jù)表的列進(jìn)行交換，構(gòu)造出一個(gè)符合Vlookup函數(shù)查詢需求的內(nèi)存數(shù)組，再用Vlookup函數(shù)從此數(shù)組中查詢。

在B4中輸入下面的公式，即可根據(jù)C2中的職工編號(hào)查詢出對(duì)應(yīng)的職工姓名。=VLOOKUP(C2,CHOOSE({1,2},B11:B289,A11:A289),2,0)公式將先執(zhí)行Choose函數(shù)，先用數(shù)組{1,2}中的第一個(gè)元素1選取了B11:B289數(shù)據(jù)區(qū)域，再用數(shù)組元素2選取A11:A289，構(gòu)造了一個(gè)B列在前，A列在后的內(nèi)存數(shù)組。最后VLOOKUP從該內(nèi)存數(shù)組中查詢（5）Vlookup和Choose結(jié)合構(gòu)造向左的列查詢從根據(jù)職工編號(hào)查找姓名的事例可知，通過(guò)CHOOSE函數(shù)可以交換原數(shù)據(jù)表的列次序，形成具有新列次序的內(nèi)存二維數(shù)組。由此可得到啟示：通過(guò)Choose函數(shù)，可將原表職工編號(hào)交換到第一列，并按查詢表數(shù)據(jù)列的先后次序?qū)⒃紨?shù)據(jù)表交換次序形成內(nèi)存數(shù)組，然后可以通過(guò)VLOOKUP函數(shù)一次性動(dòng)態(tài)查詢出職工的姓名、單位、獎(jiǎng)金、基本工資等全部數(shù)據(jù)。在B6中輸入下面的公式，將其向右復(fù)到到G6，可查詢得到第6行全部數(shù)據(jù)，請(qǐng)思考：此公式是如何查詢提取數(shù)據(jù)的呢？=VLOOKUP($C$2,CHOOSE({1,2,3,4,5,6,7},$B11:$B400,$A11:$A400,$C11:$C400,$E11:$E400,$D11:$D400,$F11:$F400,$G11:$G400),COLUMN(B1),0)CHOOSE函數(shù)構(gòu)造了數(shù)據(jù)列序如下的內(nèi)存數(shù)組，VLOOKUP函數(shù)在這個(gè)內(nèi)存數(shù)組中查詢數(shù)據(jù)，找到職工編號(hào)后，只需要依次提取數(shù)據(jù)即可，因此用了COLUMN函數(shù)提取數(shù)據(jù)列B列A列C列E列D列F列G列職工編號(hào)姓名單位獎(jiǎng)金基本工資課時(shí)費(fèi)收入總計(jì)

（6）用數(shù)組公式構(gòu)造多列組合查詢此查詢需求有兩個(gè)問(wèn)題：其一是查詢關(guān)鍵字由兩個(gè)字段組成；其二是查詢列不在數(shù)據(jù)區(qū)域的第一列。應(yīng)用Choose函數(shù)進(jìn)行數(shù)據(jù)列交換的技術(shù)和數(shù)組公式，能夠輕易完成查詢要求。在D9中輸入下面的公式:=vlookup($B9&$C9,choose({1,2,3},$A11:$A400&$C11:$C400,$B11:B400,$E11:$E400),column(B2),0)輸入完成后按Ctrl+Shift+Enter組合鍵，然后將此公式復(fù)制到E9，即可查詢出職工編號(hào)和獎(jiǎng)金。公式的利用了CHOOSE函數(shù)構(gòu)造內(nèi)存數(shù)組，數(shù)組第1列是由原數(shù)據(jù)A列和C列組合成的，請(qǐng)同學(xué)們結(jié)合前面的分析思考此公式的查詢?cè)怼?/p>

需要根據(jù)多個(gè)組合字段查詢其它信息。例如，要根據(jù)職工的姓名和單位信息才能查詢職工編號(hào)和獎(jiǎng)金（不同單位有同名職工，不能據(jù)姓名查編號(hào)）。

要求，在B9輸入職工姓名，C9輸入職工的單位后，在D9中就顯示出該職工的編號(hào)，在E9中顯示出他的獎(jiǎng)金。11.4.用Vlookup函數(shù)進(jìn)行表查找應(yīng)用案例2（補(bǔ)充內(nèi)容）【例11.16-OLD】某電話公司的電話收費(fèi)系統(tǒng)進(jìn)行了系統(tǒng)升級(jí)，下頁(yè)圖（a）是系統(tǒng)升級(jí)前的電話號(hào)碼和收費(fèi)賬號(hào)對(duì)照表，圖（b）的是升級(jí)后的收費(fèi)表。升級(jí)后系統(tǒng)新加了一些號(hào)碼，新加的號(hào)碼要重新編制賬號(hào)，但原有號(hào)碼的賬號(hào)則需要從舊系統(tǒng)中查詢。也就是說(shuō)，圖（b）中的絕大部分號(hào)碼的賬號(hào)（B列數(shù)據(jù)）要從圖（a）的B列查詢。11.4.7用Vlookup函數(shù)進(jìn)行表查找在圖的B3單元格輸入下述查找公式，然后向下復(fù)制此公式，就能查找到所有舊號(hào)碼的賬號(hào)。

=VLOOKUP(A3,舊賬號(hào)!A$2:$B$6884,2,0)11.4.7用Vlookup函數(shù)進(jìn)行表查找說(shuō)明在Excel中還有一個(gè)常用的查找函數(shù)Hlookup，其用法與Vlookup函數(shù)完全相同。它按行方式進(jìn)行數(shù)據(jù)查找，而Vlookup函數(shù)按列方式進(jìn)行數(shù)據(jù)查找。因時(shí)間所限，這里不作介紹，同學(xué)們可從Excel的幫助信息中查找該函數(shù)的用法。11.5用數(shù)據(jù)庫(kù)函數(shù)進(jìn)行查找統(tǒng)計(jì)1、概述在Excel中，數(shù)據(jù)庫(kù)是指每列數(shù)據(jù)都有標(biāo)題的數(shù)據(jù)表。Excel提供大約12個(gè)專用數(shù)據(jù)庫(kù)函數(shù)來(lái)簡(jiǎn)化這種數(shù)據(jù)表的數(shù)據(jù)統(tǒng)計(jì)和數(shù)據(jù)查找工作，這些函數(shù)都以D開(kāi)頭，所以也稱為D函數(shù)。D函數(shù)有相同的調(diào)用形式，相同參數(shù)表，格式如下。Dname(database,field,criteria)其中的Dname是函數(shù)名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、Dmax、Dmin等。各函數(shù)的功能如其名字所示，Dsum求總和，Daverage求平均數(shù)，Dget查找數(shù)據(jù)，Dcount統(tǒng)計(jì)數(shù)字個(gè)數(shù)，Dcounta統(tǒng)計(jì)文本和數(shù)據(jù)的個(gè)數(shù)，Dmax求最大數(shù)，Dmin求最小數(shù)。11.5用數(shù)據(jù)庫(kù)函數(shù)進(jìn)行查找統(tǒng)計(jì)案例【例11.17】某校某專業(yè)共有224名學(xué)生，某次期末考試的“數(shù)據(jù)庫(kù)系統(tǒng)應(yīng)用”課程的成績(jī)表如圖所示。現(xiàn)在要查找每位學(xué)生的成績(jī)，希望輸入學(xué)號(hào)后，就能夠得到該生的各種詳細(xì)數(shù)據(jù)，如圖的J1:M8區(qū)域所示。此外，還希望對(duì)各班的考試情況進(jìn)行簡(jiǎn)單的統(tǒng)計(jì)分析，能夠隨時(shí)查看各班的考試人數(shù)，最高成績(jī)，高低成績(jī)，及缺考人數(shù)等，如圖的J10:N17區(qū)域所示。11.5用數(shù)據(jù)庫(kù)函數(shù)進(jìn)行查找統(tǒng)計(jì)11.5用數(shù)據(jù)庫(kù)函數(shù)進(jìn)行查找統(tǒng)計(jì)案例解決方法（1）在K13中輸入計(jì)算上機(jī)平均成績(jī)的公式：=DAVERAGE(A4:H227,"上機(jī)成績(jī)",J12:J13)（2）在M13輸入計(jì)算綜合平均成績(jī)的公式：=DAVERAGE(A4:H227,"綜合成績(jī)",J12:J13)（3）在K15輸入計(jì)算缺考人數(shù)的公式：=DCOUNTA(A4:H227,"期末考試成績(jī)",J12:J13)-DCOUNT(A4:H227,"期末考試成績(jī)",J12:J13)（4）在M15輸入計(jì)算最高成績(jī)的公式：=DMAX(A4:H227,"期末考試成績(jī)",J12:J13)（5）在K17輸入計(jì)算最低成績(jī)的公式：=DMIN(A4:H227,"期末考試成績(jī)",J12:J13)（6）在M17輸入計(jì)算考試人數(shù)的公式：=DCOUNT(A4:H227,"淘汰率為4%下的成績(jī)",J12:J13)11.6用數(shù)組公式進(jìn)行查找統(tǒng)計(jì)數(shù)組公式在進(jìn)行數(shù)據(jù)的匯總、查詢方面有著較強(qiáng)的實(shí)用效能，能夠解決實(shí)際工作中的許多問(wèn)題。案例【例11.18】某電腦配件經(jīng)銷商將每天的銷售數(shù)據(jù)記錄在Excel的工作表中，如圖的A1:E18區(qū)域所示。該經(jīng)銷商希望隨時(shí)查看各配件的累計(jì)匯總數(shù)據(jù)，以便為進(jìn)購(gòu)做出調(diào)整。累計(jì)匯總數(shù)據(jù)表如圖中的G1:I10所示，希望在I3中輸入月份的數(shù)字時(shí)，就累計(jì)出從1月到該月各種電腦配件的銷售總數(shù)量和銷售總金額。

11.6用數(shù)組公式進(jìn)行查找統(tǒng)計(jì)將數(shù)據(jù)區(qū)域轉(zhuǎn)換成表:表1當(dāng)然,也可以不用表而用普通數(shù)據(jù)區(qū)域查找統(tǒng)計(jì).但表具有動(dòng)態(tài)擴(kuò)展特性,更能滿足本表的統(tǒng)計(jì)需求.公式中對(duì)表進(jìn)行了結(jié)構(gòu)化引用11.6用數(shù)組公式進(jìn)行查找統(tǒng)計(jì)案例解決方法（1）為了簡(jiǎn)化輸入，可建立I3單元格的“有效性”列表輸入，從列表中選中月份。同時(shí),將A:E的數(shù)據(jù)區(qū)域轉(zhuǎn)換成表:表1（2）在H5中輸入累計(jì)CPU銷售數(shù)量的數(shù)組公式。即公式輸入完后，按Ctrl+shift+Enter=SUM(IF(MONTH(表1[日期])<=$I$3,IF(表1[商品名稱]=G5,表1[銷售數(shù)量])))（3）將H5中的公