Padoms. Citas valodas ir Google tulkotas. Jūs varat apmeklēt English šīs saites versiju.
Pieslēgties
x
or
x
x
Reģistrēties
x

or

Kā aprēķināt un summēt šūnas, pamatojoties uz Excel fona krāsu?

Pieņemot, ka jums ir virkne šūnu ar dažādām fona krāsām, piemēram, sarkanā, zaļā, zilā un tā tālāk, bet tagad jums ir jāaprēķina, cik daudz šūnu šūnu ir noteikta fona krāsā, un jāsalīdzina krāsainas šūnas ar tādu pašu noteiktu krāsu . Excel programmā nav tiešas formulas, lai aprēķinātu krāsu un krāsu skaitu šūnās, šeit es iepazīstināšu jūs ar dažiem veidiem, kā atrisināt šo problēmu.

Skaits un Summas šūnas, pamatojoties uz konkrētu aizpildījuma krāsu, izmantojot filtru un SUBTOTAL

Count un Sum šūnas, pamatojoties uz specifisku aizpildījuma krāsu, izmantojot GET.CELL funkciju

Skaita un summas šūnas, pamatojoties uz konkrētu aizpildījuma krāsu ar lietotāja definētu funkciju

Count un Sum šūnas, pamatojoties uz specifisku aizpildījuma krāsu ar Kutools funkcijām

Count un Sum šūnas, pamatojoties uz specifisku aizpildījuma krāsu (vai nosacījuma formatēšanas krāsu) ar Kutools for Excel

Viens klikšķis, lai aprēķinātu, summētu un vidēji krāsainas šūnas Excel

Ar lielisku Skatiet pēc krāsas iezīme Kutools Excel, jūs varat ātri saskaitīt, summēt un vidējās šūnas pēc norādītās aizpildījuma krāsas vai fonta krāsas ar tikai vienu klikšķi programmā Excel. Turklāt šī funkcija atklās arī šūnu maksimālās un minimālās vērtības pēc aizpildījuma krāsas vai fonta krāsas. Full Feature Free Trial 60 diena!
reklāmu skaits pēc krāsas 2


Count un Sum krāsainas šūnas pēc filtra un SUBTOTAL

Pieņemot, ka mums ir augļu pārdošanas tabula, kā parādīts zemāk esošajā attēlā, un mēs skaitām vai summējam krāsas kolonnas kolonnā. Šādā situācijā mēs varam filtrēt daudzuma kolonnu pēc krāsas un pēc tam skaitīt vai summēt filtrētās krāsas šūnas ar SUBTOTAL funkciju viegli Excel.

1. Izvēlieties tukšu šūnu, lai ievadītu SUBTOTAL funkciju.

  1. Lai saskaitītu visas šūnas ar tādu pašu fona krāsu, lūdzu, ievadiet formulu = SUBTOTAL (102, E2: E20);
  2. Lai summētu visas šūnas ar tādu pašu fona krāsu, lūdzu, ievadiet formulu = SUBTOTAL (109, E2: E20);


Piezīmes: Abās formulās E2: E20 ir kolonna ar daudzumu, kas satur krāsainas šūnas, un jūs varat tos mainīt, kā nepieciešams.

2. Atlasiet tabulas galveni un noklikšķiniet uz Datums > filtrs. Skatīt ekrānuzņēmumu:

3. Noklikšķiniet uz ikonas Filter Kolonnas kolonnas galvenes šūnā un noklikšķiniet uz Atlasīt pēc Color un norādītā krāsa tiks secīgi uzskaitīta. Skatīt ekrānuzņēmumu:

Pēc filtrēšanas gan SUBTOTAL formulas, gan skaitot un filtrējot visas filtrētās krāsas šūnas, tiek automātiski kolonnā. Skatīt ekrānuzņēmumu:

Piezīmes: Šī metode prasa, lai tajā pašā kolonnā tiktu iekrāsotas krāsas vai summa.


Count vai Sum krāsainas šūnas ar GET.CELL funkciju

Šajā metodē mēs izveidosim nosaukto diapazonu ar funkciju GET.CELL, iegūsim šūnu krāsu kodu un pēc tam Excel skaitu viegli saskaita ar krāsu kodu. Lūdzu, rīkojieties šādi:

1. Noklikšķiniet Formulas > Definēt vārdu. Skatīt ekrānuzņēmumu:

2. Dialoglodziņā Jauns vārds, lūdzu, rīkojieties šādi:
(1) Ierakstiet nosaukumu lodziņā Name;
(2) Ievadiet formulu = GET.CELL (38, Sheet4! $ E2) lodziņā Atsauces uzatzīmēt: formulā 38 nozīmē atgriezt šūnu kodu un Sheet4! $ E2 ir pirmā kolonna slejā Summa, izņemot kolonnas virsrakstu, kas jāmaina, pamatojoties uz tabulas datiem.)
(3) Noklikšķiniet uz OK poga.

