Am văzut multe la viața mea, dar cu cât cotinuii să găsesc alte și alte lucruri faine, ajung să dau uneori peste chestii aberante, proceduri de lucru fără nici o regulă sau după reguli rudimentare sau care aparent sunt cele mai simple pentru îndeplinirea activităților zilnice.
Aceste cazuri stupide sunt rezultanta directă a gândirii manageriale lipsite de orizontul rezultatului pe care-l poate oferi un sistem informatic.
Revenind în meandrele concretului, mi s-a întâmplat ca o fostă studentă să-mi trimită un e-mail cu o provocare de Excel. Hm… habar nu aveam cine este, dar dacă e Excel zic să arunc totuși un ochi.
Emailul era cam asa: Lucrez la o firma si am primit un tabel cu peste 1000 de randuri si sefii vor sa adun pentru coloana A, toate sumele scrise cu verde, rosu, albastru. La fel pentru coloanele B, C, D.
Iată și Excelul:
Prima idee: Sumif()
După culori? Nu avea cum să meargă, pentru că nu ai cum să pui conditia sa fie o culoare.
SumIf() in Macro? Nici acolo nu ai cum sa pui conditia formata dintr-o culoare pentru ca in macro culoarea din Excel are o valoare care nu poate fi utilizata in insumarea conditionala.
Apoi solutia a venit, greeeeu aproape de la sine: O functie:
Function SumCuloare(Culoare As Range, Casute As Range) 'Definirea variabilelor Dim rrRange As Range Dim sumColor As Long Dim rrCasute As Range 'Definirea constantelor sumColor = 0 Set rrCasute = Casute vCuloare = Culoare.Font.Color ' Suma pe culori For Each rrRange In rrCasute If rrRange.Font.Color = vCuloare Then sumColor = sumColor + rrRange.Cells.Value End If Next rrRange ' Returnare rezultat SumCuloare = sumColor End Function
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, „Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
A?! Nice?
Iata rezultatul!
Nu stiu cum ar fi fost mai simplu să se lucreze cu Excelul ăsta în așa fel încât să fie folosit un alt criteriu decât culoarea… dar bine că putem crea funcții total dinamice.
Mult mai important este, că înainte de a critica un sistem de lucru sau altul trebuie să ne amintim că tehnica este în slujba business-ului nu invers.
Spor la Work!
Excel 2007 stie sa faca pe cod de culori …
ApreciazăApreciază
Really? :)
Cum rezolvi atunci problema aia? SumIf by colors?
Asta fara macrouri? Sau cum altfel daca nu cu SumIf.
Mercic!
ApreciazăApreciază
Scuze… am confundat filtru by color cu sumif ….
ApreciazăApreciază
Așa mă gândeam și eu că ai pățit.
Incipient am luat și eu în considerare soluția creării unui Table, cu linie de totaluri și filtru pe culori, dar dacă emulezi exemplul., nu iese niciodată suma corectă, pentru că nu ai cum să faci filtru multiplu, celelalte coloane fiind de același rang fără subordonare față de prima coloană.
Faptul că Excel lucrează totuși cu culori, atunci înseamnă că sper ca această funcție pe care am creat-o ar putea fi inserată/utilizată nativ într-o versiune viitoare.
ApreciazăApreciază
De funţie mi-am dat seama cum să o creez, am făcut chiar încă una adaptată mie (countculori), dar de codul acela CSS de după funcţie nu am înţeles ce e cu el.
Şi ce ar trebui să modific pt. a însuma căsuţele în fcţie de culorile backgroundului ?
ApreciazăApreciază
CSS-ul ala este din formatarea rezultata din Windows Live Writer, deci nu are nici o treaba cu functia. Poti sa-l ignori.
ApreciazăApreciază
thanks, and keep up the great work
ApreciazăApreciază
Salut!
Multumiri pntru functie, este de mare ajutor. O folosesc foarte des :) Am insa o nelamurire. Ex: am o suma colorata in rosu pe care mi-o aduna la totalul sumelor colorate in rosu. Pana aici e perfect. Daca colorez acea suma in albastru excel 2010 nu face refresh-ul necesar pentru a-mi recalcula totalul sumelor rosii adica sa-mi scada din el suma care nu mai este culoarea rosie. exista vreo posibilitate sa automatizez refresh-ul functiei sumculoare in excel? Multumesc anticipat!
Cu respect,
Cosmin
ApreciazăApreciază
Am gasit solutia :
Function SumCuloare(Culoare As Range, Casute As Range)
Application.Volatile True
‘Definirea variabilelor
Linia 2 (Application.Volatile True) care face functia volatila ma ajuta suficient chiar daca valorile se recalculeaza abia cand salvez sheet-ul. pentru mine e suficient. bafta!
ApreciazăApreciază
:) Ce sa zic Cosmin, nu ma asteptam ca cineva sa foloseasca cu adevarat aceasta functie :) Ma bucur ca am putut fi util. Daca mai sunt ceva SF-uri de acest gen da-mi un semn si incerc sa rezolv ;)
ApreciazăApreciază
Pai se putea si fara sa faci functii. In 2007 ai posibilitatea sa filtrezi culoarea, da? atunci filtrezi dupa verde si intr-o noua coloana alaturata scrii „verde” in dreptul celulelor verzi. dupa asta filtrezi dupa galben si scrii in aceasi coloana in celulele adiacente” galben”.s.a.m.d. La final faci sumif dupa conditia coloanei unde ai scris „verde” sau „galben” sau ce ai scris. Asa ar face un contabil care nu stie si nu are nevoie sa stie functii. Plus de asta, daca trebuie sa faci insumarea pe un alt calculator unde nu ti-ai salvat VBA ce faci?
ApreciazăApreciază
Salut! De acord dar fiecare are motivele lui obiective pentru care alege o solutie sau alta. Concret mie imi foloseste pentru calculul bugetului personal. Fisierul .xlsx de buget, care contine cate un sheet pentru fiecare luna este in cloud, deci la orice calculator m-as afla pot sa-l accesez si deci am mereu acces la functie :). Alt argument ar fi ca am nevoie sa vad toate sumele din luna respectiva in acelasi timp dar cu o culoare a fontului diferita pentru a avea o privire de ansamblu fara sa fie nevoie sa fac filtre pentru a mi se actualiza totalurile daca fac stergeri sau adaugiri in sheet. in concluzie eu mi-am dorit o solutie de automatizare care sa reactioneze dupa fiecare input manual si nu doar totaluri pe culori :)
ApreciazăApreciază
Contabilu lu peste :) Nice Nick. Deci problema este automatizare si mai mult de atat e o mica problema cu fisierul pe care-l vezi in exemplu. Ideea este ca acolo scrisurile colorate sunt pe mai multe patratele deci pe coloane diferite.
De asemenea, o functie reduce timpul de operare al unui document. Nu tre sa mai faci filtre si sa scrii si apoi o alta functie. Faci direct. Apoi o apta aplicabilitate este in dinamica fisierului: se face ca datele si culorile se schimba automat. Ce faci in cazul acesta? Refaci Filtru pui din nou culori, scrii din nou text?
Alta aplicabilitate este in formarea conditionala… adica iti formatezi automat celulele in functie de valori, si apoi se schimba automat si suma insumarilor pe culori.
Huh… multe :)
ApreciazăApreciază
De acord. Solutia mea nu este dinamica.
Altfel de ce crezi ca am dat pe google cautare dupa „sumif by color function”? Pentru ca nevoile uneori sunt mai complexe si iti multumesc pentru ca ai publicat-o si pentru unul ca mine, pentru ca o folosesc deja. Intr-un mediu de productie unde seful iti da un tabel colorat si iti zice sa ii faci in 5 sec impactul, e chiar folositor.
Am vrut doar sa punctez ca in cazul in care esti un utilizator ocazional de xls si ai nevoie de o astfel de „chestie”, raspunsul trebuie cautat intai in lucrurile simple, dar in final depinde de nevoi.
Ai nevoi complexe? Solutiile sunt pe masura.
Inca o data eu iti multumesc.
ApreciazăApreciază
Multumesc si eu pentru comentarii si aprecieri. In comunitate asta inseamna recompensa pentru o anumita munca.
In viata reala ai de departe dreptate. De multe ori cautam workaround-uri pentru a ne rezolva cat mai rapid problemele. Alteori cautam pe Google si dam peste tot felul de chestii care de care mai inutile :)
De aceea sunt bune comunitatile specializate in domeniul unei tehnologii sau alteia.
Spor la treaba si daca mai aveti exemple ciudate va rog sa ma provocati :) Sunt in cautare de probleme pentru a gasi solutii :)
ApreciazăApreciază
Great info! I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
ApreciazăApreciază
am si eu o intrebare: ce functie utilizezi, si mai ales cum, in conditia in care vrei ca o celula sa se coloreze atomat intr-o anumita culoare daca o conditie pusa este adevarata, sau falsa?
ApreciazăApreciază
Andreea, pentru problema ta trebuie sa utilizezi formatarea conditionata.
Aici aveti un tutorial complet: http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html
Sau video aici: http://www.youtube.com/watch?v=UIWFJR6y4ic
ApreciazăApreciază
Foarte folositor dar la mine apare o problema. Cum pot sa il fac sa adune si sa afiseze rezultatul cu 2 zecimale? Multumesc
ApreciazăApreciază
Cu parere de rau mentionez ca la mine nu functioneaza:( . Daca adun pe orizontala suma iese zero, daca adun pe verticala suma iese 1, indiferent de valorile in celule.
Function SumCuloare(Culoare As Range, Casute As Range)
‘Definirea variabilelor
Dim rrRange As Range
Dim sumColor As Long
Dim rrCasute As Range
‘Definirea constantelor
sumColor = 0
Set rrCasute = Casute
vCuloare = Culoare.Font.Color
‘ Suma pe culori
For Each rrRange In rrCasute
If rrRange.Font.Color = vCuloare Then
sumColor = sumColor + rrRange.Cells.Value
End If
Next rrRange
‘ Returnare rezultat
SumCuloare = sumColor
End Function
Am scris ceva gresit?
ApreciazăApreciază
Function SumColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell
SumColor = vResult
End Function
functia asta merge 100% cu backgroud-ul la celula colorat
Spor
ApreciazăApreciază
Cristian da-mi un e-mail cu fisierul tau pe valygreavu@hotmail.com
ApreciazăApreciază