Excel

Excel OFFSET -toiminto

Excel Offset Function

Excel OFFSET -toimintoYhteenveto

Excel OFFSET -toiminto palauttaa viittauksen alueeseen, 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]) Argumentit
  • viite - Lähtöpiste, toimitetaan soluviitteenä tai -alueena.
  • riviä - Aloitusviitteen alapuolella siirrettävien rivien lukumäärä.
  • cols - Aloitusviitteen oikealla puolella siirrettävien sarakkeiden määrä.
  • korkeus - [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) rivinsiirto, (3) sarakeesiirto, (4) korkeus riveissä, (5) leveys sarakkeissa.



Lähtökohta ( viite argumentti) voi olla yksi solu tai solualue. riviä ja cols argumentit ovat solujen lukumäärä, joka on siirrettävä lähtökohdasta. korkeus ja leveys argumentit ovat valinnaisia ​​ja määrittävät luodun alueen koon. Kun korkeus ja leveys jätetään pois, ne oletusarvoisesti ovat korkeuden ja leveyden viite .



kaava merkkien laskemiseksi Excelissä

Esimerkiksi viitteeseen C5 alkaen A1: stä, viite on A1, riviä on 4 ja cols on 2:

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

Viitteeseen C1: C5 A1: stä, viite on A1, riviä on 0, cols on 2, korkeus on 5 ja leveys on 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ää a dynaaminen nimetty alue kaavioille tai pivot-taulukoille, jotta lähdetiedot ovat aina ajan tasalla.



Huomautus: Excel-dokumentaation tilat korkeus ja leveys ei voi olla negatiivinen, 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.

miten päästä eroon ylimääräisistä riveistä Excelissä

Esimerkkejä

Alla olevat esimerkit osoittavat, kuinka OFFSET voidaan määrittää palauttamaan erilaisia ​​alueita. Nämä näytöt otettiin Excel 365 , niin OFFSET palauttaa a dynaaminen taulukko kun tulos on useampi kuin yksi solu. Vanhemmissa Excel-versioissa voit käyttää F9-näppäin tarkistaaksesi OFFSETiltä 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

OFFSET-toimintoesimerkki 1

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

OFFSET-toimintoesimerkki 2

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

OFFSET-toimintoesimerkki 3

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

OFFSET-toimintoesimerkki 4

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

OFFSET-toimintoesimerkki 5

Esimerkki 6

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

kuinka määritetään nimetty alue Excelissä
 
= OFFSET (B3,4,2,3,2) // returns D7:E9

OFFSET-toimintoesimerkki 6

Huomautuksia

  • OFFSET palauttaa vain viitteen, soluja ei siirretä.
  • Molemmat riviä ja cols voidaan toimittaa negatiivisina numeroina kääntääkseen normaalin siirtosuunnan - negatiivinen cols vasemmalla ja negatiivinen riviä offset edellä.
  • OFFSET on ' haihtuva toiminto '- se laskee uudelleen jokaisen laskentataulukon muutoksen kanssa. 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, korkeus ja leveys viite käytetään.
  • OFFSET-toimintoa voidaan käyttää minkä tahansa muun toiminnon kanssa, joka odottaa saavansa viitteen.
  • Excel-dokumentaatiossa sanotaan korkeus ja leveys ei voi olla negatiivinen, mutta negatiiviset arvot toimivat.


^