Summa efter färg i Excel

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 -

  1. Användning av SUBTOTAL formel i excel och filter efter färgfunktion.
  2. 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 -

Du kan ladda ner denna summa efter Excel-mall här - Summa efter färg Excel-mall

# 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 enligt nedan.

  • Steg 2: För att nå 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 refererar nummer '9' i argumentet function_num till sumfunktionalitet och referensargumentet ges som det cellområde som ska beräknas. Nedan är skärmdumpen för samma.

  • Steg 3: Som framgår av ovanstående skärmdump har en summering av USD-beloppet beräknats. För att beräkna beloppen 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 diskuteras i exemplet nedan. Tänk på datatabellen för att bättre förstå metoden.

  • Steg 1: Låt oss nu markera listan över 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 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: Nu när dialogrutan för "Definiera namn" dyker upp, ange namnet och formeln för GET.CELL i "Refer to" som visas i skärmdumpen nedan.

Som framgår av ovanstående skärmdump ä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 hänvisar till referenscellen. Klicka nu på 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' 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 man använder metoden med SUBTOTAL-formel 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 den olika kolumnen 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 funktionalitet kan användas för att sammanfatta efter färger för flera färgkoder i cellbakgrunden.