A napi feladatok során a színes jelölés népszerű módszer a fontos adatok gyors megkülönböztetésére és kiemelésére. De hogyan számoljuk vagy összegezzük a cellaadatokat adott szín (kitöltésszín, betűszín, feltételes formázás) alapján? Alapértelmezés szerint az Excel nem kínál közvetlen számlálási vagy szín szerinti összegzési funkciót. Ennek ellenére néhány trükkel és közvetett módszerrel ezt még elérhetjük. Ez a cikk megvizsgálja, hogyan számolhatja vagy összegezheti az adatokat szín szerint.

Számolja és összegezze a cellákat a háttér színe alapján

• Felhasználó által meghatározott funkció használatával
• Egy hatékony funkció használatával – Kutools for Excel
• A szűrő és a SUBTOTAL használatával

Számolja és összegezze a cellákat a betűszín alapján

• Felhasználó által meghatározott funkció használatával
• Egy egyszerű funkció használatával – Kutools for Excel

A cellák számlálása és összegzése a feltételes formázás színe alapján

• VBA kód használatával
• Egy intelligens funkció használatával – Kutools for Excel

Videó: Számolja és összegezze a cellákat szín alapján

Számolja és összegezze a cellákat a háttér színe alapján

Például, ha van egy adattartománya, ahol az értékek különböző háttérszínekkel vannak kitöltve, ahogy az alábbi képernyőképen látható. A cellák egy adott szín alapján történő megszámlálásához vagy összegzéséhez az Excel nem kínál közvetlen lehetőséget a cellák számlálására vagy összegzésére a háttérszínük alapján. Egy kis találékonysággal és néhány praktikus technikával azonban elvégezheti ezt a feladatot. Nézzünk meg néhány hasznos módszert ebben a részben.

Számolja és összegezze a cellákat háttérszín szerint a felhasználó által meghatározott funkcióval

Itt bemutatjuk, hogyan hozhat létre és használhat egy ilyen felhasználó által definiált függvényt a feladat megoldásához az Excelben. Kérjük, kövesse a következő lépéseket:

1. lépés: Nyissa meg a VBA-modul szerkesztőjét, és másolja ki a kódot

1. nyomja meg Alt + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. A megnyíló ablakban kattintson a gombra betétlap > Modulok új üres modul létrehozásához.
3. Ezután másolja ki és illessze be az alábbi kódot az üres modulba.
   VBA-kód: A cellák számlálása és összegzése a háttérszín alapján

   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
   

2. lépés: Hozzon létre képleteket a cellák háttérszín szerinti számlálásához és összegzéséhez

A fenti kód beillesztése után zárja be a modul ablakát, majd alkalmazza a következő képleteket:

• Számolja meg a cellákat az adott háttérszín alapján:
  Másolja vagy írja be az alábbi képletet a kívánt cellába az eredményhez. Ezután húzza le a kitöltő fogantyút, hogy további eredményeket kapjon. Lásd a képernyőképet:
  

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

  Megjegyzések: Ebben a képletben G2 a referenciacella a megfelelő háttérszínnel; $B$2:$E$12 az a tartomány, ahol meg akarja számolni a G2 színű celláinak számát; HAMIS a megfelelő színű sejtek megszámlálására szolgál.
  
• A cellák összege az adott háttérszín alapján:
  Másolja vagy írja be az alábbi képletet a kívánt cellába az eredményhez. Ezután húzza le a kitöltő fogantyút, hogy további eredményeket kapjon. Lásd a képernyőképet:
  

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

  Megjegyzések: Ebben a képletben G2 a referenciacella a megfelelő háttérszínnel; $B$2:$E$12 az a tartomány, ahol meg akarja számolni a G2 színű celláinak számát; TRUE a megfelelő színű cellák összegzésére szolgál.
  

Számolja és összegezze a cellákat háttérszín szerint egy hatékony funkcióval

Azok számára, akik nem ismerik a programozást, a VBA meglehetősen bonyolultnak tűnhet. Itt bemutatunk egy hatékony eszközt - Kutool az Excel számára, annak Számoljon szín szerint A funkció lehetővé teszi a háttérszínek egyszerű kiszámítását (számlálás, összegzés, átlag stb.) néhány kattintással. Lenyűgözően, Számoljon szín szerint A funkció túlmutat a háttérszíneken – a betűszínek és a feltételes formázás alapján is képes megkülönböztetni és kiszámítani.

Után a Kutools for Excel letöltése és telepítése, először válassza ki azt az adattartományt, amelyet meg szeretne számolni, vagy összegezze a cellákat egy adott háttérszín alapján. Ezután navigáljon ide Kutools Plus És válasszon Számoljon szín szerint.

A Számoljon szín szerint párbeszédpanelen adja meg a műveleteket:

1. választ Normál formázás tól Színmódszer legördülő lista;
2. Adja meg Háttér tól Számlálás típusa legördülő listából, és megtekintheti az egyes háttérszínekre vonatkozó statisztikai eredményeket a párbeszédablakban;
3. Végül kattintson Jelentést készít a számított eredmények exportálásához egy új munkafüzetbe.

Eredmény:

Most kap egy új munkafüzetet a statisztikákkal. Lásd a képernyőképet:

Számolja és összegezze a cellákat háttérszín szerint a Filter és SUBTOTAL funkcióval

Tegyük fel, hogy van egy gyümölcsértékesítési táblázatunk az alábbi képernyőképen látható módon, és megszámoljuk vagy összeadjuk a színes cellákat a Összeg oszlop.

1. lépés: Alkalmazza a SUBTOTAL függvényt

Válassza ki az üres cellákat a RÉSZÖSSZEG függvény megadásához.

• Az azonos háttérszínű cellák megszámlálásához írja be a következő képletet:

  =SUBTOTAL(102, F2:F16)

• Az azonos háttérszínű cellák összegzéséhez adja meg a képletet;

  =SUBTOTAL(109, F2:F16)

• Megjegyzések: a fenti képletekben, 102 reprezentálja a numerikus értékek megszámlálását egy szűrt listában, miközben kizárja a rejtett cellákat; 109 reprezentálja az értékek összegzésére egy szűrt listában, amely nem tartalmazza a rejtett cellákat; F2: F16 az a tartomány, amelyen belül a szám vagy az összeg kiszámításra kerül.
• 

2. lépés: Szűrje le a cellákat adott szín alapján

1. Válassza ki a táblázat fejlécét, és kattintson rá dátum > Szűrő. Lásd a képernyőképet:
   
2. Kattintson az Szűrő ikon  a fejléccellájában Összeg oszlopot, majd kattintson a gombra Szűrés Color és a megadott szín, amelyet egymás után számol. Lásd a képernyőképet:
   

Eredmény:

Szűrés után a SUBTOTAL képletek automatikusan megszámolják és összeadják a színes cellákat a Összeg oszlop. Lásd a képernyőképet:

Számolja és összegezze a cellákat a betűszín alapján

Szeretné a cellákat a betűszínük alapján számolni vagy összegezni az Excelben? Tegyük fel, hogy megvannak az adatok, mint az adott képernyőképen, piros, kék, narancssárga és fekete színű szövegeket tartalmazó cellákkal. Az Excel alapértelmezés szerint ezt nem teszi egyszerűvé. De ne aggódj! Ebben a részben bemutatunk néhány egyszerű trükköt, amelyek ezt megtehetik.

A cellák számlálása és összegzése a betűszín alapján a felhasználó által meghatározott funkcióval

A meghatározott betűszínnel rendelkező cellák megszámlálásához és összegzéséhez a következő felhasználói függvény segíthet a feladat megoldásában. Kérjük, kövesse a következő lépéseket:

1. lépés: Nyissa meg a VBA-modul szerkesztőjét, és másolja ki a kódot

1. nyomja meg Alt + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. A megnyíló ablakban kattintson a gombra betétlap > Modulok új üres modul létrehozásához.
3. Ezután másolja ki és illessze be az alábbi kódot az üres modulba.
   VBA-kód: A cellák számlálása és összegzése a betűszín alapján

   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
   

2. lépés: Hozzon létre képleteket a cellák számlálásához és összegzéséhez betűszín szerint

A fenti kód beillesztése után zárja be a modul ablakát, majd alkalmazza a következő képleteket:

• Számolja meg a cellákat az adott betűszín alapján:
  Másolja vagy írja be az alábbi képletet a kívánt cellába az eredményhez. Ezután húzza le a kitöltő fogantyút, hogy további eredményeket kapjon. Lásd a képernyőképet:
  

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

  Megjegyzések: Ebben a képletben G2 a referenciacella az egyeztetni kívánt betűszínnel; $B$2:$E$12 az a tartomány, ahol meg akarja számolni a G2 színű celláinak számát.
  
• A cellák összege az adott betűszín alapján:
  Másolja vagy írja be az alábbi képletet a kívánt cellába az eredményhez. Ezután húzza le a kitöltő fogantyút, hogy további eredményeket kapjon. Lásd a képernyőképet:
  

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

  Megjegyzések: Ebben a képletben G2 a referenciacella az egyeztetni kívánt betűszínnel; $B$2:$E$12 az a tartomány, ahol meg akarja számolni a G2 színű celláinak számát.
  

A cellák számlálása és összegzése a betűszín alapján egy egyszerű funkcióval

