Excel OFFSET funkcija (kazalo)

  • OFFSET v Excelu
  • OFFSET Formula v Excelu
  • Kako uporabljati funkcijo OFFSET v Excelu?

OFFSET v Excelu

Funkcija OFFSET v Excelu pomaga uporabniku vrniti celico ali obseg celic, ki je določeno št. vrstic in stolpcev iz celice. Določimo lahko št. vrstic in št. stolpcev, ki jih je treba vrniti.

Spodaj je Formula OFFSET v Excelu:

Funkcija OFFSET v Excelu je sestavljena iz naslednjih argumentov:

  • Sklic: Gre za argument, na katerem želimo utemeljiti pobotanje. Lahko je referenca celice ali vrsta sosednjih celic, če ne, Offset vrne # VALUE! Vrednost napake
  • Vrstice: To je št. vrstic pobotati. Če uporabimo pozitivno število, se to izravna s spodnjimi vrsticami, če je uporabljeno negativno število, pa poravna vrstico zgoraj.
  • Stolpci: Je za št. stolpcev za izravnavo. Torej gre za enak koncept kot vrstice, če uporabimo pozitivno število, potem se izravna s stolpci na desni strani in če je uporabljeno negativno število, potem odmika stolpce na levi strani.
  • Višina: Ta argument ni obvezen. To bi moralo biti pozitivno število. To je število, ki predstavlja število vrstic v vrnjeni referenci.
  • Širina: To je tudi izbirni argument. Biti mora pozitivna številka. To je število, ki predstavlja število stolpcev v vrnjeni referenci.

Opombe:

  • Funkcija OFFSET v Excelu vrne #REF! vrednost napake, če vrstice in stolpci izravnajo sklic preko roba delovnega lista.
  • V funkciji OFFSET naj bi bil enak referenčni višini ali širini, če izpustimo višino ali širino.
  • OFFSET vrne referenco, dejansko ne premakne nobene celice ali obsega celic ali ne spremeni izbire. Uporabite ga lahko s katero koli funkcijo, ki pričakuje referenčni argument.

Kako uporabljati funkcijo OFFSET v Excelu?

OFFSET Funkcija v Excelu je zelo preprosta in enostavna za uporabo. Dovolite, da razumete delovanje OFFSET funkcije v Excelu s primerom formule OFFSET.

Tu lahko prenesete predlogo OFFSET Function Excel tukaj - OFFSET Funkcija Predloga Excela

Primer # 1

Formula Offset vrne referenco celice na podlagi izhodišča, vrstic in stolpcev, ki jih določimo. Lahko ga vidimo v spodnjem primeru:

= OFFSET (A1, 3, 1)

Formula pove Excelu, naj upošteva celico A1 za začetno točko (referenco) in nato premakne 3 vrstice navzdol (vrstice) in 1 stolpec v levo (argument stolpcev). Po tem formula OFFSET vrne vrednost v celici B4.

Slika na levi kaže, da pot funkcije in posnetek zaslona na desni prikazujeta, kako lahko v realnem času uporabimo formulo OFFSET. Razlika med obema formulama je, da druga (na desni) vključuje argument celice (D1) v argumentu vrstice. Ker pa celica D1 vsebuje številko 1 in se popolnoma enako število pojavlja v argumentu vrstic prve formule, bi obe vrnili enak rezultat - vrednost v B2.

Primer # 2 Uporaba funkcije OFFSET za vsoto

Ker vemo, da lahko funkcijo OFFSET uporabimo tudi z drugo funkcijo Excela, bomo v tem primeru videli s funkcijo SUM.

Recimo, da na zgornji sliki najdemo vsoto ocen, potem lahko uporabimo tudi funkcijo OFFSET.

Formula je spodaj:

Torej, v zgornji formuli D5 je prva celica, kjer se začnejo podatki, ki je referenca kot začetna celica. Po tem je vrednost vrstic in stolpcev 0, zato jo lahko postavimo kot 0, 0. Potem je tu 4, kar pomeni 4 vrstice pod referenco, za katero mora Excel izračunati vsoto in na koncu je 1, to pomeni, da je 1 stolpec kot širina.

Zgoraj prikazano sliko lahko povežemo s pojasnjenim primerom.

Spodaj je rezultat:

Po vnosu formule v zahtevano celico moramo pritisniti Enter in rezultat je rezultat.

Spomniti se na funkcijo OFFSET v Excelu

  • Funkcija OFFSET v Excelu samo vrne referenco in ne premakne nobenih celic ali obsega celic.
  • Če Formula OFFSET vrne obseg celic, se vrstice in stolpci vedno vrnejo na zgornjo levo celico v vrnjenem območju.
  • Sklic mora vsebovati celico ali obseg celic, sicer bo formula vrnila napako #VALUE.
  • Če so vrstice in stolpci, podani čez rob preglednice, bo Offset formula v Excelu vrnila #REF! napaka.
  • OFFSET funkcijo je mogoče uporabiti v kateri koli drugi funkciji Excel, ki v svojih argumentih sprejme sklic na celico ali obseg.

Na primer, če uporabljamo formulo = OFFSET (A1, 3, 1, 1, 3) sam, bo dal # VALUE! Napaka, ker obseg vrnitve (1 vrstica, 3 stolpci) ne ustreza celici. Če pa ga primerjamo s funkcijo SUM, kot je spodaj:

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

Formula Offset vrne vsoto vrednosti v 1 vrstici z razponom 3 stolpcev, ki je 3 vrstice spodaj in 1 stolpec desno od celice A1, torej skupne vrednosti v celicah B4: D4.

Kot lahko vidimo, da je skupno 36 + 63 + 82 označenih Mar enako 181, kar je nastalo kot G2 po uporabi vsote in formule Offset. (rumeno poudarjeno)

Uporaba OFFSET funkcije v Excelu

Kot smo videli, da tisto, kar funkcija OFFSET dejansko počne s pomočjo zgornjega primera, lahko imamo vprašanja, zakaj Zakaj bi se trudili prepevati formulo OFFSET, ki je nekoliko zapletena, zakaj ne bi preprosto uporabili neposredne reference kot vsota ali B4: D4?

Funkcija OFFSET v Excelu je spodaj zelo dobra:

  • Pridobivanje obsega iz začetne celice:

Včasih ne vemo natančnega ali dejanskega naslova obsega, vemo le, od kod se začne iz določene celice. V tem primeru lahko uporabimo funkcijo OFFSET, ki je enostavna.

  • Ustvarjanje dinamičnega obsega:

Sklici, kot je zgornji primer B1: C4, so statični, kar pomeni, da se vedno nanašajo na dani ali fiksni obseg. Toda v nekaterih primerih so naloge lažje opraviti z dinamičnimi razponi. Še posebej pomaga, kadar delamo s spreminjanjem podatkov. Na primer, lahko imamo preglednico, v katero se vsak dan / teden / mesec vstavljajo ali dodajajo nove vrstice ali stolpci, v tem primeru bo pomagala funkcija OFFSET.

Omejitve in alternative funkcije OFFSET v Excelu

Ker ima vsaka formula v Excelu svoje omejitve in alternative, smo videli, kako in kdaj uporabljati funkcijo OFFSET v Excelu.

Spodaj so kritične omejitve funkcije OFFSET v Excelu:

  • Funkcija, ki je lačna vira:

To pomeni, da se bo vsakič, ko bo prišlo do spremembe izvornih podatkov, formula ponovno izračunala za celoten niz, kar bo Excel zasedlo dlje časa. Če je majhna preglednica, potem to ne bo vplivalo tako veliko, če pa je več preglednic, bo morda Excel potreboval čas za ponovni izračun.

  • Težave pri pregledu:

Kot vemo, so reference, ki jih je vrnila funkcija Offset, dinamične ali spreminjajoče se in lahko vsebujejo veliko formulo, ki bi lahko popravila ali uredila po potrebi.

Nadomestne funkcije OFFSET v Excelu:

  • Funkcija INDEX:

Funkcijo INDEX v Excelu lahko uporabite tudi za ustvarjanje dinamičnega obsega referenc. Ni povsem enako kot OFFSET. Toda tako kot OFFSET tudi funkcija INDEX ni toliko nestanovitna, zato Excel ne bo upočasnil, kar je omejitev za OFFSET.

  • POSREDNA funkcija:

Funkcijo INDIRECT lahko uporabimo tudi za ustvarjanje dinamičnega obsega referenc iz številnih virov, kot so vrednosti celic , besedilo in imenovani obsegi.

Priporočeni članki

To je vodnik za OFFSET v Excelu. Tukaj razpravljamo o formuli OFFSET v Excelu in kako uporabljati funkcijo OFFSET v Excelu skupaj s praktičnimi primeri in naloženo predlogo Excela. Ogledate si lahko tudi druge naše predlagane članke -

  1. MS Excel: Funkcija LOOKUP
  2. Najboljši primer funkcije Excela XIRR
  3. Neverjeten vodnik o NE funkciji v Excelu
  4. IPMT v Excelu s primeri

Kategorija:

Excel Nasveti