excel數(shù)據(jù)處理與分析-第11章 動態(tài)報表與數(shù)據(jù)查找

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

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

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

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

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

=MID($C4,COLUMN(A1),1)其中的COLUMN(A1)結果為1，MID將中C1的第1位開始提取1位數(shù)字；將公式復制到D5時，將變成“=MID($C4,COLUMN(B1),1)”，因COLUMN(B1)結果為2，公式將提取C4中的第2位數(shù)字……11.4.1用行、列號函數(shù)定位與提取數(shù)據(jù)【例11.6】用行列號計算函數(shù)row，rows，column，columns以及文本函數(shù)mid，left，right，text進行數(shù)字的按位提取與轉換。如圖11.12所示。(4)在A8中輸入并向下復制下面的公式，生成A8:A13中的數(shù)據(jù)公式：="第"&ROW(A1)&"期"公式利用Row函數(shù)分別生成了數(shù)字1，2，3，4。(5)在N8中輸入公式=LEFT(RIGHT(TEXT($C8*100,"￥000;;"),COLUMNS(N:$N)))將此公式向下復制到N9，再將N8:N9中的公式向左復制到D8:D9即可生成D8:N9區(qū)域中的數(shù)字。11.4.1用行、列號函數(shù)定位與提取數(shù)據(jù)【例11.6】用行列號計算函數(shù)row，rows，column，columns以及文本函數(shù)mid，left，right，text進行數(shù)字的按位提取與轉換。如圖11.12所示。(6)在D10中輸入下面公式，并將此公式向下復制到D11，向右復到到N10:N11。=MID(TEXT($C10*100,REPT("",10-LEN($C10*100))&"￥000"),COLUMNS($A:A),1)D10:N10區(qū)域共11個單元格，LEN($C10*100)計算出數(shù)字本身要占據(jù)其中的單元格個數(shù)，還有11-LEN($C10*100)個單元格沒有數(shù)字填，其中一個單元格填寫“￥”符號，因此還有10-LEN($C10*100)個單元格需要填寫空白字符“REPT("",10-LEN($C10*100))”就重復生成需要填在數(shù)字前面的空白字符?！癟EXT($C10*100,REPT("",10-LEN($C10*100))&"￥000")”創(chuàng)建了恰好夠填滿D10:N10區(qū)域的11個字符，Mid函數(shù)則應用COLUMNS函數(shù)計算出每次提出到相應單元格中的起始位置，提取恰當?shù)淖址⑻顚懙綄獑卧裰小?1.4.1用行、列號函數(shù)定位與提取數(shù)據(jù)【例11.6】(7)在N12中輸入公式：=LEFT(RIGHT(TEXT($C12*100,"[dbnum2]￥000;;"),COLUMNS(N:$N)))將此公式向左復制到D12，即可生成D12:N12區(qū)域中的數(shù)字。本公式與前面的數(shù)字提取公式含義相同，只是在提取前首先用自定義格式“[dbnum2]”將數(shù)字轉換為中文大寫，然后再提取。11.4.1用行、列號函數(shù)定位與提取數(shù)據(jù)【例11.6】(8)在N13中輸入以下公式，再將其向左復制到D13，即可計算出D13:N13的數(shù)據(jù)：=LEFT(RIGHT(TEXT($C13*100,"[dbnum1]￥000;;"),COLUMNS(N:$N)))公式中的“[dbnum1]“將數(shù)字設置為中文小寫11.4.1用行、列號函數(shù)定位與提取數(shù)據(jù)【例11.6】(9)在O1并向下填充復制下面的輸入公式，生成O列的編號“=ROWS($A$1:A1)”本公式利用Rows函數(shù)計算參數(shù)單元格區(qū)域中的行數(shù)，由于區(qū)域中結束單元格位置采用了相對應用，因此將它向下復制時會自動擴展，產(chǎn)生連續(xù)的數(shù)字編號。其特點是：如果刪除表區(qū)域中的數(shù)據(jù)行，編號會自動更新。11.4.2用INDIRECT函數(shù)和名稱查詢其他工作表中的數(shù)據(jù)Indirect概述Indirect函數(shù)返回由文字串指定的引用。該函數(shù)能夠對引用進行計算，并顯示引用的內(nèi)容。當需要更改公式中單元格的引用，而不更改公式本身時，可使用該函數(shù)。格式Indirect(ref_text,A1)其中ref_text為對單元格的引用，它可以是單元格的名稱、引用或字符串。A1為一邏輯值，指明包含在單元格ref_text中的引用的類型。如果A1為TRUE或省略，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ù)，可以構造非常靈活而高效的查詢，還能使問題簡化?！纠?1.7】某單位的職稱工資表如圖11.13（a）所示，職工基本檔案表如圖11.13（b）所示。假設圖11.13（b）中除了E列的職稱工資外，其他數(shù)據(jù)都建立完畢，現(xiàn)在要輸入每位職工的職稱工資。11.4.2用INDIRECT函數(shù)和名稱查詢其他工作表中的數(shù)據(jù)（1）指定圖（a）中A2:B11區(qū)域的最左列為名字。（2）在E2單元格中輸入下述公式=Indirect(D2)向下復制此公式，就能求出所有職工的職稱工資。11.4.3用ADDRESS和OFFSET函數(shù)進行定位查找與數(shù)據(jù)提取1．ADDRESS用指定的行列編號，生成文本形式單元格引用地址，用INDIRECT可將其轉換成可用的引用。用法如下。ADDRESS(r,c,abs_num,a1,sheet_text)r是在單元格引用中使用的行號；c列號；abs_num指明引用類型。1（或省略）——絕對引用；2——絕對行號，相對列號；3——相對行號，絕對列號；4相對引用例：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ù)進行定位查找與數(shù)據(jù)提取應用情況在實際工作中，許多時間要根據(jù)工作表，單元格行、列位置動態(tài)引用單元格，在這種情況下應用Address和行列計算函數(shù)制作這類報表是比較方便的11.4.3用ADDRESS和OFFSET函數(shù)進行定位查找與數(shù)據(jù)提取【例11.8】有銀行存折信息如圖11.14所示，其中A列是存款或取款的日期，B列是存款信息，C列是取款信息，D列是每發(fā)生一筆存取款信息后存折上的余額。P282存折余額的計算公式為：存款余額=本期存款–本期取款+前期余額。D2中的公式為：=SUM(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),-INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT(ADDRESS(ROW()-1,COLUMN())))ADDRESS(ROW(),COLUMN()-2)其中，ROW的結果為2，COLUMN()-2的結果為4-2；則ADDRESS（2，2）結果為文本形式的：$B$2；INDIRECT（$B$2）將它轉換成可用的引用并求取B2中的值11.4.3用ADDRESS和OFFSET函數(shù)進行定位查找與數(shù)據(jù)提取2．OFFSETOFFSET以指定的引用位置為參照點，再根據(jù)指定的偏移量計算出新的引用位置。函數(shù)的結果是單元格引用或區(qū)域。用法：OFFSET(reference,rows,cols,[height],[width])參數(shù)說明Reference是參照點單元格或區(qū)域。Rows和Cols用于確定新引用位置偏離Reference單元格（若Reference為單元格區(qū)域，則指該區(qū)域左上角單元格）的行數(shù)和列數(shù)。Rows為正數(shù)時表示新位置在Reference下方的第Rows行，為負數(shù)時表示新位置在Reference上方的第Rows行Cols為正數(shù)時表示新位置在Reference右邊的第cols列，為負數(shù)時表示在Reference左邊的第cols列。OFFSET(reference,rows,cols,[height],[width])Offset參數(shù)說明Height用于指定所要返回的引用區(qū)域的行數(shù)Width用于指定所要返回的引用區(qū)域的列數(shù)。Height和Width必須為正數(shù)。如果省略height或width，則假設其高度或寬度與reference相同。如果行數(shù)和列數(shù)偏移量超出工作表邊緣，函數(shù)OFFSET返回錯誤值#REF!。注意OFFSET實際上并不移動任何單元格或更改選定區(qū)域，它只是返回一個引用，可用于任何需要將引用作為參數(shù)的函數(shù)。OFFSET(reference,rows,cols,[height],[width])Offset簡單實例SUM(OFFSET(C2,1,2,3,1))

