INDIRECT

Funkcija INDIRECT kao rezultat daje adresu kad je upisana kao tekst, dakle vraća referencu navedenu tekstnim nizom. Reference su odmah vrednovane za prikaz njihova sadržaja. Koristite INDIRECT kada želite promijeniti referencu na ćeliju u formuli, bez promjene same formule.

Sintaksa

INDIRECT(ref_text;a1)

Ref_text    je referenca prema ćeliji koja sadrži referencu stila A1, referencu stila R1C1, naziv koji je određen kao referenca ili referencu prema ćeliji kao tekstni niz. Ako ref_tekst nije ispravna referenca ćelije, INDIRECT će prikazati vrijednost pogreške #REF!.

• Ako ref_text upućuje na drugu radnu knjigu (vanjska referenca), druga radna knjiga mora biti otvorena. Ako izvorna radna knjiga nije otvorena, INDIRECT vraća vrijednost pogreške #REF!.

A1    je logička vrijednost koja određuje koja vrsta reference je sadržana u ćeliji ref_text.

• Ako je a1 TRUE ili ga nema, ref_text se smatra referencom stila A1.
• Ako je a1 FALSE, ref_text se smatra referencom stila R1C1.

Primjer

Ovdje bi pojasnio rezultate sa slike. npr: "Vrijednost reference u ćeliji A2 (1,333)". U ćeliji A2 nalazi se tekst B2 što nam označava dotičnu ćeliju ali pisanu tekstualno u samoj ćeliji a u stvarnosti u ćeliji B2 imamo vrijednost 1,333

Kad napravite formulu koja upućuje prema ćeliji, referenca prema ćeliji će se obnoviti ako: (1) ćelija se premjesti naredbom Izreži za brisanje ćelije ili (2) ćelija se premjesti jer su umetnuti ili izbrisani reci ili stupci. Ako želite da formula uvijek upućuje prema istoj ćeliji, bez obzira na to izbriše li se redak iznad ćelije ili se ćelija premjesti, koristite funkciju radnog lista INDIRECT. Npr. ako uvijek želite upućivati prema ćeliji A10, koristite sljedeću sintaksu:

=INDIRECT("A10")

PRIMJER II

Situacija: Podaci se nalaze na Listu1 i linkani su na List2 ( Selektiranje područja => Kopiraj => Posebno ljepljenje => Zalijepi Vezu ). Dobijete identičnu sliku podataka na Listu1 i Listu2.

U koliko promijenite neki podatak na Listu1 promijenit će se i podatak na Listu2,
ALI, u koliko dodate neki red unutar vaših podataka na Listu1, taj podatak se neće vidjeti na Listu2.

Zato je dobro iskoristiti funkciju za pretraživanje INDIRECT.
=INDIRECT("List1!A1")
=INDIRECT("List1!A2")
=INDIRECT("List1!A3")

Tako ćete prilikom dodavanja redova i podataka na Listu1 uvijek imati i promjene na Listu2.

Naravno, potrebno je unaprijed isplanirati raspon ćelija u koje će te dodavati podatke da bi ste u Listu2 imali pripremljeno sve.

KAKO SAZNATI PODATAK U SVAKOJ PETOJ ĆELIJI STUPCA "A"

Zbrajanje vrijednosti iz svakog petog, šestog, sedmog ili n-tog reda pogledajte OVDJE

PROMJENA PODRUČJA U FUNKCIJAMA ( zamjena raspona ćelija nazivom )

Pretpostavimo da je naša veletrgovina prodaje namještaja maloprodajnim trgovinama "HRAST" i "JAVOR". Za svaku od trgovina imate tablicu u kojoj pratite prodaju svakog proizvoda posebno, za razdoblje od godine dana unatrag. Želite u nekoj ćeliji, po slobodnom izboru između ove dvije trgovine, zbrojiti iznose za sve proizvode prodane u proteklih godinu dana.
Funkcija koja Vam može pomoći da jednostavno riješite ovaj problem je INDIRECT.

