Excels OFFSET-funktion saktar ner din arbetsboks beräkning. Det finns ett bättre alternativ: en ovanlig syntax för INDEX.
Detta är ett nischtips. Det finns en otroligt flexibel funktion som kallas OFFSET. Den är flexibel eftersom den kan peka på ett intervall av olika storlek som beräknas direkt. I bilden nedan, om någon ändrar rullgardinsmenyn # Qtrs i H1 från 3 till 4, kommer det fjärde argumentet i OFFSET att se till att intervallet utvidgas till att omfatta fyra kolumner.
Kalkylarkguruer hatar OFFSET eftersom det är en flyktig funktion. Om du går till en helt orelaterad cell och anger ett nummer beräknas alla OFFSET-funktionerna. Även om den cellen inte har något att göra med H1 eller B2. För det mesta är Excel mycket noga med att bara slösa bort tid på att beräkna cellerna som behöver beräknas. Men när du väl har infört OFFSET börjar alla OFFSET-celler, plus allt downline från OFFSET, beräkna efter varje ändring i kalkylbladet.
Jag bedömde 2013 års ModelOff-finaler i New York City när några av mina vänner från Australien påpekade en bisarr lösning. I formeln nedan finns det ett kolon före INDEX-funktionen. Normalt skulle INDEX-funktionen som visas nedan returnera 1403 från cell D2. Men när du sätter ett kolon på vardera sidan av INDEX-funktionen, börjar det returnera celladressen D2 istället för innehållet i D2. Det här är vildt att det fungerar.
Varför spelar det någon roll? INDEX är inte flyktigt. Du får all den flexibla godheten hos OFFSET utan tidssugande omberäkningar om och om igen.
Jag fick först detta tips från Dan Mayoh på Fintega. Tack till Access Analytic för att du föreslog den här funktionen.
Kolla på video
Videoutskrift
Lär dig Excel från podcast, avsnitt 2048 -: INDEX ersätter en flyktig OFFSET!
Hej, jag podcastar alla mina tips från den här boken, klicka på det "i" i det övre högra hörnet för att komma till spellistan!
Okej, OFFSET är en fantastisk funktion! OFFSET låter oss ange en cell i det övre vänstra hörnet och sedan använda variabler för att definiera därifrån hur många rader nedåt, hur många rader över och sedan definiera en form, okej. Så här, om jag vill lägga till eller göra ett genomsnitt på, låt oss säga 3/4, kommer denna formel här att titta på formeln. OFFSET säger att vi börjar från B2, börjar från Q1, vi går ner 0, över 0, formen kommer att vara 1 rad hög och den kommer att vara H1, med andra ord 3 celler breda, okej. Så i det här fallet är allt vi verkligen gör att ändra hur många celler vi lägger till, vi börjar alltid tillbaka i B2, eller B3 eller B4 när jag kopierar ner, och sedan bestämmer det hur många celler breda. Okej, OFFSET är den här coola saken, den gör alla möjliga fantastiska funktioner, men här är besväret, det är flyktigt!Vilket innebär att även om något inte finns i beräkningskedjan, Excel, kommer det att ta sig tid att räkna om det, vilket kommer att sakta ner saker, okej.
Den här fantastiska versionen av INDEX, rätt, normalt om jag skulle be om INDEX för dessa 4 celler, 3, kommer den att returnera siffran 1403. Men när jag sätter ett kolon före eller efter INDEX, händer något väldigt annorlunda, vi tar en titt på denna formel här. Så indexera de 4 cellerna, vilken vill jag ha, jag vill ha den tredje, men du ser att det finns en: precis där. Så vi kommer alltid att gå från B2: E2, och jag visar dig om vi går till formler, utvärderar formel, okej, så här kommer det att beräkna antalet 3. Och här, INDEX med: nästa till det, istället för att berätta 1403, vilket är hur INDEX normalt skulle beräkna, kommer det att returnera $ D2, och då kommer GENOMSNITTET att göra genomsnittet av dessa 3. Helt fantastiskt, hur detta fungerar, och den extra fördelen, det är inte flyktigt, okej,och detta kan till och med användas för att ersätta en otroligt komplex OFFSET.
Så här med denna OFFSET baserar vi oss på dessa värden. Om jag väljer Q2 och Central, okej, använder dessa formler MATCH och COUNTIF för att räkna ut hur många rader nedåt, hur många kolumner över, hur långa, hur breda. Och sedan använder OFFSET här alla dessa värden för att räkna ut medianen. Vi gör bara ett test för att se till att det fungerar, så = MEDIAN i det blå intervallet där borta, var bättre 71, okej, och vi väljer något annat här, Q3 och West, så. Tryck på F2, jag ska bara dra det här och ändra storlek på det för att skriva om den formeln, tryck på Enter och det fungerar med OFFSET.
Tja här, med hjälp av ett INDEX, har jag faktiskt ett kolon i mitten med ett INDEX på vänster sida och ett INDEX på höger sida, se den här saken beräknas i Evaluation Formula. Så det börjar, kommer att utvärdera, utvärdera och just här kommer INDEX att göra det till en celladress. Så H16 är den första, West, Q3, och över på höger sida kommer att utvärdera, para mer, och sedan höger henne ändras det till I20. Så det coola, coola, icke-flyktiga för att ersätta en OFFSET med INDEX-funktionen. Okej, detta tips och många fler i den här boken, klicka på det "i" i det övre högra hörnet för att köpa boken.
Okej sammanfattning: OFFSET, fantastisk, flexibel funktion, när du förstår kan du göra alla möjliga saker. Peka på en variabel uppe till vänster, peka på ett område som har en variabel form, men den är flyktig, och så beräknar de vid varje beräkning. Excel gör vanligtvis en smart beräkning, eller det beräknar cellerna som behöver beräknas på nytt, men med OFFSET blir det alltid beräknat. Istället för OFFSET kan du använda INDEX: eller: INDEX eller till och med INDEX: INDEX, närhelst INDEX har ett kolon bredvid kommer det att returnera en celladress istället för värdet på den cellen. Och fördelen är INDEX är inte flyktig!
Okej, jag vill tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2048.xlsm
