Bazirajući se na formulu u ćeliji 'E31'.
Ovo je tvoja formula, koja nije ispravna [vidi zadnji dio formule COUNTIF(
A$6:A31;A31), kao i *('JANUAR 2020'!$M$4:$M$18=
A$3)].
Code:
=IFERROR(INDEX('JANUAR 2020'!$R$4:$R$18;SMALL(IF(('JANUAR 2020'!$C$4:$C$18=$A$1)*('JANUAR 2020'!$M$4:$M$18=A$3)*('JANUAR 2020'!$A$4:$A$18=A31)*('JANUAR 2020'!$D$4:$D$18=B31)*('JANUAR 2020'!$P$4:$P$18=C31)*('JANUAR 2020'!$Q$4:$Q$18=D31);ROW('JANUAR 2020'!$A$4:$A$18)-ROW('JANUAR 2020'!$A$4)+1);COUNTIF(A$6:A31;A31)));"")
Ovo je moja ispravna formula [vidi zadnji dio formule COUNTIF(
A$31:A31;A31), kao i da je kod mene ovaj dio *('JANUAR 2020'!$M$4:$M$18=
A$28)].
Code:
=IFERROR(INDEX('JANUAR 2020'!$R$4:$R$18;SMALL(IF(('JANUAR 2020'!$C$4:$C$18=$A$1)*('JANUAR 2020'!$M$4:$M$18=A$28)*('JANUAR 2020'!$A$4:$A$18=A31)*('JANUAR 2020'!$D$4:$D$18=B31)*('JANUAR 2020'!$P$4:$P$18=C31)*('JANUAR 2020'!$Q$4:$Q$18=D31);ROW('JANUAR 2020'!$A$4:$A$18)-ROW('JANUAR 2020'!$A$4)+1);COUNTIF(A$31:A31;A31)));"")
Kada klikneš u polje za unos formule tada na Sheetu vidiš neke obilježene raspone (klikni mišem u polje formule i vidjet ćeš koji dio formule pripada određenom argumentu formule tj.
vidjet ćeš sintaksu određene funkcije, zavisi unutar koje funkcije si kliknuo).
Uoči da ti nemaš ugrađen uvjet iz ćelije ('JANUAR 2020'!$M$4:$M$18=
A$28) -> (Odvoz zemlje na deponiju).
Kod tebe u formuli stoji ('JANUAR 2020'!$M$4:$M$18=
A$3), dakle ova tvoja formula traži pogrešan uvjet.
Ovdje moraš voditi računa o
apsolutnim adresama u Excelu (vidi tutorijal). Dakle ova adresa 'A$3' prilikom kopiranja cijele formule neće promijeniti baznu adresu jer je 'Row3' apsolutan. To ti moraš sam učiniti ručno.
Isto tako u ćeliji 'A31' ti za ovaj 'k' imaš funkciju/formulu ROW('JANUAR 2020'!
$A26) a trebala bi biti ROW('JANUAR 2020'!
$A1).
U principu ova formula može biti i u obliku 'ROW($A1)'. Kada kopiraš cijelu formulu ova formula mijenja svoj argument koji je označen adresom ćelije i rezultate uvećava za 1 više prilikom svakog novog reda u kojem je kopirana.
U slijedećem redu u koji si kopirao formulu ona će izgledati ovako 'ROW('JANUAR 2020'!$A2)' itd.
Ako se pitaš zašto?, zato što je slovo stupca
apsolutno a broj reda relativan i prilikom kopiranja dolje, mijenja se broj reda tj. uvećava za 1.
Ako bi ovu formulu kopirao u desnu stranu, tada bi slovo za stupac ostalo isto jer je apsolutan (postavljen znak '$' ispred slovne oznake stupca) ali i broj za redak jer se on prilikom kopiranja u istom redu ne mijenja (jednostavno eksperimentiraj malo).
Isto tako pogledaj kraj formule.
Ovo je jedan od
argumenata SMALL funkcije i dio formule tj. 'k'= COUNTIF(A$6:A6;A6) i vraća rezultat broj 1. Isti slučaj je i sa ovom
ugniježđenom funkcijom COUNTIF(A$18:A18;A18) iz ćelije 'E18', koja je sastavni dio SMALL funkcije.
Prilikom kopiranja formule tebi se pojavila ugniježđena funkcija poput ove 'k'= COUNTIF(
A$6:A31;A31) a njen rezultat je 20. U prvoj kopiranoj formuli ovaj "k" argument SMALL funkcije mora vratiti broj 1.
Tek tada ovakvo ispravljenu formulu možeš kopirati prema dolje.
Što možeš zaključiti?
Dakle ovaj argument 'k' mora imati broj reda isti kao i red u kojem se nalazi cijela formula i prva formula uvijek vraća vrijednost 1. U ćeliji 'E31' ova funkcija/formula mora biti 'COUNTIF(A$31:A31;A31)'.
Da bi vidio kako funkcionira formula, korsti
Evaluate formula i tipku
'F9' (vidi tutorijal pri dnu stranice).
Vidim da si tu krajnju donju tablicu u rasponu 'A28:E38' kopirao iz raspona 'G3:K13'. Da si je premjestio sa 'Cut/Paste' tada ne bi došlo do greške, jer bi Excel automatski ispravno korigirao formulu.
I na kraju, vidi primjer datoteke u prilogu. Napominjem da sam te formule na brzinu prepravljao i postoji mogućnost greške. Nakon testiranja ako ima negdje greška prekontroliraj formulu sa greškom.
Ili jednostavno premjesti tablicu iz 'G3:K13' pa vidi kako izgledaju formule.
PS. Da bi razumio formule i njihovu kalkulaciju potrebno je da poznaješ
sintakse funkcije tj. u stvari ne moraš poznavati, dovoljno je da počneš pisati formulu i automatski se prikaže sintaksa sa argumentima funkcije kao i da razumiješ određene
argumente Excel funkcije, no detaljniji opis argumenata možeš vidjeti na internetu ili u Helpu Excela.
Također poželjno je da znaš i razumiješ koje rezultate vraća određena funkcija a isto tako kako
ugnijezditi funkciju/formulu unutar druge formule umjesto nekog argumenta.
Npr.
Excel MATCH funkcija vraća broj reda [MATCH(lookup_value;lookup_array;match_type)]
a
Excel COLUMN funkcija vraća broj stupca. [COLUMN(reference)]
Ako ugnijezdiš te dvije funkcije u
Excel INDEX funkciju [INDEX(array;row_num;column_num)], tada ona vraća rezultat iz stupca za određeni red koji vrati Match funkcija i stupac koje je definiran sa Column funkcijom. Itd, itd...
To izgleda nešo poput INDEX(Raspon_ćelija;MATCH_funkcija;COLUMN_funkcija).
Naravno trebaš znati da su argumenti odvojeni 'delimiterom' ili 'separatorom'. Na Ex-YU tj. Istočnoj Europi uobičajeno je da se koristi "točka-zarez" kao delimiter (ali to sve zavisi o postavkama OS-a ili Excela korisnika). To je tzv. 'Non-US settings'.
Za razliku od prethodnog kada su u pitanju 'US settings' delimiter je "zarez". To sada za sobom povlači i decimalne oznake vrijednosti, valute i sl. i još štošta.
ZAKLJUČAK: Kada kopiraš neku formulu iz jedne ćelije u drugu tada obrati pažnju na
apsolutne i relativne adrese.
Ako dođe do greške, da bi bio sigurniji tada koristi 'Cut/Paste' cijele tablice.
Nadam se da ćeš se snaći u ovom pojašnjenu i da će ti biti barem donekle razumljivo.
LP Ivan