Mewn tasgau dyddiol, mae marcio lliw yn ddull poblogaidd o wahaniaethu'n gyflym ac amlygu data hanfodol. Ond, sut ydyn ni'n cyfrif neu'n crynhoi data celloedd yn seiliedig ar liw penodol (lliw llenwi, lliw ffont, fformatio amodol)? Yn ddiofyn, nid yw Excel yn cynnig nodwedd uniongyrchol i'w chyfrif na'i chrynhoi yn ôl lliw. Serch hynny, gyda rhai triciau a dulliau anuniongyrchol, gallwn gyflawni hyn o hyd. Bydd yr erthygl hon yn archwilio sut i gyfrif neu grynhoi data yn ôl lliw.

Cyfrif a symio celloedd yn seiliedig ar liw cefndir

• Trwy ddefnyddio Swyddogaeth Diffiniedig Defnyddiwr
• Trwy ddefnyddio nodwedd bwerus - Kutools for Excel
• Trwy ddefnyddio Filter a SUBTOTAL

Cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont

• Trwy ddefnyddio Swyddogaeth Diffiniedig Defnyddiwr
• Trwy ddefnyddio nodwedd hawdd - Kutools for Excel

Cyfrif a chrynhoi celloedd yn seiliedig ar liw fformatio amodol

• Trwy ddefnyddio cod VBA
• Trwy ddefnyddio nodwedd glyfar - Kutools for Excel

Fideo: Cyfrif a chrynhoi celloedd yn seiliedig ar liw

Cyfrif a symio celloedd yn seiliedig ar liw cefndir

Er enghraifft, os oes gennych ystod o ddata lle mae'r gwerthoedd wedi'u llenwi â gwahanol liwiau cefndir fel y dangosir yn y screenshot isod. I gyfrif neu grynhoi'r celloedd yn seiliedig ar liw penodol, nid yw Excel yn cynnig nodwedd uniongyrchol i gyfrif neu grynhoi celloedd yn seiliedig ar eu lliw cefndir. Fodd bynnag, gydag ychydig o ddyfeisgarwch a rhai technegau defnyddiol, gallwch chi gyflawni'r dasg hon. Gadewch i ni archwilio rhai dulliau defnyddiol yn yr adran hon.

Cyfrif a chrynhoi celloedd yn ôl lliw cefndir gyda Swyddogaeth a Ddiffiniwyd gan Ddefnyddiwr

Yma, byddwn yn dangos i chi sut i greu a defnyddio Swyddogaeth a Ddiffiniwyd gan Ddefnyddiwr o'r fath i ddatrys y dasg hon yn Excel. Os gwelwch yn dda gwnewch gyda'r camau canlynol:

Cam 1: Agorwch olygydd modiwl VBA a chopïwch y cod

1. Pwyswch Alt + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
2. Yn y ffenestr a agorwyd, cliciwch Mewnosod > Modiwlau i greu modiwl gwag newydd.
3. Yna, copïwch a gludwch y cod isod i'r modiwl gwag.
   Cod VBA: Cyfrif a chrynhoi celloedd yn seiliedig ar liw cefndir

   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
   

Cam 2: Creu fformiwlâu i gyfrif a chrynhoi celloedd yn ôl lliw cefndir

Ar ôl gludo'r cod uchod, caewch ffenestr y modiwl, yna defnyddiwch y fformiwlâu canlynol:

• Cyfrif celloedd yn seiliedig ar y lliw cefndir penodol:
  Copïwch neu deipiwch y fformiwla a ddarperir isod yn eich cell dymunol ar gyfer y canlyniad. Yna, llusgwch yr handlen llenwi i lawr i gael canlyniadau eraill. Gweler y sgrinlun:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Nodyn: Yn y fformiwla hon, G2 yw'r gell gyfeirio gyda'r lliw cefndir penodol rydych chi am ei gydweddu; $B$2:$E$12 yw'r ystod lle rydych am gyfrif nifer y celloedd o liw G2; Anghywir yn cael ei ddefnyddio i gyfrif celloedd gyda lliw cyfatebol.
  
