Kuidas kasutada funktsiooni Exceli OFFSET -

Lang L: none (table-of-contents)

Kokkuvõte

Exceli funktsioon OFFSET tagastab viite vahemikule, mis on konstrueeritud viie sisendiga: (1) alguspunkt, (2) rea nihe, (3) veeru nihe, (4) ridade kõrgus, (5) laius veerud. OFFSET on mugav valemites, mis nõuavad dünaamilist vahemikku.

Eesmärk

Looge antud lähtepunktist võrdlusnihe

Tagastusväärtus

Lahtriviide.

Süntaks

= OFFSET (viide, read, veerud, (kõrgus), (laius))

Argumendid

  • võrdluspunkt - lähtepunkt, mis antakse lahtriviite või vahemikuna.
  • read - algväärtuse alla nihutatavate ridade arv.
  • veerud - algviite paremal pool nihutatavate veergude arv.
  • kõrgus - (valikuline) Tagastatud viite kõrgus ridades.
  • width - (valikuline) Tagastatud viite laius veergudes.

Versioon

Excel 2003

Kasutusjuhised

Funktsioon Excel OFFSET tagastab dünaamilise vahemiku, mis on konstrueeritud viie sisendiga: (1) alguspunkt, (2) rea nihe, (3) veeru nihe, (4) kõrgus ridades, (5) laius veergudes.

Lähtepunkt ( võrdlusargument ) võib olla üks lahter või lahtrivahemik. Rida ja veerud argumendid on rakkude arv "nihe" lähtepunktiks. Kõrgus ja laius argumendid on vabatahtlik ja suuruse kindlakstegemiseks vahemikus, mis on loodud. Kui kõrgus ja laius on välja jäetud, on vaikimisi võrdluskõrgus ja -laius .

Näiteks viitega C5 algusega A1 on viide A1, read 4 ja veerud 2:

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

Viidaks C1: C5 A1-st, viide A1, read 0, veerud 2, kõrgus 5 ja laius 1:

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

Märkus. Laiuse võib ära jätta, kuna see on vaikimisi 1.

On tavaline, et OFFSET on pakitud teise funktsiooni, mis ootab vahemikku. Näiteks summale C1: C5, algusega A1:

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

OFFSETi põhieesmärk on võimaldada valemitel dünaamiliselt kohaneda olemasolevate andmete või kasutaja sisenditega. Funktsiooni OFFSET saab kasutada diagrammide või pöördtabelite dünaamilise nimelise vahemiku loomiseks, et tagada lähteandmete alati ajakohasus.

Märkus. Exceli dokumentatsioon ütleb, et kõrgus ja laius ei saa olla negatiivsed, kuid negatiivsed väärtused näivad olevat 1990ndate algusest peale hästi töötanud. Funktsioon OFFSET Google'i arvutustabelites ei võimalda kõrguse ega laiuse argumentide negatiivset väärtust.

Näited

Allpool toodud näited näitavad, kuidas OFFSET-i saab konfigureerida erinevat tüüpi vahemike tagastamiseks. Need ekraanid tehti koos Excel 365-ga, nii et OFFSET tagastab dünaamilise massiivi, kui tulemuseks on rohkem kui üks lahter. Exceli vanemates versioonides saate OFFSETist tagastatud tulemuste kontrollimiseks kasutada klahvi F9.

Näide 1

Alloleval ekraanil tagastame teises veerus (West) kolmanda väärtuse (märts) tagastamiseks OFFSET. H4 valem on:

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

Näide 2

Alloleval ekraanil tagastame kolmanda veeru (põhi) viimase väärtuse (juuni) tagastamiseks OFFSET. H4 valem on:

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

Näide # 3

Allpool kasutame OFFSET-i kõigi kolmandas veerus (põhjas) olevate väärtuste tagastamiseks. H4 valem on:

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

Näide 4

Allpool kasutame kõigi mai väärtuste (viies rida) tagastamiseks OFFSET-i. H4 valem on:

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

Näide 5

Allpool kasutame läänepiirkonna aprilli, mai ja juuni väärtuse tagastamiseks OFFSETi. H4 valem on:

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

Näide # 6

Allpool kasutame lääne ja põhja aprilli, mai ja juuni väärtuse tagastamiseks OFFSETi. H4 valem on:

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

Märkused

  • OFFSET tagastab ainult viite, ühtegi lahtrit ei teisaldata.
  • Mõlemad read ja veerud on võimalik esitada negatiivse numbrid vastupidises normaalne nihe suunas - negatiivne veerud nihe vasakule ja negatiivne rida nihe eespool.
  • OFFSET on "volatiilne funktsioon" - see arvutab iga töölehe muudatusega uuesti. Lenduvate funktsioonide abil saavad suuremad ja keerukamad töövihikud aeglaselt töötama.
  • OFFSET kuvab #REF! vea väärtus, kui nihe asub väljaspool töölehe serva.
  • Kui kõrgus või laius on välja jäetud, kasutatakse võrdluse kõrgust ja laiust .
  • OFFSET-i saab kasutada mis tahes muu funktsiooniga, mis eeldab viite saamist.
  • Exceli dokumentatsioon ütleb, et kõrgus ja laius ei saa olla negatiivsed, kuid negatiivsed väärtused toimivad.

Seotud videod

Kuidas luua dünaamiline nimega vahemik OFFSET-iga Selles videos vaatleme, kuidas luua dünaamiline nimega vahemik funktsiooni OFFSET abil, mis on levinum viis dünaamiliste nimede vahemiku loomiseks valemiga.

Huvitavad Artiklid...