VLOOKUP函數(shù)應(yīng)用全解

1、1、關(guān)于VLOOKUP函數(shù)的介紹VLOOKUP函數(shù)目前主要用于數(shù)據(jù)匹配、核對(duì)使用較多，這里結(jié)合函數(shù)在不同情況下的應(yīng)用做詳細(xì)的差例解讀零基礎(chǔ)均可快速上手。|函數(shù)基本結(jié)構(gòu)如下VLOOKUP(lookupue理辭 找什么？,table array, coljndox nunn r range lookup) ® 啕里找？ 什么位置？準(zhǔn)確度要求2、初級(jí)應(yīng)用函數(shù)主要包括四個(gè)參數(shù)，以圖1為案例進(jìn)行公式解讀。(1)需要查找的值以南1地區(qū)為例，需要查找的值為“南 1地區(qū)”，所在位置為F8,查找彳1錄入F&(2)查找值所在區(qū)間所要查找的區(qū)間為 B列至C列(或B3:C33)特別說(shuō)明：1) C列不

2、限制，只要包含了銷(xiāo)售額所在列即可，可以為B:D、B:EB:XF,D只要是C列后續(xù)列，公式均可生效。2) B:C可以在其他的工作表或者工作簿中查找。(3)查找值在該區(qū)間所在列數(shù)需要查找的“南1地區(qū)”在B:C區(qū)間所在列數(shù)，以 B列為第一列，所在列數(shù)為 2,填入2即可。圖ABCDF12同區(qū)同3M (萬(wàn)元)3北酎蜓33984曲恤區(qū)72515而E地區(qū)62076柳地區(qū)T西地日我配8北L地區(qū)32979北3地區(qū)931110北州醫(yī)737S11儂地區(qū)307513南二地運(yùn)292413麻地區(qū)665114口花地區(qū)錘515南1地區(qū)2SD8商刑國(guó)7117北2地底誑1R北口地區(qū)19再說(shuō)E究20北T地區(qū)15 0E1P I CI

3、：(地艮期售海f萬(wàn)元)靶胞區(qū)2fi OS=VLOCZUP(BD E ,C,2, CJ第2地區(qū)E ：|75=VL0CKUF(FS,E:Cr2,由省3冊(cè)區(qū)71-F=VLOCEnP(Fl&,B;C, 2,0)樸1也區(qū)90B6=V10CKDP (Fl l?E:C, 2, 0)第5加區(qū)3SE4KRCEUF 萬(wàn)1工 E：0 2, 0)國(guó)內(nèi)某企已知金國(guó)B罐前情況如左聶所示，現(xiàn)制導(dǎo) 需至匿蹙萱有四】商，南九南南5五個(gè)期區(qū)的射 據(jù)，通過(guò)制作以下表格,快速實(shí)現(xiàn)為星苴技3、拓展應(yīng)用第2點(diǎn)中，我們查找的值是“南 1地區(qū)”、“南1”等字樣，在查找區(qū)間均可以找到包含了一模一樣字樣的單元格，如果查找字樣與查找區(qū)間字

4、樣不一致，就會(huì)返回錯(cuò)誤值，在不修改原表以及工作表格信息的情況下，該如何進(jìn)行公式應(yīng)用？下面分3種情況進(jìn)行案列解讀。(1)查找區(qū)間字樣包含了查找字樣參考圖2,如我們要查找的字樣為 “南1”等，如果按照第2點(diǎn)所列公式，會(huì)返回錯(cuò)誤。 以南1為例，在查找區(qū)間的字樣為“南 1地區(qū)”。我們把查找的值設(shè)置為“ F8&"*" "，即可查 找到我們想要的結(jié)果值。 特別說(shuō)明：1)公式中的雙引號(hào)必須切換為英文輸入法輸入才有效；2) F8&"*"的解釋”*代表的是若干值，可以無(wú)信息或是文字、數(shù)字、空格、符號(hào)等任意信息，在公式中該組合可代表“南1地區(qū)”、“

5、南1地區(qū)”、“南1分公司”、“南12019”，“南1+某產(chǎn)品” 等等字樣。如果把 F8&"*"改為"*"&F8則可代表“某某南 1”、“某產(chǎn)品南1”等字樣，如改為"*"&F8&"*"則可代表"某某南 1地區(qū)”、“某產(chǎn)品南12019”等字樣。總之，查找值在開(kāi)頭則寫(xiě)為F8&"*",在結(jié)尾則寫(xiě)為"*"&F8 ,在中間則寫(xiě)為"*"&F8&"*"。圖2A8CDE2厘區(qū)楣售新

6、(萬(wàn)元)3北。址33504而打中L7251E郁也應(yīng)62S76加1厘E3T297908&8北1城區(qū)327q北,博區(qū)6BL110血尤而沿回3075L心再對(duì)反29G141 213玄XU8地區(qū)L520CS：6而出1區(qū)T147L7加門(mén)區(qū)；8北砒廢孫呂-9而廿1厄MPitrttKTECi2F . G .KHE銷(xiāo)告前行Th,G石度式柏2009=¥LgKnPt溝時(shí)2, 0>都307Hb父'E:匚工母印H .=VLO?ttBr tn 帆 1 E: L E.二1南40086=¥LOCEUF(F111,*,.B:C, 2,：'浙3S24=¥T.DCOPB：

7、 7P n)國(guó)內(nèi)某企業(yè)，已加全國(guó)相告戴情況如左表所示，現(xiàn)舒導(dǎo)需兔生 獨(dú)蟲(chóng)者由:、電3電九南箝用5萬(wàn)、地區(qū)的數(shù)掘*泅N制作 以T表略，快件實(shí)現(xiàn)契存杏林(2)查找字樣中包含了查找區(qū)間字樣參考圖3,以南1地區(qū)為例，要查找的字樣“南 1地區(qū)”，在查找的區(qū)間 B列無(wú)一模一 樣的字樣，但是 B列包含了 “南1”字樣，通過(guò)公式處理，將要查找的值改為“南 1”，可 以看到南1地區(qū)的左邊2個(gè)字符即為南1,通過(guò)公式“ LEFT(F8,2)'即可得到“南1”字樣, 完成結(jié)果查找。城區(qū)洎言箱:萬(wàn)北3雄區(qū)3353國(guó)怩國(guó)7251西叱區(qū)4237南H地區(qū)2729由今9094北1地區(qū)比57北3世區(qū)601；北加因73TB

