Stupid work procedures –> beautiful solutions: SUMIF By colors


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:

image

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!

image 

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!

Anunțuri

22 de gânduri despre “Stupid work procedures –> beautiful solutions: SUMIF By colors

    • 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ă

  1. 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ă

  2. 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ă

    • 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ă

      • :) 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ă

  3. 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ă

  4. 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ă

  5. 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ă

    • 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ă

  6. 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ă

  7. 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ă

  8. 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ă

  9. 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ă

  10. 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ă

Lasă un răspuns

Completează mai jos detaliile tale sau dă clic pe un icon pentru a te autentifica:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s