Excel OFFSET Formula (Sadržaj)

  • Što je OFFSET Formula u Excelu?
  • Kako koristiti OFFSET formulu u Excelu?

Što je OFFSET Formula u Excelu?

OFFSET Formula u Excelu daje ćeliju ili niz dinamičnih pravokutnih ćelija kao izlaz koji je podskup ukupnog zadanog raspona s određenim brojem redaka, stupaca, visine i širine.

Sintaksa OFFSET formule u Excelu

Objašnjenje OFFSET funkcije u Excelu:

  • referenca: Polazište za OFFSET ili možemo reći raspon ćelija koje se mogu smatrati bazom za OFFSET.
  • redaka : Broj redaka za OFFSET ispod ili iznad referentne vrijednosti.
  • cols: broj stupaca za OFFSET desno ili lijevo od osnovne baze.
  • visina: broj redova u vraćenoj referenci.
  • širina: Broj stupaca u vraćenoj referenci.

Visina i širina su opcionalni parametri za ovu funkciju i mogu se koristiti za određivanje veličine stvorene reference.

Kako koristiti OFFSET formulu u Excelu?

Sada ćemo naučiti kako napisati offset formulu kako bi dobili vrijednost pretraživanja. Razjasnimo formulu offseta s nekim primjerima u Excelu.

Ovdje možete preuzeti ovaj OFFSET predložak Formule Excel - OFFSET predložak Formule Excel

Excel OFFSET Formula - Primjer # 1

Pretpostavimo da imamo podatke kako su dolje navedeni.

  • U ćeliju E2 unesite sljedeću formulu:

= OFFSET (A1, 11, 1)

  • Nakon što završite s formulom, pritisnite Enter i pogledajte izlaz.

Ova formula je to uzela referencu iz ćelije A1 (Godina) i pomaknula se za 11 redova nakon A1 (tj. Redni broj 12), a zatim dobila vrijednost koja je koordinata 11. reda nakon A1 i prvog stupca ( stupac A = 0 u ovom slučaju stupac B = 1. Kao što znamo, možemo ići i desno i lijevo). Što je 141, 27 milijuna dolara.

Excel OFFSET Formula - Primjer # 2

Sada ćemo vidjeti još jedan primjer referentne ćelije za određenu godinu. U ćeliju E3 stavite sljedeću formulu:

= OFFSET (A1, 11, 0)

  • Nakon što završite s formulom, pritisnite Enter i pogledajte izlaz.

Ako ste vidjeli formulu, ista je kao i u prethodnoj, jedina promjena je u referenci stupca. Referenca stupca 0 (nula) znači da će vrijednost biti zabilježena iz stupca A za 11. red nakon A1 (što je 2010.).

Excel OFFSET Formula - Primjer # 3

U ovom ćemo primjeru izračunati zbroj zadnjih N redaka pomoću Excel OFFSET funkcije. Opća formula za zbrajanje zadnjih N brojeva je:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

Da biste sabrali zadnjih N opažanja iz svojih podataka, zajedno možete koristiti funkciju SUM zajedno s OFFSET i COUNT funkcijom. U ovom ćemo primjeru sumirati zadnjih 5 podataka. Imajte na umu da kada kažemo N, to je generalizacija bilo koje cjelobrojne vrijednosti. Dakle, možete izračunati za posljednja 3, zadnja 4, zadnja 5, itd., Ovisno o vašem skupu podataka, kao i vašem zahtjevu.

Učinimo to za naš skup podataka:

  • Sljedeću formulu unesite u ćeliju E2 datoteke excela.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Kliknite Enter da biste vidjeli izlaz.

Kao što smo već vidjeli, funkcija Excel OFFSET daje pravokutna dinamička raspona kada se daje početna referenca.

  • U ovom se slučaju početna referenca navodi u obliku B1 (kao prvi argument funkcije OFFSET).
  • Koristili smo funkciju COUNT jer nam treba zbroj promatranja iz zadnje vrijednosti. Funkcija COUNT pomaže OFFSETu da odredi koliko ima promatranja (redaka) u stupcu B. Broji sve numeričke vrijednosti u stupcu B (broj je 19).
  • S brojem 19, OFFSET formula postaje:

