Excel Tips and Tricks 8: Suodatettujen ja piilotettujen rivien laskeminen

Edellisessä Excel-vinkkiartikkelissa käsittelin jäsennettyjä viittauksia, jotka auttavat taulukoksi muotoiltua aluetta koskevien kaavojen kirjoittamisessa. Toinen taulukoksi muotoiltuun alueeseen liittyvä mukava automatiikka versiossa 2007 koskee funktioita. Oletuksenahan Excelissä on, että kaavassa oleva alueviittaus kattaa myös piilotetut solut. On kuitenkin tilanteita, jolloin tämä ei ole toivottavaa, esimerkiksi pikasuodatetuissa tai suodatetuissa taulukoissa ei ole välttämättä mitään mieltä sisällyttää piilotettua tietoa laskentaan.

Alla olevan kuvan pikasuodatettuun taulukkoon on haettu näkyviin myyjän tilausrivit, joiden loppusumma halutaan laskea. Kun valitaan solu H26 ja napsautetaan Automaattinen summa -painiketta (AutoSum), Excel kirjoittaa kaavan käyttämällä SUMMA-funktion tilalla VÄLISUMMA-funktiota (SUBTOTAL), joka oletusarvoisesti laskee vain näkyvät rivit. Kun pikasuodatusehto vaihdetaan, alimmaisella rivillä näkyvä loppusumma muuttuu vastaavasti.

Kun samaan soluun ryhdytään lisäämään keskiarvokaavaa valitsemalla Automaattinen summa -valikosta KESKIARVO-funktio (AVERAGE), se muutetaan jälleen VÄLISUMMA-funktioksi. Tässä yhteydessä selvinnee myös funktion ensimmäisen argumentin tarkoitus. Argumentti nimittäin määrittää sarakkeessa toteutettavan laskutoimituksen. Välisumma siis tavallaan sisältää toisen funktion, joka tarkennetaan erillisellä argumentilla. Summakaavan argumentti on 109, keskiarvon 101. Esimerkiksi 102 ottaisi käyttöön LASKE-funktion (COUNT).

Excel 2007 -versio tarjoaa VÄLISUMMA-funktiota automaattisesti silloin, kun kaavan yläpuolella on taulukoksi muotoiltu alue. Kun taulukon alapuolella olevalle riville on lisätty ensimmäinen kaava, koko rivi muuttuu siten, että kun yksittäinen solu valitaan, sen oikeaan laitaan ilmestyy nuolipainike VÄLISUMMA-funktion lisäämistä varten. Uusimmassa versiossa VÄLISUMMA-funktion käyttö onkin todella helppoa etenkin sen jälkeen kun argumenttien merkityksen on sisäistänyt. Aikaisempien versioiden käyttäjille tämän funktion löytämisestä on mahdollisesti ollut vielä enemmän apua, sillä vielä Excel 2003 kelpuuttaa suodatetun taulukon alapuolelle tarjotun SUMMA-funktion aivan täydestä. Jossakin vaiheessa sitten todetaan, että taulukko laskeskelee omiaan tai summakaavan sisältämä rivi piilotetaan suodatuksessa. VÄLISUMMAa ei tarjota tietoalueen ominaisuuksien perusteella automaattisesti, kuten versiossa 2007, vaan se on osattava itse hakea matemaattisten ja trigonometristen funktioiden joukosta.

VÄLISUMMA-funktion käyttöä ei ole rajoitettu erityisesti pikasuodatuksen yhteyteen vaan versiosta 2003 alkaen sitä on voitu käyttää myös suodattamattomissa taulukoissa, joista on syystä tai toisesta jouduttu piilottamaan rivejä. Funktion valinnaisilla argumenteilla voidaan tällöin säädellä piilotettujen rivien huomioiminen laskennassa.

Argumentteja 1–11 käytettäessä piilorivit lasketaan, argumentteja 101–111 käytettäessä ne jätetään huomioimatta:

1, 101, KESKIARVO (AVERAGE)
2, 102, LASKE (COUNT)
3, 103, LASKE.A (COUNTA)
4, 104, MAKS (MAX)
5, 105, MIN (MIN)
6, 106, TULO (PRODUCT)
7 , 107, KESKIHAJONTA (STDEV)
8, 108, KESKIHAJONTAP (STDEVP)
9, 109, SUMMA (SUM)
10, 110, VAR
11, 111, VARP

Näin esimerkiksi =VÄLISUMMA(9;H2:H25) laskee sarakkeen summan ottaen mukaan luvut myös piilotetuilta riveiltä.

Huomaa, että rivien piilottaminen pikavalikon Piilota-komennolla (Hide) ja pikasuodatus ovat toiminnallisesti eri asia: Pikasuodatusta käytettäessä argumentit 1–11 jättävät suodatetut rivit laskematta! Sen sijaan argumentit 101–111 laskevat vain näkyvät rivit riippumatta siitä, onko rivejä kätketty piilottamalla vai pikasuodattamalla.

Viimeinen Excel Tips and Tricks -artikkeli tällä erää!

Maailmassa on paljon muitakin asioita, joten on kenties jo aika päättää tämänkertainen Excel-vinkkisarja. Alun perin tarkoitukseni oli laatia pienemmistä vinkeistä koostuvia FAQ- tyyppisiä postauksia, mutta kaavat veivät nyt monestakin syystä mennessään – mm. uudet funktiot ovat siirtymäkoulutusten vuoksi ajankohtaisia. Toivon, että näistä jutuista on ollut jollekin apua. Monelle Excelin kaavat ja funktiot ovat itsestäänselvyyksiä, mutta monelle ne tuottavat myös päänvaivaa. Kyse on pitkälti siitä, mihin itse kukin on harjaantunut ja tottunut.

Exceliin varmasti palataan vielä tässä blogissa. Mahdollisesti tuo FAQ-artikkelikin ilmaantuu jossakin vaiheessa päivänvaloon.

Kaikki Excel Tips and Tricks -artikkelit:

Solun täyttökahvan käytöstä

Juokseva rivinumerointi

Päivittyvä sarakesumma

Kaavan automaattinen täydennys

JOSVIRHE (IFERROR)

Muita uusia funktioita

Jäsennetyt viittaukset