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 -Filtriranje podatakau Excelu, Prikaz određenih podataka iz filtrirane tablice raspona ćelija, Prosjek vrijednosti u filtriranoj Excel tablici, prikazivanje prva tri reda filtrirane tablice za određeni uvjet, Prikaz određenih podataka iz filtrirane tablice i Prosjek vrijednosti filtriranog raspona podataka, Prosjek brojeva vidljivih redova filtrirane Excel tablice, Izračun prosječne vrijednosti ako ručno ili manualno sakrijemo redove ili ćelije

Filtrirani podaci u Excelu, Prosjek filtrirane tablice
(prikaz određenih podataka iz filtrirane tablice podataka)




sponzor



O filtriranju u Excelu 2013 pisao sam u tutorijalu na linku 36 u sadržaju tutorijala za Excel 2013. U ovom tutorijalu pokazat ću kako možete filtrirati podatke u stupcu po određenim kriterijima i prikazati određen broj vidljivih redova iz filtrirane tablice ili filtriranog raspona podataka kao i prikaz prosjeka vrijednosti iz filtriranog raspona ćelija.

Startna situacija 1

U ovoj situaciji na slici ispod uočite raspon podataka A1:D9. Također uočite da su podaci sortirani silazno po stupcu 'D'.
U ćeliji 'F13' uočite uvjet za daljnje izračune, ovo je uvjet prema kojem želimo prikazati ostale podatke iz filtrirane Excel tablice.

ARRAY formula (CSE) u ćeliji 'F13': Ova formula vraća podatak iz posljednje ćelije u stupcu 'D'

=INDEX($C$2:$C$9;MAX(IF(SUBTOTAL(3;OFFSET($A2;ROW($C$2:$C$9)-ROW(C2);0));ROW($C$2:$C$9)-ROW(C2)+1)))

U rasponu ćelija 'A13:D16' uočite podatke koji prikazuju prva tri reda iz filtriranog raspona ćelija iznad. Dakle bez obzira koji mi kriterij ili uvjet u stupcu 'D' prvog raspona izabrali donja tablica prikazat će podatke za izabrani kriterij.

Prikazivanje rezultata prva tri reda iz filtriranog raspona ćelija u Excelu

Rezultat situacije 1

Na slici ispod uočite da su podaci cijelog gornjeg raspona ćelija filtrirani po stupcu 'C'. Podatak iz posljednjeg reda u stupcu 'C' vidimo u redu 8 a rezultat formule u ćeliji 'F13'.

ARRAY formula (CSE) u ćeliji 'A14' je slijedeća: Ova formula vrši pretraživanje filtriranog raspona podataka iznad i vraća prva tri reda podataka za uvjet u ćeliji 'F13'. Ovu formulu kopirajte desno pa zatim cijeli red kopirajte dolje. Prilikom kopiranja formula će automatski promijeniti potrebne adrese raspona ćelija i prilagoditi se potrebama rezultata jer sadrži apsolutne i relativne adrese.

=INDEX(A$2:A$9;SMALL(IF($F$13=(IF(SUBTOTAL(103;OFFSET($C$2:$C$9;MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9))-1;0;1));$C$2:$C$9));MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9));"");ROW($A1)))

Filtrirani podaci Excel tablice

U koliko želite na Excel radnom listu izbjeći ćeliju 'F13' i rezultat u njoj tj. ako želite možete prilagoditi adrese ćelija i raspona podataka i ugnijezditi formulu za uvjet (kriterij) u osnovnu formulu za vraćanje rezultata u ćeliji 'A14'.

Tada će ARRAY formula (CSE) u ćeliji 'A14' biti slijedeća. (Ako kopirate ovu formulu u svoj Excel Sheet tada obratite pažnju da formula bude u jednoj liniji (ne izlomljena kao što je to slučaj na ovoj web stranici). Formulu kopirajte u desno do 'D14' pa zatim selektirajte 'A14:D14' i kopirajte do željenog reda dolje ).

=INDEX(A$2:A$9;SMALL(IF(INDEX($C$2:$C$9;MAX(IF(SUBTOTAL(3;OFFSET($A$2;ROW($C$2:$C$9)-ROW($C$2);0));ROW($C$2:$C$9)-ROW($C$2)+1)))=(IF(SUBTOTAL(103;OFFSET($C$2:$C$9;MATCH(ROW($C$2:$C$9)
;ROW($C$2:$C$9))-1;0;1));$C$2:$C$9));MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9));"");ROW($A1)))

Naravno vi možete, ako želite prikazati više redova rezultata (ne samo 3 reda). Dovoljno je da formule kopirate do željenog broja redova. Također, ovo je samo jednostavni primjer podataka, vi možete ove formule koristiti za šire područje podataka.

sponzor


Prosjek vrijednosti u rasponu podataka Excel tablice

Na slici ispod uočite drugačiju situaciju raspona podataka. Stupac 'A' sadrži godine a stupac 'B' sadrži vrijednosti za određene godine. Uočite da se godine ponavljaju, dakle imamo duplikate u stupcu 'A'.

Ja sam na postavio nekoliko primjera kako možemo izračunati prosječnu vrijednost vidljivih redova određenog raspona podataka. Takođe sam postavio formule pomoću kojih prebrojavamo određene kriterije.

Prosjek vrijednosti za sve vidljive redove u rasponu podataka

Na slici iznad uočite slijedeće rezultate i formule:

U ćelijama 'A24' i 'A27' nalazi se uvjet (kriterij) za koji želimo izračunati određeni rezultat. U redu 24 nalaze se dva ista rezultata za jedan uvjet ali dvije različite formule.

ARRAY formula (CSE) u ćeliji 'B24' je slijedeća. (ova formula broji koliko se puta pojavljuje kriterij iz ćelijie 'A24' tj. 2002 godina u rasponu 'A2:A21').

=SUMPRODUCT(SUBTOTAL(3;OFFSET($A$2:$A$21;ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21));;1));ISNUMBER(
SEARCH(A24;$A$2:$A$21))+0)

ARRAY formula (CSE) u ćeliji 'C24' je slijedeća. (ova formula broji koliko se puta pojavljuje kriterij iz ćelijie 'A24' tj. 2002 godina u rasponu 'A2:A21').

=SUMPRODUCT(SUBTOTAL(3;OFFSET(B2;ROW(2:21)-2;))*($A$2:$A$21=A24))

U redu 27 nalaze se dva ista rezultata i dvije različite formule. Ova dva rezultata neće biti ista kada budemo filtrirali raspon podataka.

Također na slici iznad uočite rezultate u ćelijama 'B30', 'B31' i 'B34'. Njih ću pojasniti kasnije pri kraju ovog tutorijala.

Izračun prosjeka vrijednosti kada filtriramo jedan stupac

Uočite na slici ispod da sam izvršio filtriranje tablice podataka po stupcu 'A'. Za filtar sam postavio 2002 godinu. Excel je filtriranjem prikazao četiri reda podataka.

Uočite rezultate u ćelijama 'B27' i 'C27'. Rezultat je isti kao na slici iznad. Dakle vezani smo na uvjet iz ćelije 'A27'.

Ono što želim objasniti je drugačiji rezultat u ćelijama 'B30' i 'B31'. Pogledajte sliku iznad. Tamo je za dotične ćelije rezultat prosječna vrijednost 432,6 a na slici ispod taj rezultat je prosječna vrijednosti 1725. Dakle ista kao u ćelijama 'B27' i 'C27'.
Ovdje sam koristio Execl funkciju SUBTOTAL.

Formula u ćeliji 'B30' je slijedeća: (Ova funkcija vraća prosječnu vrijednost samo za vidljive redove, ručno skrivene redove ignorira). Ovdje je problem kod filtriranih redova pa zbog toga je potrebno kreirati drugu formulu kao u ćeliji 'B27'.

=SUBTOTAL(101;B2:B21)

Formula u ćeliji 'B31' je slijedeća: (Ova funkcija vraća prosječnu vrijednost za sve redove/ćelije u rasponu podataka, bez obzira da li su redovi ručno skriveni ili filtrirani).

=SUBTOTAL(1;B2:B21)

Dakle obe navedene SUBTOTAL formule sa argumentom '1' i '101' prikazuju isti rezultat kod filtriranih podataka. Uvijek vraćaju prosječnu vrijednost za vidljive redove/ćelije.

Prosjek vrijednosti za filtrirane podatke ako filtriramo jedan stupac

Na slici iznad uočite formulu u ćeliji 'B34'. Ova formula prebrojava vidljive ćelije ili redove, tj. vraća broj vidljivih ćelija u navedenom rasponu. Dakle, ova formula uzima u obzir i filtriranje podataka.

SUBTOTAL formula u ćeliji 'B34' je slijedeća: (Broj 2 u formuli ispod označava prvi argument (function_num) COUNT (prebrojavanje))

=SUBTOTAL(2;A2:A21)

Izračun prosjeka vrijednosti kada filtriramo dva stupca u rasponu podataka

