VLOOKUP, HLOOKUP i INDEX-MATCH poznati su i uobičajeni načini traženja vrijednosti u Excel tablici. No, to nisu jedini načini traženja vrijednosti u Excelu. U ovom ćemo članku naučiti kako koristiti funkciju OFFSET zajedno s funkcijom MATCH u Excelu. Da, dobro ste pročitali, upotrijebit ćemo zloglasnu OFFSET -ovu funkciju Excela za traženje određene vrijednosti u Excel tablici.
Opća formula,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Pažljivo pročitajte ovo:
StartCell:Ovo je početna ćelija tablice za pretraživanje. Recimo, ako želite tražiti u rasponu A2: A10, tada će StartCell biti A1.
RowLookupValue: Ovo je vrijednost pretraživanja koju želite pronaći u retcima ispodStartCell.
RowLookupRange:Ovo je raspon u kojem želite pronaći RowLookupValue. To je raspon u nastavku StartCell (A2: A10).
ColLookupValue: Ovo je vrijednost pretraživanja koju želite pronaći u stupcima (zaglavlja).
Raspon ColLookup:Ovo je raspon u kojem želite pronaći ColLookupValue. To je raspon s desne strane StartCell -a (poput B1: D1).
Dakle, dosta teorije. Počnimo s primjerom.
Primjer: Potražite prodaju pomoću funkcija OFFSET i MATCH iz tablice Excel
Ovdje imamo uzorak tablice prodaje različitih zaposlenika u različitim mjesecima.
Sada nas šef traži od nas da prodamo prodaju koju je Id 1004 obavio u mjesecu lipnju. Postoji mnogo načina za to, ali budući da učimo o formuli OFFSET-MATCH, upotrijebit ćemo ih za traženje željene vrijednosti.
Gore već imamo generičku formulu. Moramo samo identificirati ove varijable u ovoj tablici.
StartCell je B1 ovdje. RowLookupValue je M1. RowLookupRange je B2: B1o (raspon ispod početne ćelije).
ColLookupValue nalazi se u ćeliji M2. ColLookupRange je C1: I1 (Raspon zaglavlja desno od StartCell -a).
Identificirali smo sve varijable. Stavimo ih u Excel formulu.
Napišite ovu formulu u ćeliju M3 i pritisnite gumb za unos.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Pritiskom na tipku enter odmah ćete dobiti rezultat. Prodaja obavljena ID 1004 u mjesecu lipnju iznosi 177.
Kako radi?
Funkcija OFFSET je odmaknuti se od zadane početne ćelije do zadanog retka i stupaca, a zatim vratiti vrijednost iz te ćelije. Na primjer, ako napišem OFFSET (B1,1,1), funkcija OFFSET otići će u ćeliju C2 (1 ćelija dolje, 1 ćelija desno) i vratiti joj vrijednost.
Evo, imamo formuluOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Prva funkcija MATCH vraća indeks M1 (1004) u rasponu B2: B10, što je 4. Sada je formula,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Sljedeća funkcija MATCH vraća indeks M2 ('Jun') u rasponu C1: I1, što je 7. Sada je formulaOFFSET (B1,4,7).
Sada funkcija OFFSET jednostavno premješta 4 ćelije dolje u ćeliju B1 koja je vodi u B5. Zatim se funkcija OFFSET pomiče na 7 lijevo na B5, čime se prelazi na I5. To je to. Funkcija OFFSET vraća vrijednost iz ćelije I5 koja 177.
Prednosti ove tehnike traženja?
Ovo je brzo. Jedina prednost ove metode je što je brža od ostalih metoda. Ista se stvar može učiniti pomoću funkcije INDEX-MATCH ili funkcije VLOOKUP. No, dobro je znati neke alternative. Možda će jednog dana dobro doći.
Pa da, dečki, ovako možete koristiti funkciju OFFSET i MATCH za LOOKUP vrijednosti u Excel tablicama. Nadam se da je bilo dovoljno objašnjenje. Ako imate nedoumica u vezi ovog članka ili bilo koje druge teme vezane uz Excel/VBA, pitajte me u donjem odjeljku komentara.
Upotrijebite INDEX i MATCH za traženje vrijednosti:Formula INDEX-MATCH koristi se za dinamičko i precizno traženje vrijednosti u danoj tablici. Ovo je alternativa funkciji VLOOKUP i prevladava nedostatke funkcije VLOOKUP.
Zbroj prema OFFSET grupama u redovima i stupcima: Funkcija OFFSET može se koristiti za dinamičko zbrajanje skupine ćelija. Te grupe mogu biti bilo gdje u listu.
Kako dohvatiti svaku N -tu vrijednost u rasponu u Excelu: Pomoću OFFSET funkcije Excela možemo dohvatiti vrijednosti iz izmjenjivih redaka ili stupaca. Ova formula koristi funkciju ROW za izmjenu redova i COLUMN funkciju za izmjenu u stupcima.
Kako koristiti OFFSET funkciju u Excelu: Funkcija OFFSET moćna je Excel funkcija koju mnogi korisnici potcjenjuju. No, stručnjaci znaju moć funkcije OFFSET i kako tu funkciju možemo koristiti za obavljanje čarobnih zadataka u formuli Excel. Ovdje su osnove funkcije OFFSET.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti Excel VLOOKUP funkciju| 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 Excel COUNTIF funkcija| 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.
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.