Hvordan opsummeres efter farve i Excel? (2 nyttige metoder)
De to bedste metoder til at opsummere efter farver i Excel er som følger -
- Brug af SUBTOTAL formel i excel og filter efter farvefunktion.
- Anvendelse af GET.CELL-formel ved at definere navnet i formelfanen og anvende SUMIF-formlen i excel for at opsummere værdierne efter farvekoder.
Lad os diskutere hver af dem i detaljer -
# 1 - Sum efter farve ved hjælp af subtotalfunktion
For at forstå fremgangsmåden til beregning af summen af værdier efter baggrundsfarver, lad os overveje nedenstående datatabel, som indeholder oplysninger om beløb i US $ efter region og måned.
- Trin 1: Antag, at vi gerne vil fremhæve de celler, der har negative værdier til indikationsformål, dette kan opnås ved enten at anvende betinget formatering eller ved at fremhæve cellerne manuelt, som vist nedenfor.
- Trin 2: For at opnå summen af celler, der er farvet i excel, skal du indtaste formlen for SUBTOTAL under datatabellen. Syntaksen for SUBTOTAL- formlen er vist nedenfor.
Formlen, der indtastes for at beregne summeringen er
= SUBTOTAL (9, D2: D13)
Her henviser nummer '9' i argumentet function_num til sumfunktionalitet , og referenceargumentet er angivet som det celleområde, der skal beregnes. Nedenfor er skærmbilledet for det samme.
- Trin 3: Som det ses i ovenstående skærmbillede, er en summering af USD-beløbet beregnet for at beregne de beløb, der er fremhævet i en lys rød baggrundsfarve. Anvend filter på datatabellen ved at gå til Data og derefter vælge et filter.
- Trin 4: Vælg derefter filter efter farve, og vælg den lyserøde cellefarve under 'Filtrer efter cellefarve.' Nedenfor er skærmbilledet for bedre at beskrive filteret.
- Trin 5: Når excel-filteret er blevet anvendt, filtreres datatabellen kun for lysrøde baggrundsceller, og den subtotale formel, der anvendes i bunden af datatabellen, viser summeringen af de farvede celler, som filtreres som vist under.
Som vist i ovenstående skærmbillede opnås beregningen af den farvede celle i celle E17, subtotalformel.
# 2 - Sum efter farve ved hjælp af Get.Cell-funktion
Den anden tilgang forklares for at nå frem til summen af farvecellerne i excel, som diskuteret i nedenstående eksempel. Overvej datatabellen for bedre at forstå metoden.
- Trin 1: Lad os nu fremhæve listen over celler i kolonnen USD-beløb, som vi er villige til at nå frem til den ønskede sum af farvede celler, som vist nedenfor.
- Trin 2: Som vi kan se i ovenstående skærmbillede, i modsætning til det første eksempel her, har vi flere farver. Dermed bruger vi formlen = GET.CELL ved at definere den i navnefeltet og ikke bruge den direkte i Excel.
- Trin 3: Når først dialogboksen for 'Definer navn' dukker op, skal du indtaste navnet og formlen glemme. CELL i 'Henvis til' som vist i nedenstående skærmbillede.
Som det ses i ovenstående skærmbillede er det indtastede navn for funktionen 'CellColor', og formlen = GET.CELL (38, 'Eksempel 2! $ D2) skal indtastes i' Henviser til. ' Inden for formlen henviser tallet 38 til cellekodeoplysningerne, og det andet argument er cellenummeret D2 henviser til referencecellen. Klik nu, OK.
- Trin 4: Indtast nu funktionen Navn 'CellColor' i cellen ved siden af den farvekodede celle, som blev defineret i dialogboksen som forklaret i trin 3.
Som det kan ses i ovenstående skærmbillede indtastes funktionen 'CellColor' , som returnerer farvekoden for baggrundscellefarven.
Tilsvarende trækkes formlen for hele kolonnen.
- Trin 5: For at nå frem til summen af værdierne efter farver i excel skal vi indtaste SUMIF-formlen. Syntaksen for SUMIF-formlen er som følger: -
Som det kan ses fra ovenstående skærmbillede, indtastes følgende argumenter i SUMIF-formlen: -
- Områdeargumentet indtastes for celleområde E2: E13.
- Kriterierne indtastes som G2, hvis opsummerede værdier er nødvendige for at blive hentet.
- Celleområdet er angivet til at være D2: D13.
SUMIF-formlen trækkes ned for alle de farvekodetal, for hvilke værdier skal tilføjes.
Ting at huske
- Mens man bruger metoden med SUBTOTAL-formel, giver denne funktionalitet brugerne mulighed for kun at filtrere efter en filterfarve ad gangen. Desuden kan denne funktion bruges til kun at tilføje en kolonne med værdier efter filterfarver. Hvis der er mere end en kolonne med forskellige farver efter rækker i de forskellige kolonner, viser SUBTOTAL muligvis kun det korrekte resultat for et filter efter farve til en bestemt kolonne.
- GET.CELL-formlen, i kombination med SUMIF-tilgangen, eliminerer denne begrænsning, da denne funktionalitet kan bruges til at opsummere efter farver for flere farvekoder i cellebaggrunden.
