Preuzmite primjer radne knjige
Ovaj vodič prikazuje kako se koristi Excel OFFSET funkcija u Excelu za stvaranje referentnog pomaka od početne ćelije.
OFFSET Pregled funkcije
Funkcija OFFSET Počinje s definiranom referencom ćelije i vraća referencu ćelije određeni broj redaka i stupaca pomaknutih u odnosu na izvornu referencu. Reference mogu biti jedna ćelija ili niz ćelija. Odstupanje vam također omogućuje da promijenite veličinu reference za određeni broj redaka/stupaca.
(Obratite pažnju na to kako se pojavljuju unosi formule)
Sintaksa i ulazi funkcije IFERROR:
| 1 | = OFFSET (referenca, redovi, stupci, visina, širina) |
referenca - Početna referenca ćelije od koje želite pomaknuti.
redove - Broj redaka za pomak.
cols - Broj stupaca za pomak.
visina - NEOBVEZNO: Prilagodite broj redaka u referenci.
širina - NEOBVEZNO: Prilagodite broj stupaca u referenci.
Što je funkcija OFFSET?
Funkcija OFFSET jedna je od moćnijih funkcija proračunske tablice jer može biti svestrana u onome što stvara. Korisniku daje mogućnost definiranja ćelije ili raspona u različitim položajima i veličinama.
OPREZ: OFFSET funkcija jedna je od promjenjivih funkcija. Većinu vremena dok radite u proračunskoj tablici, računalo će ponovno izračunati formulu samo ako su unosi promijenili svoje vrijednosti. Nestabilna funkcija, međutim, preračunava se svaki vrijeme kada promijenite bilo koju ćeliju. Treba biti oprezan kako ne biste uzrokovali veliko vrijeme ponovnog izračuna zbog pretjerane uporabe hlapivih funkcija ili zbog toga što mnoge ćelije ovise o rezultatu isparljive funkcije.
Osnovni primjeri redaka
Prilikom svake uporabe funkcije OFFSET morate dati početnu točku ili sidro. Pogledajmo ovu tablicu da bismo lakše razumjeli ovo:
Koristit ćemo "Bob" u ćeliji B3 kao našu sidrišnu točku. Da želimo uhvatiti vrijednost odmah ispod (Charlie), rekli bismo da želimo pomaknuti red za 1. Naša formula bi izgledala kao
| 1 | = OFFSET (B3, 1) |
Kad bismo htjeli pomak prema gore, to bi bio negativan pomak. Ovo možete zamisliti kako se broj retka smanjuje, pa moramo oduzeti. Dakle, da bismo dobili gornju vrijednost (Adam), napisali bismo
| 1 | = OFFSET (B2, -1) |
Primjeri osnovnih stupaca
Nastavljajući ideju iz prethodnog primjera, u našu ćemo tablicu dodati još jedan stupac.
Da želimo uhvatiti učitelja za Boba, mogli bismo upotrijebiti formulu
| 1 | = OFFSET (B2, 0, 1) |
U ovom smo slučaju rekli da želimo pomaknuti nula redaka (aka ostati u istom retku), ali želimo pomaknuti 1 stupac. Za stupce pozitivan broj znači pomak udesno, a negativni broj pomak ulijevo.
OFFSET i MATCH
Pretpostavimo da imate nekoliko stupaca podataka i želite dati korisniku mogućnost odabira iz kojeg će stupca dohvatiti rezultate. Možete koristiti INDEX funkciju ili OFFSET. Budući da će MATCH vratiti relativni položaj vrijednosti, morat ćemo osigurati da je točka sidra lijevo od naše prve moguće vrijednosti. Uzmite u obzir sljedeći izgled:
U B2 napisat ćemo ovu formulu:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH će izgledati "Feb" u rasponu C1: F1 i naći će se u 2nd ćelije. OFFSET će tada pomaknuti 1 stupac desno od B2 i zgrabiti željenu vrijednost 9. Imajte na umu da OFFSET nema problema koristiti istu ćeliju koja sadrži formulu kao točku sidrenja.
NAPOMENA: Ova tehnika se može koristiti kao zamjena za VLOOKUP ili HLOOKUP kada želite vratiti vrijednost s lijeve strane/iznad raspona pretraživanja. To je zato što OFFSET može napraviti negativne pomake.
OFFSET da biste dobili raspon
Možete koristiti 4th i 5th argumenti u funkciji OFFSET za vraćanje raspona, a ne samo jedne ćelije. Pretpostavimo da želite zbrojiti 3 stupca u ovoj tablici.
| 1 | = PROSJEČNO (ODMJER (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
U F2 odabrali smo ime učenika za koje želimo dohvatiti njihove prosječne rezultate testa. Da bismo to učinili, upotrijebit ćemo formulu
| 1 | = PROSJEČNO (ODMJER (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH će tražiti naše ime kroz stupac A i vratiti relativni položaj, što je 3 u našem primjeru. Pogledajmo kako će se to ocijeniti. Prvo će krenuti OFFSET dolje 3 reda od A1 i 1 stupac do pravo od A1. To nas stavlja u ćeliju B3.
| 1 | = PROSJEČNO (ODMJER (A1, 3, 1, 1, 3)) |
Zatim ćemo promijeniti raspon. Novi raspon imat će B3 kao gornju lijevu ćeliju. Bit će visok 1 redak i 3 stupca visok, što nam daje raspon B4: D4.
| 1 | = PROSJEČNO (ODMJER (A1,3, 1, 1, 3)) |
Imajte na umu da iako u legitimne argumente možete legitimno staviti negativne vrijednosti, u argumentima veličine možete koristiti samo negativne vrijednosti.
Na kraju, naša funkcija PROSJEČNO vidi:
| 1 | = PROSJEČNO (B4: D4) |
Tako dobivamo rješenje 86,67
OFFSET s dinamičkim ZBOROM
Budući da se OFFSET koristi za pronalaženje reference, umjesto za izravno usmjeravanje na ćeliju, najkorisnije je kada se bavite podacima koji imaju dodane ili izbrisane retke. Razmotrite sljedeću tablicu s ukupnim zbrojem na dnu
| 1 | = ZBIR (B2: B4) |
Da smo ovdje upotrijebili osnovnu formulu SUM "= SUM (B2: B4)" i zatim umetnuli novi redak za dodavanje zapisa za Bill, imali bismo pogrešan odgovor
Umjesto toga, razmislimo kako to riješiti s gledišta Totala. Zaista želimo zgrabiti sve od ćelije B2 do ćelije malo iznad našeg ukupnog iznosa. Način na koji to možemo zapisati u formulu je napraviti pomak retka od -1. Stoga ovo koristimo kao formulu za naš ukupni iznos u ćeliji B5:
| 1 | = ZBIR (B2: OFFSET (B5, -1,0)) |
Ova formula radi ono što smo upravo opisali: počnite od B2 i idite do 1 ćelije iznad naše ukupne ćelije. Možete vidjeti kako se nakon dodavanja Billovih podataka naš ukupan broj ispravno ažurira.
OFFSET za dobivanje posljednjih N stavki
Recimo da bilježite mjesečnu prodaju, ali želite pogledati posljednja 3 mjeseca. Umjesto da morate ručno ažurirati formule kako biste nastavili prilagođavati kako se dodaju novi podaci, možete koristiti funkciju OFFSET s COUNT.
Već smo pokazali kako možete koristiti OFFSET za hvatanje niza ćelija. Da bismo odredili koliko ćelija moramo premjestiti, upotrijebit ćemo COUNT da pronađemo koliko brojevima nalaze se u stupcu B. Pogledajmo našu tablicu uzoraka.
| 1 | = ZBIR (POMOĆ ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Da smo počeli od B1 i pomaknuli 4 retka (broj brojeva u stupcu B), završili bismo na dnu našeg raspona, B5. No, budući da se OFFSET -u ne može promijeniti veličina s negativnom vrijednošću, moramo napraviti neke prilagodbe kako bismo završili u B3. Opća jednadžba za to će biti točna
| 1 | COUNT (…) - N + 1 |
Uzimamo u obzir cijeli stupac, oduzimamo koliko god želimo vratiti (budući da ćemo promijeniti veličinu da bismo ih zgrabili), a zatim dodamo 1 (budući da u osnovi započinjemo pomak na nuli).
Ovdje možete vidjeti da smo postavili raspon za dobivanje zbroja, prosjeka i maksimuma u zadnjih N mjeseci. U E1 unijeli smo vrijednost 3. U E2, naša formula je
| 1 | = ZBIR (POMOĆ ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Istaknuti odjeljak je naša opća jednadžba o kojoj smo upravo govorili. Ne moramo pomaknuti nijedan stupac. Zatim ćemo promijeniti raspon na 3 ćelije (određene vrijednosti prema E1) i 1 stupac u širinu. Naš SUM tada uzima ovaj raspon i daje rezultat od 1.850 USD. Također smo pokazali da možete izračunati prosjek max tog istog raspona jednostavnim prebacivanjem vanjske funkcije sa SUM -a na sve što situacija zahtijeva.
OFFSET dinamički popisi provjere valjanosti
Koristeći tehniku prikazanu u posljednjem primjeru, također možemo izgraditi imenovane raspone koji se mogu koristiti u provjeri valjanosti podataka ili grafikonima. To može biti korisno ako želite postaviti proračunsku tablicu, ali očekujete da će naši popisi/podaci promijeniti veličinu. Recimo da naša trgovina počinje prodavati voće, a trenutno imamo 3 izbora.
Da bismo napravili padajući izbornik Validacija podataka koji možemo koristiti drugdje, definirat ćemo imenovani raspon MyFruit kao
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Umjesto COUNT, koristimo COUNTA jer se bavimo tekstualnim vrijednostima. Zbog toga će, međutim, naša COUNTA biti jedna veća jer će prebrojavati ćeliju zaglavlja u A1 i dati vrijednost 4. Ako ipak pomaknemo 4 retka, završili bismo u ćeliji A5 koja je prazna. Kako bismo se tome prilagodili, oduzimamo 1.
Sada kada imamo postavljeno Imenovani raspon, možemo postaviti neke provjere podataka u ćeliju C4 pomoću vrste Popis, s izvorom:
| 1 | = Moje voće |
Imajte na umu da padajući izbornik prikazuje samo naše tri trenutne stavke. Ako zatim dodamo još stavki na popis i vratimo se na padajući izbornik, popis prikazuje sve nove stavke bez da moramo mijenjati bilo koju formulu.
Oprez pri korištenju OFFSET -a
Kao što je spomenuto na početku ovog članka, OFFSET je hlapljiva funkcija. Ovo nećete primijetiti ako ga koristite u samo nekoliko ćelija, ali ako ga počnete uključivati u stotine izračuna i brzo ćete primijetiti da vaše računalo troši zamjetno vrijeme na ponovni izračun svaki put kada unesete bilo kakve promjene .
Osim toga, budući da OFFSET ne imenuje izravno ćelije koje gleda, drugi korisnici teže dolaze kasnije i mijenjaju formule ako je potrebno.
Umjesto toga, bilo bi poželjno koristiti tablice (uvedene u sustavu Office 2007) koje dopuštaju strukturne reference. To je pomoglo korisnicima da daju jednu referencu koja se automatski prilagođava veličinom pri dodavanju ili brisanju novih podataka.
Druga mogućnost korištenja umjesto OFFSET -a je moćna INDEX funkcija. INDEX vam omogućuje da izgradite sve dinamičke raspone koje smo vidjeli u ovom članku bez problema da su hlapljive funkcije.
dodatne napomene
Pomoću funkcije OFFSET vratite vrijednost ćelije (ili raspon ćelija) pomakom određenog broja redaka i stupaca od početne reference. Kada traže samo jednu ćeliju, OFFSET formule postižu istu svrhu kao INDEX formule, koristeći nešto drugačiju tehniku. Stvarna snaga OFFSET funkcije leži u njenoj sposobnosti odabira raspona ćelija koje će se koristiti u drugoj formuli.
Kada koristite funkciju OFFSET, definirate početnu početnu ćeliju ili raspon ćelija. Zatim označite broj redaka i stupaca za pomak od te početne ćelije. Također možete promijeniti veličinu raspona; dodavanje ili oduzimanje redaka ili stupaca.
Povratak na popis svih funkcija u Excelu
OFFSET u Google tablicama
Funkcija OFFSET u Google tablicama radi potpuno isto kao i u Excelu:
