Krāsaino šūnu saskaitīšana programmā Excel (soli pa solim + VIDEO)

Noskatieties video - kā saskaitīt krāsainās šūnas programmā Excel

Vai nebūtu lieliski, ja būtu funkcija, kas programmā Excel varētu saskaitīt krāsainās šūnas?

Diemžēl nav nevienas iebūvētas funkcijas, lai to izdarītu.

BET…

To var viegli izdarīt.

Kā saskaitīt krāsainās šūnas programmā Excel

Šajā apmācībā es parādīšu trīs veidus, kā saskaitīt krāsainās šūnas programmā Excel (ar un bez VBA):

1. Filtra un SUBTOTAL funkcijas izmantošana
2. Izmantojot funkciju GET.CELL
3. Izmantojot pielāgotu funkciju, kas izveidota, izmantojot VBA

#1 Saskaitiet krāsainās šūnas, izmantojot filtru, un SUBTOTAL

Lai saskaitītu krāsainās šūnas programmā Excel, jums jāveic šādas divas darbības:

• Filtrējiet krāsainas šūnas
• Izmantojiet funkciju SUBTOTAL, lai saskaitītu redzamās krāsainās šūnas (pēc filtrēšanas).

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Šajā datu kopā tiek izmantotas divas fona krāsas (zaļa un oranža).

Tālāk ir norādītas darbības, kurās programmā Excel tiek skaitītas krāsainās šūnas.

1. Jebkurā šūnā, kas atrodas zem datu kopas, izmantojiet šādu formulu: = SUBTOTAL (102, E1: E20)
   
2. Atlasiet galvenes.
3. Dodieties uz Dati -> Kārtot un filtrēt -> Filtrēt. Tādējādi visām galvenēm tiks piemērots filtrs.
4. Noklikšķiniet uz jebkura filtra nolaižamā saraksta.
5. Dodieties uz “Filtrēt pēc krāsas” un atlasiet krāsu. Iepriekš minētajā datu kopā, tā kā šūnu izcelšanai tiek izmantotas divas krāsas, filtrs parāda divas krāsas, lai filtrētu šīs šūnas.

Tiklīdz filtrējat šūnas, pamanīsit, ka funkcija SUBTOTAL mainās un atgriež tikai pēc filtrēšanas redzamo šūnu skaitu.

Kā tas darbojas?

Funkcija SUBTOTAL izmanto 102 kā pirmo argumentu, kas tiek izmantots, lai saskaitītu redzamās šūnas (slēptās rindas netiek skaitītas) norādītajā diapazonā.

Ja dati netiek filtrēti, tie atgriež 19, bet, ja tie ir filtrēti, tad tiek atgriezts tikai redzamo šūnu skaits.

Izmēģiniet pats … Lejupielādējiet parauga failu

#2 Saskaitiet krāsainās šūnas, izmantojot funkciju GET.CELL

GET.CELL ir funkcija Macro4, kas ir saglabāta saderības iemeslu dēļ.

Tas nedarbojas, ja darblapā to izmanto kā regulāras funkcijas.

Tomēr tas darbojas programmā Excel nosauktajos diapazonos.

Skatīt arī: Uzziniet vairāk par funkciju GET.CELL.

Tālāk ir norādītas trīs darbības, lai izmantotu GET.CELL, lai saskaitītu krāsainās šūnas programmā Excel:

• Izveidojiet nosaukumu diapazonu, izmantojot funkciju GET.CELL
• Izmantojiet nosaukumu diapazonu, lai kolonnā iegūtu krāsu kodu
• Krāsu skaitļa izmantošana, lai skaitītu krāsaino šūnu skaitu (pēc krāsas)

Iedziļināsimies un redzēsim, ko darīt katrā no trim minētajiem soļiem.

Nosaukta diapazona izveide

• Dodieties uz Formulas -> Definēt vārdu.
• Dialoglodziņā Jauns nosaukums ievadiet:
  • Nosaukums: GetColor
  • Darbības joma: darba grāmata
  • Attiecas uz: = GET.CELL (38, 1. lapa! $ A2)
    Iepriekšminētajā formulā es izmantoju 1. lapa! $ A2 kā otrais arguments. Jums jāizmanto kolonnas atsauce, kurā ir šūnas ar fona krāsu.

Krāsu koda iegūšana katrai šūnai

Šūnā blakus datiem izmantojiet formulu = GetColor

Šī formula atgriezīs 0, ja šūnā nav fona krāsas, un atgriezīs noteiktu skaitli, ja ir fona krāsa.

