(3.8)-8.動(dòng)態(tài)報(bào)表與數(shù)據(jù)查找

第11章動(dòng)態(tài)報(bào)表與數(shù)據(jù)查找本章學(xué)習(xí)目標(biāo)1、

了解動(dòng)態(tài)報(bào)表、表及其結(jié)構(gòu)化引用2、掌握動(dòng)態(tài)報(bào)表的數(shù)據(jù)處理與分析方法3、掌握用查找引用類函數(shù)查詢提取數(shù)據(jù)的方法11.1表與動(dòng)態(tài)報(bào)表1、工作表的缺限日常工作中，類似于這樣的“日志”工作表很常見，每天都向此工作表添加數(shù)據(jù)，由于數(shù)據(jù)行的不確定性，為統(tǒng)計(jì)工作帶來(lái)了一定的困難！Excel2010的表格可以解決這一難題！11.1.1表格1、表格的概念表格(Table)也稱表（本章后面統(tǒng)稱表），是一系列包含相關(guān)數(shù)據(jù)的行和列，這些行和列與工作表中其他行和列中的數(shù)據(jù)分開管理。表具有動(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表3、表的結(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ū)域是指除開表標(biāo)題和匯總行之外的區(qū)域，是表存放數(shù)據(jù)的單元格區(qū)域。匯總行：匯總行位于表的最下方，在最后一個(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ū)域。11.1.1表4、表的功能排序和篩選：Excel會(huì)自動(dòng)將篩選器下拉列表添加在表的標(biāo)題行中，通過(guò)它可以實(shí)現(xiàn)工作表數(shù)據(jù)篩選。格式化：通過(guò)表樣式可以快速對(duì)數(shù)據(jù)表進(jìn)行格式化。顯示和計(jì)算表數(shù)據(jù)總計(jì)：可以快速地對(duì)表中的數(shù)據(jù)進(jìn)行匯總，方法為：在表的末尾顯示一個(gè)總計(jì)行，然后使用在每個(gè)總計(jì)行單元格的下拉列表中提供的函數(shù)。使用計(jì)算列：要使用一個(gè)適用于表中每一行的公式，可以創(chuàng)建計(jì)算列。計(jì)算列會(huì)自動(dòng)擴(kuò)展以包含其他行，從而使公式可以立即擴(kuò)展到這些行。動(dòng)態(tài)擴(kuò)展：表中的數(shù)據(jù)區(qū)域具有動(dòng)態(tài)特性，當(dāng)在表下邊相鄰的空行或表右邊相鄰的空列中輸入數(shù)據(jù)時(shí)，Excel就會(huì)自動(dòng)對(duì)表進(jìn)行擴(kuò)展。刪除重復(fù)數(shù)據(jù)：快速地刪除重復(fù)數(shù)據(jù)行。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í)重寫公式的需要。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é)構(gòu)化引用標(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é)構(gòu)化引用標(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é)構(gòu)化引用案例【例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.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、單擊“開始”選項(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、單擊“開始”選項(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ù)，將查詢結(jié)果用于公式計(jì)算時(shí)，這類函數(shù)很有用。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ù)查詢。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位開始提取1位數(shù)字；將公式復(fù)制到E4時(shí)，將變成“=MID($C4,COLUMN(B1),1)”，因COLUMN(B1)結(jié)果為2，公式將提取C4中的第2位數(shù)字……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)其中ref_text為對(duì)單元格的引用，它可以是單元格的名稱、引用或字符串。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$6在實(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(A8:D12,4,5)，A8下4行右5列與A8:D12大小相同的區(qū)域，結(jié)果為

F12:I16OFFSET(A1,,)等效于OFFSET(A1,0,0)，結(jié)果為A111.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列所示?，F(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ù)在工作表的一行（或一列）中進(jìn)行數(shù)據(jù)查找，并返回?cái)?shù)據(jù)在行（或列）中的位置。在多數(shù)情況下，Match函數(shù)的結(jié)果并不是所需要的最終答案，而是作為Vlookup的第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.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同行或同列的單元格中的值。11.4.6用Lookup函數(shù)進(jìn)行表查找注意：①r1中的內(nèi)容必須按升序排序，查找的字符文本不區(qū)分大小寫。否則，Lookup函數(shù)不能返回正確的結(jié)果。②如果Lookup函數(shù)找不到x，則查找r1中小于或等于x的最大數(shù)值。如果x小于r1中的最小值，Lookup函數(shù)返回錯(cuò)誤值“#N/A”。11.4.6用Lookup函數(shù)進(jìn)行表查找1、用lookup函數(shù)在普通工作表中查找數(shù)據(jù)【例8.6】某蔬菜供應(yīng)商在一個(gè)工作表中保存蔬菜的單價(jià)和出產(chǎn)地，如圖（a）所示。在另一工作表中保存銷售記錄，如圖（b）所示。在圖（b）中，蔬菜名和數(shù)量是實(shí)際輸入的數(shù)據(jù)，產(chǎn)地和單價(jià)需要根據(jù)產(chǎn)品名從圖（a）所示的蔬菜單價(jià)表中查詢輸入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)行表查找2、在lookup函數(shù)通過(guò)對(duì)表的結(jié)構(gòu)化引用查找數(shù)據(jù)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.用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.9數(shù)據(jù)提取與表格結(jié)構(gòu)調(diào)整1、數(shù)據(jù)提取和表格結(jié)構(gòu)調(diào)整的應(yīng)用現(xiàn)狀數(shù)據(jù)提取和表格結(jié)構(gòu)調(diào)整是日常辦公中的常事，常與表格數(shù)據(jù)打交道的辦公人員：辦公室主任、賬務(wù)處理人員、薪酬管理者、大學(xué)輔導(dǎo)員……，但有不少人不得其法，常用復(fù)制、粘貼等方法取數(shù)制表，低效費(fèi)時(shí)。例如，對(duì)表格進(jìn)行列轉(zhuǎn)換，將一列數(shù)據(jù)分離為多列數(shù)據(jù)，間隔一定數(shù)據(jù)行提取或匯總數(shù)據(jù)，在間隔固定行數(shù)的數(shù)據(jù)區(qū)域之間插入空行以生成打印報(bào)表，如此等等。2、數(shù)據(jù)提取和表格結(jié)構(gòu)調(diào)整的方法應(yīng)用查找引用類函數(shù)，并結(jié)合row和column等行列計(jì)算函數(shù)生成的自然數(shù)，往往可以靈活地解決這類問(wèn)題中的大多數(shù)，減少數(shù)據(jù)重新輸入或重制報(bào)表的麻煩。11.9.2提取間隔數(shù)據(jù)行問(wèn)題【例11.23】某專業(yè)期末考試成績(jī)表如圖11.38的A1:H225區(qū)域所示。放暑假了，為了讓每位同學(xué)知道自己的各科成績(jī)，輔導(dǎo)員要給每位同學(xué)寄成績(jī)單，成績(jī)單如圖中J:Q列所示。11.9.2提取間隔數(shù)據(jù)行問(wèn)題問(wèn)題分析對(duì)比表格結(jié)構(gòu)可知：每位同學(xué)的成績(jī)單包括3行數(shù)據(jù)：第1行固定為原表的A2:H2標(biāo)題行，第2行為該同學(xué)在原表中的實(shí)際數(shù)據(jù)行，第3行為空白。即，原表中的每行數(shù)據(jù)都變成新表中的3行數(shù)據(jù)。解題思路由于數(shù)據(jù)都是從原表中提取出來(lái)的，可以根據(jù)提取數(shù)據(jù)的特征，以原表起始單元格A2為參考點(diǎn)，用求余函數(shù)mod和行列號(hào)計(jì)算函數(shù)row和colums計(jì)算單元格距離參考點(diǎn)單元格的行列偏移量，再用OFFSET函數(shù)提取指定偏移位置的單元格數(shù)據(jù)，即可完成新表數(shù)據(jù)的提取。11.9.2提取間隔數(shù)據(jù)行問(wèn)題解題方法在J2單元格輸入下面的公式：=OFFSET($A$2,CHOOSE(MOD(ROW(A1)-1,3)+1,0,(ROW(A2)-1)/3+1,10000),COLUMN(A2)-1)&""向右向下復(fù)制即可生成J:Q區(qū)域的學(xué)生成績(jī)單。11.9.2提取間隔數(shù)據(jù)行問(wèn)題原理分析1．提取A2:H2中的標(biāo)題行=OFFSET($A$2,CHOOSE(MOD(ROW(A1)-1,3)+1,0,(ROW(A2)-1)/3+1,10000),COLUMN(A2)-1)&""OFFSET函數(shù)以A2提取數(shù)據(jù)的起始參照點(diǎn)，其中ROW和COLUMN函數(shù)中的A1、A2沒(méi)有任何特別意義，它作為這兩個(gè)函數(shù)的參數(shù)，是為了生成需要的自然數(shù)。求出公式中有有關(guān)函數(shù)的結(jié)果后，J2中的公式為，=OFFSET($A$2,CHOOSE(1,0,2,10000),0)&""”計(jì)算出CHOOSE的結(jié)果后為“=OFFSET($A$2,0,0)&""””，則結(jié)果為A2單元格。11.9.2提取間隔數(shù)據(jù)行問(wèn)題將J2的公式向右復(fù)制到K2時(shí)，因相對(duì)引用變化，K2中的公式為：=OFFSET($A$2,CHOOSE(MOD(ROW(B1)-1,3)+1,0,(ROW(B2)-1)/3+1,10000),COLUMN(B2)-1)&""計(jì)算在其中row，column和mod函數(shù)的值后，公式為，=OFFSET($A$2,CHOOSE(1,0,1,10000),1)&""計(jì)算出CHOOSE的結(jié)果后為“=OFFSET($A$2,0,1)&""””，則結(jié)果為B2單元格。由上分析可知，向右復(fù)制J2中的公式時(shí)，OFFSET偏離A2單元格的行號(hào)不變，列號(hào)由COLUMN函數(shù)的計(jì)算而相對(duì)遞增，將依次提取B2、C2、D2……11.9.2提取間隔數(shù)據(jù)行問(wèn)題2．提取成績(jī)行將J2的公式向下復(fù)制到J3時(shí)，因相對(duì)引用變化，J3中的公式為：=OFFSET($A$2,CHOOSE(MOD(ROW(A2)-1,3)+1,0,(ROW(A3)-1)/3+1,10000),COLUMN(A3)-1)&""計(jì)算出其中Row，Column函數(shù)的值后，公式變?yōu)椋?OFFSET($A$2,CHOOSE(2,0,1,10000),0)&""計(jì)算出CHOOSE的值后，公式即為“=OFFSET($A$2,1,0)&""”，結(jié)果為A3單元格。由“提取標(biāo)題行”的分析可知：向右復(fù)制J3中的公式，將依次提取B3、D3……，即第一個(gè)學(xué)生的成績(jī)單。11.9.2提取間隔數(shù)據(jù)行問(wèn)題3．提取空白行將J3中的公式復(fù)制到J4時(shí)，因相對(duì)應(yīng)用的變化，J4中的公式為：=OFFSET($A$2,CHOOSE(MOD(ROW(A3)-1,3)+1,0,(ROW(A4)-1)/3+1,10000),COLUMN(A4)-1)&""計(jì)算出其中MOD，ROW和COLUMN函數(shù)的值后，公式如下：=OFFSET($A$2,CHOOSE(3,0,2,10000),0)&""Choose執(zhí)行后，公式變成“=OFFSET($A$2,10000,0)&""”，結(jié)果為偏離A2單元格10000行列0的單元格，即A10002，那里的單元格沒(méi)有任何內(nèi)容。由此可知，最被輸入在J2公式中的10000實(shí)際上可以改寫為其它數(shù)字，只要它對(duì)應(yīng)工作表中不會(huì)有數(shù)據(jù)的空白行就行了。在實(shí)際應(yīng)用中，常常用工作表最后的數(shù)據(jù)行編號(hào)來(lái)表示。因?yàn)閷?shí)際工作表基本上不可能將數(shù)據(jù)保存到最后數(shù)據(jù)行。11.9.2提取間隔數(shù)據(jù)行問(wèn)題4．MOD函數(shù)確定數(shù)據(jù)轉(zhuǎn)換的行數(shù)將J4中的公式復(fù)制到J5后，因相對(duì)引用變化，公式將變成：=OFFSET($A$2,CHOOSE(MOD(ROW(A4)-1,3)+1,0,(ROW(A5)-1)/3+1,10000),COLUMN(A5)-1)&""求出公式中有有關(guān)函數(shù)的結(jié)果后，J5中的公式等效為，=OFFSET($A$2,CHOOSE(1,0,2,10000),0)&""可以發(fā)現(xiàn)這個(gè)公式與J2中的公式相同。原因是只要Row函數(shù)計(jì)算出的行號(hào)減1為3的倍數(shù)，就表示一個(gè)學(xué)生的成績(jī)單生成了，Mod函數(shù)將OFFSET中的行偏移量設(shè)置為0。由此可知，每個(gè)學(xué)生的成績(jī)單如果需要4行，則Mod應(yīng)模除4，有5行則模除5……11.9.2提取間隔數(shù)據(jù)行問(wèn)題5、提取間隔數(shù)據(jù)行的靈活應(yīng)用上例介紹了從數(shù)據(jù)表中提取數(shù)據(jù)，將原數(shù)據(jù)表拆分成許多小數(shù)據(jù)表的方法。反過(guò)來(lái)看，也有許多時(shí)候需要從原數(shù)據(jù)表中提取被分離的數(shù)據(jù)，將數(shù)據(jù)重新集中在一起?！纠?1.24】有學(xué)生成績(jī)單如圖11.39中A1:F23所示，現(xiàn)需要將其中的數(shù)據(jù)整理成一份完整的學(xué)生成績(jī)表，以便進(jìn)行各科成績(jī)的統(tǒng)計(jì)和分析，如下圖中I1:N8區(qū)域所示。合并數(shù)據(jù)從G列的行編號(hào)可以知道，要提取的數(shù)據(jù)行為3、7、11，即間隔4行提取。如果以A1作為OFFSET函數(shù)的參照點(diǎn)提取數(shù)據(jù)，可以用ROW（A1）*4-2作為OFFSET偏離A1的行數(shù)，再用Column計(jì)算偏離的列數(shù)，可以方便地實(shí)現(xiàn)數(shù)據(jù)的提取操作。在I3中輸入下面的公式：=OFFSET($A$1,ROW(A1)*4-2,COLUMN(A$1)-1,1,1)將此公式向右、向下復(fù)制，即可提取各同學(xué)的資料和成績(jī)數(shù)據(jù)，如圖中I3:N8區(qū)域所示。11.9.3數(shù)據(jù)表行列轉(zhuǎn)換問(wèn)題1、行列轉(zhuǎn)換的應(yīng)用情況和手段在不同應(yīng)用場(chǎng)合，以及不同應(yīng)用軟件之間傳遞數(shù)據(jù)時(shí)，常常需要進(jìn)行表格的格式轉(zhuǎn)換，表格的行列變換就是其中的常見問(wèn)題之一。2、進(jìn)行表格行列轉(zhuǎn)換的手段用Index、Offset、Match等查找引用類函數(shù)，結(jié)合Row，Column等行列計(jì)數(shù)函數(shù)，可以方便實(shí)現(xiàn)表格的行列轉(zhuǎn)換。3、將表格行轉(zhuǎn)換為列3、將表格行轉(zhuǎn)換為列【例11.25】某班某次考試的成績(jī)?nèi)缦聢D11.40的A1:F7區(qū)域所示，現(xiàn)需要將此表中的數(shù)據(jù)轉(zhuǎn)換成I:L列區(qū)域中的格式，以便將其導(dǎo)入學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中。每位同學(xué)有4科成績(jī)，則轉(zhuǎn)換之后每個(gè)同學(xué)的數(shù)據(jù)將從原表的1行轉(zhuǎn)換成4行。其中姓名和學(xué)號(hào)由原表的1行重復(fù)為4行，用Index或Offset函數(shù)都容易實(shí)現(xiàn)這樣的轉(zhuǎn)換，本例采用Index函數(shù)完成它們的轉(zhuǎn)換。