Excel OFFSET -toiminto (esimerkki + video)
Milloin käyttää Excel OFFSET -toimintoa?
Excel OFFSET -toimintoa voidaan käyttää, kun haluat saada viitteen, joka kompensoi määritetyn määrän rivejä ja sarakkeita lähtöpisteestä.
Mitä se palauttaa
Se palauttaa viittauksen, johon OFFSET -toiminto viittaa.
Syntaksi
= OFFSET (viite, rivit, sarakkeet, [korkeus], [leveys])
Syöttöargumentit
- viite - Viite, josta haluat korvata. Tämä voi olla soluviittaus tai joukko vierekkäisiä soluja.
- rivit - poistettavien rivien lukumäärä. Jos käytät positiivista lukua, se kompensoi alla olevat rivit ja jos käytetään negatiivista lukua, se siirtyy yllä oleville riveille.
- cols - siirrettävien sarakkeiden lukumäärä. Jos käytät positiivista lukua, se siirtyy oikealla oleviin sarakkeisiin ja jos käytetään negatiivista lukua, se siirtyy vasemmalla oleviin sarakkeisiin.
- [korkeus] - tämä on luku, joka edustaa palautetun viitteen rivien määrää.
- [leveys] - tämä on luku, joka edustaa palautetun viitteen sarakkeiden määrää.
Excel OFFSET -toiminnon perusteiden ymmärtäminen
Excel OFFSET -toiminto on mahdollisesti yksi hämmentävimmistä Excelin toiminnoista.
Otetaan yksinkertainen esimerkki shakkipelistä. Shakissa on pala, jota kutsutaan nimellä torni (tunnetaan myös nimellä torni, markiisi tai rehtori).
[Kuva kohteliaasti: Ihana Wikipedia]
Nyt, kuten kaikilla muillakin shakkinappuloilla, Rookilla on kiinteä polku, jolla se voi liikkua laudalla. Se voi mennä suoraan (oikealle/vasemmalle tai ylös/alas laudalle), mutta se ei voi mennä vinosti.
Oletetaan esimerkiksi, että meillä on shakkilauta Excelissä (kuten alla), ja tietyssä ruudussa (tässä tapauksessa D5) Rook voi mennä vain neljään korostettuun suuntaan.
Jos pyydän sinua ottamaan torni nykyisestä sijainnistaan keltaisella korostettuun, mitä sanot torniin?
Pyydät sitä ottamaan kaksi askelta alaspäin ja kaksi askelta oikealle… eikö niin?
Ja se on lähellä OFFSET -toiminnon toimintaa.
Katsotaan nyt, mitä tämä tarkoittaa Excelissä. Haluan aloittaa solulla D5 (jossa torni on) ja sitten mennä kaksi riviä alas ja kaksi saraketta oikealle ja noutaa arvo solusta.
Kaava olisi seuraava:
= OFFSET (mistä aloittaa, kuinka monta riviä alaspäin, kuinka monta saraketta oikealle)
Kuten näette, yllä olevan esimerkin kaava solussa J1 on = OFFSET (D5,2,2).
Se alkoi D5: stä ja meni sitten kaksi riviä alas ja kaksi saraketta oikealle ja saavutti solun F7. Sitten se palautti arvon solussa F7.
Yllä olevassa esimerkissä tarkastelimme OFFSET -funktiota kolmella argumentilla. Mutta on vielä kaksi valinnaista argumenttia, joita voidaan käyttää.
Katsotaanpa yksinkertaista esimerkkiä täältä:
Oletetaan, että haluat käyttää viittausta soluun A1 (keltaisella) ja haluat viitata koko kaavalla, joka on korostettu sinisellä (C2: E4).
Miten sen tekisit näppäimistöllä? Siirry ensin soluun C2 ja valitse sitten kaikki solut kohdassa C2: E4.
Katsotaan nyt, miten tämä tehdään OFFSET -kaavan avulla:
= SIIRTO (A1,1,2,3,3)
Jos käytät tätä kaavaa solussa, se palauttaa #ARVO! virhe, mutta jos pääset muokkaustilaan ja valitset kaavan ja painat F9, näet, että se palauttaa kaikki sinisellä korostetut arvot.
Katsotaan nyt, miten tämä kaava toimii:
= SIIRTO (A1,1,2,3,3)
- Ensimmäinen argumentti on solu, josta sen pitäisi alkaa.
- Toinen argumentti on 1, joka käskee Excelin palauttamaan viittauksen, joka on OFFSET by 1 rivi.
- Kolmas argumentti on 2, joka käskee Excelin palauttamaan viitteen, joka on POISTETTU 2 sarakkeella.
- Neljäs argumentti on 3. Tämä määrittää, että viittauksen tulee kattaa 3 riviä. Tätä kutsutaan korkeusargumentiksi.
- Viides argumentti on 3. Tämä määrittää, että viittauksen tulee kattaa 3 saraketta. Tätä kutsutaan leveysargumentiksi.
Nyt kun sinulla on viittaus solualueeseen (C2: E4), voit käyttää sitä muissa toiminnoissa (kuten SUM, COUNT, MAX, AVERAGE).
Toivottavasti sinulla on hyvät perustiedot Excel OFFSET -toiminnon käytöstä. Katsotaanpa nyt joitain käytännön esimerkkejä OFFSET -toiminnon käytöstä.
Excel OFFSET -toiminto - Esimerkkejä
Tässä on kaksi esimerkkiä Excel OFFSET -toiminnon käytöstä.
Esimerkki 1 - Sarakkeen viimeisen täytetyn solun löytäminen
Oletetaan, että sinulla on joitain tietoja alla olevassa sarakkeessa:
Voit etsiä sarakkeen viimeisen solun käyttämällä seuraavaa kaavaa:
= SIIRTO (A1, LASKE (A: A) -1,0)
Tämä kaava olettaa, että muita arvoja ei ole kuin näytetyt ja että näissä soluissa ei ole tyhjää solua. Se toimii laskemalla täytettyjen solujen kokonaismäärä ja kompensoi solun A1 vastaavasti.
Esimerkiksi tässä tapauksessa arvoja on 8, joten COUNT (A: A) palauttaa 8. Siirtämme solua A1 7: llä saadaksemme viimeisen arvon.
Esimerkki 2 - Dynaamisen pudotusvalikon luominen
Voit laajentaa esimerkin 1 konseptiesityksiä dynaamisten nimettyjen alueiden luomiseksi. Näistä voi olla hyötyä, jos käytät nimettyjä alueita kaavoissa tai avatessasi avattavia valikkoja ja lisäät/poistat tietoja todennäköisesti viittauksesta, joka muodostaa nimetyn alueen.
Tässä on esimerkki:
Huomaa, että pudotusvalikko säätyy automaattisesti, kun vuosi lisätään tai poistetaan.
Tämä tapahtuu, kun pudotusvalikon luomiseen käytetty kaava on dynaaminen ja tunnistaa mahdolliset lisäykset tai poistot ja säätää aluetta vastaavasti.
Voit tehdä tämän seuraavasti:
- Valitse solu, johon haluat lisätä avattavan valikon.
- Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
- Valitse Tietojen vahvistus -valintaikkunan Asetukset -välilehden avattavasta luettelosta.
- Kirjoita lähteeseen seuraava kaava: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Napsauta OK.
Katsotaanpa, miten tämä kaava toimii:
- OFFSET -funktion kolme ensimmäistä argumenttia ovat A1, 0 ja 0. Tämä tarkoittaa olennaisesti sitä, että se palauttaisi saman viitesolun (joka on A1).
- Neljäs argumentti koskee korkeutta ja tässä LASKE -funktio palauttaa luettelon kohteiden kokonaismäärän. Se olettaa, että luettelossa ei ole tyhjiä kohtia.
- Viides argumentti on 1, mikä osoittaa, että sarakkeen leveyden on oltava yksi.
Lisämerkinnät:
- OFFSET on haihtuva toiminto (käytä varoen).
- Se laskee uudelleen aina, kun Excel -työkirja on auki tai kun laskenta käynnistyy laskentataulukossa. Tämä voi pidentää käsittelyaikaa ja hidastaa työkirjaasi.
- Jos korkeus- tai leveysarvo jätetään pois, se katsotaan viitearvoksi.
- Jos rivit ja cols ovat negatiivisia lukuja, niin siirtymän suunta käännetään.
Excel OFFSET -toimintovaihtoehdot
Joidenkin Excel OFFSET -toiminnon rajoitusten vuoksi monet haluavat harkita vaihtoehtoja sille:
- INDEX -toiminto: INDEX -toimintoa voidaan käyttää myös soluviittauksen palauttamiseen. Napsauta tätä nähdäksesi esimerkin siitä, kuinka luoda dynaaminen nimetty alue INDEX -funktion avulla.
- Excel -taulukko: Jos käytät strukturoituja viittauksia Excel -taulukossa, sinun ei tarvitse huolehtia uusien tietojen lisäämisestä ja kaavojen säätämisestä.
Excel OFFSET -toiminto - Video -opetusohjelma
- Excel VLOOKUP -toiminto.
- Excel HLOOKUP -toiminto.
- Excel INDEX -toiminto.
- Excel Epäsuora toiminto.
- Excel MATCH -toiminto.
