✅ Kuidas arvutada Excelis värvilisi lahtreid (samm-sammuline juhend + VIDEO)

Lang L: none (table-of-contents)

Kuidas arvutada Excelis värvilisi lahtreid

Vaadake videot - kuidas arvutada Excelis värvilisi lahtreid

Kas poleks tore, kui oleks olemas funktsioon, mis loeks Excelis värvilisi lahtreid?

Kahjuks pole selle jaoks ühtegi sisseehitatud funktsiooni.

AGA…

Seda saab hõlpsasti teha.

Kuidas arvutada Excelis värvilisi lahtreid

Selles õpetuses näitan teile kolme võimalust Excelis värviliste rakkude loendamiseks (koos VBA -ga ja ilma):

Filtri ja SUBTOTAL funktsiooni kasutamine
Funktsiooni GET.CELL kasutamine
VBA abil loodud kohandatud funktsiooni kasutamine

#1 Loendage värvilised rakud filtri ja VAHESUMMA abil

Excelis värviliste lahtrite loendamiseks peate kasutama kahte järgmist sammu.

Filtreerige värvilised lahtrid
Kasutage funktsiooni SUBTOTAL, et loendada värvilisi lahtreid, mis on nähtavad (pärast filtreerimist).

Oletame, et teil on allpool näidatud andmekogum:

Selles andmekogumis kasutatakse kahte taustavärvi (roheline ja oranž).

Siin on Excelis värviliste rakkude loendamise sammud:

Andmekogumi all olevates lahtrites kasutage järgmist valemit: = VAHESUMMA (102, E1: E20)
Valige päised.
Avage Andmed -> Sorteeri ja filtreeri -> Filter. See rakendab filtri kõikidele päistele.
Klõpsake mis tahes filtri rippmenüüd.
Minge jaotisse „Filtreeri värvi järgi” ja valige värv. Kuna ülaltoodud andmekogumis on lahtrite esiletõstmiseks kasutatud kahte värvi, näitab filter nende lahtrite filtreerimiseks kahte värvi.

Niipea kui lahtreid filtreerite, märkate, et funktsiooni SUBTOTAL väärtus muutub ja tagastab ainult pärast filtreerimist nähtavate lahtrite arvu.

Kuidas see töötab?

Funktsioon SUBTOTAL kasutab esimese argumendina 102, mida kasutatakse määratud vahemikus nähtavate lahtrite loendamiseks (peidetud ridu ei loeta).

Kui andmed on filtreerimata, tagastab see 19, kuid kui see on filtreeritud, tagastab see ainult nähtavate lahtrite arvu.

Proovige ise… Laadige alla näidisfail

#2 Loendage värvilisi rakke funktsiooni GET.CELL abil

GET.CELL on funktsioon Macro4, mis on ühilduvuse tõttu alles jäetud.

See ei tööta, kui seda kasutatakse töölehel tavaliste funktsioonidena.

Kuid see töötab Exceli nimega vahemikes.

Vaata ka: Lisateave funktsiooni GET.CELL kohta.

Siin on kolm sammu GET.CELL -i kasutamiseks Excelis värviliste rakkude loendamiseks.

Looge nimega vahemik funktsiooni GET.CELL abil
Kasutage veerus värvikoodi saamiseks nimega vahemikku
Värviliste rakkude arvu (värvi järgi) loendamiseks värvinumbri kasutamine

Sukeldume sügavalt ja vaatame, mida teha kõigi kolme nimetatud sammu puhul.

Nimega vahemiku loomine

Avage Valemid -> Määrake nimi.
Sisestage dialoogiboksis Uus nimi:
- Nimi: GetColor
- Reguleerimisala: töövihik
- Viitab: = GET.CELL (38, leht1! $ A2)
  Ülaltoodud valemis olen kasutanud Leht1! $ A2 teise argumendina. Peate kasutama selle veeru viidet, kus teil on taustavärviga lahtrid.

Iga lahtri värvikoodi saamine

Andmetega külgnevas lahtris kasutage valemit = GetColor

See valem tagastaks 0, kui lahtris pole taustavärvi, ja tagastaks kindla arvu, kui taustavärv on olemas.

See arv on värvile omane, nii et kõik sama taustavärviga lahtrid saavad sama numbri.

Loendage värvilised lahtrid värvikoodi abil

Kui järgite ülaltoodud protsessi, oleks teil veerg, mille taustavärvile vastavad numbrid.

Konkreetse värvi loenduse saamiseks tehke järgmist.

Andke kusagil andmestiku all sama taustavärv lahtrile, mida soovite loendada. Veenduge, et teete seda samas veerus, mida kasutasite nimega vahemiku loomisel. Näiteks kasutasin veergu A ja seetõttu kasutan ainult veeru „A” lahtreid.
Kasutage külgnevas lahtris järgmist valemit:

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

See valem annab teile kõigi määratud taustavärviga lahtrite arvu.

Kuidas see töötab?

Funktsioon COUNTIF kasutab kriteeriumidena nimetatud vahemikku (GetColor). Valemis nimetatud vahemik viitab vasakul külgnevale lahtrile (veerus A) ja tagastab selle lahtri värvikoodi. Seetõttu on kriteeriumiks see värvikood.

Funktsioon COUNTIF kasutab vahemikku ($ F $ 2: $ F $ 18), mis hoiab kõigi lahtrite värvikoodide numbreid ja tagastab loendi kriteeriumide arvu alusel.

Proovige ise… Laadige alla näidisfail

#3 Loendage värvilised VBA abil (kohandatud funktsiooni loomine)

Ülaltoodud kahe meetodi abil õppisite värviliste rakkude loendamist ilma VBA -d kasutamata.

Kuid kui teil on VBA kasutamine hea, on see kolmest meetodist lihtsaim.

VBA abil looksime kohandatud funktsiooni, mis töötaks nagu COUNTIF -funktsioon ja tagastaks konkreetse taustavärviga lahtrite arvu.

Siin on kood:

'Sumit Bansali loodud kood saidilt https://trumpexcel.com Funktsioon GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Määra rCell = CountRange iga rge jaoks rCell.Interior.ColorIndex = CountColorValue Siis TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Selle kohandatud funktsiooni loomiseks toimige järgmiselt.

Kui töövihik on aktiivne, vajutage klahvikombinatsiooni Alt + F11 (või paremklõpsake töölehe vahekaarti ja valige Kuva kood). See avab VB redaktori.
Paremklõpsake vasakul paanil töövihiku all, kus te töötate, mõnel töölehel ja valige Lisa -> Moodul. See sisestaks uue mooduli. Kopeerige ja kleepige kood mooduli koodi aknasse.
Topeltklõpsake mooduli nime (vaikimisi mooduli nimi moodulis1) ja kleepige kood koodiaknasse.
Sulgege VB redaktor.
See on kõik! Nüüd on töölehel kohandatud funktsioon nimega GetColorCount.

Selle funktsiooni kasutamiseks kasutage seda lihtsalt tavalise Exceli funktsioonina.

Süntaks: = GetColorCount (CountRange, CountColor)

CountRange: vahemik, milles soovite määratud taustavärviga lahtreid loendada.
CountColor: värv, mille jaoks soovite rakke lugeda.

Selle valemi kasutamiseks kasutage lahtris sama taustavärvi (mida soovite lugeda) ja valemit. CountColor argument oleks sama lahter, kuhu sisestate valemi (nagu allpool näidatud):