Download het voorbeeldwerkboek
We hebben in andere artikelen besproken hoe er functies zoals OFFSET en INDIRECT zijn die vluchtig zijn. Als u veel van deze in een spreadsheet gaat gebruiken of als veel cellen afhankelijk zijn van de vluchtige functie, kunt u ervoor zorgen dat uw computer veel tijd besteedt aan herberekeningen telkens wanneer u een cel probeert te wijzigen. In plaats van gefrustreerd te raken over het feit dat uw computer niet snel genoeg is, onderzoekt dit artikel alternatieve manieren om de veelvoorkomende situaties op te lossen die mensen OFFSET en INDIRECT gebruiken.
OFFSET vervangen om een dynamische lijst te maken
Na het leren over de OFFSET-functie, is het een veel voorkomende misvatting dat dit de enige manier is om een resultaat met dynamische grootte te retourneren met behulp van de laatste paar argumenten. Laten we eens kijken naar een lijst in kolom A waar onze gebruiker later zou kunnen besluiten om extra items toe te voegen.
Als u een vervolgkeuzelijst in cel C2 wilt maken, kunt u een benoemd bereik definiëren met een vluchtige formule zoals
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1)
Met de huidige opzet zou dit zeker een verwijzing naar het bereik A2:A5 opleveren. Er is echter een andere manier om de niet-vluchtige INDEX te gebruiken. Om dit te doen, denk erover na dat we een verwijzing schrijven naar het bereik van A2 tot A5. Wanneer u "A2:A5" schrijft, beschouw dit dan niet als een enkel stuk gegevens, maar eerder als een "StartingPoint" en "EndingPoint" gescheiden door een dubbele punt (bijv. StartingPoint:EndingPoint). In een formule kunnen zowel het Startpunt als het Eindpunt het resultaat zijn van andere functies.
Dit is de formule die we zullen gebruiken om een dynamisch bereik te creëren met behulp van de INDEX-functie:
=$A$2:INDEX($A:$A, COUNTA($A:$A))
Merk op dat we hebben aangegeven dat het uitgangspunt voor dit bereik altijd A2 zal zijn. Aan de andere kant van de dubbele punt gebruiken we INDEX om te bepalen waar het EndingPoint zal zijn. De COUNTA zal bepalen dat er 5 cellen met gegevens in kolom A zijn, en dus zal onze INDEX een verwijzing naar A5 creëren. De formule wordt dus als volgt geëvalueerd:
=$A$2:INDEX($A:$A, COUNTA($A:$A)) =$A$2:INDEX($A:$A, 5) =$A$2:$A5
Met behulp van deze techniek kunt u dynamisch een verwijzing maken naar een lijst, of zelfs een tweedimensionale tabel met behulp van de INDEX-functie. In een spreadsheet met een overvloed aan OFFSET-functies, zal het vervangen van de OFFSET's door INDEX uw computer veel sneller laten werken.
INDIRECT vervangen door bladnamen
De INDIRECT-functie wordt vaak aangeroepen wanneer werkmappen zijn ontworpen met gegevens verspreid over meerdere werkbladen. Als u niet alle gegevens op één blad kunt krijgen, maar geen vluchtige functie wilt gebruiken, kunt u misschien KIEZEN gebruiken.
Overweeg de volgende lay-out, waar we verkoopgegevens hebben over 3 verschillende werkbladen. Op ons overzichtsblad hebben we geselecteerd van welk kwartaal we de gegevens willen bekijken.
Onze formule in B3 is:
=KIEZEN(WEDSTRIJD(B2, D2:D4, 0), Herfst!A2, Winter!A2, Lente!A2)
In deze formule gaat de MATCH-functie bepalen welk gebied we willen retourneren. Dit vertelt vervolgens de CHOOSE-functie welke van de volgende bereiken als resultaat moet worden geretourneerd.
U kunt ook de functie KIEZEN gebruiken om een groter bereik te retourneren. In dit voorbeeld hebben we een tabel met verkoopgegevens op elk van onze drie werkbladen.
In plaats van een INDIRECT-functie te schrijven om de bladnaam te bouwen, kunt u KIEZEN laten bepalen in welke tabel u wilt zoeken. In mijn voorbeeld heb ik de drie tabellen al tbFall, tbWinter en tbSpring genoemd. De formule in B4 is:
=VLOOKUP(B3, CHOOSE(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
In deze formule gaat de MATCH bepalen dat we de 2 . willennd item uit onze lijst. CHOOSE zal dan die 2 nemen en de referentie terugsturen naar tbWinter. Ten slotte zal onze VERT.ZOEKEN in staat zijn om de zoekopdracht in de gegeven tabel te voltooien, en het zal vinden dat de totale verkoop voor Banana in de winter $ 6000 was.
=VLOOKUP(B3, CHOOSE(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0) =VLOOKUP(B3, CHOOSE(2, tbFall, tbWinter, tbSpring), 2, 0) = VERT.ZOEKEN(B3, tbWinter, 2, 0) =6000
Deze techniek wordt beperkt door het feit dat je de CHOOSE-functie moet invullen met alle gebieden waaruit je een waarde wilt halen, maar het geeft je wel het voordeel dat je een vluchtige formule vermijdt. Afhankelijk van het aantal berekeningen dat u moet voltooien, kan deze vaardigheid behoorlijk waardevol blijken te zijn.
