Exceli OFFSETi valem (sisukord)

  • Mis on OFFSETi valem Excelis?
  • Kuidas kasutada OFFSETi valemit Excelis?

Mis on OFFSETi valem Excelis?

OFFSETi valem Excelis annab väljundina lahtri või dünaamiliste ristkülikukujuliste lahtrite vahemiku, mis on kogu antud vahemiku alamhulk koos kindla arvu ridade, veergude, kõrguse ja laiusega.

OFFSET-i valemi süntaks Excelis

OFFSET-i funktsiooni selgitus Excelis:

  • viide: OFFSET-i lähtepunkt või võime öelda lahtrite vahemiku, mida võib käsitada OFFSET-i alusena.
  • read : OFFSET-i ridade arv baasviite all või üle selle.
  • veerud: OFFSET-i veergude arv baasviitest paremal või vasakul.
  • kõrgus: tagastatud viites ridade arv.
  • laius: tagastatud viites veergude arv.

Kõrgus ja laius on selle funktsiooni valikulised parameetrid ja neid saab kasutada loodud viite suuruse määramiseks.

Kuidas kasutada OFFSETi valemit Excelis?

Nüüd õpime, kuidas kirjutada korvamisvalemit, et saada otsinguväärtust. Mõistame nihke valemit mõne näitega Excelis.

Selle OFFSET Vormel Exceli malli saate alla laadida siit - OFFSET Vormel Exceli mall

Exceli OFFSETi valem - näide # 1

Oletame, et meil on allpool toodud andmed.

  • Lahtrisse E2 sisestage järgmine valem:

= OFFSET (A1, 11, 1)

  • Kui see on valemiga tehtud, vajutage sisestusklahvi ja vaadake väljundit.

Mis see valem tegi, see on lähtunud lahtrist A1 (aasta) ja liikunud 11 rea võrra allapoole A1 (st rea nr 12) allapoole ja saavutanud väärtuse, mis on 11. rea koordinaat A1 ja esimese veeru järel ( veerg A = 0 veerg B = 1. Sel juhul saame teada nii paremale kui ka vasakule. Mis on 141, 27 miljonit dollarit.

Exceli OFFSETi valem - näide nr 2

Nüüd näeme veel ühte näidet konkreetse aasta võrdluslahtrist. Pange lahtrisse E3 järgmine valem:

= OFFSET (A1, 11, 0)

  • Kui see on valemiga tehtud, vajutage sisestusklahvi ja vaadake väljundit.

Kui olete valemit näinud, on see sama, mis eelmine, ainus muudatus on veeru viites. Veeru viide 0 (null) tähendab, et väärtus püütakse veerust A 11. reale A1 järel (mis on 2010).

Exceli OFFSETi valem - näide nr 3

Selles näites arvutame funktsiooni Excel OFFSET abil viimase N ridade summa. Viimaste N-arvude summeerimise üldvalem on järgmine:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

Kui soovite oma andmetest viimased N tähelepanekut kokku võtta, võite kasutada funktsiooni SUM koos OFFSET ja COUNT funktsioonidega. Selles näites võtame kokku 5 viimast andmepunkti. Pange tähele, et kui me ütleme N, on see üldistus mis tahes täisarvuga. Nii saate arvutada viimase 3, viimase 4, viimase 5 jne jaoks, sõltuvalt teie andmestikust ja teie nõudest.

Teeme seda meie andmekogumi jaoks:

  • Sisestage järgmine valem Exceli faili lahtrisse E2.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Väljundi kuvamiseks klõpsake sisestusklahvi.

Nagu juba nägime, väljastab funktsioon Excel OFFSET lähteviite andmisel ristkülikukujulisi dünaamilisi vahemikke.

  • Sel juhul antakse lähteviide B1 kujul (funktsiooni OFFSET esimese argumendina).
  • Kasutasime funktsiooni COUNT, kuna vajame vaatluste summat viimasest väärtusest. Funktsioon COUNT aitab OFFSET-il määrata, mitu vaatlust (rida) on veerus B. See loendab kõik veeru B arvväärtused (19 on loendus).
  • Kui arv on 19, saab OFFSETi valem:

= OFFSET (B1, 19, 0, -5)

  • See valem alustab nihutamist punktis B1 19 rea ja sama veeruga (0). Siis aitab kõrguseparameeter -5 sellel funktsioonil laiendada vahemikku 5 rea võrra tahapoole (vt 5-ga seotud negatiivset märki).
  • Viie viimase vaatluse dünaamiline vahemik antakse sisendina funktsioonile summa. Milline on saagis B veeru saagikute liitmisel

Exceli OFFSETi valem - näide # 4

Selles näites arvutame viimase 3 aasta keskmise müügi. Funktsiooni OFFSET saab sama arvutamiseks kasutada argumendina.

  • Pange lahtrisse E2 järgmine valem:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Väljundi kuvamiseks vajutage sisestusklahvi.

See valem töötab samadel joontel kui eelmine.

  • OFFSET Loob veeru B abil viimase kolme vaatluse dünaamilise vahemiku COUNT funktsiooni abil (= OFFSET (A1, 19, 0, -3)).
  • Seda saab väljundina funktsioonile MEDIAN. Mis seejärel sorteerib vaatlused kas kasvavas või kahanevas järjekorras ja valib siis keskmise keskväärtuse, nagu näete väljundis (212, 07 dollarit).

Exceli OFFSETi valem - näide # 5

Siin arvutatakse mediaan aasta, kasutades funktsiooni MEDIAN ja OFFSET.

  • Kasutage lahtris E3 sama valemi jaoks:

= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))

  • Väljundi kuvamiseks klõpsake sisestusklahvi.

Sama asi on juhtunud ka siin. Valem võttis dünaamilise vahemiku, mis on loodud funktsiooni OFFSET abil COUNT abil. Ja kasutas seda argumendina MEDIAN-funktsioonile, mis sorteeris aasta väärtused kasvavas või kahanevas järjekorras ning andis seejärel väljundi 2017. aastaks.

OFFSET-i valemi kasutamine on dünaamiliste vahemike loomisel. Staatiliste asemel on alati parem töötada dünaamiliste vahemikega. Nagu teil võib olla andmeid, mida aeg-ajalt lisatakse. Näiteks töötaja kapten. Teie töötajate põhiandmeid tõstetakse alati ja alati, kuna liituma peab mõne uue töötajaga. Iga kord, kui sama valemit kasutatakse, pole sellel mingit mõtet. Selle asemel looge OFFSET-funktsiooni abil dünaamiline vahemik ja vaadake võlu. See säästab kindlasti teie aega.

Asjad, mida meeles pidada

  • Funktsioon OFFSET tagastab viite ainult konkreetsele rakuvahemikule Excelis. See ei liiguta rakke algsest asendist.
  • Vaikimisi töötab see funktsioon ülemises osas (ridade osas) ja paremas servas (veergude osas). Vaikefunktsioonide ümberpööramiseks võib sellele siiski lisada negatiivseid argumente. Sarnaselt -negatiivne reaargument aitab andmeid nihutada allapoole üles ja negatiivne veerg argument aitab tasakaalustada andmeid vasakule.
  • OFFSETi valem võib aeglustada Exceli funktsionaalsust, kuna see on valem, mis arvutatakse ümber iga kord, kui töölehte muudetakse.
  • #REF-i viga visatakse, kui teie ulatus jääb väljaspool töövihiku piire.
  • Mis tahes muu funktsioon, mis eeldab viidet, saab selles kasutada OFFSET-funktsiooni.

Soovitatavad artiklid

See on juhend Exceli OFFSETi vormelisse. Siin arutatakse, kuidas kasutada OFFSET Function'i Excelis koos praktiliste näidete ja allalaaditava excelimalliga. Võite vaadata ka meie teisi soovitatud artikleid -

  1. Kuidas Excelis teksti mähkida?
  2. Lihtne juhend Excelis sortimiseks
  3. LOOKUP funktsioon Excelis
  4. Kuidas kasutada FIND-funktsiooni Excelis?

Kategooria:

Exceli näpunäited