Sisu
See väike õpetus selgitab, kuidas funktsiooni luua VPR (VLOOKUP) tõstutundlik, näitab mitmeid teisi valemeid, mida Excel saab tõstutundlikult otsida, ning toob välja iga funktsiooni tugevad ja nõrgad küljed.
Arvan, et iga Exceli kasutaja teab, milline funktsioon vertikaalotsingut teostab. See on õige, see on funktsioon VPR. Seda teavad aga vähesed VPR ei ole tõstutundlik, st väikesed ja suured tähed on selle jaoks identsed.
Siin on kiire näide, mis näitab suutmatust VPR tunnusta register. Oletame, et rakus A1 sisaldab väärtust “bill” ja lahtrit A2 - "Arve", valem:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… lõpetab otsingu "arve" järgi, kuna see väärtus on loendis esimesel kohal, ja eraldab väärtuse lahtrist B1.
Hiljem selles artiklis näitan teile, kuidas seda teha VPR tõstutundlik. Lisaks õpime tundma veel mõnda funktsiooni, millega saab Excelis teha tõstutundlikke otsinguid.
Alustame kõige lihtsamast - Vaata (LOOKUP) ja SUMPRODUCT (SUMPRODUCT), millel on kahjuks mitmeid olulisi piiranguid. Järgmisena vaatleme veidi keerukamat valemit INDEX+VASTA (INDEX+MATCH), mis töötab veatult igas olukorras ja mis tahes andmekogumiga.
Funktsioon VLOOKUP on tõstutundlik
Nagu te juba teate, tavaline funktsioon VPR on tõstutundlik. Siiski on võimalus muuta see tõstutundlikuks. Selleks tuleb tabelisse lisada abiveerg, nagu on näidatud järgmises näites.
Oletame, et veerus B on toote identifikaatorid (Kaua) ja soovite veergudest välja võtta toote hinna ja vastava kommentaari C и D. Probleem on selles, et identifikaatorid sisaldavad nii väike- kui ka suurtähti. Näiteks lahtri väärtused B4 (001Tvci3u) ja B5 (001Tvci3U) erinevad ainult viimase märgi puhul, u и U võrra.
Nagu võite ette kujutada, tavaline otsinguvalem
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
naaseb $ 90, kuna väärtus 001Tvci3u on otsinguvahemikus varem kui 001Tvci3U. Aga see pole see, mida me vajame, eks?
Funktsiooniga otsimiseks VPR Exceli tõstutundlikus korral peate lisama abiveeru ja täitma selle lahtrid järgmise valemiga (kus B on otsinguveerg):
=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));"")
See valem jagab soovitud väärtuse eraldi tähemärkideks, asendab iga märgi selle koodiga (näiteks selle asemel A asemel 65 a kood 97) ja seejärel ühendab need koodid ainulaadseks numbrijadaks.
Pärast seda kasutame lihtsat funktsiooni VPR tõstutundliku otsingu jaoks:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funktsiooni nõuetekohane toimimine VPR tõstutundlik oleneb kahest tegurist:
- Abiveerg peab olema vaadatava vahemiku vasakpoolseim veerg.
- Otsitav väärtus peab tegeliku väärtuse asemel sisaldama märgikoodi.
Kuidas funktsiooni CODE õigesti kasutada
Abiveeru lahtritesse sisestatud valem eeldab, et kõigil teie otsinguväärtustel on sama arv märke. Kui ei, siis peate teadma väikseimaid ja suurimaid numbreid ning lisama võimalikult palju funktsioone VIGA (IFERROR), mitu tähemärki on vahe lühima ja pikima otsitud väärtuse vahel.
Näiteks kui lühim otsinguväärtus on 3 tähemärki ja pikim 5 tähemärki, kasutage järgmist valemit:
=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));"")
Funktsiooni jaoks PSTR (MID) Esitate järgmised argumendid:
- 1. argument - tekst (tekst) on teksti või lahtri viide, mis sisaldab ekstraheeritavaid märke (meie puhul on see B2)
- 2. argument - algus_number (alguspositsioon) on nendest esimestest ekstraheeritavatest märkidest asukoht. sisened 1 esimeses funktsioonis PSTR, 2 – teises funktsioonis PSTR ja nii edasi
- 3. argument - tähemärkide_arv (märkide_arv) – määrab tekstist eraldatavate märkide arvu. Kuna meil on kogu aeg vaja ainult 1 märki, kirjutame kõigis funktsioonides 1.
Piirangud: funktsioon VPR ei ole Excelis tõstutundlike otsingute jaoks parim lahendus. Esiteks on vaja lisakolonni lisamist. Teiseks teeb valem head tööd ainult siis, kui andmed on homogeensed või on teada otsitavate väärtuste täpne märkide arv. Kui see pole teie juhtum, on parem kasutada ühte allpool näidatud lahendustest.
LOOKUP funktsioon tõstutundlikuks otsinguks
funktsioon Vaata (LOOKUP) seotud VPR, kuid selle süntaks võimaldab teha tõstutundlikke otsinguid ilma abiveergu lisamata. Selleks kasutage Vaata funktsiooniga kombineerituna TÄIELIK (TÄPSELT).
Kui võtame andmed eelmisest näitest (ilma abiveeruta), saab ülesandega hakkama järgmine valem:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Valemiotsingud vahemikus A2: A7 täpne vaste lahtri väärtusega F2 tõstutundlik ja tagastab väärtuse sama rea veerust B.
nagu VPRfunktsioon Vaata töötab võrdselt teksti- ja arvväärtustega, nagu näete alloleval ekraanipildil:
Tähtis! Selleks, et funktsioon Vaata toimis õigesti, tuleks otsingu veerus olevad väärtused sortida kasvavas järjekorras, st väikseimast suurimani.
Lubage mul lühidalt selgitada, kuidas funktsioon töötab TÄIELIK ülaltoodud valemis, kuna see on võtmepunkt.
funktsioon TÄIELIK võrdleb kahte tekstiväärtust 1. ja 2. argumendis ja tagastab TRUE, kui need on täpselt samad, või FALSE, kui nad ei ole. Meie jaoks on oluline, et funktsioon TÄIELIK tõstutundlik.
Vaatame, kuidas meie valem töötab VAATA+TÄPNE:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funktsioon TÄIELIK võrdleb lahtri väärtust F2 kõigi elementidega veerus A (A2:A7). Tagastab väärtuse TRUE, kui leitakse täpne vaste, vastasel juhul VÄÄR.
- Kuna annate funktsiooni esimese argumendi Vaata väärtus TRUE, eraldab see määratud veerust (meie puhul veerust B) vastava väärtuse ainult siis, kui leitakse täpne vaste, tõstutundlik.
Loodan, et see seletus oli selge ja nüüd saate põhiideest aru. Kui jah, siis pole teil raskusi muude funktsioonidega, mida me edasi analüüsime, sest. nad kõik töötavad samal põhimõttel.
Piirangud: Otsingu veerus olevad andmed tuleb sortida kasvavas järjekorras.
SUMPRODUCT – otsib tekstiväärtusi, tõstutundlik, kuid tagastab ainult numbreid
Nagu pealkirjast juba aru saite, SUMPRODUCT (SUMPRODUCT) on veel üks Exceli funktsioon, mis aitab teil teha tõstutundlikku otsingut, kuid tagastab ainult arvväärtusi. Kui see valik teile ei sobi, saate kohe kimbu juurde minna INDEX+VASTA, mis annab lahenduse igaks juhtumiks ja mis tahes andmetüübi jaoks.
Esiteks lubage mul lühidalt selgitada selle funktsiooni süntaksit, see aitab teil paremini mõista järgnevat tõstutundlikku valemit.
funktsioon SUMPRODUCT korrutab antud massiivide elemendid ja tagastab tulemuste summa. Süntaks näeb välja selline:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Kuna vajame tõstutundlikku otsingut, kasutame funktsiooni TÄIELIK (TÄPSELT) eelmisest näitest ühe kordajana:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Nagu mäletate, TÄIELIK võrdleb lahtri väärtust F2 kõigi elementidega veerus A. Tagastab väärtuse TRUE, kui leitakse täpne vaste, vastasel juhul VÄÄR. Matemaatilistes operatsioonides võtab Excel väärtust TRUE kui 1, ja FALSE jaoks 0edasi SUMPRODUCT korrutab need arvud ja summeerib tulemused.
Nulle ei loeta, sest korrutatuna annavad nad alati 0. Vaatame lähemalt, mis juhtub, kui veerus on täpne vaste A leitud ja tagastatud 1… Funktsioon SUMPRODUCT korrutab veerus oleva arvu B on 1 ja tagastab tulemuse – täpselt sama numbri! Seda seetõttu, et teiste toodete tulemused on nullid ja need ei mõjuta saadud summat.
Kahjuks funktsioon SUMPRODUCT ei saa töötada tekstiväärtuste ja kuupäevadega, kuna neid ei saa korrutada. Sel juhul kuvatakse tõrketeade #VALUE! (#VALUE!) nagu lahtris F4 alloleval pildil:
Piirangud: Tagastab ainult arvväärtused.
INDEX + MATCH – tõstutundlik otsing mis tahes andmetüübi jaoks
Lõpuks oleme lähedal piiramatule ja tõstutundlikule otsinguvalemile, mis töötab mis tahes andmekogumiga.
See näide on viimane, mitte sellepärast, et parim jääb magustoiduks, vaid seepärast, et eelmistest näidetest saadud teadmised aitavad teil tõstutundlikust valemist paremini ja kiiremini mõista. INDEX+VASTA (INDEKS+VASTANE).
Nagu arvatavasti arvasite, funktsioonide kombinatsioon ROHKEM AVALDATUD и INDEX kasutatakse Excelis paindlikuma ja võimsama alternatiivina VPR. Artikkel INDEXi ja MATCHi kasutamine VLOOKUP-i asemel selgitab suurepäraselt, kuidas need funktsioonid koos töötavad.
Kordan lihtsalt põhipunktid:
- funktsioon ROHKEM AVALDATUD (MATCH) otsib väärtust antud vahemikus ja tagastab selle suhtelise positsiooni, st rea ja/või veeru numbri;
- Järgmiseks funktsioon INDEX (INDEX) tagastab väärtuse määratud veerust ja/või reast.
Valemile INDEX+VASTA saab otsida tõstutundlikult, peate sellele lisama ainult ühe funktsiooni. Pole raske arvata, mis see jälle on TÄIELIK (TÄPNE):
=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))
Selles valemis TÄIELIK töötab samamoodi kui funktsiooniga koos Vaataja annab sama tulemuse:
Pange tähele, et valem INDEX+VASTA lokkis sulgudes on massiivi valem ja peate selle lõpetama vajutades Ctrl + Tõstuklahv + Sisesta.
Miks on INDEX+MATCH tõstutundliku otsingu jaoks parim lahendus?
Kimbu peamised eelised INDEX и ROHKEM AVALDATUD:
- Erinevalt ei nõua abisamba lisamist VPR.
- Erinevalt ei nõua otsinguveeru sorteerimist Vaata.
- Töötab igat tüüpi andmetega – numbrid, tekst ja kuupäevad.
See valem tundub täiuslik, kas pole? Tegelikult ei ole. Ja sellepärast.
Oletame, et otsinguväärtusega seotud tagastusväärtuse veeru lahter on tühi. Millise tulemuse valem tagastab? Ei? Vaatame, mida valem tegelikult tagastab:
Oih, valem tagastab nulli! See ei pruugi olla suur probleem, kui töötate puhta tekstiväärtustega. Kui tabel sisaldab numbreid, sealhulgas "päris" nulle, muutub see probleemiks.
Tegelikult käituvad kõik muud otsinguvalemid (VLOOKUP, LOOKUP ja SUMPRODUCT), millest me varem rääkisime, samamoodi. Aga sa tahad täiuslikku valemit, eks?
Valemi tõstetundlikuks muutmiseks INDEX+VASTA täiuslik, pane see funktsiooni IF (IF), mis testib tagastatava väärtusega lahtrit ja tagastab tühja tulemuse, kui see on tühi:
=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));"")
Selles valemis:
- B on tagastusväärtustega veerg
- 1+ on arv, mis muudab funktsiooni tagastatud lahtri suhtelist asukohta ROHKEM AVALDATUD, lahtri tegelikule aadressile. Näiteks meie funktsioonis ROHKEM AVALDATUD otsingu massiiv antud A2: A7, see tähendab lahtri suhtelist asukohta A2 will 1, sest see on massiivi esimene. Aga raku tegelik asukoht A2 veerus on 2, seega lisame 1vahet tasa teha ja funktsiooni omada KAUDSEID (INDIRECT) tõi väärtuse soovitud lahtrist.
Allolevatel piltidel on näidatud parandatud tõstutundlik valem INDEX+VASTA Tegevuses. Tagastab tühja tulemuse, kui tagastatud lahter on tühi.
Kirjutasin valemi ümber veergudeks B:Det sobitada valemiriba ekraanipildile.
Valem naaseb 0kui tagastatud lahter sisaldab nulli.
Kui soovite linki INDEX и ROHKEM AVALDATUD kuvatakse mõni teade, kui tagastatav väärtus on tühi, võite selle kirjutada valemi viimastesse jutumärkidesse (“”), näiteks järgmiselt:
=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.")