Uočite na slici ispod da sam dodatno izvršio filtriranje stupca 'B', pa tako imamo samo tri vrijednosti prikazane u "tablici". sada uočite izračune prosječne vrijednosti (AVERAGE) za prikazane/filtrirane podatke tj. formula u ćelijama. Da smo u ovom slučaju koristili funkciju AVERAGE, tada bi nam formula vratila rezultat 432,6 jer bi u obzir uzela sve vrijednosti u nevedenom rasponu podataka stupca 'B'.

=AVERAGE(B2:B21)

Moram se vratiti na ćeliju 'B27'. U ovoj ćeliji nalazi se formula koja vraća rezultat prosječne vrijednosti iz filtrirane tablice, iz stupca 'B' za uvjet iz stupca 'A'.

ARRAY formula (CSE) u ćeliji 'B27' je slijedeća. (ova formula izračunava prosječnu vrijednost za filtrirane podatke iz stupca 'B' za uvjet u stupcu 'A'). Broj 2 u formuli ispod označava prvi argument (function_num) COUNT (prebrojavanje)

=IFERROR(AVERAGE(IF(SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1));IF(A$2:A$21=A27;B$2:B$21)));"")

Ako pogledate formulu iznad, u njoj se nalazi ugniježđena formula OFFSET
U stvarnosti ako pogledate Evaluate Formula, tako ugniježđena funkcija

OFFSET funkcija u formuli vraća (Pritisnite F9 i uočite rezultat ispod).

OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1)
rezultat = {101;102;103;104;105;106;107;108;109;110;111;125;113;900;115;116;117;1000;2000;3000}

IF funkcija vraća rezultat

IF(A$2:A$21=A27;B$2:B$21)
rezultat = {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;900;FALSE
;FALSE;FALSE;1000;2000;3000}

SUBTOTAL funkcija vraća rezultat (uočite, zadnje tri jedinice. One označavaju zadnje tri vrijednosti u rezultatu IF funkcije iznad pa sukladno tome uzima u obzir dotične vrijednosti.

SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1))
rezultat = {0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;1}

AVERAGE funkcija/formula također vraća rezultat kao i IF formula ali bez vrijednosti 900. dakle, u konačnici Average izračunava prosjek za dotične tri vrijednosti: ((1000+2000+3000=6000)/3)=2000)

AVERAGE(IF(SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1));IF(A$2:A$21=A27;B$2:B$21)))
rezultat = {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;1000;2000;3000}

Prosjek vrijednosti filtriranih podataka za dva stupca

Dakle, ova ARRAY (CSE) formula (ćelija 'B27') vraća prosječnu vrijednost filtrirane tablice (filtriranog stupca ili filtriranih podataka)

=IFERROR(AVERAGE(IF(SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1));IF(A$2:A$21=A27;B$2:B$21)));"")

Formula u ćeliji 'C27' je slijedeća: (Ova formula vraća prosjek za sve ćelije/redove u rasponu za određeni uvjet iz stupca 'A')

=AVERAGEIF($A$2:$A$21;A27;$B$2:$B$21)

Izračun prosječne vrijednosti ako ručno ili manualno sakrijemo redove ili ćelije

U slijedećem primjeru uočite različite rezultate iznad navedenih formula. U ovom slučaju ja sam ručno (manualno) sakrio sve redove od 'A2:A18'. Uočite na slici ispod da formula u ćeliji 'B27' ne prikazuje ispravan rezultat. Ali zato formula u ćeliji 'B30' prikazuje ispravan rezultat (Ova formula nema veze sa kriterijem, ona izračunava prosječnu vrijednost vidljivih redova/ćelija u rasponu 'B2:B21').

Prosjek vrijednosti kod manualno skrivenih Excel redova/ćelija

Tutorijali vezani za filtriranje u Excelu

- Filtriranje u Excelu
- Sortiranje u Excelu
- Kako filtrirati svaki N-ti red
- Advanced Filter in Excel - Napredni filtar u Excelu
- Prikaži posljednju popunjenu ćeliju iz određenog stupca
- Kako filtrirati između dva vremenska perioda u Excelu
- Filtriranje u Excelu koristeći zamjenske znakove (zvjezdicu)
- Kako sortirati ili filtrirati podatke na zaštićenom radnom listu
- Kako sakriti stupac nakon filtriranja ako je određena ćelija prazna
- Sortiranje i filtriranje kada su ćelije zaključane a radni list zaštićen lozinkom - Allow Users to Edit Range




     

sponzor



COPYRIGHT © - 2005 - 2019 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