Kako koristiti VLOOKUP Excel? Excel funkcija u Excelu za lutke i ne samo

Svaka osoba koja se bavi brojevima, bez obzira radi li se o profesionalnom interesu ili jednostavno za ljubav prema umjetnosti, naravno, prva stvar je ovladati kalkulatorom. Što je sljedeće? Slijedi genijalni program za rad s brojevima - Excel. Vjerojatno je nemoguće savršeno poznavati ovaj program, kao i savršeno poznavati matematiku. Međutim, postoje osnovne značajke programa, Excelova funkcija, znajući da s 90% povjerenja možemo govoriti o sposobnosti rada s podacima na razini pouzdanog korisnika. Jedna od najvažnijih funkcija u Excelu je VLOOKUP, koji je vrlo svestran. U članku je opisano kako koristiti VLOOKUP u Excelu (za lutke). Naravno, percepcija teorije neće donijeti potpuno razumijevanje procesa korištenja funkcije. Prvo je potrebno vježbati na malim nizovima podataka, a zatim na tablicama bilo koje veličine.

Suština i svrha CDF funkcije u Excelu

Funkcija CDF štedi mnogo vremena stručnjacima kao što su ekonomisti, financijeri, računovođe, tehnologi. Opis funkcije VLOOKUP u Excelu i primjer njegove primjene treba započeti s obzirom na situaciju s kojom se često susreću stručnjaci različitih vrsta u svom radu. Ako postoje dvije tablice u kojima se pojavljuju isti podaci koji se mogu definirati (na primjer, nazivi robe), ali definicije za njih u različitim tablicama razlikuju se, na primjer, u jednoj postoji količina, au drugoj cijena. Istovremeno, zadatak je kombinirati sve te podatke i izvršiti odgovarajuće izračune - u takvim situacijama spašava CDF funkcija. Kako bi razumjeli kako VLOOKUP radi u Excelu, u nastavku su prikazane dvije tablice. U svakom od njih nalazi se šifra proizvoda. U jednoj postoji odgovarajuća količina, u drugoj - cijena. Za zamjenu cijena u prvoj tablici i korišten CDF.

Ručni zamjena podataka između tablica je ogroman gubitak vremena i truda zaposlenika, pogreške se ne mogu izbjeći. Uz pomoć CDF-a možete pronaći odgovarajuće vrijednosti brzo i jednostavno. Za duboko razumijevanje kako koristiti VLOOKUP u Excelu, potrebno je analizirati sve argumente funkcije.

Argumenti funkcije

Da biste koristili funkciju, potrebno je stajati u ćeliji rezultata, na kartici odaberite karticu Formule - "Veze i polja" - "CDF". U ćeliji se pojavio natpis "= VLR", a sada morate ispravno unijeti argumente funkcije.To možete učiniti preko točke sa zarezom izravno u traci s formulama.

CDF funkcija ima četiri argumenta - vrijednost koju tražite, tablicu, broj stupca, traženje intervala. Prva tri, označena masnim slovima u dijaloškom okviru, su obavezna. Objasnit ćemo koji su argumenti funkcije:

• tražena vrijednost je ono što treba tražiti;
• stol - gdje gledati;
• broj stupca - u kojem stupcu tražiti;
• intervalni pregled - razvrstan.

Argument "tražena vrijednost"

U prvom polju morate unijeti ono što trebate za pretraživanje. Može postojati broj, tekst, datum. Možete unijeti apsolutnu vrijednost, možete postaviti referencu ćelije na željenu vrijednost. Tražena vrijednost teoretski bi trebala biti prisutna u obje tablice. U gornjem primjeru takva vrijednost može biti šifra proizvoda. Kako bi se izbjegle pogreške, bolje je koristiti link.

Kada tekst koristite kao željenu vrijednost, on mora biti u navodnicima. Kada se odnosi na ćeliju s tekstom, navodnici nisu potrebni. Registrirajte se pri unosu teksta nije važno. Može se koristiti neizrazito pretraživanje, temeljeno na fragmentu teksta. Da biste to učinili, unutar navodnika morate priložiti dio teksta u znakovima *.

Najčešći problem pri korištenju funkcije je različit format ćelija u rasponima. Ako u polju prvog argumenta stavite vezu na ćeliju u kojoj su podaci u tekstualnom formatu, a u rasponu pretraživanja podaci će biti u numeričkom formatu, neće pronaći podudarnost s vanjskim identitetom CDF vrijednosti.

Pretvaranje u numerički format je jednostavno - morate množiti polje za jedan.

Argument tablice

Ovdje trebate unijeti raspon u kojem će funkcija tražiti prvu unesenu vrijednost. Raspon za pronalaženje vrijednosti bit će prvi stupac odabranog područja. Vrijednost koju želite pronaći i postaviti kao rezultat mora biti u stupcu desno od stupca za pretraživanje podudaranja. To je jedan od ključnih nedostataka korištenja CDF-a: da bi se moglo raditi s njim, tablica se često mora ponovno izgraditi kako bi se traženi podaci nalazili na lijevoj strani. Raspon se može unijeti i ručno i referentno. U primjeru izgleda ovako:

Raspon ovdje je pravi stol. Tražena vrijednost u stupcu desno, gdje će Excel tražiti vrijednost 3187849428, vrijednost koju treba pronaći i zamijeniti kao rezultat formule nalazi se s lijeve strane. Da biste dobili točan rezultat, bolje je popraviti raspon, odabrati ga i pritisnuti tipku F4, veza na polje će postati apsolutna.

Argument "broj stupca"

Ovdje je potrebno staviti znamenku u kojoj je u stupcu stupac, od krajnje lijevog, potrebno uzeti vrijednost za zamjenu kao rezultat računa. U gornjem primjeru, ovo je drugi stupac, u nizu argumenata morate staviti broj 2. Ako između stupaca Code i Price postoji još jedan stupac, tada biste trebali staviti broj 3 i tako dalje.

Argument za pregled intervala

Popunjavanje ovog polja nije obavezno, ali može biti vrlo važno. Može postojati jedna od dvije vrijednosti - 1 (true) ili 0 (false). Većina korisnika smatra da je funkcija ovog argumenta odrediti točnost podudaranja željenih vrijednosti. To nije posve točno. Kada određujete jedinicu, ako postoje ponavljanja u rasponu tablice, funkcija će vratiti zadnju pronađenu vrijednost. U tom slučaju, funkcija VLR će uzeti u obzir sve vrijednosti manje ili jednake onima unesenim u polje "Potrebna vrijednost". Ako funkcija pronađe veću vrijednost, ali ne pronađe manju ili jednaku vrijednost, generirat će pogrešku N / A.

Možete vidjeti da je broj 3187849425 unesen u polje "Vrijednost", ne postoji takva vrijednost u željenom rasponu i program, pronalazeći sve vrijednosti manje od ili jednake željenom, vraća vrijednost koja odgovara zadnjem prikladnom kodu na popisu - 3187848593, čija je cijena 2.479,46 rubalja. Ako polje za prikaz intervala ostane prazno, funkcija će raditi na isti način kao i vrijednost jedinice.

Kada se vrijednost 0 unese u polje argumenta, funkcija će vratiti samo vrijednost koja odgovara traženoj. Ako postoji ponavljanje u rasponu pretraživanja, funkcija će uzeti prvo podudaranje. Kada se koristi formula s argumentom 0, funkcija radi mnogo dulje, ali mnogo preciznije.

Značajke uporabe CDF-a u nekoliko uvjeta

Korištenje CDF funkcije često je kreativni proces koji zahtijeva matematičko razmišljanje od korisnika. Često postoji potreba za pronalaženjem podudarnosti ne za jedan stupac, već za dvije ili čak više. Pomoću dodatnih radnji može se primijeniti i funkcija VLOOKUP. Potrebno je stvoriti dodatne stupce u obje tablice, gdje se kombiniraju podaci iz razmatranih stupaca. To možete učiniti pomoću funkcije "CLUTCH" ili "&".

Kako koristiti CDF funkciju, ako su podaci na različitim listovima

Kako koristiti VLOOKUP u Excelu na različitim listovima, u različitim datotekama? Često korisnici stvaraju neke referentne knjige s kojima je potrebno usporediti i pronaći podudarnosti. Razlike u radu u takvim uvjetima sastoje se u nešto drugačijem obliku polja "Tablica" u prozoru funkcije argumenti. Prije ulaska u raspon potrebno je upisati broj lista (ako su podaci u jednoj knjizi) ili naziv datoteke (ako podaci nisu u jednoj knjizi). Ako se u gornjem primjeru cjenik kopira u zasebnu datoteku, nazovite ga "Cijena" i, koristeći funkciju CDF, potražite cijene tamo, to će izgledati ovako:

Sve gore navedene radnje mogu se obaviti putem veze. Morate staviti pokazivač u polje "Tablica", prijeći na željenu datoteku i mišem odabrati raspon. Ponekad se dogodi da tablica nije umetnuta kao veza u prozoru. Zatim trebate učiniti sljedeće: otvorite izvornu tablicu, gdje trebate napraviti izračune, a zatim kroz izbornik "Datoteka" - "Otvori" pronađite drugu tablicu. Datoteke otvorene na ovaj način rade glatko.

Sve gore navedene radnje mogu se obaviti putem veze. Morate staviti pokazivač u polje "Tablica", prijeći na željenu datoteku i mišem odabrati raspon. Ponekad se dogodi da tablica nije umetnuta kao veza u prozoru. Zatim trebate učiniti sljedeće: otvorite izvornu tablicu, gdje trebate napraviti izračune, a zatim kroz izbornik "Datoteka" - "Otvori" pronađite drugu tablicu. Datoteke otvorene na ovaj način rade glatko.

Izvagati datoteku u kojoj se nalaze formule s CDF-om, s referencama na druge datoteke bit će mnogo više nego bez njih. To može biti problematično, na primjer, prijenos datoteka. Kako biste izbjegli ove probleme, morate pretvoriti formulu u vrijednosti. Na vrpci odaberite podizbornik "Podaci" i naredbu "Izmijeni komunikaciju". Čak i ako podaci nisu uzeti iz druge datoteke, uvijek je korisno zamijeniti formule vrijednostima - to čini izračune pouzdanije.

Padajući popis za olakšavanje rada s CDF-om

Često VLR funkcija ne radi ako postoje manje nepodudarnosti u podacima. Taj dodatni prostor u tekstu, podaci uneseni s pogreškama. Da biste izbjegli sve te poteškoće, za unos vrijednosti koristite padajući popis u Excelu. Ima smisla pokrenuti ga kada radite s stalno ponavljajućim podacima. Ako postoji određena referentna knjiga koja se koristi kao tablica za usporedbu, tada se raspon usporedbe može uzeti kao podatak za padajući popis i koristi se za formiranje tablice, u koju će se podaci zatim umetnuti u CDF funkciju. Pokazivač se nalazi u ćeliji u koju trebate unijeti vrijednost. Nadalje, na vrpci se nalazi pododjeljak "Podaci", odabrana je naredba "Provjeri podatke". U dijaloški okvir, u polje "Vrsta podataka" unesite vrijednost "Popis". U polju "Izvor" bilježi se raspon referentne tablice. Formira se padajući popis. Sada, kada popunjavate tablicu, zajamčena je potpuna sukladnost vrijednosti.

Primjer korištenja funkcije VLOOKUP u programu Excel

