Cum se sumează în funcție de culoare în Excel? (2 metode utile)
Cele mai bune 2 metode de sumă prin culori în Excel sunt după cum urmează -
- Utilizarea formulei SUBTOTAL în excel și funcția de filtrare după culoare.
- Aplicarea formulei GET.CELL prin definirea numelui în fila formulă și aplicarea formulei SUMIF în excel pentru a rezuma valorile prin coduri de culoare.
Să discutăm fiecare dintre ele în detaliu -
# 1 - Suma prin culoare folosind funcția subtotală
Pentru a înțelege abordarea pentru calcularea sumei valorilor în funcție de culorile de fundal, să luăm în considerare tabelul de date de mai jos, care oferă detalii despre sumele în dolari SUA în funcție de regiune și lună.
- Pasul 1: Să presupunem că am dori să evidențiem acele celule care au valori negative în scopul indicării, acest lucru poate fi realizat fie prin aplicarea formatării condiționate, fie prin evidențierea manuală a celulelor, așa cum se arată mai jos.
- Pasul 2: Acum, pentru a obține suma de celule care sunt colorate în excel, introduceți formula pentru SUBTOTAL sub tabelul de date. Sintaxa pentru formula SUBTOTAL este prezentată mai jos.
Formula introdusă pentru a calcula suma este
= SUBTOTAL (9, D2: D13)
Aici numărul '9' din argumentul function_num se referă la funcționalitatea sumă , iar argumentul de referință este dat ca intervalul de celule care trebuie calculate. Mai jos este captura de ecran pentru același lucru.
- Pasul 3: După cum se vede în captura de ecran de mai sus, a fost calculată o însumare a sumei în USD pentru a calcula sumele evidențiate într-o culoare de fundal roșu deschis. Aplicați filtrul la tabelul de date accesând Date, apoi selectând un filtru.
- Pasul 4: apoi selectați filtrul după culoare și alegeți culoarea roșie deschisă a celulei sub „Filtrare după culoarea celulei”. Mai jos este captura de ecran pentru a descrie mai bine filtrul.
- Pasul 5: Odată ce filtrul excel a fost aplicat, tabelul de date va fi filtrat numai pentru celulele de fundal roșu deschis, iar formula subtotală aplicată în partea de jos a tabelului de date ar afișa însumarea celulelor colorate, care sunt filtrate așa cum se arată de mai jos.
După cum se arată în captura de ecran de mai sus, calculul celulei colorate este realizat în celula E17, formula subtotală.
# 2 - Suma prin culoare folosind funcția Get.Cell
A doua abordare este explicată pentru a ajunge la suma celulelor de culoare în excel, așa cum sa discutat în exemplul de mai jos. Luați în considerare tabelul de date pentru a înțelege mai bine metodologia.
- Pasul 1: Acum, permiteți-ne să evidențiem lista de celule din coloana Suma USD, pe care suntem dispuși să ajungem la suma dorită de celule colorate, așa cum se arată mai jos.
- Pasul 2: După cum putem vedea în captura de ecran de mai sus, spre deosebire de primul exemplu de aici, avem mai multe culori. Prin urmare, vom folosi formula = GET.CELL definind-o în caseta de nume și nu folosind-o direct în Excel.
- Pasul 3: Acum, odată ce apare caseta de dialog pentru „Definiți numele”, introduceți numele și formula uitând .CELULĂ în „Consultați” așa cum se arată în captura de ecran de mai jos.
După cum se vede în captura de ecran de mai sus, numele introdus pentru funcția este „CellColor”, iar formula = GET.CELL (38, „Exemplul 2! $ D2) trebuie introdusă în„ Se referă la ”. În cadrul formulei, numărul 38 se referă la informațiile despre codul celulei, iar al doilea argument este numărul celulei D2 se referă la celula de referință. Acum faceți clic pe OK.
- Pasul 4: Acum introduceți funcția Nume „CellColor” în celula de lângă celula codificată prin culoare, care a fost definită în caseta de dialog, așa cum se explică la pasul 3.
După cum se poate vedea în captura de ecran de mai sus, este introdusă funcția „CellColor” , care returnează codul de culoare pentru culoarea celulei de fundal.
În mod similar, formula este trasă pentru întreaga coloană.
- Pasul 5: Acum, pentru a ajunge la suma valorilor după culori în excel, vom introduce formula SUMIF. Sintaxa pentru formula SUMIF este următoarea: -
După cum se poate vedea din captura de ecran de mai sus, următoarele argumente sunt introduse în formula SUMIF: -
- Argumentul interval este introdus pentru intervalul de celule E2: E13.
- Criteriile sunt introduse ca G2, ale căror valori rezumate sunt necesare pentru a fi recuperate.
- Gama de celule este introdusă pentru a fi D2: D13.
Formula SUMIF este trasă în jos pentru toate numerele de cod de culoare pentru care valorile trebuie adăugate împreună.
Lucruri de amintit
- Folosind formula abordării prin SUBTOTAL, această funcționalitate permite utilizatorilor să filtreze pentru o singură culoare de filtru la un moment dat. Mai mult, această funcționalitate poate fi utilizată pentru a adăuga o singură coloană de valori prin culorile filtrului. Dacă există mai multe coloane cu culori diferite pe rânduri în diferite coloane, SUBTOTALUL poate afișa doar rezultatul corect pentru un filtru în funcție de culoare pentru o anumită coloană.
- Formula GET.CELL, în combinație cu abordarea SUMIF, elimină această limitare, deoarece această funcționalitate ar putea fi utilizată pentru a rezuma pe culori pentru mai multe coduri de culori în fundalul celulei.
