Excel – Pagina 5 – Valy Greavu

VBA Function CellColor

Function CellColor(ColorRef As Range, cArea As Range, ColorType As Integer, Operation As Integer) As Integer
'CellColor function is designated to perform specific operations on range of cell by font or background color
    ' Parameters:
        ' ColorRef - Reference cell that containing specific format
        ' cArea - Area to be analyzed
        ' ColorType: 0 - Font; 1 - Background
        ' Operation: 0 - Count; 1 - Sum

'Defining variables
Dim rrcArea As Range
Dim rrRange As Range
nCells = 0
Set rrcArea = cArea


Select Case ColorType
    Case 0 ' Font
            vColor = ColorRef.Font.Color
            Select Case Operation
                Case 0 ' Count
                    For Each rrRange In rrcArea
                        If rrRange.Font.Color = vColor Then
                             nCells = nCells + 1
                        End If
                    Next rrRange
                Case 1 ' Sum
                    For Each rrRange In rrcArea
                        If rrRange.Font.Color = vColor Then
                             nCells = nCells + rrRange.Cells.Value
                        End If
                    Next rrRange
            End Select
    Case 1 ' Background
            vColor = ColorRef.Interior.Color
            Select Case Operation
                Case 0 ' Count
                    For Each rrRange In rrcArea
                        If rrRange.Interior.Color = vColor Then
                             nCells = nCells + 1
                        End If
                    Next rrRange
                Case 1 ' Sum
                    For Each rrRange In rrcArea
                        If rrRange.Interior.Color = vColor Then
                             nCells = nCells + rrRange.Cells.Value
                        End If
                    Next rrRange
            End Select
End Select

' Return results
CellColor = nCells

End Function

If you want to use this function for conditional formatted cells, you must save and call macro as UDF format.

Alternate way of counting or sum values from conditional formatting cells are Countif() and Sumif() on the same condition as format.

I hope it will be helpful to someone!

Acest articol este un fel de continuare a articolului în care a fost explicată funcția OFFSET(). Pentru a înțelege mai ușor acest articol este recomandat să descărcați fișierele din imaginile prezentate.

Una din problemele interesante în elaborarea rapoartelor economice dinamice constă în realizarea de filtre dinamice pentru date. Aplicabilitatea filtrelor dinamice are sens doar în momentul în care datele sunt clasificate pe diferite categorii, categorii care pot fi uneori clasificate pe subcategorii.

Cerința problemei

Extragerea stocului din tabelul de stocuri pe baza categoriei și a denumirii produsului. Denumirea produsului trebuie să poată fi selectată doar în raport cu categoria selectată.

Pașii de rezolvare

Având în vedere modul complex de funcționare a funcțiilor următoare, pentru rezultate corecte se recomandă ca tabelul de date să fie sortat după Categorie. În cazul în care avem mai multe valori interdependente acestea trebuie sortate pe niveluri de subordonare corecte semnificației acelor date.

În tabel categoria de produs poate apărea pe mai multe linii. În acest context, pentru a putea implementa un filtru dinamic, avem nevoie de valoarea unică a categoriei.

Pentru a obține valoarea unică a categoriei, am creat zona categorii K3:K25 care conține o funcție INDEX() de extragere a valorii unice.

În celula K4 am inserat formula:

=IFNA(INDEX(tblStoc[Categorie];MATCH(0;COUNTIF($K$3:K3;tblStoc[Categorie]);0));””)

care permite indexarea valorilor de pe tblStoc, coloana Categorie în funcție de poziția unică a unui element, determinat pe egalitatea lui CountIf față de categoriile de mai sus cu 0. Este în fapt un artificiu prin care se păstrează doar unicatele din listă. IFNA() afișează un spațiu liber, în celulă, dacă în tabel sunt mai puține categorii decât formulele copiate în jos. Formula din K4 este o funcție array, deci trebuie introdusă cu CTRL+SHIFT+ENTER.

Pasul următor a fost definirea zonei de celule de categorii. Se selectează o zonă mai mare de celule de pe coloana K după care se apasă combinația de taste Ctrl+Shift+F3

După care se apasă Ok și vom putea apela blocul de celule după numele Categorii.

