În sarcinile zilnice, marcarea cu culori este o metodă populară pentru a distinge și a evidenția rapid datele esențiale. Dar, cum numărăm sau însumăm datele celulei pe baza unei anumite culori (culoarea de umplere, culoarea fontului, formatarea condiționată)? În mod implicit, Excel nu oferă o funcție directă de numărare sau însumare după culoare. Cu toate acestea, cu unele trucuri și metode indirecte, încă putem realiza acest lucru. Acest articol va explora cum să numărați sau să însumați datele după culoare.

Numărați și sumați celulele pe baza culorii de fundal

• Prin utilizarea funcției definite de utilizator
• Prin utilizarea unei funcții puternice - Kutools pentru Excel
• Folosind Filtru și SUBTOTAL

Numărați și însumați celulele în funcție de culoarea fontului

• Prin utilizarea funcției definite de utilizator
• Prin utilizarea unei funcții ușoare – Kutools pentru Excel

Numărați și însumați celulele pe baza culorii de formatare condiționată

• Prin utilizarea codului VBA
• Prin utilizarea unei funcții inteligente – Kutools pentru Excel

Video: numărați și însumați celulele în funcție de culoare

Numărați și sumați celulele pe baza culorii de fundal

De exemplu, dacă aveți o gamă de date în care valorile sunt umplute cu culori de fundal diferite, așa cum se arată în captura de ecran de mai jos. Pentru a număra sau a suma celulele pe baza unei anumite culori, Excel nu oferă o caracteristică directă pentru a număra sau a suma celulele pe baza culorii lor de fundal. Cu toate acestea, cu puțină ingeniozitate și câteva tehnici la îndemână, puteți îndeplini această sarcină. Să explorăm câteva metode utile în această secțiune.

Numărați și însumați celulele după culoarea de fundal cu funcția definită de utilizator

Aici, vă vom arăta cum să creați și să utilizați o astfel de funcție definită de utilizator pentru a rezolva această sarcină în Excel. Vă rugăm să urmați următorii pași:

Pasul 1: Deschideți editorul de module VBA și copiați codul

1. Anunturi Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. În fereastra deschisă, faceți clic Insera > Module pentru a crea un nou modul gol.
3. Apoi, copiați și inserați codul de mai jos în modulul gol.
   Cod VBA: numărați și însumați celulele pe baza culorii de fundal

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Pasul 2: Creați formule pentru a număra și a însuma celulele după culoarea de fundal

După lipirea codului de mai sus, închideți fereastra modulului, apoi aplicați următoarele formule:

• Numărați celulele pe baza culorii specifice de fundal:
  Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  notițe: În această formulă, G2 este celula de referință cu culoarea specifică de fundal pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2; FALS este folosit pentru a număra celulele cu culoarea potrivită.
  
• Sumă celulele pe baza culorii specifice de fundal:
  Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  notițe: În această formulă, G2 este celula de referință cu culoarea specifică de fundal pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2; TRUE este folosit pentru a suma celulele cu culoarea potrivită.
  

Numărați și însumați celulele după culoarea de fundal cu o funcție puternică

Pentru cei care nu sunt familiarizați cu programarea, VBA poate părea destul de complex. Aici, vom introduce un instrument puternic - Kutool pentru Excel, ict Numără după culoare caracteristica vă permite să calculați cu ușurință (număr, sumă, medie etc.) pe baza culorii de fundal în doar câteva clicuri. Impresionant, Numără după culoare caracteristica depășește doar culorile de fundal – poate, de asemenea, să diferențieze și să calculeze pe baza culorilor fontului și a formatării condiționate.

După descărcarea și instalarea Kutools pentru Excel, mai întâi, selectați intervalul de date pe care doriți să îl numărați sau să însumați celulele pe baza unei anumite culori de fundal. Apoi, navigați la Kutools Plus Și alegeți Numără după culoare.

În Numără după culoare caseta de dialog, vă rugăm să specificați operațiunile:

