LOOKUP函數(shù)在多條件查詢中的應(yīng)用

于佳含

[摘要]在多條件查詢方面，LOOKUP函數(shù)具有強(qiáng)大功能，本文通過(guò)對(duì)LOOKUP函數(shù)模糊查詢及跳過(guò)錯(cuò)誤值屬性的分析，運(yùn)用TRUE和FALSE在EXCEL中的運(yùn)算規(guī)則，對(duì)多條件查詢中出現(xiàn)的各種問(wèn)題提出相應(yīng)解決思路及一些建議。

[關(guān)鍵詞]EXCEL LOOKUP函數(shù) 多條件 查詢

[中圖分類(lèi)號(hào)]TP3 [文獻(xiàn)標(biāo)識(shí)碼]A [文章編號(hào)]1009-5349（2016）18-0162-02

LOOKUP函數(shù)、VLOOKUP函數(shù)、HLOOKUP函數(shù)屬于同系列查詢函數(shù)，是EXCEL軟件中的重要查詢工具，其中，VLOOKUP函數(shù)最為常用，稱其為查詢函數(shù)的半壁江山也不為過(guò)。但是VLOOKUP函數(shù)在使用上存在一定局限性，例如只能滿足一個(gè)查詢條件，且要求“l(fā)ookup_value”必須對(duì)應(yīng)“table_array”的第一列，另外，第一個(gè)參數(shù)“l(fā)ookup_value”必須是唯一值，如果“l(fā)ookup_value”中出現(xiàn)重復(fù)值，其查詢結(jié)果就會(huì)大打折扣。即對(duì)查詢基礎(chǔ)數(shù)據(jù)提出了唯一值和結(jié)構(gòu)順序兩個(gè)條件，對(duì)于不符合VLOOKUP函數(shù)要求的數(shù)據(jù)來(lái)說(shuō)，使用前必須做好前期工作，比較麻煩。而利用LOOKUP函數(shù)進(jìn)行查詢，可以同時(shí)滿足多個(gè)條件，通過(guò)對(duì)不同條件的篩選避免重復(fù)利用同一數(shù)值，對(duì)數(shù)據(jù)列順序也沒(méi)有特殊要求，對(duì)于重復(fù)值多且不能輕易更改數(shù)據(jù)列順序的數(shù)據(jù)來(lái)說(shuō)，LOOKUP函數(shù)的操作更為方便，查詢結(jié)果更加準(zhǔn)確。

一、LOOKUP函數(shù)

LOOKUP函數(shù)是EXCEL中的常用查找函數(shù)，有向量和數(shù)組兩種語(yǔ)法形式。向量形式是在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值，公式為“=LOOKUP（lookup_value，lookup_vector，result_vector）”；數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值，然后返回?cái)?shù)組的最后一行或最后一列中相同位置的數(shù)值，公式為“=LOOKUP（lookup_value，array）”。在這兩種語(yǔ)法形式中，向量形式在多條件查詢中的應(yīng)用最為廣泛。

以表1中左側(cè)表為數(shù)據(jù)源表，查詢滿足右側(cè)表所列條件人員的出生日期，需要同時(shí)滿足對(duì)應(yīng)學(xué)院、姓名兩個(gè)條件，這就應(yīng)用到了LOOKUP函數(shù)的多條件查詢功能。

二、TRUE和FALSE在EXCEL中的運(yùn)算規(guī)則

以表1中L6單元格為例，查詢L6的值需要滿足的第一個(gè)條件是部門(mén)為“政法學(xué)院”，即需要找到B列中值為“政法學(xué)院”的單元格所在行。為使查詢結(jié)果一目了然，可在F列設(shè)置輔助列，因J6值在拖拽中不可發(fā)生變化，所以需要對(duì)J6進(jìn)行絕對(duì)引用，則F2公式為“=B2：B16=$J$6”，表示B2至B16區(qū)間是否等于J6（政法學(xué)院）值的顯示結(jié)果。向下拖拽后，顯示“TRUE”則為相同值，顯示“FALSE”則為不同值，如表2。

同理，查詢L6的值需要滿足的第二個(gè)條件是姓名為“王云”，即需要找到C列中值為“王云”的單元格所在行。同樣設(shè)置輔助列G列，絕對(duì)引用K6單元格，則G2公式為“=C2：C16=$K$6”，表示C2至C16區(qū)間是否等于K6（王云）值的顯示結(jié)果。如表2。

