Tag Archives: erikoissuodatus

Erkoissuodatus

Erikoissuodatus

Voit etsiä ja poimia tietoja listasta monella eri tavalla. Yksi tapa on hyödyntää erikoissuodatusta. Erikoissuodatus vaatii oman ehtoalueen, johon voit rakentaa erittäin monimutkaisia ehtoja, joiden avulla poimit tietoja. Voit myös tehdä ja käyttää laskennallisia kenttiä ehtojen asettelussa.

Luomisehtoja voit myös hyödyntää tietokantafunktioilla. Erikoissuodatuksen löydät Tiedot-valintanauhan Lajittele ja suodata -ryhmästä olevasta Lisäasetukset-toiminnosta. (Data – Sort & Filter. Advanced).

Huomaa: jos haluat kopioida ehtoja vastaavat tiedot toiseen tauluun, aloita siitä taulusta jonne haluat lopputuloksen.

Sinulla voi myös olla useampi ehtoalue. Silloin ne kannattaa nimetä ja lista kannattaa määritellä taulukoksi, jolle antaa nimen (Aloitus – Tyyli, Muotoile taulukoksi (Home – Styles, Format as Table)).

Vierekkäiset ehdot muodostavat JA-ehdon ja allekkaiset TAI-ehdon. Ehtojen kenttänimien täytyy täsmätä listan kenttänimiin ja itse kopioin ne useimmiten välttyäkseni kirjoitusvirheiltä.
Alla muutama yksinkertaisempi ehto ja sitten pari laskentakenttää ehtoina.

Lista josta esimerkki suodatukset tehdään:

Lista josta suodatukset tehdään

Lähde

Kaikki tanskalaiset:

Country
=Denmark

Huomaa, että joissakin tapauksissa Excel saattaa antaa virheellisen tuloksen jos kirjoitat soluun vain ehdon. Tämän takia olen kirjoittanut sen kaavaan ja ehdon merkkijonona =”=Denmark” Näin pyrin varmentamaan, että Excel tulkitsee ehtoni oikein.

TAI-ehto
Kaikki tanskalaiset tai ranskalaiset:

Country
=Denmark
=France

Tai-ehdossa kirjoitetaan ehdot allekkain.

JA-ehto
Kaikki tanskalaiset JA jossa tilauspäivä on joko 16.1.2013 tai uudempi. Huomaa >= -merkkien järjestys.

Country Order Date
=Denmark >=16.1.2013

JA-ehdossa kirjoitetaan ehdot vierekkäin.

Tanskalaiset JA jossa tilauspäivä on joko 16.1.2013 tai uudempi TAI sitten kaikki tilaukset jossa hinta suurempi kuin 10,50.

Country Order Date Price
=Denmark >=16.1.2013
10,50

Laskennallinen kenttä
Kun käytetään laskennallista kenttää, otsikot jätetään pois ja kaavassa viitataan listan/kannan ensimmäiselle riville suhteellisella viittauksella. Ehtoalueeseen on kuitenkin sisällytettävä tyhjät solut ehtojen yläpuolelle.

Näytä kaikki tilaukset joissa toimituspäivä on tyhjä ja tilauspäivästä kulunut yli 10 päivää verrattuna tähän päivään. TODAY = TÄMÄ.PÄIVÄ

 
TRUE FALSE

Kaavat ovat seuraavat:

=TODAY()-C11>10 =D11=””

Tietokantafunktiot

Tietokantafunktioilla voidaan suorittaa monenlaisia yhteenvetolaskelmia listoista moninaisilla ehdoilla. Ehtoalue kannattaa pitää erillään listasta, niin että ehdot on helppo muuttaa ja ennen kaikkea näet suoraan yhdellä silmäyksellä mitä funktiot laskevat. Ehtoalue voi sijaita omassa taulussaan erillään koko listasta.

Funktioiden syntaksi on:
FUNKTIO(Tietokanta;Sarake;Ehdot)
Tietokantafunktiot ovat luokassa Tietokanta (Database) ja alkavat kirjaimella T, englanniksi D.

Tietokanta on joko alue, nimetty alue tai lista joka on määritelty taulukoksi.   Sarake on se sarake jota halutaan käyttää, joko sarakeindeksinä tai nimenä kirjoitettuna lainausmerkeissä.

Ehdot ovat erillinen ehtoalue, joka voi koostua useammasta sarakkeesta ja rivistä. Ehtoalueessa on listan/tietokannan sarakeotsikko ja sen alapuolella ehto. Vierekkäiset ehdot muodostavat JA (AND) ehdon ja allekkain olevat TAI (OR) ehdon.

Esimerkiksi lasketaan yhteensä tammikuun tilausten arvo
Ehto:

Order Date Order Date
>=1.1.2013 <1.2.2013

Kaava soluun johon haluat summan:
=TSUMMA(A8:I20;”Price”;A1:B2)
=DSUM(A8:F20;”Price”;A1:B2)

Huomaa että laskettavan sarakkeen voi antaa joko lista-alueen sarakeindeksinä (numero) tai sarakkeen nimi merkkijonona (”” -merkkien sisälle).   Jos olet käyttänyt pakollisia rivivaihtoja (Ctrl + Enter) listan sarakeotsikoissa, on helpompaa käyttää sarakeindeksiä. Tällöin kaava olisi muotoa:
=TSUMMA(A8:F20;6;A1:B2) jossa numero 6 on sarakkeen positio listassa.

Aputoiminnosta löydät esimerkkejä muista tietokantafunktioista.