Raspon  ćelija s podacima o prodaji u proteklih godinu dana tvrtke HRAST ( D312:D317 ) nazvali smo imenom UKHRAST a tvrtke JAVOR  ( H312:H317 ) imenom UKJAVOR. ( kako kreirate ime nekog raspona ćelija ? selektirajte raspon ćelija => Sa tekstualnog izbornika Umetanje => Naziv => Definiraj i dodajte ime. )

U ćeliji F319 upisano je ime UKHRAST, ime raspona ćelija koje želite zbrojiti. U ćeliji F320 upisana je funkcija SUM, a unutar nje funkcija INDIRECT koja kao argument ima adresu ćelije F319. Zadatak funkcije INDIRECT je da provjeri je li u ćeliji F319 upisan tekst kojeg može pretvoriti u adresu. Ako je tekstom u toj ćeliji upisana adresa nekog raspona ( npr: D312:D317 ) ili ime nekog raspona ( npr: UKHRAST ), funkcija INDIRECT promijenit će tekst upisan u ćeliju u adresu raspona i predati funkciji SUM, raspona ćelija koji treba zbrojiti, a ne tekst.
Dakle:pomoću funkcije INDIRECT promijenili smo TEKST u RASPON ĆELIJA ( uobičajeno se kaže KRITERIJ ).

Pretpostavite da ste u funkciju SUM jednostavno upisali adresu ćelije F319 bez funkcije INDIRECT što bi izgledalo ovako [ =SUM(F319) ]. Kako je u ćeliji F319 upisan tekst, a nema funkcije INDIRECT da taj tekst promjeni u adresu ( ili raspon ćelija ), SUM će kao rezultat dati nulu ( 0 ). Funkcija SUM ne zbraja one ćelije u kojima se nalazi upisan tekst.
Ali da ste u istu ćeliju upisali formulu [ =SUM(D312:D317) ] dobili bi ispravan rezultat kao na slici. Dakle kriterij ( naziv-ime ) UKHRAST nam zamjenjuje raspon ćelija D312:D317.

Ako sada u ćeliju F319 upišete ime drugog raspona ( kriterija ) pod nazivom-imenom UKJAVOR, funkcija SUM će zbrojiti ćelije u rasponu H312:H317. Vidi sliku ispod.  

Pogledajmo primjer:

Pretpostavimo da tvrtka "HRAST" od veleprodaje traži predračun za pet stolaca ( slika ispod ). Tablica sa svim proizvodima sortirana rastuće po oznakama, s imenom i cijenama upisan je u tablicu CJENIK ( B329:D334 ).

U posebnim tablicama za svakog kupca vodi se pregled prodaje proizvoda u posljednjih godinu dana. Za tvrtku "HRAST" to je tablica u rasponu F329:H334 kojem je dodijeljeno ime HRAST, a za tvrtku "JAVOR" to je raspon s imenom JAVOR , raspon  J329:L334 .

U zavisnosti od prije kupljene i ovim predračunom naručene robe kupcima u veletrgovini daje se popust. Svrha popusta je da kupci naručuju što više i što ravnomjernije pojedine proizvode. Pretpostavimo da je zbog zahtjeva tržišta isključena mogućnost da se neke maloprodajne trgovine specijaliziraju za prodaju samo jednog proizvoda. Tablica s rasponom iznosa i pripadajućim postocima nalazi se u tablici POPUST ( N329:O333 ).

Želite napraviti takav model proračuna da upisom tvrtke, imena proizvoda ( ne oznake ) i broja komada dobijete veleprodajnu cijenu s popustom za određenog kupca.

Zbog bolje preglednosti upisanih formula cijeli proračun modela postavljen je u stupcu, umjesto u retku i objašnjen je po ćelijama.

