Skip i'r prif gynnwys

Awgrymiadau Excel: Cyfrif / swm celloedd yn ôl lliw (cefndir, ffont, fformatio amodol)

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

Cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont

Cyfrif a chrynhoi celloedd yn seiliedig ar liw fformatio amodol


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 lawrlwytho a gosod Kutools ar gyfer 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:

Awgrymiadau:
  1. Mae adroddiadau Cyfrif yn ôl Lliw Mae nodwedd hefyd yn cefnogi cyfrif a chrynhoi celloedd yn seiliedig ar liw ffont safonol, cefndir neu liw ffont o fformatio amodol, a chyfuniad o liwiau llenwi a fformatio amodol.
  2. Diddordeb yn y nodwedd hon, os gwelwch yn dda cliciwch i'w lawrlwytho i gael treial am ddim am 30 diwrnod.

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:

Nodyn: Mae'r dull hwn yn gofyn bod y celloedd lliw y byddwch chi'n eu cyfrif neu eu swm yn yr un golofn.

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 ar gyfer 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 lawrlwytho a gosod Kutools ar gyfer 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:

Awgrymiadau: Diddordeb yn y nodwedd hon, os gwelwch yn dda cliciwch i'w lawrlwytho i gael treial am ddim am 30 diwrnod.

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 ar gyfer 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 lawrlwytho a gosod Kutools ar gyfer 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:

Awgrymiadau: Diddordeb yn y nodwedd hon, os gwelwch yn dda cliciwch i'w lawrlwytho i gael treial am ddim am 30 diwrnod.

Erthyglau cysylltiedig:

  • Os yw lliw'r ffont yn goch yna dychwelwch destun penodol
  • Sut allech chi ddychwelyd testun penodol os yw lliw'r ffont yn goch mewn cell arall fel y dangosir y sgrinlun isod? Yn yr erthygl hon, byddaf yn cyflwyno rhai triciau ar gyfer gwneud rhai gweithrediadau yn seiliedig ar y testun ffont coch yn Excel.
  • Hidlo data yn ôl lliwiau lluosog
  • Fel rheol, yn Excel, gallwch hidlo rhesi yn gyflym gyda dim ond un lliw, ond, a ydych erioed wedi ystyried hidlo rhesi â lliwiau lluosog ar yr un pryd? Yr erthygl hon, byddaf yn siarad am dric cyflym i chi ddelio â'r broblem hon.
  • Ychwanegu lliw i'r gwymplen
  • Yn Excel, gall creu cwymplen eich helpu chi'n fawr, ac weithiau, mae angen i chi roi cod lliw ar werthoedd y gwymplen yn dibynnu ar y cyfatebol a ddewiswyd. Er enghraifft, rwyf wedi creu cwymplen o'r enwau ffrwythau, pan fyddaf yn dewis Apple, mae angen i mi fod y gell wedi'i lliwio â choch yn awtomatig, a phan fyddaf yn dewis Oren, gellir lliwio'r gell ag oren.
  • Lliwiwch resi am yn ail ar gyfer celloedd unedig
  • Mae'n ddefnyddiol iawn fformatio rhesi am yn ail â lliw gwahanol mewn data mawr i ni allu sganio'r data, ond, weithiau, efallai y bydd rhai celloedd wedi'u huno yn eich data. Er mwyn tynnu sylw at y rhesi bob yn ail â lliw gwahanol ar gyfer y celloedd unedig fel y dangosir y sgrinlun isod, sut allech chi ddatrys y broblem hon yn Excel?