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 -Kumulativno zbrajanje vrijednosti po mjesecima sa više radnih listova u jedan radni list, Zbrajanje vrijednosti sa više radnih listova za određeni uvjet u Excelu, Sabiranje brojeva sa više radnih listova, Sum all values from multiple Sheets for condition name

Zbrajanje vrijednosti sa više radnih listova
za određeni uvjet u Excelu




sponzor



U ovom tutorijalu prikazati ću kako možemo zbrojiti određene vrijednosti za određeni uvjet (koji se ponavlja na ostalim radnim listovima) sa više radnih listova na jedan kumulativni radni list. U situaciji na slici ispod primjetite sve radne listove. Uvaj primjer bazira se na dvanaest mjeseci u trenutnoj godini.

  • Radni list 'Cumulative' je radni list na kojem prikazujemo sve vrijednosti za određeni uvjet, ove vrijednosti kopiraju se sa ostalih radnih listova zavisno o nazivu radnog lista (ime mjeseca u godini)
  • Radni listovi koji su imenovani nazivom mjeseca u trenutnoj godini (ja sam prikazao nazive samo za tri mjeseca, JAN, FEB, MAR)
  • Radni list 'DefinedNames' na kojem imamo popis uvjeta. U ovom primjeru taj popis sadrži imena osoba a može biti bilo koja vrsta podatka.

Kumulativno zbrajanje vrijednosti sa više radnih listova uz uvjet

Radni list Cumulative

Na slici iznad možete primjetiti

U stupcu A nalaze se imena osoba (ili neki drugi podatak). Ovaj podatak je Unique na svakom pojedinom radnom listu mjeseca u godini. Ove podatke možemo postavljati preko Data Validation padajućeg izbornika ili direktnim upisom imena (podatka)

Također uočite da za svaki pojedini mjesec postoje tri stupca. Ovi podaci automatski pomoću formule se kopiraju sa radnih listova koji imaju nazive mjeseca u godini ali tako da vrate rezultat za uvjet iz stupca 'A'.

- prvi stupac Value1
- drugi stupac Value2
- treći stupac Value1/Value2

Radni list JAN

Radni listovi sa nazivima dvanaest mjeseci u trenutnoj godini imaju skraćeni naziv (JAN, FEB, MAR...) radi jednostavnosti a također zbog imenovanja raspona podataka koje sam ja imenovao punim nazivom mjeseca u godini (january, february, march....)

Inače, savjetujem vam da uvijek imenujete raspone ćelija radi lakšeg kreiranja formule.
Dakle ovdje je važno da primjetite imenovani raspon podataka $A2$:$D$20 u kojem se na svakom radnom listu nalaze imena osoba koja sudjeluju u kalkulacijama za dotični mjesec. Vi po potrebi možete proširiti ovaj raspon.

Imenovani raspon ćelija u Excelu

Radni list FEB

Definiranje naziva raspona podataka na radnom listu u Excelu

Radni list MAR

Imenovanje naziva raspona podataka na Sheet u Excelu

Radni list DefineNames

Na ovom radnom listu primjetite da sam definirao raspon ćelija A2:A27 tj. imenovao raspon ćelija nazivom "names". Ovaj radni list nam služi za to da na njemu možemo dodavati imena osoba jer u svakom mjesecu nemamo sve osobe na popisu tj. u kalkulaciji.

Definiranje raspona ćelija u Excelu

Kako zbrojiti vrijednosti sa više radnih listova za određeno ime u Excelu 2013

Vratimo se na prvu sliku ovog tutorijala na radnom listu "Cumulative". Za svaki mjesec potrebno je kopirati podatak iz definiranog stupca sa određenog radnog lista za uvjet koji se pojavljuje u stupcu 'A'.

Formula u stupcu B (B2) - ova formula treba vratiti rezultat sa radnog lista 'JAN' za uvjet iz 'A' stupca tj. ćelije 'A2'. Ako pogledate uvjet (ime) u 'A' stupcu na radnom listu 'JAN' uočit ćete da je to vrijednost 301, dok je ta vrijednost 201 na radnom listu 'FEB' a na radnom listu 'MAR' nema vrijednosti jer ne postoji ime osobe 'A'

