Excel

Funkcija Excel OFFSET

Excel Offset Function

Excel OFFSET funkcijaPovzetek

Funkcija Excel OFFSET vrne sklic na obseg, sestavljen s petimi vhodi: (1) izhodišče, (2) odmik vrstice, (3) odmik stolpca, (4) višina v vrsticah, (5) širina v stolpci. OFFSET je priročen v formulah, ki zahtevajo dinamični razpon.

Namen Ustvari referenčni odmik od dane začetne točke Vrnjena vrednost Referenca celice. Sintaksa = OFFSET (sklic, vrstice, stolpci, [višina], [širina]) Argumenti
  • sklic - Začetna točka, na voljo kot referenca celice ali obseg.
  • vrstice - Število vrstic, ki jih je treba premakniti pod začetno referenco.
  • cols - Število stolpcev, ki se pomaknejo desno od začetne reference.
  • višina - [neobvezno] Višina v vrsticah vrnjene reference.
  • premer - [neobvezno] Širina v stolpcih vrnjene reference.
Različica Excel 2003 Opombe o uporabi

Funkcija Excel OFFSET vrne dinamični obseg, sestavljen s petimi vhodi: (1) izhodišče, (2) odmik vrstice, (3) odmik stolpca, (4) višina v vrsticah, (5) širina v stolpcih.



Izhodišče ( sklic argument) je lahko ena celica ali obseg celic. The vrstice in cols Argumenti so število celic, ki se 'odmikajo' od začetne točke. The višina in premer Argumenti niso obvezni in določajo velikost ustvarjenega obsega. Kdaj višina in premer so izpuščeni, privzeto so višina in širina sklic .



če in potem formula v excelu

Na primer, na sklic C5, ki se začne pri A1, sklic je A1, vrstice je 4 in cols je 2:

 
= OFFSET (A1,4,2) // returns reference to C5

Za sklicevanje na C1: C5 iz A1, sklic je A1, vrstice je 0, cols je 2, višina je 5 in premer je 1:



 
= OFFSET (A1,0,2,5,1) // returns reference to C1:C5

Opomba: širino lahko izpustite, saj bo privzeto 1.

Običajno je videti OFFSET zavit v drugo funkcijo, ki pričakuje obseg. Na primer do SUM C1: C5, ki se začne pri A1:

 
= SUM ( OFFSET (A1,0,2,5,1)) // SUM C1:C5

Glavni namen OFFSET je, da se formule dinamično prilagodijo razpoložljivim podatkom ali uporabniškemu vnosu. Funkcijo OFFSET lahko uporabite za izdelavo a dinamično imenovano območje za grafikone ali vrtilne tabele, da zagotovite, da so izvorni podatki vedno posodobljeni.



Opomba: Excelova dokumentacija navaja višina in premer ne more biti negativna, vendar se zdi, da so negativne vrednosti dobro delovale od zgodnjih devetdesetih let . Funkcija OFFSET v Google Preglednicah ne dovoljuje negativne vrednosti za argumente višine ali širine.

kako naredim kljukico v

Primeri

Spodnji primeri kažejo, kako je OFFSET mogoče konfigurirati za vrnitev različnih vrst obsegov. Ti zasloni so bili posneti z Excel 365 , tako da OFFSET vrne a dinamično polje ko je rezultat več celic. V starejših različicah Excela lahko uporabite Tipka F9 za preverjanje rezultatov, vrnjenih iz OFFSET.

Primer # 1

Na spodnjem zaslonu z OFFSET vrnemo tretjo vrednost (marec) v drugem stolpcu (zahod). Formula v H4 je:

 
= OFFSET (B3,3,2) // returns D6

Primer funkcije OFFSET 1

2. primer

Na spodnjem zaslonu z OFFSET vrnemo zadnjo vrednost (junij) v tretjem stolpcu (sever). Formula v H4 je:

 
= OFFSET (B3,6,3) // returns E9

Primer funkcije OFFSET 2

3. primer

Spodaj z OFFSET vrnemo vse vrednosti v tretjem stolpcu (sever). Formula v H4 je:

 
= OFFSET (B3,1,3,6) // returns E4:E9

Primer funkcije OFFSET 3

Primer # 4

Spodaj z OFFSET vrnemo vse vrednosti za maj (peta vrstica). Formula v H4 je:

 
= OFFSET (B3,5,1,1,4) // returns C8:F8

Primer funkcije OFFSET 4

Primer # 5

Spodaj uporabljamo OFFSET za vrnitev vrednosti aprila, maja in junija za zahodno regijo. Formula v H4 je:

 
= OFFSET (B3,4,2,3,1) // returns D7:D9

Primer funkcije OFFSET 5

Primer # 6

Spodaj uporabljamo OFFSET za vrnitev vrednosti aprila, maja in junija za zahod in sever. Formula v H4 je:

vlookup za več listov s primerom
 
= OFFSET (B3,4,2,3,2) // returns D7:E9

Primer funkcije OFFSET 6

Opombe

  • OFFSET vrne samo sklic, celice se ne premaknejo.
  • Oboje vrstice in cols lahko dobite kot negativna števila, da obrnete njihovo normalno smer odmika - negativno cols odmik v levo in negativni vrstice odmik zgoraj.
  • OFFSET je ' hlapna funkcija '- izračuna se z vsako spremembo delovnega lista. Zaradi spremenljivih funkcij lahko večji in bolj zapleteni delovni zvezki delujejo počasi.
  • OFFSET bo prikazal #REF! vrednost napake, če je odmik zunaj roba delovnega lista.
  • Če sta višina ali širina izpuščeni, sta višina in širina sklic se uporablja.
  • OFFSET se lahko uporablja s katero koli drugo funkcijo, ki pričakuje, da bo prejela referenco.
  • V dokumentaciji programa Excel piše višina in premer ne more biti negativna, vendar negativne vrednosti delujejo.


^