• Swm celloedd yn seiliedig ar y lliw cefndir penodol:
  Copïwch neu deipiwch y fformiwla a ddarperir isod yn eich cell dymunol ar gyfer y canlyniad. Yna, llusgwch yr handlen llenwi i lawr i gael canlyniadau eraill. Gweler y sgrinlun:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Nodyn: Yn y fformiwla hon, G2 yw'r gell gyfeirio gyda'r lliw cefndir penodol rydych chi am ei gydweddu; $B$2:$E$12 yw'r ystod lle rydych am gyfrif nifer y celloedd o liw G2; TRUE yn cael ei ddefnyddio i grynhoi celloedd gyda lliw cyfatebol.
  

Cyfrif a chrynhoi celloedd yn ôl lliw cefndir gyda nodwedd bwerus

I'r rhai sy'n anghyfarwydd â rhaglennu, gall VBA ymddangos yn eithaf cymhleth. Yma, byddwn yn cyflwyno offeryn pwerus - Kutool ar gyfer Excel, ei Cyfrif yn ôl Lliw nodwedd yn eich galluogi i gyfrifo yn hawdd (cyfrif, swm, cyfartaledd, ac ati) yn seiliedig ar liw cefndir mewn dim ond rhai cliciau. Yn drawiadol, Cyfrif yn ôl Lliw mae nodwedd yn mynd y tu hwnt i liwiau cefndir yn unig - gall hefyd wahaniaethu a chyfrifo yn seiliedig ar liwiau ffont a fformatio amodol.

Ar ôl llwytho i lawr a gosod Kutools for Excel, yn gyntaf, dewiswch yr ystod ddata yr ydych am ei gyfrif neu grynhoi celloedd yn seiliedig ar liw cefndir penodol. Nesaf, llywiwch i Kutools Byd Gwaith a dewis Cyfrif yn ôl Lliw.

Yn y Cyfrif yn ôl Lliw blwch deialog, nodwch y gweithrediadau:

1. dewiswch Fformatio safonol oddi wrth y Dull lliw rhestr ostwng;
2. Nodwch Cefndir oddi wrth y Math o gyfrif rhestr ostwng, a gallwch gael rhagolwg o'r canlyniadau ystadegol ar gyfer pob lliw cefndir yn y blwch deialog;
3. O'r diwedd, cliciwch Cynhyrchu adroddiad i allforio'r canlyniadau a gyfrifwyd i lyfr gwaith newydd.

Canlyniad:

Nawr fe gewch chi lyfr gwaith newydd gyda'r ystadegau. Gweler y screenshot:

Cyfrif a chrynhoi celloedd yn ôl lliw cefndir gyda ffwythiant Filter a SUBTOTAL

Gan dybio bod gennym dabl gwerthu ffrwythau fel y sgrinlun a ddangosir isod, a byddwn yn cyfrif neu'n crynhoi'r celloedd lliw yn y swm colofn.

Cam 1: Cymhwyso'r swyddogaeth SUBTOTAL

Dewiswch gelloedd gwag i fynd i mewn i'r swyddogaeth SUBTOTAL.

• I gyfrif pob cell gyda'r un lliw cefndir, rhowch y fformiwla:

  =SUBTOTAL(102, F2:F16)

• I grynhoi pob cell gyda'r un lliw cefndir, rhowch y fformiwla;

  =SUBTOTAL(109, F2:F16)

• Nodyn: yn y fformiwlâu uchod, 102 cynrychioli i gyfrif gwerthoedd rhifol mewn rhestr wedi'i hidlo tra'n eithrio celloedd cudd; 109 cynrychioli i grynhoi gwerthoedd mewn rhestr wedi'i hidlo heb gynnwys celloedd cudd; F2: F16 yw'r ystod y bydd naill ai'r cyfrif neu'r swm yn cael ei gyfrifo drosti.
• 

Cam 2: Hidlo celloedd yn seiliedig ar liw penodol

1. Dewiswch bennawd y tabl, a chliciwch Dyddiad > Hidlo. Gweler y screenshot:
   
2. Cliciwch ar y Hidlo icon  yng nghell pennawd y swm colofn, a chlicio Hidlo yn ôl Lliw a'r lliw penodedig y byddwch chi'n ei gyfrif yn olynol. Gweler y screenshot:
   

Canlyniad:

Ar ôl hidlo, mae'r fformiwlâu SUBTOTAL yn cyfrif ac yn crynhoi'r celloedd lliw yn awtomatig yn y swm colofn. Gweler y screenshot:

Cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont

Eisiau cyfrif neu grynhoi celloedd yn seiliedig ar liw eu ffont yn Excel? Gadewch i ni ddweud bod gennych chi'r data, fel yn y sgrinlun a roddir, gyda chelloedd sy'n cynnwys testunau mewn lliw coch, glas, oren a du. Nid yw Excel yn gwneud hyn yn hawdd yn ddiofyn. Ond peidiwch â phoeni! Yn yr adran hon, byddwn yn dangos rhai triciau syml i chi wneud yn union hynny.

Cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont gyda Swyddogaeth Diffiniedig Defnyddiwr

I gyfrif a chrynhoi celloedd â lliwiau ffont penodol, efallai y bydd y Swyddogaeth Ddiffiniedig Defnyddiwr ganlynol yn eich helpu i ddatrys y dasg hon. Os gwelwch yn dda gwnewch gyda'r camau canlynol:

Cam 1: Agorwch olygydd modiwl VBA a chopïwch y cod

1. Pwyswch Alt + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
2. Yn y ffenestr a agorwyd, cliciwch Mewnosod > Modiwlau i greu modiwl gwag newydd.
3. Yna, copïwch a gludwch y cod isod i'r modiwl gwag.
   Cod VBA: Cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont

   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
   

Cam 2: Creu fformiwlâu i gyfrif a chrynhoi celloedd yn ôl lliw ffont

Ar ôl gludo'r cod uchod, caewch ffenestr y modiwl, yna defnyddiwch y fformiwlâu canlynol:

• Cyfrif celloedd yn seiliedig ar y lliw ffont penodol:
  Copïwch neu deipiwch y fformiwla a ddarperir isod yn eich cell dymunol ar gyfer y canlyniad. Yna, llusgwch yr handlen llenwi i lawr i gael canlyniadau eraill. Gweler y sgrinlun:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Nodyn: Yn y fformiwla hon, G2 yw'r gell cyfeirio gyda'r lliw ffont penodol yr ydych am ei gydweddu; $B$2:$E$12 yw'r ystod lle rydych am gyfrif nifer y celloedd o liw G2.
  
• Swm celloedd yn seiliedig ar y lliw ffont penodol:
  Copïwch neu deipiwch y fformiwla a ddarperir isod yn eich cell dymunol ar gyfer y canlyniad. Yna, llusgwch yr handlen llenwi i lawr i gael canlyniadau eraill. Gweler y sgrinlun:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Nodyn: Yn y fformiwla hon, G2 yw'r gell cyfeirio gyda'r lliw ffont penodol yr ydych am ei gydweddu; $B$2:$E$12 yw'r ystod lle rydych am gyfrif nifer y celloedd o liw G2.
  

Cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont gyda nodwedd hawdd

Eisiau cyfrif neu grynhoi gwerthoedd celloedd yn Excel yn ddiymdrech yn seiliedig ar liw ffont? Deifiwch i mewn Kutools for Excel's Cyfrif yn ôl Lliw nodwedd! Gyda'r offeryn craff hwn, mae cyfrif a chrynhoi celloedd yn ôl lliw ffont penodol yn dod yn awel. Darganfyddwch sut Kutools yn gallu trawsnewid eich profiad Excel.

Ar ôl llwytho i lawr a gosod Kutools for Excel, yn gyntaf, dewiswch yr ystod ddata yr ydych am ei gyfrif neu grynhoi celloedd yn seiliedig ar liw ffont penodol. Yna, cliciwch Kutools Byd Gwaith > Cyfrif yn ôl Lliw i agor y Cyfrif yn ôl Lliw blwch deialog.

Yn y Cyfrif yn ôl Lliw blwch deialog, nodwch y gweithrediadau:

1. dewiswch Fformatio safonol oddi wrth y Dull lliw rhestr ostwng;
2. Nodwch Ffont oddi wrth y Math o gyfrif rhestr ostwng, a gallwch gael rhagolwg o'r canlyniadau ystadegol ar gyfer pob lliw ffont yn y blwch deialog;
3. O'r diwedd, cliciwch Cynhyrchu adroddiad i allforio'r canlyniadau a gyfrifwyd i lyfr gwaith newydd.

Canlyniad:

Nawr, mae gennych lyfr gwaith newydd yn arddangos yr ystadegau manwl yn seiliedig ar liw ffont. Gweler y sgrinlun:

