Download eksemplet på projektmappe
Vi har diskuteret i andre artikler om, hvordan der er funktioner som OFFSET og INDIRECT, som er flygtige. Hvis du begynder at bruge mange af disse i et regneark eller har mange celler afhængig af flygtig funktion, kan du få din computer til at bruge en mærkbar tid på at foretage genberegninger, hver gang du prøver at ændre en celle. I stedet for at blive frustreret over, hvordan din computer ikke er hurtig nok, vil denne artikel undersøge alternative måder at løse de almindelige situationer, folk bruger OFFSET og INDIRECT.
Udskiftning af OFFSET for at oprette en dynamisk liste
Efter at have lært om OFFSET -funktionen, er det en almindelig misforståelse, at det er den eneste måde at returnere et resultat med dynamisk størrelse ved hjælp af de sidste par argumenter. Lad os se på en liste i kolonne A, hvor vores bruger senere kan beslutte at tilføje yderligere varer.
For at foretage en dropdown i celle C2 kan du definere et navngivet område med en flygtig formel som
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Med den nuværende opsætning ville dette helt sikkert returnere en reference til området A2: A5. Der er dog en anden måde at bruge det ikke-flygtige INDEX på. For at gøre dette skal du tænke på, at vi skriver en reference til intervallet fra A2 til A5. Når du skriver "A2: A5", skal du ikke tænke på dette som et enkelt stykke data, men snarere som et "Startpunkt" og "EndingPoint" adskilt af et kolon (f.eks. Startpunkt: EndingPoint). I en formel kan både StartPoint og EndingPoint være resultaterne af andre funktioner.
Her er formlen, vi vil bruge til at oprette et dynamisk område ved hjælp af INDEX -funktionen:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Bemærk, at vi har angivet, at Startpunktet for dette område altid vil være A2. På den anden side af tyktarmen bruger vi INDEX til at bestemme, hvor EndingPoint skal være. COUNTA bestemmer, at der er 5 celler med data i kolonne A, og derfor vil vores INDEX oprette en reference til A5. Formlen vurderes således:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Ved hjælp af denne teknik kan du dynamisk opbygge en reference til enhver liste eller endda en todimensionel tabel ved hjælp af INDEX-funktionen. I et regneark med en overflod af OFFSET -funktioner vil udskiftning af OFFSET'erne med INDEX give din computer mulighed for at begynde at køre meget hurtigere.
Udskiftning af INDIRECT for arknavne
INDIRECT -funktionen bliver ofte kaldt, når projektmapper er designet med data spredt over flere regneark. Hvis du ikke kan få alle data på et enkelt ark, men ikke vil bruge en flygtig funktion, kan du muligvis bruge VÆLG.
Overvej følgende layout, hvor vi har salgsdata på tværs af 3 forskellige regneark. På vores oversigtsark har vi valgt, hvilket kvartal vi gerne vil se dataene fra.
Vores formel i B3 er:
= VÆLG (MATCH (B2, D2: D4, 0), efterår! A2, vinter! A2, forår! A2)
I denne formel bestemmer MATCH -funktionen, hvilket område vi vil returnere. Dette fortæller derefter funktionen VÆLG, hvilket af følgende områder der skal returneres som resultat.
Du kan også bruge funktionen VÆLG for at returnere et større område. I dette eksempel har vi en tabel med salgsdata på hvert af vores tre regneark.
I stedet for at skrive en INDIRECT -funktion til at opbygge arknavnet, kan du lade VÆLG bestemme, hvilken tabel der skal søges på. I mit eksempel har jeg allerede navngivet de tre tabeller tbFall, tbWinter og tbSpring. Formlen i B4 er:
= VLOOKUP (B3, VÆLG (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
I denne formel vil MATCH bestemme, at vi vil have 2nd element fra vores liste. VÆLG tager derefter den 2 og returnerer referencen til tbWinter. Endelig vil vores VLOOKUP kunne afslutte søgningen i den givne tabel, og den vil konstatere, at det samlede salg for Banana om vinteren var $ 6000.
= VLOOKUP (B3, VÆLG (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, VÆLG (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Denne teknik er begrænset af, at du skal udfylde VÆLG -funktionen med alle de områder, du måske vil hente en værdi fra, men det giver dig fordelen ved at undgå en flygtig formel. Afhængigt af hvor mange beregninger du skal udføre, kan denne evne vise sig at være ganske værdifuld.
