Last ned eksemplet på arbeidsbok
Vi har diskutert i andre artikler om hvordan det er funksjoner som OFFSET og INDIRECT som er flyktige. Hvis du begynner å bruke mange av disse i et regneark eller har mange celler avhengig av flyktig funksjon, kan du få datamaskinen til å bruke merkbar tid på å gjøre nye beregninger hver gang du prøver å endre en celle. I stedet for å bli frustrert over hvordan datamaskinen din ikke er rask nok, vil denne artikkelen utforske alternative måter å løse de vanlige situasjonene folk bruker OFFSET og INDIRECT på.
Erstatter OFFSET for å lage en dynamisk liste
Etter å ha lært om OFFSET -funksjonen, er det en vanlig misforståelse at det er den eneste måten å returnere et resultat med dynamisk størrelse ved å bruke de siste argumentene. La oss se på en liste i kolonne A der brukeren vår senere kan velge å legge til flere elementer.
For å gjøre en rullegardin i celle C2, kan du definere et navngitt område med en flyktig formel som
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Med det nåværende oppsettet vil dette absolutt returnere en referanse til området A2: A5. Det er imidlertid en annen måte å bruke den ikke-flyktige INDEX. For å gjøre dette, tenk på at vi skriver en referanse til området som går fra A2 til A5. Når du skriver "A2: A5", ikke tenk på dette som et enkelt stykke data, men heller som et "Startpunkt" og "EndingPoint" atskilt med et kolon (f.eks. Startpunkt: EndingPoint). I en formel kan både StartPoint og EndingPoint være resultatet av andre funksjoner.
Her er formelen vi skal bruke til å lage et dynamisk område ved hjelp av INDEX -funksjonen:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Vær oppmerksom på at vi har uttalt at Startpunktet for dette området alltid vil være A2. På den andre siden av tykktarmen bruker vi INDEX for å bestemme hvor EndingPoint skal være. COUNTA vil bestemme at det er 5 celler med data i kolonne A, og derfor vil vår INDEX opprette en referanse til A5. Formelen blir dermed vurdert slik:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Ved å bruke denne teknikken kan du dynamisk bygge en referanse til hvilken som helst liste, eller til og med et todimensjonalt bord ved å bruke INDEX-funksjonen. I et regneark med en mengde OFFSET -funksjoner, vil erstatning av OFFSETs med INDEX gjøre at datamaskinen din kan begynne å kjøre mye raskere.
Erstatter INDIRECT for arknavn
INDIRECT -funksjonen blir ofte kalt når arbeidsbøker er designet med data spredt over flere regneark. Hvis du ikke kan få alle dataene på et enkelt ark, men ikke vil bruke en flyktig funksjon, kan du kanskje bruke VELG.
Tenk på følgende oppsett, hvor vi har salgsdata på tvers av tre forskjellige regneark. På sammendragsarket har vi valgt hvilket kvartal vi vil se dataene fra.
Formelen vår i B3 er:
= VELG (MATCH (B2, D2: D4, 0), høst! A2, vinter! A2, vår! A2)
I denne formelen kommer MATCH -funksjonen til å bestemme hvilket område vi vil returnere. Dette forteller deretter CHOOSE -funksjonen hvilket av følgende områder som skal returneres som resultat.
Du kan også bruke CHOOSE -funksjonen for å returnere et større område. I dette eksemplet har vi en tabell med salgsdata på hvert av våre tre regneark.
I stedet for å skrive en INDIRECT -funksjon for å bygge arknavnet, kan du la CHOOSE bestemme hvilken tabell du skal gjøre søket på. I mitt eksempel har jeg allerede navngitt de tre tabellene tbFall, tbWinter og tbSpring. Formelen i B4 er:
= VLOOKUP (B3, VELG (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
I denne formelen kommer MATCH til å bestemme at vi vil ha 2nd element fra vår liste. CHOOSE vil da ta det 2 og returnere referansen til tbWinter. Til slutt vil vår VLOOKUP kunne fullføre søket i tabellen, og det vil finne at det totale salget for Banana om vinteren var $ 6000.
= VLOOKUP (B3, VELG (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Denne teknikken er begrenset av det faktum at du må fylle ut VELG -funksjonen med alle områdene du kanskje vil hente en verdi fra, men det gir deg fordelen av å unngå en flyktig formel. Avhengig av hvor mange beregninger du trenger å fullføre, kan denne evnen vise seg å være ganske verdifull.
