In dit artikel zullen we leren hoe u de OFFSET-functie in Excel kunt gebruiken.
Wat is OFFSET-functie?
Offset-functie is een functie voor het ophalen van informatie. Als u een enorme tabel heeft met bepaalde gegevens en u wilt er gegevens uit halen, kunt u dit het beste doen door een offset-formule voor de tabel in dat werkblad of een ander werkblad in dezelfde werkmap in te voegen. Dus als u eenmaal de ophaaltabel voor verrekening heeft gemaakt, hoeft u alleen maar de gegevens in te voeren, bijvoorbeeld 'maand' en u krijgt de 'omzet' of 'inkomsten' van die maand uit die tabel.
De tabel doornemen is een andere optie, maar zou u de optie overwegen als u bladen en bladen met gegevens heeft die allemaal een gegevenstabel met 1000 kolommen bevatten? Hier wordt offset gecombineerd met andere functies.
Het concept van draaitabel komt van offset. Een draaitabel is een algemene tabel en uit die specifieke combinatie van gegevens en grafieken wordt zo nodig verkregen. Op dezelfde manier kunnen ook dynamische tabellen worden gemaakt. Voor deze tabellen moet u de Excel-offsetformule maken
Syntaxis van OFFSET
| =OFFSET(verwijzing,rijen,kolommen, [hoogte], [breedte]) |
rijen - je vraagt Excel om het aantal rijen te verplaatsen om de info te krijgen. In dit geval moet het over de hele tafel bewegen en dus gewoon een spatie achterlaten die wordt aangegeven door een komma (,). Anders, om een kolom naar voren te verplaatsen, geeft u de waarde 1 en om een kolom eerder te verplaatsen, geeft u de waarde -1.
Kolommen - dit is om de functie naar het aantal kolommen te sturen. Het waardesysteem is hetzelfde als dat voor rij.
Hoogte - de hoogte van de tafel, in dit geval A11.
Breedte - breedte van de tafel, in dit geval D11.
Nadat u de offset-functie hebt ingesteld, is het belangrijk om een proef uit te voeren om eventuele fouten te voorkomen.
Laten we een tabel nemen die naam, e-mailadres, geboortedatum en leeftijd bevat. De tabel begint bij cel A1, met daarin de kop 'Naam'. De gegevens lopen voor bijvoorbeeld A11. En de rijen lopen tot D1. De hele tabel loopt A1:D11. U wilt een ophaalsysteem dat u de naam geeft wanneer u de e-mail-ID invoert, of de e-mail-ID wanneer u de naam invoert, samen met andere details. U maakt een formule door aan de 5 argumenten te voldoen.
Referentiepunt is de cel van waaruit het opzoeken moet beginnen. In dit geval moet het A2 zijn. Dat is de algemene norm.
Voorbeeld:
U geeft bijvoorbeeld de referentie van B4 en offset rij 0, en offset kolom 1 dan wordt de waarde van C4 geretourneerd. Verwarrend? Laten we nog wat voorbeelden hebben. Het laatste voorbeeld van de OFFSET-functie vertelt hoe dynamisch SOM kan worden.
OFFSET-functie om waarde te krijgen van rechts en links van een cel
We hebben deze tafel.
Als ik nu waarde wil krijgen van 2 cellen naar B2 (wat D2 betekent). Ik zal deze OFFSET-formule schrijven:
| =VERSCHUIVING(B2,0,2) |
OFFSET-functie verplaatst 2 cellen naar rechts van cel B4 en retourneert de waarde. Zie afbeelding hieronder:
Als ik waarde wil krijgen van 1 cel over naar B2 (wat A2 betekent). Ik zal deze OFFSET-formule schrijven:
| =OFFSET(B2,0,-1) |
Het minteken (-) geeft de offset aan om omgekeerd te bewegen.
OFFSET-functie om waarde van onder en boven van een cel te krijgen
Dus nogmaals in de bovenstaande tabel, als ik waarde wil krijgen van 2-cell belove naar B3 (wat B5) betekent. Ik zal deze OFFSET-formule schrijven:
| =VERSCHUIVING(B2,2,0) |
Als ik waarde wil krijgen van 1 cel over naar B2 (wat A2 betekent). Ik zal deze OFFSET-formule schrijven:
| =OFFSET(B2,-2,0) |
- Pro-tip: Behandel een OFFSET als grafiekaanwijzer waarbij Referentie de oorsprong is en rij en kolom de x- en y-as zijn.
OFFSET-functie om het bereik dynamisch op te tellen
Laten we dit voorbeeld bekijken.
In bovenstaande tabel Totale rij aan het einde van de tabel. In de loop van de tijd zult u rijen aan deze tabel toevoegen. Dan moet u het sombereik in de formule wijzigen. Hier kunnen we de OFFSET-functie gebruiken om dynamisch op te tellen. Momenteel hebben we in cel C11: =SOM(C2:C10). Vervang dit door onderstaande formule.
| =SOM(C2:OFFSET(C11,-1,0)) |
Deze formule neemt alleen de eerste gegevensrij en telt vervolgens het bereik boven de totale rij op.
Offset voor het verkrijgen van array
OFFSET-functie heeft twee optionele argumenten, [hoogte] en [breedte]. Neem ze niet als vanzelfsprekend aan. Als de functie OFFSET wordt geleverd met argumenten [hoogte] en [breedte], wordt een tweedimensionale array geretourneerd. Als slechts één van deze als argument wordt doorgegeven, wordt deze geretourneerd op een dimensionale reeks waarden.
Als u deze formule in een willekeurige cel typt:
| =SOM(OFFSET(C1,1,0,9,3)) |
Het zal waarden optellen in het bereik vanaf C2 tot 9 rijen hieronder en 3 kolommen rechts.
OFFSET(C1,1,0,9,3) : Dit vertaalt zich naar {8,8,7;6,1,2;8,5,8;5,1,5;8,3,5;8,3,9;8,9,2;3,5 ,4;6,6,5}.
Dus tot slot, offset is een zeer nuttige functie van Excel die u kan helpen bij het oplossen van veel gewurgde threads. Laat me weten hoe u de OFFSET-functie in uw formule gebruikt en waar dit u het meest heeft geholpen.
De opzoekfunctie is hetzelfde als offset, maar gebruikt functies zoals Match, Counta en IF om een dynamische tabel te bewerken.
Het gebruik van offset in Excel vereist vaardigheid en goede kennis van Excels-functies en hoe ze samenwerken. Het zonder fouten afmaken is de grootste hindernis.
Hier zijn alle observatienotities met betrekking tot het gebruik van de formule.
Opmerkingen:
- Deze formule werkt zowel met tekst als met getallen.
- Er zijn vijf argumenten van de offset waaraan moet worden voldaan om foutloze informatie te krijgen. Als u een onjuiste formule schrijft, treedt er een Ref-fout op.
Ik hoop dat je hebt begrepen wat de Excel-offsetfunctie is en hoe je deze in Excel kunt gebruiken. Bekijk hier meer artikelen over het opzoeken en matchen van waarden in Excel met formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
VERT.ZOEKEN-functie om cijfer in Excel te berekenen : Om cijfers te berekenen zijn IF en IFS niet de enige functies die u kunt gebruiken. De VERT.ZOEKEN is efficiënter en dynamischer voor dergelijke voorwaardelijke berekeningen. Om cijfers te berekenen met VERT.ZOEKEN kunnen we deze formule gebruiken.
17 dingen over Excel VERT.ZOEKEN : VERT.ZOEKEN wordt meestal gebruikt voor het ophalen van overeenkomende waarden, maar VERT.ZOEKEN kan veel meer dan dit. Hier zijn 17 dingen over VERT.ZOEKEN die u moet weten om effectief te gebruiken.
ZOEK de eerste tekst uit een lijst in Excel : De functie VERT.ZOEKEN werkt prima met jokertekens. We kunnen dit gebruiken om de eerste tekstwaarde uit een bepaalde lijst in Excel te extraheren. Hier is de generieke formule.
LOOKUP-datum met laatste waarde in lijst : Om de datum op te halen die de laatste waarde bevat, gebruiken we de LOOKUP-functie. Deze functie zoekt naar de cel die de laatste waarde in een vector bevat en gebruikt vervolgens die verwijzing om de datum te retourneren.
Hoe de laatste prijs in Excel op te halen : Het is gebruikelijk om de prijzen in elk bedrijf bij te werken en het is een must om de laatste prijzen te gebruiken voor elke aankoop of verkoop. Om de laatste prijs op te halen uit een lijst in Excel gebruiken we de LOOKUP functie. De LOOKUP-functie haalt de laatste prijs op.
Populaire artikels:
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.