1. Selectați Formatare standard de la Metoda culorii lista verticală;
2. Specifica Context de la Tipul de numărare listă derulantă și puteți previzualiza rezultatele statistice pentru fiecare culoare de fundal în caseta de dialog;
3. În cele din urmă, faceți clic pe Generează raport pentru a exporta rezultatele calculate într-un nou registru de lucru.

Rezultat:

Acum veți obține un nou registru de lucru cu statistici. Vedeți captura de ecran:

Numărați și însumați celulele după culoarea de fundal cu funcția Filtru și SUBTOTAL

Presupunând că avem un tabel de vânzări de fructe așa cum se arată mai jos, și vom număra sau însumăm celulele colorate din Sumă coloana.

Pasul 1: Aplicați funcția SUBTOTAL

Selectați celule goale pentru a intra în funcția SUBTOTAL.

• Pentru a număra toate celulele cu aceeași culoare de fundal, introduceți formula:

  =SUBTOTAL(102, F2:F16)

• Pentru a suma toate celulele cu aceeași culoare de fundal, introduceți formula;

  =SUBTOTAL(109, F2:F16)

• notițe: în formulele de mai sus, 102 reprezintă numărarea valorilor numerice într-o listă filtrată, excluzând celulele ascunse; 109 reprezintă însumarea valorilor într-o listă filtrată, excluzând celulele ascunse; F2: F16 este intervalul în care se va calcula fie numărul, fie suma.
• 

Pasul 2: Filtrați celulele pe baza unei anumite culori

1. Selectați antetul tabelului și faceți clic Date > Filtru. Vedeți captura de ecran:
   
2. Apasă pe Filtru icoană  în celula antet a Sumă și faceți clic pe Filtrul de culoare și culoarea specificată pe care o veți număra succesiv. Vedeți captura de ecran:
   

Rezultat:

După filtrare, formulele SUBTOTAL numără și însumează automat celulele colorate din Sumă coloană. Vedeți captura de ecran:

Numărați și însumați celulele în funcție de culoarea fontului

Doriți să numărați sau să însumați celule în funcție de culoarea fontului în Excel? Să presupunem că aveți datele, ca în captura de ecran dată, cu celule care conțin texte în roșu, albastru, portocaliu și negru. Excel nu face acest lucru ușor în mod implicit. Dar nu-ți face griji! În această secțiune, vă vom arăta câteva trucuri simple pentru a face exact asta.

Numărați și însumați celulele pe baza culorii fontului cu funcția definită de utilizator

Pentru a număra și a suma celulele cu anumite culori de font, următoarea funcție definită de utilizator vă poate ajuta să rezolvați această sarcină. Vă rugăm să urmați următorii pași:

Pasul 1: Deschideți editorul de module VBA și copiați codul

1. Anunturi Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. În fereastra deschisă, faceți clic Insera > Module pentru a crea un nou modul gol.
3. Apoi, copiați și inserați codul de mai jos în modulul gol.
   Cod VBA: numărați și însumați celulele în funcție de culoarea fontului

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Pasul 2: Creați formule pentru a număra și a însuma celulele după culoarea fontului

După lipirea codului de mai sus, închideți fereastra modulului, apoi aplicați următoarele formule:

• Numărați celulele în funcție de culoarea specifică a fontului:
  Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  notițe: În această formulă, G2 este celula de referință cu culoarea specifică a fontului pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2.
  
• Sumă celulele în funcție de culoarea specifică a fontului:
  Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  notițe: În această formulă, G2 este celula de referință cu culoarea specifică a fontului pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2.
  

Numărați și însumați celulele pe baza culorii fontului, cu o funcție simplă

Doriți să numărați sau să însumați fără efort valorile celulelor în Excel pe baza culorii fontului? Scufundă-te în Kutools pentru Excel's Numără după culoare caracteristică! Cu acest instrument inteligent, numărarea și însumarea celulelor după culoarea specifică a fontului devine ușor. Descoperă cum Kutools vă poate transforma experiența Excel.

