Zamislite da u radnoj knjizi imate više identičnih listova koji sadrže identične tablice (poput evidencije o posjećenosti svakog mjeseca na zasebnom listu). Sada želite stvoriti nadzornu ploču koja vam prikazuje ukupnu posjećenost za mjesec. Sadržaj podataka o mjesečnim podacima na nadzornoj ploči istovremeno nije dobra opcija. Želimo padajući izbornik za odabir mjeseca. Potrebna nam je VLOOKUP formula za pregled s lista odabranog mjeseca.
Jednostavnim riječima, potrebna nam je formula za pretraživanje kako bismo potražili s promjenjivih listova.
Kao što gornji gif pokazuje, koristit ćemo VLOOKUP i INDIRECT funkciju zajedno za traženje s više listova, na temelju njihovih naziva.
Opća formula za traženje više listova
| =VLOOKUP(lookup_value,IZREDNO(""&reference_name_reference&"! lookup_table "), col_index, 0) |
Lookup_value: Ovo je vrijednost koju tražite u pretraživanju stol.
Referenca_ime_naloga: Ovo je referenca ćelije koja sadrži naziv lista.
Tablica_potraživanja: Ovo je referenca tablice u kojoj želite tražiti lookup_value. To može biti imenovani raspon, tablica ili apsolutna referenca. Trebao bi biti isti na svim listovima.
Col_Index: Ovo je broj stupca u tablici iz koje želite dohvatiti vrijednost. Ako ste upoznati s funkcijom VLOOKUP, znate što je to.
Pa idemo na primjer.
Primjer: Dohvatite prisustvo za odabrani mjesec
Dakle, imamo radnu bilježnicu koja održava posjećenost mojih učenika programa Excel. Podaci svakog mjeseca zasebno se spremaju u različite listove. Naziv lista postavljen je na naziv mjeseci. Za sada imam podatke za tri mjeseca, ali bit će ih naravno više.
Želim izraditi izvješće koje prikazuje posjećenost odabranog mjeseca. Mjesec se može odabrati s padajućeg popisa. Formula bi trebala moći automatski gledati s tog lista, čak i ako mu se doda novi list.
Dakle, pripremamo gornju tablicu. Ćelija G3 stvorili smo padajući popis pomoću padajućeg popisa.
Vrijednost pretraživanja je in B4. Referentna tabla_ime_naloga je u G3. Tablica za pretraživanje u svim listovima je B3: AZ100. Željeli bismo dohvatiti vrijednost iz 2 stupca. Stoga zapisujemo ovu formulu u C4 i povlačimo je prema dolje. Slično, za odsutne vrijednosti mijenjamo indeks stupca.
| =VLOOKUP(B4,IZREDNO("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Kako radi?
Formula je riješena iznutra prema van. Prvo da vidimo kako se to rješava korak po korak.
Pretpostavimo da G3 sadrži Siječanj.
| =VLOOKUP(B4,IZREDNO("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, INDIREKTNO ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = VLOOKUP (B4,Siječanj! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Dakle, prvo se izraz "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" razrješava u niz "Jan! $ B $ 3: $ Az $ 100". Zatim funkcija INDIRECT pretvara ovaj niz u stvarnu referencu. I na kraju smo dobili formulu VLOOKUP (B4, siječanj! $ B $ 3: $ Az $ 100,2,0). I ovo se konačno rješava na 7. Sada, ako promijenite naziv lista u G3, tekst referentne vrijednosti bit će promijenjen. Ovako gledate s promjenjivih listova u Excelu.
Nadam se da vam je ovo bilo od pomoći. Ako imate bilo kakvih pitanja ili želite drugačije pretraživanje, javite mi u donjem odjeljku komentara. Rado ću vam pomoći. Do tada nastavite s Excellingom.
Koristite VLOOKUP iz dvije ili više tablica za pretraživanje | Za pretraživanje iz više tablica možemo uzeti IFERROR pristup. Pogled s više tablica uzima pogrešku kao prekidač za sljedeću tablicu. Druga metoda može biti pristup If.
Kako izvršiti pretraživanje osjetljivo na velika i mala slova u Excelu | excelova funkcija VLOOKUP ne razlikuje velika i mala slova i vratit će prvu usklađenu vrijednost s popisa. INDEX-MATCH nije iznimka, ali se može izmijeniti kako bi bila osjetljiva na velika i mala slova. Da vidimo kako…
Potražite često pojavljujući tekst s kriterijima u Excelu | Pretraživanje se najčešće pojavljuje u tekstu u rasponu koji koristimo INDEX-MATCH s MODE funkcijom. Evo metode.
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 najpopularnijih 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.