Post despre Excel, culori, numere. Plicticos deci!

Am primit ieri o cerere de ajutor de la un prea bun şi vechi prieten. Şi cum era vorba şi de o problemuţă interesantă, normal că n-am dormit toată noaptea până nu i-am dat de cap.

Despre ce este vorba? Pe scurt, se dau într-un sheet Excel 4.458.329 de celule colorate în 4295 de culori diferite. Din anumite cauze, nu intrăm în detalii, dacă aşa se dau aşa se primesc şi aşa se luptă cu ele. Celulele mai au şi cifre/ numere prin ele ca să arate într-un mare fel. Cam ca mai jos:

Acuma, amicul neavând ce face (el sau şeful lui, nu spun cine mai exact vă daţi voi seama!) ar cam trebui să afle, câte celule de culoare roşie/galbenă/mov/kaki/else  sunt şi care este suma tuturor celulelor de culoare roşie/galbenă/mov/kaki/else. Nu uitaţi avem un sheet cu 4.458.329 de celule colorate în 4295 de culori diferite. Altfel spus să găsească o metodă/formulă/ceva cu care să poată aduna celule bazându-se/folosindu-se de culoarea lor.

Este că-i tare funny?

Cum ar fi, hocus-pocus, din doi timpi şi trei mişcări să ajungă la rezultatele de mai jos:

respectiv,

Şi mie mi-a plăcut ideea, aşa că am luat Excelu’ la scărmănat, ferm convins că la câte ştie el să facă, astea pentru el sunt mici copilării. Însă după vreo jumătate de oră am descoperit că nu-i deloc aşa.  Şi-am trecut la next level, Google. Și-a mai trecut vreo jumătate de oră. Şi nişte teste. Şi nişte nervi. Şi apoi gata. Am găsit. Ceva.

(Dacă nu te-ai plicitisit până acum şi chiar vrei să afli cum se face,  poţi să citeşti în continuare. Dacă ai adormit, somn uşor, atenţie cu sforăitul că te aud colegii de birou!!, dacă deja ai închis pagina şi-ai trecut la altceva, o să mori în beznă, oricum).

Cu puţin cod (găsit nu făcut de mine, recunosc) treaba asta este foarte simplă. Aşadar:

1. deschideți fişierul Excel în care aveţi ceva treabă.
2. apăsaţi Alt+F11 (sau după plac Developer/Visual Basic) din meniul de sus alegeți Insert/Module.
3. inseraţi cu atenţie în fereastra nou deschisă codul de mai jos (nu uita un Alt+Q înainte de închiderea ferestrei):

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

Dim rCell As Range

Dim lCol As Long

Dim vResult

lCol = rColor.Interior.ColorIndex

    If SUM = True Then

       For Each rCell In rRange

        If rCell.Interior.ColorIndex = lCol Then

                vResult = WorksheetFunction.SUM(rCell) + vResult

        End If

       Next rCell

    Else

        For Each rCell In rRange

        If rCell.Interior.ColorIndex = lCol Then

                vResult = 1 + vResult

        End If

       Next rCell

End If

ColorFunction = vResult

End Function

Apoi, cu nedisimulată încredere dar şi profundă emoţie în buricele deştelor, tastaţi lin şi cu încredere următoarea formulă

=colorfunction(celuladesprecarevremsăaflămcevauldoritdeşefulsă,toatecele4.458.329decelule colorate,true)ca să aflaţi suma tuturor celulelor de culoare x sau =colorfunction(celuladesprecarevremsăaflămcevauldoritdeşefulsă,toatecele4.458.329decelule colorate), v-aţi prins aceeaşi formulă ca mai sus dară fără true la final pentru a descoperi America câte sunt ele pictate şi aranjate frumos.

Adică, mai simplu şi colorat ca mai sus:

sau,

Merge? Hmm, nu? V-am zis că şmecheria merge doar pe Microsoft  Excel 2007? (pardon, pe asta am testat-o eu). Tot nu merge? Sigur ai respectat paşii de mai sus?….. Aaaaa, acum merge?

Ok, atunci să-i mulţumim şi celui care a scris răndurile de cod de mai sus şi şefului amicului pe care-l mănâncă undeva şi visează prea multe noaptea – NU şi amicului care o să dea o bere că l-am scos şi de ce nu chiar şi bătrânului Bill că fără el noi tare mult timp liber mai aveam.

PS: Să vă prind eu pe vreunul care găseşte/ştie o metodă mai uşoară pentru cele de mai sus şi nu o strigă în gura mare, mai jos, da?