在EXCEL中，TRUE被當(dāng)做“1”來(lái)運(yùn)算，F(xiàn)ALSE被當(dāng)做“0”來(lái)運(yùn)算，因此，根據(jù)以上公式顯示結(jié)果，F(xiàn)列可表示為“0，0，0，0，0，1，1，1，1，1，0，0，0，0，0”；F列可表示為“0，0，0，0，0，0，0，0，1，0，0，0，0，0，0”。F列中“1”為滿足部門(mén)為“政法學(xué)院”的第一個(gè)條件，G列中的“1”為滿足姓名為“王云”的第二個(gè)條件，想要同時(shí)滿足以上兩個(gè)條件，必須使F列和G列同一行上的結(jié)果皆為“1”。根據(jù)“1*1=1，1*0=0，0*0=0”的運(yùn)算規(guī)則，可以將F列單元格與G列對(duì)應(yīng)單元格相乘，設(shè)H列為輔助列，則H2公式為“=F2*G2”，向下拖拽后得到結(jié)果為“0，0，0，0，0，0，0，0，1，0，0，0，0，0，0”，如表2，顯示為1的單元格所在行即為查詢結(jié)果所在行。根據(jù)計(jì)算結(jié)果，只有H10單元格顯示為“1”，其余全部顯示為“0”，由此可見(jiàn)，H10單元格所在行就是查詢結(jié)果所在行，即D10為查詢結(jié)果，也就是L6單元格所求的值。

通過(guò)以上分析，根據(jù)向量公式“=LOOKUP（lookup_value，lookup_vector，result_vector）”，“l(fā)ookup_value”可以設(shè)為“1”，“l(fā)ookup_vector”需滿足學(xué)院為“政法學(xué)院”且姓名為“王云”兩個(gè)條件，可設(shè)為“（$B$2：$B$16=J6）*（$C$2：$C$16=K6）”，“result_vector”為D2至D16區(qū)間，對(duì)相應(yīng)區(qū)間進(jìn)行絕對(duì)引用后，即L6的公式可以寫(xiě)為“=LOOKUP（1，（$B$2：$B$16=J6）*（$C$2：$C$16=K6），$D$2：$D$16）”。

三、模糊查詢

將以上公式寫(xiě)入L6單元格后，通過(guò)與數(shù)據(jù)源表核對(duì)，不難發(fā)現(xiàn)這個(gè)結(jié)果是錯(cuò)誤的。數(shù)據(jù)源表中政法學(xué)院王云的生日是1982年4月15日，而運(yùn)用上文所寫(xiě)公式查詢到的結(jié)果卻是1982年9月16日，之所以出現(xiàn)這樣的差別，其原因就在于LOOKUP函數(shù)的屬性——模糊查詢。

與LOOKUP函數(shù)系列中的VLOOKUP和HLOOKUP不同，LOOKUP函數(shù)沒(méi)有設(shè)定精確查詢或模糊查詢的參數(shù)“range_lookup”，所以全部默認(rèn)模糊查詢。如果LOOKUP函數(shù)找不到“l(fā)ookup_value”，則查找“l(fā)ookup_vector”中小于或等于“l(fā)ookup_value”的最大數(shù)值。因此，想要得到精確的查詢結(jié)果，需要采取一些特殊的方法。

LOOKUP函數(shù)還具備另外一個(gè)比較重要的屬性——跳過(guò)錯(cuò)誤值。雖然模糊查詢不能夠精準(zhǔn)地定位正確值，但如果將不符合條件的值全部變成錯(cuò)誤值，則LOOKUP函數(shù)就會(huì)跳過(guò)這些錯(cuò)誤值，別無(wú)選擇地直接提取正確值。根據(jù)這個(gè)思路，屏蔽錯(cuò)誤值就成為了利用LOOKUP函數(shù)進(jìn)行多條件精確查詢最為重要的一個(gè)環(huán)節(jié)。

四、屏蔽錯(cuò)誤值