8、3075南三羽Q4北也區(qū)M51HsilE42韓rtl2施麗Z147北2地區(qū)11Z7北g地區(qū)9248同地區(qū)5 HM舊世區(qū)752再向芫企dL已加至I二言二坂情況如左奏所示，明軟與需生單曲言*ai. 喀、13c4,25五個(gè)咆區(qū)用熟據(jù)，通過(guò)鎘作以下整超,快速實(shí)現(xiàn)熟據(jù)登 找地E儲(chǔ)曾雙萬(wàn)商，。列£式tl'itHE顏0H叫UXSUP (LSFT)比力.Be,工第二地區(qū)30T5-TLxnp:u-TCpe.a) b：Ci z,c-刀州區(qū)TH7-TLTinF n，F(xiàn) = T阿 G 1 -r； 'hOl南攏區(qū)=¥LMOP，1?FT m九打.H ：C, 2,0)1南地區(qū)Z9Z4”L

9、 KEF 'LETT -咫,2). B Q.'(3)查找區(qū)間及查找值包含了相同字樣，見(jiàn)圖 4首先，觀察數(shù)據(jù)可以看到，要查找區(qū)間的字樣完全沒(méi)有按照規(guī)定的標(biāo)準(zhǔn)進(jìn)行填列， 與要查找的字樣也完全不一致，但是分析發(fā)現(xiàn)，都包含了地名的關(guān)鍵字樣，通過(guò)結(jié)合前 面所說(shuō)的兩種處理方式，運(yùn)用LEFT函數(shù)以及輔助符號(hào)”*：組合出我們需要查找的字樣, 最終準(zhǔn)確查找出我們需要的信息。圖4第2頁(yè)/共6頁(yè)吐一id；FEjLWE»北6化國(guó)"耳南加建Gi287南壯區(qū)2729巴4二計(jì)弼6升優(yōu)區(qū)3EWT北ML區(qū)0日11北4歸Hrare南加W30T5匯顯啷2924北5幽區(qū)bfiSl北R地區(qū)42死3.

10、E：窘計(jì)為：2W 靶.二TL47升 Rjr.M01-7H湖區(qū)924B閆斌2區(qū)7503北Tit區(qū)而“池區(qū)洎白軸也五虎公式南1地區(qū)ZfiOB=VU03EUF' J+ SLsFTlf 2)&"Z, 0)矯虬9079< I- /I r'_"''. rh . '.H- ：. '- <",/!I旗加匕T147=VLOOKWPC* ftLWTKM, 2)貳 *: E; ；, 2. E由專(zhuān)屯0KS6哂LOOKUP，、0,南B地區(qū)27924=TunEDFCVfiLsrr<F8n Dsr*",止 j

