Luku 17 · 17.16
Näytä kunkin luokan top 5
Myydyimmät tuotteet osastoittain, suurimmat asiakkaat alueittain, kiireisimmät päivät kuukausittain: et halua koko luettelon top 5:tä, vaan top 5:n yhden luokan sisältä. SUURI toimii koko sarakkeessa ja antaa yhtä suurilla summilla saman tuotteen kahdesti. Euro-Officessa ratkaiset tämän yhdellä kaavalla.
Esimerkkinä käytämme Bloemisterij Het Zonnetjen tuotekohtaista vuosimyyntiä: sarakkeessa A on tuote, sarakkeessa B luokka (Rozen, Tulpen, Planten) ja sarakkeessa C myynti, riveillä 2:sta 18:aan. Solussa F1 on luokka, jonka haluat nähdä: Rozen.
Top 5 yhdellä kaavalla
Kirjoita soluun H2:
=OTA(LAJITTELE(SUODATA(A2:C18;B2:B18=F1);3;-1);5)
Lue kaava sisältä ulospäin:
- SUODATA(A2:C18;B2:B18=F1) jättää jäljelle vain solun F1 luokan rivit, kaikkine kolmine sarakkeineen.
- LAJITTELE(...;3;-1) lajittelee nämä rivit kolmannen sarakkeen (myynnin) mukaan suurimmasta pienimpään (-1).
- OTA(...;5) jättää jäljelle 5 ylintä riviä.
Paina Enter: viisi tuotetta luokkineen ja myynteineen näkyvät soluissa H2:J6. Jos kirjoitat soluun F1 Planten, luettelo muuttuu heti.

Yhtä suuret summat eivät ole ongelma: esimerkissä sekä Grand Prix että Ecuadoriaanse roos ovat myyneet 3 400, ja molemmat ovat luettelossa. Jos käyttäisit kahdesti SUURI-funktiota yhdessä INDEKSI- ja VASTINE-funktioiden kanssa, Grand Prix näkyisi kaksi kertaa.
Vain summat, luokat rinnakkain
Jos haluat vain viisi suurinta summaa, esimerkiksi useammalle luokalle rinnakkain, käytä SUURI-funktiota suodatettuun luetteloon. Kirjoita luokat soluihin F1, G1 ja niin edelleen, valitse F2:G6 ja kirjoita:
=JOSVIRHE(SUURI(SUODATA($C$2:$C$18;$B$2:$B$18=F$1);RIVIT($1:1));"")
ja paina Ctrl+Enter. SUODATA jättää jäljelle vain rivin 1 luokan summat. RIVIT($1:1) on ensimmäisellä rivillä 1 ja sen alapuolisilla riveillä 2, 3, 4 ja 5: suurin, toiseksi suurin ja niin edelleen. JOSVIRHE jättää solun tyhjäksi, jos luokassa on alle viisi tuotetta; luokassa Tulpen niitä on vain neljä.
Huomaa: Excel-sivustoilla näkee usein kaavan =SUURI(JOS($B$2:$B$18=F$1;$C$2:$C$18);RIVIT($1:1)). Euro-Officessa se antaa pelkällä Enter-näppäimellä väärän tuloksen: JOS ei silloin katso koko saraketta, joten saat kaikkien luokkien suurimmat summat tai #NUM!. Jos päätät kaavan näppäinyhdistelmällä Ctrl+Vaihto+Enter, kaava saa aaltosulkeet {…} ja tulos on oikein. Yllä esitetty SUODATA-ratkaisu on yksinkertaisempi.
Vain suurin summa
Yhdelle luvulle on yksinkertaisempi tapa: =MAKS.JOS.JOUKKO(C2:C18;B2:B18;F1) antaa luokan suurimman myynnin, tässä 6 150.
Sudenkuopat
- Alle 5 tuotetta luokassa: ilman JOSVIRHE-funktiota SUURI antaa #NUM! riveillä, joita ei ole. OTA ei kärsi tästä, vaan näyttää yksinkertaisesti vähemmän rivejä.
- OTA-kaava jää tyhjäksi: niissä 5 rivissä ja 3 sarakkeessa, joihin tulos haluaa levitä, on jotain. Katso Itsestään päivittyvät luettelot: SUODATA, AINUTKERTAISET.ARVOT ja LAJITTELE.
- Kollega, jolla on Excel 2019 tai vanhempi, näkee OTA-, LAJITTELE- ja SUODATA-funktioiden kohdalla #NAME?.
