Tässä artikkelissa opimme käyttämään OFFSET -toimintoa Excelissä.
Mikä on OFFSET -toiminto?
Siirtymätoiminto on tiedonhakutoiminto. Jos sinulla on valtava taulukko, joka sisältää tiettyjä tietoja ja haluat hakea tietoja siitä, paras tapa tehdä se on lisätä taulukon siirtymäkaava kyseiseen laskentataulukkoon tai toiseen saman työkirjan taulukkoon. Siten kun olet luonut offset -noutotaulukon, sinun on vain syötettävä tiedot, esimerkiksi "kuukausi", ja saat kyseisen kuukauden "myynnin" tai "tulon" kyseisestä taulukosta.
Taulukon selaaminen on toinen vaihtoehto, mutta harkitsisitko vaihtoehtoa, jos sinulla on taulukkoja ja tietolomakkeita, jotka kaikki sisältävät 1000 sarakkeen tietotaulukon? Tässä offset yhdistetään muihin toimintoihin.
Pivot -taulukon käsite tulee offsetista. Pivot -taulukko on yleinen taulukko, josta saadaan erityinen tietojen ja kaavioiden yhdistelmä tarpeen mukaan. Myös dynaamisia taulukoita voidaan luoda samalla tavalla. Näitä taulukoita varten sinun on luotava Excel -offset -kaava
OFFSET -syntaksi
| = OFFSET (viite, rivit, sarakkeet, [korkeus], [leveys]) |
Rivit - pyydät exceliä siirtämään rivien lukumäärän saadaksesi tietoja. Tässä tapauksessa sen on liikuttava ympäri pöytää ja jätettävä siten tyhjäksi pilkulla merkitty (,). Muussa tapauksessa, jos haluat siirtää saraketta eteenpäin, anna arvo 1 ja siirrä sarake ennen arvoa -1.
Sarakkeet - tämä toiminto ohjaa funktion sarakkeiden lukumäärään. Arvojärjestelmä on sama kuin rivillä.
Korkeus - pöydän korkeus, tässä tapauksessa A11.
Leveys - taulukon leveys, tässä tapauksessa D11.
Kun olet määrittänyt offset -toiminnon, kokeilun suorittaminen on tärkeää virheiden poistamiseksi.
Otetaan taulukko, joka sisältää nimen, sähköpostiosoitteen, syntymäajan ja iän. Taulukko alkaa A1 -solusta, joka sisältää otsikon "Nimi". Tiedot toimivat esimerkiksi A11: lle. Ja rivit kulkevat D1: een asti. Koko taulukko kulkee A1: D11. Haluat hakujärjestelmän, joka antaa sinulle nimen, kun kirjoitat sähköpostitunnuksen, tai sähköpostiosoitteen, kun kirjoitat nimen yhdessä muiden tietojen kanssa. Luot kaavan täyttämällä 5 argumenttia.
Vertailupiste on solu, josta etsinnän pitäisi alkaa. Tässä tapauksessa sen pitäisi olla A2. Se on yleinen standardi.
Esimerkki:
Jos esimerkiksi annat viitteen B4: lle ja offset -riville 0, ja offset -sarakkeelle 1, C4: n arvo palautetaan. Hämmentävä? Annetaan lisää esimerkkejä. Viimeinen esimerkki OFFSET -toiminnosta kertoo kuinka summaa dynaamisesti.
OFFSET -toiminto arvon saamiseksi solun oikealta ja vasemmalta puolelta
Meillä on tämä pöytä.
Nyt jos haluan saada arvon 2 solusta oikealle B2: lle (mikä tarkoittaa D2). Kirjoitan tämän OFFSET -kaavan:
| = SIIRTO (B2,0,2) |
OFFSET -toiminto siirtää 2 solua solun B4 oikealle puolelle ja palauttaa arvon. Katso kuva alla
Jos haluan saada arvon 1 solusta vasemmalle kohtaan B2 (mikä tarkoittaa A2). Kirjoitan tämän OFFSET -kaavan:
| = SIIRTO (B2,0, -1) |
Miinus (-) -merkki osoittaa siirtymän käänteiseksi.
OFFSET -toiminto, jolla saat arvon solun alapuolelta ja yläpuolelta
Joten jälleen yllä olevassa taulukossa, jos haluan saada arvon 2 solun belovesta B3: een (mikä tarkoittaa B5). Kirjoitan tämän OFFSET -kaavan:
| = SIIRTO (B2,2,0) |
Jos haluan saada arvon 1 solusta vasemmalle kohtaan B2 (mikä tarkoittaa A2). Kirjoitan tämän OFFSET -kaavan:
| = SIIRTO (B2, -2,0) |
- Pro -vinkki: Käsittele SIIRTYTÄ kuvaajaosoittimena, jossa viite on alkuperä ja rivi ja sarake ovat x- ja y -akseli.
OFFSET -toiminto summa -alueelle dynaamisesti
Katsotaanpa tämä esimerkki.
Yllä olevassa taulukossa Kokonaisrivi taulukon lopussa. Ajan mittaan lisäät tähän taulukkoon rivejä. Sitten sinun on muutettava kaavan summa -aluetta. Tässä voimme ottaa käyttöön OFFSET -toiminnon summan dynaamisesti. Tällä hetkellä solussa C11 meillä on = SUMMA (C2: C10). Korvaa tämä alla olevalla kaavalla.
| = SUMMA (C2: SIIRTO (C11, -1,0)) |
Tämä kaava ottaa vain ensimmäisen tietorivin ja summaa sitten kokonaisrivin yläpuolella olevan alueen.
Siirtymä matriisin saamiseksi
OFFSET -funktiolla on kaksi valinnaista argumenttia, [korkeus] ja [leveys]. Älä ota niitä itsestäänselvyytenä. Kun OFFSET -funktio on [korkeus] ja [leveys] -argumentit, se palauttaa kaksiulotteisen taulukon. Jos vain yksi niistä välitetään argumenttina, se palauttaa arvojen matriisimatriisin.
Jos kirjoitat tämän kaavan mihin tahansa soluun:
| = SUMMA (SIIRTO (C1,1,0,9,3)) |
Se summaa arvot alueella C2 - 9 riviä alla ja 3 saraketta oikealla.
SIIRTO (C1,1,0,9,3): Tämä tarkoittaa {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Joten lopuksi, offset on erittäin hyödyllinen Excel -toiminto, joka voi auttaa sinua ratkaisemaan monia kuristettuja säikeitä. Kerro minulle, kuinka käytät OFFSET -funktiota kaavassasi ja missä se auttoi sinua eniten.
Hakutoiminto on sama kuin offset, mutta hyödyntää dynaamisen taulukon toimintoja, kuten Match, Counta ja IF.
Offsetin käyttäminen Excelissä vaatii taitoa ja hyvää tietoa Excel -toiminnoista ja niiden toiminnasta. Sen poistaminen ilman virheitä on suurin este.
Tässä on kaikki huomautukset kaavan käytöstä.
Huomautuksia:
- Tämä kaava toimii sekä tekstin että numeroiden kanssa.
- Siirtymässä on viisi argumenttia, jotka on täytettävä virheettömän tiedon saamiseksi. Jos kirjoitat väärän kaavan, ilmenee Ref -virhe.
Toivottavasti ymmärsit mikä on Excel -offset -toiminto ja miten sitä käytetään Excelissä. Tutustu muihin artikkeleihin Excel -hausta ja vastaavat arvot käyttämällä kaavoja täällä. Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse.
VLOOKUP -toiminto laskea arvosanan Excelissä : Arvosanojen laskeminen IF ja IFS eivät ole ainoita toimintoja, joita voit käyttää. VLOOKUP on tehokkaampi ja dynaamisempi tällaisille ehdollisille laskelmille.
17 asiaa Excel VLOOKUPista : VLOOKUPia käytetään useimmiten haettujen arvojen hakemiseen, mutta VLOOKUP voi tehdä paljon enemmän kuin tämä. Tässä on 17 asiaa VLOOKUPista, jotka sinun pitäisi tietää tehokkaasti.
KATSO ensimmäinen teksti Excelin luettelosta : VLOOKUP -toiminto toimii hyvin jokerimerkillä. Voimme käyttää tätä poimiaksesi ensimmäisen tekstiarvon tietystä Excel -luettelosta. Tässä on yleinen kaava.
LOOKUP -päivämäärä, jossa on luettelon viimeinen arvo : Haemme viimeisimmän arvon sisältävän päivämäärän LOOKUP -toiminnolla. Tämä toiminto etsii solun, joka sisältää vektorin viimeisen arvon, ja käyttää sitten tätä viitettä päivämäärän palauttamiseen.
Kuinka hakea uusin hinta Excelissä : On tavallista päivittää hinnat kaikissa yrityksissä ja käyttää uusimpia hintoja kaikissa ostoissa tai myynneissä. Nouda uusin hinta Excel -luettelosta käyttämällä LOOKUP -toimintoa. LOOKUP -toiminto hakee viimeisimmän hinnan.
Suosittuja artikkeleita:
50 Excel -pikakuvake tuottavuuden lisäämiseksi : Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
IF -toiminnon käyttäminen Excelissä : Excelin IF -käsky tarkistaa ehdon ja palauttaa tietyn arvon, jos ehto on TOSI, tai palauttaa toisen arvon, jos EPÄTOSI.
VLOOKUP -toiminnon käyttäminen Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