11、2. d)國(guó)內(nèi)某企業(yè),已卻全國(guó)蕭0得埼用工左青明亍,現(xiàn)軸導(dǎo)高器單H查若內(nèi). 戲.公.7.柘石個(gè)地區(qū)F和據(jù).請(qǐng)"制下以下煮格.田淳二現(xiàn)取倨哲 找4、高階應(yīng)用當(dāng)我們需要查找的信息不止1個(gè)時(shí)，如何實(shí)現(xiàn)快速的公式查找？見(jiàn)下圖5 (此處假設(shè)不同產(chǎn)品銷(xiāo)售價(jià)格允許不一致，且每個(gè)地區(qū)只銷(xiāo)售一種產(chǎn)品)現(xiàn)需要查找完整的銷(xiāo)售數(shù)據(jù)信息情況，以南1地區(qū)為例。首先，我們?cè)诘?列鍵入對(duì)應(yīng)查找信息字段在查找區(qū)間的位置，如“銷(xiāo)售額”在第 2 列(以B列為第一列)，“銷(xiāo)售人員”在第 3歹U,依次類(lèi)推。然后在 K9 單元格鍵入公式 “ =VLOOKUP($J9,$B$2:$G$33,K$7,0)公式解讀“$J9”，查找J

12、9,其中鎖定J列，前面加“ $”符號(hào)即可；“$B$2:$G$33”，查找區(qū)間為B2至G33區(qū)間，分別鎖定行和列；“K$7'，查找的位置為K7單元格的數(shù)字系列，鎖定第 7行。然后將J9單元格的公式復(fù)制到J9至O12區(qū)間的所有單元格，即可完成全部數(shù)據(jù)的查詢(xún)。為何要加“ $”“$”的作用主要是鎖定行和列，在進(jìn)行公式復(fù)制的時(shí)候，查找的條件和區(qū)間等參數(shù)按照我們預(yù)設(shè)的規(guī)制進(jìn)行變化。例如：鎖定列，J9如果不鎖定J列，我們把K9單元格的公式復(fù)制到 L9單元格，公式就變成了 “=VLOOKUP（K9,$B$2:$G$33,L$7,0）,要查找的并不是我們要找的值。鎖定行，"K$7'如果

13、不鎖定第7行，我們把J9公式復(fù)制到J10,公式就變成了 “=VLOOKUP（$J9,$B$2:$G$33,K$8,0）,而K8并不是我們預(yù)想要的查找位置?？傊绻绘i定列，我們把公式每往前（-）后（+）復(fù)制移動(dòng)一定的列數(shù)，其他對(duì)應(yīng)參數(shù)的列數(shù)都會(huì)移動(dòng)相應(yīng)的列數(shù)， 如果不鎖定行，每往上（-）下（+）復(fù)制移動(dòng)一定的行數(shù)， 其他對(duì)應(yīng)參數(shù)的列數(shù)都會(huì)移動(dòng)相應(yīng)的行數(shù)。圖5F 一 。 _ 1 一 /&"I : J_ K_F如蚪年12月情售伯恩表2壯HMU斗，1元j廣鼠與L.out t :*北州E.33S9踐五BOOL“山心口%4寓地支“251也因31'.11513E南日施里62 B

14、7+紅2LH&/12/25n的g6*我電2T29.明日002xtl 19/12/63914曹逸二切窿學(xué)E33可北泗門(mén)況印.切0WS3K0北3地圮Mil李國(guó)AM21"上班二7373心BD01ZDl/12/13IN1H W.3D73物三core血哂如？33IZ.工鉆照ECfl?2ZIT詞6651昵日在35511+北地工4295小SLBWJ3書(shū)181E*為.畝CW114：11中鹿3鳴7H7TEiar17才上址區(qū)9127.-紅BDOSn*1£北BifiZ9143小紅EDOS2019/12/34E3E1名甫炮ESM6嗔B002；01*n/lX/2T通23075CQ小偉 . 1

