U ovom ćemo članku naučiti Kako POGLEDATI vrijednost između dva broja u Excelu.
Scenarij:
Lako je potražiti vrijednost s jednim kriterijem u tablici. Možemo jednostavno koristiti VLOOKUP. No, što bismo mogli učiniti ako se u vašim podacima podudara taj kriterij s više stupaca i potrebno je tražiti u više stupaca kako bi odgovarali vrijednosti. Naučimo kako se ovaj problem može riješiti pomoću različitih verzija pretraživanja programa Excel
LOOKUP vrijednost između dva broja
Koristeći formulu VLOOKUP
U približnom podudaranju VLOOKUP -a, ako vrijednost nije pronađena, tada se podudara posljednja vrijednost koja je manja od vrijednosti pregledavanja i vraća se vrijednost iz danog indeksa stupca.
Opća formula za vrijednost LOOKUP između dva broja:
| = VLOOKUP (vrijednost, tablica, lookup_col, 1) |
I još jedna stvar u vezi s Vlookupom je to što traži vrijednost u stupcu, a ako ne pronađe vrijednost u nizu stupaca, tada se podudara i vraća vrijednost manju od te vrijednosti u nizu tablice.
Napomena: VLOOKUP prema zadanim postavkama odgovara približno ako izostavimo varijablu traženja raspona. Približno podudaranje korisno je kada želite napraviti približno podudaranje i kada imate raspored tablica prema uzlaznom redoslijedu.
Koristeći formulu INDEX i MATCH
Formula INDEX i MATCH rade isti posao kao gore, ali s različitom sintaksom. Ali ako biste samo trebali izabrati onu s funkcijama koje vam odgovaraju
Generička formula za vrijednost LOOKUP između dva broja
| = INDEKS (opseg_potraživanja, MATCH (1, INDEX ((kriterij1 = raspon1)*(kriterij2 = raspon2),0,1),0)) |
lookup_range: raspon iz kojeg želite dohvatiti vrijednost.
Kriterij1, kriterij2, kriterij N: Ovo su kriteriji koje želite uskladiti u rasponu1, rasponu2 i rasponu N. Možete imati do 270 kriterija - parova raspona.
Raspon1, raspon2, rasponN: Ovo su rasponi u kojima ćete odgovarati vašim kriterijima.
Primjer :
Sve ovo može biti zbunjujuće za razumijevanje. Shvatimo kako koristiti funkciju na primjeru. Ovdje imamo studentsku iskaznicu i odgovarajuće ocjene na testu. Sada smo morali dobiti ocjene za svakog učenika pregledavanjem tablice sustava ocjena.
Da bismo to učinili, upotrijebit ćemo atribut funkcije VLOOKUP, a ako funkcija VLOOKUP ne pronađe točno podudaranje, traži približno podudaranje samo u tablici i samo ako je zadnji argument funkcije bilo PRAVI ili 1.
Tablica ocjena / bodova mora biti rastuća
Koristite formulu:
| = VLOOKUP (B2, tablica, 2, 1) |
Kako radi?
Funkcija Vlookup traži vrijednost 40 u stupcu oznaka i vraća odgovarajuću vrijednost, ako se podudara. Ako se ne podudara, funkcija traži manju vrijednost od vrijednosti pretraživanja (tj. 40) i vraća njezin rezultat.
Ovdje se tablica_array naziva rasponom kao stol u formuli i B2 je dan kao referenca ćelije.
Kao što vidite dobili smo ocjenu za prvog učenika. Sada ćemo dobiti sve ostale ocjene, koristit ćemo prečac Ctrl + D ili upotrijebite opciju povlačenja ćelije u Excelu.
Ovdje su sve ocjene razreda koje koriste funkciju VLOOKUP.
LOOKUP vrijednost između dva broja na tablici Employee
Imamo tablicu koja sadrži podatke o svim zaposlenicima u organizaciji na zasebnom listu. Prvi stupac sadrži ID tih zaposlenika. Ovu sam tablicu nazvao emp_data.
Sada moj list za pretraživanje mora dohvatiti podatke o zaposleniku čiji je ID zapisan u ćeliji B3. Nazvao sam B3 kao ID.
Radi lakšeg razumijevanja, svi naslovi stupaca nalaze se u potpuno istom redoslijedu kao i tablica emp_data.
Sada upišite donju formulu u ćeliju C3 da biste dohvatili zonu ID -a zaposlenika napisanu u B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Ovo će vratiti zonu zaposlenika Id 1-1830456593 jer stupac broj 2 u bazi podataka sadrži zonu zaposlenika.
Kopirajte ovu formulu u ostale ćelije i promijenite broj stupca u formuli kako biste dobili sve podatke o zaposlenicima.
Sve podatke vezane uz spomenuti ID možete vidjeti u ćeliji B3. Koji god ID upišete u ćeliju B3, svi će se podaci dohvatiti bez ikakve promjene u formuli.
Kako ovo radi?
Nema ništa zeznuto. Jednostavno koristimo funkciju VLOOKUP za traženje ID -a, a zatim dohvaćanje spomenutog stupca. Vježbajte VLOOKUP koristeći takve podatke da biste bolje razumjeli VLOOKUP.
Dohvatite podatke o zaposlenicima pomoću naslova
U gornjem primjeru imali smo sve stupce organizirane istim redoslijedom, ali bit će trenutaka kada ćete imati bazu podataka koja će imati stotine stupaca. U takvim slučajevima ova metoda dohvaćanja podataka o zaposlenicima neće biti dobra. Bit će bolje ako formula može pogledati naslov stupca i dohvatiti podatke iz tog stupca iz tablice zaposlenika.
Dakle, za dohvaćanje vrijednosti iz tablice pomoću naslova stupaca koristit ćemo dvosmjernu metodu pretraživanja ili recimo dinamički stupac VLOOKUP.
Koristite ovu formulu u ćeliji C3 i kopirajte u ostale ćelije. Ne morate ništa mijenjati u formuli, sve će se dohvatiti iz thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Ova formula jednostavno preuzima sve podatke iz podudarnih stupaca. Zaglavlja možete izmiješati u izvješću, to neće imati nikakve razlike. Koji god je naslov napisan u gornjoj ćeliji, odgovarajući podaci sadrže.
Kako ovo radi?
Ovo je jednostavno dinamički VLOOKUP. Ovdje možete pročitati o tome. Ako to ovdje objasnim, postat će prevelik članak.
Dohvatite ID zaposlenika s djelomičnim podudaranjem
Može se dogoditi da se ne sjećate cijele osobne iskaznice zaposlenika, ali ipak želite dohvatiti podatke neke osobne iskaznice. U takvim slučajevima djelomično podudaranje VLOOKUP je najbolje rješenje.
Na primjer, ako znam da neki id sadrži 2345, ali ne znam cijeli ID. Ako unesem ovaj broj u ćeliju C3, izlaz će biti poput.
Ne dobivamo ništa. Budući da se u tablici ništa ne podudara s 2345. Gornju formulu izmijenite ovako.
| =VLOOKUP("*"&iskaznica&"*", Emp_Data,UTAKMICA(C2, Zaglavlji_prazni_podaci, 0), 0) |
Kopirajte ovo u cijeli red. A sada imate podatke o prvom zaposleniku koji sadrže ovaj broj.
Imajte na umu da ćemo dobiti prvi ID koji sadrži odgovarajući broj u stupcu Emp Id. Ako bilo koji drugi ID sadrži isti broj, ova formula neće dohvatiti podatke o tom zaposleniku.
Ako želite dobiti sve ID -ove zaposlenika koji sadrže isti broj, upotrijebite formulu koja traži sve podudarne vrijednosti.
Koristeći formulu INDEX i MATCH
Ovdje imamo tablicu podataka. Želim povući ime kupca koristeći datum rezervacije, graditelja i područje. Dakle, ovdje imam tri kriterija i jedan raspon pretraživanja.
Upišite ovu formulu u ćeliju I4 pritisnite enter.
| = INDEKS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Kako radi:
Već znamo kako funkcije INDEX i MATCH rade u EXCEL -u, pa to neću ovdje objašnjavati. Govorit ćemo o triku koji smo ovdje upotrijebili.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Glavni dio je ovo. Svaki dio ove izjave vraća niz true false.
Kad se logičke vrijednosti pomnože, vraćaju niz od 0 i 1. Množenje funkcionira kao operator AND. Dakle, kad su sve vrijednosti istinite, tada vraća 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Ovo će se ukupno vratiti
| {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} |
Što će se prevesti u
| {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 vratit će isti niz ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) u MATCH funkciju kao niz za pretraživanje.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH funkcija će tražiti 1 u nizu {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. I vratit će indeksni broj prvih 1 pronađenih u nizu. Ovdje je 8.
INDEKS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Konačno, INDEX će se vratiti vrijednost iz zadanog raspona (E2: E16) pri pronađenom indeksu (8).
Ovdje su sve opservacijske bilješke korištene formulom u Excelu
Bilješke:
- Stupac tablice ocjena / bodova mora biti u rastućem redoslijedu, inače funkcija može dati pogrešan rezultat.
- Funkcija VLOOKUP traži vrijednost u prvom retku tablice_ matrice i izdvaja odgovarajuće vrijednosti samo desno od raspona_pregleda.
- Zadnji argument funkcije VLOOKUP funkcije mora biti postavljen na TRUE ili 1 kako bi se dobilo približno podudaranje.
- Funkcija VLOOKUP vraća pogrešku ako je adresa radne knjige neispravna ili pogrešna.
- Funkcija VLOOKUP vraća pogrešku ako se vrijednost ne podudara.
Nadam se da je ovaj članak o tome kako tražiti vrijednost između dva broja u Excelu objašnjen. Ovdje pronađite više članaka o izračunavanju vrijednosti i srodnih Excel formula. Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte.
Način korištenja funkcije Vlookup u provjeri valjanosti podataka : Ograničite korisnike da dopuštaju vrijednosti iz tablice za pretraživanje pomoću okvira formule za provjeru valjanosti podataka u Excelu. Okvir formule u provjeri valjanosti podataka omogućuje vam da odaberete vrstu potrebnog ograničenja.
Kako dohvatiti najnoviju cijenu u Excelu : Uobičajeno je ažurirati cijene u bilo kojem poslu, a korištenje najnovijih cijena za bilo koju kupnju ili prodaju je neophodno. Za dohvaćanje najnovije cijene s popisa u Excelu koristimo funkciju LOOKUP. Funkcija LOOKUP postiže najnoviju cijenu.
VLOOKUP funkcija za izračun ocjene u Excelu : Za izračun ocjena IF i IFS nisu jedine funkcije koje možete koristiti. VLOOKUP je učinkovitiji i dinamičniji za takve uvjetne izračune. Za izračun ocjena pomoću VLOOKUP -a možemo koristiti ovu formulu.
17 stvari o Excelu VLOOKUP : VLOOKUP se najčešće koristi za dohvaćanje usklađenih vrijednosti, ali VLOOKUP može učiniti puno više od ovoga. Evo 17 stvari o VLOOKUP -u koje biste trebali znati koristiti učinkovito.
POGLEDAJTE prvi tekst sa popisa u Excelu : Funkcija VLOOKUP dobro radi s zamjenskim znakovima. To možemo koristiti za izdvajanje prve tekstualne vrijednosti s danog popisa u Excelu. Evo opće formule.
LOOKUP datum s posljednjom vrijednošću na popisu : Za dohvaćanje datuma koji sadrži zadnju vrijednost koristimo funkciju LOOKUP. Ova funkcija provjerava ima li ćelija koja sadrži posljednju vrijednost u vektoru, a zatim koristi tu referencu za vraćanje datuma.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti : Ubrzajte svoje zadatke u Excelu. Ovi prečaci pomoći će vam u povećanju radne učinkovitosti u Excelu.
Kako koristiti funkciju VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti IF funkciju u Excelu : IF izraz u Excelu provjerava uvjet i vraća određenu vrijednost ako je uvjet TRUE ili vraća drugu određenu vrijednost ako je FALSE.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.