04.03.2014
Excel
Excel , Excel trikovi

Trikovi u Excelu 35. deo – Težak zadatak sabiranja opsega ćelija.

  Upotrebite funkciju OFFSET da saberete opseg ćelija određene visine u Excelu.

PROBLEM KORISNIKA:

Treba da saberem opseg ćelija koji počinje u ćeliji A5 i ima visinu navedenu u ćeliji C5. Formula u ćeliji C5 izračunava jedan broj a ja treba da toliko ćelija uključim u zbir.

  REŠENJE: Upotrebite svestranu funkciju OFFSET.

Upotrebom funkcije OFFSET, Excel će Vam omogućiti:

  • Da pođete od određene ćelije ili opsega ćelija;
     
  • Da se pomerite od polazne pozicije za određen broj redova;
     
  • Da se pomerite od polazne pozicije za određen broj kolona.

Prva tri argumenta Vas dovode do ćelije u gornjem levom uglu dinamičkog opsega. Imajte u vidu da, ako je polazna ćelija J10, ako kao drugi argument navedete 5 možete da pokažete na ćeliju J15 a ako navedete -5 možete da pokažete na ćeliju J5. Na sličan način, treći argument 1 pokazivaće na ćeliju K10 a -1 na ćeliju I10.

 Ova prva tri argumenta Vam daju polaznu ćeliju. U gore navedenom problemu korisnika nije Vam potreban nijedan od tih podataka jer znate da uvek polazite od ćelije A5. Vaša formula će početi ovako: OFFSET(A5,0,0,...

  Funkcija OFFSET Vam omogućava da navedete još dva argumenta po izboru.
  • Četvrti argument opisuje visinu dinamičkog opsega.
     
  • Peti argument opisuje širinu dinamičkog opsega.
     
  • Možete koristiti OFFSET(A5,0,0,C5,1) da dobijete opseg koji je širok jednu kolonu i visok promenljiv broj redova.
     
[caption id="" align="aligncenter" width="207"] Slika 1.[/caption]   PAŽNJA:

U većini slučajeva, funkcija OFFSET će pokazivati na opseg koji je visok više od jedne ćelije. U tim slučajevima ne možete da prosto unesete =OFFSET() već morate da koristite OFFSET kao argument u nekoj drugoj funkciji.

 U ovom slučaju upotrebite =SUM(OFFSET(A5,0,0,C5,1)) kao što je prikazano na Slici 1.   [caption id="" align="aligncenter" width="199"] Slika 2.[/caption]   U ćeliji C5 promenite 5 u 3 i formula će sabrati opseg A5:A7 (Slika 2). [caption id="" align="aligncenter" width="387"] Slika 3.[/caption]     [caption id="" align="aligncenter" width="391"] Slika 4.[/caption]  

Funkcija OFFSET može da se koristi da pokaže na jednu ćeliju iznad aktuelne ćelije. Zašto biste se s tim mučili ako isto to radi jednostavna formula? (Slika 3). Međutim, šta se događa kada obrišete red br. 4 (Slika 4)? Jednostavna formula u koloni B se menja u grešku #REF!. Formula OFFSET u koloni E i dalje funkcioniše. Ako bi moglo da dođe do brisanja redova, onda je formula OFFSET ta koja spasava situaciju.

  [caption id="" align="aligncenter" width="307"] Slika 5.[/caption]   NAPOMENA:

Polazni opseg može biti veći od jedne ćelije. U primeru koji sledi, polazni opseg je A4:A11. Kao treći argument funkcije OFFSET koristi se MONTH(A1) radi pomeranja pet kolona udesno (Slika 5). Ta formula će sabrati kolonu koja odgovara datumu u ćeliji A1.

Slika 6.
  PAŽNJA:

OFFSET je promenljiva funkcija. To znači da prilikom svakog izračunavanja u radnoj tabeli (worksheet) funkcija OFFSET vrši ponovno računanje, čak i ako nije promenjena nijedna ćelija u tabeli. Te ćelije bi mogle da ostanu iste ceo mesec, a funkcija OFFSET će ipak vršiti ponovno računanje svaki put kada promenite neku ćeliju bilo gde u radnoj tabeli. Mnoge funkcije OFFSET mogu da izazovu usporavanje rada s radnom tabelom. U mnogim slučajevima umesto toga možete da koristite funkciju INDEX.

Ranije ste u temi vezanoj za funkciju VLOOKUP pročitali kako da koristite funkciju =INDEX(B4:M11,red,kolona) da dobijete jednu ćeliju iz nekog opsega. Ako izostavite argument za red, Excel će dati sve redove. Ekvivalentan rezultat će dati formula =SUM(INDEX(B4:M11,,MONTH(A1))) (Slika 6).

 

Kategorije