Exceli OFFSET-funktsioon (sisukord)

  • OFFSET Excelis
  • OFFSETi valem Excelis
  • Kuidas kasutada OFFSET-funktsiooni Excelis?

OFFSET Excelis

OFFSET-i funktsioon Excelis aitab kasutajal tagastada lahtri või lahtrite vahemiku, mille tähis pole. ridade ja veergude lahtrist. Saame täpsustada ei. ridade ja ei. tagastatavate veergude arv.

Allpool on Exceli OFFSETi valem:

OFFSETi funktsioon Excelis koosneb järgmistest argumentidest:

  • Viide: see on argument, millele tahame tasaarvestuse rajada. See võib olla lahtri viide või külgnevate lahtrite vahemik, kui mitte, siis Offset annab väärtuse #VALUE! Vea väärtus.
  • Ridad: see on ei. rida nihutada. Kui kasutame positiivset arvu, nihkub see allpool olevatele ridadele ja kui kasutatakse negatiivset arvu, korvab see ülaltoodud read.
  • Veerud: see on ei. veergude tasakaalustamiseks. Niisiis, see on sama mõiste nagu read, kui kasutame positiivset arvu, siis nihkub see parempoolsetesse veergudesse ja kui kasutatakse negatiivset arvu, siis nihkub see vasakpoolsesse veergu.
  • Kõrgus: see argument on valikuline. See peaks olema positiivne arv. See on arv, mis tähistab tagastatud viites ridade arvu.
  • Laius: see on ka valikuline argument. See peab olema positiivne arv. See on arv, mis tähistab tagastatud viites veergude arvu.

Märkused:

  • Funktsioon OFFSET tagastab #REF! tõrke väärtus, kui read ja veerud nihutavad viidet töölehe serva kohal.
  • Funktsioonis OFFSET peaks kõrguse või laiuse väljajätmisel olema võrdluskõrgus või -laius.
  • OFFSET tagastab viite, see ei liiguta tegelikult ühtegi lahtrit ega lahtrite vahemikku ega muuda valikut. Seda saab kasutada mis tahes funktsiooniga, mis eeldab võrdlusargumenti.

Kuidas kasutada OFFSET-funktsiooni Excelis?

OFFSET-i funktsioon Excelis on väga lihtne ja hõlpsasti kasutatav. Mõistame OFFSET-funktsiooni toimimist Excelis mõne OFFSET-i valemi näite abil.

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

Näide nr 1

Nihke valem tagastab lahtri viite, lähtudes meie määratud lähtepunktist, ridadest ja veergudest. Näeme seda allpool toodud näites:

= OFFSET (A1, 3, 1)

Valem soovitab Excelil kaaluda lahtri A1 lähtepunkti (viide), seejärel liigutage 3 rida allapoole (read) ja 1 veerg vasakule (veergude argument). Pärast seda tagastab OFFSETi valem lahtris B4 väärtuse.

Vasakul olev pilt näitab, et funktsiooni marsruut ja paremal olev ekraanipilt näitavad, kuidas me saame OFFSETi valemit reaalajas kasutada. Kahe valemi erinevus on see, et teine ​​(paremal) sisaldab lahtri viidet (D1) rea argumendis. Kuid kuna lahter D1 sisaldab arvu 1 ja esimese valemi ridade argumendis kuvatakse täpselt sama arv, annaksid mõlemad identse tulemuse - väärtuse B2.

Näide # 2 OFFSET-i funktsiooni kasutamine summa jaoks

Kuna me teame, et OFFSET-funktsiooni saab kasutada ka koos Exceli teise funktsiooniga, näeme selles näites funktsiooni SUM.

Ülaloleval pildil oletame, et peame märkide summa välja selgitama, siis saame kasutada ka OFFSET-funktsiooni.

Valem on järgmine:

Niisiis, ülaltoodud valemis on D5 esimene lahter, kus andmed algavad, mis on võrdlus lähte lahtriks. Pärast seda on ridade ja veergude väärtus 0, seega saame selle arvutada kui 0, 0. Siis on 4, mis tähendab 4 rida allpool seda viidet, mille jaoks Excel peab summa arvutama, ja siis lõpuks on see 1, see tähendab, et laiusena on 1 veerg.

Me võime ülaltoodud pildi seostada selgitatud näitega.

Ja allpool on tulemus:

Peame pärast valemile vajalikku lahtrisse sisestamist vajutama sisestusklahvi ja siis on meil tulemus.

Mida tuleks meeles pidada funktsiooni OFFSET kohta Excelis

  • Exceli funktsioon OFFSET tagastab lihtsalt viite, see ei liiguta tegelikult ühtegi lahtrit ega lahtrite vahemikku.
  • Kui OFFSETi valem tagastab lahtrite vahemiku, viitavad read ja veerud alati tagasitulekuvahemiku vasakpoolsele ülaosale.
  • Viide peaks sisaldama lahtrit või lahtrite vahemikku, vastasel juhul tagastab valem vea #VALUE.
  • Kui arvutustabeli serva kohal olevad read ja veerud on täpsustatud, tagastab Exceli nihke valem väärtuse #REF! viga.
  • OFFSET-funktsiooni saab kasutada mis tahes muu Exceli funktsiooni sees, mis aktsepteerib lahtri- või vahemikuviite oma argumentides.

Näiteks kui me kasutame valemit = OFFSET (A1, 3, 1, 1, 3) iseseisvalt, annab see väärtuse #VALUE! Viga, kuna tagastamise vahemik (1 rida, 3 veergu) lahtrisse ei mahu. Kui me koondame selle SUM-funktsiooni, nagu näiteks allpool:

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

Nihke valem tagastab 1-realiste väärtuste summa kolme veeru vahemiku võrra, mis on 3 rida allpool ja 1 veerg lahtrist A1 paremal, st lahtrites B4: D4 leiduvate väärtuste summa.

Nagu näeme, on esiletõstetud Mar kokku 36 + 63 + 82, mis võrdub 181-ga, mis saadi G2 tulemusel pärast summa ja tasaarvestuse valemi rakendamist. (kollasega esile tõstetud)

OFFSET-funktsiooni kasutamine Excelis

Nagu nägime, mida OFFSET-funktsioon ülaltoodud näite abil tegelikult teeb, võib tekkida küsimusi, miks Miks vaevata laulda pisut keerulist OFFSET-i valemit, miks mitte kasutada lihtsalt otsest viidet nagu summa või B4: D4?

OFFSET-i funktsioon Excelis sobib järgmiseks:

  • Vahemiku hankimine lähtelahtrist:

Mõnikord ei tea me vahemiku täpset ega tegelikku aadressi, me teame ainult seda, kust see algab teatud lahtrist. Sel juhul saame kasutada funktsiooni OFFSET, mida on lihtne kasutada.

  • Dünaamilise vahemiku loomine:

Viited nagu ülaltoodud näide B1: C4 on staatilised, see tähendab, et nad viitavad alati kindlale või fikseeritud vahemikule. Kuid mõnel juhul on dünaamiliste vahemikega ülesandeid lihtsam täita. See aitab eriti siis, kui töötame andmete muutmisega. Näiteks võiks meil olla arvutustabel, kuhu uus rida või veerg sisestatakse või lisatakse iga päev / nädal / kuu, sel juhul aitab funktsioon OFFSET.

Funktsiooni OFFSET piirangud ja alternatiivid Excelis

Kuna igal Exceli valemil on oma piirangud ja alternatiivid, nägime, kuidas ja millal Exceli funktsiooni OFFSET kasutada.

Allpool on toodud Exceli funktsiooni OFFSET kriitilised piirangud:

  • Ressurssidevaene funktsioon:

See tähendab, et kui lähteandmed muutuvad, arvutatakse valem kogu komplekti jaoks ümber, mis hoiab Exceli pikema aja jooksul hõivatud. Kui on väike arvutustabel, ei mõjuta see nii palju, kuid kui tabeleid on palju, võib Exceli ümberarvutamine võtta aega.

  • Raskus ülevaatamisel:

Nagu me teame, on funktsiooni Nihutamine tagastatud viited dünaamilised või muutuvad ja võivad sisaldada suurt valemit, mida võib olla keeruline vastavalt nõudele parandada või muuta.

Funktsiooni OFFSET alternatiivid Excelis:

  • INDEX funktsioon:

Exceli funktsiooni INDEX võib kasutada ka dünaamilise viitevahemiku loomiseks. See pole täpselt sama, mis OFFSET. Kuid nagu OFFSET, pole ka INDEX-funktsioon nii palju lenduv, seega ei aeglusta see Exceli kasutamist, mis on OFFSET-i piiranguks.

  • KAUDNE funktsioon:

Samuti saame funktsiooni INDIRECT kasutada dünaamilise viitevahemiku loomiseks paljudest allikatest, näiteks lahtri väärtused , tekst ja nimetatud vahemikud.

Soovitatavad artiklid

See on olnud Excelis OFFSET-i juhend. Siin käsitleme Excelis OFFSET-i valemit ja kuidas kasutada OFFSET-i funktsiooni Excelis koos praktiliste näidete ja allalaaditava Exceli malliga. Võite vaadata ka meie teisi soovitatud artikleid -

  1. MS Excel: LOOKUP funktsioon
  2. Parim näide Exceli XIRR-funktsioonist
  3. Hämmastav juhend Exceli funktsiooni NOT kohta
  4. IPMT Excelis koos näidetega

Kategooria:

Exceli näpunäited