IZBORNIK HOME FORUM ACCESS 2003 EXCEL 2003 WORD 2003  .
   
   
HOME
FORUM Win Tips&Tricks
   
KAKO INSTALIRATI
WINDOWS XP ?
Kako instalirati Win XP sa USB STICKA
Kako instalirati WINDOWS 7 ?
Naučite za 15 minuta raditi u Windows XP
Naučite Internet Explorer i Outlook Express
za 15 minuta
Kako kreirati BOOT CD za instalaciju Win95
   
MS OFFICE 2003
MS OFFICE 2007
   
   
HOME NETWORK
tutorijal za mreže
Network Windows 7 - XP
   
ZANIMLJIVI LINKOVI
BROJEVNI SUSTAVI
(DEC, OKT, BIN, HEX )
CMD - Command Prompt
CISCO - CCNA tutoriali
VLSM and SUBNETTING
   
Tutorijali za phpBB forum
JAVASCRIPT
VISUAL BASIC 6.0
AUTOCAD 2007
 
-
MICROSOFT EXCEL 2007 - Sum Color Cells, Count color cells, Zbrajanje vrijednosti ćelija u boji, Kako zbrojiti obojane ćelije, Koliko je ćelija u boji, sum cells by color, brojanje ćelija u boji

Kako zbrojiti ćelije u boji u Excelu
(Kako zbrojiti vrijednosti u ćelijama u boji)

Search This Web Site




Zbrajanje vrijednosti koje se nalaze u ćelijama u boji, kako prebrojati ćelije određene boje
(kako izbrojiti obojane ćelije, koliko je ćelija u boji)

Pojavom Excela 2007 i novitetima njegovih funkcija mnogi su počeli koristiti boje u ćelijama, Conditional Formatting , brojanjem tj. koliko je ćelija u boji u nekoj tablici (rasponu/range) a uz to i željom za zbrajanjem ćelija u boji, pojavio se problem. Ovaj problem zbrajanja vrijednosti koje se nalaze u ćelijama može se riješiti na upotrebom VBA koda. Na interentu možete pronaći dosta primjera. U ovom tutorijalu prikazat ću vam dva načina kako možete zbrajati brojeve koji se nalaze u obojanim ćelijama a isto tako kako možete izbrojiti "Koliko je ćelija u boji".

U ovom tutorijalu pokazat ću slijedeće:

PRIMJER 1 => Korištenje =FGCol(cell) i =BGCol(row;col)
Kako se određuje broj neke boje pomoću VBA koda
- Font Foreground (postavljanje broja boje za slova u ćeliji) => =FGCol(cell)
- Background (postavljanje broja boje za boju kojom je ćelija obojana) => =BGCol(row;col)

PRIMJER 2 => korištenje formule =BGCol(row;col) i SUMPRODUCT za Sum i Count
a) Zbrajanje vrijednosti u obojanim ćelijama sa pomoćnim stupcem uz uvjet da sve ćelije moraju biti obojane pa i one koje netrebaju biti u boji (njih ćemo obojati bijelom bojom, radi greške)
b) Koliko ima ćelija u određenoj boji koje se nalaze u našem rasponu podataka

PRIMJER 3 => korištenje formule =bGColr(cell) i SUMPRODUCT za Sum i Count
a) Zbrajanje vrijednosti u obojanim ćelijama sa pomoćnim stupcem koji sadrži brojeve (IndexColor) boja uz to da sve ćelije ne moraju biti obojane
b) Koliko ima ćelija u određenoj boji koje se nalaze u našem rasponu podataka

PRIMJER 4 => korištenje formule =ColorFunction(cell;range;True) i =ColorFunction(cell;range;False) za Sum i Count
a) Zbrajanje vrijednosti u obojanim ćelijama sa pomoćnom ćelijom koja je obojana uz to da sve ćelije ne moraju biti obojane
b) Koliko ima ćelija u određenoj boji koje su iste boje kao i specificirana ćelija


PRIMJER 1.

Kako odrediti broj za neku boju u ćeliji (ColorIndex)

Insertirati u VBE kod koji će nam  omogućiti da koristimo =FGCol(cell) i =BGCol(row;col)  za određivanje ColorIndex tj. broj neke boje (ne radi sa Conditional Formatting !)

Kao što možete vidjeti iz samih formula ovdje se radi o Foreground boji teksta i Background boji ćelije. Nas trenutno zanima pozadina (background) boje ćelije.