Pentru a ne asigura că numele este creat corect putem apăsa combinația Ctrl+F3 pentru afișarea numelor blocurilor de celule din fișierul curent.

Pentru definirea listei pentru celula de categorie (I3), se accesează meniul Data, DataValidation, Allow: List și se trece la sursă numele blocului de celule:

Pentru afișarea doar a produselor din categoria selectată la I3 va trebui să folosim o formulă OFFSET care să indexeze conținutul tabelului de date pe baza valorilor din categoria selectată.

Una din limitele Excel-ului este dată de faptul că în casetele de validare a datelor nu se pot folosi nume de tabele și coloane din tabelele de date. Poate în edițiile viitoare va fi rezolvată această problemă. De exemplu în versiunea Excel 2016 se poate scrie la sursa în Data Validation numele unui tabel și a unei coloane asociate, se selectează corelat zona de date, dar nu este permisă validarea formulei introduse în acest format.

Revenind, pașii pe care trebuie să-i parcurgem pentru a putea selecta doar produsele din categoria selectată, accesăm celula I4, meniul Data, Data Validation, Allow, List iar la sursă se va scrie formula:

=OFFSET($A$2;MATCH(I3;$C$3:$C$10;0);1;COUNTIF($C$3:$C$10;I3);1)

în care:

– $A$2 – reprezintă celula de start a tabelului cu date care va fi indexat;

– MATCH(I3;$C$3:$C$10;0) – reprezintă linia din tabel în care se regăsește prima înregistrare din categoria specificată în I3. (Reamintesc faptul că tabelul trebuie să fie sortat după categorie);

– 1 – reprezintă numărul coloanei față de $A$2 de pe care se vor extrage datele. În cazul prezentat denumirea produsului este pe a doua coloană ceea ce înseamnă prima coloană față de referință;

– COUNTIF($C$3:$C$10;I3) – reprezintă numărul de linii aferente produselor din categoria selectată. Acest număr specifică parametrul opțional height al funcției Offset. În cazul tabelului prezentat pentru produsele din categoria Panificație vom obține un vector (array) cu 3 înregistrări;

– 1 de final reprezintă lățimea vectorului, în cazul nostru o coloană.

În cazul în care doriți să studiați mai în amănunt modul în care a fost creat acest exemplu, puteți descărca fișierul de aici.

Un exemplu mai elaborat

Un alt exemplu de problemă este aceea în care dorim mai multe filtre interdependente.

Cerința este de a realiza un raport dinamic pentru o sursă de date de vânzări care să permită pe baza mai multor selecții, afișarea dinamică a unui tabel cu date.

Pentru câteva explicații, deschideți fișierul Filtre Dinamice. Atenție nu puteți lucra cu acest fișier on-lie. Trebuie descărcat local. Fișierul nu conține macros.

În foaia de calcul DateVanzari este stocată sursa de date. Această sursă este sortată după Continent, Tară, Categorie și nume Produs.

În foaia de calcul DateUnice, care în practică poate fi ascunsă, am implementat un mecanism de identificare a valorilor unice, specifice filtrelor selectate. De exemplu, dacă se va selecta un anumit continent pe coloana de Țări, vor apărea toate țările specifice acelui continent, corespondent fiecărei înregistrări din tabelul sursă. Pentru a stabili unicitatea țărilor pe coloana Tara am extras valorile unice înregistrărilor de pe coloana Țări. La fel am procedat și cu coloanele Categorii/Categorie.

Pasul următor a fost atribuirea numelor blocurilor de celule pentru a le folosi pentru filtrele din foaia de calcul Raport.

Formulele din DateUnice sunt combinații de Offset și Index/Match, asemănătoare celor explicate anterior. Limitele acestui model sunt date de faptul că nu putem folosi mai mult de 3 coloane imbricate. De exemplu dacă dorim să introducem și o coloană de Produse/Produs vom observa că Excelul oferă și alte valori decât cele specifice categoriei de produs. Aceeași problemă apare și în versiunea Excel 2016.

În zona de raport am implementat Data Validation pentru celulele corespondente continentului, țării și categoriei, după care am indexat dinamic datele din tabel pentru afișarea lor și am folosit formatarea condițională pentru afișarea dinamică a liniilor de raport și un simplu IF pentru determinarea poziției liniei de total.