= OFFSET (B1, 19, 0, -5)

  • Ova formula započinje Pomak na B1 s 19 redaka i istim stupcem (0). Zatim parametar visine -5 pomaže ovoj funkciji proširiti raspon u 5 smjera unatrag (vidi negativni znak povezan s 5).
  • Dinamički raspon posljednjih pet opažanja dan je kao ulaz za funkciju zbrajanja. Koji prinosi zbrajaju one iz stupca B.

Excel OFFSET Formula - Primjer # 4

U ovom ćemo primjeru izračunati srednju prodaju za posljednje 3 godine. OFFSET funkcija može se koristiti kao argument za izračunavanje iste.

  • U ćeliju E2 stavite sljedeću formulu:

= MEDIJAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Pritisnite Enter za prikaz rezultata.

Ova formula djeluje na linije slične prethodnoj.

  • OFFSET Stvara dinamički raspon posljednja tri promatranja iz stupca B pomoću funkcije COUNT (= OFFSET (A1, 19, 0, -3)).
  • Ovo se može dati kao izlaz za funkciju MEDIAN. Koji zatim razvrstava promatranja u rastućem ili silaznom redoslijedu, a zatim odabire srednju vrijednost kao što vidite u rezultatu (212, 07 USD).

Excel OFFSET Formula - Primjer # 5

Ovdje ćemo izračunati srednju godinu pomoću funkcije MEDIAN i OFFSET.

  • Koristite sljedeću formulu za istu u ćeliji E3:

= ENT (offset (A1, COUNT (A: A), 0, -3))

  • Kliknite Enter da biste vidjeli izlaz.

Ista stvar se dogodila ovdje. Formula je preuzela dinamički raspon stvoren funkcijom OFFSET uz pomoć COUNT. Koristila ga je kao argument funkciji MEDIAN koja je zatim sortirala vrijednosti godine u uzlaznom ili silaznom redoslijedu, a zatim dala izlaz kao 2017.

Upotreba OFFSET formule je u stvaranju dinamičkih raspona. Uvijek je bolje raditi na dinamičkim rasponima umjesto na statičkim. Kao što možda imate podatke koji se dodaju svaki čas. Na primjer, zaposleni gospodar. Glavni podaci o zaposlenima uvijek će se povećavati, budući da se moraju pridružiti neki novi zaposlenici. Svaki put kada se koristi ista formula nema smisla. Umjesto toga, stvorite raspon koji je dinamičan pomoću OFFSET funkcije i pogledajte magiju. To će vam sigurno uštedjeti vrijeme.

Stvari koje treba zapamtiti

  • OFFSET funkcija vraća referencu samo na određeni raspon ćelija u Excelu. Ne pomiče stanice sa svojih izvornih položaja.
  • Prema zadanim postavkama ova funkcija djeluje na gornji dio (u redovima) i na desnoj strani (u smislu stupaca). No mogu se dobiti negativni argumenti za poništavanje zadane funkcionalnosti. Kao i -negativni argument retka pomaže nadoknađivanju podataka odozdo prema gore, a negativni argument col pomaže nadoknađivanju podataka s lijeve strane.
  • Formula OFFSET može usporiti Excel funkcionalnost jer je to formula koja se preračunava svaki put kad dođe do promjene radnog lista.
  • #REF greška bit će bačena ako je vaš domet izvan raspona vaše radne knjige.
  • Bilo koja druga funkcija koja očekuje referencu može koristiti OFFSET funkciju unutar nje.

Preporučeni članci

Ovo je vodič za OFFSET Formulu u Excelu. Ovdje smo raspravljali o načinu korištenja OFFSET funkcije u Excelu zajedno s praktičnim primjerima i besplatnim Excelovim predloškom. Možete i pregledati naše druge predložene članke -

  1. Kako zamotati tekst u Excelu?
  2. Jednostavan vodič za razvrstavanje u Excelu
  3. LOOKUP funkcija u Excelu
  4. Kako se u programu Excel koristi FIND funkcija?

Kategorija:

Excel savjeti