Iskoristivost ovih formula nam služi da možemo odrediti koja boja u nekoj ćeliji ima svoj "specifičan" broj. Da bi mogli upotrijebiti ove formule potrebno je u VBE umetniti Module i VBA Code. Ako ne znate kako se radi umetanje pogledajte link Kako se umeće VBA Code u Excelu. U principu uradite slijedeće

Ova dva VBA koda ispod COPY  (prvi je za Foreground a drugi za Background). Ako želite stalno imati ove formule aktivne kada god radite u Excelu tada ovaj VBA Code snimite u PERSONAL.xls (PERSONAL.xlsb)

Function FGCol(MyRef As Range) As Long
FGCol = MyRef.Font.Color
End Function

Function BGCol(MRow As Integer, MCol As Integer) As Long
BGCol = Cells(MRow, MCol).Interior.ColorIndex
End Function

2. Otvorite Excel Book u kojem se nalazi vaša tablica
3. Pritisnite ALT+F11
4. Otvorio Vam se VBA editor
6. Klik na Insert => Module , i taj module Vam je automatski selektiran u lijevom dijelu prozora a kursor blinka u desnom dijelu prozora. Paste VBA code u desni dio radnog prozora i snimite

Kako odrediti broj za neku boju u ćeliji

Kako iskoristiti ove formule, pogledajte u primjeru na slici ispod. Dakle imamo ćelije u kojima se nalazi Foreground boja teksta i Background boja ćelije. Desno imamo formulu koje se pozivaju na ćelije lijevo i određuju boju prema zadanome. Nas zanima ćelija B3 sa zelenom bojom. Formula glasi =BGCol(3;2) (=BGCol(row;col) što znači da ćelija B2 se nalazi u sjecištu, tj. u trećem redu u drugom stupcu. Rezultat te formule je broj boje (koju prepoznaje Excel) a to je broj 14. Dakle zelena boja u ćeliji B2 jednaka je broju 14.
Ako ćelija koju kontrolirate nije obojana formula će vratiti rezultat -4142. Dakle sve ćelije koje kontrolirate ovom formulom moraju biti obojane. Sada se pitate a što za ćelije koje u nekoj tablici nisu i netrebaju biti obojane? Obojajte ih BIJELOM bojom pa će djelovati kao da nemaju boju i nećete ih uvrstiti u svoju kalkulaciju ili formulu ugradite u IF funkciju.

Također uočite brojeve ColorIndex (Broj boje) za boje koji se razlikuju od onih koje možete dobiti
ako koristite =bGColr(cell) koja je objašnjena u Primjeru 3.

Kako odrediti broj za neku boju slova ili pozadine u excel ćeliji

NAPOMENA: AKo radite sa ćelijama u boji preko VBA coda tada refresh F9 ne vrijedi uvijek već je potrebno nakon svake promjene pritisnuti kombinaciju CTRL+ALT+F9. Boje dobivene sa Conditional Formatting (Uvjetno oblikovanje) nemaju utjecaja na ove radnje i boju uvijek trebate postavljati sa izbornika boja.


PRIMJER 2.

Kako zbrojiti vrijednosti u obojanim ćelijama i izbrojiti koliko je obojanih ćelija (pomoćni stupac za broj boje)

Za ovaj zadatak situacija je slijedeća:

1. U rasponu podataka A2:C10 (u tablici) nalaze se nekakvi podaci.
2. U stupcu A je naziv nekog artikla, u stupcu B je količina i određene boje za svaku ćeliju i u stupcu C je ColorIndex tj. broj za svaku boju koja se nalazi ispred
3. Potrebno je zbrajati (sum) količine (vrijednosti) u ćelijama iste boje a uz to izračunati koliko je ćelija u nekoj boji (count)

Potrebno je insertirati u VBE kod koji će nam omogućiti da koristimo =BGCol(row;col)  za određivanje ColorIndex tj. broj neke boje (ne radi sa Conditional Formatting !)

Ovaj kod ispod COPY  (za Background). Ako želite stalno imati ovu "funkciju" aktivnu kada god radite u Excelu tada ovaj VBA Code snimite u PERSONAL.xls (PERSONAL.xlsb)

Function BGCol(MRow As Integer, MCol As Integer) As Long
BGCol = Cells(MRow, MCol).Interior.ColorIndex
End Function

2. Otvorite Excel Book u kojem se nalazi vaša tablica
3. Pritisnite ALT+F11
4. Otvorio Vam se VBA editor
6. Klik na Insert => Module , i taj module Vam je automatski selektiran u lijevom dijelu prozora a kursor blinka u desnom dijelu prozora. Paste VBA code u desni dio radnog prozora i snimite

VBA funkcija kao procedura za background cells

Pratite sliku ispod, u stupcu B nam se nalaze nekakve vrijednosti koje smo obojali za svaku ćeliju neku boju. U stupcu C nam se nalazi formula =IF(B2>0;BGCol(2;2);"") koja kaže (ako je vrijednost u ćeliji B2 veća od nule tada je rezultat broj boje dotične ćelije (ako B2 nije veće od nule tada je rezultat prazna ćelija; uočite slučaj u ćeliji C10 da je rezultat prazna ćelija jer nema vruijednosti u ćeliji A10). Ćelija C6 ima negativan rezultat jer ima vrijednost u ćeliji B6 ali nije obojana, Ako bi ćeliju C6 obojali bijelom bojom tada bi broj boje bio 2)

kako zbrojiti brojeve u ofarbanim ćelijama u boji

ZBRAJANJE VRIJEDNOSTI U ĆELIJAMA KOJE IMAJU BOJU

U ćeliji C13 imamo formulu =SUMPRODUCT(($B$2:$B$10)*($C$2:$C$10=4)) koja zbraja vrijednosti za boju pod brojem 4 a u ćeliji C14 računamo SUM za broj boje 44 i C15 za broj boje 33. Dakle ova formula će zbrojiti vrijednosti za sve ćelije koje su iste boje. Ova SUMPRODUCT formula sastoji se od dva dijela. Prvi dio je raspon B2:B10 u kojem se nalaze ćelije u boji a drugi dio je raspon C2:C10 u kojem se nalaze brojevi za određene boje u stupcu B


PRIMJER 3.

Kako zbrajati podatke u obojanim ćelijama i izbrojiti koliko je obojanih ćelija (pomoćni stupac za broj boje)

Za ovaj zadatak situacija je slijedeća:

1. U rasponu podataka A2:C10 (u tablici) nalaze se nekakvi podaci.
2. U stupcu A je naziv nekog artikla, u stupcu B je količina i određene boje za svaku ćeliju i u stupcu C je ColorIndex tj. broj za svaku boju koja se nalazi ispred
3. Potrebno je zbrajati (sum) količine (vrijednosti) u ćelijama iste boje a uz to izračunati koliko je ćelija u nekoj boji (count)

Potrebno je insertirati u VBE kod koji će nam omogućiti da koristimo =bGColR(cell)  za određivanje ColorIndex tj. broj neke boje (ne radi sa Conditional Formatting !)

Ovaj VBA kod ispod COPY  (za Background). Ako želite stalno imati ovu "funkciju" aktivnu kada god radite u Excelu tada ovaj VBA Code snimite u PERSONAL.xls (PERSONAL.xlsb)

Function BGColR(MyRef As Range) As Long
Application.Volatile (True)
BGColR = MyRef.Interior.Color
End Function

2. Otvorite Excel Book u kojem se nalazi vaša tablica
3. Pritisnite ALT+F11
4. Otvorio Vam se VBA editor
6. Klik na Insert => Module , i taj module Vam je automatski selektiran u lijevom dijelu prozora a kursor blinka u desnom dijelu prozora. Zalijepite VBA kod u desni dio radnog prozora i snimite

Pratite sliku ispod, u stupcu B nam se nalaze nekakve vrijednosti koje smo obojali za svaku ćeliju neku boju, ćelije koje nemamo potrebe obojati su bez boje. Ovdje smo koristili formulu =bGColR(cell) za razliku od primjera 1. U stupcu C nam se nalazi formula =bGColR(B2) ili =IF(B2>0;bGColR(B2);"") .Ćelija C6 nije obojana ali ima vrijednost a ćelija B10 nije obojana i nema vrijednost (razlika od primjera 1). Također uočite da broj za boju ćelije B10 je 16777215 iako nema fill color (boju). Ako ovu formulu ugradimo u funkciju IF tada nam se neće pojaviti broj za boju prazne ćelije.

brojanje ćelija i zbrajanje brojeva u ćelijama u boji koristeći funkkciju Sumproduct i broj pozadine background

ZBRAJANJE VRIJEDNOSTI U ĆELIJAMA KOJE IMAJU ODREĐENU BOJU

U ćeliji C13 imamo formulu =SUMPRODUCT(($B$2:$B$10)*($C$2:$C$10=3407718)) koja zbraja vrijednosti za boju pod brojem 3407718 a u ćeliji C14 računamo SUM za broj boje 52479 i C15 za broj boje 16763955. Dakle ova formula će zbrojiti vrijednosti za sve ćelije koje su iste boje. Ova SUMPRODUCT formula sastoji se od dva dijela. Prvi dio je raspon B2:B10 u kojem se nalaze ćelije u boji a drugi dio je raspon C2:C10 u kojem se nalaze brojevi za određene boje u stupcu B.
Uočite razliku za brojeve boja u Primjeru 2. i Primjeru 3. s obzirom na VBA kod i formule =BGCol(row;col), =bGColR(cell). U Primjeru 2. su brojevi niže vrijednosti.


PRIMJER 4.

Kako zbrojiti vrijednosti u obojanim ćelijama i izbrojiti koliko je obojanih ćelija (bez pomoćnog stupca)

Za ovaj zadatak situacija je slijedeća:

1. U rasponu podataka A2:C10 (u tablici) nalaze se nekakvi podaci.
2. U stupcu A je naziv nekog artikla, u stupcu B je količina i određene boje za svaku ćeliju
3. Potrebno je zbrajati (sum) količine (vrijednosti) u ćelijama iste boje a uz to izračunati koliko je ćelija u nekoj boji (count)

Potrebno je insertirati u VBE kod koji će nam omogućiti da koristimo =ColorFunction(cell;range;True)

Ovaj kod (autora: Ozgrid) ispod COPY  (za Background cell). Ako želite stalno imati ovu "funkciju" aktivnu kada god radite u Excelu tada ovaj VBA Code snimite u PERSONAL.xls (PERSONAL.xlsb)

 

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

  Dim rCell As Ranges Range

  Dim lCol As Long

  Dim vResult

''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com

'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

  lCol = rColor.Interior.ColorIndex

  If SUM = True Then

  For Each rCell In rRange rRange

  If rCell.Interior.ColorIndex = lCol Then

  vResult = WorksheetFunction.SUM(rCell,vResult)

  End If

  Next rCellt rCell

  Else

  For Each rCell In rRange

  If rCell.Interior.ColorIndex = lCol Then

  vResult = 1 + vResult

  End If

  Next rCell

  End If

   ColorFunction = vResultvResult

End Function

2. Otvorite Excel Book u kojem se nalazi vaša tablica
3. Pritisnite ALT+F11
4. Otvorio Vam se VBA editor
6. Klik na Insert => Module , i taj module Vam je automatski selektiran u lijevom dijelu prozora a kursor blinka u desnom dijelu prozora. Paste VBA code u desni dio radnog prozora i snimite

Pratite sliku ispod, u stupcu B nam se nalaze nekakve vrijednosti koje smo obojali za svaku ćeliju neku boju. Ovdje nemamo pomoćni stupac u kojem trebamo izračunati broj neke određene boje u Excelu. Umjesto toga imamo obojanu ćeliju na koju vežemo formulu. U ovom slučaju to su ćelije B13, B14 i B15 koje nismo koristili u prethodnim primjerima.

count colored cells and sum value in the color cells

Adsense sponzor



ZBRAJANJE VRIJEDNOSTI U ĆELIJAMA KOJE IMAJU BOJU POMOĆU "ColorFunction"

U ćeliji C13 imamo formulu =ColorFunction($B$13;$B$2:$B$10;TRUE) koja zbraja sve vrijednosti za boju kojom je obojana ćelija B13. Ovdje vodite računa da boje moraju biti "IDENTIČNE" a najbolje ih je kopirati sa naredbom (gumbom) Format Painter, onaj mali KIST ili ČETKICA u izborniku (Clipboard-Cut/Copy/Paste). U protivnom ako ne pogodite iste boje (jer ih ima puno kao kod Excela 2007) tada nećete dobiti ispravan rezultat.

Kako postaviti i biti siguran da ste zadali pravu boju?
Kliknite na recimo ćeliju B3 da je selektirate, sada kliknite na gumb Format Painter pa potom na ćeliju B13, sada smo 100% sigurni da je boja za te dvije ćelije ista.

Adsense sponzor



Adsense sponzor



Teme vezane za ćelije u boji

- Uvjetno oblikovanje u Excelu 2003
-
Conditional formatting u Excelu 2007
-
Sortiranje ćelija u boji
-
Brojanje ćelija u boji
-
Zbrajanje vrijednosti u ćelijama u boji
-
Kako pronaći podatak u tablici i obojati ćeliju ili red bojom
- Kako obojati ćeliju nakon unosa specifičnog ili određenog teksta ili broja i pritiska na tipku ENTER
- Kako saznati određeni broj koda za specifičnu boju u VBA
- Kako obojati kao uvjet ćelije koje treba popuniti





©- 2006 - 2018 - IvanC  - Sva prava pridržana.  ic.ims.hr