Oppsummering Basert på formatering i tilstøtende celler

October 31  by Eliza

Srinivas har data i begge kolonnene A og B. Han trenger å summere verdiene i kolonne B som cellen formatet på de tilsvarende cellene i kolonne A er gul. For eksempel, hvis formatet i cellen A17 er gul, deretter verdien i cellen B17 bør inkluderes i den sum.

Det er mange makroer tilgjengelig på internett (herunder på ExcelTips) som lar deg gjøre betinget summering basert på fargen eller annet format av en celle. Dette behovet er forskjellige, men ved at det er ikke fargen av cellen på problemet, men fargen på cellen en kolonne mot venstre. Dette kan fortsatt gjøres ved hjelp av en makro, som vist her:

Funksjon SumNextYellow (ByVal r Som Range)
Dim c Som Range
Dimme en Som Double

For hver c I r
Hvis c.Offset (0, -1) .Interior.ColorIndex = 6 Da 'Yellow
a = a + c.Value
End If
Neste c
SumNextYellow = en
End Function

Funksjonen kan brukes i et regneark formel, og aksepterer en områdereferanse som et argument. Det skritt deretter gjennom hver celle i området, og hvis cellen rett til venstre er gul, så verdien er inkludert i summen. (Du bør merke seg at ColorIndex brukes i makro bør testes med din versjon av Excel for å være sikker på at det er aktuelt, kan det være forskjellig i ulike versjoner.)

En mye mer robust eksempel er vist i den følgende oversikt. Denne funksjonen aksepterer ett eller flere områder av celler, sammen med et argument som representerer et utvalg av formateringen du vil bruke.

Funksjon ColorConditionSum (cSample Som Excel.Range, RNG Som Excel.Range)
'Denne funksjonen returnerer summen av verdiene i rng.Columns (2) hvis
'Tilsvarende celle i rng.Columns (1) er farget med prøven
'Farge (cSample celle)

"Argumenter: cSample = celle farget av prøven farge
'RNG = celleområdet som skal behandles (to kolonner)

Dim rngCol2 Som Excel.Range
Dim rngConstants Som Excel.Range
Dim rngFormulas Som Excel.Range
Dim lColorIndex Som Long
Dim MySum Som Double
Dim område som Excel.Range
Dim c Som Excel.Range

ColorConditionSum = False
Hvis ikke typeof cSample Er Excel.Range Deretter Avslutt Funksjon '>>>
lColorIndex = cSample.Interior.ColorIndex

MySum = 0
Satt rngCol2 = Nothing
Hvis typeof RNG Excel.Range Deretter
Hvis rng.Columns.Count <2 Then Exit Function '>>>
On Error Resume Next
For hvert område I rng.Areas
Hvis rngCol2 Er Ingenting Deretter
Satt rngCol2 = area.Columns (2) .SpecialCells (xlCellTypeConstants, 1)
Hvis rngCol2 Er Ingenting Deretter
Satt rngCol2 = area.Columns (2) .SpecialCells (xlCellTypeFormulas, 1)
Else
Satt rngCol2 = Application.Union (_
rngCol2, area.Columns (2) .SpecialCells (xlCellTypeFormulas, 1))
End If
Else
Satt rngCol2 = Application.Union (_
rngCol2, area.Columns (2) .SpecialCells (xlCellTypeConstants, 1))
Satt rngCol2 = Application.Union (_
rngCol2, area.Columns (2) .SpecialCells (xlCellTypeFormulas, 1))
End If
Neste område

For hvert område I rngCol2.Areas
For Hver c I area.Cells
Med c.Offset (0, -1)
Hvis .Interior.ColorIndex = lColorIndex Deretter
MySum = MySum + c.Value
End If
Avslutt med
Neste c
Neste område
End If

ColorConditionSum = MySum
End Function

Du bruker denne funksjonen på følgende måte i et regneark:

= ColorConditionSum (A10, A12: B22)

I dette tilfellet er en celle som har interiøret fargen du ønsker å matche og A12: B22 er celleområdet som skal evalueres. Verdiene er trukket fra den andre kolonnen i området og formateringen blir sjekket på cellene i den første kolonnen.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (3298) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Oppsummering Basert på formatering i tilstøtende celler.