V tem članku se bomo naučili, kako uporabljati funkcijo OFFSET v Excelu.
Kaj je Funkcija OFFSET?
Offset funkcija je funkcija iskanja informacij. Če imate ogromno tabelo, ki vsebuje določene podatke, in želite iz nje pridobiti podatke, je to najbolje narediti tako, da v ta delovni list ali drug list v istem delovnem zvezku vstavite formulo odmika za tabelo. Ko ustvarite tabelo za iskanje odmika, morate preprosto vnesti podatke, na primer "mesec", in iz te tabele boste dobili "prodajo" ali "dohodek" tega meseca.
Pregledovanje tabele je še ena možnost, vendar bi razmislili o možnosti, če imate liste in liste podatkov, ki vsebujejo podatkovno tabelo s 1000 stolpci? Tu se odmik kombinira z drugimi funkcijami.
Koncept vrtilne tabele prihaja iz zamika. Vrtilna tabela je splošna tabela in iz nje se po potrebi pridobi posebna kombinacija podatkov in grafov. Na enak način lahko ustvarite tudi dinamične tabele. Za te tabele boste morali ustvariti formulo excel offset
Sintaksa OFFSET
| = OFFSET (sklic, vrstice, stolpci, [višina], [širina]) |
Vrstice - od Excela zahtevate, da premakne število vrstic za pridobitev podatkov. V tem primeru se mora premikati po mizi in tako preprosto pustiti prazno, označeno z vejico (,). V nasprotnem primeru, če želite stolpec premakniti naprej, dajte vrednost 1 in stolpec prej dajte vrednost -1.
Stolpci - to je, da funkcijo usmerite na število stolpcev. Sistem vrednosti je enak kot pri vrstici.
Višina - višina mize, v tem primeru A11.
Premer - širina mize, v tem primeru D11.
Ko nastavite funkcijo odmika, je izvajanje preskusa pomembno, da odpravite vse napake.
Vzemimo tabelo, ki vsebuje ime, elektronski naslov, datum rojstva in starost. Tabela se začne pri celici A1, ki vsebuje naslov »Ime«. Podatki tečejo za recimo A11. Vrstice tečejo do D1. Celotna miza teče A1: D11. Želite sistem za pridobivanje, ki vam bo dal ime, ko vnesete e -poštni ID, ali e -poštni ID, ko vnesete ime skupaj z drugimi podrobnostmi. Formulo ustvarite tako, da izpolnite 5 argumentov.
Referenčna točka je celica, iz katere se mora začeti iskanje. V tem primeru mora biti A2. To je splošni standard.
Primer:
Na primer navedete sklic na B4 in vrstico zamika 0 ter odmik stolpca 1, potem se vrne vrednost iz C4. Zmedeno? Dajmo še nekaj primerov. Zadnji primer funkcije OFFSET pove, kako dinamično seštevati.
Funkcija OFFSET za pridobivanje vrednosti z desne in leve strani celice
Mi imamo to mizo.
Zdaj, če želim prejeti vrednost iz 2 celice desno v B2 (kar pomeni D2). Napisal bom to formulo OFFSET:
| = OFFSET (B2,0,2) |
Funkcija OFFSET premakne 2 celico desno od celice B4 in vrne njeno vrednost. Glej sliko spodaj
Če želim dobiti vrednost od 1 celice levo do B2 (kar pomeni A2). Napisal bom to formulo OFFSET:
| = OFFSET (B2,0, -1) |
Znak minus (-) označuje zamik za premik v obratni smeri.
Funkcija OFFSET za pridobivanje vrednosti od spodaj in zgoraj celice
Torej spet v zgornji tabeli, če želim dobiti vrednost iz 2 celice belove v B3 (kar pomeni B5). Napisal bom to formulo OFFSET:
| = OFFSET (B2,2,0) |
Če želim dobiti vrednost od 1 celice levo do B2 (kar pomeni A2). Napisal bom to formulo OFFSET:
| = OFFSET (B2, -2,0) |
- Nasvet za profesionalce: OFFSET obravnavajte kot kazalec grafa, kjer je referenca izvor, vrstica in stolpec pa osi x in y.
Funkcija OFFSET za dinamični seštevek
Poglejmo ta primer.
V zgornji tabeli Skupna vrstica na koncu tabele. Sčasoma boste v to tabelo dodali vrstice. Nato morate v formuli spremeniti obseg vsote. Tu lahko uporabimo funkcijo OFFSET za dinamično seštevanje. Trenutno imamo v celici C11 = SUM (C2: C10). To zamenjajte s spodnjo formulo.
| = SUM (C2: OFFSET (C11, -1,0)) |
Ta formula vzame samo prvo podatkovno vrstico in nato povzame obseg nad celotno vrstico.
Odmik za pridobivanje matrike
Funkcija OFFSET ima dva izbirna argumenta, [višina] in [širina]. Ne jemljite jih kot samoumevne. Ko so funkciji OFFSET podani argumenti [height] in [width], vrne dvodimenzionalno matriko. Če se kot argument posreduje le eden od njih, se vrne na dimenzijsko polje vrednosti.
Če v katero koli celico vnesete to formulo:
| = SUM (ZMIR (C1,1,0,9,3)) |
Sešteval bo vrednosti v razponu od C2 do 9 vrstic spodaj in 3 stolpce desno.
ZAKON (C1,1,0,9,3): To bo pomenilo {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}.
Skratka, offset je zelo uporabna funkcija excela, ki vam lahko pomaga rešiti številne zadavljene niti. Povejte mi, kako uporabljate funkcijo OFFSET v svoji formuli in kje vam je najbolj pomagala.
Funkcija iskanja je enaka odmiku, vendar uporablja funkcije, kot so Match, Counta in IF za delo z dinamično tabelo.
Uporaba offseta v Excelu zahteva spretnost in dobro poznavanje Excelovih funkcij in njihovega skupnega delovanja. Največja ovira je, da ga izvlečete brez napak.
Tu so vse opombe glede uporabe formule.
Opombe:
- Ta formula deluje tako z besedilom kot s številkami.
- Za pridobitev informacij brez napak je treba upoštevati pet argumentov odmika. Če napišete napačno formulo, pride do napake Ref.
Upam, da ste razumeli, kaj je funkcija Excel Offset in kako jo uporabljati v Excelu. Raziščite več člankov o iskanju Excela in ujemanju vrednosti s formulami tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Funkcija VLOOKUP za izračun ocene v Excelu : Za izračun ocen IF in IFS nista edini funkciji, ki jo lahko uporabite. VLOOKUP je za take pogojne izračune učinkovitejši in dinamičnejši. Za izračun ocen z uporabo VLOOKUP -a lahko uporabimo to formulo.
17 stvari o Excelu VLOOKUP : VLOOKUP se najpogosteje uporablja za pridobivanje ujemajočih se vrednosti, vendar lahko VLOOKUP naredi veliko več kot to. Tukaj je 17 stvari o VLOOKUP -u, ki jih morate vedeti za učinkovito uporabo.
POGLEDAJTE prvo besedilo s seznama v Excelu : Funkcija VLOOKUP dobro deluje z nadomestnimi znaki. To lahko uporabimo za ekstrahiranje prve besedilne vrednosti z danega seznama v Excelu. Tu je splošna formula.
LOOKUP datum z zadnjo vrednostjo na seznamu : Za pridobitev datuma, ki vsebuje zadnjo vrednost, uporabimo funkcijo LOOKUP. Ta funkcija preveri celico, ki vsebuje zadnjo vrednost v vektorju, in nato uporabi to referenco za vrnitev datuma.
Kako pridobiti zadnjo ceno v Excelu : Običajno je, da se cene posodabljajo pri katerem koli podjetju, pri nakupu ali prodaji pa je treba uporabiti najnovejše cene. Za pridobitev najnovejše cene s seznama v Excelu uporabimo funkcijo LOOKUP. Funkcija LOOKUP doseže najnovejšo ceno.
Priljubljeni članki:
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.