Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, miten Excel OFFSET -toiminto Excelissä viitepoikkeaman luomiseksi ensimmäisestä solusta.
OFFSET -toiminnon yleiskatsaus
OFFSET -toiminto alkaa määritetyllä soluviittauksella ja palauttaa soluviittauksen määrätyn määrän rivejä ja sarakkeita, jotka on siirretty alkuperäisestä viitteestä. Viitteet voivat olla yksi solu tai solualue. Siirtymällä voit myös muuttaa viittauksen kokoa tietyn määrän rivejä/sarakkeita.
(Huomaa, miten kaavasyötteet näkyvät)
IFERROR -toiminnon syntaksi ja tulot:
| 1 | = OFFSET (viite, rivit, sarakkeet, korkeus, leveys) |
viite - Ensimmäinen soluviittaus, josta haluat siirtää.
rivit - Poikkeavien rivien määrä.
cols - Siirrettävien sarakkeiden määrä
korkeus - VALINNAINEN: Säädä viitteen rivien määrää.
leveys - VALINNAINEN: Säädä viitteen sarakkeiden määrää.
Mikä on OFFSET -toiminto?
OFFSET -toiminto on yksi tehokkaimmista laskentataulukkotoiminnoista, koska se voi olla varsin monipuolinen sen luomisessa. Se antaa käyttäjälle mahdollisuuden määrittää solun tai alueen eri sijainneissa ja koossa.
VAROITUS: OFFSET -toiminto on yksi haihtuvista toiminnoista. Suurimman osan ajasta, kun työskentelet laskentataulukossasi, tietokone laskee kaavan uudelleen vain, jos syötteet ovat muuttaneet arvojaan. Haihtuva funktio laskee kuitenkin uudelleen joka kun teet muutoksen mihin tahansa soluun. Varovaisuutta on noudatettava sen varmistamiseksi, että et aiheuta suurta uudelleenlaskenta -aikaa haihtuvien toimintojen liiallisen käytön tai monien solujen vuoksi, jotka ovat riippuvaisia haihtuvan toiminnon tuloksesta.
Perusriviesimerkkejä
Jokaisessa OFFSET -toiminnon käytössä sinun on annettava lähtökohta tai ankkuri. Katsotaanpa tätä taulukkoa ymmärtääksemme tämän:
Käytämme "Bobia" solussa B3 ankkuripisteenä. Jos halusimme napata alla olevan arvon (Charlie), sanoisimme, että haluamme siirtää riviä yhdellä. Kaavamme näyttäisi
| 1 | = SIIRTO (B3, 1) |
Jos haluaisimme siirtyä ylöspäin, se olisi negatiivinen muutos. Voit ajatella tätä, kun rivinumero vähenee, joten meidän on vähennettävä. Siten saadaksemme yllä olevan arvon (Adam), kirjoittaisimme
| 1 | = SIIRTO (B2, -1) |
Perusarakkeiden esimerkit
Jatkamalla edellisestä esimerkistä saatua ideaa lisäämme taulukkoon toisen sarakkeen.
Jos halusimme napata opettajan Bobille, voisimme käyttää kaavaa
| 1 | = SIIRTO (B2, 0, 1) |
Tässä tapauksessa sanoimme, että haluamme siirtää nollariviä (eli pysyä samalla rivillä), mutta haluamme siirtää yhden sarakkeen. Sarakkeissa positiivinen luku tarkoittaa siirtymistä oikealle ja negatiiviset luvut vasemmalle.
SIIRTYMÄ JA MATCH
Oletetaan, että sinulla on useita tietosarakkeita ja haluat antaa käyttäjälle mahdollisuuden valita, mistä sarakkeesta haetaan tuloksia. Voit käyttää INDEX -toimintoa tai OFFSET -toimintoa. Koska MATCH palauttaa arvon suhteellisen sijainnin, meidän on varmistettava, että ankkuripiste on ensimmäisen mahdollisen arvon vasemmalla puolella. Harkitse seuraavaa asettelua:
B2: ssa kirjoitamme tämän kaavan:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH näyttää "helmikuulta" alueella C1: F1 ja löytää sen 2: statoinen solu. SIIRTYMÄ siirtää sitten yhden sarakkeen B2: n oikealle puolelle ja nappaa halutun arvon 9. Huomaa, että OFFSETilla ei ole ongelmia käyttää samaa solua, joka sisältää kaavan ankkuripisteenä.
HUOMAUTUS: Tätä tekniikkaa voidaan käyttää korvaamaan VLOOKUP tai HLOOKUP, kun haluat palauttaa arvon hakualueesi vasemmalta/yläpuolelta. Tämä johtuu siitä, että OFFSET voi tehdä negatiivisia siirtymiä.
OFFSET saadaksesi alueen
Voit käyttää 4th ja 5th argumentit OFFSET -funktiossa palauttaaksesi alueen yksittäisen solun sijasta. Oletetaan, että haluat laskea yhteen 3 saraketta tässä taulukossa.
| 1 | = KESKIARVO (SIIRTO (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
F2: ssa olemme valinneet sellaisen opiskelijan nimen, jolta haluamme hakea keskimääräiset testitulokset. Tätä varten käytämme kaavaa
| 1 | = KESKIARVO (SIIRTO (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH etsii nimemme sarakkeesta A ja palauttaa suhteellisen sijainnin, joka on esimerkissämme 3. Katsotaan miten tämä arvioidaan. Ensinnäkin OFFSET menee alas 3 riviä A1: stä ja 1 sarake oikein alkaen A1. Tämä asettaa meidät soluun B3.
| 1 | = KESKIARVO (SIIRTO (A1, 3, 1, 1, 3)) |
Seuraavaksi aiomme muuttaa alueen kokoa. Uuden alueen vasemman yläkulman solu on B3. Se on 1 rivi korkea ja 3 saraketta korkea, mikä antaa meille alueen B4: D4.
| 1 | = KESKIARVO (SIIRTO (A1,3, 1, 1, 3)) |
Huomaa, että vaikka voit laillisesti asettaa negatiivisia arvoja offset-argumentteihin, voit käyttää vain ei-negatiivisia arvoja mitoitusargumenteissa.
Lopuksi KESKI -toiminto näkee:
| 1 | = KESKIMÄÄRÄINEN (B4: D4) |
Näin saamme ratkaisumme 86.67
OFFSET dynaamisella SUM
Koska OFFSETia käytetään viittauksen löytämiseen sen sijaan, että osoitettaisiin suoraan soluun, siitä on eniten hyötyä, kun käsittelet tietoja, joihin on lisätty tai poistettu rivejä. Harkitse seuraavaa taulukkoa, jonka lopussa on yhteensä
| 1 | = SUMMA (B2: B4) |
Jos olisimme käyttäneet SUM -peruskaavaa tässä "= SUM (B2: B4)" ja lisäsimme sitten uuden rivin Billin tietueen lisäämiseksi, saisimme väärän vastauksen
Ajatelkaamme sen sijaan, kuinka ratkaista tämä Totalin näkökulmasta. Haluamme todella napata kaiken solusta B2 soluun hieman yli kokonaismäärän. Tapa, jolla voimme kirjoittaa tämän kaavaan, on tehdä rivin siirtymä -1. Siten käytämme tätä solun B5 kokonaismäärän kaavana:
| 1 | = SUMMA (B2: SIIRTO (B5, -1,0)) |
Tämä kaava tekee juuri kuvatun: aloita kohdasta B2 ja siirry 1 soluun kokonaissolumme yläpuolelle. Voit nähdä, kuinka Billin tietojen lisäämisen jälkeen kokonaismäärä päivittyy oikein.
OFFSET saadaksesi viimeiset N kohdetta
Oletetaan, että tallennat kuukausittaista myyntiä, mutta haluat nähdä viimeiset kolme kuukautta. Sen sijaan, että kaavoja olisi päivitettävä manuaalisesti, jotta ne muuttuisivat jatkuvasti, kun uusia tietoja lisätään, voit käyttää OFFSET -toimintoa COUNT -toiminnolla.
Olemme jo osoittaneet, kuinka voit käyttää OFFSETia solualueiden nappaamiseen. Määritelläksemme kuinka monta solua meidän on siirrettävä, käytämme COUNT: aa löytääksemme kuinka monta numeroita ovat sarakkeessa B. Katsotaanpa esimerkkitaulukkoamme.
| 1 | = SUMMA (SIIRTO ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Jos aloittaisimme kohdasta B1 ja siirtäisimme 4 riviä (sarakkeen B numeroiden määrä), päätyisimme alueen B5 alareunaan. Koska OFFSET ei kuitenkaan voi muuttaa negatiivista arvoa, meidän on tehtävä joitain muutoksia, jotta päädymme B3: een. Yleinen yhtälö tähän tulee olemaan
| 1 | LASKE (…) - N + 1 |
Laskemme koko sarakkeen määrän, vähennämme kuinka monta tahansa haluamme palauttaa (koska muutamme kokoa napataksemme ne) ja lisäämme sitten yhden (koska aloitamme olennaisesti offsetin nollasta).
Täältä näet, että olemme määrittäneet alueen saadaksesi viimeisen N kuukauden summan, keskiarvon ja enimmäismäärän. Kohdassa E1 olemme syöttäneet arvon 3. E2: ssa kaava on
| 1 | = SUMMA (SIIRTO ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Korostettu osa on yleinen yhtälö, josta juuri keskustelimme. Meidän ei tarvitse siirtää mitään sarakkeita. Muutamme sitten alueen kooksi siten, että se on 3 solua pitkä (määritetty E1 -arvon perusteella) ja 1 sarake leveä. Summamme ottaa tämän alueen ja antaa meille tuloksen 1 850 dollaria. Olemme myös osoittaneet, että voit laskea saman alueen maksimin keskiarvon yksinkertaisesti vaihtamalla ulkoisen funktion SUM -tilasta tilanteeseen mitä tahansa.
OFFSET dynaamiset validointiluettelot
Käyttämällä viimeisessä esimerkissä esitettyä tekniikkaa voimme myös rakentaa nimettyjä alueita, joita voidaan käyttää tietojen validoinnissa tai kaavioissa. Tästä voi olla apua, kun haluat määrittää laskentataulukon, mutta odotat luettelojemme/datamme muuttavan kokoa. Oletetaan, että myymälämme alkaa myydä hedelmiä, ja meillä on tällä hetkellä 3 vaihtoehtoa.
Jos haluat tehdä avattavan tietojen validoinnin, jota voimme käyttää muualla, määritämme nimetyksi alueeksi MyFruit
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
COUNT: n sijaan käytämme COUNTA: ta, koska käsittelemme tekstiarvoja. Tästä syystä COUNTAmme tulee olemaan yksi korkeampi, koska se laskee otsikkosolun A1: ssä ja antaa arvon 4. Jos kuitenkin siirrytään 4 rivillä, päädyimme soluun A5, joka on tyhjä. Tämän säätämiseksi vähennämme 1.
Nyt kun meillä on Named Range -asetus, voimme määrittää jonkin tiedon validoinnin solussa C4 käyttämällä luettelotyyppiä ja lähdettä:
| 1 | = MyFruit |
Huomaa, että pudotusvalikko näyttää vain kolme nykyistä kohdetta. Jos sitten lisäämme luetteloon lisää kohteita ja palaamme alasvetovalikkoon, luettelo näyttää kaikki uudet kohteet ilman, että meidän tarvitsee muuttaa kaavoja.
Varoituksia OFFSETin käytöstä
Kuten tämän artikkelin alussa mainittiin, OFFSET on epävakaa funktio. Et huomaa tätä, jos käytät sitä vain muutamassa solussa, mutta jos alat ottaa sen mukaan satoihin laskelmiin ja huomaat nopeasti, että tietokoneesi käyttää huomattavan paljon aikaa uudelleenlaskennassa joka kerta, kun teet muutoksia .
Lisäksi koska OFFSET ei nimeä suoraan katselemiaan soluja, muiden käyttäjien on vaikeampi tulla myöhemmin ja muuttaa kaavoja tarvittaessa.
Sen sijaan olisi suositeltavaa käyttää taulukoita (otettu käyttöön Office 2007: ssä), jotka sallivat rakenteelliset viittaukset. Tämä auttoi käyttäjiä antamaan yhden viitteen, jonka kokoa muutettiin automaattisesti, kun uusia tietoja lisättiin tai poistettiin.
Toinen vaihtoehto OFFSETin sijaan on tehokas INDEX -toiminto. INDEXin avulla voit rakentaa kaikki dynaamiset alueet, jotka näimme tässä artikkelissa ilman, että kyse on haihtuvasta funktiosta.
Lisämerkinnät
Käytä OFFSET -funktiota solun arvon (tai solualueen) palauttamiseen kuittaamalla tietty määrä rivejä ja sarakkeita lähtöviitteestä. Kun etsit vain yhtä solua, OFFSET -kaavat saavuttavat saman tarkoituksen kuin INDEX -kaavat käyttämällä hieman eri tekniikkaa. OFFSET -toiminnon todellinen voima on sen kyky valita solualue, jota käytetään toisessa kaavassa.
Kun käytät OFFSET -toimintoa, määrität alkusolun tai solualueen. Sitten määrität rivien ja sarakkeiden lukumäärän, jotka siirretään alkuperäisestä solusta. Voit myös muuttaa alueen kokoa; lisätä tai vähentää rivejä tai sarakkeita.
Palaa Excelin kaikkien toimintojen luetteloon
OFFSET Google Sheetsissä
OFFSET -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
