Cookie-urile ne ajuta sa oferim serviciile noastre. Prin utilizarea serviciilor noastre, sunteți de acord cu utilizarea de cookie-uri.
Sfat: alte limbi sunt traduse de Google. Puteți vizita English versiune a acestei legături.
Contul meu
x
or
x
x
Inregistreaza-te
x

or

Cum de a număra și suma celulelor pe baza culorii de fundal în Excel?

Presupunând că aveți o gamă de celule cu diferite culori de fundal, cum ar fi roșu, verde, albastru și așa mai departe, dar acum trebuie să numărați câte celule din acea gamă au o anumită culoare de fundal și să însumeze celule colorate cu aceeași culoare . În Excel, nu există o formulă directă pentru a calcula suma și numărul de celule de culoare, aici vă voi prezenta câteva modalități de a rezolva această problemă.

Count și Sum cells pe baza culorii specifice de umplere de către Filter și SUBTOTAL

Count și Sum cells pe baza culorii specifice de umplere prin funcția GET.CELL

Count și sum cells pe baza culorii specifice de umplere cu funcția definită de utilizator

Count și Sum cells bazate pe culori specifice de umplere (sau colorare condiționată de formatare) cu Kutools pentru Excel

Un clic pentru a număra, suma și celulele medii colorate în Excel

Cu cei excelenți Numărați după culoare caracteristică a Kutools pentru Excel, aveți posibilitatea să numărați rapid, suma și celulele medii după culoarea specificată de umplere sau culoarea fontului, cu un singur clic în Excel. În plus, această caracteristică va afla, de asemenea, valorile maxime și minime ale celulelor prin culoarea de umplere sau culoarea fontului. Full Feature Free Trial 60-day!
numărul anunțurilor după culoarea 2


Numără și sortează celule colorate prin Filtru și SUBTOTAL

Presupunând că avem o masă de vânzare a fructelor ca în imaginea de mai jos prezentată și vom număra sau suma celulelor colorate din coloana Sumă. În această situație, putem filtra coloana Cantitate după culoare și apoi numărați sau filtrați sumele colorate cu ajutorul funcției SUBTOTAL cu ușurință în Excel.

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

  1. Pentru a număra toate celulele cu aceeași culoare de fundal, introduceți formula = SUBTOTAL (102, E2: E20);
  2. Pentru a rezuma toate celulele cu aceeași culoare de fundal, introduceți formula = SUBTOTAL (109, E2: E20);


notițe: În ambele formule, E2: E20 este coloana Sumă care conține celulele colorate și le puteți modifica după cum doriți.

2. Selectați antetul tabelului și faceți clic pe Data > Filtru. Vedeți captura de ecran:

3. Dați clic pe pictograma Filtru în celula antet a coloanei Sumă și faceți clic pe Filtrul de culoare și culoarea specificată veți conta treptat. Vedeți captura de ecran:

După filtrare, ambele formule SUBTOTAL numără și sortează automat toate celulele colorate filtrate în coloana Sumă. Vedeți captura de ecran:

notițe: Această metodă presupune că celulele colorate pe care le veți număra sau sumele se află în aceeași coloană.


Count sau Sum celule colorate prin funcția GET.CELL

În această metodă, vom crea o gamă numită cu funcția GET.CELL, obțineți codul de culoare al celulelor și apoi numărați sau suma prin codul de culoare cu ușurință în Excel. Procedați după cum urmează:

1. Clic Formule > Definiți numele. Vedeți captura de ecran:

2. În caseta de dialog New Name (Nume nou), faceți clic pe imaginea de mai jos:
(1) Introduceți un nume în caseta Nume;
(2) Introduceți formula = GET.CELL (38, Sheet4! $ E2) în caseta Se referă la (nota: în formula, 38 înseamnă returnarea codului celular și Sheet4! $ E2 este prima celulă din coloana Sumă, cu excepția antetului din coloană pe care trebuie să îl modificați pe baza datelor din tabel.)
(3) Faceți clic pe OK butonul.

