Excel OFFSET-functie

De Excel-VERSCHUIVING-functie retourneert een verwijzing naar een cel of een bereik van cellen dat is verschoven ten opzichte van een bepaalde cel, met een opgegeven aantal rijen en kolommen.

Syntaxis

=OFFSET ()reference, rows, cols, [height], [width])

Argumenten

Retourneerwaarde

De VERSCHUIVING-functie retourneert een Een celverwijzing die is verschoven ten opzichte van een gegeven startpunt.

Opmerkingen bij de functie

1. Er wordt een #WAARDE!-fout geretourneerd als de verwijzing betrekking heeft op een bereik met niet-aaneengesloten cellen.
2. Er wordt een #VERWIJZING!-fout geretourneerd als de rij- en kolomverschuiving buiten het werkblad valt.

Voorbeelden

Voorbeeld 1: Basisgebruik van de VERSCHUIVING-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 geven aan dat er drie rijen omlaag en één kolom naar rechts wordt verplaatst vanaf cel B2, waardoor uiteindelijk de waarde in C5 wordt geretourneerd. Zie schermafbeelding:

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

In dit geval krijgt u het resultaat van het 2×2-bereik dat zich drie rijen onder en één kolom rechts van cel B2 bevindt.

Opmerking: U krijgt een #WAARDE!-fout als u slechts één cel selecteert om de VERSCHUIVING-functie toe te passen voor het retourneren van een bereik met meerdere cellen. Selecteer in plaats daarvan een 2×2-bereik (bijvoorbeeld 4 lege cellen), voer de formule in en druk op Ctrl+Shift+Enter om de resultaten te verkrijgen.

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

Zoals hierboven is vermeld, retourneert de VERSCHUIVING-functie, zoals =OFFSET(B2,3,1,2,2), een #WAARDE!-fout wanneer u deze in één enkele cel gebruikt. Maar zodra u de SOM- en VERSCHUIVING-functie combineert — zoals te zien is in de onderstaande schermafbeelding — krijgt u direct de som van de waarden in het bereik C5:D6.

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

Voorbeeld 3: Kolommen optellen op basis van bepaalde criteria

Zoals in de onderstaande schermafbeelding wordt weergegeven, hoe kunt u de totale verkoop van Mango van zondag tot en met dinsdag verkrijgen? Probeer het volgende.

1. Selecteer een lege cel, kopieer de onderstaande formule naar die cel en druk op Enterom het resultaat te verkrijgen.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Opmerkingen:

1. In de bovenstaande formule zoekt MATCH(G4,B3:B8) naar ‘Mango’ en retourneert de positie ervan in het bereik B3:B8. Mango staat vijf rijen onder cel B2, dus wordt het getal 5 geretourneerd;
2. Omdat de VERGELIJKEN-functie 5 oplevert en de laatste komma hier 0 vertegenwoordigt, wordt de VERSCHUIVING-functie nu weergegeven als OFFSET(C2:E2,5,0). Dit betekent dat vanaf het bereik C2:E2 vijf rijen omlaag en nul kolommen naar rechts wordt verschoven, zodat het bereik C7:E7 wordt gerefereerd;
3. Vervolgens wordt de SOM-functie weergegeven als SUM(C7:E7) en retourneert uiteindelijk de som van de waarden in het bereik C7:E7.

Meer voorbeelden

Hoe telt u elke n-de rij omlaag op in Excel?

Hoe maakt u een dynamisch benoemd bereik in Excel?

Hoe haalt u de celwaarde uit elke vijfde of n-de rij in Excel?