Cum se numără celulele colorate în Excel (Ghid pas cu pas + VIDEO)

Vizionați videoclipuri - Cum să numărați celulele colorate în Excel

Nu ar fi minunat dacă ar exista o funcție care ar putea număra celulele colorate în Excel?

Din păcate, nu există nicio funcție încorporată pentru a face acest lucru.

DAR…

Se poate face cu ușurință.

Cum se numără celulele colorate în Excel

În acest tutorial, vă voi arăta trei moduri de a număra celulele colorate în Excel (cu și fără VBA):

1. Folosind funcția Filtru și SUBTOTAL
2. Folosind funcția GET.CELL
3. Folosind o funcție personalizată creată utilizând VBA

# 1 Numărați celulele colorate folosind Filter și SUBTOTAL

Pentru a număra celulele colorate în Excel, trebuie să utilizați următorii doi pași:

• Filtrează celulele colorate
• Utilizați funcția SUBTOTAL pentru a număra celulele colorate care sunt vizibile (după filtrare).

Să presupunem că aveți un set de date așa cum se arată mai jos:

Există două culori de fundal utilizate în acest set de date (verde și portocaliu).

Iată pașii care numără celulele colorate în Excel:

1. În orice celulă de sub setul de date, utilizați următoarea formulă: = SUBTOTAL (102, E1: E20)
   
2. Selectați anteturile.
3. Accesați Date -> Sortare și filtrare -> Filtru. Aceasta va aplica un filtru tuturor anteturilor.
4. Faceți clic pe oricare dintre meniurile drop-down ale filtrului.
5. Accesați „Filtrare după culoare” și selectați culoarea. În setul de date de mai sus, deoarece există două culori utilizate pentru evidențierea celulelor, filtrul arată două culori pentru a filtra aceste celule.

De îndată ce filtrați celulele, veți observa că valoarea din funcția SUBTOTAL se modifică și returnează doar numărul de celule care sunt vizibile după filtrare.

Cum funcționează asta?

Funcția SUBTOTAL utilizează 102 ca prim argument, care este utilizat pentru a număra celulele vizibile (rândurile ascunse nu sunt numărate) în intervalul specificat.

Dacă datele, dacă nu sunt filtrate, returnează 19, dar dacă sunt filtrate, atunci returnează doar numărul de celule vizibile.

Încercați-l singur … Descărcați fișierul de exemplu

# 2 Numărați celulele colorate folosind funcția GET.CELL

GET.CELL este o funcție Macro4 care a fost păstrată din motive de compatibilitate.

Nu funcționează dacă este utilizat ca funcții obișnuite în foaia de lucru.

Cu toate acestea, funcționează în intervale denumite Excel.

Vezi si: Aflați mai multe despre funcția GET.CELL.

Iată cei trei pași pentru a utiliza GET.CELL pentru a număra celulele colorate în Excel:

• Creați un domeniu numit utilizând funcția GET.CELL
• Utilizați gama numită pentru a obține codul de culoare într-o coloană
• Utilizarea numărului de culoare pentru a număra numărul de celule colorate (după culoare)

Să ne scufundăm profund și să vedem ce să facem în fiecare dintre cei trei pași menționați.

Crearea unui domeniu numit

• Accesați Formule -> Definiți numele.
• În caseta de dialog Nume nou, introduceți:
  • Nume: GetColor
  • Domeniu: Caiet de lucru
  • Se referă la: = GET.CELL (38, Sheet1! $ A2)
    În formula de mai sus, am folosit Foaie1! $ A2 ca al doilea argument. Trebuie să utilizați referința coloanei unde aveți celulele cu culoarea de fundal.

Obținerea codului de culoare pentru fiecare celulă

În celula adiacentă datelor, utilizați formula = GetColor

Această formulă ar returna 0 dacă NU există culoare de fundal într-o celulă și ar returna un anumit număr dacă există o culoare de fundal.

Acest număr este specific unei culori, deci toate celulele cu aceeași culoare de fundal primesc același număr.

Numărați celulele colorate folosind codul de culoare

Dacă urmați procesul de mai sus, veți avea o coloană cu numere corespunzătoare culorii de fundal în ea.

Pentru a obține numărul unei anumite culori:

• Undeva sub setul de date, dați aceeași culoare de fundal unei celule pe care doriți să o numărați. Asigurați-vă că faceți acest lucru în aceeași coloană pe care ați folosit-o pentru crearea intervalului numit. De exemplu, am folosit coloana A și, prin urmare, voi folosi numai celulele din coloana „A”.
• În celula alăturată, utilizați următoarea formulă:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Această formulă vă va oferi numărul tuturor celulelor cu culoarea de fundal specificată.

Cum functioneazã?

Funcția COUNTIF folosește ca criteriu intervalul numit (GetColor). Intervalul numit din formulă se referă la celula adiacentă din stânga (în coloana A) și returnează codul de culoare pentru acea celulă. Prin urmare, acest număr de cod de culoare este criteriul.

Funcția COUNTIF folosește intervalul ($ F $ 2: $ F $ 18) care deține numerele codurilor de culoare ale tuturor celulelor și returnează numărul în funcție de numărul criteriilor.

Încercați-l singur … Descărcați fișierul de exemplu

# 3 Număr colorat folosind VBA (prin crearea unei funcții personalizate)

În cele două metode de mai sus, ați învățat cum să numărați celulele colorate fără a utiliza VBA.

Dar, dacă sunteți bine cu utilizarea VBA, aceasta este cea mai ușoară dintre cele trei metode.

Folosind VBA, am crea o funcție personalizată, care ar funcționa ca o funcție COUNTIF și ar returna numărul de celule cu culoarea de fundal specifică.

Iată codul:

'Cod creat de Sumit Bansal din https://trumpexcel.com Funcția GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange Pentru fiecare rCell În CountRange rCell.Interior.ColorIndex = CountColorValue Apoi TotalCount = TotalCount + 1 End Dacă Următorul rCell GetColorCount = TotalCount Funcția de sfârșit

Pentru a crea această funcție personalizată:

• Cu registrul de lucru activ, apăsați Alt + F11 (sau faceți clic dreapta pe fila Foaie de lucru și selectați Vizualizare cod). Aceasta ar deschide editorul VB.
• În panoul din stânga, sub registrul de lucru în care lucrați, faceți clic dreapta pe oricare dintre foile de lucru și selectați Inserați -> Modul. Aceasta ar insera un nou modul. Copiați și lipiți codul în fereastra codului modulului.
• Faceți dublu clic pe numele modulului (în mod implicit numele modulului în Module1) și lipiți codul în fereastra codului.
• Închideți Editorul VB.
• Asta e! Acum aveți o funcție personalizată în foaia de lucru numită GetColorCount.

Pentru a utiliza această funcție, pur și simplu folosiți-o ca orice funcție excel obișnuită.

Sintaxă: = GetColorCount (CountRange, CountColor)

• CountRange: intervalul în care doriți să numărați celulele cu culoarea de fundal specificată.
• CountColor: culoarea pentru care doriți să numărați celulele.

Pentru a utiliza această formulă, utilizați aceeași culoare de fundal (pe care doriți să o numărați) într-o celulă și utilizați formula. Argumentul CountColor ar fi aceeași celulă în care introduceți formula (așa cum se arată mai jos):

Notă: Deoarece există un cod în registrul de lucru, salvați-l cu o extensie .xls sau .xlsm.

Încercați-l singur … Descărcați fișierul de exemplu

Știți vreo altă modalitate de a număra celulele colorate în Excel?

Dacă da, împărtășiți-o mie lăsând un comentariu.