Prenesite primer delovnega zvezka
V drugih člankih smo razpravljali o tem, kako so funkcije, kot sta OFFSET in INDIRECT, nestanovitne. Če začnete uporabljati veliko teh v preglednici ali če je veliko celic odvisno od nestanovitne funkcije, lahko povzroči, da računalnik porabi opazen čas za ponovne izračune vsakič, ko poskusite spremeniti celico. Namesto da bi bili razočarani nad tem, kako vaš računalnik ni dovolj hiter, bo ta članek raziskal alternativne načine za reševanje pogostih situacij, ki jih ljudje uporabljajo OFFSET in INDIRECT.
Zamenjava OFFSET za ustvarjanje dinamičnega seznama
Ko ste izvedeli za funkcijo OFFSET, je običajno napačno prepričanje, da je to edini način, da z zadnjimi argumenti vrnete rezultat z dinamično velikostjo. Oglejmo si seznam v stolpcu A, kjer bi se lahko naš uporabnik pozneje odločil za dodajanje dodatnih elementov.
Če želite v celici C2 narediti spustni meni, lahko določite imenovano območje s spremenljivo formulo, kot je
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
S trenutno nastavitvijo bi to zagotovo vrnilo sklic na obseg A2: A5. Obstaja pa še en način uporabe nehlapnega INDEX-a. Če želite to narediti, razmislite o tem, da napišemo sklic na obseg od A2 do A5. Ko pišete »A2: A5«, na to ne pomislite kot na en sam podatek, ampak kot na »StartingPoint« in »EndingPoint«, ločeni z dvopičjem (npr. StartingPoint: EndingPoint). V formuli sta lahko tako StartingPoint kot EndingPoint rezultat drugih funkcij.
Tukaj je formula, ki jo bomo uporabili za ustvarjanje dinamičnega obsega s funkcijo INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Upoštevajte, da smo izjavili, da bo izhodiščna točka za to območje vedno A2. Na drugi strani debelega črevesa uporabljamo INDEX, da ugotovimo, kje bo EndingPoint. COUNTA bo ugotovila, da je v stolpcu A 5 celic s podatki, zato bo naš INDEX ustvaril sklic na A5. Formula se tako oceni tako:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
S to tehniko lahko s funkcijo INDEX dinamično sestavite sklic na kateri koli seznam ali celo dvodimenzionalno tabelo. V preglednici z obilico funkcij OFFSET bo zamenjava funkcij OFFSET z indeksom omogočila, da se bo računalnik začel izvajati veliko hitreje.
Zamenjava INDIRECT za imena listov
Funkcija INDIRECT se pogosto pokliče, ko so delovni zvezki oblikovani s podatki, razpršenimi po več delovnih listih. Če vseh podatkov ne morete dobiti na en list, vendar ne želite uporabljati nestanovitne funkcije, boste morda lahko uporabili CHOOSE.
Razmislite o naslednji postavitvi, kjer imamo podatke o prodaji na treh različnih delovnih listih. Na našem povzetku smo izbrali, iz katerega četrtletja želimo videti podatke.
Naša formula v B3 je:
= IZBERI (UJEMA (B2, D2: D4, 0), jesen! A2, zima! A2, pomlad! A2)
V tej formuli bo funkcija MATCH določila, katero področje želimo vrniti. To potem funkciji CHOOSE pove, kateri od naslednjih obsegov naj se kot rezultat vrne.
Za vrnitev večjega razpona lahko uporabite tudi funkcijo IZBERI. V tem primeru imamo tabelo prodajnih podatkov na vsakem od treh delovnih listov.
Namesto da napišete funkcijo INDIRECT za izdelavo imena lista, lahko CHOOSE določite, v kateri tabeli boste iskali. V svojem primeru sem tri tabele že poimenoval tbFall, tbWinter in tbSpring. Formula v B4 je:
= VLOOKUP (B3, IZBERI (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
V tej formuli bo MATCH določil, da želimo 2nd element z našega seznama. CHOOSE bo nato vzel to 2 in sklic vrnil na tbWinter. Nazadnje bo naš VLOOKUP lahko dokončal iskanje v podani tabeli in ugotovil, da je celotna prodaja Banane pozimi znašala 6000 USD.
= VLOOKUP (B3, IZBERI (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
Ta tehnika je omejena z dejstvom, da morate funkcijo CHOOSE izpolniti z vsemi področji, iz katerih boste morda želeli pridobiti vrednost, vendar vam daje prednost, da se izognete nestanovitni formuli. Odvisno od tega, koliko izračunov morate dokončati, bi se lahko ta sposobnost izkazala za zelo dragoceno.
