Teil on need valikud
Kiireim ja parim viis konkreetsete kriteeriumide otsimiseks on funktsiooni VLOOKUP kasutamine. Selle abil on võimalik Microsoft Excelis leida väärtus millisekundites.
VLOOKUP - see toimib otsingukriteeriumiga
Et mõista, kuidas VLOOKUP töötab mitme otsingukriteeriumiga, peavad Exceli kasutajad kõigepealt mõistma, kuidas VLOOKUP -funktsioon on põhimõtteliselt üles ehitatud. Järgmine näide näitab ettevõttes renditöötajate arvu kalendriaasta erinevatel kuudel.
VLOOKUPi abil tuleks näidata, kui palju ajutisi töötajaid märtsis ja detsembris tööle võeti.
Detsembri näite valem VLOOKUP on järgmine:
VLOOKUP (D8, A: B, 2, 0)
Valem otsib renditöötajate vastavaid numbreid järgmiselt.
- Leidke veergudest A ja B väärtus D8 (detsembrikuu).
- Leidke veeru B väärtus. Seda tähistab valemis 2.
- Valemis olev 0 näitab, et tuleks otsida täpset väärtust.
Funktsioon VLOOKUP otsib ühte otsingukriteeriumi. Valemi maatriks on piirkond, kus vastavad väärtused asuvad. Lisaks peab veerg VLOOKUP sisaldama otsitavat väärtust.
Tähtis:
Funktsiooni VLOOKUP korral peab otsingukriteerium alati olema 1. veerus. Otsitud kriteeriumist paremal tuleb näidata ka otsitud seotud väärtused. Kui arvutustabel on üles ehitatud erinevalt, siis VLOOKUP ei tööta. Sel juhul võib soovitud päringu loomisel olla kasulik funktsioonide INDEX ja COMPARE kombinatsioon.
VLOOKUP mitme otsingukriteeriumiga - kuidas integreerida IF -funktsioon
Ülaltoodud näites küsiti, kui palju renditöötajaid töötas ettevõttes teatud kuu jooksul. Funktsiooni VLOOKUP abil saab konkreetseid väärtusi kergesti tuletada. Järgmises näites näete ulatuslikku artiklite loendit 36 kirjega [1], millest soovite kiiresti ja lihtsalt filtreerida sobivaid artiklinumbreid mitme kriteeriumi alusel.
Otsingukriteeriumid on loetletud paremal olevas tabelis. Leitud artikli number peaks ilmuma lahtrisse H8.
Esimene samm on lisada valem koos VLOOKUP -iga, mille abil saate otsingukriteeriumi alusel leida üksuse numbri. Seejärel laiendate seda valemit samm -sammult teiste otsingukriteeriumide jaoks.
Kasutage lahtris H8 järgmise valemi abil lahtri H3 suuruse üksuse numbrit: [2]
= OTSING (H3, A3: E40, 5
Valem otsib vastavat artiklinumbrit järgmiselt.
- Leidke veergudest A kuni E väärtus H3 (suurus 142).
- Leidke väärtus veerust E (artikli number). Seda tähistab valemis 5.
Kokkuvõttes edastab funktsioon VLOOKUP esimese argumendina täieliku loendi, milles te sisu otsite. Otsingukriteerium, mida tuleb ülekantava ala esimesest veerust otsida, on keskendunud teisele argumendile. Kolmas argument määratleb veeru, kust tulemus tagastatakse.
Esitage loend, mis on sorteeritud kasvavas järjekorras esimese veeru alusel, et Excel leiaks järgmise madalama väärtuse. Valem leiab lahtrist E16 artikli numbri 2.253. Kuna lahtri H3 suurust 142 loendis ei kuvata, leitakse järgmine madalam väärtus 139.
Lisage teine otsingukriteerium
Järgmise sammuna soovite teise kriteeriumina otsida ka lahtrist H4 olevat rühma. Tuleb leida ainult artikli number, milles otsitav rühm esineb.
Selleks lisage lahtrisse H8 valemile IF -funktsioon ja sisestage valem massiivivalemina:
= VLOOKUP (H3; IF (B3: B40 = H4; A3: E40; ““); 5)
Info:
Microsoft Excelis eristatakse tavapäraseid valemeid ja võimsamaid maatriksvalemeid. Maatriksvalemid võimaldavad Exceli kasutajatel teha keerulisi arvutusi. Neid ei saa standardvalemitega läbi viia. Kuna pärast valemi sisestamist tuleb klahvikombinatsiooni "CTRL + SHIFT + ENTER" vajutada, on maatriksvalemid rahvusvaheliselt tuntud kui või CSE valemid (CTRL + SHIFT + ENTER).
Kinnitage valem pärast selle sisestamist, kasutades maatriksvalemina klahvikombinatsiooni Ctrl + Shift + Enter. [3]
Selle tulemusena tarnitakse kauba number 1.188. Valem leiab veerust "Suurus" väärtuse 126. See on arvutustabeli suurim väärtus. See on väiksem või võrdne otsitava väärtusega 142 ja selle sisu on rühma suhtes C2.
Valemi IF -funktsioon kontrollib vahemiku B3: B40 iga lahtri vastavust lahtri H4 sisule. Niipea kui vaste on olemas, edastatakse ala B3: E40 vastav rida funktsioonile VLOOKUP.
Lisage hõlpsalt täiendavaid otsingukriteeriume igal ajal
Samamoodi saate lisada täiendavaid otsingukriteeriume. Selleks pesake valemisse rohkem IF -funktsioone. Kõigi nelja otsingukriteeriumi täitmiseks lisage lahtrisse H8 valemisse veel kaks pesastatud IF -funktsiooni:
= VLOOKUP (H3; IF (B3: B40 = H4; IF (C3: C40 = H5; IF (D3: D40 = H6; A3: E40; ""); ""; ""); 5)
Pärast maatriksi valemi kinnitamist klahvikombinatsiooni Ctrl + Shift + Enter abil tagastab VLOOKUP artikli numbri 1.748. See on number reast 14 suuruse 125. See on veeru "Suurus" kõrgeima väärtusega rida, kus on täidetud kõik kolm täiendavat kriteeriumi. [4]
Esimene IF -funktsioon ei edasta vastavat rida kohe funktsioonile VLOOKUP, kui kontroll on positiivne. Selle asemel kontrollib teine IF -funktsioon, kas teise lisakriteeriumiga on vastavust. Kui ka see kontroll on positiivne, kasutatakse kolmandat IF -funktsiooni. Alles siis, kui kõik kolm IF -funktsiooni tagastavad positiivse kontrolli, edastab kolmas IF -funktsioon vastava rea funktsioonile VLOOKUP. Sel viisil saate pesastada kuni seitse IF -funktsiooni ja seega kontrollida kuni kaheksat kriteeriumi.
Kokkuvõte: Siin on tõhus viis Excelis mitme kriteeriumi otsimiseks
Microsoft Exceli kasutajad seisavad oma igapäevatöös pidevalt silmitsi küsimusega, kuidas lugeda andmeid ja teavet ulatuslikest või pesastatud arvutustabelitest. Exceli kasutajad leiavad funktsiooni VLOOKUP kõige tõhusama viitefunktsiooni. Funktsiooni VLOOKUP ja muude võrdlusfunktsioonide suur eelis on see, et vaikimisi otsivad nad järgmist madalamat väärtust kohe, kui otsitud väärtust ei leita.
Kuna funktsioon VLOOKUP on vaikimisi piiratud ühe otsinguterminiga, tuleb mitme otsingukriteeriumi jaoks kasutada laiendatud valemit. Funktsiooni VLOOKUP abil mitme otsingutermini leidmiseks sobib IF -funktsioonidega massiivivalem. See kontrollib üksikuid IF -funktsioone üksteise järel ja kui tulemus on positiivne, viitab see VLOOKUP -le. See tagab erinevate otsinguterminitega ulatuslike arvutustabelite õige tulemuse.
KKK
Mida saab funktsioon VLOOKUP teha?
Funktsioon VLOOKUP Microsoft Excelis on üks Exceli olulisemaid ja tõhusamaid funktsioone. VLOOKUPi kaudu saate automaatselt otsida konkreetset väärtust ulatuslikust arvutustabelist. Tulemust redigeeritakse ja väljastatakse teises lahtris.
Millal kasutate EXCEL -is IF -funktsiooni?
Funktsioon IF on Microsoft Excelis üks laialdasemalt kasutatavaid funktsioone. Esimeses etapis kasutatakse tingimuse määratlemiseks funktsiooni IF. Teises etapis lingite IF näiteks funktsiooniga THEN ja määrate, milline teave väljastatakse. Funktsiooni ELSE saab kasutada ka alternatiivse tulemuse määramiseks. Praktiline näide: KUI töötaja A on töötanud rohkem kui 240 päeva aastas, SIIS boonus A ELSE boonus B.
Mis vahe on massiivivalemitel ja Exceli standardvalemitel?
Excelis on kõige tuntumad tavalised standardvalemid. Excelis on maatriksvalemid võimsad valemid, mille abil on võimalik teha keerukaid arvutusi. Massiivivalemi täitmiseks tuleb massiivivalem pärast selle sisestamist kinnitada klahvikombinatsiooniga "CTRL + SHIFT + ENTER". Klaviatuuri otsetee näitab Excelile, et see on massiivivalem.
