|
在Excel中，使用最多的可能就是數(shù)據(jù)的查詢引用，除了簡(jiǎn)單的篩選查詢之外，我們還可以使用Vlookup等函數(shù)公式來(lái)實(shí)現(xiàn)查詢引用。 一、Excel查詢引用：Vlookup函數(shù)法。 功能：在指定的數(shù)據(jù)范圍內(nèi)查詢返回符合要求的指定值。 語(yǔ)法結(jié)構(gòu)：=Vlookup(查詢值，數(shù)據(jù)范圍，返回值所在的列數(shù)，匹配模式)。 其中匹配模式分為“0”和“1”兩種，“0”為精準(zhǔn)查詢，“1”為模糊查詢。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=VLOOKUP(H3,B3:D9,3,0)。 解讀： 公式=VLOOKUP(H3,B3:D9,3,0)中，H3為查詢值，B3:D3為查詢的數(shù)據(jù)范圍，在此范圍中，目的是返回“銷量”，而銷量在此范圍的第3列，所以第三個(gè)參數(shù)為“3”，最后一個(gè)參數(shù)“0”為精準(zhǔn)查詢。 二、Excel查詢引用：Lookup函數(shù)法。 功能：從單列、單行或指定的數(shù)據(jù)區(qū)域中返回符合條件的值。 Lookup函數(shù)有兩種使用形式：向量形式和數(shù)組形式。 1、向量形式。 語(yǔ)法結(jié)構(gòu)：=Lookup(查詢值,查詢值所在的列,返回值所在的列)。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 1、選定數(shù)據(jù)源，以“銷售員”為【主要關(guān)鍵字】進(jìn)行【升序】排序。 2、在目標(biāo)單元格中輸入公式：=LOOKUP(H3,B3:B9,D3:D9)。 解讀： 1、從動(dòng)圖的演示結(jié)果可以看出，沒有對(duì)“銷售員”進(jìn)行排序之前，查詢的結(jié)果并不準(zhǔn)確。原因在于Lookup的“缺點(diǎn)”所致。所以在用Lookup函數(shù)查詢引用數(shù)據(jù)時(shí)，必須先對(duì)查詢值所在列的值進(jìn)行升序排序。 2、公式=LOOKUP(H3,B3:B9,D3:D9)中，H3為查詢值，B3:B9為查詢值所在的列，D3:D9為返回值所在的列。 2、數(shù)組形式。 語(yǔ)法結(jié)構(gòu)：=Lookup(查詢值,查詢值和返回值所在的數(shù)據(jù)范圍)。 條件：查詢值和返回值必須在數(shù)據(jù)范圍的第一列和最后一列，否則無(wú)法得到正確的結(jié)果。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 1、選定數(shù)據(jù)源，以“銷售員”為【主要關(guān)鍵字】進(jìn)行【升序】排序。 2、在目標(biāo)單元格中輸入公式：=LOOKUP(H3,B3:D9)。 解讀： 數(shù)據(jù)范圍B3:D9中，B列為“查詢值”H3所在的列，D列為返回值“銷量”所在的列。所以數(shù)據(jù)范圍以B列開始，以D列結(jié)束。 3、Lookup“改進(jìn)”形式。 從“向量形式”和“數(shù)組形式”的應(yīng)用中已經(jīng)知道，要得到正確的查詢結(jié)果，必須先以“查詢值”為【主要關(guān)鍵字】進(jìn)行【升序】排序，如果不排序，能否實(shí)現(xiàn)查詢呢？答案當(dāng)然是肯定的…… 語(yǔ)法結(jié)構(gòu)：=Lookup(1,0/查詢條件,返回值范圍)。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=LOOKUP(1,0/(B3:B9=H3),D3:D9)。 解讀： 1、公式=LOOKUP(1,0/(B3:B9=H3),D3:D9)中，查詢值H3如果和B3:B9范圍內(nèi)的值相等，則返回。 2、其公式的本質(zhì)為“數(shù)組形式”，查詢值為1，0/(B3:B9=H3)形成了一個(gè)以0和False為值的新數(shù)據(jù)范圍，返回值范圍為D3:D9。 三、Excel查詢引用：Offset+Match組合函數(shù)法。 Offset函數(shù)功能：以指定的參照為引用，通過(guò)給定的偏移量返回新的引用。 語(yǔ)法結(jié)構(gòu)：=Offset(參照單元格,行,列,[高],[寬度])。 Match函數(shù)功能：返回定位值在指定范圍中的相對(duì)位置。 語(yǔ)法結(jié)構(gòu)：=Match(定位值,定位范圍,匹配模式)。其中匹配模式有：-1（大于）、0（精準(zhǔn)）、1（小于）三種。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=OFFSET(D2,MATCH(H3,B3:B9,0),0)。 解讀： 公式中，以D2為參照單元格，用Match定位H3值在B3:B9這個(gè)范圍中的相對(duì)位置，返回值作為Offset函數(shù)的第2個(gè)參數(shù)，最后用Offset提取符合條件的值。 四、Excel查詢引用：Index+Match組合函數(shù)法。 Index函數(shù)功能：返回給定的單元格區(qū)域中，行列交叉處的值或引用。 語(yǔ)法：=Index(數(shù)據(jù)范圍,行,[列])，當(dāng)省略“列”時(shí)，默認(rèn)為0。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=INDEX(D3:D9,MATCH(H3,B3:B9,0))。 解讀： 公式=INDEX(D3:D9,MATCH(H3,B3:B9,0))中，首先用Match函數(shù)定位H3在B3:B9范圍中的相對(duì)位置，作為Index函數(shù)的第2個(gè)參數(shù)，然后從D3:D9范圍中返回相應(yīng)位置的值。 五、Exce查詢引用：Indirect+Match組合函數(shù)法。 Indirect函數(shù)功能：返回文本字符串所指定的引用。 語(yǔ)法結(jié)構(gòu)：=Indirect(單元格引用,[引用樣式])。引用樣式分為：A1和R1C1樣式。默認(rèn)為A1樣式。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=INDIRECT('d'&MATCH(H3,B3:B9,0)+2)。 解讀： 返回的銷量在D3:D9單元格區(qū)域中，公式=INDIRECT('d'&MATCH(H3,B3:B9,0)+2)中，首先用Match函數(shù)定位H3在B3:B9范圍中的相對(duì)位置，以“王東”為例，則返回1，然后繼續(xù)+2，暨用Indirect函數(shù)返回D3單元格的值。 六、Excel查詢引用：Indirect+Address+Match組合函數(shù)法。 Address函數(shù)功能：返回指定行、列交叉單元格絕對(duì)地址。 語(yǔ)法結(jié)構(gòu)：=Address(行,列)。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=INDIRECT(ADDRESS(MATCH(H3,B3:B9,0)+2,4))。 解讀： 以“王東”為例，首先用Match函數(shù)定位其在B3:B9中的相對(duì)位置，返回值1，1+2,4作為Address函數(shù)的參數(shù)，暨返回$C$4作為Indirect的參數(shù)，最后返回值“66”。 七、Excel查詢引用：Dget函數(shù)法。 功能：從數(shù)據(jù)庫(kù)中返回符合條件且唯一存在的值。 語(yǔ)法結(jié)構(gòu)：=Dget(范圍數(shù)據(jù)庫(kù),返回值列數(shù),條件數(shù)據(jù)庫(kù))。 目的：根據(jù)“銷售員”的姓名查詢對(duì)應(yīng)的“銷量”。  方法： 在目標(biāo)單元格中輸入公式：=DGET(B2:D9,3,H2:H3)。 解讀： Dget函數(shù)的第一個(gè)和第三個(gè)參數(shù)已經(jīng)很明確的說(shuō)了，是*數(shù)據(jù)庫(kù)，簡(jiǎn)單的理解就是包含“標(biāo)題”的數(shù)據(jù)范圍。所以第一、第三個(gè)參數(shù)從B2:D9、H2:H3，而不是從B3:D9或直接的H3。 結(jié)束語(yǔ)： 從上述的示例中已經(jīng)知道，Excel數(shù)據(jù)查詢引用絕不是Lookup或Vlookup的專利，除了這兩個(gè)函數(shù)外，還有很多的函數(shù)或公式都可以實(shí)現(xiàn)……對(duì)于使用技巧，你Get到了嗎？如果在學(xué)習(xí)過(guò)程中有疑問(wèn)或?qū)xcel數(shù)據(jù)查詢引用有獨(dú)到的見解，歡迎親在留言區(qū)留言討論哦！ |
|
|