3. Acum adăugați o nouă coloană Culoare direct la tabela originală. Apoi tastați formulele = NumColor și trageți mânerul Completare automată pentru a aplica formula la alte celule din coloana Culoare. Vedeți captura de ecran:
notițe: În formula, NumColor este intervalul numit specificat în primii pași 2. Trebuie să îl schimbați la numele specificat setat.

Acum, codul de culoare al fiecărei celule din coloana Sumă revine în coloana Culoare. Vedeți captura de ecran:

4. Copiați și listați culoarea de umplere într-un interval gol în foaia de lucru activă și introduceți formulele de lângă ea, după cum se arată în imaginea de mai jos:
A. Pentru a număra celulele după culoare, introduceți formula = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Pentru a sinteti celulele după culoare, introduceți formula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

notițe: În ambele formule, $ F $ 2: $ F $ 20 este coloana Culoare, NumColor este intervalul specificat numit, $ E $ 2: $ E $ 20 este Suma Coloană și le puteți modifica după cum doriți.

Acum veți vedea că celulele din coloana Sumă sunt contorizate și sumate după culorile lor de umplere.


Count și sum cells pe baza culorii specifice de umplere cu funcția definită de utilizator


Dacă presupunem că celulele colorate se împrăștie într-un interval, cum arată imaginea de mai jos, ambele metode de mai sus nu pot conta sau sinteti celulele colorate. Aici, această metodă va introduce o VBA pentru a rezolva problema.

1. Țineți apăsată tasta ALT + F11 chei și se deschide Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în fereastra Module.

VBA: Numărătoare și suma celulelor pe baza culorii de fundal:

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

3. Apoi salvați codul și aplicați următoarea formulă:
A. Numărați celulele colorate: = Colorfunction (A, B: C, FALSE)
B. Sumați celulele colorate: = Colorfunction (A, B: C, TRUE)

Notă: în formulele de mai sus, A este celula cu culoarea de fond particulară pe care doriți să o calculați și suma și B: C este intervalul celular în care doriți să calculați numărul și suma.

4. Faceți următoarea captură de ecran, de exemplu, introduceți formula= Colorfunction (A1, A1: D11, FALSE) pentru a număra celulele galbene. Și folosiți formula = Colorfunction (A1, A1: D11, TRUE) pentru a rezuma celulele galbene. Vedeți captura de ecran:

5. Dacă doriți să numărați și să însumați alte celule colorate, repetați pasul 4. Apoi veți obține următoarele rezultate:


Count și Sum cells bazate pe culori specifice de umplere cu Kutools pentru Excel

Cu funcția definită de utilizator mai sus, trebuie să introduceți formula una câte una, dacă există o mulțime de culori diferite, această metodă va fi obositoare și consumatoare de timp. Dar dacă aveți Kutools pentru Excel'S Numărați după culoare utilitate, puteți genera rapid un raport al celulelor colorate. Puteți nu numai să numărați și să însumați celulele colorate, ci și să obțineți valorile medii, max și min din gama colorată.

1. Selectați intervalul pe care doriți să îl utilizați și dați clic pe Kutools Mai mult > Numărați după culoare, a se vedea captura de ecran:

2. Și în Numărați după culoare caseta de dialog, vă rugăm să faceți cum se arată mai jos:
(1) Selectați Formatare standard de la Metoda de culoare lista verticală;
(2) Selectați fundal de la Tipul de contorizare lista verticală.
(3) Faceți clic pe butonul Generați raport.

Kutools pentru Excel - Include mai mult decât instrumente 300 la îndemână Excel. Full feature free trial 60-day, fără card de credit necesar! Ia-l acum!

notițe: Pentru a număra și a însuma celule colorate cu o anumită culoare de formatare condițională, selectați Formatarea condițională de la Metoda de culoare derulați în lista de dialog de mai sus sau selectați Formatare standard și condiționată din lista derulantă pentru a număra toate celulele umplută cu culoarea specificată.

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


Articol asociat:

Cum de a număra / suma celulelor pe baza culorilor de fonturi din Excel?