Cyfrif a chrynhoi celloedd yn seiliedig ar liw fformatio amodol

Yn Excel, efallai y byddwch yn aml yn defnyddio'r Fformatio Amodol i gymhwyso lliw penodol i gelloedd sy'n bodloni meini prawf penodol, gan wneud delweddu data yn reddfol. Ond beth os oes angen i chi gyfrif neu grynhoi'r celloedd hynny sydd wedi'u fformatio'n arbennig? Er nad yw Excel yn cynnig ffordd uniongyrchol ar gyfer hyn, dyma ffyrdd o symud o gwmpas y cyfyngiad hwn.

Cyfrif a chrynhoi celloedd sydd wedi'u fformatio'n amodol gyda chod VBA

Nid yw cyfrif a chrynhoi celloedd wedi'u fformatio'n amodol yn Excel yn syml gan ddefnyddio swyddogaethau adeiledig. Fodd bynnag, gallwch chi gyflawni'r dasg hon gan ddefnyddio cod VBA. Gadewch i ni fynd dros sut y gallwch chi ddefnyddio VBA ar gyfer hyn:

Cam 1: Agorwch olygydd modiwl VBA a chopïwch y cod

1. Pwyswch Alt + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
2. Yn y ffenestr a agorwyd, cliciwch Mewnosod > Modiwlau i greu modiwl gwag newydd.
3. Yna, copïwch a gludwch y cod isod i'r modiwl gwag.
   Cod VBA: Cyfrif a swm celloedd yn seiliedig ar liw fformatio amodol

   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
   

Cam 2: Gweithredwch y cod VBA hwn

1. Ar ôl pasio'r cod, pwyswch F5 allweddol i redeg y cod hwn, bydd blwch prydlon yn ymddangos, dewiswch yr ystod data lle rydych chi am gyfrif a chrynhoi celloedd yn seiliedig ar fformatio amodol. Yna, cliciwch OK, Gweler y screenshot:
   
2. Mewn blwch prydlon arall, dewiswch liw fformatio amodol penodol yr ydych am ei gyfrif a'i grynhoi, a chliciwch OK botwm, gweler y screenshot:
   

Canlyniad:

Nawr, bydd y canlyniad, sy'n cynnwys cyfrif a swm y celloedd gyda'r lliw fformatio amodol penodedig, yn cael ei arddangos yn y blwch popped-out. Gweler y sgrinlun:

Cyfrif a chrynhoi celloedd sydd wedi'u fformatio'n amodol gyda nodwedd glyfar

Os ydych chi'n chwilio am ddulliau cyflym a hawdd eraill i gyfrif a chrynhoi celloedd sydd wedi'u fformatio'n amodol, Kutools for Excel yw eich ateb mynd-i. Ei Cyfrif yn ôl Lliw Gall nodwedd ddatrys y dasg hon mewn dim ond ychydig o gliciau. Deifiwch i mewn i ddarganfod yr effeithlonrwydd a'r manwl gywirdeb y gall Kutools eu cynnig i'ch llif gwaith.

Ar ôl llwytho i lawr a gosod Kutools for Excel, yn gyntaf, dewiswch yr ystod ddata yr ydych am ei gyfrif neu grynhoi celloedd yn seiliedig ar liw fformatio amodol penodol. Yna, cliciwch Kutools Byd Gwaith > Cyfrif yn ôl Lliw i agor y Cyfrif yn ôl Lliw blwch deialog.

Yn y Cyfrif yn ôl Lliw blwch deialog, nodwch y gweithrediadau:

1. dewiswch Fformatio amodol oddi wrth y Dull lliw rhestr ostwng;
2. Nodwch Cefndir oddi wrth y Math o gyfrif rhestr ostwng, a gallwch gael rhagolwg o'r canlyniadau ystadegol ar gyfer pob lliw fformatio conditionla yn y blwch deialog;
3. O'r diwedd, cliciwch Cynhyrchu adroddiad i allforio'r canlyniadau a gyfrifwyd i lyfr gwaith newydd.

Canlyniad:

Nawr, mae gennych lyfr gwaith newydd sy'n dangos yr ystadegau manwl yn seiliedig ar y lliw fformatio amodol. Gweler y sgrinlun:

Erthyglau cysylltiedig: