U ovom ćemo članku naučiti kako dohvatiti cijenu s popisa koji odgovara kriterijima kategorije i stavke u Excelu.
Scenarij:
Lako je pronaći vrijednost s jednim kriterijem u tablici, za to bismo jednostavno mogli koristiti VLOOKUP. No, ako u svojim podacima nemate jedan kriterij stupca i morate pronaći više kriterija stupca koji odgovaraju vrijednosti, VLOOKUP ne pomaže.
Opća formula za kategoriju i stavku
| = INDEX (opseg_pregledavanja, MATCH (1, INDEX ((stavka1 = raspon_ stavke) * * (kategorija2 = raspon_kategorije), 0,1), 0)) |
lookup_range: To je 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 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)) |
Već znamo kako funkcije INDEX i MATCH rade u EXCEL -u, pa to ovdje nećemo 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
Š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. Što je ovdje 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).
Ako posljedično možete pritisnuti CTRL + SHIFT + ENTER, tada možete ukloniti unutarnju funkciju INDEX. Samo napišite ovu formulu i pritisnite CTRL + SHIFT + ENTER.
Formula
| = INDEKS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Opća formula niza za traženje više kriterija
| = INDEX (opseg_pregledavanja, MATCH (1, (kriterij1 = raspon1)*(kriterij2 = raspon2)*(kriterijN = rasponN), 0)) |
Ovdje su sve opservacijske bilješke korištene formulom u Excelu
Bilješke:
- Koristite Vlookup ako imate jedan kriterij koji odgovara, to je uobičajena praksa.
- Možete dodati više redaka i stupaca u niz za pretraživanje.
- Tekstualni argumenti moraju biti navedeni pod navodnicima ("").
- Tablica VLOOKUP mora imati niz lookup_arig u krajnjem lijevom ili prvom stupcu.
- Indeks col ne može biti 1 jer je to niz za pretraživanje
- Za vrijednost točnog podudaranja koristi se argument 0. Za približnu vrijednost podudaranja upotrijebite 1.
- Funkcija vraća pogrešku #N/A, ako vrijednost traženja nije pronađena u nizu za pretraživanje. Zato uhvatite grešku, ako je potrebno.
Nadamo se da je ovaj članak o tome kako dohvatiti cijenu s popisa koji odgovara kriterijima kategorije i stavke 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.
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.
