IZBORNIK HOME FORUM ACCESS EXCEL WORD KAZALO
  HOME
  POPIS zadataka
   
- Excel FORMULE
- Excel FUNKCIJE
-

MICROSOFT EXCEL - primjeri zadataka za ECDL - evaluate formula ili vrednovanje formule u Excelu, kako raščlaniti formulu, objašnjenje pojedinih funkcija ugniježđene formule, kako redoslijedom izračunati rezultate i ugnijezditi funkciju u formulu.

Objašnjenje pojedinih funkcija unutar ARRAY formule

Search This Web Site

Adsense sponzor







1. Kako zbrojiti vrijednosti nekog stupca uz određeni uvjet, ako uvjet dijeli ćeliju sa ostalim uvjetima ?

Evaluate formula ili raščlanjenje formule na redoslijed izračuna funkcija

Kao što možete uočiti na slici iznad, ovaj zadatak je specifičan po tome što se uvjet koji postavljamo ( traženu šifru ) nalazi u nekim ćelijama u kojima dijeli ćeliju sa nekom drugom šifrom. Na slici iznad selektirana je ćelija H3 u kojoj pomoću formule koja ima ugnježđeno više funkcija. Uočite da su u formuli korištene APSOLUTNE adrese za ćelije raspona podataka (range).

Unos formule u ćeliju i izmjena dijelova formule

Formulu unosimo u polje za unos formula (formula bar) na radnom prozoru Excela odmah iza znaka "fx". Kada želimo neku formulu izmjeniti tada selektiramo ćeliju u kojoj se nalazi formula i kliknemo dvoklik mišem ako želimo izmjenu vršiti direktno u ćeliji. Isto tako nakon selektiranje ćelije u kojoj se nalazi formula možemo uočiti formulu u "polju za unos formula" (formula bar). Također u ovom polju za unos formula možemo izvršiti izmjenu formule tako što kliknemo mišem unutar formule i izvršavamo promjene.

Objašnjenje dijelova formule

Dakle formula glasi: =SUM(IF(ISERROR(FIND(G3;$C$3:$C$6));0;$D$3:$D$6)) koju završavamo sa Ctrl+Shift+Enter jer je to ARRAY - formula polja

Zadatak je: zbrojiti odgovarajuće vrijednosti u stupcu "D" za šifre RRU.

Gledajući ćeliju G3 u kojoj je postavljen uvjet RRU, možemo vidjeti da se u stupcu tj. rasponu ( range ) C3:C6 nalazi popis svih uvjeta koje možemo postaviti. U prve dvije ćelije C3 i C4 šifre su samostalne, dok u C5 i C6 imamo dvije šifre u istoj ćeliji. Upravo ovakva situacija gdje su dvije šifre u jednoj ćeliji stvaraju problem kod izračuna. Da je svaka šifra u zasebnoj ćeliji tada je problem riješiti jednostavno pomoću Vlookup-a ali ovako to ne ide s tom funkcijom.

Ovdje je potrebno za sve one šifre koje dijele ćeliju sa drugom šifrom izdvojiti posebno a potom je zbrojiti

U formuli se koriste slijedeće funkcije FIND => ISERROR => IF => SUM

Objašnjenje za ćeliju H3:

