Excel OFFSET -toiminto (sisällysluettelo)

  • OFFSET Excelissä
  • OFFSET-kaava Excelissä
  • Kuinka käyttää OFFSET-toimintoa Excelissä?

OFFSET Excelissä

OFFSET-toiminto Excelissä auttaa käyttäjää palauttamaan solun tai solualueen, jolla on määritetty numero. riviä ja sarakkeita solusta. Voimme määrittää ei. rivejä ja ei. palautettavien sarakkeiden lukumäärä.

Alla on Excel-mallin OFFSET-kaava:

OFFSET-toiminto Excelissä koostuu seuraavista argumenteista:

  • Viite: Se on argumentti, jonka perusteella haluamme perustaa offsetin. Se voi olla soluviittaus tai joukko vierekkäisiä soluja, ellei Offset palauttaa arvon #VALUE! Virhearvo.
  • Rivit: Se on ei. rivien välillä. Jos käytämme positiivista lukua, se siirtyy alla oleviin riveihin ja jos käytetään negatiivista lukua, se korvataan yllä olevat rivit.
  • Sarakkeet: Se on ei. sarakkeista korvata. Joten, se on sama käsite kuin Rivit, jos käytämme positiivista lukua, se siirtyy oikealle sarakkeille ja jos käytetään negatiivista lukua, se siirtyy vasemmanpuoleisiin sarakkeisiin.
  • Korkeus: Tämä argumentti on vapaaehtoinen. Sen pitäisi olla positiivinen luku. Se on numero, joka edustaa palautettujen viitteiden rivien lukumäärää.
  • Leveys: Tämä on myös valinnainen argumentti. Sen on oltava positiivinen luku. Se on numero, joka edustaa palautettujen viitteiden sarakkeiden lukumäärää.

Huomautuksia:

  • OFFSET-toiminto Excelissä palauttaa #REF! virhearvo, jos rivit ja sarakkeet poikkeavat viitteistä taulukon reunan yli.
  • OFFSET-toiminnossa sen oletetaan olevan sama korkeus tai leveys kuin referenssissä, jos korkeus tai leveys jätetään pois.
  • OFFSET palauttaa viitteen, se ei todellakaan siirrä mitään soluja tai solualueita tai muuta valintaa. Sitä voidaan käyttää minkä tahansa toiminnon kanssa, joka odottaa viiteargumenttia.

Kuinka käyttää OFFSET-toimintoa Excelissä?

OFFSET-toiminto Excelissä on erittäin yksinkertainen ja helppo käyttää. Antaa ymmärtää OFFSET-toiminnon toiminnan Excelissä jollain OFFSET-kaavan esimerkillä.

Voit ladata tämän OFFSET Function Excel -mallin täältä - OFFSET Function Excel Template

Esimerkki # 1

Offset-kaava palauttaa soluviittauksen määrittämämme lähtöpisteen, rivien ja sarakkeiden perusteella. Voimme nähdä sen alla olevassa esimerkissä:

= OFFSET (A1, 3, 1)

Kaava kehottaa Exceliä harkitsemaan solua A1 lähtöpisteeksi (viite), siirrä sitten 3 riviä alas (rivit) ja 1 sarake vasemmalle (sarakkeet argumentti). Tämän jälkeen OFFSET-kaava palauttaa arvon solussa B4.

Vasemmalla oleva kuva osoittaa, että toimintoreitti ja oikealla oleva kuvakaappaus osoittavat, kuinka voimme käyttää OFFSET-kaavaa reaaliaikaisesti. Ero kahden kaavan välillä on, että toinen (oikealla) sisältää soluviittauksen (D1) rivin argumentissa. Mutta koska solu D1 sisältää luvun 1 ja täsmälleen sama numero esiintyy ensimmäisen kaavan riviargumentissa, molemmat antaisi saman tuloksen - arvon B2.

Esimerkki # 2 OFFSET-toiminnon käyttö summalle

Koska tiedämme, että OFFSET-toimintoa voidaan käyttää myös muun Excel-toiminnon kanssa, tässä esimerkissä näemme SUM-toiminnon.

Oletetaan, että yllä olevassa kuvassa on selvitettävä pisteiden summa, niin voimme käyttää myös OFFSET-toimintoa.

Kaava on alla:

Joten, yllä olevassa kaavassa D5 on ensimmäinen solu, josta data alkaa, joka on viite aloitussoluksi. Sen jälkeen rivien ja sarakkeiden arvo on 0, joten voimme asettaa sen arvoksi 0, 0. Sitten on 4, mikä tarkoittaa 4 riviä referenssin alapuolella, jolle Excel tarvitsee laskea summan, ja sitten viimeinkin se on 1, se tarkoittaa, että leveys on 1 sarake.

Voimme viitata yllä olevaan kuvaan selittääksesi esimerkin.

Ja alla on tulos:

Meidän on vain painettava Enter, kun olet kirjoittanut kaavan vaadittuun soluun, ja sitten meillä on tulos.

