Laadige alla töövihiku näide
See õpetus näitab, kuidas Funktsioon Excel OFFSET Excelis viite nihke loomiseks algsest lahtrist.
OFFSET -funktsioonide ülevaade
Funktsioon OFFSET Alustab määratletud lahtri viitega ja tagastab lahtri viite kindla arvu ridu ja veerge, mis on nihutatud algsest viitest. Viited võivad olla üks lahter või lahtrivahemik. Nihe võimaldab ka viite suurust muuta teatud arvu ridade/veergude jaoks.
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni IFERROR süntaks ja sisendid:
| 1 | = OFFSET (viide, read, veerud, kõrgus, laius) |
viide - Lahtri esialgne viide, mida soovite nihutada.
ridu - nihutatavate ridade arv.
kollased - nihutatavate veergude arv.
kõrgus - VALIKULINE: viite ridade arvu reguleerimine.
laius - VALIKULINE: viite veergude arvu reguleerimine.
Mis on OFFSET funktsioon?
Funktsioon OFFSET on üks võimsamaid arvutustabeli funktsioone, kuna see võib olla üsna mitmekülgne. See annab kasutajale võimaluse määrata lahtrit või vahemikku erinevates asukohtades ja suurustes.
ETTEVAATUST: OFFSET -funktsioon on üks lenduvaid funktsioone. Kui arvutustabelis töötate, arvutab arvuti enamasti valemi ümber ainult siis, kui sisendid on oma väärtusi muutnud. Lenduv funktsioon aga arvutab uuesti iga kui muudate mõnda lahtrit. Tuleb olla ettevaatlik, et vältida lenduvate funktsioonide liigse kasutamise või lenduvate funktsioonide tulemusest sõltuvate lahtrite tõttu liiga palju ümberarvestusaega.
Põhilised rea näited
Funktsiooni OFFSET iga kasutamise korral peate andma lähtepunkti või ankru. Vaatame seda tabelit, et aidata sellest aru saada:
Kinnituspunktina kasutame lahtris B3 sõna „Bob”. Kui me tahaksime tabada väärtust, mis on allpool (Charlie), siis ütleksime, et tahame rida nihutada 1 võrra. Meie valem näeks välja selline
| 1 | = Nihe (B3, 1) |
Kui me tahaksime üles liikuda, oleks see negatiivne nihe. Võite mõelda sellele, kui rea number väheneb, seega peame lahutama. Seega, et saada ülaltoodud väärtust (Aadam), kirjutaksime
| 1 | = Nihe (B2, -1) |
Veergude põhinäited
Jätkates eelmise näite ideed, lisame oma tabelisse veel ühe veeru.
Kui me tahaksime Bobi õpetajat haarata, saaksime valemit kasutada
| 1 | = Nihe (B2, 0, 1) |
Sel juhul ütlesime, et tahame null rida nihutada (ehk jääda samale reale), kuid tahame nihutada 1 veergu. Veergude puhul tähendab positiivne arv nihutamist paremale ja negatiivne arv vasakule.
NIIRE ja MATCH
Oletame, et teil on mitu andmeveergu ja soovite anda kasutajale võimaluse valida, millisest veerust tulemused tuua. Võite kasutada funktsiooni INDEX või OFFSET. Kuna MATCH tagastab väärtuse suhtelise asukoha, peame veenduma, et kinnituspunkt asub meie esimesest võimalikust väärtusest vasakul. Kaaluge järgmist paigutust:
B2 -s kirjutame järgmise valemi:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH näeb välja "veebruar" vahemikus C1: F1 ja leiab selle 2nd kamber. Nihutamine nihutab seejärel 1 veeru B2 -st paremale ja võtab soovitud väärtuse 9. Pange tähele, et OFFSET -il pole probleeme, kasutades sama lahtrit, mis sisaldab kinnituspunkti valemit.
MÄRKUS. Seda tehnikat saab kasutada VLOOKUPi või HLOOKUPi asendajana, kui soovite tagastada väärtuse otsinguvahemiku vasakult/kõrgemalt. Seda seetõttu, et OFFSET võib teha negatiivseid nihkeid.
OFFSET vahemiku saamiseks
Võite kasutada 4th ja 5th argumente funktsioonis OFFSET, et tagastada vahemik, mitte ainult üks lahter. Oletame, et soovite selles tabelis kokku võtta 3 veergu.
| 1 | = KESKMINE (nihe (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
F2 -s oleme valinud õpilase nime, kellelt tahame saada tema keskmisi testitulemusi. Selleks kasutame valemit
| 1 | = KESKMINE (nihe (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH otsib veerust A meie nime ja tagastab suhtelise positsiooni, mis on meie näites 3. Vaatame, kuidas seda hinnatakse. Esiteks läheb nihe alla 3 rida A1 -st ja 1 veerg õige alates A1. See asetab meid lahtrisse B3.
| 1 | = KESKMINE (nihe (A1, 3, 1, 1, 3)) |
Järgmisena muudame vahemiku suurust. Uue vahemiku vasakus ülanurgas on B3. See on 1 rea kõrge ja 3 veeru kõrge, andes meile vahemiku B4: D4.
| 1 | = KESKMINE (nihe (A1,3, 1, 1, 3)) |
Pange tähele, et kuigi te saate seadistada negatiivseid väärtusi nihkeargumentidesse, saate suuruse argumentides kasutada ainult mitte-negatiivseid väärtusi.
Lõpuks näeb meie KESKMINE funktsioon järgmist:
| 1 | = KESKMINE (B4: D4) |
Seega saame oma lahenduse 86.67
Dünaamilise SUM -iga nihe
Kuna OFFSET -i kasutatakse viite leidmiseks, selle asemel et otse lahtrile osutada, on see kõige kasulikum, kui tegelete andmetega, millele on lisatud või kustutatud ridu. Mõelge järgmisele tabelile, mille allosas on kogusumma
| 1 | = SUMMA (B2: B4) |
Kui oleksime siin kasutanud SUM -i põhivalemit “= SUM (B2: B4)” ja seejärel sisestanud Billi kirje lisamiseks uue rea, oleks meil vale vastus
Selle asemel mõtleme, kuidas seda Kokku vaatenurgast lahendada. Me tõesti tahame haarata kõike alates lahtrist B2 kuni lahtrini veidi üle meie summa. Selle valemisse kirjutamise viis on rea nihutamine -1. Seega kasutame seda lahtris B5 kogu valemina:
| 1 | = SUMMA (B2: nihe (B5, -1,0)) |
See valem teeb seda, mida me just kirjeldasime: alustage B2 -st ja minge 1 lahtrisse meie koguraku kohal. Näete, kuidas pärast Billi andmete lisamist värskendatakse meie kogusummat õigesti.
OFFSET viimase N eseme hankimiseks
Oletame, et registreerite igakuist müüki, kuid soovite vaadata viimase kolme kuu kohta. Selle asemel, et valemeid käsitsi värskendada, et uute andmete lisamisel kohandamist jätkata, saate funktsiooni OFFSET kasutada COUNT -iga.
Oleme juba näidanud, kuidas saate OFFSET -i abil kasutada erinevaid lahtreid. Et määrata, kui palju lahtreid peame nihutama, kasutame COUNT, et leida nende arv numbrid on veerus B. Vaatame meie näidistabelit.
| 1 | = SUMMA (nihe ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Kui me alustaksime punktist B1 ja nihutaksime 4 rida (veerus B olevate numbrite arv), jõuaksime oma vahemiku B5 lõppu. Kuna aga OFFSET ei saa negatiivse väärtusega suurust muuta, peame tegema mõningaid kohandusi, et jõuda punkti B3. Selle üldine võrrand tuleb teha
| 1 | COUNT (…) - N + 1 |
Arvutame kogu veeru, lahutame, kui palju tahame tagasi saata (kuna muudame nende haaramiseks suurust), ja lisame siis 1 (kuna sisuliselt alustame nihkega nullist).
Siin näete, et oleme seadistanud vahemiku, et saada viimase N kuu summa, keskmine ja maksimum. E1 -s oleme sisestanud väärtuse 3. E2 -s on meie valem järgmine
| 1 | = SUMMA (nihe ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Esiletõstetud jaotis on meie üldine võrrand, mida me just arutasime. Me ei pea veerge nihutama. Seejärel muudame vahemiku suuruseks 3 lahtrit (määratud E1 väärtuse järgi) ja 1 veeru laiuseks. Seejärel võtab meie SUM selle vahemiku ja annab tulemuseks 1850 dollarit. Samuti oleme näidanud, et saate arvutada sama vahemiku maksimumide keskmise, lihtsalt lülitades välise funktsiooni SUM -lt olukorrale.
OFFSET dünaamilise valideerimise loendid
Viimases näites näidatud tehnikat kasutades saame luua ka nimevahemikke, mida saaks kasutada andmete valideerimisel või diagrammides. See võib olla kasulik, kui soovite arvutustabeli seadistada, kuid eeldate, et meie loendite/andmete suurus muutub. Oletame, et meie kauplus hakkab puuvilju müüma ja meil on praegu 3 valikut.
Andmete valideerimise rippmenüü tegemiseks, mida saame kasutada mujal, määratleme nimega vahemik MyFruit kui
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
COUNT asemel kasutame COUNTA, kuna tegeleme tekstiväärtustega. Sellegipoolest on meie COUNTA üks kõrgem, kuna loendab päise lahtri A1 -s ja annab väärtuseks 4. Kui nihutame 4 rida, jõuame lahtrisse A5, mis on tühi. Selle kohandamiseks lahutame 1.
Nüüd, kui meil on oma nimega vahemiku seadistus, saame lahtris C4 seadistada mõningaid andmete valideerimisi, kasutades loenditüüpi koos allikaga:
| 1 | = MyFruit |
Pange tähele, et rippmenüü näitab ainult meie kolme praegust üksust. Kui lisame oma loendisse veel üksusi ja läheme tagasi rippmenüüsse, kuvatakse loendis kõik uued üksused, ilma et me peaksime valemit muutma.
Ettevaatusabinõud OFFSETi kasutamisel
Nagu käesoleva artikli alguses mainitud, on OFFSET lenduv funktsioon. Te ei märka seda, kui kasutate seda vaid mõnes lahtris, kuid kui hakkate seda kaasama sadadesse arvutustesse ja märkate kiiresti, et teie arvuti kulutab iga kord muudatuste tegemisel märkimisväärselt palju aega ümber arvutamiseks .
Lisaks, kuna OFFSET ei nimeta otseselt lahtreid, mida ta vaatab, on teistel kasutajatel hiljem raskem tulla ja vajadusel teie valemeid muuta.
Selle asemel oleks soovitatav kasutada tabeleid (tutvustatud Office 2007 -s), mis võimaldavad struktuurseid viiteid. Need aitasid kasutajatel anda ühe viite, mille suurust automaatselt kohandati uute andmete lisamisel või kustutamisel.
Teine võimalus, mida kasutada OFFSET asemel, on võimas INDEX -funktsioon. INDEX võimaldab teil luua kõik dünaamilised vahemikud, mida me selles artiklis nägime, ilma et see oleks lenduv funktsioon.
lisamärkmed
Kasutage funktsiooni OFFSET, et tagastada lahtri väärtus (või lahtrivahemik), nihutades lähtearvust ette antud arvu ridu ja veerge. Kui otsite ainult ühte lahtrit, saavutavad OFFSET -valemid sama eesmärgi kui INDEX -valemid, kasutades veidi erinevat tehnikat. Funktsiooni OFFSET tegelik jõud seisneb selle võimes valida lahtrite vahemik, mida kasutatakse teises valemis.
Funktsiooni OFFSET kasutamisel määrate algse lahtri või lahtrite vahemiku. Seejärel määrate algsest lahtrist nihutatavate ridade ja veergude arvu. Samuti saate vahemikku muuta; ridade või veergude lisamine või lahutamine.
Tagasi kõigi Exceli funktsioonide loendisse
OFFSET Google'i arvutustabelites
Funktsioon OFFSET töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
