Excel OFFSET-formel (innehållsförteckning)

  • Vad är OFFSET-formel i Excel?
  • Hur använder man OFFSET-formeln i Excel?

Vad är OFFSET-formel i Excel?

OFFSET Formel i Excel ger en cell eller ett intervall av dynamiska rektangulära celler som en utgång som är en delmängd av det totala givna intervallet med ett specificerat antal rader, kolumner, höjd och bredd.

Syntax av OFFSET-formel i Excel

Förklaring av OFFSET-funktionen i Excel:

  • referens: Startpunkten för OFFSET eller vi kan säga ett intervall av celler som kan betraktas som basen för OFFSET.
  • rader : Antalet rader till OFFSET under eller över basreferensen.
  • cols: Antalet kolumner till OFFSET till höger eller vänster om basreferensen.
  • höjd: Antal rader i returreferens.
  • bredd: Antal kolumner i returnerad referens.

Höjd och bredd är valfria parametrar för denna funktion och kan användas för att bestämma storleken på den referens som skapats.

Hur använder man OFFSET-formeln i Excel?

Vi kommer nu att lära dig hur man skriver offsetformel för att få uppslagningsvärdet. Låt oss förstå offsetformeln med några exempel i Excel.

Du kan ladda ner denna OFFSET-formel Excel-mall här - OFFSET Formula Excel-mall

Excel OFFSET-formel - exempel # 1

Anta att vi har data enligt nedan.

  • Ange följande formel i cell E2:

= OFFSET (A1, 11, 1)

  • När du är klar med formeln trycker du på Enter och ser utgången.

Vad denna formel gjorde är att den har tagit hänvisning från cell A1 (år) och flyttats ned med 11 rader efter A1 (dvs. rad nr 12) och sedan fått värdet som är koordinat för den 11: e raden efter A1 och första kolumnen ( kolumn A = 0 kolumn B = 1 i detta fall. Som vi vet kan vi gå både höger och vänster). Vilket är $ 141, 27 miljoner.

Excel OFFSET-formel - exempel # 2

Nu kommer vi att se ett annat exempel på en referenscell för ett visst år. Lägg följande formel i cell E3:

= OFFSET (A1, 11, 0)

  • När du är klar med formeln trycker du på Enter och ser utgången.

Om du har sett formeln är den densamma som tidigare, den enda ändringen är i kolumnreferensen. Kolumnreferensen 0 (Noll) betyder att värdet kommer att fångas från kolumn A för 11: e rad efter A1 (som är 2010).

Excel OFFSET-formel - exempel # 3

I det här exemplet kommer vi att beräkna summan av de senaste raderna med Excel OFFSET-funktionen. Den allmänna formeln för att sammanfatta de senaste N-siffrorna är:

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

För att sammanfatta de senaste N-observationerna från dina data kan du använda SUM-funktionen tillsammans med OFFSET och COUNT-funktionen tillsammans. I det här exemplet kommer vi att sammanfatta de 5 senaste datapunkterna. Observera att när vi säger N är det en generalisering av valfritt heltal. Så du kan beräkna för sista 3, sista 4, sista 5 osv. Beroende på ditt datasystem såväl som ditt krav.

Låt oss göra det för vår datauppsättning:

  • Ange följande formel i cell E2 i Excel-filen.

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

  • Klicka på Enter för att se utgången.

Som vi redan har sett, skickar Excel OFFSET-funktionen ut rektangulära dynamiska områden när det ges en startreferens.

  • I detta fall tillhandahålls startreferensen i termer av B1 (som ett första argument till OFFSET-funktionen).
  • Vi använde COUNT-funktion eftersom vi behöver summan av observationer från det sista värdet. COUNT-funktion hjälper OFFSET att bestämma hur många observationer (rader) som finns i kolumn B. Den räknar alla numeriska värden i kolumn B (19 är räkningen).
  • Med räkningen som 19 blir OFFSET-formeln:

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

  • Denna formel startar förskjutning vid B1 med 19 rader och samma kolumn (0). Sedan hjälper höjdsparameter -5 denna funktion att utöka intervallet i bakåtriktning med 5 rader (se negativtecken som är associerat med 5).
  • Det dynamiska området för de senaste fem observationerna ges som en ingång till sumfunktion. Vilket ger en summa av dem från kolumn B.

Excel OFFSET-formel - exempel # 4

I det här exemplet kommer vi att beräkna medianförsäljningen för de senaste tre åren. OFFSET-funktionen kan användas som ett argument för att beräkna samma.

  • Lägg följande formel i cell E2:

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

  • Tryck på Enter för att se utgången.

Denna formel fungerar på de linjer som liknar den föregående.

  • OFFSET Skapar ett dynamiskt intervall för de tre senaste observationerna från kolumn B med hjälp av COUNT-funktionen (= OFFSET (A1, 19, 0, -3)).
  • Detta kan ges som en utgång till MEDIAN-funktionen. Som sedan sorterar observationerna i antingen stigande eller fallande ordning och sedan väljer det mesta mitten av mitten som du ser i utgången ($ 212, 07).

Excel OFFSET-formel - exempel # 5

Här kommer vi att beräkna medianåret med hjälp av MEDIAN och OFFSET-funktionen.

  • Använd följande formel för samma i cell E3:

= MEDIAN (FÖRSKJUTNING (A1, COUNT (A: A), 0, -3))

  • Klicka på Enter för att se utgången.

Samma sak har hänt här. Formeln tog upp ett dynamiskt intervall skapat med OFFSET-funktion med hjälp av COUNT. Och använde det som ett argument till en MEDIAN-funktion som sedan sorterade årsvärdena i stigande eller fallande ordning och sedan gav utdata som 2017.

Användning av OFFSET-formeln är att skapa dynamiska intervall. Det är alltid bättre att arbeta med dynamiska intervall istället för statiska. Som du kanske har data som läggs till då och då. Till exempel anställd master. Dina medarbetaruppgifter kommer alltid att öka då och då eftersom det måste vara några nya anställda som går med. Varje gång att använda samma formel är inte vettigt. Istället för det, skapa ett intervall som är dynamiskt med OFFSET-funktionen och se magin. Det kommer säkert att spara en tid.

Saker att komma ihåg

  • OFFSET-funktionen returnerar bara en referens till ett visst antal celler i Excel. Det flyttar inte cellerna från sina ursprungliga positioner.
  • Som standard fungerar den här funktionen på den övre delen (i termer av rader) och höger sida (i termer av kolumner). Det kan dock förses med negativa argument för att vända standardfunktionen. Som -negativ radargument hjälper till att kompensera data från ner till upp och negativt kolargument hjälper till att kompensera data till vänster.
  • OFFSET-formel kan bromsa Excel-funktionaliteten eftersom det är en formel som beräknas om varje gång det förändras kalkylbladet.
  • #REF-fel kommer att kastas om ditt intervall ligger utanför din arbetsbok.
  • Alla andra funktioner som förväntar sig en referens kan använda OFFSET-funktionen inom den.

Rekommenderade artiklar

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

  1. Hur kan jag lägga in text i Excel?
  2. Enkel guide till sortering i Excel
  3. LOOKUP-funktion i Excel
  4. Hur använder jag FIND-funktion i Excel?

Kategori:

Excel-tips