Hvordan summeres etter farge i Excel? (2 nyttige metoder)
De to beste metodene for å summere etter farger i Excel er som følger -
- Bruk av SUBTOTAL formel i Excel og filter etter fargefunksjon.
- Bruk av GET.CELL-formelen ved å definere navnet i formelfanen og bruke SUMIF-formelen i excel for å oppsummere verdiene etter fargekoder.
La oss diskutere hver av dem i detalj -
# 1 - Sum etter farge ved hjelp av subtotalfunksjon
For å forstå tilnærmingen for beregning av summen av verdier etter bakgrunnsfarger, la oss se på datatabellen nedenfor, som gir detaljer om beløp i amerikanske dollar etter region og måned.
- Trinn 1: Anta at vi vil markere de cellene som har negative verdier for indikasjonsformål, dette kan oppnås ved å enten bruke betinget formatering eller ved å markere cellene manuelt, som vist nedenfor.
- Trinn 2: Nå, for å oppnå summen av celler som er farget i excel, skriv inn formelen for SUBTOTAL under datatabellen. Syntaksen for SUBTOTAL- formelen er vist nedenfor.
Formelen som er angitt for å beregne summeringen er
= SUBTOTAL (9, D2: D13)
Her refererer nummer '9' i funksjon_num-argumentet til sumfunksjonalitet , og referanse-argumentet er gitt som celleområdet som skal beregnes. Nedenfor er skjermbildet for det samme.
- Trinn 3: Som det fremgår av skjermbildet ovenfor, er det beregnet en summering av USD-beløpet for å beregne beløpene som er uthevet i en lys rød bakgrunnsfarge. Bruk filter på datatabellen ved å gå til Data og deretter velge et filter.
- Trinn 4: Velg deretter filter etter farge og velg den lyse røde cellefargen under "Filtrer etter cellefarge." Nedenfor er skjermbildet for å bedre beskrive filteret.
- Trinn 5: Når excel-filteret er brukt, vil datatabellen bli filtrert for bare lysrøde bakgrunnsceller, og den delsummerformelen som er brukt nederst i datatabellen, viser summeringen av de fargede cellene, som er filtrert som vist under.
Som vist i skjermbildet ovenfor oppnås beregningen av den fargede cellen i celle E17, delsumformel.
# 2 - Sum etter farge ved hjelp av Get.Cell-funksjonen
Den andre tilnærmingen forklares for å komme til summen av fargecellene i excel, som diskutert i eksemplet nedenfor. Vurder datatabellen for bedre å forstå metodikken.
- Trinn 1: La oss nå markere listen over celler i kolonnen USD Beløp, som vi er villige til å komme frem til ønsket sum av fargede celler, som vist nedenfor.
- Trinn 2: Som vi kan se i skjermbildet ovenfor, i motsetning til i det første eksemplet her, har vi flere farger. Dermed vil vi bruke formelen = GET.CELL ved å definere den i navnefeltet og ikke bruke den direkte i Excel.
- Trinn 3: Nå, når dialogboksen for "Definer navn" dukker opp, skriv inn navnet og formelen som glemmer. CELL i "Henvis til" som vist på skjermbildet nedenfor.
Som vist på skjermbildet ovenfor, er navnet som er angitt for funksjonen 'CellColor', og formelen = GET.CELL (38, 'Eksempel 2! $ D2) skal angis i' Henviser til. ' Innenfor formelen henviser tallet 38 til cellekodeinformasjonen, og det andre argumentet er cellenummeret D2 refererer til referansecellen. Klikk nå, OK.
- Trinn 4: Skriv inn funksjonen Navn 'CellColor' i cellen ved siden av den fargekodede cellen, som ble definert i dialogboksen, som forklart i trinn 3.
Som det fremgår av skjermbildet ovenfor, angis funksjonen 'CellColor' , som returnerer fargekoden for bakgrunnscellefargen.
Tilsvarende dras formelen for hele kolonnen.
- Trinn 5: Nå, for å komme til summen av verdiene etter farger i excel, vil vi gå inn i SUMIF-formelen. Syntaksen for SUMIF-formelen er som følger: -
Som det fremgår av skjermbildet ovenfor, legges følgende argumenter inn i SUMIF-formelen: -
- Områdeargumentet legges inn for celleområde E2: E13.
- Kriteriene er angitt som G2, hvis oppsummerte verdier er nødvendige for å bli hentet.
- Celleområdet angis for å være D2: D13.
SUMIF-formelen blir dratt ned for alle fargekodetallene som verdiene skal legges sammen for.
Ting å huske
- Mens du bruker fremgangsmåten med SUBTOTAL-formelen, lar denne funksjonaliteten brukerne filtrere for bare en filterfarge om gangen. Videre kan denne funksjonaliteten brukes til å legge til bare en kolonne med verdier etter filterfarger. Hvis det er mer enn en kolonne med forskjellige farger etter rader i de forskjellige kolonnene, kan SUBTOTAL bare vise det riktige resultatet for ett filter etter farge til en bestemt kolonne.
- GET.CELL-formelen, i kombinasjon med SUMIF-tilnærmingen, eliminerer denne begrensningen, da denne funksjonaliteten kan brukes til å oppsummere etter farger for flere fargekoder i cellebakgrunnen.
