V tem članku se bomo naučili, kako poiskati vrednost med dvema številkama v Excelu.
Scenarij:
Enostavno je poiskati vrednost z enim merilom v tabeli. Lahko preprosto uporabimo VLOOKUP. Kaj pa bi lahko storili, če bi se v vaših podatkih ujemalo to merilo za več stolpcev in bi morali iskati v več stolpcih, da bi se ujemali z vrednostjo. Naučimo se, kako lahko to težavo rešimo z različnimi iskalnimi različicami programa Excel
LOOKUP vrednost med dvema številkama
Z uporabo formule VLOOKUP
V približnem ujemanju VLOOKUP, če vrednost ni najdena, se ujema zadnja vrednost, ki je manjša od iskalne vrednosti, in vrne se vrednost iz podanega indeksa stolpca.
Splošna formula za vrednost LOOKUP med dvema številkama:
| = VLOOKUP (vrednost, tabela, iskanje_col, 1) |
In še ena stvar pri Vlookupu je, da išče vrednost v stolpcu in če ne najde vrednosti v matriki stolpcev, se potem ujema in vrne vrednost, ki je manjša od te vrednosti v matriki tabel.
Opomba: VLOOKUP privzeto ustreza približnemu ujemanju, če izpustimo spremenljivko za iskanje obsega. Približno ujemanje je uporabno, če želite narediti približno ujemanje in če imate matriko tabel razvrščeno po naraščajočem vrstnem redu.
Z uporabo formule INDEX in MATCH
Formula INDEX in MATCH delujeta enako kot zgoraj, vendar z različno skladnjo. Če pa izberete samo tisto, s katerimi funkcijami vam ustreza
Splošna formula za vrednost LOOKUP med dvema številkama
| = INDEX (lookup_range, MATCH (1, INDEX ((merilo1 = obseg1)*(kriteriji2 = obseg2),0,1),0)) |
lookup_range: obseg, iz katerega želite pridobiti vrednost.
Merila1, Merila2, Merila N: To so merila, ki jih želite ujemati v razponu1, obsegu2 in obsegu N. Lahko imate do 270 meril - parov območij.
Razpon 1, obseg2, obsegN: To so obsegi, v katerih boste ustrezali vašim kriterijem.
Primer:
Vse to je lahko zmedeno za razumevanje. Poglejmo, kako uporabiti funkcijo na primeru. Tu imamo študentsko izkaznico in ustrezne ocene v testu. Zdaj smo morali dobiti ocene za vsakega učenca, tako da smo poiskali tabelo sistemov ocen.
V ta namen bomo uporabili atribut funkcije VLOOKUP, ki je, če funkcija VLOOKUP ne najde natančnega ujemanja, išče le približno ujemanje v tabeli in le, če je zadnji argument funkcije bodisi PRAV ali 1.
Tabela z ocenami in ocenami mora biti v naraščajočem vrstnem redu
Uporabite formulo:
| = VLOOKUP (B2, tabela, 2, 1) |
Kako deluje?
Funkcija Vlookup poišče vrednost 40 v stolpcu oznak in vrne ustrezno vrednost, če se ujema. Če se ne ujema, funkcija išče manjšo vrednost od iskalne vrednosti (tj. 40) in vrne njen rezultat.
Tu je table_array poimenovan obseg kot miza v formuli in je B2 referenčna celica.
Kot vidite, smo dobili oceno za prvega učenca. Zdaj, da dobimo vse druge stopnje, bomo uporabili bližnjico Ctrl + D ali uporabite možnost povleci celico navzdol v Excelu.
Tu so vse stopnje razreda, ki uporabljajo funkcijo VLOOKUP.
Vrednost LOOKUP med dvema številkama v tabeli zaposlenih
Imamo tabelo, ki vsebuje podatke o vseh zaposlenih v organizaciji na ločenem listu. Prvi stolpec vsebuje ID teh zaposlenih. To tabelo sem poimenoval emp_data.
Zdaj mora moj iskalni list pridobiti podatke o zaposlenem, katerega ID je zapisan v celici B3. B3 sem poimenoval kot ID.
Zaradi lažjega razumevanja so vsi naslovi stolpcev v popolnoma enakem vrstnem redu kot tabela emp_data.
Zdaj v celico C3 napišite spodnjo formulo, da pridobite območje ID -ja zaposlenega, zapisano v B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
S tem se vrne cona zaposlenega Id 1-1830456593, ker stolpec številka 2 v zbirki podatkov vsebuje območje zaposlenih.
Kopirajte to formulo v preostale celice in spremenite številko stolpca v formuli, da dobite vse podatke o zaposlenih.
Vse podatke v zvezi z omenjenim ID -jem si lahko ogledate v celici B3. Ne glede na to, kateri ID vnesete v celico B3, bodo vse informacije pridobljene, ne da bi pri tem spremenili formulo.
Kako to deluje?
Ni nič zapletenega. Preprosto uporabljamo funkcijo VLOOKUP, da poiščemo ID in nato pridobimo omenjeni stolpec. Če želite bolje razumeti VLOOKUP, vadite VLOOKUP z uporabo teh podatkov.
Pridobite podatke o zaposlenih z naslovi
V zgornjem primeru smo imeli vse stolpce organizirane v istem vrstnem redu, vendar bodo včasih imeli bazo podatkov, ki bo imela na stotine stolpcev. V takih primerih ta način pridobivanja podatkov o zaposlenih ne bo dober. Bolje bi bilo, če bi formula lahko pogledala naslov stolpca in pridobila podatke iz tega stolpca iz tabele zaposlenih.
Za pridobitev vrednosti iz tabele z naslovom stolpca bomo uporabili dvosmerno metodo iskanja ali recimo dinamični stolpec VLOOKUP.
Uporabite to formulo v celici C3 in kopirajte v preostale celice. V formuli vam ni treba ničesar spreminjati, vse bo pridobljeno iz thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Ta formula preprosto pridobi vse podatke iz ujemajočih se stolpcev. V poročilu lahko zmešate glave, to ne bo spremenilo. Ne glede na to, ali je naslov zapisan v zgornji celici, ustrezni podatki vsebujejo.
Kako to deluje?
To je preprosto dinamičen VLOOKUP. O tem lahko preberete tukaj. Če bom razložil tukaj, bo postal prevelik članek.
Pridobite ID zaposlenega z delnim ujemanjem
Lahko se zgodi, da se ne spomnite celotnega ID -ja zaposlenega, a vseeno želite pridobiti podatke o nekem ID -ju. V takih primerih je delna ujemanje VLOOKUP najboljša rešitev.
Na primer, če vem, da neki ID vsebuje 2345, vendar ne poznam celotnega ID -ja. Če vnesem to številko v celico C3, bo izpis podoben.
Ne dobimo nič. Ker se v tabeli nič ne ujema z 2345. Zgornjo formulo spremenite tako.
| =VLOOKUP("*"&ID&"*", Emp_Data,UJEMA(C2, Emp_Data_Headers, 0), 0) |
Kopirajte to v celo vrstico. In zdaj imate podatke o prvem zaposlenem, ki vsebuje to številko.
Upoštevajte, da bomo v stolpcu Emp Id dobili prvi ID, ki vsebuje ujemajočo se številko. Če kateri koli drug ID vsebuje isto številko, ta formula ne bo pridobila podatkov o tem zaposlenem.
Če želite pridobiti vse ID -je zaposlenih, ki vsebujejo isto številko, uporabite formulo, ki poišče vse ujemajoče se vrednosti.
Z uporabo formule INDEX in MATCH
Tukaj imamo podatkovno tabelo. Želim potegniti ime stranke z datumom rezervacije, graditeljem in površino. Tu imam torej tri kriterije in en obseg iskanja.
To formulo zapišite v celico I4, pritisnite Enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Kako deluje:
Že vemo, kako funkciji INDEX in MATCH delujeta v EXCEL -u, zato tega ne bom razlagal tukaj. Govorili bomo o triku, ki smo ga uporabili tukaj.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Glavni del je to. Vsak del te izjave vrne polje true false.
Ko se logične vrednosti pomnožijo, vrnejo matriko 0 in 1. Množenje deluje kot operator AND. Torej, ko so vse vrednosti resnične, potem vrne 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) To se bo skupaj vrnilo
| {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} |
Kar se bo prevedlo v
| {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): Funkcija INDEX vrne isto matriko ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) funkciji MATCH kot iskalna matrika.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): funkcija MATCH bo iskala 1 v matriki {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. In vrne indeksno številko prve 1, najdene v matriki. To je 8 tukaj.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Nazadnje se bo INDEX vrnil vrednost iz danega območja (E2: E16) pri najdenem indeksu (8).
Tu so vse opombe, ki uporabljajo formulo v Excelu
Opombe:
- Stolpec tabel oznak / rezultatov mora biti v naraščajočem vrstnem redu, sicer lahko funkcija povzroči napačen rezultat.
- Funkcija VLOOKUP poišče vrednost v prvi vrstici matrike_tabel in izvleče ustrezne vrednosti le desno od območja iskanja.
- Zadnji argument funkcije VLOOKUP mora biti nastavljen na TRUE ali 1, da dobimo približno ujemanje.
- Funkcija VLOOKUP vrne napako, če je naslov delovnega zvezka neveljaven ali napačen.
- Funkcija VLOOKUP vrne napako, če se vrednost ne ujema.
Upam, da je ta članek o tem, kako poiskati vrednost med dvema številkama v Excelu, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Način uporabe funkcije Vlookup pri preverjanju podatkov : Omejite uporabnike, da dovolijo vrednosti iz iskalne tabele z uporabo formule za preverjanje veljavnosti podatkov v Excelu. Polje formule pri preverjanju podatkov vam omogoča, da izberete vrsto zahtevane omejitve.
Kako pridobiti zadnjo ceno v Excelu : Običajno je posodobiti cene v katerem koli poslu in uporaba najnovejših cen pri nakupu ali prodaji je nujna. Za pridobitev najnovejše cene s seznama v Excelu uporabimo funkcijo LOOKUP. Funkcija LOOKUP doseže najnovejšo ceno.
Funkcija VLOOKUP za izračun ocene v Excelu : Za izračun ocen IF in IFS nista edini funkciji, ki jo lahko uporabite. VLOOKUP je za take pogojne izračune učinkovitejši in dinamičnejši. Za izračun ocen z uporabo VLOOKUP -a lahko uporabimo to formulo.
17 stvari o Excelu VLOOKUP : VLOOKUP se najpogosteje uporablja za pridobivanje ujemajočih se vrednosti, vendar lahko VLOOKUP naredi veliko več kot to. Tukaj je 17 stvari o VLOOKUP -u, ki jih morate vedeti za učinkovito uporabo.
POGLEDAJTE prvo besedilo s seznama v Excelu : Funkcija VLOOKUP dobro deluje z nadomestnimi znaki. To lahko uporabimo za ekstrahiranje prve besedilne vrednosti z danega seznama v Excelu. Tu je splošna formula.
LOOKUP datum z zadnjo vrednostjo na seznamu : Za pridobitev datuma, ki vsebuje zadnjo vrednost, uporabimo funkcijo LOOKUP. Ta funkcija preveri celico, ki vsebuje zadnjo vrednost v vektorju, in nato uporabi to referenco za vrnitev datuma.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost : Pospešite svoje naloge v Excelu. Te bližnjice vam bodo pomagale povečati učinkovitost dela v Excelu.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.