Ugrás a tartalomra

Excel tippek: A cellák számlálása/összegzése szín szerint (háttér, betűtípus, feltételes formázás)

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

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

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


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.

Ban,-ben 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:

tippek:
  1. Az Számoljon szín szerint A funkció támogatja a cellák számlálását és összegzését is a szabványos betűszín, a háttér vagy a feltételes formázásból származó betűszín, valamint a kitöltési és feltételes formázási színek kombinációja alapján.
  2. Érdekelne ez a funkció, kérem Kattintson a letöltéshez, és 30 napig ingyenes próbaverziót kap.

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:

Megjegyzések: Ez a módszer megköveteli, hogy a színes cellák, amelyeket megszámlál, vagy összeadódja, ugyanabban az oszlopban legyenek.

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.

Ban,-ben 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.

Ban,-ben 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:

  • Ha a betűszín piros, akkor adjon vissza egy adott szöveget
  • Hogyan tudna visszaadni egy adott szöveget, ha a betűszín piros egy másik cellában, ahogy az alábbi képernyőképen látható? Ebben a cikkben bemutatok néhány trükköt néhány művelet végrehajtásához az Excel piros betűtípusa alapján.
  • Adatok szűrése több szín szerint
  • Normál esetben az Excel programban gyorsan csak egy színnel szűrheti a sorokat, de fontolgatta-e már, hogy egyszerre több színnel is sorokat szűrjen? Ebben a cikkben a gyors trükkökről fogok beszélni, amelyekkel kezelheti ezt a problémát.
  • Szín hozzáadása a legördülő listához
  • Az Excelben a legördülő lista létrehozása sokat segíthet, és néha a legördülő lista értékeit színkóddal kell ellátni a megfelelő kiválasztotttól függően. Létrehoztam például egy legördülő listát a gyümölcsnevekről, amikor az Apple-t választom, a cellát automatikusan pirosra kell színezni, ha pedig a Narancsot választom, a cellát narancssárgával lehet színezni.
  • Színezze az egyesített cellák alternatív sorait
  • Nagyon hasznos, ha egy nagy adatban eltérő színű sorokat formáz, hogy átvizsgálhassuk az adatokat, de néha előfordulhat, hogy egyesített cellák találhatók az adatokban. Hogyan tudná megoldani ezt a problémát az Excelben, ha az egyesített cellák sorait felváltva más színnel szeretné kiemelni az alábbi képernyőképen látható módon?