Kako uporabljati funkcijo Excel OFFSET -

Povzetek

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 podane izhodiščne točke

Vrnjena vrednost

Sklic na celico.

Sintaksa

= OFFSET (sklic, vrstice, stolpci, (višina), (širina))

Argumenti

  • referenca - izhodišče, na voljo kot referenca celice ali obseg.
  • vrstice - število vrstic, ki jih je treba premakniti pod začetno referenco.
  • Zbirke - Število stolpcev za odmik na desni sklicevanja začetne.
  • višina - (neobvezno) Višina v vrsticah vrnjene reference.
  • width - (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 ( referenčni argument) je lahko ena celica ali obseg celic. V vrstice in COLS argumenti so število celic za "odmik" iz izhodišča. Na višina in širina trditve so neobvezna in določimo velikost območja, ki je ustvarjen. Če sta višina in širina izpuščeni, privzeto določita višino in širino sklica .

Na primer, z referenčnimi C5 se začne pri A1, referenčna A1, vrstice je 4 in stolpcev 2:

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

Za označitev C1: C5 A1, referenčna A1, vrstice je 0, stolpcev 2, višina je 5 in širina 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 ustvarjanje dinamičnega imenovanega obsega za grafikone ali vrtilne tabele, da zagotovite, da so izvorni podatki vedno posodobljeni.

Opomba: Excelova dokumentacija navaja, da višina in širina ne moreta biti negativni, vendar se zdi, da negativne vrednosti od začetka devetdesetih let delujejo dobro. Funkcija OFFSET v Google Preglednicah ne dovoljuje negativne vrednosti za argumente višine ali širine.

Primeri

Spodnji primeri kažejo, kako je OFFSET mogoče konfigurirati za vrnitev različnih vrst obsegov. Ti zasloni so bili posneti z Excelom 365, zato OFFSET vrne dinamično matriko, ko je rezultat več celic. V starejših različicah Excela lahko s tipko F9 preverite rezultate, vrnjene 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

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

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 # 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 # 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 # 6

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

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

Opombe

  • OFFSET vrne samo sklic, celice se ne premaknejo.
  • Tako vrstice kot stolpce lahko dobite kot negativna števila, da obrnete svojo normalno smer odmika - negativni stolpci se pomaknejo v levo, negativne vrstice pa zgoraj.
  • OFFSET je "spremenljiva funkcija" - preračunala se bo 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ščena, se uporabljata referenčna višina in širina .
  • OFFSET se lahko uporablja s katero koli drugo funkcijo, ki pričakuje, da bo prejela referenco.
  • Excel dokumentacija pravi, da višina in širina ne moreta biti negativni, negativne vrednosti pa delujejo.

Sorodni videoposnetki

Kako ustvariti dinamični imenovani obseg s funkcijo OFFSET V tem videoposnetku si bomo ogledali, kako ustvariti dinamični imenovani obseg s pomočjo funkcije OFFSET, najpogostejšega načina ustvarjanja dinamičnega obsega imen s formulo.

Zanimive Članki...