Selles artiklis õpime, kuidas otsida väärtust kahe numbri vahel Excelis.
Stsenaarium:
Tabelis on lihtne ühest kriteeriumist väärtust otsida. Saame lihtsalt kasutada VLOOKUP -i. Aga mida me saaksime teha, kui see mitme veeru kriteerium sobiks teie andmetega ja oleks vaja väärtuse leidmiseks otsida mitmest veerust. Õpime, kuidas seda probleemi saab lahendada, kasutades erinevaid Exceli otsinguvariante
LOOKUP väärtus kahe numbri vahel
VLOOKUP valemi kasutamine
Kui väärtust VLOOKUP ligikaudse vaste korral ei leita, sobitatakse viimane otsinguväärtusest väiksem väärtus ja tagastatakse antud veeruindeksi väärtus.
Üldvalem LOOKUP väärtusele kahe numbri vahel:
| = VLOOKUP (väärtus, tabel, lookup_col, 1) |
Ja veel üks asi Vlookupi kohta on see, et see otsib veerust väärtust ja kui see ei leia veerumassist väärtust, siis see vastab ja tagastab väärtuse, mis on väiksem kui see tabelimassiivi väärtus.
Märkus. VLOOKUP teeb vaikimisi ligikaudse vaste, kui jätame vahemiku otsingumuutuja välja. Ligikaudne vaste on kasulik, kui soovite ligikaudse vaste teha ja kui teie tabelimassiiv on järjestatud kasvavas järjekorras.
Kasutades valemit INDEX ja MATCH
INDEX ja MATCH valem teeb sama tööd nagu eespool, kuid erineva süntaksiga. Aga kui peaksite valima ainult selle, mille funktsioonidega olete rahul
Üldvalem LOOKUP väärtuseks kahe numbri vahel
| = INDEX (lookup_range, MATCH (1, INDEX ((kriteerium1 = vahemik1)*(kriteerium2 = vahemik2),0,1),0)) |
lookup_range: vahemik, millest soovite väärtust hankida.
Kriteeriumid 1, kriteeriumid 2, kriteeriumid N: need on kriteeriumid, mille soovite vahemikus 1, vahemikus2 ja vahemikus N sobitada. Teil võib olla kuni 270 kriteeriumi - vahemiku paarid.
Vahemik1, vahemik2, vahemik N: need on vahemikud, milles vastate oma vastavatele kriteeriumidele.
Näide:
Kõiki neid võib segadusse ajada. Mõistame, kuidas funktsiooni kasutada näite abil. Siin on meil õpilase ID ja nende vastavad märgid testis. Nüüd pidime hinde süsteemitabelisse otsides iga õpilase hinded hankima.
Selleks kasutame funktsiooni VLOOKUP atribuuti, mis on siis, kui funktsioon VLOOKUP ei leia täpset vastet, mida ta otsib ainult tabelist, ja ainult siis, kui funktsiooni viimane argument on kas TÕSI või 1.
Märkide / tulemuste tabel peab olema kasvavas järjekorras
Kasutage valemit:
| = VLOOKUP (B2, tabel, 2, 1) |
Kuidas see töötab?
Funktsioon Vlookup otsib märkide veerust väärtuse 40 ja tagastab vastava väärtuse, kui see on vastavuses. Kui see ei ühti, otsib funktsioon otsimisväärtusest väiksemat väärtust (st 40) ja tagastab oma tulemuse.
Siin on tabeli_massiivi nimeks vahemik tabel valemis ja B2 on antud lahtri viitena.
Nagu näete, saime esimese õpilase hinde. Nüüd kasutame kõigi teiste klasside hankimiseks otseteed Ctrl + D või kasutage Excelis lahtri lohistamise võimalust.
Siin on kõik klassi hinded, mis kasutavad funktsiooni VLOOKUP.
LOOKUP väärtus kahe tabeli töötajate vahel
Meil on tabel, mis sisaldab organisatsiooni kõigi töötajate andmeid eraldi lehel. Esimene veerg sisaldab nende töötajate ID -d. Panin selle tabeli nimeks emp_data.
Nüüd peab minu otsinguleht tooma töötaja andmed, kelle ID on kirjutatud lahtrisse B3. Panin ID -ks nimeks B3.
Mõistmise hõlbustamiseks on kõik veerupäised täpselt samas järjekorras kui tabel emp_data.
Nüüd kirjutage lahtrisse C3 järgmine valem, et hankida B3 -s kirjutatud töötaja ID tsoon.
| = VLOOKUP (ID, Emp_Data, 2,0) |
See tagastab töötaja ID tsooni 1-1830456593, kuna andmebaasi veerg 2 sisaldab töötajate tsooni.
Kopeerige see valem ülejäänud lahtritesse ja muutke valemi veerunumbrit, et saada kogu töötajate teave.
Kogu mainitud ID -ga seotud teavet näete lahtris B3. Ükskõik, millise ID lahtrisse B3 kirjutate, tuuakse kogu teave valemit muutmata.
Kuidas see töötab?
Pole midagi keerulist. Me kasutame lihtsalt funktsiooni VLOOKUP ID otsimiseks ja seejärel nimetatud veeru toomiseks. Harjutage VLOOKUP -i selliste andmete abil, et VLOOKUP -ist rohkem aru saada.
Tooge töötajate andmed rubriikide abil
Ülaltoodud näites korraldasime kõik veerud samas järjekorras, kuid mõnikord on teil andmebaas, mis sisaldab sadu veerge. Sellistel juhtudel ei ole see töötajate teabe hankimise meetod hea. Parem on, kui valem saab vaadata veeru pealkirja ja tuua selle veeru andmed töötajate tabelist.
Seega kasutame tabeli väärtuse leidmiseks veeru pealkirja abil kahesuunalist otsingumeetodit või ütleme dünaamilist veergu VLOOKUP.
Kasutage seda valemit lahtris C3 ja kopeerige ülejäänud lahtritesse. Te ei pea valemis midagi muutma, kõik tuuakse thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
See valem lihtsalt otsib kogu teabe sobitatud veergudest. Saate aruande päised segi ajada, see ei muuda midagi. Ükskõik milline pealkiri on ülaltoodud lahtrisse kirjutatud, sisaldavad vastavad andmed.
Kuidas see töötab?
See on lihtsalt dünaamiline VLOOKUP. Selle kohta saate lugeda siit. Kui ma seda siin selgitan, saab sellest liiga suur artikkel.
Osalise vastega töötaja ID toomine
Võib juhtuda, et te ei mäleta kogu töötaja isikutunnistust, kuid soovite siiski mõne isikutunnistuse teavet hankida. Sellistel juhtudel on parim lahendus osaline vaste VLOOKUP.
Näiteks kui ma tean, et mõni ID sisaldab 2345, kuid ma ei tea kogu ID -d. Kui sisestan selle numbri lahtrisse C3, on väljund sarnane.
Me ei saa midagi. Kuna tabelis ei vasta miski 2345 -le. Muutke ülaltoodud valemit nii.
| =VLOOKUP("*"&ID&"*", Emp_Data,MATCH(C2, Emp_Data_Headers, 0), 0) |
Kopeerige see kogu reale. Ja nüüd on teil selle numbri sisaldava esimese töötaja andmed.
Pange tähele, et saame esimese ID, mis sisaldab veerus Emp Id vastavat numbrit. Kui mõni muu ID sisaldab sama numbrit, ei tooda see valem selle töötaja andmeid.
Kui soovite saada kõiki sama numbrit sisaldavaid töötajate ID -sid, kasutage valemit, mis otsib üles kõik sobitatud väärtused.
Kasutades valemit INDEX ja MATCH
Siin on meil andmetabel. Soovin broneeringu kuupäeva, ehitaja ja piirkonna abil kasutada kliendi nime. Siin on mul kolm kriteeriumi ja üks otsinguvahemik.
Kirjutage see valem lahtrisse I4, vajutades sisestusklahvi.
| = INDEKS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Kuidas see töötab:
Me juba teame, kuidas INDEX ja MATCH funktsioon EXCELis töötavad, nii et ma ei hakka seda siin selgitama. Me räägime trikkist, mida me siin kasutasime.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Põhiosa on see. Selle väite iga osa tagastab massiivi tõene vale.
Kui tõeväärtused korrutatakse, tagastavad nad massiivi 0 ja 1. Korrutamine toimib JA -operaatorina. Raske, kui kõik väärtused on tõesed, siis tagastab see 1 muu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) See tuleb tagasi
| {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} |
Mis tõlgitakse
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funktsioon INDEX tagastab sama massiivi ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) funktsiooni MATCH otsimismassiivina.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funktsioon MATCH otsib massiivist {0; 0; 0 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Ja tagastab massiivist leitud esimese 1 indeksi numbri. Siin on 8.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Lõpuks naaseb INDEX väärtus antud vahemikust (E2: E16) leitud indeksi (8) juures.
Siin on kõik vaatlusmärkused, kasutades Exceli valemit
Märkused:
- Märkide / tulemuste tabeli veerg peab olema kasvavas järjekorras, vastasel juhul võib funktsioon anda vale tulemuse.
- Funktsioon VLOOKUP otsib väärtuse tabeli_massiivi esimeselt realt ja ekstraheerib vastavad väärtused ainult otsingu_vahemikust.
- Funktsiooni VLOOKUP funktsiooni viimane argument peab olema määratud kas TRUE või 1, et saada ligikaudne vaste.
- Funktsioon VLOOKUP tagastab vea, kui töövihiku aadress on vale või vale.
- Funktsioon VLOOKUP tagastab vea, kui väärtus ei sobi.
Loodetavasti on see artikkel, kuidas Excelis kahe numbri vahel väärtust otsida, selgitav. Siit leiate rohkem artikleid väärtuste arvutamise ja sellega seotud Exceli valemite kohta. Kui teile meeldisid meie ajaveebid, jagage neid oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile.
Vlookup -funktsiooni kasutamise viis andmete valideerimisel : Piirake kasutajatel otsingutabeli väärtuste lubamist, kasutades Exceli Exceli andmete valideerimise valemit. Valemikast andmete valideerimisel võimaldab teil valida vajaliku piirangu tüübi.
Kuidas Excelis uusimat hinda hankida : Igas ettevõttes on tavaline hindu värskendada ja iga ostu või müügi puhul tuleb kasutada uusimaid hindu. Viimase hinna hankimiseks Exceli loendist kasutame funktsiooni LOOKUP. Funktsioon LOOKUP toob viimase hinna.
Funktsioon VLOOKUP Exceli hinde arvutamiseks : Klasside arvutamiseks ei ole IF ja IFS ainsad funktsioonid, mida saate kasutada. VLOOKUP on selliste tingimuslike arvutuste jaoks tõhusam ja dünaamilisem. Hinnete arvutamiseks VLOOKUPi abil saame kasutada seda valemit.
17 asja Exceli kohta VLOOKUP : VLOOKUP -i kasutatakse kõige sagedamini sobitatud väärtuste toomiseks, kuid VLOOKUP saab teha palju enamat. Siin on 17 asja VLOOKUPi kohta, mida peaksite tõhusaks kasutamiseks teadma.
Otsi üles esimene tekst loendist Excelis : Funktsioon VLOOKUP töötab metamärkidega hästi. Me saame seda kasutada Exceli antud loendist esimese tekstiväärtuse eraldamiseks. Siin on üldine valem.
OTSINGU kuupäev loendis viimase väärtusega : Viimast väärtust sisaldava kuupäeva toomiseks kasutame funktsiooni LOOKUP. See funktsioon otsib lahtrit, mis sisaldab vektori viimast väärtust, ja kasutab seejärel seda viidet kuupäeva tagastamiseks.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks : Saate Excelis oma ülesannetega kiiremini hakkama. Need otseteed aitavad teil Excelis oma töö efektiivsust suurendada.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuste otsimiseks.
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.