Excel OFFSET -toiminto

Excel OFFSET -toiminto palauttaa viittauksen alueeseen, jossa on viisi tuloa: (1) lähtökohta, (2) rivin siirtymä, (3) sarakkeen siirtymä, (4) korkeus riveillä, (5) leveys sarakkeet. OFFSET on kätevä kaavoissa, jotka edellyttävät dynaamista aluetta.

• viite - Lähtökohta, joka toimitetaan soluviitteenä tai -alueena.
• rivit - Siirrettävien rivien määrä aloitusviitteen alapuolella.
• cols - Sarakkeiden määrä, jotka on siirrettävä aloitusviitteen oikealle puolelle.
• korkeus - [valinnainen] Palautetun viitteen korkeus riveillä.
• leveys - [valinnainen] Palautetun viitteen leveys sarakkeissa.

Excel OFFSET -toiminto palauttaa dynaamisen alueen, joka koostuu viidestä tulosta: (1) lähtökohta, (2) rivin siirtymä, (3) sarakkeen siirtymä, (4) korkeus riveillä, (5) leveys sarakkeissa.

Lähtökohta ( viite argumentti) voi olla yksi solu tai solualue. The rivit ja cols argumentit ovat solujen lukumäärää, jotka on 'siirrettävä' lähtökohdasta. The korkeus ja leveys argumentit ovat valinnaisia ​​ja määrittävät luotavan alueen koon. Kun korkeus ja leveys jätetään pois, niiden oletusarvo on korkeus ja leveys viite .

kaava merkkien laskemiseksi Excelissä

Esimerkiksi viite C5 alkaen A1, viite on A1, rivit on 4 ja cols on 2:

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

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

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

Huomautus: leveys voidaan jättää pois, koska sen oletusarvo on 1.

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

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

OFFSETin päätarkoitus on antaa kaavojen mukautua dynaamisesti käytettävissä oleviin tietoihin tai käyttäjän syöttämiin tietoihin. OFFSET -toimintoa voidaan käyttää a dynaaminen nimetty alue kaavioille tai pivot -taulukoille, jotta lähdetiedot ovat aina ajan tasalla.

Huomautus: Excel -dokumentaatiossa todetaan korkeus ja leveys ei voi olla negatiivinen, mutta negatiiviset arvot näyttävät toimivan hyvin 1990 -luvun alusta lähtien . Google Sheetsin OFFSET -toiminto ei salli negatiivista arvoa 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 , joten OFFSET palauttaa a dynaaminen ryhmä kun tulos on useampi kuin yksi solu. Vanhemmissa Excel -versioissa voit käyttää F9 -näppäin tarkistaa OFFSET -toiminnosta palautetut tulokset.

Esimerkki #1

Alla olevassa näytössä käytämme OFFSETia palauttaaksesi kolmannen arvon (maaliskuu) toisessa sarakkeessa (länsi). H4: n kaava on:

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

Esimerkki #2

Alla olevassa näytössä käytämme OFFSET -toimintoa kolmannen sarakkeen (pohjoinen) viimeisen arvon (kesäkuu) palauttamiseen. H4: n kaava on:

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

Esimerkki #3

Alla palautamme kaikki kolmannen sarakkeen (pohjoinen) arvot OFFSET. H4: n kaava on:

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

Esimerkki #4

Alla käytämme OFFSETia palauttaaksesi kaikki toukokuun arvot (viides rivi). H4: n kaava on:

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

Esimerkki #5

Alla käytämme OFFSETia palauttaaksesi länsi -alueen huhti-, toukokuu- ja kesäkuun arvon. H4: n kaava on:

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

Esimerkki #6

Alla käytämme OFFSETia palauttaaksesi huhtikuun, toukokuun ja kesäkuun arvon länteen ja pohjoiseen. H4: n kaava on:

kuinka määritetään nimetty alue Excelissä

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

Huomautuksia

• OFFSET palauttaa vain viitteen, soluja ei siirretä.
• Molemmat rivit ja cols voidaan toimittaa negatiivisina numeroina kääntääkseen normaalin siirtymissuuntansa - negatiivinen cols siirtynyt vasemmalle ja negatiivinen rivit offset yllä.
• OFFSET on ' haihtuva toiminto ' - se laskee uudelleen jokaisen laskentataulukon muutoksen yhteydessä. Haihtuvat toiminnot voivat saada suurempia ja monimutkaisempia työkirjoja 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.
• OFFSETia voidaan käyttää minkä tahansa muun toiminnon kanssa, joka odottaa saavansa viitteen.
• Excel -dokumentaatio sanoo korkeus ja leveys Ne eivät voi olla negatiivisia, mutta negatiiviset arvot toimivat.