Šis skaitlis ir raksturīgs krāsai, tāpēc visas šūnas ar vienādu fona krāsu iegūst vienādu skaitli.

Saskaitiet krāsainās šūnas, izmantojot krāsu kodu

Ja sekojat iepriekš minētajam procesam, jums būs kolonna ar cipariem, kas atbilst fona krāsai.

Lai noteiktu konkrētas krāsas skaitu:

• Kaut kur zem datu kopas norādiet to pašu fona krāsu šūnai, kuru vēlaties saskaitīt. Pārliecinieties, ka to darāt tajā pašā slejā, kuru izmantojāt, veidojot nosaukto diapazonu. Piemēram, es izmantoju A sleju, un tāpēc es izmantošu tikai šūnas A slejā.
• Blakus esošajā šūnā izmantojiet šādu formulu:

= COUNTIF (2 ASV dolāri: 20 ASV dolāri, GetColor)

Šī formula sniegs jums visu šūnu skaitu ar norādīto fona krāsu.

Kā tas darbojas?

Funkcija COUNTIF kā kritēriju izmanto nosaukto diapazonu (GetColor). Formulā nosauktais diapazons attiecas uz blakus esošo šūnu kreisajā pusē (A slejā) un atgriež šīs šūnas krāsas kodu. Tādējādi šis krāsu koda numurs ir kritērijs.

Funkcija COUNTIF izmanto diapazonu ($ F $ 2: $ F $ 18), kas satur visu šūnu krāsu koda numurus un atgriež skaitli, pamatojoties uz kritēriju numuru.

Izmēģiniet pats … Lejupielādējiet parauga failu

#3 Saskaitiet krāsas, izmantojot VBA (izveidojot pielāgotu funkciju)

Iepriekš minētajās divās metodēs jūs uzzinājāt, kā skaitīt krāsainās šūnas, neizmantojot VBA.

Bet, ja jums ir labi izmantot VBA, šī ir vienkāršākā no trim metodēm.

Izmantojot VBA, mēs izveidotu pielāgotu funkciju, kas darbotos kā funkcija COUNTIF un atgrieztu šūnu skaitu ar konkrētu fona krāsu.

Šeit ir kods:

'Kods, ko izveidojis Sumits Bansals no vietnes https://trumpexcel.com Funkcija GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue Kā Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Iestatīt rCell = CountRangeRangeRangeRange rCell.Interior.ColorIndex = CountColorValue Tad TotalCount = TotalCount + 1 beigas, ja nākamā rCell GetColorCount = TotalCount beigu funkcija

Lai izveidotu šo pielāgoto funkciju:

• Kad darbgrāmata ir aktīva, nospiediet taustiņu kombināciju Alt + F11 (vai ar peles labo pogu noklikšķiniet uz darblapas cilnes un atlasiet Skatīt kodu). Tādējādi tiks atvērts VB redaktors.
• Kreisajā rūtī zem darbgrāmatas, kurā strādājat, ar peles labo pogu noklikšķiniet uz jebkuras darblapas un atlasiet Ievietot -> Modulis. Tas ievietos jaunu moduli. Kopējiet un ielīmējiet kodu moduļa koda logā.
• Veiciet dubultklikšķi uz moduļa nosaukuma (pēc noklusējuma moduļa nosaukums modulī 1) un ielīmējiet kodu koda logā.
• Aizveriet VB redaktoru.
• Tieši tā! Tagad darblapā ir pielāgota funkcija ar nosaukumu GetColorCount.

Lai izmantotu šo funkciju, vienkārši izmantojiet to kā jebkuru parastu Excel funkciju.

Sintakse: = GetColorCount (CountRange, CountColor)

• CountRange: diapazons, kurā vēlaties saskaitīt šūnas ar norādīto fona krāsu.
• CountColor: krāsa, kurai vēlaties saskaitīt šūnas.

Lai izmantotu šo formulu, šūnā izmantojiet to pašu fona krāsu (kuru vēlaties saskaitīt) un izmantojiet formulu. CountColor arguments būtu tā pati šūna, kurā ievadāt formulu (kā parādīts zemāk):

Piezīme: Tā kā darbgrāmatā ir kods, saglabājiet to ar paplašinājumu .xls vai .xlsm.

Izmēģiniet pats … Lejupielādējiet parauga failu

Vai jūs zināt kādu citu veidu, kā saskaitīt krāsainās šūnas programmā Excel?

Ja jā, dalieties tajā ar mani, atstājot komentāru.