Excel OFFSET-functie (inhoudsopgave)

  • OFFSET in Excel
  • OFFSET-formule in Excel
  • Hoe de OFFSET-functie in Excel te gebruiken?

OFFSET in Excel

OFFSET-functie in Excel helpt de gebruiker om een ​​cel of cellenbereik terug te geven dat een opgegeven nr is. rijen en kolommen uit een cel. We kunnen het nee specificeren. van rijen en nee. van te retourneren kolommen.

Hieronder vindt u de OFFSET-formule in Excel:

OFFSET-functie in Excel bestaat uit de volgende argumenten:

  • Referentie: het is het argument waaruit we de offset willen baseren. Het kan een celverwijzing zijn of een bereik van aangrenzende cellen, zo niet Offset retourneert de #WAARDE! Foutwaarde.
  • Rijen: Het is het nee. van rijen om te compenseren. Als we een positief getal gebruiken, worden de onderstaande rijen gecompenseerd en als een negatief getal wordt gebruikt, worden de bovenstaande rijen gecompenseerd.
  • Kolommen: het is voor nee. van te compenseren kolommen. Het is dus hetzelfde concept als rijen, als we een positief getal gebruiken, wordt het naar de kolommen aan de rechterkant verplaatst en als een negatief getal wordt gebruikt, verschuift het naar de kolommen aan de linkerkant.
  • Hoogte: dit argument is optioneel. Het moet een positief getal zijn. Het is een getal dat het aantal rijen in de geretourneerde verwijzing vertegenwoordigt.
  • Breedte: dit is ook een optioneel argument. Het moet een positief getal zijn. Het is een getal dat het aantal kolommen in de geretourneerde verwijzing vertegenwoordigt.

Opmerkingen:

  • De OFFSET-functie in Excel retourneert de #REF! foutwaarde, als rijen en kolommen de referentie over de rand van het werkblad verschuiven.
  • In de OFFSET-functie wordt verondersteld dat het dezelfde hoogte of breedte is als een referentie als hoogte of breedte wordt weggelaten.
  • OFFSET retourneert een referentie, het verplaatst eigenlijk geen cellen of cellenbereik of verandert de selectie. Het kan worden gebruikt met elke functie die een referentieargument verwacht.

Hoe de OFFSET-functie in Excel te gebruiken?

OFFSET-functie in Excel is zeer eenvoudig en gemakkelijk te gebruiken. Laten we de werking van de OFFSET-functie in Excel begrijpen door een voorbeeld van een OFFSET-formule.

U kunt deze OFFSET-functie Excel-sjabloon hier downloaden - OFFSET-functie Excel-sjabloon

Voorbeeld 1

De formule Offset retourneert een celverwijzing op basis van een beginpunt, rijen en kolommen die we opgeven. We kunnen het zien in het onderstaande voorbeeld:

= OFFSET (A1, 3, 1)

De formule vertelt Excel om cel A1 als startpunt (referentie) te overwegen en vervolgens 3 rijen naar beneden (rijen) en 1 kolom naar links te verplaatsen (kolommenargument). Hierna retourneert de OFFSET-formule de waarde in cel B4.

De foto links laat zien dat de functieroute en de screenshot rechts laten zien hoe we de OFFSET-formule op basis van realtime gegevens kunnen gebruiken. Het verschil tussen de twee formules is dat de tweede (rechts) een celverwijzing (D1) bevat in het argument van de rij. Maar omdat cel D1 nummer 1 bevat en exact hetzelfde nummer voorkomt in het rijenargument van de eerste formule, zouden beide een identiek resultaat opleveren: de waarde in B2.

Voorbeeld # 2 Gebruik van de OFFSET-functie voor som

Zoals we weten dat de OFFSET-functie ook kan worden gebruikt met de andere functie van Excel, zullen we in dit voorbeeld zien met de SOM-functie.

Stel dat we op de bovenstaande foto de som van de cijfers moeten vinden, dan kunnen we ook de functie OFFSET gebruiken.

De formule is hieronder:

Dus in bovenstaande formule is D5 de eerste cel waar de gegevens beginnen, wat een referentie is als startcel. Hierna zijn de rijen en kolomwaarden 0, dus we kunnen het als 0, 0 zetten. Dan is er 4, wat 4 rijen onder de referentie betekent waarvoor Excel de som moet berekenen en uiteindelijk is het 1, het betekent dat er 1 kolom zo breed is.

We kunnen de bovenstaande foto verwijzen om het verklaarde voorbeeld mede te relateren.

En hieronder is het resultaat:

We hoeven alleen maar op Enter te drukken na het invoeren van een formule in de vereiste cel en dan hebben we het resultaat.

