Yksilöllisten arvojen määrä (osa 1: Excel)

Tuoreimmassa Excelissä on yli 450 funktiota, mutta edelleen puuttuu yksi tärkeä. Excelissä ei ole omaa funktiota alueella olevien yksilöllisten (distinct) arvojen määrän laskemiseen, joka vastaisi esimerkiksi kysymyksiin ”Miten monta eri tuotetta viime viikon myyntitapahtumalistalla on?” tai ”Miten monesta eri maasta  meillä on asiakkaita?”.

Kerron tässä jutussa miten lasket yksilölliset arvot Excelissä ja käytän esimerkkinä alla olevaa aineistoa sekä kysymystä:

”Miten monesta eri postitoimipaikasta rekisterissämme on jäseniä?”.

jäsenluettelo

Esimerkkiaineisto on muunnettu taulukoksi, jolle on annettu nimeksi jäsenet, joten sekä taulukon nimi että sarakeotsikot esiintyvät esimerkkikaavoissa.

Excelin lukumäärä-funktiot

Excelin lukumäärä-funktiot:

Englanninkielinen versio Suomenkielinen versio
Lukuja sisältävien solujen määrä COUNT LASKE
Muiden paitsi tyhjien solujen määrä COUNTA LASKE.A
Tyhjien solujen määrä COUNTBLANK LASKETYHJÄT
Ehtojen mukaisten solujen määrä (kuten Espoo-solujen määrä) COUNTIF (yksi ehto)
COUNTIFS (useita ehtoja)
LASKE.JOS
LASKE.JOS.JOUKKO

Yllä mainittujen lisäksi lukumääriä voi laskea FREQUENCY (TAAJUUS) -funktiolla, mutta siitä jossakin toisessa artikkelissa lisää.

Koska yksilöllisten arvojen laskemiseen tarvitaan COUNTIF (LASKE.JOS) -funktiota, silmäillään kertaukseksi sen muoto:

=COUNTIF(laskettava alue; ehto)

Esimerkiksi kaava

=COUNTIF([Postitoimipaikka]; ”ESPOO”)

laskee Postitoimipaikka-sarakkeessa olevien ESPOO-solujen lukumäärän. Joten siis kaava

=COUNTIF([Postitoimipaikka]; [@Postitoimipaikka])

laskee Postitoimipaikka-sarakkeesta kaavan kanssa samalla rivillä olevien postitoimipaikkojen määrän. [Postitoimipaikka] tarkoittaa koko Postitoimipaikka-saraketta ja [@Postitoimipaikka] tarkoittaa kaavan kanssa samalla rivillä olevaa Postitoimipaikkaa (@ = at this row).

Yksilöllisten arvojen lukumäärän laskeminen

Laske ensin COUNTIF (LASKE.JOS) -funktiolla miten moneen kertaan kullakin rivillä oleva Postitoimipaikka esiintyy:

=COUNTIF([Postitoimipaikka]; [@Postitoimipaikka])

lkm

Esimerkiksi ESPOO esiintyy luettelossa 3 kertaa ja HELSINKI 2 kertaa. Luetteloa ei suinkaan tarvitse lajitella postitoimipaikoittain järjestykseen, mutta tässä esimerkissä lajittelu helpottaa kaavan ymmärtämistä.

Muokkaa kaava siten, että laitat jaettavaksi luvun 1 ja käytät jakajana juuri laskettua lukumäärää. Lasket siis kunkin rivin osuuden kaikista saman postitoimipaikan riveistä:

=1/COUNTIF([Postitoimipaikka]; [@Postitoimipaikka])

osuudet

Kuten kuvasta huomaat, kunkin postitoimipaikan lukujen yhteissumma on 1. Laskemalla näiden lukujen summan, saat tulokseksi yksilöllisten postitoimipaikkojen lukumäärän. Helppoa kuin tomaatin kasvatus!

valmis

Jos lasket tuloksen tavallisesta luettelosta, jota ei ole muunnettu taulukoksi, soluun F5 tulee kaava:

=1/COUNTIF($E$5:$E$15; E5)

Guru-vinkkejä

Jos haluat lukumäärän yhteen soluun ilman F-sarakkeeseen laskettuja rivikohtaisia lukuja, voit kirjoittaa kaavan yhteen soluun käyttämällä SUMPRODUCT (TULOJEN.SUMMA) -funktiota seuraavasti:

=SUMPRODUCT(1 / COUNTIF( jäsenet[Postitoimipaikka]; jäsenet[Postitoimipaikka]) )

Voit myös käyttää matriisikaavaa:

{=SUM(1/COUNTIF(jäsenet[Postitoimipaikka]; jäsenet[Postitoimipaikka]))}

tai jos aineistosi ei ole taulukko:

{=SUM(1/COUNTIF(E5:E15;E5:E15))}. Käytä suomenkielisessä versiossa SUMMA-funktiota.

Matriisikaavat ovat melko harvinaisia ja ne pitää hyväksyä Enterin sijaan näppäinyhdistelmällä Ctrl + Shift + Enter. Hyväksymisen jälkeen kaavan ympärille ilmestyy aaltosulkeet {  }. Muista käyttää näppäinyhdistelmää myös silloin kun muokkaat kaavaa. Aion kirjoittaa artikkelin myös matriisikaavoista, mutta nyt voit tutustua aiheeseen Excelin Matriisikaava-ohjeiden avulla.

Pari muuta matriisikaavaesimerkkiä yksilöllisten arvojen laskemiseen: Tietyn arvon esiintymiskertojen tai tietoalueen ainutkertaisten arvojen määrän laskeminen

Lue myös miten saat paljon helpommin laskettua yksilölliset arvot pivot-taulukossa Power Pivotin avulla: Yksilöllisten arvojen määrä (osa 2: Power Pivot)

7 kommenttia artikkeliin ”Yksilöllisten arvojen määrä (osa 1: Excel)

  1. Hyvä vinkki, kiitos Heidi!

    ”Ongelmahan” tuossa tietenkin on, että tiedot täytyy muistaa putsata ensin. Suurissa asiakasrekistereissä, jotka osin muodostuvat automaattisesti ja osin käsin, saattavat sisältää tauhkaa. Esimerkiksi monet käyttävät osoitteissaan edelleen ”Helsinki 13” ja ”Helsinki 14” pelkän Helsingin sijaan. Myöskään Helsingfors ei ole sama kuin Helsinki, puhumattakaan Helisinkistä. 🙂 Jos tieto on viety käsin, siellä saattaa olla myös turhia välilyöntejä ja taas kaava antaa erilaisen (ei välttämättä väärän) tuloksen kuin ehkä alunperin oli toivottu.

    Jos pyörittelee vähänkään isompia datamassoja ja usein, niin alkaa arvostamaan myös sitä alkupäätä, eli hyvän laatuista lähdeaineistoa. Ei pelkästään mahtavan näköisiä Excelkaavioita. 🙂

    Tykkää

  2. Täsmälleen Nicke!

    Erilaiset datan siivoamistekniikat olisi myös hyvä blogin aihe. Ei onnistu ihan yhdellä eikä kahdella komennolla ja voi joskus olla hyvinkin työlästä. Yllättävän sotkuista tietoa löytyy myös sellaisista järjestelmistä, joissa kaiken pitäisi olla ok.

    Tykkää

  3. Entä Excel 2013 + tabledata + datamalli + pivot + value field settings – summarize value field by – distinct count?

    Tykkää

  4. Paluuviite: Excelin SM-kisojen ensimmäisen kierroksen vastaukset | HExcelligent.fi

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