Demo: Count și suma celulelor bazate pe fundal, formatare condiționată color:

În acest videoclip, Kutools și tab - ul Kutools Mai mult sunt adăugate de către Kutools pentru Excel. Dacă este necesar, faceți clic pe aici să aveți o încercare gratuită 60-zi fără limitări!



Instrumente de productivitate recomandate

Tab-ul Office

aur star1 Aduceți file la îndemână Excel și alte programe Office, la fel ca Chrome, Firefox și noul Internet Explorer.

Kutools pentru Excel

aur star1 Uimitor! Sporiți productivitatea în minute 5. Nu aveți nevoie de abilități speciale, cu excepția a două ore în fiecare zi!

aur star1 300 Caracteristici noi pentru Excel, Make Excel mult mai ușor și mai puternic:

  • Mergeți celule / rânduri / coloane fără a pierde date.
  • Combină și consolidează mai multe coli și foi de lucru.
  • Comparați intervale, copiați mai multe intervale, convertiți textul în data, conversia unității și monedelor.
  • Numărați după culori, subtotalări pentru pagini, sortare avansată și filtru super,
  • Mai mult Selectați / Inserați / Ștergeți / Text / Format / Link / Comment / Workbooks / Foi de lucru Instrumente ...

Ecranul lui Kutools pentru Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    AvGravy · 5 years ago
    Thank you so much! This is genius, thanks for the help!
  • To post as a guest, your comment is unpublished.
    RAMON · 5 years ago
    I applied the formula but the cell said NAMES? what I need to fix
    • To post as a guest, your comment is unpublished.
      skyyang · 5 years ago
      Hello RAMON, maybe you didn’t copy the above code into the Module. You must copy the code into the Module first and save it, then apply the formulas. You can try it. If it doesn’t help you, please let me know.
      • To post as a guest, your comment is unpublished.
        Mike F · 2 years ago
        Skyyang,

        If I email you a document can you help me with it?

        Mike
      • To post as a guest, your comment is unpublished.
        Alexandra · 5 years ago
        Hello, It worked the first time, but now, when I have to add a cell to the sum... I tried introducing again the code, saving, writing the formula again... it says "name?"
        • To post as a guest, your comment is unpublished.
          Daria · 5 years ago
          I'm having the same problem -- worked beautifully at first, but getting the NAME error after adding another cell. Have tried to delete module and add again, re-enter the function...no joy.
          • To post as a guest, your comment is unpublished.
            Gary_9991 · 4 years ago
            You might need to enable Marcos again. Should flash up at the top of the screen depending on version of excel. :)
  • To post as a guest, your comment is unpublished.
    Sankar Narayanan · 5 years ago
    Hi, getting error. complie error, ambiguous error is coming.
  • To post as a guest, your comment is unpublished.
    David Coe · 5 years ago
    This is great tool, thanks for sharing! I do have one question: I noticed that the macro formula does not update itself when you change a cell color from one color to another. Is there a way to get it to automatically update when cell colors change? If I double click on the cell with the addition formula and press enter, it updates, but I have a sheet with many of these cells and don't want to have to manually update it each time.
    • To post as a guest, your comment is unpublished.
      Louise · 5 years ago
      try CTL+ALT+f9 it will update all cells with addition formulas at once. Not quite automatic but at least better than having to click on each cell with addition formula individually.
    • To post as a guest, your comment is unpublished.
      Happiqiu · 5 years ago
      Hi, i have tried. and the worksheet just needs an "Enter" No matter which cell. So, after coloring your cell, just type a memo on the colored cells and "enter"
    • To post as a guest, your comment is unpublished.
      skyyang · 5 years ago
      Thank you for your reply, I’m sorry, under the current situation, the VBA code can’t solve the question that you pointed out, you need to update them manually.
      • To post as a guest, your comment is unpublished.
        Jako · 2 years ago
        I have also found that using the format painter to update the cell color will cause the formulas to work as expected. Still not perfect.
  • To post as a guest, your comment is unpublished.
    Shankee · 5 years ago
    Amazing,thanks a lot