Hoe om te tel en som selle gebaseer op agtergrondkleur in Excel?
Gestel jy het 'n reeks selle met verskillende agtergrondkleure soos rooi, groen, blou en so aan, maar nou moet jy tel hoeveel selle in die reeks 'n sekere agtergrondkleur het en die gekleurde selle met dieselfde sekere kleur opsom . In Excel is daar geen direkte formule om Sum en Graad van kleur selle te bereken nie. Hier sal ek u 'n paar maniere voorstel om hierdie probleem op te los.
Tel en som selle gebaseer op spesifieke vul kleur deur Filter en SUBTOTAL
Tel en som selle gebaseer op spesifieke vul kleur deur die GET.CELL funksie
Tel en som selle gebaseer op spesifieke vul kleur met Gebruiker gedefinieerde funksie
Tel en som selle gebaseer op spesifieke vul kleur met Kutools Funksies
Telling en som-selle gebaseer op spesifieke vulkleur (of voorwaardelike formateringskleur) met Kutools vir Excel
Een kliek om te tel, som en gemiddelde gekleurde selle in Excel
Met die uitstekende Tel volgens kleur kenmerk van Kutools for Excel, kan jy vinnig tel, som en gemiddelde selle volgens gespesifiseerde vulkleur of lettertipe kleur met net een kliek in Excel. Daarbenewens sal hierdie funksie ook die maksimum en minwaardes van selle vind deur die vulkleur of lettertipe kleur. Volledige funksie Gratis proeflopie 60-dag!
Tel en Som gekleurde selle deur Filter en SUBTOTAL
Gestel ons het 'n vrugteverkope tafel soos hieronder aangedui, en ons sal die gekleurde selle tel of som in die kolom Bedrag. In hierdie situasie kan ons die hoeveelheid kolom op kleur verfyn, en dan tel of geumuleer gekleurde selle deur die SUBTOTAL-funksie maklik in Excel.
1. Kies leë selle om die SUBTOTAL-funksie in te voer.
Om alle selle met dieselfde agtergrondkleur te tel, vul asseblief die formule in = SUBTOTAL (102, E2: E20);
Om alle selle met dieselfde agtergrondkleur op te som, vul asseblief die formule in = SUBTOTAL (109, E2: E20);
nota: In beide formules is E2: E20 die hoeveelheid kolom wat die gekleurde selle bevat, en jy kan dit verander soos benodig.
2. Kies die kop van die tabel en klik data > Filters. Sien skermkiekie:
3. Klik die Filter-ikoon in die kopkolom van die Bedrag kolom en klik Filter deur Kleur en die gespesifiseerde kleur sal jy agtereenvolgens tel. Sien skermkiekie:
Na filtering tel beide SUBTOTAL formules tel en summier alle gefilterde kleur selle in die Bedrag kolom outomaties. Sien skermkiekie:
nota: Hierdie metode vereis die gekleurde selle wat jy sal tel of som in dieselfde kolom.
Tel of Som gekleurde selle deur GET.CELL funksie
In hierdie metode skep ons 'n naamreeks met die GET.CELL funksie, kry u die kleurkode van selle, en tel dan telkens of in die kleurkode maklik in Excel. Doen asseblief as volg:
1. Kliek Formules > Definieer Naam. Sien skermkiekie:
2. Gaan soos volg te werk in die dialoog Nuwe naam:
(1) Tik 'n naam in die vak Naam;
(2) Tik die formule in = GET.CELL (38, Sheet4! $ E2) in die verwys na boks (daarop: in die formule, 38 beteken die selkode terug, en Sheet4! $ E2 is die eerste sel in die Bedrag kolom behalwe die kolomkop wat u moet verander op grond van u tabel data.)
(3) Klik die OK knoppie.
3. Voeg nou 'n nuwe kleur kolom regs by die oorspronklike tabel. Volgende tik die formule = NumColor , en die sleep die AutoFill-hanteer om die formule toe te pas op ander selle in die kolom Kleur. Sien skermkiekie:
nota: In die formule, NumColor is die genoemde reeks wat ons in die eerste 2 stappe gespesifiseer het. Jy moet dit verander na die gespesifiseerde naam wat jy gestel het.
Nou kom die kleurkode van elke sel in die kolomhoeveelheid terug in die kolom. Sien skermkiekie:
4. Kopieer en lys die vulkleur in 'n leë reeks in die aktiewe werkblad en tik formules langs die onderstaande skermkiekie:
A. Om selle volgens kleur te tel, voer asseblief die formule in = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Om selle volgens kleur op te som, vul asseblief die formule in = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).
nota: In beide formules, $ F $ 2: $ F $ 20 is die kolom Kleur, NumColor is die gespesifiseerde naamreeks, $ E $ 2: $ E $ 20 is die Bedrag Kolom, en jy kan dit verander soos jy nodig het.
Nou sal jy sien dat die selle in die hoeveelheid kolom getel word en som volgens hul invulkleure.
As die gekleurde selle versprei word in 'n reeks soos hieronder aangedui, kan beide bogenoemde metodes nie die gekleurde selle tel of som nie. Hier sal hierdie metode 'n VBA bekendstel om die probleem op te los.
1. Hou die ALT + F11 sleutels, en dit maak die Microsoft Visual Basic vir toepassings venster.
VBA: Telling- en someselle gebaseer op agtergrondkleur:
3. Slaan dan die kode op en pas die volgende formule toe:
A. Tel die gekleurde selle: = Colorfunction (A, B: C, valse)
B. Som die gekleurde selle op: = Colorfunction (A, B: C, WAAR)
Let wel: In bostaande formules, A is die sel met die spesifieke agtergrondkleur wat jy die telling en som wil bereken, en B: C is die selreeks waar jy die telling en som wil bereken.
4. Neem byvoorbeeld die volgende skermkiekie, voer die formule in= Colorfunction (A1, A1: D11, valse) om die geel selle te tel. En gebruik die formule = Colorfunction (A1, A1: D11, WAAR) om die geel selle op te som. Sien skermkiekie:
5. As jy ander gekleurde selle wil tel en tel, herhaal asseblief die stap 4. Dan kry jy die volgende resultate:
Kutools for Excel ondersteun ook 'n paar nuttige funksies om Excel-gebruikers te help om spesiale berekeninge te maak, sê telling deur selterugkleur, som volgens letterkleur, ens.
Kutools for Excel - Sluit meer as 300 handige Excel-gereedskap in. Volledige funksie gratis verhoor 60-dag, geen kredietkaart benodig nie! Kry dit nou!
1. Kies die leë sel waarin jy die telresultate plaas en klik Kutools > Kutools Funksies > Statistiese en Wiskunde > COUNTBYCELLCOLOR. Sien skermkiekie:
2. Gee in die dialoog Funksie Argumente die reeks wat jy gekleurde selle in die verwysing boks, kies die sel wat gevul is deur die gespesifiseerde agtergrondkleur in die Color_index_nr boks en klik op die OK knoppie. Sien skermkiekie:
(1) U kan ook die gespesifiseerde Kutools-funksie tik = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) in die leë sel of formule balk direk om die telresultate te kry;
(2) Kliek Kutools > Kutools Funksies > Statistiese en Wiskunde > SUMBYCELLCOLOR of tik = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) in die leë sel direk na someselle gebaseer op die gespesifiseerde agtergrondkleur.
Pas die COUNTBYCELLCOLOR en SUMBYCELLCOLOR funksioneer vir elke agtergrondkleur afsonderlik, en jy sal die resultate kry soos hieronder aangedui:
Kutools Funksies bevat 'n aantal ingeboude funksies om Excel-gebruikers maklik te help bereken, insluitend Tel / som / gemiddelde sigbare selle, Tel / som volgens selkleur, Tel / som volgens lettertipe kleur, Tel karakters, Tel volgens vetdruk, Ens. Het u 'n gratis proef!
Tel en som selle gebaseer op spesifieke vul kleur met Kutools vir Excel
Met die bogenoemde gebruiker gedefinieerde funksie, moet jy die formule een vir een invoer. As daar baie verskillende kleure is, sal hierdie metode vervelend en tydrowend wees. Maar as jy het Kutools for Excel'N Tel volgens kleur nut, kan jy vinnig 'n verslag van die gekleurde selle genereer. Jy kan nie net die gekleurde selle tel nie, maar ook die gemiddelde, maksimum en minwaardes van die gekleurde reeks kan kry.
1. Kies die reeks wat jy wil gebruik, en klik Kutools Plus > Tel volgens kleur, sien skermkiekie:
2. En in die Tel volgens kleur dialoogkassie, doen asseblief soos hieronder vertoon kiekie:
(1) Kies Standaard formatering van die Kleur metode drop down list;
(2) Kies agtergrond van die Telling tipe drop down list.
(3) Klik op die knoppie Generate Report.
nota: Om gekleurde selle volgens spesifieke voorwaardelike formateringskleur te tel en op te tel, kies asseblief Voorwaardelike formatering van die Kleur metode drop-down lys in hierbo dialoog, of kies Standaard en voorwaardelike formatering uit die aftreklys om al die selle wat deur die gekose kleur gevul is, te tel.
Nou kry jy 'n nuwe werkboek met die statistieke. Sien skermkiekie:
Die Tel volgens kleur funksie bereken (telling, som, gemiddeld, maksimum, ens) selle deur agtergrond kleur of font kleur. Het u 'n gratis proef!