De OFFSET-functie van Excel vertraagt de berekening van uw werkmap. Er is een beter alternatief: een ongebruikelijke syntaxis van INDEX.
Dit is een niche-tip. Er is een verbazingwekkend flexibele functie genaamd OFFSET. Het is flexibel omdat het kan verwijzen naar een bereik van verschillende afmetingen dat on-the-fly wordt berekend. Als iemand in de onderstaande afbeelding de vervolgkeuzelijst # Qtrs in H1 wijzigt van 3 in 4, zorgt het vierde argument van OFFSET ervoor dat het bereik wordt uitgebreid met vier kolommen.
Spreadsheetgoeroes haten OFFSET omdat het een vluchtige functie is. Als u naar een volledig niet-gerelateerde cel gaat en een getal invoert, worden alle OFFSET-functies berekend. Zelfs als die cel niets te maken heeft met H1 of B2. Meestal is Excel erg voorzichtig om alleen tijd te verspillen aan het berekenen van de cellen die moeten worden berekend. Maar zodra u OFFSET introduceert, beginnen alle OFFSET-cellen, plus alles downline van de OFFSET, te rekenen na elke wijziging in het werkblad.
Ik was jurylid van de ModelOff-finale van 2013 in New York City toen een paar van mijn vrienden uit Australië een bizarre oplossing noemden. In de onderstaande formule staat er een dubbele punt voor de functie INDEX. Normaal gesproken retourneert de hieronder getoonde INDEX-functie de 1403 uit cel D2. Maar als u aan beide kanten van de INDEX-functie een dubbele punt plaatst, begint deze het celadres D2 te retourneren in plaats van de inhoud van D2. Dit is wild dat het werkt.
Waarom maakt dit uit? INDEX is niet vluchtig. U krijgt alle flexibele goedheid van OFFSET zonder de tijdrovende herberekeningen keer op keer.
Ik leerde deze tip voor het eerst van Dan Mayoh bij Fintega. Met dank aan Access Analytic voor het suggereren van deze functie.
Bekijk video
Videotranscriptie
Leer Excel van podcast, aflevering 2048 -: INDEX zal een vluchtige OFFSET vervangen!
Hé, ik podcast al mijn tips uit dit boek, klik op die "i" in de rechterbovenhoek om naar de afspeellijst te gaan!
Oké, OFFSET is een geweldige functie! Met OFFSET kunnen we een cel in de linkerbovenhoek specificeren en vervolgens variabelen gebruiken om van daaruit te bepalen hoeveel rijen naar beneden, hoeveel rijen erboven, en dan een vorm te definiëren, oké. Dus hier, als ik wil optellen, of een gemiddelde wil doen van, laten we zeggen 3/4, zal deze formule hier de formule bekijken. De OFFSET zegt dat we beginnen bij B2, beginnend bij Q1, we gaan 0 omlaag, meer dan 0, de vorm zal 1 rij lang zijn en het zal H1 zijn, met andere woorden 3 cellen breed, oké. Dus in dit geval is alles wat we echt doen veranderen hoeveel cellen we optellen, we beginnen altijd terug in B2, of B3 of B4 terwijl ik kopieer, en dan beslist het hoeveel cellen breed zijn. Oké, OFFSET is zo cool, het heeft allerlei geweldige functies, maar hier is het gedoe, het is vluchtig!Wat betekent dat zelfs als iets niet in de rekenketen zit, Excel de tijd zal nemen om het opnieuw te berekenen, wat de zaken zal vertragen, oké.
Deze geweldige versie van INDEX, klopt, normaal gesproken als ik zou vragen om de INDEX van deze 4 cellen, 3, zal het het nummer 1403 retourneren.Wanneer ik echter een dubbele punt voor of na de INDEX plaats, gebeurt er iets heel anders, we zullen deze formule hier bekijken. Dus index van de 4 cellen, welke wil ik, ik wil de derde, maar je ziet dat er een: daar is. Dus we gaan altijd van B2: E2, en ik zal je laten zien of we naar Formules gaan, Formule evalueren, oké, dus hier gaat het het getal 3 berekenen. En hier, de INDEX met de: volgende in plaats van ons 1403 te vertellen, zoals INDEX normaal zou berekenen, zal het $ D2 opleveren, en dan zal het GEMIDDELDE het gemiddelde van die drie doen. Absoluut verbazingwekkend, de manier waarop dit werkt, en het extra voordeel, het is niet vluchtig, oké,en dit kan zelfs worden gebruikt om een ongelooflijk complexe OFFSET te vervangen.
Dus hier met deze OFFSET zijn we gebaseerd op deze waarden. Als ik Q2 en Centraal kies, oké, gebruiken deze formules MATCH en AANTAL.ALS om erachter te komen hoeveel rijen naar beneden, hoeveel kolommen er over, hoe hoog en hoe breed. En dan gebruikt de VERSCHUIVING hier al die waarden om de mediaan te berekenen. We doen gewoon een test om er zeker van te zijn dat het werkt, dus = MEDIAAN van dat blauwe bereik daar, of beter 71, oké, en we zullen hier iets anders kiezen, Q3 en West, dus. Druk op F2, ik ga dit gewoon slepen en het formaat wijzigen om die formule te herschrijven, druk op Enter en het werkt met de OFFSET.
Welnu, met behulp van een INDEX heb ik eigenlijk een dubbele punt in het midden met een INDEX aan de linkerkant en een INDEX aan de rechterkant, kijk hoe dit ding wordt berekend in Formule evalueren. Dus het begint, zal evalueren, evalueren, en hier staat INDEX op het punt om er een celadres van te maken. Dus H16 is de 1e, West, Q3, en aan de rechterkant zal evalueren, meer koppelen, en dan rechts verandert het in I20. Dus het koele, koele niet-vluchtige om een OFFSET te vervangen met behulp van de INDEX-functie. Oké, deze tip en nog veel meer in dit boek, klik op de "i" in de rechterbovenhoek om het boek te kopen.
Oké samenvatting: OFFSET, geweldige, flexibele functie, als je het eenmaal onder de knie hebt, kun je van alles doen. Wijs een variabele cel linksboven aan, wijs een bereik aan met een variabele vorm, maar het is vluchtig, en dus berekenen ze bij elke berekening. Excel voert meestal een slimme berekening uit, of het herberekent de cellen die moeten worden berekend, maar met OFFSET wordt het altijd berekend. In plaats van OFFSET kunt u INDEX: of: INDEX of zelfs INDEX: INDEX gebruiken, elke keer dat INDEX een dubbele punt ernaast heeft, zal het een celadres retourneren in plaats van de waarde van die cel. En het voordeel is dat INDEX niet vluchtig is!
OK, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2048.xlsm
