Hoe de Excel OFFSET-functie - te gebruiken

Samenvatting

De Excel OFFSET-functie retourneert een verwijzing naar een bereik dat is opgebouwd met vijf ingangen: (1) een beginpunt, (2) een rijoffset, (3) een kolomoffset, (4) een hoogte in rijen, (5) een breedte in kolommen. OFFSET is handig in formules die een dynamisch bereik vereisen.

Doel

Creëer een referentieoffset vanaf een gegeven startpunt

Winstwaarde

Een celverwijzing.

Syntaxis

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

Argumenten

  • reference - Het startpunt, geleverd als celverwijzing of bereik.
  • rijen : het aantal rijen dat onder de startreferentie moet worden verschoven.
  • cols - Het aantal kolommen dat rechts van de beginverwijzing moet worden verschoven.
  • hoogte - (optioneel) De hoogte in rijen van de geretourneerde referentie.
  • width - (optioneel) De breedte in kolommen van de geretourneerde verwijzing.

Versie

Excel 2003

Gebruiksopmerkingen

De Excel OFFSET-functie retourneert een dynamisch bereik opgebouwd met vijf ingangen: (1) een startpunt, (2) een rijoffset, (3) een kolomoffset, (4) een hoogte in rijen, (5) een breedte in kolommen.

Het startpunt (het referentieargument ) kan een cel of een reeks cellen zijn. De argumenten rijen en cols zijn het aantal cellen dat vanaf het startpunt moet worden "verschoven". De hoogte- en breedte- argumenten zijn optioneel en bepalen de grootte van het bereik dat wordt gemaakt. Als hoogte en breedte worden weggelaten, worden ze standaard ingesteld op de hoogte en breedte van de referentie .

Als u bijvoorbeeld wilt verwijzen naar C5 beginnend bij A1, is referentie A1, rijen is 4 en cols is 2:

=OFFSET(A1,4,2) // returns reference to C5

Om te verwijzen naar C1: C5 vanaf A1, referentie is A1, rijen is 0, cols is 2, hoogte is 5 en breedte is 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Opmerking: de breedte kan worden weggelaten, aangezien deze standaard 1 is.

Het is gebruikelijk om OFFSET verpakt te zien in een andere functie die een bereik verwacht. Bijvoorbeeld naar SUM C1: C5, beginnend bij A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Het belangrijkste doel van OFFSET is om formules dynamisch aan te passen aan beschikbare gegevens of aan gebruikersinvoer. De OFFSET-functie kan worden gebruikt om een ​​dynamisch benoemd bereik op te bouwen voor grafieken of draaitabellen, om ervoor te zorgen dat de brongegevens altijd up-to-date zijn.

Opmerking: in Excel-documentatie staat dat hoogte en breedte niet negatief kunnen zijn, maar negatieve waarden lijken goed te hebben gewerkt sinds het begin van de jaren negentig. De OFFSET-functie in Google Spreadsheets staat geen negatieve waarde toe voor hoogte- of breedte-argumenten.

Voorbeelden

De onderstaande voorbeelden laten zien hoe OFFSET kan worden geconfigureerd om verschillende soorten bereiken te retourneren. Deze schermen zijn gemaakt met Excel 365, dus OFFSET retourneert een dynamische array als het resultaat meer dan één cel is. In oudere versies van Excel kunt u de F9-toets gebruiken om de resultaten te controleren die zijn geretourneerd door OFFSET.

Voorbeeld 1

In het onderstaande scherm gebruiken we OFFSET om de derde waarde (maart) in de tweede kolom (West) te retourneren. De formule in H4 is:

=OFFSET(B3,3,2) // returns D6

Voorbeeld 2

In het onderstaande scherm gebruiken we OFFSET om de laatste waarde (juni) in de derde kolom (Noord) te retourneren. De formule in H4 is:

=OFFSET(B3,6,3) // returns E9

Voorbeeld # 3

Hieronder gebruiken we OFFSET om alle waarden in de derde kolom (Noord) te retourneren. De formule in H4 is:

=OFFSET(B3,1,3,6) // returns E4:E9

Voorbeeld # 4

Hieronder gebruiken we OFFSET om alle waarden voor mei (vijfde rij) te retourneren. De formule in H4 is:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Voorbeeld # 5

Hieronder gebruiken we OFFSET om de waarde voor april, mei en juni voor de regio West te retourneren. De formule in H4 is:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Voorbeeld # 6

Hieronder gebruiken we OFFSET om de waarde voor april, mei en juni voor west en noord te retourneren. De formule in H4 is:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Opmerkingen

  • OFFSET geeft alleen een verwijzing terug, er worden geen cellen verplaatst.
  • Zowel rijen als cols kunnen worden geleverd als negatieve getallen om hun normale offset-richting om te keren - negatieve cols naar links en negatieve rijen naar boven.
  • OFFSET is een "vluchtige functie" - het zal herberekenen bij elke wijziging in het werkblad. Vluchtige functies kunnen ervoor zorgen dat grotere en complexere werkmappen traag werken.
  • OFFSET zal de #REF! foutwaarde als de offset buiten de rand van het werkblad valt.
  • Als hoogte of breedte wordt weggelaten, worden de hoogte en breedte van de referentie gebruikt.
  • OFFSET kan worden gebruikt met elke andere functie die verwacht een referentie te ontvangen.
  • Excel-documentatie zegt dat hoogte en breedte niet negatief kunnen zijn, maar negatieve waarden werken wel.

Gerelateerde video's

Een dynamisch benoemd bereik maken met OFFSET In deze video bekijken we hoe je een dynamisch benoemd bereik kunt maken met behulp van de OFFSET-functie, de meest gebruikelijke manier om een ​​dynamisch bereik met namen te maken met een formule.

Interessante artikelen...