După descărcarea și instalarea Kutools pentru Excel, mai întâi, selectați intervalul de date pe care doriți să îl numărați sau să însumați celulele pe baza unei anumite culori de font. Apoi apasa Kutools Plus > Numără după culoare pentru a deschide Numără după culoare căsuță de dialog.

În Numără după culoare caseta de dialog, vă rugăm să specificați operațiunile:

1. Selectați Formatare standard de la Metoda culorii lista verticală;
2. Specifica Font de la Tipul de numărare listă derulantă și puteți previzualiza rezultatele statistice pentru fiecare culoare de font în caseta de dialog;
3. În cele din urmă, faceți clic pe Generează raport pentru a exporta rezultatele calculate într-un nou registru de lucru.

Rezultat:

Acum, aveți un nou registru de lucru care afișează statisticile detaliate bazate pe culoarea fontului. Vedeți captura de ecran:

Numărați și însumați celulele pe baza culorii de formatare condiționată

În Excel, puteți utiliza în mod obișnuit Formatarea condiționată pentru a aplica o anumită culoare celulelor care îndeplinesc anumite criterii, făcând vizualizarea datelor intuitivă. Dar ce se întâmplă dacă trebuie să numărați sau să însumați acele celule formatate special? Deși Excel nu oferă o modalitate directă pentru aceasta, iată modalități de a manevra această limitare.

Numărați și însumați celulele formatate condiționat cu cod VBA

Numărarea și însumarea celulelor formatate condiționat în Excel nu este simplă folosind funcțiile încorporate. Cu toate acestea, puteți îndeplini această sarcină folosind codul VBA. Să vedem cum puteți folosi VBA pentru asta:

Pasul 1: Deschideți editorul de module VBA și copiați codul

1. Anunturi Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. În fereastra deschisă, faceți clic Insera > Module pentru a crea un nou modul gol.
3. Apoi, copiați și inserați codul de mai jos în modulul gol.
   Cod VBA: numărați și însumați celulele pe baza culorii de formatare condiționată

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Pasul 2: Executați acest cod VBA

1. După lipirea codului, apăsați F5 pentru a rula acest cod, va apărea o casetă de prompt, vă rugăm să selectați intervalul de date în care doriți să numărați și să însumați celulele pe baza formatării condiționate. Apoi apasa OK, A se vedea captura de ecran:
   
2. Într-o altă casetă de solicitare, selectați o anumită culoare de formatare condiționată pe care doriți să o numărați și să o însumați și faceți clic OK buton, vezi captura de ecran:
   

Rezultat:

Acum, rezultatul, care include atât numărul, cât și suma celulelor cu culoarea de formatare condiționată specificată, va fi afișat în caseta pop-out. Vedeți captura de ecran:

Numărați și însumați celulele formatate condiționat cu o funcție inteligentă

Dacă sunteți în căutarea altor metode rapide și ușoare de numărare și însumare a celulelor formatate condiționat, Kutools pentru Excel este soluția ta de bază. Este Numără după culoare funcția poate rezolva această sarcină în doar câteva clicuri. Pătrundeți-vă pentru a descoperi eficiența și precizia pe care Kutools le poate aduce fluxului dvs. de lucru.

După descărcarea și instalarea Kutools pentru Excel, mai întâi, selectați intervalul de date pe care doriți să îl numărați sau să însumați celulele pe baza unei anumite culori de formatare condiționată. Apoi apasa Kutools Plus > Numără după culoare pentru a deschide Numără după culoare căsuță de dialog.

În Numără după culoare caseta de dialog, vă rugăm să specificați operațiunile:

1. Selectați Formatarea condițională de la Metoda culorii lista verticală;
2. Specifica Context de la Tipul de numărare lista derulantă și puteți previzualiza rezultatele statistice pentru fiecare culoare de formatare a condiției în caseta de dialog;
3. În cele din urmă, faceți clic pe Generează raport pentru a exporta rezultatele calculate într-un nou registru de lucru.

Rezultat:

Acum, aveți un nou registru de lucru care afișează statisticile detaliate bazate pe culoarea de formatare condiționată. Vedeți captura de ecran:

Articole pe aceeaşi temă: