Hur summerar man efter färg i Excel? (2 användbara metoder)
De två bästa metoderna att summera efter färger i Excel är som följer -
- Användning av SUBTOTAL formel i excel och filter efter färgfunktion.
- Tillämpa GET.CELL-formeln genom att definiera namnet på formelfliken och använda SUMIF-formeln i excel för att sammanfatta värdena efter färgkoder.
Låt oss diskutera var och en av dem i detalj -
# 1 - Summa efter färg med hjälp av delsumma
För att förstå metoden för att beräkna summan av värden efter bakgrundsfärger, låt oss överväga nedanstående datatabell, som ger information om belopp i US $ per region och månad.
- Steg 1: Antag att vi vill markera de celler som har negativa värden för indikationsändamål, detta kan uppnås genom att antingen tillämpa villkorlig formatering eller genom att markera cellerna manuellt, som visas nedan.
- Steg 2: För att uppnå summan av celler som är färgade i excel, ange formeln för SUBTOTAL under datatabellen. Syntaxen för SUBTOTAL- formeln visas nedan.
Formeln som anges för att beräkna summeringen är
= SUBTOTAL (9, D2: D13)
Här hänvisar nummer '9' i argumentet function_num till sumfunktionalitet , och referensargumentet ges som cellintervallet som ska beräknas. Nedan är skärmdumpen för samma.
- Steg 3: Som framgår av skärmbilden ovan har en summering av USD-beloppet beräknats för att beräkna beloppen som är markerade i en ljusröd bakgrundsfärg. Använd filter i datatabellen genom att gå till Data och sedan välja ett filter.
- Steg 4: Välj sedan filter efter färg och välj den ljusröda cellfärgen under "Filtrera efter cellfärg." Nedan är skärmdumpen för att bättre beskriva filtret.
- Steg 5: När excel-filtret har applicerats filtreras datatabellen endast för ljusröda bakgrundsceller, och delsummaformeln som används längst ner i datatabellen visar summeringen av de färgade cellerna, som filtreras som visas Nedan.
Som visas i skärmbilden ovan uppnås beräkningen av den färgade cellen i cell E17, delsummaformel.
# 2 - Summa efter färg med Get.Cell-funktionen
Det andra tillvägagångssättet förklaras för att komma fram till summan av färgcellerna i excel, såsom diskuteras i exemplet nedan. Tänk på datatabellen för att bättre förstå metoden.
- Steg 1: Låt oss nu markera listan med celler i kolumnen USD Belopp, som vi är villiga att nå fram till önskad summa av färgade celler, som visas nedan.
- Steg 2: Som vi kan se i ovanstående skärmdump, till skillnad från i det första exemplet här, har vi flera färger. Därmed kommer vi att använda formeln = GET.CELL genom att definiera den i namnrutan och inte direkt använda den i Excel.
- Steg 3: När dialogrutan för "Definiera namn" dyker upp, ange namnet och formeln att glömma. CELL i "Se till" som visas på skärmbilden nedan.
Som framgår av skärmbilden ovan är namnet som anges för funktionen 'CellColor' och formeln = GET.CELL (38, 'Exempel 2! $ D2) ska anges i' Hänvisar till. ' Inom formeln hänvisar siffran 38 till cellkodinformationen, och det andra argumentet är cellnumret D2 refererar till referenscellen. Klicka nu, OK.
- Steg 4: Ange nu funktionen Namn 'CellColor' i cellen bredvid den färgkodade cellen, som definierades i dialogrutan, som förklaras i steg 3.
Som framgår av skärmbilden ovan matas funktionen 'CellColor' in, som returnerar färgkoden för bakgrundsfärgen.
På samma sätt dras formeln för hela kolumnen.
- Steg 5: För att nå summan av värdena efter färger i excel kommer vi att ange SUMIF-formeln. Syntaxen för SUMIF-formeln är som följer: -
Som framgår av ovanstående skärmdump anges följande argument i SUMIF-formeln: -
- Områdesargumentet anges för cellintervall E2: E13.
- Kriterierna anges som G2, vars sammanfattade värden behövs för att hämtas.
- Celleområdet anges som D2: D13.
SUMIF-formeln dras ner för alla färgkodnummer för vilka värden ska läggas till.
Saker att komma ihåg
- Medan metoden med SUBTOTAL-formeln används tillåter denna funktionalitet användarna att bara filtrera efter en filterfärg åt gången. Dessutom kan denna funktion användas för att bara lägga till en kolumn med värden efter filterfärger. Om det finns mer än en kolumn med olika färger efter rader i de olika kolumnerna, kan SUBTOTAL endast visa rätt resultat för ett filter efter färg till en specifik kolumn.
- GET.CELL-formeln, i kombination med SUMIF-metoden, eliminerar denna begränsning, eftersom denna funktion kan användas för att sammanfatta efter färger för flera färgkoder i cellbakgrunden.
