Excel OFFSET-formule (inhoudsopgave)

  • Wat is OFFSET-formule in Excel?
  • Hoe de OFFSET-formule in Excel te gebruiken?

Wat is OFFSET-formule in Excel?

OFFSET-formule in Excel geeft een cel of een bereik van dynamische rechthoekige cellen als een uitvoer die een subset is van het totale gegeven bereik met een opgegeven aantal rijen, kolommen, hoogte en breedte.

Syntaxis van OFFSET-formule in Excel

Verklaring van de OFFSET-functie in Excel:

  • referentie: het startpunt voor OFFSET of we kunnen een reeks cellen noemen die kunnen worden beschouwd als de basis voor OFFSET.
  • rijen : het aantal rijen naar OFFSET onder of boven de basisreferentie.
  • cols: het aantal kolommen OFFSET rechts of links van de basisreferentie.
  • hoogte: aantal rijen in geretourneerde referentie.
  • breedte: aantal kolommen in geretourneerde referentie.

De hoogte en breedte zijn de optionele parameters voor deze functie en kunnen worden gebruikt om de grootte van de gemaakte referentie te bepalen.

Hoe de OFFSET-formule in Excel te gebruiken?

We zullen nu leren hoe we offsetformules moeten schrijven om de opzoekwaarde te krijgen. Laten we de offset-formule begrijpen met enkele voorbeelden in Excel.

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

Excel OFFSET Formula - Voorbeeld # 1

Stel dat we gegevens hebben zoals hieronder aangegeven.

  • Voer in cel E2 de volgende formule in:

= OFFSET (A1, 11, 1)

  • Als u klaar bent met de formule, drukt u op Enter en ziet u de uitvoer.

Wat deze formule deed, is dat deze is overgenomen uit cel A1 (Jaar) en met 11 rijen na A1 is verplaatst (dwz rij nr. 12) en vervolgens de waarde heeft verkregen die coördinaat is van de 11e rij na A1 en de eerste kolom ( kolom A = 0 kolom B = 1 in dit geval. Zoals we weten, kunnen we zowel naar rechts als naar links gaan). Dat is $ 141, 27 miljoen.

Excel OFFSET Formula - Voorbeeld # 2

Nu zullen we een ander voorbeeld van een referentiecel voor een specifiek jaar zien. Zet de volgende formule in cel E3:

= OFFSET (A1, 11, 0)

  • Als u klaar bent met de formule, drukt u op Enter en ziet u de uitvoer.

Als u de formule hebt gezien, is deze hetzelfde als bij de vorige, de enige wijziging is de kolomverwijzing. De kolomreferentie 0 (nul) betekent dat de waarde wordt vastgelegd uit kolom A voor de 11e rij na A1 (wat 2010 is).

Excel OFFSET Formula - Voorbeeld # 3

In dit voorbeeld berekenen we de som van de laatste N rijen met behulp van de Excel OFFSET-functie. De algemene formule voor het samenvatten van de laatste N-nummers is:

= SOM (OFFSET (A1, COUNT (A: A), 0, -N))

Om de laatste N-waarnemingen uit uw gegevens samen te vatten, kunt u de functie SOM samen met de functie OFFSET en COUNT samen gebruiken. In dit voorbeeld zullen we de laatste 5 gegevenspunten samenvatten. Let op: als we N zeggen, is dit een generalisatie van een geheel getal. U kunt dus de laatste 3, laatste 4, laatste 5, enz. Berekenen, afhankelijk van uw gegevensset en uw vereiste.

Laten we het voor onze dataset doen:

  • Voer de volgende formule in cel E2 van het Excel-bestand in.

= SOM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Klik op Enter om de uitvoer te bekijken.

Zoals we al hebben gezien, voert de Excel OFFSET-functie rechthoekige dynamische bereiken uit wanneer een startreferentie wordt gegeven.

  • In dit geval wordt de startreferentie gegeven in termen van B1 (als een eerste argument voor de OFFSET-functie).
  • We hebben de functie AANTAL gebruikt omdat we de som van waarnemingen van de laatste waarde nodig hebben. AANTAL Functie helpt OFFSET om te bepalen hoeveel waarnemingen (rijen) er in kolom B zijn. Het telt alle numerieke waarden in kolom B (19 is de telling).
  • Met telling als 19 wordt de OFFSET-formule:

= OFFSET (B1, 19, 0, -5)

  • Deze formule begint met Offsetting op B1 met 19 rijen en dezelfde kolom (0). Dan helpt hoogteparameter -5 deze functie om het bereik in achterwaartse richting met 5 rijen uit te breiden (zie het negatieve teken dat bij 5 hoort).
  • Het dynamische bereik van Laatste vijf observaties wordt gegeven als een input voor de somfunctie. Dat levert de som op van die uit kolom B.

Excel OFFSET Formula - Voorbeeld # 4

In dit voorbeeld berekenen we de gemiddelde omzet voor de laatste 3 jaar. OFFSET-functie kan worden gebruikt als een argument om hetzelfde te berekenen.

  • Zet de volgende formule in cel E2:

= MEDIAAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Druk op Enter om de uitvoer te bekijken.

Deze formule werkt op dezelfde manier als de vorige.

  • OFFSET Creëert een dynamisch bereik van de laatste drie waarnemingen uit kolom B met behulp van de functie COUNT (= OFFSET (A1, 19, 0, -3)).
  • Dit kan worden gegeven als uitvoer voor de MEDIAN-functie. Die sorteert vervolgens de observaties in oplopende of aflopende volgorde en selecteert vervolgens de middelste waarde zoals u ziet in uitvoer ($ 212.07).

Excel OFFSET Formula - Voorbeeld # 5

Hier zullen we het mediaanjaar berekenen met behulp van de MEDIAANSE en OFFSET-functie.

  • Gebruik de volgende formule voor hetzelfde in cel E3:

= Mediaan (OFFSET (A1, COUNT (A: A), 0, -3))

  • Klik op Enter om de uitvoer te bekijken.

Hetzelfde is hier gebeurd. De formule nam een ​​dynamisch bereik op dat werd gecreëerd door de OFFSET-functie met behulp van COUNT. En gebruikte het als argument voor een MEDIAN-functie die vervolgens de jaarwaarden in oplopende of aflopende volgorde sorteerde en vervolgens uitvoer als 2017 gaf.

Gebruik van de OFFSET-formule zit in het maken van de dynamische bereiken. Het is altijd beter om aan dynamische bereiken te werken in plaats van statische. Omdat je misschien gegevens hebt die zo nu en dan worden toegevoegd. Bijvoorbeeld, Werknemer Master. De stamgegevens van uw medewerkers worden altijd zo nu en dan verhoogd omdat er nieuwe werknemers bij moeten komen. Het heeft geen zin elke keer dezelfde formule te gebruiken. Maak in plaats daarvan een bereik dat dynamisch is met de OFFSET-functie en bekijk de magie. Het zal u zeker een tijd besparen.

Dingen om te onthouden

  • OFFSET-functie retourneert alleen een verwijzing naar een bepaald cellenbereik in Excel. Het verplaatst de cellen niet van hun oorspronkelijke posities.
  • Standaard werkt deze functie aan de bovenkant (in rijen) en aan de rechterkant (in kolommen). Het kan echter worden voorzien van negatieve argumenten om de standaardfunctionaliteit om te keren. Net als -negatief rijargument helpt de gegevens van onder naar boven te verschuiven en negatief col-argument helpt de gegevens naar links te verschuiven.
  • OFFSET-formule kan de Excel-functionaliteit vertragen omdat het een formule is die telkens opnieuw wordt berekend als het werkblad wordt gewijzigd.
  • #REF-fout wordt gegenereerd als uw bereik buiten het bereik van uw werkmap valt.
  • Elke andere functie die een referentie verwacht, kan de OFFSET-functie gebruiken.

Aanbevolen artikelen

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

  1. Hoe tekst in Excel te verpakken?
  2. Eenvoudige gids voor sorteren in Excel
  3. ZOEKEN Functie in Excel
  4. Hoe de FIND-functie in Excel te gebruiken?

Categorie:

Excel-tips