U ovom ćemo članku naučiti Kako koristiti funkciju OFFSET u Excelu.
Što je OFFSET funkcija?
Offset funkcija je funkcija dohvaćanja informacija. Ako imate ogromnu tablicu koja sadrži određene podatke i želite iz nje dohvatiti podatke, najbolji način da to učinite je da umetnete formulu pomaka za tablicu na tom radnom listu ili drugom listu u istoj radnoj knjizi. Stoga, nakon što ste stvorili tablicu za pretraživanje pomaka, jednostavno morate unijeti podatke na primjer 'mjesec' i iz te tablice dobit ćete 'prodaju' ili 'prihod' tog mjeseca.
Prolazak kroz tablicu je još jedna mogućnost, no biste li razmotrili tu opciju ako imate listove i listove podataka koji sadrže tablicu podataka s 1000 stupaca? Ovdje se pomak kombinira s drugim funkcijama.
Koncept zaokretne tablice dolazi od pomaka. Zaokretna tablica je opća tablica i iz te se posebne kombinacije podataka i grafikona dobiva prema potrebi. Također se dinamičke tablice mogu stvoriti na isti način. Za ove tablice morat ćete stvoriti excel formulu pomaka
Sintaksa OFFSET -a
| = OFFSET (referenca, redovi, stupci, [visina], [širina]) |
Redovi - tražite od Excela da pomakne broj redaka da biste dobili podatke. U tom slučaju mora se pomicati po cijelom stolu i tako jednostavno ostaviti prazno mjesto označeno zarezom (,). U protivnom, za pomicanje stupca naprijed dajte vrijednost kao 1, a za pomicanje stupca prije dajte vrijednost -1.
Stupci - ovo služi za povećanje funkcije broja stupaca. Sustav vrijednosti isti je kao u retku.
Visina - visina stola, u ovom slučaju A11.
Širina - širina stola, u ovom slučaju D11.
Nakon što postavite funkciju pomaka, pokretanje probnog postupka važno je kako biste uklonili sve pogreške.
Uzmimo tablicu koja sadrži ime, email adresu, datum rođenja i dob. Tablica počinje u ćeliji A1 koja sadrži naslov "Ime". Podaci se izvode za recimo A11. Redovi traju do D1. Cijela tablica radi A1: D11. Želite sustav za dohvaćanje koji vam daje ime kada unesete id e -pošte ili id e -pošte kada unesete ime zajedno s ostalim pojedinostima. Formulu stvarate zadovoljavajući 5 argumenata.
Referentna točka je ćelija iz koje bi pretraživanje trebalo početi. U ovom slučaju to bi trebao biti A2. To je opći standard.
Primjer:
Na primjer, dajete referencu B4 i pomak retka 0, a stupac pomaka 1 tada će se vratiti vrijednost iz C4. Zbunjujuće? Dajmo još nekoliko primjera. Posljednji primjer OFFSET funkcije govori kako dinamički ZBIRATI.
OFFSET funkcija za dobivanje vrijednosti s desne i lijeve strane ćelije
Imamo ovaj stol.
Sada, ako želim dobiti vrijednost od 2 ćelije desno do B2 (što znači D2). Napisat ću ovu OFFSET formulu:
| = OFFSET (B2,0,2) |
OFFSET funkcija pomaknut će 2 ćelije desno od ćelije B4 i vratiti njezinu vrijednost. Pogledajte sliku ispod
Ako želim dobiti vrijednost od 1 ćelije lijevo do B2 (što znači A2). Napisat ću ovu OFFSET formulu:
| = OFFSET (B2,0, -1) |
Znak minus (-) označava pomak za pomicanje unatrag.
OFFSET funkcija za dobivanje vrijednosti odozdo i iznad ćelije
Dakle, opet u gornjoj tablici, ako želim dobiti vrijednost od 2 ćelijskog ruba do B3 (što znači B5). Napisat ću ovu OFFSET formulu:
| = OFFSET (B2,2,0) |
Ako želim dobiti vrijednost od 1 ćelije lijevo do B2 (što znači A2). Napisat ću ovu OFFSET formulu:
| = OFFSET (B2, -2,0) |
- Profesionalni savjet: Tretirajte OFFSET kao pokazivač grafikona gdje je Referenca ishodište, a red i stupac osi x i y.
Funkcija OFFSET za dinamički zbroj raspona
Pogledajmo ovaj primjer.
U gornjoj tablici Ukupan red na kraju tablice. S vremenom ćete u ovu tablicu dodavati retke. Zatim ćete morati promijeniti raspon zbroja u formuli. Ovdje možemo uzeti pomoć funkcije OFFSET za dinamičko zbrajanje. Trenutno u ćeliji C11 imamo = ZBIR (C2: C10). Zamijenite ovu formulom ispod.
| = ZBIR (C2: OFFSET (C11, -1,0)) |
Ova formula samo uzima prvi redak podataka, a zatim zbraja raspon iznad ukupnog retka.
Odmak za dobivanje niza
OFFSET funkcija ima dva izborna argumenta, [height] i [width]. Nemojte ih uzimati zdravo za gotovo. Kad je funkciji OFFSET naveden argument [visina] i [širina], vraća dvodimenzionalni niz. Ako se samo jedan od njih proslijedi kao argument, vraća se na dimenzionalni niz vrijednosti.
Ako ovu formulu upišete u bilo koju ćeliju:
| = ZBIR (ODMJER (C1,1,0,9,3)) |
Zbrajat će vrijednosti u rasponu počevši od C2 do 9 redaka ispod i 3 stupca desno.
ZAMJENA (C1,1,0,9,3): To će značiti {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}.
Dakle, u zaključku, offset je vrlo korisna funkcija programa Excel koja vam može pomoći riješiti mnoge zadavljene niti. Javite mi kako koristite OFFSET funkciju u formuli i gdje vam je to najviše pomoglo.
Funkcija traženja ista je kao i pomak, ali koristi funkcije kao što su Match, Counta i IF za rad s dinamičkom tablicom.
Korištenje pomaka u Excelu zahtijeva vještinu i dobro poznavanje Excelovih funkcija i njihovog zajedničkog rada. Povlačenje bez greške najveća je prepreka.
Ovdje su sve napomene o primjeni formule.
Bilješke:
- Ova formula radi s tekstom i brojevima.
- Postoji pet argumenata pomaka koje treba zadovoljiti da bi se dobile informacije bez pogrešaka. Ako napišete netočnu formulu, dolazi do greške Ref.
Nadam se da ste razumjeli što je Excel Offset funkcija i kako je koristiti u Excelu. Ovdje istražite više članaka o pretraživanju programa Excel i usporedite vrijednosti pomoću formula. Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte.
VLOOKUP funkcija za izračun ocjene u Excelu : Za izračun ocjena IF i IFS nisu jedine funkcije koje možete koristiti. VLOOKUP je učinkovitiji i dinamičniji za takve uvjetne izračune. Za izračun ocjena pomoću VLOOKUP -a možemo koristiti ovu formulu.
17 stvari o Excelu VLOOKUP : VLOOKUP se najčešće koristi za dohvaćanje usklađenih vrijednosti, ali VLOOKUP može učiniti puno više od ovoga. Evo 17 stvari o VLOOKUP -u koje biste trebali znati koristiti učinkovito.
POGLEDAJTE prvi tekst sa popisa u Excelu : Funkcija VLOOKUP dobro radi s zamjenskim znakovima. To možemo koristiti za izdvajanje prve tekstualne vrijednosti s danog popisa u Excelu. Evo opće formule.
LOOKUP datum s posljednjom vrijednošću na popisu : Za dohvaćanje datuma koji sadrži zadnju vrijednost koristimo funkciju LOOKUP. Ova funkcija provjerava ima li ćelija koja sadrži posljednju vrijednost u vektoru, a zatim koristi tu referencu za vraćanje datuma.
Kako dohvatiti najnoviju cijenu u Excelu : Uobičajeno je ažurirati cijene u bilo kojem poslu, a korištenje najnovijih cijena za bilo koju kupnju ili prodaju je neophodno. Za dohvaćanje najnovije cijene s popisa u Excelu koristimo funkciju LOOKUP. Funkcija LOOKUP postiže najnoviju cijenu.
Popularni članci:
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti IF funkciju u Excelu : IF izraz u Excelu provjerava uvjet i vraća određenu vrijednost ako je uvjet TRUE ili vraća drugu određenu vrijednost ako je FALSE.
Kako koristiti funkciju VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.
