Ladda ner exempelarbetsboken
Vi har diskuterat i andra artiklar om hur det finns funktioner som OFFSET och INDIRECT som är flyktiga. Om du börjar använda många av dessa i ett kalkylblad eller har många celler beroende på flyktig funktion, kan du få din dator att lägga märkbar tid på att göra omberäkningar varje gång du försöker byta cell. Istället för att bli frustrerad över hur din dator inte är tillräckligt snabb, kommer den här artikeln att utforska alternativa sätt att lösa de vanliga situationer som människor använder OFFSET och INDIRECT.
Ersätta OFFSET för att skapa en dynamisk lista
Efter att ha lärt sig om OFFSET -funktionen är det en vanlig missuppfattning att det är det enda sättet att returnera ett resultat med dynamisk storlek med de senaste par argumenten. Låt oss titta på en lista i kolumn A där vår användare senare kan bestämma sig för att lägga till ytterligare objekt.
För att göra en nedrullning i cell C2 kan du definiera ett namngivet område med en flyktig formel som
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Med den nuvarande inställningen skulle detta säkert ge en referens till intervallet A2: A5. Det finns dock ett annat sätt att använda den icke-flyktiga INDEX. För att göra detta, tänk på att vi skriver en referens till intervallet från A2 till A5. När du skriver "A2: A5", tänk inte på detta som en enda bit av data, utan snarare som ett "Startpunkt" och "Slutpunkt" åtskilt av ett kolon (t.ex. Startpunkt: EndingPoint). I en formel kan både StartPoint och EndingPoint vara resultatet av andra funktioner.
Här är formeln vi kommer att använda för att skapa dynamiskt intervall med INDEX -funktionen:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Observera att vi har uppgett att Startpunkten för detta intervall alltid kommer att vara A2. På andra sidan kolon, använder vi INDEX för att avgöra var EndingPoint ska vara. COUNTA kommer att bestämma att det finns 5 celler med data i kolumn A, så vår INDEX kommer att skapa en referens till A5. Formeln utvärderas således:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Med denna teknik kan du dynamiskt bygga en referens till valfri lista eller till och med en tvådimensionell tabell med INDEX-funktionen. I ett kalkylblad med ett överflöd av OFFSET -funktioner kommer ersättning av OFFSETs med INDEX att göra att din dator kan börja köra mycket snabbare.
Ersätter INDIRECT för bladnamn
INDIRECT -funktionen kallas ofta när arbetsböcker har utformats med data spridda över flera kalkylblad. Om du inte kan få all data på ett enda blad, men inte vill använda en flyktig funktion, kan du kanske använda VÄLJ.
Tänk på följande layout, där vi har försäljningsdata i tre olika kalkylblad. På vårt sammanfattningsblad har vi valt vilket kvartal vi vill se data från.
Vår formel i B3 är:
= VÄLJ (MATCH (B2, D2: D4, 0), höst! A2, vinter! A2, vår! A2)
I denna formel kommer MATCH -funktionen att bestämma vilket område vi vill returnera. Detta berättar sedan för VÄLJ -funktionen vilket av följande intervall som ska returneras som resultat.
Du kan också använda funktionen VÄLJ för att returnera ett större intervall. I det här exemplet har vi en tabell med försäljningsdata på vart och ett av våra tre kalkylblad.
I stället för att skriva en INDIRECT -funktion för att bygga bladets namn kan du låta VÄLJA bestämma vilken tabell du ska göra sökningen på. I mitt exempel har jag redan namngett de tre tabellerna tbFall, tbWinter och tbSpring. Formeln i B4 är:
= VLOOKUP (B3, VÄLJ (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
I denna formel kommer MATCH att bestämma att vi vill ha 2nd objekt från vår lista. VÄLJ tar sedan det 2 och returnerar referensen till tbWinter. Slutligen kommer vår VLOOKUP att kunna slutföra sökningen i tabellen, och den kommer att upptäcka att den totala försäljningen för Banana på vintern var $ 6000.
= VLOOKUP (B3, VÄLJ (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, VÄLJ (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Denna teknik begränsas av det faktum att du måste fylla i VÄLJ -funktionen med alla områden du kanske vill hämta ett värde från, men det ger dig fördelen att undvika en flyktig formel. Beroende på hur många beräkningar du behöver slutföra kan denna förmåga visa sig vara ganska värdefull.
