Excel OFFSET -kaava (sisällysluettelo)
- Mikä on OFFSET-kaava Excelissä?
- Kuinka käyttää OFFSET-kaavaa Excelissä?
Mikä on OFFSET-kaava Excelissä?
Excelissä oleva OFFSET-kaava antaa solun tai joukon dynaamisia suorakulmaisia soluja tulosteena, joka on osajoukko annetusta kokonaismäärästä, jossa on määritetty määrä rivejä, sarakkeita, korkeus ja leveys.
OFFSET-kaavan syntaksi Excelissä
Selitys OFFSET-toiminnosta Excelissä:
- viite: OFFSETin lähtökohta tai voidaan sanoa solualue, jota voidaan pitää OFFSETin perustana.
- rivit : OFFSET- rivien lukumäärä perusviitteen alapuolella tai yläpuolella.
- cols: OFFSET-sarakkeiden lukumäärä perusviitteen oikealla tai vasemmalla puolella.
- korkeus: rivien lukumäärä palautetussa vertailussa.
- leveys: Palautettujen ohjeiden sarakkeiden lukumäärä.
Korkeus ja leveys ovat tämän toiminnon valinnaiset parametrit, ja niiden avulla voidaan määrittää luodun referenssin koko.
Kuinka käyttää OFFSET-kaavaa Excelissä?
Oppimme nyt kuinka kirjoittaa offset-kaava hakuarvon saamiseksi. Ymmärretään offset-kaava joihinkin Excel-esimerkkeihin.
Voit ladata tämän OFFSET Formula Excel -mallin täältä - OFFSET Formula Excel TemplateExcel OFFSET -kaava - esimerkki # 1
Oletetaan, että meillä on alla esitetyt tiedot.
- Syötä soluun E2 seuraava kaava:
= OFFSET (A1, 11, 1)
- Kun olet tehnyt kaavan, paina Enter ja katso tulos.
Mitä tämä kaava teki, se on ottanut viittauksen solusta A1 (vuosi) ja siirtynyt 11 rivillä alaspäin A1: n jälkeen (ts. Rivi nro 12) ja saavuttanut arvon, joka on 11. rivin koordinaatti A1: n ja ensimmäisen sarakkeen jälkeen ( sarake A = 0 sarake B = 1. Tässä tapauksessa voimme mennä sekä oikealle että vasemmalle). Mikä on 141, 27 miljoonaa dollaria.
Excel OFFSET -kaava - esimerkki 2
Nyt näemme toisen esimerkin viitekennosta tietylle vuodelle. Lisää seuraava kaava soluun E3:
= OFFSET (A1, 11, 0)
- Kun olet tehnyt kaavan, paina Enter ja katso tulos.
Jos olet nähnyt kaavan, se on sama kuin edellinen, ainoa muutos on sarakeviitteessä. Sarakkeen viite 0 (nolla) tarkoittaa, että arvo otetaan talteen sarakkeesta A 11. rivillä A1: n (joka on 2010) jälkeen.
Excel OFFSET -kaava - esimerkki 3
Tässä esimerkissä lasketaan viimeisten N rivien summa käyttämällä Excel OFFSET -toimintoa. Yleinen kaava viimeisten N-lukujen summaamiseksi on:
= SUM (OFFSET (A1, COUNT (A: A), 0, -N))
Tiivistääksesi viimeiset N havaintoa tiedostasi, voit käyttää SUM-toimintoa yhdessä OFFSET- ja COUNT-toimintojen kanssa. Tässä esimerkissä summataan viimeiset 5 datapistettä. Huomaa, että kun sanomme N, se on yleistä kokonaislukuarvoista. Joten, voit laskea viimeisen 3, viimeisen 4, viimeisen 5, jne., Riippuen aineistostasi ja vaatimuksistasi.
Tehdään se tekemään tietojoukollemme:
- Syötä seuraava kaava Excel-tiedoston soluun E2.
= SUM (OFFSET (B1, COUNT (B: B), 0, -5))
- Napsauta Enter nähdäksesi tulosteen.
Kuten olemme jo nähneet, Excel OFFSET -toiminto tuottaa suorakulmaiset dynaamiset alueet, kun niille annetaan aloitusohje.
- Tässä tapauksessa lähtöviite annetaan B1: n muodossa (ensimmäisenä argumenttina OFFSET-toiminnolle).
- Käytimme COUNT-toimintoa, koska tarvitsemme havaintojen summan viimeisestä arvosta. COUNT-toiminto auttaa OFFSET-järjestelmää määrittämään kuinka monta havaintoa (riviä) on sarakkeessa B. Se laskee kaikki sarakkeen B numeeriset arvot (19 on luku).
- Kun luku on 19, OFFSET-kaavasta tulee:
= OFFSET (B1, 19, 0, -5)
- Tämä kaava alkaa siirtymän kohdalla B1 19 rivillä ja samalla sarakkeella (0). Sitten korkeusparametri -5 auttaa tätä toimintoa laajentamaan aluetta taaksepäin 5 rivillä (katso kohtaan 5 liittyvä negatiivinen merkki).
- Viimeisten viiden havainnon dynaaminen alue annetaan syötteenä summafunktioon. Mikä saanto lasketaan yhteen sarakkeesta B
Excel OFFSET -kaava - esimerkki 4
Tässä esimerkissä lasketaan viimeisen 3 vuoden mediaanimyynti. OFFSET-toimintoa voidaan käyttää perusteena laskea sama.
- Lisää seuraava kaava soluun E2:
= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))
- Paina Enter nähdäksesi tulosteen.
Tämä kaava toimii samanlaisilla riveillä kuin edellinen.
- OFFSET Luo kolmen viimeisen havainnon dynaamisen alueen sarakkeesta B COUNT-toiminnon avulla (= OFFSET (A1, 19, 0, -3)).
- Tämä voidaan antaa lähtökohtana MEDIAN-toiminnolle. Mikä sitten lajittelee havainnot joko nousevassa tai laskevassa järjestyksessä ja valitsee sitten keskimmäisen arvon, kuten näet tuotoksessa (212, 07 dollaria).
Excel OFFSET -kaava - esimerkki 5
Tässä lasketaan mediaanivuosi käyttämällä mediaania ja OFFSET-toimintoa.
- Käytä samaa kaavaa samaan soluun E3:
= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))
- Napsauta Enter nähdäksesi tulosteen.
Sama asia on tapahtunut täällä. Kaava otti käyttöön OFFSET-toiminnon luoman dynaamisen alueen COUNT: n avulla. Ja käytti sitä perusteena MEDIAN-funktiolle, joka sitten lajitti vuoden arvot nousevassa tai laskevassa järjestyksessä ja antoi sitten arvon 2017.
OFFSET-kaavaa käytetään dynaamisten alueiden luomiseen. Aina on parempi työskennellä dynaamisilla alueilla staattisten sijasta. Kuten sinulla on tietoja, joita lisätään aina silloin tällöin. Esimerkiksi työntekijämestari. Työntekijöiden perustietoja kasvatetaan aina silloin tällöin, koska uusien työntekijöiden on oltava liittymässä. Joka kerta saman kaavan käyttäminen ei ole mitään järkeä. Luo sen sijaan dynaaminen alue, joka käyttää OFFSET-toimintoa, ja näe taikuus. Se säästää varmasti aikaa.
Muistettavat asiat
- OFFSET-toiminto palauttaa viitteen vain tietylle Excel-solualueelle. Se ei siirrä soluja alkuperäisestä sijainnistaan.
- Oletuksena tämä toiminto toimii yläosassa (riveinä) ja oikealla (sarakkeina). Sille voidaan kuitenkin antaa negatiivisia argumentteja oletustoimintojen kääntämiseksi. Kuten -negatiivinen riviargumentti auttaa siirtämään tiedot alaspäin ylöspäin ja negatiivinen kol-argumentti auttaa siirtämään tiedot vasemmalle.
- OFFSET-kaava voi hidastaa Excel-toimintoa, koska se on kaava, joka lasketaan uudelleen aina, kun laskentataulukossa tapahtuu muutoksia.
- #REF-virhe heitetään, jos alueesi on työkirjan rajojen ulkopuolella.
- Mikä tahansa muu referenssiä odottava toiminto voi käyttää OFFSET-toimintoa siinä.
Suositellut artikkelit
Tämä on opas OFFSET-kaavaan Excelissä. Tässä keskustellaan siitä, kuinka OFFSET-toimintoa käytetään Excelissä, sekä käytännön esimerkkejä ja ladattavaa Excel-mallia. Voit myös käydä läpi muiden ehdottamiemme artikkeleidemme -
- Kuinka kääri tekstiä Excelissä?
- Yksinkertainen opas lajitteluun Excelissä
- LOOKUP-toiminto Excelissä
- Kuinka käyttää FIND-toimintoa Excelissä?