3. Tagad pievienojiet jaunu krāsu kolonnu uz sākotnējās tabulas. Tālāk ierakstiet formulu = NumColor , un velciet automātiskās aizpildīšanas rokturi, lai izmantotu formulu citām kolonnām kolonnā Krāsa. Skatīt ekrānuzņēmumu:
Piezīmes: Formulā NumColor ir nosauktais diapazons, kuru mēs norādījām pirmajos 2 soļos. Jums tas jāmaina uz norādīto nosaukumu.

Tagad katras šūnas krāsu kods kolonnā Summa atgriež krāsu kolonnā. Skatīt ekrānuzņēmumu:

4. Nokopējiet un uzskaitiet aizpildījuma krāsu tukšajā diapazonā aktīvajā darblapā un ierakstiet formulu blakus tai, kā parādīts tālāk attēlā:
A. Lai saskaitītu šūnas pēc krāsas, lūdzu, ievadiet formulu = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Lai summētu šūnas pēc krāsas, lūdzu, ievadiet formulu = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Piezīmes: Abās formulās: $ F $ 2: $ F $ 20 ir sleja Krāsa, NumColor ir norādītais nosaukums, $ E $ 2: $ E $ 20 ir daudzuma sleja, un jūs varat tos mainīt, cik nepieciešams.

Tagad jūs redzēsiet, ka kolonnā Summas tiek uzskaitītas šūnas un to summa tiek aizpildīta pēc to aizpildījuma krāsas.


Skaita un summas šūnas, pamatojoties uz konkrētu aizpildījuma krāsu ar lietotāja definētu funkciju


Pieņemot, ka krāsainas šūnas izkliedē diapazonā, kas redzams zemāk redzamajā attēlā, abas iepriekš minētās metodes nevar skaitīt vai summēt krāsas šūnas. Šeit šī metode ieviesīs VBA, lai atrisinātu problēmu.

1. Turiet nospiestu ALT + F11 atslēgas, un tas atver Microsoft Visual Basic for Applications logs.

2. Noklikšķiniet ielaidums > Moduļiun ielīmējiet moduļa logā šādu kodu.

VBA: Skaitļu un summu kopas, pamatojoties uz fona krāsu:

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. Pēc tam saglabājiet kodu un izmantojiet šādu formulu:
A. Skaitiet krāsainas šūnas: = krāsu funkcija (A, B: C, FALSE)
B. Summa krāsainas šūnas: = krāsu funkcija (A, B: C, TRUE)

Piezīme. A ir šūna ar konkrētu fona krāsu, kuru vēlaties aprēķināt, un B: C ir šūnu diapazons, kurā vēlaties aprēķināt skaitu un summu.

4. Piemēram, uzņemiet šādu ekrānuzņēmumu, ievadiet formulu= krāsu funkcija (A1, A1: D11, FALSE) uzskaitīt dzeltenās šūnas. Un izmantojiet formulu = krāsu funkcija (A1, A1: D11, TRUE) summēt dzeltenās šūnas. Skatīt ekrānuzņēmumu:

5. Ja vēlaties saskaitīt un summēt citas krāsas šūnas, lūdzu, atkārtojiet solis 4. Tad jūs saņemsit šādus rezultātus:


Count un Sum šūnas, pamatojoties uz specifisku aizpildījuma krāsu ar Kutools funkcijām

"Kutools for Excel" atbalsta arī dažas noderīgas funkcijas, kas palīdz Excel lietotājiem veikt īpašus aprēķinus, saka skaits pēc šūnu fona krāsas, summa pēc fontu krāsas utt.

Kutools Excel - ietver vairāk par 300 ērtiem Excel rīkiem. Pilna bezmaksas bezmaksas izmēģinājuma versija 60-diena, kredītkartes nav nepieciešama! Get to tagad!

1. Atlasiet tukšu šūnu, kurā ievietojat skaitīšanas rezultātus, un noklikšķiniet uz Kutools > Kutools funkcijas > Statistikas un matemātikas > COUNTBYCELLCOLOR. Skatīt ekrānuzņēmumu:

2. Dialoglodziņā Funkciju argumenti, lūdzu, norādiet diapazonu, kurā jūs skaitīsiet krāsainas šūnas atsauces lodziņā atlasiet šūnu, kas tiek aizpildīta ar norādīto fona krāsu Color_index_nr lodziņā un noklikšķiniet uz OK poga Skatīt ekrānuzņēmumu:

Piezīmes:
(1) Varat arī ievadīt norādīto Kutools funkciju = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) tukšajā šūnā vai formulas joslā tieši, lai iegūtu skaitīšanas rezultātus;
(2) Noklikšķiniet Kutools > Kutools funkcijas > Statistikas un matemātikas > SUMBYCELLCOLOR vai tipa = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) tukšajā šūnā tieši šūnu summēšanai, pamatojoties uz norādīto fona krāsu.
piemēro COUNTBYCELLCOLOR un SUMBYCELLCOLOR katrai fona krāsai atsevišķi, un jūs saņemsiet rezultātus zemāk redzamajā ekrānuzņēmumā:

Kutools funkcijas ir vairākas iebūvētas funkcijas, kas palīdz Excel lietotājiem viegli aprēķināt, ieskaitot Skaits / summa / vidēji redzamās šūnas, Skaits / summa pēc šūnu krāsas, Skaits / summa pēc fontu krāsas, Skaitļu rakstzīmes, Skaitīt pēc fontu treknraksta, Uc Jums ir bezmaksas izmēģinājums!


Skaita un summas šūnas, kuru pamatā ir specifiska aizpildījuma krāsa ar Kutools programmā Excel

Izmantojot iepriekš definēto lietotāja definēto funkciju, jums ir jāievada formula pa vienam, ja ir daudz dažādu krāsu, šī metode būs nogurdinoša un laikietilpīga. Bet, ja jums ir Kutools Excel'S Skatiet pēc krāsas lietderība, varat ātri ģenerēt pārskatu par krāsainām šūnām. Jūs varat ne tikai skaitīt un summēt krāsainās šūnas, bet arī iegūt krāsas diapazona vidējo, maksimālo un minimālo vērtību.

Kutools Excel - ietver vairāk par 300 ērtiem Excel rīkiem. Pilna bezmaksas bezmaksas izmēģinājuma versija 60-diena, kredītkartes nav nepieciešama! Get to tagad!

1. Izvēlieties diapazonu, kuru vēlaties izmantot, un noklikšķiniet uz Kutools Vairāk > Skatiet pēc krāsas, skatiet ekrānuzņēmumu:

2. Un Skatiet pēc krāsas dialoglodziņš, lūdzu, rīkojieties šādi:
(1) Izvēlieties Standarta formatēšana no Krāsu metode izvēlnes saraksts;
(2) Izvēlieties fons no Skaita tips izvēlnes saraksts.
(3) Noklikšķiniet uz pogas Izveidot atskaiti.

Piezīmes: Lai skaitītu un summētu krāsainas šūnas ar konkrētu nosacījuma formatēšanas krāsu, lūdzu, izvēlieties Nosacīta formatēšana no Krāsu metode nolaižamajā sarakstā vai izvēlieties Standarta un nosacītā formatēšana nolaižamajā sarakstā, lai saskaitītu visas šūnas, kas aizpildītas ar norādīto krāsu.

Tagad ar statistiku jūs saņemsiet jaunu darbgrāmatu. Skatīt ekrānuzņēmumu:

The Skatiet pēc krāsas funkcija aprēķina (Count, Sum, Average, Max uc) šūnas pēc fona krāsas vai fonta krāsas. Jums ir bezmaksas izmēģinājums!


Saistītais raksts:

Kā aprēķināt / summēt šūnas, pamatojoties uz Excel fontu krāsām?


Demonstrācija: skaita un summas šūnas, pamatojoties uz fonu, nosacīta formatējuma krāsa:

Šajā video Kutools cilni un Kutools Vairāk tab pievieno Kutools Excel. Ja nepieciešams, noklikšķiniet uz šeit lai bez ierobežojumiem varētu veikt 60 dienas bezmaksas izmēģinājumu!



Ieteicamie produktivitātes rīki

Cilnē Office

zelta zvaigzne1 Novietojiet parocīgas cilnes Excel un citai Office programmatūrai, tāpat kā Chrome, Firefox un jaunā Internet Explorer.

Kutools Excel

zelta zvaigzne1 Pārsteidzošs! Palieliniet savu produktivitāti 5 minūtēs. Nepieciešamas īpašas prasmes, izņemot katru dienu divas stundas!

zelta zvaigzne1 300 jaunas funkcijas Excel, Make Excel daudz viegli un jaudīgu:

  • Apvienot šūnu / rindas / kolonnas, nezaudējot datus.
  • Apvienot un konsolidēt vairākas lapas un darbgrāmatas.
  • Salīdziniet diapazonus, kopējiet vairākus diapazonus, pārveidojiet tekstu uz datumu, vienību un valūtas konvertēšanu.
  • Count by Colors, Paging Starpsvars, Advanced Kārtot un Super Filter,
  • Vairāk Izvēlēties / Ievietot / Dzēst / Teksts / Format / Saite / Komentārs / Darbgrāmatas / Darblapas Rīki ...

Kutools ekrānuzņēmums Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 1 months ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 2 months ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 1 months ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 2 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 3 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 2 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 4 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 6 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.