Szeretné könnyedén megszámolni vagy összeadni a cellaértékeket az Excelben a betűszín alapján? Merüljön el Kutools az Excel számára's Számoljon szín szerint funkció! Ezzel az intelligens eszközzel a cellák számlálása és összegzése adott betűszín szerint gyerekjáték lesz. Fedezze fel, hogyan Kutools átalakíthatja az Excel-élményt.

Után a Kutools for Excel letöltése és telepítése, először válassza ki azt az adattartományt, amelyet meg szeretne számolni, vagy összegezze a cellákat egy adott betűszín alapján. Ezután kattintson Kutools Plus > Számoljon szín szerint megnyitni Számoljon szín szerint párbeszédablak.

A Számoljon szín szerint párbeszédpanelen adja meg a műveleteket:

1. választ Normál formázás tól Színmódszer legördülő lista;
2. Adja meg betűtípus tól Számlálás típusa legördülő listából, és megtekintheti az egyes betűszínekre vonatkozó statisztikai eredményeket a párbeszédablakban;
3. Végül kattintson Jelentést készít a számított eredmények exportálásához egy új munkafüzetbe.

Eredmény:

Most van egy új munkafüzete, amely részletes statisztikákat jelenít meg a betűszín alapján. Lásd a képernyőképet:

A cellák számlálása és összegzése a feltételes formázás színe alapján

Az Excelben gyakran használhatja a Feltételes formázást, hogy meghatározott színt alkalmazzon bizonyos feltételeknek megfelelő cellákra, így az adatok megjelenítése intuitív. De mi van akkor, ha meg kell számolnia vagy összegeznie kell ezeket a speciálisan formázott cellákat? Bár az Excel nem kínál közvetlen módot erre, a következő módokon manőverezheti ki ezt a korlátozást.

Számolja és összegezze a feltételesen formázott cellákat VBA kóddal

A feltételesen formázott cellák számlálása és összegzése az Excelben nem egyszerű a beépített függvények használatával. Ezt a feladatot azonban végrehajthatja VBA-kód használatával. Nézzük meg, hogyan használhatod a VBA-t ehhez:

1. lépés: Nyissa meg a VBA-modul szerkesztőjét, és másolja ki a kódot

1. nyomja meg Alt + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. A megnyíló ablakban kattintson a gombra betétlap > Modulok új üres modul létrehozásához.
3. Ezután másolja ki és illessze be az alábbi kódot az üres modulba.
   VBA-kód: A cellák számlálása és összegzése a feltételes formázás színe alapján

   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
   

2. lépés: Futtassa ezt a VBA-kódot

1. A kód beillesztése után nyomja meg a gombot F5 kulcsot a kód futtatásához, megjelenik egy prompt mező, kérjük, válassza ki azt az adattartományt, amelyben feltételes formázás alapján szeretné számolni és összegezni a cellákat. Ezután kattintson OK, Lásd a képernyőképet:
   
2. Egy másik prompt mezőben válasszon ki egy adott feltételes formázási színt, amelyet meg szeretne számolni és összegezni, majd kattintson a gombra OK gomb, lásd a képernyőképet:
   

Eredmény:

Most az eredmény, amely a megadott feltételes formázási színnel rendelkező cellák számát és összegét is tartalmazza, megjelenik a kiugró mezőben. Lásd a képernyőképet:

Számolja és összegezze a feltételesen formázott cellákat egy intelligens funkcióval

Ha más gyors és egyszerű módszereket keres a feltételesen formázott cellák megszámlálására és összegzésére, Kutools az Excel számára ez a megoldás. Az Számoljon szín szerint funkció néhány kattintással megoldja ezt a feladatot. Merüljön el, és fedezze fel, hogy a Kutools milyen hatékonyságot és pontosságot tud nyújtani a munkafolyamatához.

Után a Kutools for Excel letöltése és telepítése, először válassza ki azt az adattartományt, amelyet meg szeretne számolni, vagy összegezze a cellákat egy adott feltételes formázási szín alapján. Ezután kattintson Kutools Plus > Számoljon szín szerint megnyitni Számoljon szín szerint párbeszédablak.

A Számoljon szín szerint párbeszédpanelen adja meg a műveleteket:

1. választ Feltételes formázás tól Színmódszer legördülő lista;
2. Adja meg Háttér tól Számlálás típusa legördülő listából, és megtekintheti az egyes feltételes formázási színek statisztikai eredményeit a párbeszédablakban;
3. Végül kattintson Jelentést készít a számított eredmények exportálásához egy új munkafüzetbe.

Eredmény:

Most van egy új munkafüzete, amely a feltételes formázás színe alapján részletes statisztikákat jelenít meg. Lásd a képernyőképet:

Kapcsolódó cikkek: