Kaip skaičiuoti spalvotus langelius „Excel“ (žingsnis po žingsnio vadovas + VIDEO)

Žiūrėti vaizdo įrašą - kaip skaičiuoti spalvotus langelius „Excel“

Ar nebūtų puiku, jei būtų funkcija, galinti suskaičiuoti spalvotus langelius „Excel“?

Deja, nėra jokios integruotos funkcijos tai padaryti.

BET…

Tai galima lengvai padaryti.

Kaip skaičiuoti spalvotus langelius „Excel“

Šioje pamokoje parodysiu tris būdus, kaip skaičiuoti spalvotus langelius „Excel“ (su VBA ir be jos):

1. Naudojant filtrą ir SUBTOTAL funkciją
2. Naudojant GET.CELL funkciją
3. Naudojant pasirinktinę funkciją, sukurtą naudojant VBA

#1 Skaičiuokite spalvotus langelius naudodami filtrą ir SUBTOTAL

Norėdami skaičiuoti spalvotus langelius „Excel“, turite atlikti šiuos du veiksmus:

• Filtruokite spalvotas ląsteles
• Naudokite funkciją SUBTOTAL, kad suskaičiuotumėte matomas spalvotas ląsteles (po filtravimo).

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Šiame duomenų rinkinyje naudojamos dvi fono spalvos (žalia ir oranžinė).

Štai „Excel“ spalvotų ląstelių skaičiavimo veiksmai:

1. Bet kuriame langelyje žemiau duomenų rinkinio naudokite šią formulę: = SUBTOTAL (102, E1: E20)
   
2. Pasirinkite antraštes.
3. Eikite į Duomenys -> Rūšiuoti ir filtruoti -> Filtruoti. Tai pritaikys filtrą visoms antraštėms.
4. Spustelėkite bet kurį išskleidžiamąjį filtro meniu.
5. Eikite į „Filtruoti pagal spalvą“ ir pasirinkite spalvą. Ankstesniame duomenų rinkinyje, kadangi ląstelėms paryškinti naudojamos dvi spalvos, filtras rodo dvi spalvas šioms ląstelėms filtruoti.

Kai tik filtruosite langelius, pastebėsite, kad funkcija SUBTOTAL keičiasi ir grąžina tik ląstelių skaičių, kuris matomas po filtravimo.

Kaip tai veikia?

Funkcija SUBTOTAL naudoja 102 kaip pirmąjį argumentą, kuris naudojamas skaičiuoti matomas langelius (paslėptos eilutės neskaičiuojamos) nurodytame diapazone.

Jei duomenys nefiltruoti, jie grąžina 19, bet jei jie yra filtruojami, jie grąžina tik matomų langelių skaičių.

Išbandykite patys … Atsisiųskite pavyzdinį failą

#2 Skaičiuokite spalvotas ląsteles naudodami funkciją GET.CELL

„GET.CELL“ yra „Macro4“ funkcija, kuri buvo išsaugota dėl suderinamumo priežasčių.

Jis neveikia, jei naudojamas kaip įprastos funkcijos darbalapyje.

Tačiau jis veikia „Excel“ pavadintuose diapazonuose.

Taip pat žr: Sužinokite daugiau apie GET.CELL funkciją.

Štai trys veiksmai, kaip naudoti „GET.CELL“, norint suskaičiuoti spalvotus langelius „Excel“:

• Sukurkite pavadintą diapazoną naudodami funkciją GET.CELL
• Naudokite pavadintą diapazoną, kad gautumėte spalvų kodą stulpelyje
• Spalvotų ląstelių skaičiavimas (pagal spalvą) naudojant spalvų skaičių

Pasinerkime giliai ir pažiūrėkime, ką daryti kiekviename iš trijų paminėtų žingsnių.

Pavadinto diapazono kūrimas

• Eikite į Formulės -> Apibrėžkite vardą.
• Dialogo lange Naujas vardas įveskite:
  • Pavadinimas: „GetColor“
  • Taikymo sritis: darbo knyga
  • Nurodo: = GET.CELL (38, Sheet1! $ A2)
    Aukščiau pateiktoje formulėje aš naudojau 1 lapas! $ A2 kaip antras argumentas. Turite naudoti stulpelio, kuriame yra langeliai su fono spalva, nuorodą.

Kiekvienos ląstelės spalvos kodo gavimas

Ląstelėje šalia duomenų naudokite formulę = „GetColor“

Ši formulė grąžintų 0, jei ląstelėje nebūtų fono spalvos, ir grąžintų konkretų skaičių, jei yra fono spalva.