OFFSET(C2,1,2,3,1)確定單元格C2靠下1行右2列的3行1列的區(qū)域，即E3:E5，原公式相當于“=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ū)域，結果為

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

在G3單元格中下向下復制下面公式，模糊查找出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中找到最大的那個小于等于F3的單元格。F3的內(nèi)容為6582，而A3:A10中最大的那個小于6582的數(shù)是5000，它位于A3:A10區(qū)域內(nèi)的第8個位置，所以MATCH($F3,$A$3:$A$10,1)結果是8。因此，原公式就相當于INDEX($A$3:$B$10,8,2)，則該函數(shù)將返回A3:B10區(qū)域內(nèi)的第8行、第2列所對應的單元格引用，即B10單元格。B10的稅率為25%，這正好是工資6825所對應的個人所得稅稅率。11.4.5用match和index函數(shù)構造靈活的查詢5.用INDEX和MATCH進行重復數(shù)據(jù)判斷應用INDEX、MATCH和其它統(tǒng)計函數(shù)，可以查找或標識出工作表中的重復數(shù)據(jù)。例如，在圖11.18中，要判斷D列哪些姓名重復了，重復最多的是哪個姓名？（1）標識重復出現(xiàn)的姓名在H3中輸入公式，將此公式向下復制到H12，標識出重復的姓名。=IF(MATCH(D3,D$3:D$12,0)=ROW(A1),"","重復")”原理Row(A1)=1，如果D3中的姓名在D3:D12區(qū)域中只出現(xiàn)一次，MATCH(D3,D$3:D$12,0)的結果為1，IF函數(shù)的結果為空白串""，在H3中就不會有顯示；如果D3中的姓名在D3:D12中重復了，MATCH函數(shù)將返回該姓名最后一次出現(xiàn)的位置，這個位置一定不等于Row(A1)，IF條件不成立，函數(shù)結果為“重復”11.4.5用match和index函數(shù)構造靈活的查詢5.用INDEX和MATCH進行重復數(shù)據(jù)判斷應用INDEX、MATCH和其它統(tǒng)計函數(shù)，可以查找或標識出工作表中的重復數(shù)據(jù)。例如，在圖11.18中，要判斷D列哪些姓名重復了，重復最多的是哪個姓名？（2）找出重復出現(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)逐個查找D3:D12區(qū)域中每個姓名在D3:D12區(qū)域中出現(xiàn)的位置，重復最多的姓名，MATCH函數(shù)返回它的位置就最多。MODE是取眾數(shù)的函數(shù)，它返回一組數(shù)中出現(xiàn)次數(shù)最多的數(shù)字，它會返回出現(xiàn)重復次數(shù)最多的姓名在D3:D12第一次出現(xiàn)的位置，INDEX再據(jù)此位置查詢得取重復最多的姓名。11.4.6用Lookup函數(shù)進行表查找Lookup函數(shù)Lookup函數(shù)在一個大表中找出特定數(shù)據(jù)，并在其它工作表中引用查找結果，在工作中應用較廣。功能從給定的向量（單行或單列單元格區(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】某蔬菜供應商在一個工作表中保存蔬菜的單價和出產(chǎn)地，如圖（a）所示。在另一工作表中保存銷售記錄，如圖（b）所示。在圖（b）中，蔬菜名和數(shù)量是實際輸入的數(shù)據(jù)，產(chǎn)地和單價需要根據(jù)產(chǎn)品名從圖（a）所示的蔬菜單價表中查詢輸入11.4.6用Lookup函數(shù)進行表查找D3中輸入下述公式：=LOOKUP(A3,蔬菜單價表!$A$2:$A$11,蔬菜單價表!$B$2:$B$11)向下復制此公式，查出蔬菜的單價在B3中輸入下述查找公式：=LOOKUP(A3,蔬菜單價表!$A$2:$A$11,蔬菜單價表!$C$2:$C$11)向下復制此公式，查出蔬菜的產(chǎn)地11.4.6用Lookup函數(shù)進行表查找案例解決方法（1）建立圖（a）所示的蔬菜單價表，并按升序對該工作表進行排序，排序主關鍵字為“蔬菜”。（2）輸入圖（b）的A列數(shù)據(jù)，和第1、2行的標題。在B3中輸入下述查找公式，然后向下填充復制該公式，就可找出各蔬菜的產(chǎn)地。=LOOKUP(A3,蔬菜單價表!$A$2:$A$11,蔬菜單價表!$C$2:$C$11)查找蔬菜單價的方法與此完全類似，只需要在圖（b）的D3單元格中輸入下述公式，然后向下復制該公式就行了。=LOOKUP(A3,蔬菜單價表!$A$2:$A$11,蔬菜單價表!$B$2:$B$11)11.4.6用Lookup函數(shù)進行表查找案例解決方法（1）建立圖（a）所示的蔬菜單價表，并按升序對該工作表進行排序，排序主關鍵字為“蔬菜”。（2）輸入圖（b）的A列數(shù)據(jù)，和第1、2行的標題。在B3中輸入下述查找公式，然后向下填充復制該公式，就可找出各蔬菜的產(chǎn)地。=LOOKUP(A3,蔬菜單價表!$A$2:$A$11,蔬菜單價表!$C$2:$C$11)查找蔬菜單價的方法與此完全類似，只需要在圖（b）的D3單元格中輸入下述公式，然后向下復制該公式就行了。=LOOKUP(A3,蔬菜單價表!$A$2:$A$11,蔬菜單價表!$B$2:$B$11)11.4.6用Lookup函數(shù)進行表查找注意：①r1中的內(nèi)容必須按升序排序，查找的字符文本不區(qū)分大小寫。否則，Lookup函數(shù)不能返回正確的結果。②如果Lookup函數(shù)找不到x，則查找r1中小于或等于x的最大數(shù)值。如果x小于r1中的最小值，Lookup函數(shù)返回錯誤值“#N/A”。11.4.6用Lookup函數(shù)進行表查找2、在lookup函數(shù)通過對表的結構化引用查找數(shù)據(jù)對于例11.13中的數(shù)據(jù)查找問題，較好的方法是將蔬菜單價表保存在表中，然后在LOOKUP函數(shù)中通過表的結構化引用查找蔬菜單價，這樣的優(yōu)點是在表中添加新的蔬菜品種時，LOOKUP函數(shù)不用修改就可以對新增加的蔬菜進行單價查詢，而且意義更清楚11.4.7用Vlookup函數(shù)進行表查找Vlookup函數(shù)功能Vlookup按列查找的方式從指定數(shù)據(jù)表區(qū)域的最左列查找特定數(shù)據(jù)，它能夠返回查找區(qū)域中與找到單元格位于相同行不同列的單元格內(nèi)容。格式Vlookup(x,table,n,f)其中，x是要查找的值；table是一個單元格區(qū)域；n中table區(qū)域中要返回的數(shù)據(jù)所在列的序號。n=1時，返回table第1列中的數(shù)值；n=2時，返回table第2列中的數(shù)值；以此類推。f是一個邏輯值，表示查找的方式。當其為true（或1）時，表示模糊查找；當它為false（或0）時，表示精確查找。11.4.7用Vlookup函數(shù)進行表查找說明：Vlookup函數(shù)在table區(qū)域的第1列中查找值為x的數(shù)值，如果找到，就返回與找到數(shù)據(jù)同行第n列單元格中的數(shù)據(jù)。當f為true時，table的第1列數(shù)據(jù)必須按升序排列，否則找不到正確的結果；當f為false時，table的第1列數(shù)據(jù)不需要排序。注意①如果Vlookup函數(shù)找不到x，且f=true，則返回小于等于x的最大值。②如果x小于table第1列中的最小值，Vlookup函數(shù)返回錯誤值“#N/A”。③如果Vlookup函數(shù)找不到x且f=FALSE，Vlookup函數(shù)返回錯誤值“#N/A”。11.4.7用Vlookup函數(shù)進行表查找1、用Vlookup進行模糊查找模糊查找也就是常說的近似查找，常用于數(shù)據(jù)轉換或數(shù)據(jù)對照表中的數(shù)據(jù)查找。案例【例11.14】假設所得稅的稅率如圖的A1:B10區(qū)域所示。其中的含義是：0~500的稅率為0%，500~1000的稅率為1%，1000~1500的稅率為3%……，4000以上的稅率為20%。某公司的職工收入數(shù)據(jù)如圖的D1:J11所示，現(xiàn)在計算每位職工應繳的所得稅。11.4.7用Vlookup函數(shù)進行表查找I列的所得稅率的計算方法如下。在I3單元格中輸入下述公式，然后向下復制此公式，就能夠計算出每位職工的所得稅率。=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ù)進行表查找2、用Vlookup進行精確查找概述精確查找就是指查找數(shù)據(jù)完全匹配的查找，Vlookup函數(shù)具有此項功能。在大表中查找特定數(shù)據(jù)，或查找不同工作表中的數(shù)據(jù)，特別是工作表數(shù)據(jù)較多，Vlookup函數(shù)顯得非常有效11.4.7用Vlookup函數(shù)進行表查找案例【例11.15】某學校職工收入由基本工資、獎金、課時費等部分組成，這些金額分別保存在同名的基本工資、獎金和課時費表中，如圖11.22(b)(c)(d)所示?，F(xiàn)在要形成圖11.22(a)所示的匯總表，并計算職工的總收入。在四張數(shù)據(jù)表中，只有職工收入表和基本工資表的數(shù)據(jù)包括了全部職工，有些職工沒有獎金，有些職工沒有課時費，所以獎金表和課時費表中只有部分職工信息11.4.2用Vlookup函數(shù)進行表查找案例解決方法（1）用Vlookup精確查找基本工資、獎金和課時費在圖11.22(a)單位列的C2單元格輸入下面的公式，然后向下填充復制此公式，即可查詢出每位職工的單位。=VLOOKUP(A2,基本工資!$A$1:$D$279,4,0)在11.22(a)的E2中輸入查找獎金的公式：=VLOOKUP(A2,獎金!$A$1:$D$279,4,0)在11.22(a)的F2中輸入查找課時費的公式：=VLOOKUP(A2,課時費!$A$1:$D$279,4,0)11.4.2用Vlookup函數(shù)進行表查找案例解決方法2）處理Vlookup錯誤如果沒有找到相應的數(shù)據(jù)，Vlookup函數(shù)將返回#N/A錯誤信息，如果不處理這些錯誤數(shù)據(jù)，還會導致其它計算錯誤。例如，圖11.23中G列通過Sum函數(shù)計算D、E、C三列數(shù)據(jù)的總和，從中可以看出F列的錯誤也導致了G列的#N/A錯誤，無法算出對應職工的總收入。在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查詢有錯誤，if函數(shù)的值為0，否則IF函數(shù)就取Vlookup查詢的結果。即用0代替了Vlookup的錯誤值11.4.2用Vlookup函數(shù)進行表查找（3）Vlookup和Indirect相結合構造通用查詢由于基本工資、獎金和課時費三張數(shù)據(jù)表的結構完全相同（見圖11.22(b)(c)(d)），這些表的第四列數(shù)據(jù)是要查詢提取的數(shù)據(jù)。此外，圖11.22(a)中D1:F1對應列的標題名稱與其數(shù)據(jù)所在的工作表名稱對應相同，可用Indirect函數(shù)將此區(qū)域中的列標題取出構造出要查詢出的工作表名稱和區(qū)域，創(chuàng)建通用的數(shù)據(jù)表查詢公式。因此，圖11.23中基本工資、獎金和課時費的查詢和提取，可用下面的方法實現(xiàn)。在D2中輸入公式，向右復制此公式到F2，向下復制此公式到最后一個職工的數(shù)據(jù)行，即可查詢出所有職工的基本工資、獎金和課時費=VLOOKUP($A2,INDIRECT(D$1&"!$A$1:$D$500"),4,0)公式的巧妙在于用INDIRECT函數(shù)生成了工作表名稱和數(shù)據(jù)區(qū)域：INDIRECT(D$1&"!$A$1:$D$500")”的計算結果為“基本工資!$A$1:$D$500”11.4.2用Vlookup函數(shù)進行表查找（4）Vlookup、Match結合構造動態(tài)列序的查詢許多時間需要從原數(shù)據(jù)表中查詢提取多列數(shù)據(jù)，形成報表或構造綜合信息查詢。如果查詢結果數(shù)據(jù)的次序與原始數(shù)據(jù)表中的順序相同，則可以通過查詢公式的復制快速完成；如果次序不同，則需要對復制后的查詢公式進行修改。在Vlookup函數(shù)中應用Match函數(shù)動態(tài)變通數(shù)據(jù)表行列位置，可以構造滿足這一需求的動態(tài)序列查詢?！纠?1.16】某學校職工收入明細數(shù)據(jù)如圖11.24中A11:G289區(qū)域所示。由于人數(shù)太多，需要按照職工編號查詢該職工的各項收入明細。如圖中A2:G4區(qū)域所示。要求在C2中輸入職工的編號后，就查詢顯示出該職工的姓名、單位、資金、基本工資等數(shù)據(jù)。原始數(shù)表在C2中輸入職工編號，就會從原數(shù)據(jù)表中查詢到該職工的各項數(shù)據(jù)在B8、C8中分別輸入職工姓名、單位，從原數(shù)據(jù)表中查詢到該職工的各項數(shù)據(jù)在C4中輸入下面的公式，將它從C4向右復制到G4，即可查詢出C2中職工編號對應的單位、獎金、基本工資、課時費和收入總計。=VLOOKUP($C2,$B$11:$G$289,MATCH(C3,$B$11:$G$11,0),0)公式含義不難理解：在B11:G289區(qū)域中查詢C2中的職工編號，但找到后的數(shù)據(jù)列由MATCH(C3,$B$11:$G$11,0)決定，C3中為獎金，它位于B11:G11區(qū)域的第4列，所以結果為4，因此VLOOLUP函數(shù)將返回B11:G289區(qū)域中找到職工號同行第4列的數(shù)據(jù)，即該職工的獎金；當將C4中的公式復制到D4后，MATCH中的相對引用會改變，變成MATCH(D3,$B$11:$G$11,0)，其結果為3……Vlookup、Match結合構造動態(tài)列序查詢職工的獎金、基本工資、課時費、收入總計問題：通過觀察可知，查詢報表頭B3:G3和原數(shù)據(jù)表頭B11:G11的次序不同，但是B3:G3區(qū)域中的標題都在源數(shù)據(jù)表中職工編號的右邊，而職工編號是從原數(shù)據(jù)表中查詢數(shù)據(jù)的依據(jù)。像這樣的查詢可以用在Vlookup函數(shù)中用Match定位要查詢的數(shù)據(jù)列，實現(xiàn)動態(tài)查詢。但是姓名列數(shù)據(jù)在原數(shù)據(jù)表中位于查詢依據(jù)列（職工編號）左邊，就不能用這種方式查詢。因為VLOOKUP函數(shù)要求要查詢的數(shù)據(jù)必須位于原表中查詢依據(jù)列的右邊。（5）Vlookup和Choose結合構造向左的列查詢問題：如何根據(jù)職工編號查詢職工姓名呢？VLOOUP函數(shù)只能在查詢數(shù)據(jù)區(qū)域的第1列（最左列）查找指定的數(shù)據(jù)，但在原數(shù)據(jù)A11:G289區(qū)域中，姓名位于職工編號的左邊，不能用Vlookup函數(shù)直接查詢。對于這種情況，可以用Choose函數(shù)通過數(shù)組的方式，對原數(shù)據(jù)表的列進行交換，構造出一個符合Vlookup函數(shù)查詢需求的內(nèi)存數(shù)組，再用Vlookup函數(shù)從此數(shù)組中查詢。

在B4中輸入下面的公式，即可根據(jù)C2中的職工編號查詢出對應的職工姓名。=VLOOKUP(C2,CHOOSE({1,2},B11:B289,A11:A289),2,0)公式將先執(zhí)行Choose函數(shù)，先用數(shù)組{1,2}中的第一個元素1選取了B11:B289數(shù)據(jù)區(qū)域，再用數(shù)組元素2選取A11:A289，構造了一個B列在前，A列在后的內(nèi)存數(shù)組。最后VLOOKUP從該內(nèi)存數(shù)組中查詢（5）Vlookup和Choose結合構造向左的列查詢從根據(jù)職工編號查找姓名的事例可知，通過CHOOSE函數(shù)可以交換原數(shù)據(jù)表的列次序，形成具有新列次序的內(nèi)存二維數(shù)組。由此可得到啟示：通過Choose函數(shù)，可將原表職工編號交換到第一列，并按查詢表數(shù)據(jù)列的先后次序將原始數(shù)據(jù)表交換次序形成內(nèi)存數(shù)組，然后可以通過VLOOKUP函數(shù)一次性動態(tài)查詢出職工的姓名、單位、獎金、基本工資等全部數(shù)據(jù)。在B6中輸入下面的公式，將其向右復到到G6，可查詢得到第6行全部數(shù)據(jù)，請思考：此公式是如何查詢提取數(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ù)構造了數(shù)據(jù)列序如下的內(nèi)存數(shù)組，VLOOKUP函數(shù)在這個內(nèi)存數(shù)組中查詢數(shù)據(jù)，找到職工編號后，只需要依次提取數(shù)據(jù)即可，因此用了COLUMN函數(shù)提取數(shù)據(jù)列B列A列C列E列D列F列G列職工編號姓名單位獎金基本工資課時費收入總計

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

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

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

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

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