Un must have pentru un raport ar fi acela de generare a unor grafice dinamice, genul celui din imagine.

Astfel de grafice pot fi fezabile doar pentru același număr de linii rezultat.

O soluție alternativă, mai simplă, dar cu neajunsurile sale, la această problemă, este utilizarea tehnicilor de Advanced filter.

Sper să fie util cuiva. Vă rog să nu ezitați să dați comentarii acestui articol în cazul în care aveți metode mult mai utile sau ușoare de filtrare avansată sau dacă ați descoperit erori în acest articol. Vă mulțumesc.

În urma publicării articolului trecut legat de transformarea numerelor în litere în Excel, un mare specialist în Excel, Ciprian Stoian, membru al comunității DrExcel.ro și ITLearning.ro mi-a trimis o variantă actualizată a formulei care poate fi folosită fără tabele de căutare și funcții VLOOKUP(). Provocarea a fost legată de idea de a implementa această funcționalitate în listele de tip SharePoint, care se poate realiza prin coloane calculate în mod nativ sau xPath pe formularele InfoPath.

Problema coloanelor calculate în SharePoint este dată de limitarea formulelor la 1024 de caractere, așa că am folosit formula lui Ciprian (vezi imaginea) și am descompus-o în 3 coloane calculate diferite (Bani, SuteZeci, SuteMii) a căror rezultat l-am reconcatenat într-o coloană calculată cu LitereTotal.

Noua versiune, pentru care am obținut acordul lui Ciprian, a fișierului Excel incluzând ambele variante poate fi descărcat de aici. Din punctul meu de vedere formula lui Ciprian este mai bună decât cea propusă inițial de mine pentru că funcționează oarecum diferit pe partea de calcul matematic a zecimalelor și este mai scurtă, deci mai ușor de memorat. ”Marele meu merit” este că am reușit să-l provoc! Mai trebuie să vă amintesc faptul că Ciprian a fost cel care a propus cea mai funcțională soluție pentru problema cu combinațiile unice între mulțimi.

Mai jos găsiți un model de implemetare adaptată a formulei lui Ciprian în format funcțional pentru SharePoint din Office 365.

Pentru cei care doresc să dezvolte astfel de formulare citiți înainte seria de articole despre business forms. Pe lângă formula în sine apare problema completării on-line a formularului. Câmpurile calculate nu apar pe formularele de date Excel, ci apar abia după ce a fost completat formularul. Poate fi deranjant pentru cineva care are de completat mai multe date din cauză ca trebuie să deschidă și apoi să printeze formularul. Pentru a rezolva problema am creat un secondary data connection către lista de OP-uri care să fie declanșată la modificarea sumei. Utilizatorul scrie suma în caseta dedicată, ceea ce înseamnă change. După change, formularul trimite datele în listă (sursa Main), pasul 2 este interogarea pe secondary și apoi setarea formulei calculate de litere pe formularul curent. Asta se traduce prin a salva în timp real.

Cineva m-a întrebat de ce fără VBA?

Pentru că VBA-ul chiar dacă este excelent de bun și util, nu este suportat în toate mediile. În SharePoint poți introduce date în liste de date asemănător Excelului și poți folosi coloane calculate pentru combinarea valorilor. Dar nu poți folosi toate funcțiile Excel și nici pe departe VBA. De asemenea, în Excel Services (aplicațiile de editare on-line a fișierelor Excel) nu sunt permise macrourile VBA, din rațiuni de securitate. Pentru mine este suficient să caut metode, sau prieteni dispuși să-și își aloce timp pentru a ajuta. Nu în ultimul rând, consider că utilizatorii obișnuiți caută soluții simple la probleme complicate. Iar pentru mulți VBA-ul este deja o soluție suficient de complexă. Sigur uneori chiar nu se poate fără VBA, vezi aici propunerea mea pentur formula de însumare a unor celule Excel pe bază de culori.

Dacă mai aveți probleme interesate de Excel nu exitați să mă contactați sau să contactați forumurile

Sper să vă fie util! Mulțumim Ciprian!

Etichetă: Excel

CellColor() custom VBA function in Excel

VBA Function CellColor

Filtre dinamice sau interdependente în Excel cu Data Validation

Transformare numere in litere in Excel si SharePoint