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 prikazati imena i prezimena svih djelatnika koji imaju plaću između 3000 i 4500 u određenom mjesecu godine?

Vrati popis svih radnika ako su zadovoljena tri uvjeta




sponzor



Vrati listu svih zaglavlja redaka ako su ispunjena tri uvjeta

Ako ste ikada imali potrebu prikazati višestruka zaglavlja redova u nekoj Excel tablici ako su ispunjena tri uvjeta a niste znali kako, u ovom tutorijalu prikazati ću vam kako možete prikazati popis svih djelatnika koji imaju zaradu između minimalne i maksimalne vrijednosti u određenom mjesecu godine.

Uzmimo situaciju kao na slici ispod. Uočite slijedeće podatke u rasponu ćelija.

  • U stupcu 'A' nalazi se popis svih radnika.
  • U stupcima od 'B' do 'M' nalaze popis svih mjeseci u godini.
  • U rasponu ćelija 'B2:M24' nalaze se novčani iznosi plaća radnika

Popis svih djelatnika koji imaju plaću između 3500 kn  i 4000 kn u određenom mjesecu godine

Naš zadatak je slijedeći:
Za postavljena tri uvjeta moramo prikazati popis imena svih radnika koji ispunjavaju postavljene kriterije.

  • Prvi kriterij je minimalna vrijednost (Excel ćelija 'P2')
  • Drugi kriterij je maksimalna vrijednost (Excel ćelija 'P3)
  • Treći kriterij je naziv mjeseca u godini (Excel ćelija 'P4)

Dakle, nakon unosa svih kriterija u rasponu ćelija od 'P7' do 'P24' želimo prikazati popis svih radnika koji ispunjavaju postavljena tri kriterija. Na slici iznad uočite boje ćelija kojima sam označio duplikate (radi lakšeg razumijevanja ovog tutorijala). U rasponu ćelija 'B2:M24' postavio sam Conditional Formatting koji crvenom bojom fonta označava ispunjene uvjete.

ARRAY formula (CSE formula)u 'P7' ćeliji je slijedeća: Ovu formulu kopirajte u Excel i postavite u jednoj liniji u formuli polja (Formula Bar). Unos formule završavate sa CSE.
Napomena! Ova formula prikazuje popis rezultata od zadnjeg reda prema prvom redu koji zadovoljava postavljene uvjete.

=IF(ROWS($P$7:P7)>SUMPRODUCT(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4));"";
INDEX($A$2:$A$24;SUMPRODUCT(LARGE(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*
($B$1:$M$1=$P$4)*(ROW($B$2:$M$24)-ROW(B$2:M$2)+1);ROWS($P$7:P7))))
)

Da bi lakše razumjeli formulu, upotrijebite Evaluate Formula pomoću kojeg možete vidjeti korak po korak tijekom kalkulacije formule. Glavna formula je Excel IF funkcija u kojoj imamo ugniježđene ostale funkcije poput Excel SUMPRODUCT funkcije, LARGE funkcije i ROW funkcije, ROWS funkcije, INDEX funkcije.

Ako pogledamo sintaksu Excel IF funkcije tada možemo uočiti slijedeće:

=IF(logical_test;value_if_true;value_if_false)

Za prvi argument IF funkcije koristimo slijedeću formulu: (Rows > Sumproduct)
ROWS($P$7:P7)>SUMPRODUCT(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4))

Prvi dio formule u kojem koristimo ROWS funkciju vraća rezultat broj 1 kao broj retka (rekao sam da se formula nalazi u ćeliji 'P7'). Drugi dio je SUMPRODUCT formula koja vraća broj reda za određene uvjete (Sumproduct množi tri kriterija), a rezultat je broj 4. Dakle ako pogledamo 1 nije veće od 4 pa je rezultat za prvi argument 'FALSE' (1>4=False).

Ako ovu formulu kopiramo prema dolje tada imamo situaciju (slika ispod). ROWS Funkcija kopiranjem prema dolje vraća redne brojeve redaka. Funkcija SUMPRODUCT stalno vraća rezultat broj '4'. Ako se pitate što znači taj 'broj četiri' tada uočite da ova formula ima tri uvjeta. S obzirom da je Sumproduct formula pronašla stupac u kojem su ispunjena prva dva uvjeta i izbrojala da '4' ćelije sadrže vrijednosti koje zadovoljavaju uvjete u stupcu koji je postavljen kao treći uvjet.

Broj ćelija koje ispunjavaju tri uvjeta u stupcu

S obzirom da prvi argument vraća rezultat 'FLASE', Excel IF funkcija vratit će rezultat iz trećeg argumenta.
U slučaju kada je za ovaj prvi argument rezultat 'TRUE' konačan rezultat formule bit će prazna ćelija (vidi sliku iznad).

Za treći argument postavljena je slijedeća formula.(Index(raspon; broj reda))
INDEX($A$2:$A$24;SUMPRODUCT(LARGE(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4)*
(ROW($B$2:$M$24)-ROW(B$2:M$2)+1);ROWS($P$7:P7)))
)

U ovoj formuli osnova je INDEX funkcija koja vraća podatak iz raspona ćelija baziran na broju reda i stupca (pogledajte sintaksu funkcije na linku iznad). Dakle ova formula u drugom argumentu vratit će broj '21'. Taj broj '21' je rezultat Sumproduct formule. Da se odmah razumijemo. Mogli smo izostaviti funkciju Sumproduct i upotrijebiti samo LARGE, rezultat bi bio isti. To je broj reda u kojem se nalazi naš prvi podatak ako se baziramo na raspon koji počinje u drugom redu (dakle stvarni broj reda 22-1 zaglavlje=21). Uočite na slici iznad da je to posljednji redak u kojem su zadovoljena naša tri uvjeta (pogledajte 'L22' ćeliju na slici iznad).

Kopiranjem ove CSE formule automatski se mijenjaju brojevi redaka koji zadovoljavaju tri postavljena uvjeta tako da formula vraća zaglavlje reda iz 'A' stupca.

Ako pogledamo treći argument 'IF funkcije' tada možemo uočiti (vidi sliku ispod) da kombinacija Excel funkcija Sumproduct/Large ili sama Large funkcija, vraćaju isti rezultat a to je broj reda u kojem se nalazi vrijednost koja je zadovoljila sva tri uvjeta (vidi 'Y i Z' stupce na slici ispod. Kada LARGE funkciju tj. formulu ugnijezdimo unutar INDEX funkcije tada će rezultat biti prikazan iz dotičnog reda ali iz prvog stupca zadatog raspona ćelija. Tako, u 'AA' stupcu na slici ispod uočite rezultate iz 'A' stupca raspona ćelija koji smo postavili ('($A$2:$A$24'). Dakle formula vraća sadržaj svih ćelija iz 'A' stupca za dotični red.

Nadalje, također uočite u 'AA' stupcu na slici da je formula do kraja vratila prvi podatak iz 'A' stupca a to je Name "ddd". Naravno to nama ne odgovara pa ćemo to riješiti "prvim i drugim" argumentom 'IF funkcije'. Uočite ćelije markirane sivom bojom pozadine. Za svaki rezultat formule u kojem je prvi argument 'IF funkcije = TRUE', treći argument ("ddd") neće se prikazati jer će će 'IF funkcija' vratiti rezultat prazna ćelija.

Na slici ispod uočite što su "stvarni redovi" označeni brojevima od 1:24 a što su "redovi raspona ćelija" u kojem je 'red broj 1' u ćeliji 'L2'.

Rezultati formule koja sadrži INDEX SUMPRODUCT i LARGE Excel funkcije

Koliko sam dobro pojasnio i koliko ste uspjeli razumjeti? ne znam, ali slike govore više od 1000 riječi.

Dakle finalna formula izgleda ovako:

=IF(ROWS($P$7:P7)>SUMPRODUCT(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4));"";
INDEX($A$2:$A$24;LARGE(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4)*(ROW($B$2:$M$24)-ROW(B$2:M$2)+1);ROWS($P$7:P7))))


Kako prikazati popis podataka iz stupca obrnutim redoslijedom

Ako želite invertirati (reverse) redoslijed prikazanog popisa upotrijebite slijedeću formulu ispod. Postavite ovu formulu na primjer u 'S7' ćeliju i kopirajte je prema dolje.

=INDEX($P$7:$P$24;LARGE(IF($P$7:$P$24<>"";ROW($P$7:$P$24)-MIN(ROW($P$7:$P$24))+1;"");ROWS($S$6:S6)))

Invertiranje popisa podataka iz Excel stupca

I na kraju evo još jedan primjer kako prikazati popis svih zaglavlja redaka baziran na tri uvjeta (uvjet između dvije vrijednosti i naziv mjeseca u godini)

primjer kako prikazati popis svih zaglavlja redaka baziran na tri uvjeta

Obratite pažnju kod korištenja Excel funkcije SUMPRODUCT. Ako imate puno redova i podataka ili ako imate puno formula koje sadrže Sumproduct funkciju, tada vam to može usporiti Excel prilikom izračuna.




     

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