✅ Funktsioon Excel OFFSET - Valeminäited + TASUTA video

Lang L: none (table-of-contents)

Funktsioon Excel OFFSET (näide + video)
Exceli OFFSET -funktsiooni põhitõdede mõistmine
Funktsioon Excel OFFSET - näited
Funktsiooni Excel OFFSET alternatiivid
Funktsioon Excel OFFSET - videoõpetus

Funktsioon Excel OFFSET (näide + video)

Millal kasutada funktsiooni Excel OFFSET

Funktsiooni Excel OFFSET saab kasutada siis, kui soovite saada viite, mis nihutab lähtepunktist määratud arvu ridu ja veerge.

Mida see tagastab

See tagastab viite, millele funktsioon OFFSET osutab.

Süntaks

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

Sisendargumendid

viide - Viide, mida soovite tasaarvestada. See võib olla lahtri viide või külgnevate lahtrite vahemik.
read - nihutatavate ridade arv. Kui kasutate positiivset numbrit, nihutab see allpool olevaid ridu ja kui kasutatakse negatiivset numbrit, siis nihutab see ülaltoodud ridu.
kollased - nihutatavate veergude arv. Kui kasutate positiivset numbrit, nihutab see paremale veerge ja kui kasutatakse negatiivset, siis nihutab see vasakpoolsetele veergudele.
[kõrgus] - see on number, mis tähistab tagastatava viite ridade arvu.
[laius] - see on number, mis tähistab veergude arvu tagastatud viites.

Exceli OFFSET -funktsiooni põhitõdede mõistmine

Funktsioon Excel OFFSET on tõenäoliselt üks segasemaid funktsioone Excelis.

Võtame lihtsa näite malemängust. Males on tükk nimega Rook (tuntud ka kui torn, marquess või rektor).

[Pilt viisakalt: Imeline Wikipedia]

Nüüd, nagu kõigil teistel maletükkidel, on ka Rookil kindel rada, millel ta saab laual ringi liikuda. See võib liikuda otse (paremale/vasakule või üles/alla lauale), kuid see ei saa minna diagonaalselt.

Oletame näiteks, et meil on Excelis malelaud (nagu allpool näidatud), antud kastis (antud juhul D5) saab Rook liikuda ainult neljas esiletõstetud suunas.

Kui ma palun teil nüüd Rooki praeguselt positsioonilt kollasega esile tõstetud kohale viia, siis mida te ütlete talle?

Te palute tal astuda kaks sammu allapoole ja kaks sammu paremale … kas pole?

Ja see on OFFSET -funktsiooni toimimise lähedane lähedus.

Nüüd vaatame, mida see Excelis tähendab. Ma tahan alustada lahtriga D5 (see on koht, kus Rook asub) ja seejärel minna kaks rida alla ja kaks veergu paremale ning tuua väärtus sealt lahtrist.

Valem oleks järgmine:
= OFFSET (kust alustada, mitu rida alla, mitu veergu paremale)

Nagu näete, on ülaltoodud näite valem lahtris J1 = OFFSET (D5,2,2).

See algas D5 -st ja läks siis kaks rida alla ja kaks veergu paremale ning jõudis lahtrisse F7. Seejärel tagas see väärtuse lahtris F7.

Ülaltoodud näites vaatasime funktsiooni OFFSET 3 argumendiga. Kuid on veel kaks valikulist argumenti, mida saab kasutada.

Vaatame siin lihtsat näidet:

Oletame, et soovite kasutada viidet lahtrile A1 (kollane) ja soovite kogu valemis sinisega esile tõsta (C2: E4).

Kuidas te seda klaviatuuri kasutades teeksite? Esmalt lähete lahtrisse C2 ja seejärel valite kõik lahtrid lahtris C2: E4.

Nüüd vaatame, kuidas seda OFFSET valemi abil teha:

= Nihe (A1,1,2,3,3)

Kui kasutate seda valemit lahtris, tagastab see #VALUE! viga, kuid kui jõuate redigeerimisrežiimi ja valite valemi ning vajutate klahvi F9, näete, et see tagastab kõik sinisega esile tõstetud väärtused.

Nüüd vaatame, kuidas see valem töötab:

= Nihe (A1,1,2,3,3)

