Kuinka käyttää Excel OFFSET -toimintoa -

Sisällysluettelo

Yhteenveto

Excel OFFSET -toiminto palauttaa viitteen alueelle, joka on muodostettu viidellä tulolla: (1) aloituspiste, (2) rivinsiirto, (3) sarakeesiirto, (4) korkeus riveissä, (5) leveys in sarakkeita. OFFSET on kätevä kaavoissa, jotka edellyttävät dynaamista aluetta.

Tarkoitus

Luo viitepoikkeama annetusta lähtöpisteestä

Palautusarvo

Soluviite.

Syntaksi

= OFFSET (viite, rivit, sarakkeet, (korkeus), (leveys))

Väitteet

  • referenssi - Lähtöpiste, toimitetaan soluviitteenä tai -alueena.
  • rivit - Aloitusviitteen alapuolella siirrettävien rivien määrä.
  • sarakkeet - Aloitusviitteen oikealla puolella siirrettävien sarakkeiden määrä.
  • height - (valinnainen) Palautetun viitteen korkeus riveissä.
  • leveys - (valinnainen) Palautetun viitteen leveys sarakkeissa.

Versio

Excel 2003

Käyttöohjeet

Excel OFFSET -toiminto palauttaa dynaamisen alueen, joka on muodostettu viidestä tulosta: (1) aloituspiste, (2) rivinsiirtymä, (3) sarakepoikkeama, (4) korkeus riveissä, (5) leveys sarakkeissa.

Aloituskohdan (jäljempänä viite argumentti) voi olla yhden solun tai solualueen. Rivit ja sarakkeet perustelut ovat solujen määrä "offset" lähtöpisteestä. Korkeus ja leveys argumentit ovat valinnaisia ja määrittää koko alue, joka on luotu. Kun korkeus ja leveys jätetään pois, niissä käytetään oletusarvoisesti viitteen korkeutta ja leveyttä .

Esimerkiksi viitteestä C5 alkaen A1: stä viite on A1, rivit 4 ja sarakkeet 2:

=OFFSET(A1,4,2) // returns reference to C5

Viitteeseen C1: C5 A1: stä, viite on A1, rivit 0, sarakkeet 2, korkeus 5 ja leveys 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Huomaa: leveys voidaan jättää pois, koska se on oletusarvoisesti 1.

On tavallista nähdä OFFSET kääritty toiseen toimintoon, joka odottaa aluetta. Esimerkiksi summaan SUM C1: C5 alkaen A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

OFFSETin päätarkoitus on antaa kaavojen sopeutua dynaamisesti käytettävissä oleviin tietoihin tai käyttäjän syötteisiin. OFFSET-toimintoa voidaan käyttää rakentamaan dynaaminen nimetty alue kaavioille tai pivot-taulukoille varmistaakseen, että lähdetiedot ovat aina ajan tasalla.

Huomaa: Excel-dokumentaation mukaan korkeus ja leveys eivät voi olla negatiivisia, mutta negatiiviset arvot näyttävät toimineen hyvin 1990-luvun alusta lähtien. Google Sheetsin OFFSET-toiminto ei salli negatiivisia arvoja korkeus- tai leveysargumenteille.

Esimerkkejä

Alla olevat esimerkit osoittavat, kuinka OFFSET voidaan määrittää palauttamaan erilaisia ​​alueita. Nämä näytöt otettiin Excel 365: llä, joten OFFSET palauttaa dynaamisen taulukon, kun tulos on useampi kuin yksi solu. Excelin vanhemmissa versioissa voit käyttää F9-näppäintä tarkistaaksesi OFFSETista palautetut tulokset.

Esimerkki 1

Alla olevassa näytössä palautamme toisen sarakkeen (länsi) kolmannen arvon (maaliskuu) OFFSET-toiminnolla. H4: n kaava on:

=OFFSET(B3,3,2) // returns D6

Esimerkki 2

Alla olevassa näytössä palautamme viimeisen arvon (kesäkuu) kolmannessa sarakkeessa (pohjoinen) OFFSET-toiminnolla. H4: n kaava on:

=OFFSET(B3,6,3) // returns E9

Esimerkki 3

Alla käytämme OFFSET-funktiota palauttamaan kaikki kolmannen sarakkeen (pohjoinen) arvot. H4: n kaava on:

=OFFSET(B3,1,3,6) // returns E4:E9

Esimerkki 4

Alla käytämme OFFSET-funktiota palauttamaan kaikki toukokuun arvot (viides rivi). H4: n kaava on:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Esimerkki 5

Alla käytämme OFFSET-toimintoa palauttamaan länsi-alueen huhti-, touko- ja kesäkuun arvon. H4: n kaava on:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Esimerkki 6

Alla käytämme OFFSET-toimintoa palauttaaksemme länsi- ja pohjois-huhtikuun, toukokuun ja kesäkuun arvon. H4: n kaava on:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Huomautuksia

  • OFFSET palauttaa vain viitteen, soluja ei siirretä.
  • Sekä rivit että sarakkeet voidaan toimittaa negatiivisina numeroina normaalin siirtymissuunnan kääntämiseksi - negatiiviset sarakkeet siirtyvät vasemmalle ja negatiiviset rivit siirtyvät edellä.
  • OFFSET on "haihtuva toiminto" - se laskee uudelleen jokaisen laskentataulukon muutoksen yhteydessä. Haihtuvat toiminnot voivat saada suuremmat ja monimutkaisemmat työkirjat toimimaan hitaasti.
  • OFFSET näyttää #REF! virhearvo, jos siirtymä on laskentataulukon reunan ulkopuolella.
  • Kun korkeus tai leveys jätetään pois, käytetään referenssin korkeutta ja leveyttä .
  • OFFSET-toimintoa voidaan käyttää minkä tahansa muun toiminnon kanssa, joka odottaa saavansa viitteen.
  • Excel-ohjeiden mukaan korkeus ja leveys eivät voi olla negatiivisia, mutta negatiiviset arvot toimivat.

Liittyvät videot

Dynaamisen nimellisen alueen luominen OFFSET-toiminnolla Tässä videossa tarkastellaan, miten luodaan dynaaminen nimetty alue OFFSET-toiminnolla, joka on yleisin tapa luoda dynaaminen nimialue alue kaavan avulla.

Mielenkiintoisia artikkeleita...