Värillisten solujen laskeminen Excelissä (vaiheittainen opas + VIDEO)

Katso video - Kuinka laskea värilliset solut Excelissä

Eikö olisi hienoa, jos olisi toiminto, joka voisi laskea värilliset solut Excelissä?

Valitettavasti tähän ei ole sisäänrakennettua toimintoa.

MUTTA…

Se voidaan tehdä helposti.

Värillisten solujen laskeminen Excelissä

Tässä opetusohjelmassa näytän sinulle kolme tapaa laskea värilliset solut Excelissä (VBA: n kanssa ja ilman sitä):

1. Suodatin- ja SUBTOTAL -toiminnon käyttäminen
2. GET.CELL -toiminnon käyttäminen
3. Käyttämällä VBA: lla luotua mukautettua toimintoa

#1 Laske värilliset solut suodattimen avulla ja VAIHTOEHTO

Jos haluat laskea värilliset solut Excelissä, sinun on suoritettava seuraavat kaksi vaihetta:

• Suodata värilliset solut
• Käytä SUBTOTAL -toimintoa laskeaksesi näkyvät värilliset solut (suodatuksen jälkeen).

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Tässä tietojoukossa käytetään kahta taustaväriä (vihreä ja oranssi).

Tässä on vaiheet, joissa Excel laskee värilliset solut:

1. Käytä missä tahansa tietojoukon alapuolella olevassa solussa seuraavaa kaavaa: = VAHVISUMMA (102, E1: E20)
   
2. Valitse otsikot.
3. Siirry kohtaan Tiedot -> Lajittele ja suodata -> Suodata. Tämä käyttää suodatinta kaikkiin otsikoihin.
4. Napsauta mitä tahansa suodattimen avattavaa valikkoa.
5. Siirry kohtaan Suodata värin mukaan ja valitse väri. Yllä olevassa tietojoukossa, koska solujen korostamiseen käytetään kahta väriä, suodatin näyttää kaksi väriä näiden solujen suodattamiseen.

Heti kun suodatat solut, huomaat, että SUBTOTAL -funktion arvo muuttuu ja palauttaa vain suodatuksen jälkeen näkyvien solujen määrän.

Miten tämä toimii?

SUBTOTAL -funktio käyttää 102 -argumenttia ensimmäisenä argumenttina, jota käytetään määritettyjen alueiden näkyvien solujen laskemiseen (piilotettuja rivejä ei lasketa).

Jos dataa ei suodateta, se palauttaa 19, mutta jos se suodatetaan, se palauttaa vain näkyvien solujen määrän.

Kokeile itse… Lataa esimerkkitiedosto

#2 Laske värilliset solut käyttämällä GET.CELL -toimintoa

GET.CELL on Macro4 -toiminto, joka on säilytetty yhteensopivuussyistä.

Se ei toimi, jos sitä käytetään laskentataulukon säännöllisinä toimintoina.

Se toimii kuitenkin Excel -nimisissä alueissa.

Katso myös: Lisätietoja GET.CELL -toiminnosta.

Tässä on kolme vaihetta GET.CELL: n käyttämiseksi värillisten solujen laskemiseen Excelissä:

• Luo nimetty alue GET.CELL -toiminnolla
• Käytä nimettyä aluetta saadaksesi värikoodin sarakkeeseen
• Värinumeron käyttäminen värillisten solujen laskemiseen (värin mukaan)

Sukelletaan syvälle ja katsotaan, mitä tehdä jokaisessa kolmesta mainitusta vaiheesta.

Nimetyn alueen luominen

• Siirry kohtaan Kaavat -> Määritä nimi.
• Kirjoita Uusi nimi -valintaikkunaan:
  • Nimi: GetColor
  • Soveltamisala: Työkirja
  • Viittaa kohteeseen: = GET.CELL (38, Sheet1! $ A2)
    Yllä olevassa kaavassa olen käyttänyt Taulukko1! $ A2 toisena argumenttina. Sinun on käytettävä sen sarakkeen viitettä, jossa solut ovat taustavärillä.

Värikoodin saaminen jokaiselle solulle

Käytä tietojen vieressä olevassa solussa kaavaa = GetColor