Šis skaičius būdingas spalvai, todėl visos ląstelės su ta pačia fono spalva gauna tą patį skaičių.

Skaičiuokite spalvotas ląsteles naudodami spalvų kodą

Jei atliksite aukščiau aprašytą procesą, turėsite stulpelį su skaičiais, atitinkančiais fono spalvą.

Norėdami sužinoti konkrečios spalvos skaičių:

• Kažkur po duomenų rinkiniu pateikite tą pačią fono spalvą langeliui, kurį norite suskaičiuoti. Įsitikinkite, kad tai darote tame pačiame stulpelyje, kurį naudojote kurdami pavadintą diapazoną. Pavyzdžiui, aš naudoju A stulpelį, todėl naudoju tik „A“ stulpelio langelius.
• Gretimoje ląstelėje naudokite šią formulę:

= COUNTIF (2 USD: 2 USD: 20 USD, „GetColor“)

Ši formulė parodys visų langelių skaičių su nurodyta fono spalva.

Kaip tai veikia?

Funkcija COUNTIF kaip kriterijų naudoja įvardytą diapazoną („GetColor“). Pavadintas diapazonas formulėje nurodo gretimą langelį kairėje (A stulpelyje) ir grąžina tos ląstelės spalvos kodą. Taigi šis spalvos kodo numeris yra kriterijus.

Funkcija COUNTIF naudoja diapazoną ($ F $ 2: $ F $ 18), kuriame yra visų langelių spalvų kodų numeriai ir pateikiamas pagal kriterijų skaičių.

Išbandykite patys … Atsisiųskite pavyzdinį failą

#3 Skaičiuokite spalvotai naudodami VBA (sukurdami pasirinktinę funkciją)

Naudodamiesi dviem aukščiau nurodytais metodais, jūs sužinojote, kaip skaičiuoti spalvotas ląsteles nenaudojant VBA.

Bet jei jums gerai sekasi naudoti VBA, tai yra lengviausias iš trijų metodų.

Naudodami VBA sukurtume pasirinktinę funkciją, kuri veiktų kaip COUNTIF funkcija ir grąžintų ląstelių skaičių su konkrečia fono spalva.

Čia yra kodas:

„Kodas, kurį sukūrė Sumit Bansal iš https://trumpexcel.com Funkcija„ GetColorCount “(„ ​​CountRange As Range “,„ CountColor As Range “) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Nustatykite„ rCell = CountRange “kiekvienam r rCell.Interior.ColorIndex = CountColorValue Tada TotalCount = TotalCount + 1 pabaiga, jei kita rCell GetColorCount = TotalCount pabaigos funkcija

Norėdami sukurti šią pasirinktinę funkciją:

• Kai jūsų darbaknygė aktyvi, paspauskite Alt + F11 (arba dešiniuoju pelės mygtuku spustelėkite darbalapio skirtuką ir pasirinkite Peržiūrėti kodą). Tai atvertų VB redaktorių.
• Kairiojoje srityje, po darbaknyge, kurioje dirbate, dešiniuoju pelės mygtuku spustelėkite bet kurį darbalapį ir pasirinkite Įterpti -> Modulis. Tai įterptų naują modulį. Nukopijuokite ir įklijuokite kodą modulio kodo lange.
• Dukart spustelėkite modulio pavadinimą (pagal numatytuosius nustatymus modulio pavadinimas modulyje1) ir įklijuokite kodą į kodo langą.
• Uždarykite VB redaktorių.
• Viskas! Dabar darbalapyje turite pasirinktinę funkciją „GetColorCount“.

Norėdami naudoti šią funkciją, tiesiog naudokite ją kaip bet kurią įprastą „Excel“ funkciją.

Sintaksė: = GetColorCount (CountRange, CountColor)

• CountRange: diapazonas, kuriame norite skaičiuoti langelius su nurodyta fono spalva.
• CountColor: spalva, pagal kurią norite suskaičiuoti ląsteles.

Norėdami naudoti šią formulę, ląstelėje naudokite tą pačią fono spalvą (kurią norite suskaičiuoti) ir naudokite formulę. „CountColor“ argumentas būtų tas pats langelis, kuriame įvedate formulę (kaip parodyta žemiau):

Pastaba: Kadangi darbaknygėje yra kodas, išsaugokite jį .xls arba .xlsm plėtiniu.

Išbandykite patys … Atsisiųskite pavyzdinį failą

Ar žinote kokį kitą būdą skaičiuoti spalvotus langelius „Excel“?

Jei taip, pasidalykite ja su manimi, palikdami komentarą.