Excel OFFSET Functie

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

Syntaxis

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

argumenten

Winstwaarde

De OFFSET-functie retourneert een celverwijzingsverschuiving vanaf een bepaald startpunt.

Functie-opmerkingen

1. #WAARDE! foutwaarde wordt geretourneerd wanneer de verwijzing verwijst naar een reeks onderbroken cellen.
2. #REF! foutwaarde wordt geretourneerd wanneer de rijen en kolommen de verwijzing naar de rand van het werkblad verschuiven.

Voorbeelden

Voorbeeld 1: basisgebruik voor de OFFSET-functie

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

In dit geval is B2 het startpunt, nummer 3 en 1 betekenen dat je 3 rijen naar beneden en 1 kolom naar rechts verplaatst vanuit cel B2, en als laatste de waarde in C5 retourneert. Zie screenshot:

Retourneer een verwijzing naar een celbereik met de onderstaande formule:
=OFFSET(B2,3,1,2,2)

In dit geval krijgt u de resultaten van een 2 x 2 bereik dat 3 rijen onder en 1 kolom rechts van cel B2 is.

Note: #WAARDE! Er treedt een fout op wanneer u slechts één cel selecteert om de OFFSET-functie toe te passen voor het retourneren van een celbereik. U moet een bereik van 2 x 2 selecteren (zegt 4 lege cellen), voer de formule in en druk op Ctrl + Shift + Enter toetsen om de resultaten te krijgen.

Voorbeeld 2: gebruik de functie OFFSET om een ​​reeks waarden op te tellen

Zoals we hierboven vermeldden, als u de OFFSET-functie probeert te gebruiken, zoals =OFFSET(B2,3,1,2,2) op zichzelf in een enkele cel, retourneert het een #WAARDE! Fout. Als u echter de functie SOM en VERSCHUIVING combineert zoals hieronder afgebeeld, wordt de som van de waarden in het bereik C5: D6 rechtstreeks geretourneerd.

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

Voorbeeld 3: Somkolommen op basis van bepaalde criteria

Zoals onderstaand screenshot laat zien, hoe krijg je de totale verkoop van Mango van zon tot dinsdag? Probeer het als hieronder.

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

Notes:

1. In de bovenstaande formule, MATCH(G4,B3:B8) is op zoek naar Mango en komt terug in het assortiment B3: B8. Laten we eens kijken, Mango lokaliseert 5 rijen onder cel B2, dus het retourneert het getal 5;
2. Omdat het MATCH-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 offset begint vanaf bereik C2: E2, verplaats 5 rijen naar beneden en 0 kolom naar rechts om de verwijzing naar bereik C7: E7 te krijgen;
3. Vervolgens wordt de SOM-functie weergegeven als SUM(C7:E7), en retourneer tenslotte de som van de waarden in bereik C7: E7.

Meer voorbeelden

Hoe som je elke n rijen op in Excel?

Hoe maak je een dynamisch benoemd bereik in Excel?

Hoe celwaarde elke vijf of n-de rij in Excel retourneren?