- Ćelija C336 ime tvrtke koja je naručila proizvod. Napomena: ime tvrtke je ujedno i ime za područje tablice J329:L334 u kojoj se prati prodaja robe toj tvrtki u posljednjih godinu dana.
- Ćelija C337 ime proizvoda koji je naručen.
- Ćelija C338 oznaka naručenog proizvoda. Formula na osnovu upisa imena proizvoda u ćeliji C337 iz tablice cjenika prikazuje oznaku naručenog proizvoda. Napomena: MATCH i INDEX upotrijebljeni su zato što je stupac s imenima proizvoda  nesortiran. Od funkcije MATCH s posljednjim argumentom 0 traži se da pronađe samo točno upisana imena proizvoda.
- Ćelija C339 cijena proizvoda. Funkcija VLOOKUP pomoću oznake u ćeliji C338 prikazuje cijenu iz CJENIKA ( B329:D334 )
- Ćelija C340 broj komada proizvoda koji je naručen
- Ćelija C341 ukupni iznos za naručeni proizvod. Umnožak cijene s brojem naručenih komada.
- Ćelija C342 prodaja naručenog proizvoda u protekloj godini tvrtki "HRAST". Formula u kojoj funkcija VLOOKUP na osnovu oznake proizvoda u C338 i iz imena raspona upisanog u ćeliji C336 ( funkcija INDIRECT ) izdvaja iz trećeg stupca kriterija "HRAST" prodaju 1543. ( koji se nalazi u H333 ) za proteklu godinu.
- Ćelija C343 zbroj ukupnog iznosa narudžbe i prodaje naručenog proizvoda u proteklih godinu dana tvrtki "HRAST". Ovaj zbroj poslužit će nam za određivanje visine popusta tvrtki "HRAST".
- Ćelija C344 popust tvrtki "HRAST" za naručeni proizvod. Funkcija LOOKUP pomoću zbroja u C343, prikazat će postotak iz tablice s popustima ( N329:O333 ).
- Ćelija C345 ukupni iznos narudžbe proizvoda umanjen za iznos popusta. Funkcija ROUND zaokružit će iznos na cijeli broj.

izvor: knjiga Excel za Windows ( autor: D. Božić )

Primjer INDIRECT funkcije gdje tražimo naslov stupca uz uvjet koji se nalazi negdje u tablici podataka

Na slici iznad uočite gornju tablicu u kojoj se nalaze naši podaci. Gradovi su raspoređeni po zonama. Mi želimo u nekom daljnjem proračunu uz uvjet nekog grada znati kojoj zoni pripada. Ovaj problem možemo rješavati na dva načina. Pomoću funkcije INDIRECT ili Offset. U dotičnu funkcije ugradit ćemo još neke dodatne funkcije poput MAX, COLUMN. Za početak imenovat ćemo range A2:F3 sa nazivom "gradovi" što je isto kao i $A$2:$F$3 Pomoću funkcije INDIRECT za rezultate u stupcu B5:B9 postavit ćemo formulu polja (matričnu ili array) koju nakon upisa završavamo sa Ctrl+Shift+Enter da bi dobili vitičaste zagrade na kraju. dakle formula u B5 izgleda ovako. =INDIRECT("R1C"&MAX(IF(gradovi=A5;COLUMN(gradovi);0));FALSE)  => + Ctrl+Shift+Enter

Kako iskoristiti funkciju INDIRECT kao zamjenu za naziv radnog lista (Sheeta) u samoj formuli

Kao što i sami znate, kada pišete neku formulu a podatak se nalazi u rasponu podataka u nekom drugom Sheetu tada ispred raspona podataka treba stajati naziv Sheeta sa uskličnikom.
npr: na sheetu "sumarum"imamo formulu i u njoj dio formule siječanj!$B$1:$B$10 što znači da se raspon podataka (range) nalazi na Sheetu "siječanj" od B1:B10. Ako bi imali potrebe pisati na Sheetu "sumarum" naziv Sheeta "siječanj" u ćeliji A1 i da tu ćeliju u nekoj formuli trebamo uzeti u obzir tada možemo isti izraz napisati kao INDIRECT($A$1&"!"&"$B$1:$B$10"). Ovaj izraz uzet će u obzir naziv Sheeta upisan u A1 i povezati ga sa nazivom Sheeta pa shodno tome uzeti i podatke u izračun sa dotičnog Sheeta.
Da bi vam bilo jasnije pogledajte tutorijal 3 uvjeta i zbrajanje vrijednosti sa specifičnog Sheeta

Ova web stranica koristi COOKIES - COPYRIGHT 2008 - 2021 - IvanC - ic.ims.hr