logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali - Kako izbrojati sve datume koji jesu a koji nisu praznici u dva različita raspona ćelija

Brojanje datuma bazirano na dva raspona ćelija




sponzor



Kako izbrojati sve datuma koji su praznik

U ovom primjeru pokazat ću kako možemo prebrojiti ćelije koje se podudaraju za višestruke uvjete. Na prvoj slici ispod uočite raspon ćelija koji prikazuje određene datume. U 'L' stupcu nalaze se neki važni datumi poput praznika.
Naš zadatak je izračunati slijedeće rezultate:

  1. Koliko ima praznika u B2:J2 rasponu?
  2. Koliko u rasponu B2:J2 rasponu ćelija ima datuma koji nisu praznik?
  3. Koliko ima datuma koji nisu praznik i u B3:J3 rasponu je prazna ćelija?

Brojanje datuma u retku bazirano na praznicima u stupcu

Kako izbrojati sve datume koji nisu praznik

  1. Koliko ima praznika u B2:J2 rasponu
    Rješenje za ovaj zadatak je formula: =SUMPRODUCT(COUNTIF(B2:J2;"=" &L2:L4))
  2. Koliko u rasponu B2:J2 rasponu ćelija ima datuma koji nisu praznik
    Rješenje za ovaj zadatak je formula: =COUNT(B2:J2)-SUMPRODUCT(COUNTIF(B2:J2;L2:L4))
  3. Koliko ima datuma koji nisu praznik i u B3:J3 rasponu je prazna ćelija
    Za rješenje ovog zadatka u prvom primjeru korišten je pomoćni ('helper') redak. U ovom retku postavio sam formulu: =AND(B3="";COUNTIF($L$2:$L$4;B2)=0)
    Kao što vidite na slici ispod ova formula vraća TRUE/FALSE rezultate za svaku ćeliju.
    U ćeliji 'H8' nalazi se formula =SUMPRODUCT(--B4:J4) koja prebroji sve 'TRUE' rezultate u rasponu 'B4:J4'. (Ova dva minusa (dvije crtice) ispred izraza "B4:J4" konvertiraju svaki 'TRUE' rezultat u broj 1 (jedinicu). Pa tako imamo u završnom toku izračuna formulu =SUMPRODUCT({1\0\0\0\1\1\0\1\1}).
    Ako bi formulu pisali bez "crtica" tada bi ona u završnom koraku izgledala ovako: Dakle bez dva minusa na početku izraza =SUMPRODUCT({TRUE\FALSE\FALSE\FALSE\TRUE\TRUE\FALSE\TRUE\TRUE}) što odgovara rezultatima u redu 'B4:J4'.

Counting Data based on multiple criteria

Kako izbrojati datume koji nisu praznik a pripada im prazna ćelija ispod

Ipak, da bi riješili zadatak i izbjegli pomoćni ('helper') redak potrebna nam je jedna formula koja će izračunati koliko datuma nije praznik a u retku ispod imaju praznu ćeliju. Za tu svrhu koristiti ćemo slijedeću formulu.

=SUMPRODUCT(ISNA(MATCH($B$2:$J$2;$L$2:$L$4;0))*(B3:J3=""))

Count Non Duplicate Dates without helper row

Pojašnjenje, kako radi ova formula iznad

Da bi razumjeli kako funkcionira neka Excel formula, trebate poznavati Excel funkcije koje su korištene u formuli kao i sintakse i argumente Excel funkcije. Uz to potrebno je znati koji rezultat kao osnovni vraća pojedina Excel funkcija. Uz to potrebno je poznavati kako ugnijezditi neku funkciju unutar formule. Također, Excel ima "Evaluate Formula" naredbu preko koje se može vidjeti tijek kalkulacije neke formule, korak po korak. Postoji i zanimljiva mogućnost u Excelu, gdje koristimo "F9" tipku na tipkovnici a pomoću koje možemo vidjeti rezultat određene ugniježđene funkcije.

Dakle, imamo tri Excel funkcije: 'Sumproduct()', 'Isna()', 'Match()'. Kao što vidite 'Match()' funkcija je ugniježđena u 'Isna()' funkciju. Pa opet 'Isna()' funkcija je ugniježđena u 'Sumproduct()' funkciju.

Sintakse ovih funkcija izgledaju ovako:

'Sumproduct' funkcija
=SUMPRODUCT(array1;array2;array3;...) -->> Vraća zbroj umnožaka

'Isna' funkcija
=ISNA(value) -->> Vraća TRUE ako je vrijednost (value) greška #N/A

'Match' funkcija
=MATCH(lookup_value;lookup_array;match_type) --> Vraća relativan položaj stavke

Naša formula, figurativno bi se mogla prikazati ovako:
=SUMPRODUCT(ISNA(MATCH(range1;range2;0))*range3))

Dakle 'Sumproduct() formula' sadrži samo jedan "array" i on je slijedeća formula
ISNA(MATCH(range1;range2;0))*range3) -->> ISNA(MATCH*range3)

Slijedi da 'Isna() formula' sadrži jednu vrijednosti (value) koja se poslije množi sa rezultatom 'range3'
MATCH(range1;range2;0)

Prvi korak tijeka izračuna formule je MATCH funkcija. Ako koristite 'F9' tipku tada možete vidjeti da ova formula bazirana na njenim argumentima, vraća slijedeći rezultat ispod. Dakle argumenti 'range1' i 'range2' vraćaju rezultate:
MATCH({43831\43832\43833\43834\43835\43836\43837\43838\43839};{43834;43837;43832};0)

Izraz iznad prikazuje sve datume (nadam se da ste upoznati ili znate da Excel vidi datume kao niz brojeva) koji se nalaze u "range1" i "range2". Dakle vratiti će njihove relativne položaje.
Daljnji rezultat cjelokupne 'MATCH formule' je izraz {#N/A\3\#N/A\1\#N/A\#N/A\2\#N/A\#N/A}

Ovo je rezultat koji možemo opisati na slijedeći način. Uočite grešku "#N/A" i brojeve 3,1 i 2. Što ovi brojevi znače?

  • Prvi izraz je greška '#N/A'. Dakle datum 1.1.2020 (43831) ne postoji u 'range2' ili 'L2:L4' (vidi sliku iznad)
  • Drugi izraz je broj 3. Dakle datum 2.1.2020 (43832) nalazi se u trećem redu u 'range2' ili 'L2:L4' (vidi sliku iznad)
  • Treći izraz je greška '#N/A'. Dakle datum 3.1.2020 (43833) ne postoji u 'range2' ili 'L2:L4' (vidi sliku iznad)
  • Četvrti izraz je broj 1. Dakle datum 4.1.2020 (43834) nalazi se u prvom redu u 'range2' ili 'L2:L4' (vidi sliku iznad)
  • i tako dalje…

Bazirano na izrazima koje je vratila 'Match()' funkcija, 'ISNA() formula' će za svaki izraz greške "#N/A" vratiti 'TRUE' izraz
{TRUE\FALSE\TRUE\FALSE\TRUE\TRUE\FALSE\TRUE\TRUE}

Ako nadalje koristimo "F9" tipku tada za dio argumenta (B3:J3="") možemo uočiti da vraća slijedeći izraz
{TRUE\TRUE\FALSE\FALSE\TRUE\TRUE\TRUE\TRUE\TRUE}

Slijedeći korak je množenje dva izraza, {ISNA formula}*{B3:J3=""} pa u konačnici imamo kalkulacijski izraz
{1\0\0\0\1\1\0\1\1}. Ovaj niz će biti argument ('array1') za 'Sumproduct() formulu'. Rekli smo da 'Sumproduct funkcija' zbraja umnoške, pa tako zbroj brojeva ili vrijednosti u ovom izrazu je '5'. To je konačan rezultat.

Ako se pitate zašto su ove jedinice i nule (1 i 0) baš ovako raspoređene u nizu, tada se idemo prisjetiti logičkih operacija.

TRUE+TRUE=TRUE (1)
TRUE+FALSE=FALSE (0)
FALSE+TRUE=FALSE (0)
FALSE+FALSE=FALSE (0)

Dakle, Prvi 'TRUE' iz rezultata "ISNA formule" i prvi 'TRUE' iz rezultata izraza (B3:J3="") vraćaju broj 1.
Možda slika ispod bude jasnija za pojašnjenje (kako ono kažu "slika govori više nego tisuću riječi").

Pojašnjenje kalkulacije Excel formule - Evaluate formula

Još jedna formula koja vraća isti rezultat kao prethodno opisana i nalazi se ispod. Da ne bi opet sve pisao nanovo, pogledajte sliku ispod formule.

=SUMPRODUCT((COUNTIF($L$2:$L$4;$B$2:$J$2)=0)*(B3:J3=""))

Usporedba dva raspona ćelija i množenje sa drugim kriterijem




     

sponzor



COPYRIGHT © - 2005 - 2020 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela