A SZÍNES cellák számlálása az Excelben (Lépésről lépésre útmutató + VIDEÓ)

Videó megtekintése - Színes cellák számlálása Excelben

Nem lenne jó, ha lenne olyan funkció, amely képes megszámolni a színes cellákat az Excelben?

Sajnos nincs beépített funkció erre.

DE…

Könnyen elvégezhető.

Színes cellák számlálása Excelben

Ebben az oktatóanyagban háromféle módon mutatom be a színes sejtek számolását Excelben (VBA -val és anélkül):

1. A szűrő és a SUBTOTAL funkció használata
2. A GET.CELL funkció használata
3. VBA segítségével létrehozott egyéni funkció használata

#1 Számolja ki a színes cellákat a szűrő és a RÉSZVÉTEL segítségével

A színes cellák Excelben történő számlálásához a következő két lépést kell végrehajtania:

• Szűrje ki a színes cellákat
• Használja a SUBTOTAL funkciót a látható színes cellák számolásához (szűrés után).

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Ebben az adathalmazban két háttérszínt használnak (zöld és narancssárga).

Az alábbi lépésekben számoljuk a színes cellákat az Excelben:

1. Az adathalmaz alatti cellákban használja a következő képletet: = RÉSZVÉTEL (102, E1: E20)
   
2. Válassza ki a fejléceket.
3. Lépjen az Adatok -> Rendezés és szűrés -> Szűrő menüpontra. Ez szűrőt alkalmaz az összes fejlécre.
4. Kattintson a szűrő legördülő menüire.
5. Lépjen a „Szűrés szín szerint” oldalra, és válassza ki a színt. A fenti adatkészletben, mivel két színt használnak a cellák kiemelésére, a szűrő két színt jelenít meg ezeknek a celláknak a szűrésére.

Amint kiszűri a cellákat, észre fogja venni, hogy a SUBTOTAL függvény értéke megváltozik, és csak a szűrés után látható cellák számát adja vissza.

Hogy működik ez?

A SUBTOTAL függvény a 102 -t használja első argumentumként, amely a látható cellák számolására szolgál (a rejtett sorok nem számítanak) a megadott tartományban.

Ha az adatok, ha nincsenek szűrve, akkor 19 -et adnak vissza, de ha szűrik, akkor csak a látható cellák számát adja vissza.

Próbálja ki maga … Töltse le a példafájlt

#2 Számolja ki a színes cellákat a GET.CELL függvény használatával

A GET.CELL egy Macro4 funkció, amelyet kompatibilitási okok miatt megtartottak.

Nem működik, ha rendszeres funkcióként használja a munkalapon.

Azonban működik az Excel nevű tartományokban.

Lásd még: Tudjon meg többet a GET.CELL funkcióról.

Íme a három lépés a GET.CELL használatához a színes sejtek számításához Excelben:

• Hozzon létre egy elnevezett tartományt a GET.CELL függvény használatával
• A Named Range használatával színkódot kaphat egy oszlopban
• Színes számok használata a színes cellák számának számításához (szín szerint)

Merüljünk mélyen, és nézzük meg, mit kell tennünk a három említett lépés mindegyikében.

Elnevezett tartomány létrehozása

• Lépjen a Képletek -> Név definiálása menüpontra.
• Az Új név párbeszédpanelen írja be:
  • Név: GetColor
  • Hatály: Munkafüzet
  • Hivatkozik: = GET.CELL (38, 1. lap! $ A2)
    A fenti képletben használtam 1. lap! $ A2 második érvként. Az oszlop hivatkozását kell használnia, ahol a háttérszínű cellák találhatók.

Színkód lekérése minden cellához

Az adatok melletti cellában használja a = GetColor képletet

Ez a képlet 0 -t adna vissza, ha a cellában NINCS háttérszín, és adott számot adna vissza, ha van háttérszín.

Ez a szám egy színre jellemző, így az azonos háttérszínű cellák ugyanazt a számot kapják.

Színes cellák számolása a színkód használatával

Ha követi a fenti folyamatot, akkor egy oszlop lesz a háttérszínnek megfelelő számokkal.

Egy adott szín számának kiszámításához:

• Valahol az adatkészlet alatt adja meg ugyanazt a háttérszínt a számolni kívánt cellának. Győződjön meg arról, hogy ezt ugyanabban az oszlopban teszi, amelyet a megnevezett tartomány létrehozásakor használt. Például az A oszlopot használtam, ezért csak az „A” oszlop celláit fogom használni.
• A szomszédos cellában használja a következő képletet:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Ez a képlet megadja a megadott háttérszínű cellák számát.

Hogyan működik?

A COUNTIF függvény a megnevezett tartományt (GetColor) használja kritériumként. A képletben megnevezett tartomány a bal oldali szomszédos cellára utal (az A oszlopban), és az adott cella színkódját adja vissza. Ezért ez a színkódszám a kritérium.

A COUNTIF függvény azt a tartományt használja ($ F $ 2: $ F $ 18), amely az összes cella színkódját tartalmazza, és a feltételszám alapján adja vissza a számot.

Próbálja ki maga … Töltse le a példafájlt

#3 Színes számolás a VBA használatával (egyéni függvény létrehozásával)

A fenti két módszerben megtanulta, hogyan kell megszámolni a színes sejteket VBA használata nélkül.

De ha rendben van a VBA használatával, akkor ez a három módszer közül a legegyszerűbb.

A VBA használatával létrehozunk egy egyéni függvényt, amely úgy működik, mint egy COUNTIF függvény, és visszaadja a sejtek számát az adott háttérszínnel.

Itt a kód:

'A kódot Sumit Bansal hozta létre a https://trumpexcel.com webhelyen rCell.Interior.ColorIndex = CountColorValue Akkor TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Az egyéni funkció létrehozásához:

• Aktív munkafüzet esetén nyomja meg az Alt + F11 billentyűkombinációt (vagy kattintson jobb gombbal a munkalap fülére, és válassza a Kód megtekintése) lehetőséget. Ezzel megnyílik a VB szerkesztő.
• A bal oldali ablaktáblában, a munkafüzet alatt, amelyben dolgozik, kattintson jobb gombbal bármelyik munkalapra, és válassza a Beszúrás -> Modul lehetőséget. Ez új modult helyezne be. Másolja ki és illessze be a kódot a modulkód ablakba.
• Kattintson duplán a modul nevére (alapértelmezés szerint a modul neve a 1. modulban), és illessze be a kódot a kódablakba.
• Zárja be a VB szerkesztőt.
• Ez az! Most van egy egyéni függvénye a GetColorCount nevű munkalapon.

Ennek a funkciónak a használatához egyszerűen használja azt bármely hagyományos Excel funkcióként.

Szintaxis: = GetColorCount (CountRange, CountColor)

• CountRange: az a tartomány, amelyben a meghatározott háttérszínnel szeretné számolni a cellákat.
• CountColor: az a szín, amelyhez a cellákat számolni szeretné.

Ennek a képletnek a használatához használja ugyanazt a háttérszínt (amelyet meg szeretne számolni) egy cellában, és használja a képletet. A CountColor argumentum ugyanaz a cella, ahol a képletet adja meg (az alábbiak szerint):

Jegyzet: Mivel a munkafüzetben van kód, mentse el .xls vagy .xlsm kiterjesztéssel.

Próbálja ki maga … Töltse le a példafájlt

Tud más módot a színes cellák Excel -ben történő számlálására?

Ha igen, ossza meg velem megjegyzést.