fbpx

Tietyn väristen solujen laskeminen Excelillä

Taulukkoa työstettäessä on usein tapana merkitä tietyn tyyppiset solut jollakin värillä. Esittelen tässä kirjoituksessa muutaman kätevän tavan ja lopuksi custom-funktion, jonka avulla saman voi tehdä kaavalla. Valitettavasti kyseistä kaavaa ei löydy Excelistä valmiina, mutta sen lisääminen on onneksi kohtuullisen helppoa.

Tapa 1: Nopea tsekkaus

Nopein tapa laskea tietyn väriset solut yhteen on lisätä taulukkoon filtterit (eli suodattimet) ja suodattaa sitten värin mukaan. Filtterit voit lisätä valitsemalla alueen ja painamalla Ctrl + Shift + L. Alla olevasta kuvasta näet, mistä värin mukaan suodattaminen löytyy.

Voit sen jälkeen laskea solujen lukumäärän ja summan nopeasti valitsemalla alueen ja kurkkaamalla oikeassa alakulmassa olevia laskelmia.

Vinkki: Voit valita alueella näytettävät laskelmat klikkaamalla siinä kohdassa hiiren oikealla näppäimellä. Samalla voit piilottaa View Shortcutsit, joita ainakin itse painan usein vahingossa ja Zoom Sliderin, jonka sijaan käytän Ctrl + Hiiren rullaa.

Tapa 2: Kaava, joka laskee näkyvän alueen solut

Jos nopea tsekkaus ei riitä, niin voit käyttää apuna SUBTOTAL-kaavaa (suom. VÄLISUMMA). Tavallinen SUM-funktio ei riitä, sillä se laskee mukaan myös piilotetut solut.

SUBTOTAL-kaavan avulla voit laskea vain näkyvien solujen lukumäärän, summan tai keskiarvon (jne.). SUBTOTAL-funktio on kuin linkkuveitsi, jossa on useampia eri kaavoja samassa, ja lisäksi voit valita lasketaanko kaavoihin mukaan manuaalisesti piilotetut solut vai ei. Tässä tilanteessa tällä ei ole tosin niinkään merkitystä, sillä suodattimien avulla piilotetut solut ovat aina ulkona laskelmista.

SUBTOTAL-funktion valinnat 1-11 ottavat mukaan manuaalisesti piilotetut solut ja 101-111 eivät. Oikean laskelman valitseminen on helppoa, kun vain aloitat kirjoittaa kaavaa ja seuraat IntelliSensen antamia ohjeita. Esimerkiksi solujen lukumäärä lasketaan kaavalla =SUBTOTAL(2;B1:B5). Tässä tapauksessa siis ei ole merkitystä käytätkö valintoja 1-11 vai 101-111.

Tapa 3. Custom-funktio

Edellinen kaava toimii hyvin niin kauan kuin taulukosta täytyy ottaa suodatus pois ja laskelmat päivittyvät. Kuten sanottu, niin valitettavasti Excelistä ei löydy suoraan kaavaa, jonka avulla voisi tehdä laskelmia tietyn väristen solujen perusteella, vaan tätä varten täytyy käyttää custom-funktioita (eli UDF = User Defined Function).

Kopioin tähän alle ensin kyseiset kaavat, ja kerron sitten miten ne voidaan lisätä.

Function ColorCount(SampleCell As Range, LookupRange As Range)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = SampleCell.Interior.ColorIndex
For Each rCell In LookupRange
If rCell.Interior.ColorIndex = lCol Then
vResult = vResult + 1
End If
Next rCell
ColorCount = vResult
End Function

Function ColorSum(SampleCell As Range, LookupRange As Range)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = SampleCell.Interior.ColorIndex
For Each rCell In LookupRange
If rCell.Interior.ColorIndex = lCol Then
vResult = vResult + rCell.Value
End If
Next rCell
ColorSum = vResult
End Function

Function ColorAverage(SampleCell As Range, LookupRange As Range)
Dim rCell As Range
Dim lCol As Long
Dim vResult
Dim vCount
lCol = SampleCell.Interior.ColorIndex
For Each rCell In LookupRange
If rCell.Interior.ColorIndex = lCol Then
vCount = vCount + 1
vResult = vResult + rCell.Value
End If
Next rCell
ColorAverage = vResult / vCount
End Function

Custom-funktion lisääminen

Voit lisätä uusia kaavoja Exceliin VBA-editorin kautta. Tätä varten täytyy kytkeä päälle Developer-mode, File -> Options -> Customize Ribbon -> Developer-täppä päälle.

Nyt voit painaa Alt+F11 ja avata VBA-editorin.

Paina nyt ylhäältä Insert + Module ja kopioi yllä olevat kaavat tyhjään ikkunaan. Ensimmäinen kaava laskee lukumäärän, toinen summan ja kolmas keskirarvon.

Tämän jälkeen moduuli täytyy tallentaa Excel-lisäosana (.xlam) kansioon:

C://<Käyttäjät>/<Käyttäjä>/AppData/Roaming/Microsoft/Excel/XLSTART

Voit antaa sille nimeksi vaikka ColorFunctions.xlam

Funktioiden käyttäminen

Nyt voit käyttää kaavoja työkirjassasi. Kaavat toimii seuraavalla logiikalla. Ensin funktiolle syötetään esimerkkisolu, josta katsotaan väri ja seuraavaksi laskettava alue, eli esimerkiksi näin:

Valitettavasti IntelliSensen (eli kaava-avun) lisääminen on yli osaamiseni. Jos et muista järjestystä, voit painaa Ctrl+Sift+A, jolloin kaavaan tulee funktiolle annettavat argumentit esiin.

Voit myös ladata tiedoston allaolevasta linkistä ja kopioida samaan kansioon.

C:\\<Käyttäjät>\<Käyttäjä>\AppData\Roaming\Microsoft\Excel\XLSTART

Huom! Solun taustan värjääminen ei triggeröi Exceliä uudelleenlaskemaan kaavaa. Voit päivittää kaavan menemällä kys. soluun ja painamalla Enter, muuttamalla jonkin solun arvoa hakualueelta tai painamalla Ctrl + Shift + Alt + A, joka laskee kaikki solut uudelleen.

Otathan yhteyttä jos funktioiden kanssa esiintyy ongelmia!

2 ajatusta aiheesta “Tietyn väristen solujen laskeminen Excelillä”

  1. Nuo Custom-funktiot eivät tunnu toimivan jos väri on asetettu ehdollisen muotoilun tuloksena. Jos värivalintasoluun on väri tullut ehdollisesta muotoilusta, niin tulos on kaikkien alueen solujen lukumäärä. Jos värivalintasolu on sellainen, johon on annettu väri normaalin solun muotoilun kautta niin funktio antaa tulokseksi nollan vaikka ehdollinen muotoilu antaa joukkoon samanvärisiä soluja. Sitten jos sekä värivalintasoluun että tarkasteltavan alueen soluihin laitetaan väri ihan tavallisen solujen muotoilun avulla, niin funktio toimii oikein.

  2. Hei!

    Törmäsinpä kirjoitukseesi etsiessäni ratkaisua tällaiseen asiaan: kuinka laskea alueelta yhteen niiden solujen arvot, joiden muotoiluna on esim. ”valuutta”. Mutta ei siis muita alueella olevia lukuarvoja.

    En onnistunut (ainakaan toistaiseksi) löytämään vastausta mistään. Onko sinulla tiedossa?

    Yst.terv. Matilda

Kommentoi

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *