Laadige alla töövihiku näide
Oleme teistes artiklites arutanud, kuidas on olemas selliseid funktsioone nagu OFFSET ja INDIRECT, mis on kõikuvad. Kui hakkate paljusid neist arvutustabelis kasutama või teil on palju lahtreid, mis sõltuvad lenduvast funktsioonist, võite arvuti iga kord, kui proovite lahtrit muuta, kulutada märgatavalt aega ümberarvutuste tegemiseks. Selle asemel, et pettuda selles, kuidas teie arvuti pole piisavalt kiire, uurib see artikkel alternatiivseid viise, kuidas lahendada levinud olukordi, mida inimesed kasutavad OFFSET ja INDIRECT.
OFFSET asendamine dünaamilise loendi loomiseks
Pärast funktsiooni OFFSET tundmaõppimist on levinud eksiarvamus, et see on ainus viis viimase paari argumendi abil dünaamilise suurusega tulemuse tagastamiseks. Vaatame veerus A olevat loendit, kus meie kasutaja võib hiljem otsustada täiendavate üksuste lisamise.
Lahtris C2 rippmenüü tegemiseks võite määratleda nimega vahemiku lenduva valemiga
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Praeguse seadistuse korral annaks see kindlasti viite vahemikule A2: A5. Siiski on veel üks viis mittelenduva INDEXi kasutamiseks. Selleks mõtle, kas kirjutame viite vahemikule A2 kuni A5. Kui kirjutate „A2: A5”, ärge mõelge sellest kui ühest andmestikust, vaid pigem kui „lähtepunktist” ja „lõpp -punktist”, mis on eraldatud kooloniga (nt StartingPoint: EndingPoint). Valemis võivad nii lähte- kui ka lõpp -punkt olla muude funktsioonide tulemused.
Siin on valem, mida kasutame dünaamilise vahemiku loomiseks funktsiooni INDEX abil:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Pange tähele, et oleme öelnud, et selle vahemiku lähtepunkt on alati A2. Käärsoole teisel poolel kasutame INDEX -i, et määrata lõpp -punkti asukoht. COUNTA teeb kindlaks, et veerus A on 5 lahtrit andmetega ja seega loob meie INDEX viite A5 -le. Seega hinnatakse valemit järgmiselt:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Selle tehnika abil saate dünaamiliselt luua viite mis tahes loendile või isegi kahemõõtmelisele tabelile, kasutades funktsiooni INDEX. Arvutustabelis, kus on palju OFFSET -funktsioone, võimaldab OFFSET -ide asendamine INDEX -iga teie arvuti palju kiiremini tööle hakata.
Lehtede nimede INDIRECT asendamine
Funktsiooni INDIRECT kutsutakse sageli esile, kui töövihikud on kujundatud nii, et andmed on hajutatud mitmele töölehele. Kui te ei saa kõiki andmeid ühele lehele, kuid te ei soovi kasutada lenduvat funktsiooni, võite kasutada funktsiooni CHOOSE.
Kaaluge järgmist paigutust, kus meil on müügiandmed kolme erineva töölehe kohta. Oleme oma kokkuvõtte lehel valinud, millisest kvartalist tahame andmeid vaadata.
Meie valem B3 -s on järgmine:
= VALI (MATCH (B2, D2: D4, 0), Fall! A2, Winter! A2, Spring! A2)
Selles valemis määrab funktsioon MATCH kindlaks, millist piirkonda tahame tagastada. See ütleb seejärel funktsioonile VALI, milline järgmistest vahemikest tuleb tulemuseks tagastada.
Suurema vahemiku tagastamiseks võite kasutada ka funktsiooni VALI. Selles näites on meil tabel iga kolme töölehe müügiandmete kohta.
Selle asemel, et kirjutada lehe nime loomiseks kaudset funktsiooni, võite lasta valikul CHOOSE otsustada, millisel tabelil otsingut teha. Minu näites olen juba nimetanud kolm tabelit tbFall, tbWinter ja tbSpring. B4 valem on järgmine:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Selles valemis otsustab MATCH, et me tahame 2nd üksus meie loendist. Seejärel valib CHOOSE selle 2 ja tagastab viite tbWinterile. Lõpuks saab meie VLOOKUP antud tabelis otsingu lõpule viia ja leiab, et banaanide kogumüük talvel oli 6000 dollarit.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbTalv, 2, 0) = 6000
Seda tehnikat piirab asjaolu, et peate täitma funktsiooni VALI kõik alad, kust võiksite väärtust hankida, kuid see aitab teil vältida lenduvaid valemeid. Sõltuvalt sellest, kui palju arvutusi peate täitma, võib see võime osutuda üsna väärtuslikuks.