Kako koristiti VLOOKUP u Excelu, svaki korisnik sam rješava, na temelju praktičnih zadataka u svom radu. Uzimajući u obzir sve gore navedene nijanse, prethodni primjer možemo dovesti u logički kraj i konstruirati prikladan i jasan algoritam za primjenu VLR funkcije u praksi. Dakle, postoji procjena troškova za rezervne dijelove za popravak opreme. Postoji i cjenik za rezervne dijelove. Ekonomist tvrtke je dužan pronaći cijenu rezervnih dijelova za popravke.

Prvo morate pravilno napisati formulu.

Dakle, korištenjem funkcije VLOOKUP, cijena će se pojaviti u skladu s kodom u prvom retku. Potrebno je pomnožiti formulu prema dolje, da biste to učinili, odaberite ćeliju s formulom i povucite kvadrat u desnom kutu ćelije. U stupcu D funkcija vraća cijene koje odgovaraju šiframa. Potrebno je zamijeniti formule vrijednostima. Da biste to učinili, odaberite ispunjene ćelije stupca D, kopirajte ih i zalijepite kao vrijednosti. Zatim morate stvoriti stupac "Sum", u koji unosite formulu za proizvod količine i cijene, a zatim upotrijebite zbrajanje da sumirate troškove.

Ovo je primjer kako VLOOKUP radi u Excelu.

Pogreške pri korištenju CDF funkcije

U početnoj fazi uporabe, umjesto željenih vrijednosti, funkcija često ukazuje na različite vrste pogrešaka. Znati što znači pogreška je pravi način da se to brzo popravi. Najčešće vraćene pogreške:

• "N / A" - najčešći tip pogreške. Može se dogoditi iz nekoliko razloga.

1. Neispravno je smješten stupac na kojem funkcija traži podudarnost (mora biti krajnje lijeva). Ako se pojavi situacija u kojoj je željena vrijednost lijevo od područja podudaranja, tablica se mora pretvoriti. Primjerice, kopirajte željeni stupac i zalijepite ga desno od područja pretraživanja.
2. Pogreška "N / A" može se vratiti ako raspon pretraživanja nije fiksan, dok povlačite WFD formulu.
3. Ako je točno određeno pretraživanje korištenjem argumenta Interval View (postavljena je znamenka 0), vraća se pogreška "N / A" ako nema točnog podudaranja u dvije tablice.
4. Argument Interval Viewing postavljen je na najbližu vrijednost (1 je označen, ili polje nije popunjeno), a raspon preko kojeg se pretraživanje obavlja nije sortiran. U slučaju netočne pretrage potrebno je sortirati krajnji lijevi stupac raspona pretraživanja.
5. Usporedni podaci imaju dodatne razmake (kako bi ih uklonili, možete koristiti funkciju "SIFPROBELS", primijeniti je na tablicu i na željenu vrijednost), drugačiji format, dodatne citate. Za pisanje istih vrijednosti u obje tablice, ima smisla koristiti padajući popis.

• "LINK" - ova se pogreška često pojavljuje kada je broj stupca naveden pogrešno, ako toliko stupaca nije u odabranom rasponu. U takvim slučajevima potrebno je zapamtiti da je broj odložen, računajući iz lijevog stupca odabranog raspona, a ne tablice u cjelini.
• "NAME" - pogreška se često vraća kada se tekst unese u "Potrebna vrijednost". Tekst mora biti napisan u navodnicima.

Poznavanje načina korištenja VLOOKUP-a u Excelu, kao i sve ostalo u ovom programu, dolazi s praksom. Sama proučavanja teorije umjesto jasnoće vjerojatno će donijeti kaos u razumijevanju određene operacije u programu. Kada proučavate bilo koju Excelovu funkciju, uvijek je učinkovitije da se korisnik osloni na određeni, premda mali, primjerni tablet. To vam omogućuje bolje razumijevanje suštine analize, učvršćivanje stečenog znanja. Tako s funkcijom CDF u Excelu. Uz veliku količinu teorije, praktična primjena pokazuje da ona nije toliko složena koliko je korisna.