Tegyük fel, hogy gyümölcs értékesítési táblázatunk van, az alábbi képernyőképen, és megszámoljuk vagy összegezzük az Összeg oszlop színes celláit. Ebben a helyzetben szűrhetjük az Összeg oszlopot szín szerint, majd a szűrt színes cellákat a SUBTOTAL függvény segítségével könnyen számolhatjuk vagy összegezhetjük az Excelben.

1. Válassza ki az üres cellákat a SUBTOTAL függvény megadásához.

1. Ha meg szeretné számlálni az összes cellát ugyanazzal a háttérszínnel, kérjük, adja meg a képletet = RÉSZSZINT (102, E2: E20);
2. Az összes azonos háttérszínű cella összegzéséhez adja meg a képletet = RÉSZSZINT (109, E2: E20);

Megjegyzések: Mindkét képletben az E2: E20 az Összeg oszlop, amely a színes cellákat tartalmazza, és szükség szerint megváltoztathatja őket.

2. Válassza ki a táblázat fejlécét, majd kattintson a gombra dátum > Szűrő. Lásd a képernyőképet:

3. Kattintson a Szűrő ikonra  az Összeg oszlop fejléc cellájában, és 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:

Szűrés után mindkét SUBTOTAL képlet automatikusan összeszámolja és összegezi az Összeg oszlop összes szűrt színcelláját. 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.

Egy kattintás az Excel színes celláinak számolásához, összegezéséhez és átlagához

A kiváló Számoljon szín szerint jellemzője Kutools az Excel számára, gyorsan megszámolhatja, összegezheti és átlagolja a cellákat megadott kitöltési vagy betűszín szerint, egyetlen kattintással az Excel programban. Ezenkívül ez a szolgáltatás a cellák maximális és min értékét is kitalálja a kitöltési vagy a betűszín alapján.

Ebben a módszerben létrehozunk egy megnevezett tartományt a GET.CELL függvénnyel, megkapjuk a cellák színkódját, majd az Excelben könnyen megszámoljuk vagy összeadjuk a színkódot. Kérjük, tegye a következőket:

1. Kattints képletek > Definiálja a nevet. Lásd a képernyőképet:

2. Az Új név párbeszédpanelen tegye a következőket:
(1) Írjon be egy nevet a Név mezőbe;
(2) Írja be a képletet = GET.CELL (38, 4. lap! $ E2) a Hivatkozások mezőbe (megjegyezni: a képletben, 38 azt jelenti, hogy adja vissza a cella kódot, és 4. lap! $ E2 az Összeg oszlop első cellája, kivéve az oszlopfejlécet, amelyet a táblázat adatai alapján meg kell változtatnia.)
(3) Kattintson a gombra OK gombot.

3. Most adjon hozzá egy új Szín oszlopot közvetlenül az eredeti táblához. Ezután írja be a képletet = NumColor , és húzza az AutoFill fogantyút a képlet alkalmazásához a Szín oszlop többi cellájára. Lásd a képernyőképet:
Megjegyzések: A képletben NumColor a megnevezett tartomány, amelyet az első 2 lépésben adtunk meg. Meg kell változtatnia a megadott névre.

Most az Összeg oszlop egyes celláinak színkódja visszatér a Szín oszlopba. Lásd a képernyőképet:

4. Másolja és sorolja fel a kitöltési színt egy üres tartományba az aktív munkalapon, és írja be a képleteket mellé, az alábbi képernyőkép szerint:
A. A cellák szín szerinti megszámításához adja meg a képletet = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. A cellák szín szerinti összegzéséhez adja meg a képletet = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Megjegyzések: Mindkét képletben $ F $ 2: $ F $ 20 a Szín oszlop, NumColor a megadott megnevezett tartomány, $ E $ 2: $ 20 USD az Összeg oszlop, és szükség szerint módosíthatja őket.

Most látni fogja, hogy az Összeg oszlop cellái megszámlálódnak és összeadódnak kitöltési színük szerint.

Ha feltételezzük, hogy a színes sejtek egy tartományban szóródnak, amint az az alábbi képernyőképen látható, mindkét fenti módszer nem számolhatja vagy összegezheti a színes cellákat. Itt ez a módszer bevezet egy VBA-t a probléma megoldására.

1. Tartsa lenyomva a ALT + F11 gombokat, és ez megnyitja a Microsoft Visual Basic for Applications ablak.

2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modul ablakba.

VBA: Számolja és összegezze a cellákat a háttérszín alapján:

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. Ezután mentse el a kódot, és alkalmazza a következő képletet:
A. Számolja meg a színes cellákat: = színfunkció (A, B: C, HAMIS)
B. Összegezze a színes cellákat: = színfunkció (A, B: C, IGAZ)

Megjegyzés: A fenti képletekben A az a cella az adott háttérszínnel, amellyel ki akarja számítani a számot és az összeget, és IDŐSZÁMÍTÁSUNK ELŐTT az a cellatartomány, ahol a számot és az összeget ki akarja számítani.

4. Készítse el például a következő képernyőképet, írja be a képletet= színfunkció (A1, A1: D11, HAMIS) megszámolni a sárga sejteket. És használja a képletet = színfunkció (A1, A1: D11, IGAZ) összegezve a sárga cellákat. Lásd a képernyőképet:

5. Ha meg akar számlálni és összesíteni más színes cellákat, kérjük, ismételje meg a 4. lépést. Ezután a következő eredményeket kapja:

A Kutools for Excel néhány hasznos funkciót is támogat, amelyek segítenek az Excel felhasználóknak speciális számítások elvégzésében, mondja a cellák háttérszíne, az összeg betűszín szerint stb.

1. Jelölje ki az üres cellát, ahová a számlálási eredményeket elhelyezi, majd kattintson Kutools > Kutools funkciók > Statisztikai és matematika > COUNTBYCELLCOLOR. Lásd a képernyőképet:

2. A Funkció Argumentumok párbeszédpanelen adja meg azt a tartományt, amelyen belül a színes cellákat számolja Referencia mezőben válassza ki azt a cellát, amelyet kitölt a megadott háttérszín a Szín_index_nr mezőbe, és kattintson a OK gomb. Lásd a képernyőképet:

Megjegyzések:
(1) Beírhatja a megadott Kutools függvényt is = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  közvetlenül az üres cellában vagy képletsávban a számlálási eredmények megszerzéséhez;
(2) Kattintson Kutools > Kutools funkciók > Statisztikai és matematika > SUMBYCELLCOLOR vagy típus = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) az üres cellában közvetlenül a megadott háttérszín alapján cellák összegzésére.
Alkalmazza a COUNTBYCELLCOLOR és a SUMBYCELLCOLOR funkciók minden háttérszínhez külön-külön, és az eredményeket az alábbi képernyőképen látja:

Kutools funkciók számos beépített funkciót tartalmaz, amelyek segítenek az Excel felhasználóknak a könnyű számításban, többek között Számlálás / Összeg / Átlag látható cellák, Számlálás / összeg sejtszín szerint, Számolás / Összeg betűszín szerint, Számolja a karaktereket, Félkövér betűkkel számolvaStb Ingyenes próbaverziót!

A fenti Felhasználó által definiált funkcióval egyenként kell megadnia a képletet, ha sokféle szín van, akkor ez a módszer unalmas és időigényes lesz. De ha van Kutools az Excel számára'S Számoljon szín szerint segédprogram segítségével gyorsan létrehozhat egy jelentést a színes cellákról. Nem csak megszámolhatja és összegezheti a színes cellákat, hanem megkaphatja a színtartomány átlagos, max és min értékét is.

1. Válassza ki a használni kívánt tartományt, majd kattintson a gombra Kutools Plus > Számoljon szín szerint, lásd a képernyőképet:

2. És a Számoljon szín szerint párbeszédpanelt, kérjük, tegye az alábbi képernyőképet:
(1) Válassza ki Normál formázás tól Színmódszer legördülő lista;
(2) Válassza ki Háttér tól Számlálás típusa legördülő listát.
(3) Kattintson a Jelentés létrehozása gombra.

Megjegyzések: A színes cellák meghatározott feltételes formázási szín szerint történő megszámolásához és összegzéséhez válassza a lehetőséget Feltételes formázás tól Színmódszer legördülő listát a fenti párbeszédpanelen, vagy válassza a lehetőséget Normál és feltételes formázás a legördülő listából a megadott színnel kitöltött összes cellához.

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

A Számoljon szín szerint funkció kiszámítja a cellákat (Count, Sum, Average, Max stb.) háttérszín vagy betűszín szerint. Ingyenes próbaverziót!

Hogyan számoljuk meg / összegezzük a cellákat az Excel betűszínei alapján?