Dingen om te onthouden Over de OFFSET-functie in Excel

  • De OFFSET-functie in Excel retourneert alleen een verwijzing, het verplaatst in werkelijkheid geen cellen of cellenbereik.
  • Als de OFFSET-formule een cellenbereik retourneert, verwijzen de rijen en kolommen altijd naar de cel linksboven in het geretourneerde bereik.
  • De verwijzing moet een cel of celbereik bevatten, anders retourneert de formule de foutwaarde #WAARDE.
  • Als de rijen en kolommen die zijn opgegeven over de rand van de spreadsheet, retourneert de Offset-formule in Excel de foutwaarde #REF! fout.
  • OFFSET-functie kan worden gebruikt in elke andere Excel-functie die een cel- of bereikverwijzing accepteert in de argumenten.

Als we bijvoorbeeld de formule = OFFSET (A1, 3, 1, 1, 3) alleen gebruiken, geeft dit een #WAARDE! Fout, omdat een retourbereik (1 rij, 3 kolommen) niet in een cel past. Als we het echter in de SOM-functie sorteren zoals hieronder:

= SOM (OFFSET (A1, 3, 1, 1, 3))

De Offset-formule retourneert de som van waarden in een rij van 1 rij met een bereik van 3 kolommen dat 3 rijen lager is en 1 kolom rechts van cel A1, dat wil zeggen het totaal van waarden in cellen B4: D4.

Zoals we kunnen zien is het totaal van 36 + 63 + 82 van gemarkeerde Mar gelijk aan 181, wat resulteerde in G2 na het toepassen van som en Offset-formule. (gemarkeerd in Geel)

Gebruik van de OFFSET-functie in Excel

Zoals we hebben gezien dat wat de OFFSET-functie eigenlijk doet met behulp van het bovenstaande voorbeeld, kunnen we vragen hebben die de OFFSET-formule die een beetje complex is, lastig vallen, waarom niet gewoon een directe referentie gebruiken zoals som of B4: D4?

De OFFSET-functie in Excel is erg goed voor hieronder:

  • Het bereik van de startcel krijgen:

Soms weten we niet het exacte of werkelijke adres van het bereik, we weten alleen waar het begint vanuit een bepaalde cel. In dit geval kunnen we de OFFSET-functie gebruiken, die gemakkelijk te gebruiken is.

  • Dynamisch bereik maken:

De referenties zoals het bovenstaande voorbeeld B1: C4 zijn statisch, dit betekent dat ze altijd verwijzen naar een gegeven of vast bereik. Maar in sommige gevallen zijn de taken gemakkelijker uit te voeren met dynamische bereiken. Het helpt vooral als we werken met het wijzigen van gegevens. We kunnen bijvoorbeeld een spreadsheet hebben waarin elke dag / week / maand een nieuwe rij of kolom wordt ingevoegd of toegevoegd. In dit geval helpt de OFFSET-functie.

Beperkingen en alternatieven van de OFFSET-functie in Excel

Omdat elke formule in Excel zijn eigen beperkingen en alternatieven heeft, hebben we gezien hoe en wanneer de OFFSET-functie in Excel moet worden gebruikt.

Hieronder staan ​​de kritieke beperkingen van de OFFSET-functie in Excel:

  • Resource-hongerige functie:

Het betekent dat wanneer er een wijziging in de brongegevens plaatsvindt, de formule opnieuw wordt berekend voor de hele set, waardoor Excel langer bezig blijft. Als er een kleine spreadsheet is, heeft dit niet zoveel invloed, maar als er een aantal spreadsheets zijn, kan Excel de tijd nemen om opnieuw te berekenen.

  • Moeilijkheidsgraad bij beoordeling:

Zoals we weten, zijn de referenties die worden geretourneerd door de Offset-functie dynamisch of aan het veranderen en kunnen ze een grote formule bevatten, die lastig te corrigeren of te bewerken kan zijn, zoals vereist.

Alternatieven van de OFFSET-functie in Excel:

  • INDEX-functie:

De INDEX-functie in Excel kan ook worden gebruikt om een ​​dynamisch bereik van verwijzingen te maken. Het is niet precies hetzelfde als OFFSET. Maar net als OFFSET, is de INDEX-functie niet zo veel vluchtig, daarom zal het de Excel niet vertragen, wat een beperking is voor OFFSET.

  • INDIRECTE functie:

We kunnen ook de INDIRECT-functie gebruiken om een ​​dynamisch bereik van verwijzingen te maken uit vele bronnen, zoals celwaarden , tekst en de benoemde bereiken.

Aanbevolen artikelen

Dit is een leidraad geweest voor OFFSET in Excel. Hier bespreken we de OFFSET-formule in Excel en hoe de OFFSET-functie in Excel te gebruiken, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -

  1. MS Excel: ZOEKEN functie
  2. Beste voorbeeld van Excel XIRR-functie
  3. Geweldige gids over NIET-functie in Excel
  4. IPMT in Excel met voorbeelden

Categorie:

Excel-tips