Formula u Excel ćeliji 'B2' je slijedeća:

=IFERROR(VLOOKUP($A2;INDIRECT($B$1);COLUMN($B1);FALSE);"")

Ovdje smo koristili funkciju COLUMN() koju smo ugradili u funkciju INDIRECT, a dotičnu smo ugradili u funkciju VLOOKUP a sve to opet ugradili u funkciju IFERROR da bi izbjegli prikazivanje greške ako podatak ne postoji za uvjet iz 'A' stupca.

Excel funkcija Column zamjenjuje treći argument unutar funkcije Vlookup (ovaj argument je inače broj stupca iz kojeg vraćamo rezultat).

Excel funkcija Indirect zamjenjuje drugi argument a to je raspon podataka ili 'array_table' koji pretražujemo. Ova funkcija Indirect koristi naziv mjeseca iz ćelije 'B1' a to je 'JANUARY'. Ovaj naziv mjeseca identičan je imenovanom rasponu ćelija na radnom listu 'JAN' što možete vidjeti na slici iznad za dotični radni list.

Excel funkcija Vlookup vraća rezultat iz stupca br. 2 na radnom listu 'JAN' za uvjet iz ćelije 'A2'.

Excel funkcija IFERROR. U slučaju da ne postoji podatak za uvjet iz stupca 'A' na radnom listu 'JAN' Vlookup formula vratila bi grešku '#N/A'. Da bi izbjegli prikazivanje ove greške cijelu Vlookup funkciju ugnijezdimo u IFERROR funkciju

Formula u Excel ćeliji 'C2' je slijedeća:

=IFERROR(VLOOKUP($A2;INDIRECT($B$1);COLUMN($C1);FALSE);"")

Ova formula je identična prethodnoj osim što se rezultat treba vratiti iz stupca broj 3 a to smo definirali Excel funkcijom Column(C1). Funkcioniranje i objašnjenje ove formule vrijedi kao i za prethodnu formulu iznad.

Formula u Excel ćeliji 'D2' je slijedeća:

=IFERROR(ROUND(IF(OR(IF(AND(B2="";C2="");"";B2/C2)="";IF(AND(B2>0;C2>0);B2/C2;"")="");"";B2/C2);3);"")

U ćeliji 'D2' imamo formulu koja ima dva uvjeta a vezana je na ćelije 'B2' i 'C2'. Ako su obe ćelije popunjene tada će formula izvršiti operaciju dijeljenja 'B2/C2'.

- U koliko nema nijednog podatka, u ovim ćelijama formula kao rezultat vraća grešku '#VALUE!' a prikazivanje ove greške izbjegavamo sa Iferror funkcijom.

- U koliko nema podatka u ćeliji 'B2' formula kao rezultat vraća praznu ćeliju jer nulu nemožemo dijeliti sa drugim brojem a prikazivanje ove greške izbjegavamo sa Iferror funkcijom.

- U koliko nema podatka u ćeliji 'C2' formula kao rezultat vraća grešku '#DIV/0!' jer broj ne možemo dijeliti sa nulom (0) a prikazivanje ove greške izbjegavamo sa Iferror funkcijom.

- Pomoću Excel funkcije ROUND u koju smo ugnijezdili IF, vrijednost zaokružujemo na 3 decimalne znamenke. Naravno ovo nije potrebno ako vi ne želite.

Sum values from multiple worksheets for condition

I na kraju ostale formule za mjesec FEBRUARY.

U ćeliji E2 formula je:
=IFERROR(VLOOKUP($A2;INDIRECT($E$1);COLUMN($B1);FALSE);"")

U ćeliji F2 formula je:
=IFERROR(VLOOKUP($A2;INDIRECT($E$1);COLUMN($C1);FALSE);"")

U ćeliji G2 formula je:
=IFERROR(ROUND(IF(OR(IF(AND(E2="";F2="");"";E2/F2)=""
;IF(AND(E2>0;F2>0);E2/F2;"")="");"";E2/F2);3);"")

sponzor


Izgled formula u stupcima za mjesec MARCH ostavljam vama na istraživanje.




     

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