Excel OFFSET-functie

De Excel OFFSET-functie retourneert een verwijzing naar een cel of een bereik van cellen dat is verschoven ten opzichte van een specifieke cel met een bepaald aantal rijen en kolommen.

Syntaxis

=OFFSET(referentie, rijen, kolommen, [hoogte], [breedte])

Argumenten

Retourwaarde

De OFFSET-functie retourneert een celverwijzing die is verschoven vanaf een gegeven startpunt.

Functienotities

1. #WAARDE! foutmelding wordt geretourneerd wanneer de referentie verwijst naar een bereik van niet-aaneengesloten cellen.
2. #VERW! foutmelding wordt geretourneerd wanneer de rij- en kolomverschuiving buiten de rand van het werkblad valt.

Voorbeelden

Voorbeeld 1: Basisgebruik van de OFFSET-functie

Retourneer een verwijzing naar een cel met de volgende formule:
=OFFSET(B2,3,1)

In dit geval is B2 het startpunt, de getallen 3 en 1 betekenen dat je 3 rijen omlaag en 1 kolom naar rechts beweegt vanaf cel B2, en uiteindelijk de waarde in C5 retourneert. Zie screenshot:

Retourneer een verwijzing naar een bereik van cellen met de volgende formule:
=OFFSET(B2,3,1,2,2)

In dit geval krijg je de resultaten van een 2 x 2 bereik dat 3 rijen onder en 1 kolom rechts van cel B2 ligt.

Opmerking: #WAARDE! Fout treedt op wanneer je slechts één cel selecteert om de OFFSET-functie toe te passen voor het retourneren van een bereik van cellen. Je moet een 2 x 2 bereik selecteren (bijvoorbeeld 4 lege cellen), de formule invoeren en op Ctrl + Shift + Enter drukken om de resultaten te krijgen.

Voorbeeld 2: Gebruik de OFFSET-functie om een bereik van waarden op te tellen

Zoals hierboven vermeld, als je probeert de OFFSET-functie zoals =OFFSET(B2,3,1,2,2) op zichzelf in een enkele cel te gebruiken, zal het een #WAARDE! fout retourneren. Echter, als je de SOM en OFFSET-functies combineert zoals in de onderstaande screenshot, zal het direct de som van de waarden in bereik C5:D6 retourneren.

1. Selecteer een lege cel, kopieer de onderstaande formule erin en druk op de Enter toets om het resultaat te krijgen.
=SOM(OFFSET(B2,3,1,2,2)))

Voorbeeld 3: Kolommen optellen op basis van bepaalde criteria

Zoals in de onderstaande screenshot te zien is, hoe krijg je de totale verkoop van Mango van zondag tot dinsdag? Probeer het als volgt.

1. Selecteer een lege cel, kopieer de onderstaande formule erin en druk op de Enter toets om het resultaat te krijgen.
=SOM(OFFSET(C2:E2;VERGELIJKEN(G4;B3:B8;);))

Opmerkingen:

1. In de bovenstaande formule VERGELIJKEN(G4;B3:B8) zoekt naar Mango en retourneert de positie ervan in het bereik B3:B8. Laten we kijken, Mango bevindt zich 5 rijen onder cel B2, dus retourneert het het getal 5;
2. Aangezien het VERGELIJK-resultaat 5 is, en de laatste komma hier staat voor 0, wordt de OFFSET-functie nu weergegeven als OFFSET(C2:E2;5;0), wat betekent dat de verschuiving begint vanaf bereik C2:E2, 5 rijen naar beneden en 0 kolommen naar rechts verplaatst om de verwijzing naar bereik C7:E7 te krijgen;
3. Vervolgens wordt de SOM-functie weergegeven als SOM(C7:E7), en retourneert uiteindelijk de som van de waarden in bereik C7:E7.

Meer Voorbeelden

Hoe tel je elke n rijen naar beneden op in Excel?

Hoe maak je een dynamisch benoemd bereik in Excel?

Hoe retourneer je een celwaarde elke vijf of elke n-de rij in Excel?