Tämä kaava palauttaisi 0, jos solussa EI ole taustaväriä, ja palauttaisi tietyn luvun, jos taustaväriä on.

Tämä luku on ominaista värille, joten kaikki solut, joilla on sama taustaväri, saavat saman numeron.

Laske värilliset solut värikoodin avulla

Jos noudatat yllä olevaa prosessia, sinulla olisi sarake, jossa on taustaväriä vastaavat numerot.

Tietyn värin laskeminen:

• Jossain tietojoukon alapuolella, anna sama taustaväri solulle, jonka haluat laskea. Varmista, että teet tämän samassa sarakkeessa, jota käytit nimettyä aluetta luodessasi. Käytin esimerkiksi saraketta A, joten käytän vain sarakkeen A soluja.
• Käytä viereisessä solussa seuraavaa kaavaa:

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

Tämä kaava antaa sinulle kaikkien solujen määrän määritetyllä taustavärillä.

Kuinka se toimii?

COUNTIF -funktio käyttää nimettyä aluetta (GetColor) kriteerinä. Kaavan nimetty alue viittaa viereiseen soluun vasemmalla (sarakkeessa A) ja palauttaa kyseisen solun värikoodin. Siksi tämä värikoodinumero on kriteeri.

COUNTIF -toiminto käyttää aluetta ($ F $ 2: $ F $ 18), joka sisältää kaikkien solujen värikoodinumerot ja palauttaa määrän ehtojen numeron perusteella.

Kokeile itse… Lataa esimerkkitiedosto

#3 Laske värillinen käyttämällä VBA: ta (luomalla mukautettu toiminto)

Yllä olevissa kahdessa menetelmässä opit laskemaan värilliset solut ilman VBA: ta.

Mutta jos olet kunnossa VBA: n käytössä, tämä on helpoin kolmesta menetelmästä.

VBA: n avulla luomme mukautetun funktion, joka toimisi COUNTIF -funktion tavoin ja palauttaisi solujen määrän, jolla on tietty taustaväri.

Tässä on koodi:

'Sumit Bansalin luoma koodi osoitteesta https://trumpexcel.com Funktio GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue Integer Dim TotalCount Integer CountColorValue = CountColor.Interior.ColorIndex Aseta rCell = CountRange Jokaiselle r rCell.Interior.ColorIndex = CountColorValue Sitten TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Voit luoda tämän mukautetun toiminnon seuraavasti:

• Kun työkirja on aktiivinen, paina Alt + F11 (tai napsauta laskentataulukon välilehteä hiiren kakkospainikkeella ja valitse Näytä koodi). Tämä avaa VB -editorin.
• Napsauta hiiren kakkospainikkeella mitä tahansa laskentataulukkoa vasemman ruudun työkirjan alla, jossa työskentelet, ja valitse Lisää -> Moduuli. Tämä lisäisi uuden moduulin. Kopioi ja liitä koodi moduulin koodi -ikkunaan.
• Kaksoisnapsauta moduulin nimeä (oletusarvoisesti moduulin nimi moduulissa 1) ja liitä koodi koodi -ikkunaan.
• Sulje VB Editor.
• Se siitä! Laskentataulukossa on nyt mukautettu toiminto nimeltä GetColorCount.

Käytä tätä toimintoa yksinkertaisesti käyttämällä sitä tavallisena Excel -funktiona.

Syntaksi: = GetColorCount (CountRange, CountColor)

• CountRange: alue, jolla haluat laskea solut määritetyllä taustavärillä.
• CountVäri: väri, jolle haluat laskea solut.

Jos haluat käyttää tätä kaavaa, käytä samaa taustaväriä (jonka haluat laskea) solussa ja käytä kaavaa. CountColor -argumentti olisi sama solu, johon kirjoitat kaavan (kuten alla):

merkintä: Koska työkirjassa on koodi, tallenna se .xls- tai .xlsm -laajennuksella.

Kokeile itse… Lataa esimerkkitiedosto

Tiedätkö muita tapoja laskea värilliset solut Excelissä?

Jos kyllä, jaa se kanssani jättämällä kommentti.