以L6為例，根據(jù)上文，在“l(fā)ookup_vector”公式“（$B$2：$B$16=J6）*（$C$2：$C$16=K6）”中，TRUE為“1”，F(xiàn)ALSE為“0”，只有TRUE與TRUE相乘其結(jié)果才能為“1”，只要有一個(gè)FALSE存在，其相乘的結(jié)果即為“0”。在數(shù)學(xué)運(yùn)算中，0可以做被除數(shù)，但卻不可以做除數(shù)，即“0/1=0”，但“0/0”卻是錯(cuò)誤的寫(xiě)法。在EXCEL中，輸入“=0/0”返回的也是錯(cuò)誤值，顯示為“#DIV/0！”。因此，根據(jù)這個(gè)規(guī)則，可以把表示“l(fā)ookup_vector”部分的公式整體作為除數(shù)，如果公式結(jié)果返回“1”，則除數(shù)成立，其所在行即為正確值所在行，如果公式結(jié)果返回“0”，則除數(shù)不成立，最終將返回錯(cuò)誤值“#DIV/0！”，進(jìn)而被LOOKUP函數(shù)屏蔽掉。由此，L6單元格中“l(fā)ookup_vector”部分的公式可以寫(xiě)為“0/（（$B$2：$B$16=J6）*（$C$2：$C$16=K6））”。

五、多條件查詢

通過(guò)上文對(duì)TRUE、FALSE、模糊查詢及屏蔽錯(cuò)誤值的分析，可以分析出L6單元格的最終公式為“=LOOKUP（1，0/（（$B$2：$B$16=J6）*（$C$2：$C$16=K6）），$D$2：$D$16）”。得出結(jié)果為1982年4月15日，與數(shù)據(jù)源表相符，即唯一正確值。值得一提的是，在基礎(chǔ)數(shù)據(jù)中，很容易出現(xiàn)多個(gè)重復(fù)值，同一學(xué)院甚至也有同名同姓的情況存在。為保證查詢結(jié)果的準(zhǔn)確性，需要對(duì)多個(gè)條件同時(shí)進(jìn)行篩選，只有同時(shí)滿足多個(gè)條件，才能作為查詢結(jié)果列出。以表2為例，如果政法學(xué)院有兩個(gè)王云，其中一位職稱為助教，另一位職稱為講師，則利用職稱不同這個(gè)已知條件就可以將兩人區(qū)分開(kāi)來(lái)。查找王云助教，公式可按照“=LOOKUP（1，0/（（區(qū)域1=政法學(xué)院）*（區(qū)域2=王云）*（區(qū)域3=助教）），目標(biāo)查詢區(qū)域）”列出，查找王云講師，公式可按照“=LOOKUP（1，0/（（區(qū)域1=政法學(xué)院）*（區(qū)域2=王云）*（區(qū)域3=講師）），目標(biāo)查詢區(qū)域）”列出。如果還是存在重復(fù)值，則可以此類(lèi)推，依次將已知條件列在公式中，對(duì)重復(fù)值進(jìn)行更為精確的篩選，直至查找到最終結(jié)果。由此，可得出利用LOOKUP函數(shù)進(jìn)行多條件查詢的通用公式為“=LOOKUP（1，0/（（區(qū)域1=條件1）*（區(qū)域2=條件2） *（區(qū)域3=條件3）*（……）），目標(biāo)查詢區(qū)域）”，其中，“區(qū)域1/2/3……”和“目標(biāo)查詢區(qū)域”均需要絕對(duì)引用。

綜上所述，利用LOOKUP函數(shù)進(jìn)行多條件查詢，不僅需要考慮到函數(shù)本身為模糊查詢的屬性，還要將混淆正確值的其他值一一進(jìn)行錯(cuò)誤值處理，只要解決以上兩點(diǎn)，LOOKUP函數(shù)就可以發(fā)揮其強(qiáng)大的功能，使查詢工作達(dá)到事半功倍的效果。值得注意的是，在使用LOOKUP函數(shù)時(shí)，需要對(duì)條件區(qū)域和目標(biāo)查詢區(qū)域進(jìn)行絕對(duì)引用，避免取值區(qū)域混亂。正確運(yùn)用LOOKUP函數(shù)進(jìn)行多條件查詢，不僅可以加強(qiáng)查詢準(zhǔn)確率，對(duì)提高工作效率也有很大的幫助。

【參考文獻(xiàn)】

[1]Excelhome.實(shí)戰(zhàn)技巧精粹：Excel2010 函數(shù)與公式[M].北京：人民郵電出版社，2014.

[2]Excelhome.Excel2010實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2013.

[3]周賀來(lái).Excel數(shù)據(jù)處理[M].北京：中國(guó)水利水電出版社，2011.

[4]吳愛(ài)妤.Excel2007高效辦公800招[M].北京：機(jī)械工業(yè)出版社，2009.1.

[5]王國(guó)勝，李春曉編.Excel2010公式與函數(shù)辭典606秘技大全[M].北京：中國(guó)青年出版社，2012.

[6]伍昊.你早該這么玩excel[M].北京：北京大學(xué)出版社，2011.

責(zé)任編輯：楊柳