Lataa esimerkkityökirja
Olemme keskustelleet muissa artikkeleissa siitä, miten on olemassa toimintoja, kuten OFFSET ja INDIRECT, jotka ovat epävakaita. Jos aloitat useiden näistä laskentataulukossa tai sinulla on monia soluja riippuvaisia haihtuvista toiminnoista, voit saada tietokoneesi käyttämään huomattavan paljon aikaa uudelleenlaskennassa aina, kun yrität vaihtaa solua. Sen sijaan, että turhautuisi siihen, kuinka tietokoneesi ei ole riittävän nopea, tässä artikkelissa tutkitaan vaihtoehtoisia tapoja ratkaista yleisiä tilanteita, joita ihmiset käyttävät OFFSET- ja Epäsuorasti.
Korvaamalla OFFSET luodaan dynaaminen lista
Kun olet oppinut OFFSET -toiminnon, on yleinen harhaluulo, että se on ainoa tapa palauttaa dynaamisen kokoinen tulos käyttämällä viimeisiä argumentteja. Katsotaanpa luetteloa sarakkeessa A, jossa käyttäjämme saattaa myöhemmin päättää lisätä muita kohteita.
Jos haluat tehdä pudotusvalikon solussa C2, määritä nimetty alue haihtuvalla kaavalla, kuten
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Nykyisellä asetuksella tämä varmasti palauttaisi viittauksen alueeseen A2: A5. Kuitenkin on toinen tapa käyttää haihtumatonta INDEXiä. Voit tehdä tämän kirjoittamalla viittauksen alueelle A2 - A5. Kun kirjoitat "A2: A5", älä ajattele tätä yksittäisenä datana, vaan pikemminkin "StartingPoint" ja "EndingPoint", jotka on erotettu kaksoispisteellä (esim. StartingPoint: EndingPoint). Kaavassa sekä StartingPoint että EndingPoint voivat olla muiden toimintojen tuloksia.
Tässä on kaava, jota käytämme dynaamisen alueen luomiseen INDEX -funktion avulla:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Huomaa, että olemme todenneet, että tämän alueen aloituspiste on aina A2. Kaksoispisteen toisella puolella käytämme INDEXiä määrittääksesi, missä päätepiste on. COUNTA määrittää, että sarakkeessa A on 5 solua, joiden tiedot ovat, ja siten INDEX luo viitteen A5: een. Kaava arvioidaan näin:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Tällä tekniikalla voit rakentaa dynaamisesti viittauksen mihin tahansa luetteloon tai jopa kaksiulotteiseen taulukkoon INDEX-funktion avulla. Laskentataulukossa, jossa on paljon OFFSET -toimintoja, kun OFFSET -kohteet korvataan INDEXillä, tietokoneesi voi alkaa toimia paljon nopeammin.
Arkien nimien epäsuoran korvaaminen
INDIRECT -toimintoa kutsutaan usein, kun työkirjat on suunniteltu siten, että tiedot ovat hajallaan useille laskentataulukoille. Jos et voi saada kaikkia tietoja yhdelle arkille, mutta et halua käyttää haihtuvia toimintoja, voit ehkä käyttää VALITSE -vaihtoehtoa.
Harkitse seuraavaa asettelua, jossa meillä on myyntitietoja kolmelta eri laskentataulukolta. Yhteenveto -taulukossamme olemme valinneet, millä vuosineljänneksellä haluamme tarkastella tietoja.
Kaavamme B3: ssa on:
= VALITSE (MATCH (B2, D2: D4, 0), Fall! A2, Winter! A2, Spring! A2)
Tässä kaavassa MATCH -toiminto määrittää, minkä alueen haluamme palauttaa. Tämä kertoo VALITSE -toiminnolle, mikä seuraavista alueista palautetaan tuloksena.
Voit myös palauttaa suuremman alueen käyttämällä VALITSE -toimintoa. Tässä esimerkissä meillä on taulukko myyntitiedoista jokaisesta kolmesta laskentataulukostamme.
Sen sijaan, että kirjoittaisit epäsuoraa funktiota arkin nimen luomiseksi, voit antaa VALITSE päättää, millä taulukolla haku tehdään. Esimerkissäni olen jo nimittänyt kolme taulukkoa tbFall, tbWinter ja tbSpring. B4: n kaava on:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Tässä kaavassa MATCH määrittää, että haluamme 2toinen kohde luettelostamme. CHOOSE ottaa tämän 2 ja palauttaa viittauksen tbWinteriin. Lopuksi VLOOKUP pystyy suorittamaan haun annetusta taulukosta ja huomaa, että banaanin kokonaismyynti talvella oli 6000 dollaria.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbTalvi, 2, 0) = 6000
Tätä tekniikkaa rajoittaa se, että sinun on täytettävä VALITSE -toiminto kaikilla alueilla, joista haluat ehkä hakea arvon, mutta se auttaa sinua välttämään haihtuvan kaavan. Riippuen siitä, kuinka monta laskentaa sinun on suoritettava, tämä kyky voi osoittautua varsin arvokkaaksi.