15、- -304S因內(nèi)姓企業(yè)f已如空國(guó)旭也朝也況也左哀所示咀招號(hào)曲全粒電暑，*1, *5曲叵町時(shí)忠。通過(guò)制網(wǎng)下 總帶步逮，期期I有棺冊(cè)用皆離萬(wàn)元吟聲=產(chǎn)同駐號(hào)常都中用凸5“年件工利掄一南區(qū)ZflCB小麗C0012OT9/13/Z31+31二ULOCTUF力，第1 E：3G拗，K37, 0RWE30755;三COQZ2Q19/1Z/093M3-ii- S: R 1 r> !17L47至明用心1 1:.:關(guān)”" ! m iv，博,* f u邛力區(qū)的時(shí)董四aojMlE門(mén)中3CQE=皿/|"'尸打仁尸在¥：FG=!5r.x國(guó)小時(shí)町町220 5/12/2Z1T-u

16、udomp 臼ji 一 ：花 斜 力- V rij5、終極應(yīng)用1）不同情況下的函數(shù)綜合應(yīng)用在進(jìn)行數(shù)據(jù)處理時(shí)，以上所有的情況均有可能同時(shí)出現(xiàn)，而且我們往往需要對(duì)數(shù)據(jù)等信息進(jìn)行匯總，同時(shí)，同時(shí)因各地區(qū)人員、 統(tǒng)計(jì)標(biāo)準(zhǔn)等參差不齊，而我們?cè)谶M(jìn)行數(shù)據(jù)處理的時(shí) 候又必須保證原始數(shù)據(jù)的完整性，這時(shí)候就需要用到很多輔助公式、符號(hào)、單元格等手段進(jìn)行數(shù)據(jù)處理。結(jié)合上述出現(xiàn)的情況，通過(guò)圖6做處理詳解。這里我們要對(duì)需要統(tǒng)計(jì)的數(shù)據(jù)做匯總，同時(shí)假設(shè)南5本月沒(méi)有銷(xiāo)售（處理前不知情），而且統(tǒng)計(jì)的字樣和查找的字樣存在各種不同的情況。首先進(jìn)行數(shù)據(jù)分析，需要查找的字樣包含了地區(qū)關(guān)鍵信息， 查找區(qū)間字段也包含了地區(qū) 關(guān)鍵信息字樣。先

17、對(duì)查找的字樣進(jìn)行公式處理。 因需要統(tǒng)計(jì)的標(biāo)識(shí)較多， 要完成快速處理需 添加輔助單元格以及鎖定符號(hào)。然后，結(jié)合前述講解對(duì)函數(shù)進(jìn)行加工處理，綜合考慮不同的情景。由于涉及到銷(xiāo)售額以及銷(xiāo)售數(shù)量的統(tǒng)計(jì)，為保證統(tǒng)計(jì)數(shù)據(jù)有效，在計(jì)算的結(jié)果中不能出現(xiàn)錯(cuò)誤值，因此，結(jié)合 IFERROF®數(shù)將錯(cuò)誤值歸零。第3頁(yè)/共6頁(yè)設(shè)定完畢后，直接復(fù)制公式到相應(yīng)區(qū)間，就能得到我們想要的數(shù)據(jù)了。2019年L2月梢衿信息喪It已徜號(hào)n Ji ->U! i/FR泗14.聚電r，河jttattE339B岫BXigZC4 s/*郎罵例了rzai軀皿A 001：SCXLBi'lZ/LZimmtni'E心H1

18、小fl.儂爾打E：+,加地目M翌1明BX220曰口匕弋39C4崖坨井HBO李曲A 002SMLBi'lZi'E.Cisetsr ji»L3：燈他K031as已卻£國(guó)，鄧口亶房/你四n野/舞星置12. U. 心五i枷三明修M、電11周瘴1：1下*4J附）RRHII癡抵ME情"身產(chǎn)R出與KBtlH泊譽(yù)"件UbiiE!»*甫否肺1aWML內(nèi)知地Z7251他四1513電日城仁tSBiTd-nBMS3MQ.U3'25meE小明州W制1如團(tuán)已514商號(hào)合計(jì)享四A哨W2陶之注匕3洞蚤=RLQfl21r 2fi3W3R3HE11小朋Bi" ?加L知】引掰:7：1】共陞7375T冏BMS3561書(shū)2匚m3075小虹B0C3aiiarss+-161L5.tK4611小麗加1加"1一工L431血題R德H-1-U梗口鼻260*小明BQ023MW12J27魏i*14：o（s，川1沙防融鶴區(qū)iirrC0C3泡磯才弱北9城田tsd中明B0O230W 747n?SSA41純前嗨R1R1恒計(jì)杜帕ilTWET5U?1F汕63m1研&quo