Muistettavat tiedot OFFSET-toiminnosta Excelissä

  • OFFSET-toiminto Excelissä vain palauttaa viitteen, se ei siirrä mitään soluja tai solualueita todellisuudessa.
  • Jos OFFSET-kaava palauttaa solualueen, rivit ja sarakkeet viittaavat aina vasempaan yläosaan palautettuun alueeseen.
  • Viitteen tulisi sisältää solu tai solualue, muuten kaava palauttaa #VALUE-virheen.
  • Jos laskentataulukon reunan yläpuolella määritetyt rivit ja sarakkeet, Excelin offset-kaava palauttaa #REF! virhe.
  • OFFSET-toimintoa voidaan käyttää missä tahansa muussa Excel-funktiossa, joka hyväksyy solu- tai alueviittauksen argumentteissaan.

Esimerkiksi, jos käytämme kaavaa = OFFSET (A1, 3, 1, 1, 3) yksinään, se antaa arvon #VALUE! Virhe, koska paluualue (1 rivi, 3 saraketta) ei sovi soluun. Jos kuitenkin kootamme sen SUM-funktioon kuten alla:

= SUM (OFFSET (A1, 3, 1, 1, 3))

Siirtymäkaava palauttaa yhden rivin arvojen summan 3 sarakkeen alueella, joka on 3 riviä alapuolella ja 1 sarake solun A1 oikealla puolella, ts. Solujen B4: D4 arvojen kokonaismäärä.

Kuten näemme, korostetun Mar: n kokonaismäärä 36 + 63 + 82 on yhtä suuri kuin 181, joka tuli G2: n tuloksena summan ja offset-kaavan soveltamisen jälkeen. (keltaisella korostettu)

OFFSET-toiminnon käyttö Excelissä

Kuten olemme nähneet, mitä OFFSET-funktio tosiasiallisesti tekee yllä olevan esimerkin avulla, meillä voi olla kysymyksiä, että miksi häiritä laulaa OFFSET-kaavaa, joka on vähän monimutkainen, miksi et yksinkertaisesti käytä suoraa viittausta, kuten summa tai B4: D4?

OFFSET-toiminto Excelissä on erittäin hyvä seuraavalle:

  • Alueen saaminen aloitussolusta:

Joskus emme tiedä alueen tarkkaa tai todellista osoitetta, tiedämme vain mistä se alkaa tietystä solusta. Tässä tapauksessa voimme käyttää OFFSET-toimintoa, joka on helppo käyttää.

  • Dynaamisen alueen luominen:

Viitteet, kuten yllä oleva esimerkki B1: C4, ovat staattisia, se tarkoittaa, että ne viittaavat aina tiettyyn tai kiinteään alueeseen. Mutta joissain tapauksissa tehtäviä on helpompi suorittaa dynaamisilla alueilla. Se auttaa erityisesti, kun työskentelemme tietojen vaihtamisen kanssa. Meillä voisi olla esimerkiksi laskentataulukko, johon uusi rivi tai sarake lisätään tai lisätään joka päivä / viikko / kuukausi. Tässä tapauksessa OFFSET-toiminto auttaa.

OFFSET-toiminnon rajoitukset ja vaihtoehdot Excelissä

Koska jokaisella Excel-kaavalla on omat rajoituksensa ja vaihtoehtonsa, olemme nähneet kuinka ja milloin käyttää OFFSET-toimintoa Excelissä.

Alla on OFFSET-toiminnon kriittiset rajoitukset Excelissä:

  • Resursseja nälkäinen toiminto:

Se tarkoittaa, että aina kun lähdetiedoissa tapahtuu muutoksia, kaava lasketaan uudelleen koko joukolle, mikä pitää Excelin kiireisen pidempään. Jos taulukossa on pieni laskentataulukko, se ei vaikuta siihen niin paljon, mutta jos taulukkolaskentataulukoita on useita, Excel saattaa viedä aikaa uudelleenlaskentaan.

  • Vaikeus arvioinnissa:

Kuten tiedämme, Offset-toiminnon palauttamat referenssit ovat dynaamisia tai muuttuvia ja voivat sisältää suuren kaavan, joka voi olla hankala korjata tai muokata vaatimuksen mukaisesti.

Vaihtoehdot OFFSET-toiminnolle Excelissä:

  • INDEX-toiminto:

Excelin INDEX-toimintoa voidaan käyttää myös dynaamisen referenssialueen luomiseen. Se ei ole aivan sama kuin OFFSET. Mutta kuten OFFSET, myös INDEX-toiminto ei ole niin epävakaa, joten se ei hidasta Excel-ohjelmaa, joka on OFFSET-ohjelman rajoitus.

  • Epäsuora toiminto:

Voimme myös käyttää INDIRECT-toimintoa dynaamisen referenssialueen luomiseen monista lähteistä, kuten soluarvoista , tekstistä ja nimetyistä alueista.

Suositellut artikkelit

Tämä on opas OFFSET-ohjelmaan Excelissä. Tässä keskustellaan OFFSET-kaavasta Excelissä ja 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 -

  1. MS Excel: LOOKUP-toiminto
  2. Paras esimerkki Excel XIRR -toiminnosta
  3. Upea opas EI toiminnasta Excelissä
  4. IPMT Excelissä esimerkkien avulla

Luokka:

Excel-vinkkejä