Koristit ćemo za početak funkciju Find, jer ta funkcija traži jednu tekstualnu vrijednost unutar druge, a nama su uvjeti text.
Krenut ćemo prvo sa funkcijom Find da bi pretražili stupac C u rasponu ćelija C3:C6 u kojima ćemo potražiti pojam iz ćelije G3
Funkcija FIND ce vratiti niz brojeva koji pokazuju postoji li tekstualni podatak u određenoj ćeliji
a rezultat ce biti { #Value! , 1, 5, 4 }

Značenje dijelova funkcije FIND:

- #Value! (#Vrij!) znači grešku koju će istražiti kasnije funkcija ISERROR, više o greškama u Excelu pogledajte na linku

Za usporedbu evo redosljedni primjer razrade toka izračuna funkcije FIND za uvjet u ćelji G3 i G4 samo za raspon u ćeliji C3.

FIND(G3;C3)
FIND("RRU";C3)
FIND("RRU";"RRI")
FIND("RRU";"RRI")
konačni rezultat funkcije FIND za uvjet u ćeliji G3 je greška
#VRIJ! ili #VALUE!

Redosljedni primjer razrade toka izračuna funkcije FIND za uvjet u ćelji G4 ( uvjet je RRI )

FIND(G4;C3)
FIND("RRI";C3)
FIND("RRI";"RRI")
FIND("RRI";"RRI")
konačni rezultat funkcije FIND za uvjet u ćeliji G4 je broj
1

Prvi korak izračuna u našoj kompletnoj formuli je pronalazak "uvjeta" u funkciji FIND ( napominjem da je ovo za uvjet u G3 )

Evaluate formula ili vrednovanje formule u Excelu

Na tabu ili kartici Formulas pomoću naredbe (ikone) Evaluate formula pokrećete prozor u kojem možete vidjeti tok redoslijeda proračuna operacija u samoj formuli

evaluate formula ili vrednovanje formule u Excelu

Kada se formula sa funkcijom FIND ugnijezdi u funkciju ISERROR u određenom momentu prilikom razrade toka izračuna formule imamo slijedeće: ISERROR({#VRIJ!/1/5/4})

- , ovi brojevi 1,5,4 znače slijedeće:

Ako primijenite razradu proračuna formule (evaluate formula ili vrednovanje formule ) ISERROR(FIND(G3;$C$3:$C$6)) uočit ćete ove brojeve i to istim redoslijedom

 

Ti brojevi označavaju redna mjesta znamenke u vrijednošću/textu koji se nalazi u pojedinoj ćeliji
1 - Tako imamo slučaj za ćeliju C4 gdje se traženi pojam u ovom slučaju RRU nalazi odmah na početku i počinje sa prvom znamenkom tj. brojem 1
5 - Pogledajte ćeliju C5 u kojoj je funkcija Find pronašla traženi pojam na početnom mjestu znamenke 5 ( RRI_RRU ili 123_5 ) uočite da i razmak između dijelova teksta se računa kao mjesto znamenke
4 - Pogledajte ćeliju C6 u kojoj je funkcija Find pronašla traženi pojam iz ćelije G3 na početnom mjestu znamenke u nizu tj. na broju 4 ( GG_RRU ili 12_4 )

evaluate formula

ISEROR je zadužen da #Value! (#Vrij!) zamjeni sa TRUE a svako pojavljivanje uvjeta sa FALSE,
tj. dobit ćemo sljedeći niz {TRUE/FALSE/FALSE/FALSE}
[{TRUE;FALSE;FALSE;FALSE}]

vrednovanje formule u Excelu

Adsense sponzor



Funkcija IF ce rezultirati za TRUE - 0 a za FALSE odgovarajuću vrijednost iz stupca D ( tj. brojeve 2,6,3 ).

Za kraj ostaje funkcija SUM da se takav niz vrijednosti {0/2/6/3} [{0;2;6;3}] zbroji. Ovi brojevi 2,6,3 su podaci iz stupca D a odgovaraju tekstualnoj šifri našeg uvjeta.
Rezultat prve formule u ćeliji H3 bit će zbroj 11.

Ova "komplicirana" formula završava se formulom polja ( matričnom formulom ili Array ) gdje je potrebno pritisnuti kombinaciju tipki CTRL+SHIFT+ENTER u H3 pa iskopirati prema dolje.
Više detalja za ARRAY - formulu polja 
autor idejnog rješenja i formule @timmy sa elitesecurity.org Također pogledajte formulu Countif i Sumif kada koriste funkciju Average kao argument

Adsense sponzor





Adsense sponzor




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