Excel OFFSET-functie (voorbeeld + video)
Wanneer Excel OFFSET-functie gebruiken?
Excel OFFSET-functie kan worden gebruikt wanneer u een referentie wilt krijgen die het opgegeven aantal rijen en kolommen vanaf het startpunt compenseert.
Wat het teruggeeft
Het retourneert de referentie waarnaar de functie OFFSET verwijst.
Syntaxis
=OFFSET(referentie, rijen, kolommen, [hoogte], [breedte])
Invoerargumenten
- referentie - De referentie waarvan u wilt compenseren. Dit kan een celverwijzing zijn of een reeks aangrenzende cellen.
- rijen - het aantal te compenseren rijen. Als u een positief getal gebruikt, wordt dit verschoven naar de rijen eronder, en als een negatief getal wordt gebruikt, wordt dit verschoven naar de rijen erboven.
- cols - het aantal te verschuiven kolommen. Als u een positief getal gebruikt, wordt dit verschoven naar de kolommen aan de rechterkant, en als een negatief getal wordt gebruikt, wordt dit verschoven naar de kolommen aan de linkerkant.
- [hoogte] - dit is een getal dat het aantal rijen in de geretourneerde referentie vertegenwoordigt.
- [breedte] - dit is een getal dat het aantal kolommen in de geretourneerde referentie vertegenwoordigt.
De basisprincipes van Excel OFFSET-functie begrijpen
Excel OFFSET-functie is mogelijk een van de meest verwarrende functies in Excel.
Laten we een eenvoudig voorbeeld van een schaakspel nemen. Er is een stuk in Schaken dat een toren wordt genoemd (ook bekend als een toren, markies of rector).
[Afbeelding met dank aan: Prachtige Wikipedia]
Nu, zoals alle andere schaakstukken, heeft een toren een vast pad waarop hij over het bord kan bewegen. Het kan rechtdoor gaan (naar rechts/links of omhoog/omlaag op het bord), maar het kan niet diagonaal.
Stel dat we bijvoorbeeld een schaakbord hebben in Excel (zoals hieronder getoond), in een bepaald vak (D5 in dit geval), kan de toren alleen in de vier gemarkeerde richtingen gaan.
Als ik je nu vraag om de toren van zijn huidige positie naar de geel gemarkeerde positie te brengen, wat zeg je dan tegen de toren?
Je zult hem vragen om twee stappen naar beneden en twee stappen naar rechts te doen … is het niet?
En dat is een goede benadering van hoe de OFFSET-functie werkt.
Laten we nu eens kijken wat dit betekent in Excel. Ik wil beginnen met de cel D5 (waar de toren is) en dan twee rijen naar beneden en twee kolommen naar rechts gaan en de waarde daar uit de cel halen.
De formule zou zijn:
=OFFSET (van waar te beginnen, hoeveel rijen naar beneden, hoeveel kolommen naar rechts)
Zoals u kunt zien, is de formule in het bovenstaande voorbeeld in cel J1 =OFFSET(D5,2,2).
Het begon bij D5 en ging toen twee rijen naar beneden en twee kolommen naar rechts en bereikte de cel F7. Het retourneerde vervolgens de waarde in cel F7.
In het bovenstaande voorbeeld hebben we gekeken naar de OFFSET-functie met 3 argumenten. Maar er zijn nog twee optionele argumenten die kunnen worden gebruikt.
Laten we hier een eenvoudig voorbeeld bekijken:
Stel dat u de verwijzing naar cel A1 (in geel) wilt gebruiken en dat u in een formule wilt verwijzen naar het hele bereik dat blauw is gemarkeerd (C2:E4).
Hoe zou je dat doen met een toetsenbord? U zou eerst naar cel C2 gaan en vervolgens alle cellen in C2:E4 selecteren.
Laten we nu kijken hoe we dit kunnen doen met behulp van de OFFSET-formule:
=VERSCHUIVING(A1,1,2,3,3)
Als u deze formule in een cel gebruikt, wordt een #WAARDE! fout, maar als u in de bewerkingsmodus komt en de formule selecteert en op F9 drukt, ziet u dat alle waarden worden geretourneerd die blauw zijn gemarkeerd.
Laten we nu eens kijken hoe deze formule werkt:
=VERSCHUIVING(A1,1,2,3,3)
- Het eerste argument is de cel waar het moet beginnen.
- Het tweede argument is 1, wat Excel vertelt om een verwijzing te retourneren die met 1 rij is UITGESCHAKELD.
- Het derde argument is 2, wat Excel vertelt om een verwijzing terug te geven die met 2 kolommen is UITGESCHAKELD.
- Het vierde argument is 3. Dit geeft aan dat de verwijzing 3 rijen moet beslaan. Dit wordt het hoogteargument genoemd.
- Het vijfde argument is 3. Dit geeft aan dat de verwijzing 3 kolommen moet beslaan. Dit wordt het breedte-argument genoemd.
Nu u de verwijzing naar een celbereik (C2:E4) hebt, kunt u deze binnen andere functies gebruiken (zoals SUM, COUNT, MAX, AVERAGE).
Hopelijk heb je een goed basisbegrip van het gebruik van de Excel OFFSET-functie. Laten we nu eens kijken naar enkele praktische voorbeelden van het gebruik van de OFFSET-functie.
Excel OFFSET-functie - voorbeelden
Hier zijn twee voorbeelden van het gebruik van de Excel OFFSET-functie.
Voorbeeld 1 - De laatst gevulde cel in de kolom vinden
Stel dat u enkele gegevens in een kolom hebt, zoals hieronder weergegeven:
Gebruik de volgende formule om de laatste cel in de kolom te vinden:
=OFFSET(A1,COUNT(A:A)-1,0)
Deze formule gaat ervan uit dat er geen andere waarden zijn dan de weergegeven waarden en dat deze cellen geen lege cel bevatten. Het werkt door het totale aantal cellen te tellen dat is gevuld en compenseert de cel A1 dienovereenkomstig.
In dit geval zijn er bijvoorbeeld 8 waarden, dus COUNT(A:A) retourneert 8. We verschuiven de cel A1 met 7 om de laatste waarde te krijgen.
Voorbeeld 2 - Een dynamische vervolgkeuzelijst maken
U kunt de conceptshows in voorbeeld 1 uitbreiden om dynamische benoemde bereiken te maken. Deze kunnen handig zijn als u de benoemde bereiken gebruikt in formules of bij het maken van vervolgkeuzemenu's en u waarschijnlijk gegevens toevoegt of verwijdert uit de verwijzing die het benoemde bereik maakt.
Hier is een voorbeeld:
Houd er rekening mee dat de vervolgkeuzelijst automatisch wordt aangepast wanneer het jaar wordt toegevoegd of verwijderd.
Dit gebeurt omdat de formule die wordt gebruikt om de vervolgkeuzelijst te maken dynamisch is en elke toevoeging of verwijdering identificeert en het bereik dienovereenkomstig aanpast.
Hier is hoe dit te doen:
- Selecteer de cel waar u de vervolgkeuzelijst wilt invoegen.
- Ga naar Gegevens -> Gegevenshulpmiddelen -> Gegevensvalidatie.
- Selecteer in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen Lijst in de vervolgkeuzelijst.
- Voer in de bron de volgende formule in: =OFFSET(A1,0,0,COUNT(A:A),1)
- Klik OK.
Laten we eens kijken hoe deze formule werkt:
- De eerste drie argumenten van de functie OFFSET zijn A1, 0 en 0. Dit betekent in wezen dat het dezelfde referentiecel zou retourneren (dit is A1).
- Het vierde argument is voor hoogte en hier retourneert de COUNT-functie het totale aantal items in de lijst. Het gaat ervan uit dat er geen lege plekken in de lijst zijn.
- Het vijfde argument is 1, wat aangeeft dat de kolombreedte één moet zijn.
Extra notities:
- OFFSET is een vluchtige functie (voorzichtig gebruiken).
- Het herberekent telkens wanneer de Excel-werkmap is geopend of wanneer een berekening wordt geactiveerd in het werkblad. Dit kan de verwerkingstijd verlengen en uw werkmap vertragen.
- Als de hoogte- of breedtewaarde wordt weggelaten, wordt deze beschouwd als die van de referentie.
- Indien rijen en cols zijn negatieve getallen, dan wordt de offset-richting omgekeerd.
Excel OFFSET Functie Alternatieven
Vanwege enkele van de beperkingen van de Excel OFFSET-functie, willen velen er alternatieven voor overwegen:
- INDEX-functie: de INDEX-functie kan ook worden gebruikt om een celverwijzing te retourneren. Klik hier om een voorbeeld te zien van hoe u een dynamisch benoemd bereik kunt maken met behulp van de INDEX-functie.
- Excel-tabel: als u gestructureerde verwijzingen in Excel-tabel gebruikt, hoeft u zich geen zorgen te maken over het toevoegen van nieuwe gegevens en de noodzaak om de formules aan te passen.
Excel OFFSET-functie - Video-tutorial
- Excel VERT.ZOEKEN Functie.
- Excel HORIZ.ZOEKEN-functie.
- Excel INDEX-functie.
- Excel INDIRECTE Functie.
- Excel MATCH-functie.
