SUM, SUBTOTAL vai AGGREGATE?

SUM, SUBTOTAL vai AGGREGATE?

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.

summa-piilotettuja

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)

subtotal-funktion-koodit

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.

piilotettuja-riveja

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)

aggregate-koodit

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.

mita-jatetaan-huomiotta

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).

piilotetut-ja-virheet

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

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s