Esimene argument on lahter, kust see peaks algama.
Teine argument on 1, mis käsib Excelil tagastada viite, mis on nihutatud 1 rea võrra.
Kolmas argument on 2, mis käsib Excelil tagastada viite, mis on 2 veeru võrra nihutatud.
Neljas argument on 3. See täpsustab, et viide peaks hõlmama 3 rida. Seda nimetatakse kõrguse argumendiks.
Viies argument on 3. See täpsustab, et viide peaks hõlmama 3 veergu. Seda nimetatakse laiuse argumendiks.

Nüüd, kui teil on viide lahtrivahemikule (C2: E4), saate seda kasutada muude funktsioonide raames (nt SUM, COUNT, MAX, AVERAGE).

Loodetavasti on teil põhiteadmised funktsiooni Excel OFFSET kasutamisest. Nüüd vaatame mõningaid praktilisi näiteid funktsiooni OFFSET kasutamiseks.

Funktsioon Excel OFFSET - näited

Siin on kaks näidet funktsiooni Excel OFFSET kasutamise kohta.

Näide 1 - veeru viimati täidetud lahtri leidmine

Oletame, et teil on veerus mõned andmed, nagu allpool näidatud.

Veeru viimase lahtri leidmiseks kasutage järgmist valemit:

= Nihe (A1, loendus (A: A) -1,0)

See valem eeldab, et peale näidatud väärtuste pole muid väärtusi ja nendes lahtrites pole tühja lahtrit. See töötab, lugedes täidetud lahtrite koguarvu ja nihutab vastavalt lahtri A1.

Näiteks sel juhul on 8 väärtust, nii et COUNT (A: A) tagastab 8. Viimase väärtuse saamiseks nihutame lahtri A1 7 võrra.

Näide 2 - dünaamilise rippmenüü loomine

Näite 1 kontseptsioonisaateid saate laiendada, et luua dünaamilisi nimevahemikke. Need võivad olla kasulikud, kui kasutate valemites nimetatud vahemikke või loote rippmenüüsid ja tõenäoliselt lisate/kustutate andmeid nimetatud vahemikku moodustavast viitest.

Siin on näide:

Pange tähele, et rippmenüü kohandub automaatselt aasta lisamisel või eemaldamisel.

See juhtub, kuna rippmenüü loomiseks kasutatav valem on dünaamiline ja tuvastab kõik lisamised või kustutamised ning kohandab vahemikku vastavalt.

Siin on, kuidas seda teha.

Valige lahter, kuhu soovite rippmenüü sisestada.
Avage Andmed -> Andmetööriistad -> Andmete valideerimine.
Valige dialoogiboksis Andmete valideerimine vahekaardil Seaded rippmenüüst Loend.
Sisestage allikasse järgmine valem: = OFFSET (A1,0,0, COUNT (A: A), 1)
Klõpsake nuppu OK.

Vaatame, kuidas see valem töötab:

Funktsiooni OFFSET kolm esimest argumenti on A1, 0 ja 0. See tähendab sisuliselt, et see tagastaks sama võrdluslahtri (mis on A1).
Neljas argument on kõrguse kohta ja siin tagastab funktsioon COUNT loendis olevate üksuste koguarvu. See eeldab, et loendis pole tühikuid.
Viies argument on 1, mis näitab, et veeru laius peaks olema üks.

Lisamärkmed:

OFFSET on lenduv funktsioon (kasutage ettevaatlikult).
- See arvutab uuesti, kui Exceli töövihik on avatud või kui töölehel käivitatakse arvutus. See võib pikendada töötlemisaega ja aeglustada teie töövihikut.
Kui kõrguse või laiuse väärtus on välja jäetud, loetakse see võrdlusväärtuseks.
Kui ridu ja kollased on negatiivsed numbrid, siis nihke suund on vastupidine.

Funktsiooni Excel OFFSET alternatiivid

Funktsiooni Excel OFFSET mõningate piirangute tõttu soovite paljud kaaluda selle alternatiive:

Funktsioon INDEX: funktsiooni INDEX saab kasutada ka lahtri viite tagastamiseks. Klõpsake siin, et näha näidet dünaamilise nimega vahemiku loomiseks funktsiooni INDEX abil.
Exceli tabel: kui kasutate Exceli tabelis struktureeritud viiteid, ei pea te muretsema uute andmete lisamise ja valemite kohandamise pärast.