Preuzmite primjer radne knjige
U drugim smo člancima raspravljali o tome kako postoje funkcije poput OFFSET i INDIRECT koje su nestabilne. Ako počnete koristiti mnoge od njih u proračunskoj tablici ili imate mnogo ćelija ovisno o promjenjivoj funkciji, možete uzrokovati da vaše računalo potroši značajno vrijeme na ponovne izračune svaki put kada pokušate promijeniti ćeliju. Umjesto da postanete frustrirani zbog toga što vaše računalo nije dovoljno brzo, ovaj će članak istražiti alternativne načine rješavanja uobičajenih situacija koje ljudi koriste OFFSET i INDIRECT.
Zamjena OFFSET -a za izradu dinamičkog popisa
Nakon što ste saznali za funkciju OFFSET, uobičajeno je zabluda da je to jedini način da vratite rezultat s dinamičkom veličinom koristeći zadnjih par argumenata. Pogledajmo popis u stupcu A na kojem bi naš korisnik kasnije mogao odlučiti dodati dodatne stavke.
Da biste napravili padajući izbornik u ćeliji C2, mogli biste definirati Named Range s promjenjivom formulom poput
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
S trenutnim postavljanjem, ovo bi zasigurno vratilo referencu na raspon A2: A5. Međutim, postoji i drugi način korištenja nehlapljivog INDEKSA. Da biste to učinili, razmislite o tome da napišemo referencu na raspon koji ide od A2 do A5. Kad pišete "A2: A5", nemojte ovo smatrati jednim jedinim podatkom, nego "početnom točkom" i "završnom točkom" odvojenom dvotočkom (npr. Početna točka: krajnja točka). U formuli i početna i završna točka mogu biti rezultat drugih funkcija.
Evo formule koju ćemo koristiti za stvaranje dinamičkog raspona pomoću funkcije INDEX:
= $ A $ 2: INDEKS ($ A: $ A, COUNTA ($ A: $ A))
Imajte na umu da smo naveli da će početna točka za ovaj raspon uvijek biti A2. S druge strane debelog crijeva koristimo INDEX da odredimo gdje će se nalaziti EndingPoint. COUNTA će utvrditi da postoji 5 ćelija s podacima u stupcu A, pa će naš INDEX stvoriti referencu na A5. Formula se tako procjenjuje na sljedeći način:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Pomoću ove tehnike možete dinamički izgraditi referencu na bilo koji popis ili čak dvodimenzionalnu tablicu pomoću funkcije INDEX. U proračunskoj tablici s obiljem OFFSET funkcija, zamjena OFFSET -ova s INDEX -om omogućit će vašem računalu da počne raditi mnogo brže.
Zamjena INDIRECT za nazive listova
Funkcija INDIRECT često se poziva kada su radne knjige dizajnirane s podacima razasutim po više radnih listova. Ako ne možete unijeti sve podatke na jedan list, ali ne želite koristiti promjenjivu funkciju, možda ćete moći upotrijebiti CHOOSE.
Razmotrite sljedeći izgled, gdje imamo podatke o prodaji na 3 različita radna lista. Na našoj stranici Sažetak odabrali smo iz kojeg tromjesečja želimo pregledati podatke.
Naša formula u B3 je:
= ODABERITE (UTAKMICA (B2, D2: D4, 0), jesen! A2, zima! A2, proljeće! A2)
U ovoj formuli funkcija MATCH će odrediti koje područje želimo vratiti. To tada govori funkciji IZABERI koji od sljedećih raspona treba vratiti kao rezultat.
Također možete koristiti funkciju IZABERI da biste vratili veći raspon. U ovom primjeru imamo tablicu prodajnih podataka za svaki od naša tri radna lista.
Umjesto pisanja INDIRECT funkcije za izradu naziva lista, možete dopustiti CHOOSE da odredi na kojoj tablici će se vršiti pretraživanje. U svom primjeru već sam imenovao tri tablice tbFall, tbWinter i tbSpring. Formula u B4 je:
= VLOOKUP (B3, ODABERITE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
U ovoj formuli, MATCH će utvrditi da želimo 2nd stavku s našeg popisa. CHOOSE će tada uzeti to 2 i vratiti referencu na tbWinter. Konačno, naš VLOOKUP moći će dovršiti pretraživanje u zadanoj tablici i ustanovit će da je ukupna prodaja Banane zimi bila 6000 USD.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbZima, 2, 0) = 6000
Ova je tehnika ograničena činjenicom da funkciju CHOOSE morate ispuniti svim područjima iz kojih biste htjeli dohvatiti vrijednost, ali vam daje prednost izbjegavanja promjenjive formule. Ovisno o tome koliko proračuna trebate dovršiti, ova bi se sposobnost mogla pokazati vrlo vrijednom.
