Përmbajtje
Ky tutorial i vogël shpjegon se si të krijoni një funksion VPR (VLOOKUP) me ndjeshmëri ndaj shkronjave të vogla, tregon disa formula të tjera që Excel mund t'i kërkojë në një mënyrë të ndjeshme ndaj shkronjave të vogla dhe vë në dukje pikat e forta dhe të dobëta të secilit funksion.
Unë mendoj se çdo përdorues i Excel e di se çfarë funksioni kryen kërkimin vertikal. Është e drejtë, është një funksion VPR. Megjithatë, pak njerëz e dinë këtë VPR nuk është i ndjeshëm ndaj shkronjave të mëdha, dmth. karakteret e vogla dhe të mëdha janë identike për të.
Këtu është një shembull i shpejtë që tregon paaftësinë VPR njohin regjistrin. Supozoni në një qelizë A1 përmban vlerën “faturë” dhe qelizën A2 – “Faturë”, formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… do të ndalojë kërkimin e tij në "faturë" pasi ajo vlerë vjen e para në listë dhe do ta nxjerrë vlerën nga qeliza B1.
Më vonë në këtë artikull, unë do t'ju tregoj se si të bëni VPR rast i ndjeshëm. Përveç kësaj, do të mësojmë disa funksione të tjera që mund të kryejnë kërkime të ndjeshme ndaj rasteve në Excel.
Ne do të fillojmë me më të thjeshtat - VIEW (KËRKONI) dhe SUMPRODUCT (SUMPRODUCT), të cilat, për fat të keq, kanë disa kufizime të rëndësishme. Më pas, ne do të hedhim një vështrim më të afërt në formulën pak më komplekse INDEKS+Përputhje (INDEX+MATCH), i cili funksionon pa të meta në çdo situatë dhe me çdo grup të dhënash.
Funksioni VLOOKUP është i ndjeshëm ndaj shkronjave të vogla
Siç e dini tashmë, funksioni i zakonshëm VPR është e pandjeshme ndaj shkronjave të vogla. Megjithatë, ekziston një mënyrë për ta bërë atë të ndjeshëm ndaj rasteve. Për ta bërë këtë, duhet të shtoni një kolonë ndihmëse në tabelë, siç tregohet në shembullin e mëposhtëm.
Supozoni në një kolonë B ka identifikues produkti (Artikulli) dhe ju dëshironi të nxirrni çmimin e produktit dhe komentin përkatës nga kolonat C и D. Problemi është se identifikuesit përmbajnë karaktere të vogla dhe të mëdha. Për shembull, vlerat e qelizave B4 (001Tvci3u) dhe B5 (001Tvci3U) ndryshojnë vetëm në rastin e karakterit të fundit, u и U respektivisht.
Siç mund ta imagjinoni, formula e zakonshme e kërkimit
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
do te kthehen $ 90, që nga vlera 001Tvci3u është në diapazonin e kërkimit më herët se 001Tvci3U. Por kjo nuk është ajo që na nevojitet, apo jo?
Për të kërkuar me një funksion VPR në Excel të ndjeshëm ndaj rasteve, do t'ju duhet të shtoni një kolonë ndihmëse dhe të mbushni qelizat e saj me formulën e mëposhtme (ku B është kolona e kërkimit):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Kjo formulë ndan vlerën e dëshiruar në karaktere të veçanta, zëvendëson çdo karakter me kodin e tij (për shembull, në vend të A në vend të kësaj në 65 a kodi 97) dhe më pas i kombinon këto kode në një varg unik numrash.
Pas kësaj, ne përdorim një funksion të thjeshtë VPR për kërkimin e ndjeshëm ndaj rasteve:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funksionimi i duhur i funksionit VPR ndjeshmëria ndaj rastit varet nga dy faktorë:
- Kolona ndihmëse duhet të jetë kolona më e majtë në diapazonin e shikueshëm.
- Vlera që kërkoni duhet të përmbajë një kod karakteri në vend të vlerës reale.
Si të përdorni funksionin CODE në mënyrë korrekte
Formula e futur në qelizat e kolonës ndihmëse supozon që të gjitha vlerat e kërkimit tuaj kanë të njëjtin numër karakteresh. Nëse jo, atëherë duhet të dini numrat më të vegjël dhe më të mëdhenj dhe të shtoni sa më shumë veçori IFERROR (IFERROR) sa karaktere është diferenca midis vlerës më të shkurtër dhe më të gjatë të kërkuar.
Për shembull, nëse vlera më e shkurtër e kërkimit është 3 karaktere dhe më e gjata është 5 karaktere, përdorni këtë formulë:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Për funksionin PSTR (MID) Ju jepni argumentet e mëposhtme:
- Argumenti i parë - tekst (tekst) është teksti ose referenca e qelizës që përmban karakteret që do të nxirren (në rastin tonë është B2)
- Argumenti i parë - fillimi_num (start_position) është pozicioni i personazhit të parë që do të nxirret. ju hyni 1 në funksionin e parë PSTR, 2 – në funksionin e dytë PSTR etj.
- Argumenti i parë - numri_karaktereve (numri_i_personazheve) – Përcakton numrin e karaktereve për të nxjerrë nga teksti. Meqenëse na duhet vetëm 1 karakter gjatë gjithë kohës, në të gjitha funksionet ne shkruajmë 1.
Kufizime: funksion VPR nuk është zgjidhja më e mirë për kërkimet e ndjeshme ndaj rasteve në Excel. Së pari, kërkohet shtimi i një kolone ndihmëse. Së dyti, formula bën një punë të mirë vetëm nëse të dhënat janë homogjene ose dihet numri i saktë i karaktereve në vlerat e kërkuara. Nëse ky nuk është rasti juaj, është më mirë të përdorni një nga zgjidhjet që tregojmë më poshtë.
Funksioni LOOKUP për kërkim të ndjeshëm ndaj rasteve
funksion VIEW (LOOKUP) lidhur VPR, megjithatë sintaksa e saj lejon kërkime të ndjeshme ndaj rasteve pa shtuar një kolonë ndihmëse. Për ta bërë këtë, përdorni VIEW e kombinuar me funksionin saktë (SAKTE).
Nëse marrim të dhënat nga shembulli i mëparshëm (pa një kolonë ndihmëse), atëherë formula e mëposhtme do të përballet me detyrën:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Kërkimet e formulave në rreze A2: A7 përputhje e saktë me vlerën e qelizës F2 sensitive dhe kthen vlerën nga kolona B e të njëjtit rresht.
Like VPRfunksion VIEW funksionon në mënyrë të barabartë me tekstin dhe vlerat numerike, siç mund ta shihni në pamjen e mëposhtme të ekranit:
E rëndësishme! Në mënyrë që funksioni VIEW të punuara në mënyrë korrekte, vlerat në kolonën e kërkimit duhet të renditen në rend rritës, pra nga më i vogli tek më i madhi.
Më lejoni të shpjegoj shkurtimisht se si funksionon funksioni saktë në formulën e treguar më sipër, pasi kjo është pika kryesore.
funksion saktë krahason dy vlerat e tekstit në argumentet 1 dhe 2 dhe kthen TRUE nëse janë saktësisht të njëjta, ose FALSE nëse nuk janë. Është e rëndësishme për ne që funksioni saktë rast i ndjeshëm.
Le të shohim se si funksionon formula jonë PARAQIT+EKAKTE:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funksion saktë krahason vlerën e qelizës F2 me të gjithë elementët në një kolonë A (A2:A7). Kthen TRUE nëse gjendet një përputhje e saktë, përndryshe FALSE.
- Meqenëse ju jepni argumentin e parë të funksionit VIEW vlera TRUE, nxjerr vlerën përkatëse nga kolona e specifikuar (në rastin tonë, kolona B) vetëm nëse gjendet një përputhje e saktë, e ndjeshme ndaj shkronjave të vogla.
Shpresoj se ky shpjegim ishte i qartë dhe tani e kuptoni idenë kryesore. Nëse po, atëherë nuk do të keni asnjë vështirësi me funksionet e tjera që do t'i analizojmë më tej, sepse. të gjithë punojnë në të njëjtin parim.
Kufizime: Të dhënat në kolonën e kërkimit duhet të renditen në rend rritës.
SUMPRODUCT – gjen vlerat e tekstit, me ndjeshmëri të madhe, por kthen vetëm numra
Siç e keni kuptuar tashmë nga titulli, SUMPRODUCT (SUMPRODUCT) është një tjetër funksion i Excel që do t'ju ndihmojë të bëni një kërkim të ndjeshëm ndaj shkronjave të vogla, por do të kthejë vetëm vlera numerike. Nëse ky opsion nuk ju përshtatet, atëherë mund të vazhdoni menjëherë te paketa INDEKS+Përputhje, i cili jep një zgjidhje për çdo rast dhe për çdo lloj të dhënash.
Së pari, më lejoni të shpjegoj shkurtimisht sintaksën e këtij funksioni, kjo do t'ju ndihmojë të kuptoni më mirë formulën e ndjeshme ndaj shkronjave të vogla që vijon.
funksion SUMPRODUCT shumëzon elementet e vargjeve të dhëna dhe kthen shumën e rezultateve. Sintaksa duket si kjo:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Meqenëse kemi nevojë për një kërkim të ndjeshëm ndaj rasteve, ne përdorim funksionin saktë (EXACT) nga shembulli i mëparshëm si një nga shumëzuesit:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Si ju kujtohet, saktë krahason vlerën e qelizës F2 me të gjithë elementët në një kolonë A. Kthen TRUE nëse gjendet një përputhje e saktë, përndryshe FALSE. Në operacionet matematikore, Excel merr TRUE si 1, dhe FALSE për 0Me tutje SUMPRODUCT i shumëzon këta numra dhe i mbledh rezultatet.
Zerot nuk numërohen sepse kur shumëzohen japin gjithmonë 0. Le të hedhim një vështrim më të afërt se çfarë ndodh kur një përputhje e saktë në një kolonë A gjetur dhe kthyer 1… Funksioni SUMPRODUCT shumëzon numrin në kolonë B on 1 dhe kthen rezultatin - saktësisht të njëjtin numër! Kjo për shkak se rezultatet e produkteve të tjera janë zero dhe ato nuk ndikojnë në shumën që rezulton.
Fatkeqësisht funksioni SUMPRODUCT nuk mund të punojë me vlerat dhe datat e tekstit pasi ato nuk mund të shumëzohen. Në këtë rast, do të merrni një mesazh gabimi #VALUE! (#VLERË!) si në qelizë F4 në foton më poshtë:
Kufizime: Kthen vetëm vlera numerike.
INDEX + MATCH – kërkim i ndjeshëm ndaj rasteve për çdo lloj të dhënash
Së fundi, ne jemi afër një formule kërkimi të pakufizuar dhe të ndjeshme ndaj rasteve që funksionon me çdo grup të dhënash.
Ky shembull vjen i fundit, jo sepse më e mira është lënë për ëmbëlsirë, por sepse njohuritë e marra nga shembujt e mëparshëm do t'ju ndihmojnë të kuptoni më mirë dhe më shpejt formulën e ndjeshme ndaj rasteve. INDEKS+Përputhje (INDEKS+Përputhje).
Siç e keni menduar ndoshta, kombinimi i funksioneve ME TE EKSPOZUAR и INDEKSI përdoret në Excel si një alternativë më fleksibël dhe më e fuqishme për VPR. Artikulli Përdorimi i INDEX dhe MATCH në vend të VLOOKUP do të shpjegojë në mënyrë të përsosur se si këto funksione funksionojnë së bashku.
Unë vetëm do të përmbledh pikat kryesore:
- funksion ME TE EKSPOZUAR (MATCH) kërkon një vlerë në një diapazon të caktuar dhe kthen pozicionin e saj relativ, domethënë numrin e rreshtit dhe/ose të kolonës;
- Tjetra, funksioni INDEKSI (INDEX) kthen një vlerë nga një kolonë dhe/ose rresht i caktuar.
Në formulë INDEKS+Përputhje mund të kërkoni me ndjeshmëri të madhe të rasteve, ju duhet vetëm të shtoni një funksion në të. Nuk është e vështirë të merret me mend se çfarë është përsëri saktë (SAKTE):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Në këtë formulë saktë funksionon në të njëjtën mënyrë si në lidhje me funksionin VIEW, dhe jep të njëjtin rezultat:
Vini re se formula INDEKS+Përputhje e mbyllur në kllapa kaçurrelë është një formulë grupi dhe duhet ta plotësoni duke shtypur Ctrl + Shift + Enter.
Pse është INDEX+MATCH zgjidhja më e mirë për kërkimin e ndjeshëm ndaj rasteve?
Përparësitë kryesore të paketës INDEKSI и ME TE EKSPOZUAR:
- Nuk kërkon shtimin e një kolone ndihmëse, ndryshe nga VPR.
- Nuk kërkon që kolona e kërkimit të renditet, ndryshe nga VIEW.
- Punon me të gjitha llojet e të dhënave - numra, tekst dhe data.
Kjo formulë duket perfekte, apo jo? Në fakt, nuk është. Dhe kjo është arsyeja pse.
Supozoni se qeliza në kolonën e vlerës së kthimit e lidhur me vlerën e kërkimit është bosh. Çfarë rezultati do të sjellë formula? Jo? Le të shohim se çfarë kthen në të vërtetë formula:
Oops, formula kthehet zero! Ky mund të mos jetë një problem i madh nëse jeni duke punuar me vlera teksti të pastër. Megjithatë, nëse tabela përmban numra, duke përfshirë zero "reale", kjo bëhet problem.
Në fakt, të gjitha formulat e tjera të kërkimit (VLOOKUP, LOOKUP dhe SUMPRODUCT) që diskutuam më parë sillen në të njëjtën mënyrë. Por ju dëshironi formulën perfekte, apo jo?
Për të bërë një formulë të ndjeshme sipas rastit INDEKS+Përputhje perfekte, vendoseni në funksion IF (IF) që do të testojë një qelizë me një vlerë të kthimit dhe do të kthejë një rezultat bosh nëse është bosh:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Në këtë formulë:
- B është një kolonë me vlera të kthyera
- 1+ është një numër që kthen pozicionin relativ të qelizës të kthyer nga funksioni ME TE EKSPOZUAR, në adresën reale të qelisë. Për shembull, në funksionin tonë ME TE EKSPOZUAR grup kërkimi i dhënë A2: A7, domethënë pozicioni relativ i qelizës A2 do të 1, sepse është i pari në grup. Por pozicioni aktual i qelizës A2 në kolonë është 2, kështu shtojmë 1për të bërë diferencën dhe për të pasur funksionin INDIREKT (INDIRECT) mori vlerën nga qeliza e dëshiruar.
Fotografitë më poshtë tregojnë formulën e korrigjuar të ndjeshme ndaj shkronjave INDEKS+Përputhje Në veprim. Ai kthen një rezultat bosh nëse qeliza e kthyer është bosh.
E rishkrova formulën në kolona B:Dpër të përshtatur shiritin e formulës në pamjen e ekranit.
Formula kthehet 0nëse qeliza e kthyer përmban zero.
Nëse dëshironi lidhjen INDEKSI и ME TE EKSPOZUAR shfaqet një mesazh kur vlera e kthimit është bosh, mund ta shkruani në thonjëzat e fundit (“”) të formulës, për shembull, si kjo:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
