Excel OFFSET-funktion (indholdsfortegnelse)

  • OFFSET i Excel
  • OFFSET Formel i Excel
  • Hvordan bruges OFFSET-funktionen i Excel?

OFFSET i Excel

OFFSET-funktion i Excel hjælper brugeren med at returnere en celle eller et område med celler, som er et specificeret nr. af rækker og kolonner fra en celle. Vi kan specificere nej. af rækker og nr. af kolonner, der skal returneres.

Nedenfor er OFFSET-formlen i Excel:

OFFSET Funktion i Excel består af følgende argumenter:

  • Reference: Det er det argument, hvorfra vi vil basere forskydningen. Det kan være en cellehenvisning eller en række tilstødende celler, hvis ikke Offset returnerer #VÆRDET! Fejlværdi.
  • Rækker: Det er nej. af rækker, der skal udlignes. Hvis vi bruger et positivt tal, udlignes det til rækkerne nedenfor, og hvis der bruges et negativt tal, udlignes det rækkerne ovenfor.
  • Kolonner: Det er til nr. af kolonner, der skal kompenseres. Så det er det samme koncept som rækker, hvis vi bruger et positivt tal, udlignes det til kolonnerne til højre, og hvis der bruges et negativt tal, er dens forskydninger til kolonnerne til venstre.
  • Højde: Dette argument er valgfrit. Det skal være et positivt tal. Det er et tal, der repræsenterer antallet af rækker i den returnerede reference.
  • Bredde: Dette er også et valgfrit argument. Det skal være et positivt tal. Det er et tal, der repræsenterer antallet af kolonner i den returnerede reference.

Bemærkninger:

  • OFFSET-funktionen i Excel returnerer #REF! fejlværdi, hvis rækker og kolonner forskydes henvisning over kanten af ​​regnearket.
  • I OFFSET-funktionen antages det at være den samme højde eller bredde som en reference, hvis højde eller bredde er udeladt.
  • OFFSET returnerer en reference, det bevæger sig faktisk ikke nogen celler eller række af celler eller ændrer markeringen. Det kan bruges til enhver funktion, der forventer et referenceargument.

Hvordan bruges OFFSET-funktionen i Excel?

OFFSET Funktion i Excel er meget enkel og let at bruge. Lad os forstå, hvordan OFFSET-funktionen fungerer i Excel ved hjælp af et eksempel på OFFSET-formel.

Du kan downloade denne OFFSET-funktion Excel-skabelon her - OFFSET-funktion Excel-skabelon

Eksempel 1

Offsetformlen returnerer en cellehenvisning baseret på et startpunkt, rækker og kolonner, som vi specificerer. Vi kan se det i nedenstående eksempel:

= OFFSET (A1, 3, 1)

Formlen fortæller Excel at overveje celle A1 til startpunkt (reference), og flyt derefter 3 rækker ned (rækker) og 1 kolonne til venstre (kolonnens argument). Herefter returnerer OFFSET-formlen værdien i celle B4.

Billedet til venstre viser, at ruten til funktion og skærmbilledet til højre viser, hvordan vi kan bruge OFFSET-formlen på data i realtid. Forskellen mellem de to formler er, at den anden (til højre) inkluderer en cellehenvisning (D1) i rækkeens argument. Men da celle D1 indeholder nummer 1, og nøjagtigt det samme nummer vises i rækkerargumentet med den første formel, ville begge få et identisk resultat - værdien i B2.

Eksempel # 2 Anvendelse af OFFSET-funktion til sum

Som vi ved, at OFFSET-funktionen også kan bruges med den anden funktion af Excel, vil vi i dette eksempel derfor se med SUM-funktionen.

I ovenstående billede, antager vi, at vi skal finde ud af summen af ​​mærker, så kan vi også bruge OFFSET-funktionen.

Formlen er nedenfor:

I ovenstående formel er D5 den første celle, hvor dataene begynder, som er en reference som startcelle. Derefter er rækkerne og kolonneværdien 0, så vi kan sætte den som 0, 0. Så er der 4, hvilket betyder 4 rækker under den reference, som Excel skal beregne summen for, og så er den til sidst 1, det betyder, at der er en kolonne som bredde.

Vi kan henvise til ovenstående billede for at co-relatere det forklarede eksempel.

Og nedenfor er resultatet:

Vi skal bare trykke på Enter efter indtastning af en formel i den krævede celle, og så har vi resultatet.

Ting at huske på OFFSET-funktionen i Excel

  • OFFSET-funktionen i Excel returnerer bare en reference, den bevæger ikke nogen celler eller række af celler i virkeligheden.
  • Hvis OFFSET-formlen returnerer et område med celler, henviser rækkerne og kolonnerne altid til den øverste venstre celle i det returnerede område.
  • Henvisningen skal omfatte en celle eller et område med celler, ellers formel returnerer #VALUE fejlen.
  • Hvis de rækker og kolonner, der er specificeret over kanten af ​​regnearket, vil Offsetformel i Excel returnere #REF! fejl.
  • OFFSET-funktion kan bruges i enhver anden Excel-funktion, der accepterer en celle- eller områdereference i dens argumenter.

For eksempel, hvis vi bruger formlen = OFFSET (A1, 3, 1, 1, 3) alene, vil det give en #VALUE! Fejl, da en række retur (1 række, 3 kolonner) ikke passer ind i en celle. Men hvis vi sorterer det i SUM-funktionen som nedenfor:

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

Offsetformlen returnerer summen af ​​værdier i en 1-række med et 3-kolonnes interval, der er 3 rækker under og 1 kolonne til højre for celle A1, dvs. summen af ​​værdier i celler B4: D4.

Som vi kan se, er det samlede antal 36 + 63 + 82 af den fremhævede Mar lig med 181, hvilket kom som et resultat i G2 efter anvendelse af sum og Offsetformel. (fremhævet i gult)

Brug af OFFSET-funktion i Excel

Som vi har set, at hvad OFFSET-funktionen faktisk gør ved hjælp af ovenstående eksempel, kan vi have spørgsmål, hvorfor hvorfor gider sunget OFFSET-formel, der er en smule kompleks, hvorfor ikke blot bruge en direkte reference som sum eller B4: D4?

OFFSET-funktionen i Excel er meget god til nedenfor:

  • Henter området fra startcellen:

Nogle gange ved vi ikke den nøjagtige eller faktiske adresse på området, vi ved kun, hvor det starter fra en bestemt celle. I dette tilfælde kan vi bruge OFFSET-funktionen, som er let at gå.

  • Oprettelse af dynamisk rækkevidde:

Henvisningerne som ovenstående eksempel B1: C4 er statiske, det betyder, at de altid refererer til et givet eller fast interval. Men i nogle tilfælde er opgaverne lettere at udføre med dynamiske intervaller. Det hjælper især, når vi arbejder med skiftende data. For eksempel kunne vi have et regneark, hvor en ny række eller kolonne indsættes eller tilføjes hver dag / uge / måned, i dette tilfælde hjælper OFFSET-funktionen.

Begrænsninger og alternativer til OFFSET-funktionen i Excel

Da hver formel i Excel har sine egne begrænsninger og alternativer, har vi set, hvordan og hvornår man skal bruge OFFSET-funktionen i Excel.

Nedenfor er de kritiske begrænsninger for OFFSET-funktionen i Excel:

  • Ressource-sulten funktion:

Det betyder, at når der sker en ændring i kildedataene, beregnes formlen igen for hele sættet, hvilket holder Excel optaget i længere tid. Hvis der er et lille regneark, vil det ikke påvirke så meget, men hvis der er et antal regneark, kan Excel tage tid at genberegne igen.

  • Problemer med gennemgang:

Som vi ved, er referencerne, der er returneret af Offset-funktionen, dynamiske eller ændrende og kan indeholde en stor formel, som kan være vanskeligt at rette eller redigere efter krav.

Alternativer til OFFSET-funktionen i Excel:

  • INDEX-funktion:

INDEX-funktionen i Excel kan også bruges til at oprette en dynamisk række referencer. Det er ikke nøjagtigt det samme som OFFSET. Men ligesom OFFSET, er INDEX-funktionen ikke så meget ustabil, og derfor vil den ikke nedsætte Excel, hvilket er en begrænsning for OFFSET.

  • INDIREKT Funktion:

Vi kan også bruge INDIRECT-funktionen til at oprette et dynamisk interval af referencer fra mange kilder som celleværdier , tekst og de navngivne intervaller.

Anbefalede artikler

Dette har været en guide til OFFSET i Excel. Her diskuterer vi OFFSET-formlen i Excel og Sådan bruges OFFSET-funktionen i Excel sammen med praktiske eksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler -

  1. MS Excel: LOOKUP-funktion
  2. Bedste eksempel på Excel XIRR-funktion
  3. Fantastisk guide til NOT funktion i Excel
  4. IPMT i Excel med eksempler

Kategori:

Excel-tip