Lasketko Excelissä summia, lukumääriä tai vaikkapa keskiarvoja? Entä suodatatko luetteloita? Jos vastasit myöntävästi, tarvitset myös SUBTOTAL ja AGGREGATE-funktioita.

Tavalliset funktiot

Tavalliset matemaattiset ja tilastolliset funktiot kuten SUM (SUMMA), COUNT (LASKE) tai esimerkiksi AVERAGE (KESKIARVO) laskevat tuloksiin mukaan kaikki alueella olevat luvut. Mukana ovat siis myös kaikki piilotetut tai aineistosta poissuodatetut tiedot, kuten esimerkkikuvan luvut 3 ja 6 riveillä 3 ja 6.

SUBTOTAL (VÄLISUMMA)

SUBTOTAL (VÄLISUMMA) funktio laskee halutun tilastollisen tuloksen ja huomioi joko suodatuksen tai myös piilotuksen. Funktiolle annetaan ensimmäisenä argumenttina laskettavan funktion numerokoodi ja toisena alue, josta lasketaan. Funktion numerosarjoja on 2 erilaista: 1-11 ja 101-11.

=SUBTOTAL(funktion numero; alue)

Seuraavassa esimerkissä rivit 3 ja 6 (eli luvut 3 ja 6) on piilotettu piilota (hide) -komennolla, joten  numerokoodilla 9 SUBTOTAL laskee ne mukaan, mutta numerokoodilla 109 jättää ne huomiotta.

Vinkki! Funktiot kannattaa yleensä sijoittaa aineiston otsikoiden yläpuolelle, jotta esimerkiksi suodatuksen yhteydessä näet heti tulokset eikä niitä tarvitse vierittää esiin aineiston alapuolelta.

AGGREGATE (KOOSTE)

AGGREGATE (KOOSTE) hallitsee useampia tilastollisia tuloksia kuin SUBTOTAL. Funktion numeron lisäksi sille annetaan koodina myös tieto, mitä sen tulee jättää huomiotta. Kolmantena argumenttina määritellään laskettava alue. Lisäksi osa funktioista edellyttää vielä neljännen argumentin, joka määrittää tarkemmin miten funktio halutaan suorittaa.

=AGGREGATE(funktion numero; koodi joka kuvaa mitkä luvut huomoidaan; alue; valinnainen neljäs lisätietoargumentti)

Toinen argumentti kuvaa mitä lukuja funktion halutaan jättää huomiotta. Jos kaavan halutaan jättävän  huomiotta alueella olevien SUBTOTAL ja AGGREGATE -funktioiden tulokset sekä piilotetut tiedot, toisena argumenttina annettaisiin koodi 3. Jos funktion halutaan jättävän huomiotta vain piilotetut rivit (joko suodatus tai piilotus), koodiksi annettaisiin 5.

Seuraavan esimerkin ensimmäinen kaava laskee summan (koodi 9) eikä piittaa virheilmoituksesta eikä huomioi piilotettuja tai suodatettuja rivejä (koodi 7). Jälkimmäinen kaava etsii toiseksi suurimman luvun (koodi 14 ja viimeinen valinnainen argumentti 2) piittaamatta piilotetuista riveistä (koodi 5).

Hienoa, että sekä SUBTOTAL että AGGREGATE osaavat laskea tuloksia suodatuksen tulosluettelosta! Hyödyllisiä molemmat.