Excel OFFSET-funktion (innehållsförteckning)

  • OFFSET i Excel
  • OFFSET-formel i Excel
  • Hur använder man OFFSET-funktionen i Excel?

OFFSET i Excel

OFFSET-funktion i Excel hjälper användaren att returnera en cell eller ett område med celler som är ett specificerat nr. med rader och kolumner från en cell. Vi kan ange nej. av rader och nr. av kolumner som ska returneras.

Nedan är OFFSET-formeln i Excel:

OFFSET-funktionen i Excel består av följande argument:

  • Referens: Det är argumentet från vilket vi vill basera offset. Det kan vara en cellreferens eller ett område med angränsande celler, om inte Offset returnerar #VÄRDE! Felvärde.
  • Rader: Det är nej. av rader som ska kompenseras. Om vi ​​använder ett positivt tal kommer det att kompenseras till raderna nedan och om ett negativt nummer används kompenseras det raderna ovan.
  • Kolumner: Det är för nr. av kolumner som ska kompenseras. Så det är samma koncept som Rader, om vi använder ett positivt tal kommer det att kompenseras till kolumnerna till höger och om ett negativt nummer används, så är dess förskjutningar till kolumnerna till vänster.
  • Höjd: Detta argument är valfritt. Det borde vara ett positivt tal. Det är ett nummer som representerar antalet rader i den returnerade referensen.
  • Bredd: Detta är också ett valfritt argument. Det måste vara ett positivt tal. Det är ett nummer som representerar antalet kolumner i den returnerade referensen.

Anmärkningar:

  • OFFSET-funktionen i Excel returnerar #REF! felvärde, om rader och kolumner förskjuter referens över kalkylbladet.
  • I OFFSET-funktionen ska den ha samma höjd eller bredd som en referens om höjd eller bredd utelämnas.
  • OFFSET returnerar en referens, den flyttar inte några celler eller cellintervall eller ändrar markeringen. Det kan användas med alla funktioner som förväntar sig ett referensargument.

Hur använder man OFFSET-funktionen i Excel?

OFFSET-funktionen i Excel är mycket enkel och enkel att använda. Låt oss förstå hur OFFSET-funktionen fungerar i Excel genom ett exempel på OFFSET-formel.

Du kan ladda ner den här OFFSET-funktionen Excel-mall här - OFFSET-funktionen Excel-mall

Exempel 1

Offsetformeln returnerar en cellreferens baserad på en startpunkt, rader och kolumner som vi anger. Vi kan se det i nedanstående exempel:

= OFFSET (A1, 3, 1)

Formeln beräknar Excel att överväga cell A1 för startpunkt (referens), flytta sedan 3 rader nedåt (rader) och 1 kolumn till vänster (kolumnens argument). Efter detta returnerar OFFSET-formeln värdet i cell B4.

Bilden till vänster visar att funktionsvägen och skärmdumpen till höger visar hur vi kan använda OFFSET-formeln i realtid basdata. Skillnaden mellan de två formlerna är att den andra (till höger) innehåller en cellreferens (D1) i radens argument. Men eftersom cell D1 innehåller nummer 1 och exakt samma nummer visas i radernas argument för den första formeln, skulle båda ge ett identiskt resultat - värdet i B2.

Exempel 2 Användning av OFFSET-funktion för summan

Som vi vet att OFFSET-funktionen också kan användas med den andra funktionen i Excel, kommer vi i detta exempel att se med SUM-funktionen.

I bilden ovan, anta att vi måste ta reda på summan av märken då kan vi också använda OFFSET-funktionen.

Formeln är nedan:

Så i formeln ovan är D5 den första cellen där data börjar som är en referens som startcell. Därefter är raderna och kolumnvärdet 0, så vi kan sätta det som 0, 0. Sedan finns det 4 vilket betyder 4 rader under referensen som Excel behöver för att beräkna summan och äntligen är det 1, det betyder att det finns en kolumn som bredd.

Vi kan hänvisa till bilden ovan för att tillsammans relatera det förklarade exemplet.

Och nedan är resultatet:

Vi måste bara trycka på Enter när du har angett en formel i den önskade cellen och sedan har vi resultatet.

Saker att komma ihåg om OFFSET-funktionen i Excel

  • OFFSET-funktionen i Excel returnerar bara en referens, den flyttar inte några celler eller intervall av celler i själva verket.
  • Om OFFSET-formeln returnerar ett område med celler, hänvisar raderna och kolumnerna alltid till den övre vänstra cellen i det returnerade intervallet.
  • Referensen bör innehålla en cell eller ett område med celler annars kommer formeln att returnera #VALUE-felet.
  • Om raderna och kolumnerna som specificeras över kanten på kalkylbladet kommer Offset-formeln i Excel att returnera #REF! fel.
  • OFFSET-funktionen kan användas i alla andra Excel-funktioner som accepterar en cell- eller intervallreferens i sina argument.

Om vi ​​till exempel använder formeln = OFFSET (A1, 3, 1, 1, 3) ensam kommer det att ge en #VALUE! Fel eftersom ett avkastningsområde (1 rad, 3 kolumner) inte passar i en cell. Men om vi samlar det i SUM-funktionen som nedan:

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

Offsetformeln returnerar summan av värden i en 1-rad med ett 3-kolumnintervall som är 3 rader under och 1 kolumn till höger om cell A1, dvs. summan av värden i cellerna B4: D4.

Som vi kan se är summan 36 + 63 + 82 för den markerade Mar lika med 181, vilket kom som ett resultat i G2 efter att ha använt summa och Offset-formel. (gulmarkerad)

Användning av OFFSET-funktion i Excel

Som vi har sett att vad OFFSET-funktionen faktiskt gör med hjälp av exemplet ovan, kan vi ha frågor som varför att bry sig om att sjunga OFFSET-formeln som är lite komplex, varför inte bara använda en direkt referens som summa eller B4: D4?

OFFSET-funktionen i Excel är mycket bra för nedan:

  • Hämta intervallet från startcellen:

Ibland vet vi inte den exakta eller faktiska adressen för intervallet, vi vet bara varifrån det startar från en viss cell. I det här fallet kan vi använda OFFSET-funktionen, som är lätt att gå.

  • Skapa dynamisk räckvidd:

Hänvisningarna som exemplet ovan B1: C4 är statiska, det betyder att de alltid hänvisar till ett givet eller fast intervall. Men i vissa fall är uppgifterna lättare att utföra med dynamiska intervall. Det hjälper särskilt när vi arbetar med att ändra data. Vi kan till exempel ha ett kalkylblad där ny rad eller kolumn infogas eller läggs till varje dag / vecka / månad, i detta fall hjälper OFFSET-funktionen.

Begränsningar och alternativ för OFFSET-funktionen i Excel

Eftersom varje formel i Excel har sina egna begränsningar och alternativ, har vi sett hur och när vi ska använda OFFSET-funktionen i Excel.

Nedan är de kritiska begränsningarna för OFFSET-funktionen i Excel:

  • Resurshungig funktion:

Det betyder att när källdata ändras kommer formeln att beräknas igen för hela uppsättningen, vilket håller Excel upptagen under en längre tid. Om det finns ett litet kalkylblad påverkar det inte så mycket men om det finns ett antal kalkylblad kan det ta tid att beräkna om.

  • Svårighet att granska:

Som vi vet är referenser från Offset-funktionen dynamiska eller förändrade och kan innehålla en stor formel, vilket kan vara svårt att korrigera eller redigera enligt krav.

Alternativ till OFFSET-funktionen i Excel:

  • INDEX-funktion:

INDEX-funktionen i Excel kan också användas för att skapa ett dynamiskt intervall av referenser. Det är inte exakt samma som OFFSET. Men precis som OFFSET är INDEX-funktionen inte så mycket flyktig, därför kommer den inte att bromsa Excel, vilket är en begränsning för OFFSET.

  • INDIREKT Funktion:

Vi kan också använda INDIRECT-funktionen för att skapa ett dynamiskt intervall av referenser från många källor som cellvärden , text och de nämnda områdena.

Rekommenderade artiklar

Detta har varit en guide till OFFSET i Excel. Här diskuterar vi OFFSET-formeln i Excel och hur man använder OFFSET-funktionen i Excel tillsammans med praktiska exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar -

  1. MS Excel: LOOKUP-funktion
  2. Bästa exemplet på Excel XIRR-funktion
  3. Fantastisk guide om INTE funktion i Excel
  4. IPMT i Excel med exempel

Kategori:

Excel-tips