Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de Excel OFFSET-functie in Excel om een referentie-offset van een initiële cel te maken.
OFFSET Functie Overzicht
De OFFSET-functie Begint met een gedefinieerde celverwijzing en retourneert een celverwijzing met een bepaald aantal rijen en kolommen, verschoven ten opzichte van de oorspronkelijke verwijzing. Verwijzingen kunnen één cel of een reeks cellen zijn. Met Offset kunt u ook de grootte van de verwijzing wijzigen voor een bepaald aantal rijen/kolommen.
(Let op hoe de formule-invoer verschijnt)
IFERROR Functiesyntaxis en ingangen:
| 1 | =OFFSET(referentie,rijen,kolommen,hoogte,breedte) |
verwijzing - De initiële celverwijzing waarvan u wilt afwijken.
rijen - Het aantal te compenseren rijen.
cols - Het aantal kolommen dat moet worden gecompenseerd.
hoogte - OPTIONEEL: Pas het aantal rijen in de referentie aan.
breedte - OPTIONEEL: Pas het aantal kolommen in de referentie aan.
Wat is de OFFSET-functie?
De OFFSET-functie is een van de krachtigere spreadsheetfuncties omdat deze behoorlijk veelzijdig kan zijn in wat het creëert. Het geeft de gebruiker de mogelijkheid om een cel of bereik in verschillende posities en maten te definiëren.
LET OP: De OFFSET-functie is een van de vluchtige functies. Wanneer u in uw spreadsheet werkt, berekent de computer meestal een formule alleen opnieuw als de invoer hun waarden heeft gewijzigd. Een vluchtige functie herberekent echter elk keer dat u een wijziging aanbrengt in een cel. Voorzichtigheid is geboden om ervoor te zorgen dat u geen grote herberekeningstijd veroorzaakt door overmatig gebruik van vluchtige functies of doordat veel cellen afhankelijk zijn van het resultaat van een vluchtige functie.
Voorbeelden van basisrijen
Bij elk gebruik van de OFFSET-functie moet u een startpunt of anker opgeven. Laten we naar deze tabel kijken om dit te begrijpen:
We gebruiken "Bob" in cel B3 als ons ankerpunt. Als we de waarde net onder (Charlie) wilden pakken, zouden we zeggen dat we de rij met 1 willen verschuiven. Onze formule zou er als volgt uitzien
| 1 | =VERSCHUIVING(B3, 1) |
Als we zouden willen opschuiven, zou dat een negatieve verschuiving zijn. Je kunt dit zien als het rijnummer afneemt, dus we moeten aftrekken. Dus om de bovenstaande waarde (Adam) te krijgen, zouden we schrijven:
| 1 | =VERSCHUIVING(B2, -1) |
Voorbeelden van basiskolommen
Als we het idee uit het vorige voorbeeld voortzetten, voegen we nog een kolom toe aan onze tabel.
Als we de leraar voor Bob wilden pakken, konden we de formule gebruiken
| 1 | =VERSCHUIVING(B2, 0, 1) |
In dit geval hebben we gezegd dat we nul rijen willen compenseren (ook wel op dezelfde rij blijven) maar we willen 1 kolom compenseren. Voor kolommen betekent een positief getal een verschuiving naar rechts en negatieve getallen een verschuiving naar links.
OFFSET en MATCH
Stel dat u meerdere kolommen met gegevens had en dat u de gebruiker de mogelijkheid wilde geven om te kiezen uit welke kolom de resultaten moeten worden opgehaald. U kunt de INDEX-functie gebruiken, of u kunt OFFSET gebruiken. Aangezien MATCH de relatieve positie van een waarde retourneert, moeten we ervoor zorgen dat het ankerpunt zich links van onze eerst mogelijke waarde bevindt. Denk aan de volgende indeling:
In B2 schrijven we deze formule:
| 1 | =OFFSET(B2, 0, MATCH(A2, $C$1:$F$1, 0)) |
De MATCH ziet er "Feb" uit in het bereik C1:F1 en vindt het in de 2nd cel. De OFFSET zal dan 1 kolom naar rechts van B2 verschuiven en de gewenste waarde van 9 pakken. Merk op dat OFFSET geen probleem heeft om dezelfde cel die de formule bevat als ankerpunt te gebruiken.
OPMERKING: Deze techniek kan worden gebruikt als vervanging voor VERT.ZOEKEN of HORIZ.ZOEKEN wanneer u een waarde van links/boven uw opzoekbereik wilt retourneren. Dit komt omdat OFFSET negatieve offsets kan doen.
OFFSET om een bereik te krijgen
U kunt de 4 . gebruikene en 5e argumenten in de OFFSET-functie om een bereik te retourneren in plaats van slechts een enkele cel. Stel dat u 3 kolommen in deze tabel wilt optellen.
| 1 | =GEMIDDELDE(OFFSET(A1,MATCH(F2,A2:A5,0),1,1,3)) |
In F2 hebben we de naam geselecteerd van een student waarvoor we hun gemiddelde testscores willen ophalen. Om dit te doen, gebruiken we de formule:
| 1 | =GEMIDDELDE(OFFSET(A1,MATCH(F2,A2:A5,0),1,1,3)) |
De MATCH zoekt in kolom A naar onze naam en retourneert de relatieve positie, die in ons voorbeeld 3 is. Laten we eens kijken hoe dit zal worden geëvalueerd. Eerst gaat de OFFSET omlaag 3 rijen van A1 en 1 kolom naar de Rechtsaf vanaf A1. Dit plaatst ons in cel B3.
| 1 | =GEMIDDELDE(OFFSET(A1, 3, 1, 1, 3)) |
Vervolgens gaan we het bereik verkleinen. Het nieuwe bereik heeft B3 als cel linksboven. Het zal 1 rij hoog en 3 kolommen hoog zijn, wat ons het bereik B4:D4 geeft.
| 1 | =GEMIDDELDE(OFFSET(A1,3, 1, 1, 3)) |
Merk op dat hoewel u legitieme negatieve waarden in de offset-argumenten kunt plaatsen, u alleen niet-negatieve waarden in de grootte-argumenten kunt gebruiken.
Aan het einde ziet onze GEMIDDELDE functie:
| 1 | =GEMIDDELDE(B4:D4) |
Zo krijgen we onze oplossing van 86,67
OFFSET met dynamische SUM
Omdat OFFSET wordt gebruikt om een verwijzing te vinden, in plaats van rechtstreeks naar de cel te verwijzen, is het vooral handig wanneer u te maken hebt met gegevens waaraan rijen zijn toegevoegd of verwijderd. Beschouw de volgende tabel met een Totaal onderaan:
| 1 | =SOM(B2:B4) |
Als we hier een basis SOM-formule van "=SUM(B2:B4)" hadden gebruikt en vervolgens een nieuwe rij hadden ingevoegd om een record voor Bill toe te voegen, zouden we het verkeerde antwoord hebben
Laten we in plaats daarvan bedenken hoe we dit kunnen oplossen vanuit het oogpunt van Total. We willen echt alles pakken, van cel B2 tot cel net boven ons totaal. De manier waarop we dit in een formule kunnen schrijven, is door een rijverschuiving van -1 uit te voeren. We gebruiken dit dus als de formule voor ons totaal in cel B5:
| 1 | =SOM(B2:OFFSET(B5,-1,0)) |
Deze formule doet wat we zojuist hebben beschreven: begin bij B2 en ga naar 1 cel boven onze totale cel. U kunt zien hoe na het toevoegen van de gegevens van Bill ons totaal correct wordt bijgewerkt.
OFFSET om de laatste N items te krijgen
Stel dat u maandelijkse verkopen registreert, maar naar de laatste 3 maanden wilt kunnen kijken. In plaats van uw formules handmatig bij te werken om te blijven aanpassen wanneer nieuwe gegevens worden toegevoegd, kunt u de OFFSET-functie gebruiken met COUNT.
We hebben al laten zien hoe je OFFSET kunt gebruiken om een reeks cellen te pakken. Om te bepalen hoeveel cellen we moeten verschuiven, gebruiken we COUNT om te bepalen hoeveel nummers staan in kolom B. Laten we eens kijken naar onze voorbeeldtabel.
| 1 | =SOM(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
Als we bij B1 zouden beginnen en 4 rijen zouden verschuiven (het aantal getallen in kolom B), zouden we onderaan ons bereik eindigen, B5. Omdat OFFSET echter niet van grootte kan veranderen met een negatieve waarde, moeten we enkele aanpassingen doen zodat we in B3 terechtkomen. De algemene vergelijking hiervoor zal zijn:
| 1 | COUNT(… ) - N + 1 |
We nemen de telling van de hele kolom, trekken er zoveel af dat we willen retourneren (aangezien we het formaat wijzigen om ze te pakken), en voegen er vervolgens 1 aan toe (aangezien we onze offset in wezen op positie nul beginnen).
Hier kunt u zien dat we een bereik hebben ingesteld om de som, het gemiddelde en het maximum van de afgelopen N maanden te krijgen. In E1 hebben we de waarde van 3 ingevoerd. In E2 is onze formule
| 1 | =SOM(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
Het gemarkeerde gedeelte is onze algemene vergelijking die we zojuist hebben besproken. We hoeven geen kolommen te compenseren. We gaan dan het formaat van het bereik aanpassen tot 3 cellen hoog (bepaald door de waarde in E1) en 1 kolom breed. Onze SUM neemt dan dit bereik en geeft ons het resultaat van $ 1.850. We hebben ook laten zien dat je het gemiddelde van max van hetzelfde bereik kunt berekenen door simpelweg de buitenste functie van SOM te veranderen naar wat de situatie vereist.
OFFSET dynamische validatielijsten
Met behulp van de techniek die in het laatste voorbeeld wordt getoond, kunnen we ook benoemde bereiken bouwen die kunnen worden gebruikt in gegevensvalidatie of grafieken. Dit kan handig zijn als je een spreadsheet wilt opzetten, maar verwacht dat onze lijsten/gegevens van grootte veranderen. Laten we zeggen dat onze winkel fruit begint te verkopen en dat we momenteel 3 keuzes hebben.
Om een vervolgkeuzelijst Gegevensvalidatie te maken die we elders kunnen gebruiken, definiëren we het benoemde bereik MyFruit als:
| 1 | =$A$2:OFFSET($A$1, COUNTA($A:$A)-1, 0) |
In plaats van COUNT gebruiken we COUNTA omdat we te maken hebben met tekstwaarden. Hierdoor zal onze AANTAANDELING echter één hoger zijn, omdat deze de kopcel in A1 gaat tellen en een waarde van 4 geeft. Als we echter 4 rijen compenseren, komen we in cel A5 terecht die leeg is. Om hiervoor te corrigeren, trekken we de 1 af.
Nu we onze Named Range-configuratie hebben, kunnen we wat gegevensvalidatie instellen in cel C4 door een lijsttype te gebruiken, met bron:
| 1 | =Mijn Fruit |
Merk op dat de vervolgkeuzelijst alleen onze drie huidige items toont. Als we vervolgens meer items aan onze lijst toevoegen en teruggaan naar de vervolgkeuzelijst, toont de lijst alle nieuwe items zonder dat we een van de formules hoeven te wijzigen.
Waarschuwingen bij het gebruik van OFFSET
Zoals vermeld aan het begin van dit artikel, is OFFSET een vluchtige functie. U zult dit niet merken als u het in slechts een paar cellen gebruikt, maar als u het begint te betrekken bij honderden berekeningen en u zult snel merken dat uw computer een aanzienlijke hoeveelheid tijd besteedt aan het herberekenen elke keer dat u wijzigingen aanbrengt .
Bovendien, omdat OFFSET de cellen waarnaar het kijkt niet direct een naam geeft, is het moeilijker voor andere gebruikers om later langs te komen en uw formules indien nodig te wijzigen.
In plaats daarvan is het raadzaam om tabellen te gebruiken (geïntroduceerd in Office 2007) die structurele verwijzingen mogelijk maken. Hierdoor konden gebruikers één enkele referentie geven die automatisch in grootte werd aangepast wanneer nieuwe gegevens werden toegevoegd of verwijderd.
De andere optie om te gebruiken in plaats van OFFSET is de krachtige INDEX-functie. Met INDEX kun je alle dynamische bereiken bouwen die we in dit artikel hebben gezien zonder dat het een vluchtige functie is.
extra notities
Gebruik de OFFSET-functie om een celwaarde (of een celbereik) te retourneren door een bepaald aantal rijen en kolommen van een beginverwijzing te verschuiven. Wanneer u slechts naar één cel zoekt, bereiken OFFSET-formules hetzelfde doel als de INDEX-formules, met een iets andere techniek. De echte kracht van de OFFSET-functie ligt in het vermogen om een reeks cellen te selecteren die in een andere formule moeten worden gebruikt.
Wanneer u de OFFSET-functie gebruikt, definieert u een eerste startcel of celbereik. Vervolgens geeft u het aantal rijen en kolommen aan dat moet worden verschoven ten opzichte van die eerste cel. U kunt het bereik ook verkleinen; rijen of kolommen optellen of aftrekken.
Keer terug naar de lijst met alle functies in Excel
OFFSET in Google Spreadsheets
De OFFSET-functie werkt in Google Spreadsheets precies hetzelfde als in Excel:
