Tässä opetusohjelmassa opit laskemaan ainutlaatuiset arvot Excelissä käyttämällä kaavoja (COUNTIF- ja SUMPRODUCT -funktiot).
Ainutlaatuisten arvojen laskeminen Excelissä
Oletetaan, että meillä on alla esitetyn kaltainen tietojoukko:
Tässä opetusohjelmassa nimeän alueen A2: A10 NIMEKSI. Jatkossa käytämme tätä nimettyä aluetta kaavoissa.
Katso myös: Nimettyjen alueiden luominen Excelissä.
Tässä tietojoukossa on toistoa NAMES -alueella. Jotta saisimme yksilöllisten nimien määrän tästä tietojoukosta (A2: A10), voimme käyttää COUNTIF- ja SUMPRODUCT -toimintojen yhdistelmää alla esitetyllä tavalla:
= SUMPRODUCT (1/COUNTIF (NIMES, NAMES))
Kuinka tämä kaava toimii?
Hajotetaan tämä kaava, jotta ymmärrämme paremmin:
- COUNTIF (NIMET, NIMET)
- Tämä kaavan osa palauttaa taulukon. Yllä olevassa esimerkissä se olisi {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tässä olevat numerot osoittavat, kuinka monta kertaa arvo esiintyy annetulla solualueella.
Esimerkiksi nimi on Bob, joka esiintyy kahdesti luettelossa, joten se palauttaisi Bobin numeron 2. Samoin Steve esiintyy kolmesti ja siten 3 palautetaan Steve.
- Tämä kaavan osa palauttaa taulukon. Yllä olevassa esimerkissä se olisi {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tässä olevat numerot osoittavat, kuinka monta kertaa arvo esiintyy annetulla solualueella.
- 1/COUNTIF (NIMET, NIMET)
- Tämä kaavan osa palauttaisi taulukon - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Koska olemme jakaneet yhden taulukolla, se palauttaa tämän taulukon.
Esimerkiksi yllä palautetun taulukon ensimmäinen elementti oli 2. Kun 1 jaetaan 2: lla, se palauttaa .5.
- Tämä kaavan osa palauttaisi taulukon - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (NIMET, NAMES))
- SUMPRODUCT yksinkertaisesti lisää kaikki nämä numerot. Huomaa, että jos Bob esiintyy kahdesti luettelossa, yllä oleva taulukko palauttaa .5 missä tahansa Bobin nimi esiintyi luettelossa. Samoin, koska Steve näkyy kolmesti luettelossa, taulukko palauttaa .3333333 aina, kun Steven nimi tulee näkyviin. Kun lisäämme kunkin nimen numerot, se palauttaa aina 1. Ja jos lisäämme kaikki numerot, se palauttaa luettelon yksilöllisten nimien kokonaismäärän.
Tämä kaava toimii hyvin, kunnes alueella ei ole tyhjiä soluja. Mutta jos sinulla on tyhjiä soluja, se palauttaa #DIV/0! virhe.
Kuinka käsitellä tyhjiä soluja?
Ymmärrämme ensin, miksi se palauttaa virheen, kun alueella on tyhjä solu. Oletetaan, että meillä on alla esitetyt tiedot (solu A3 on tyhjä):
Jos käytämme samaa kaavaa, jota käytimme yllä, kaavan COUNTIF -osa palauttaa taulukon {2; 0; 3; 1; 3; 1; 2; 3; 1}. Koska solussa A3 ei ole tekstiä, sen määrä palautetaan 0: ksi.
Ja koska jaamme yhden tällä koko matriisilla, se palauttaa #DIV/0! virhe.
Voit käsitellä tätä jakovirhettä tyhjien solujen tapauksessa käyttämällä seuraavaa kaavaa:
= SUMPRODUCT ((1/COUNTIF (NIMET, NAMES & ””)))
Yksi tähän kaavaan tekemämme muutos on COUNTIF -funktion kriteerit. Olemme käyttäneet NAMES & ”” -nimeä NAMES -sijasta. Näin tekemällä kaava palauttaa tyhjien solujen määrän (aiemmin se palautti 0, jossa oli tyhjä solu).
HUOMAUTUS: Tämä kaava laskee tyhjät solut ainutlaatuiseksi arvoksi ja palauttaa sen tulokseen.
Yllä olevassa esimerkissä tuloksen pitäisi olla 5, mutta se palauttaa 6, koska tyhjä solu lasketaan yhdeksi ainutlaatuisista arvoista.
Tässä on kaava, joka huolehtii tyhjistä soluista eikä laske sitä lopputulokseen:
= SUMPRODUCT ((NIMET ””)/COUNTIF (NIMET, NAMES & ””))
Tässä kaavassa 1: n sijaan osoittajana olemme käyttäneet NAMES ””. Tämä palauttaa joukon TOSIA ja EPÄTOSIA. Se palauttaa EPÄTOSI aina, kun solu on tyhjä. Koska TOSI on yhtä kuin 1 ja EPÄTOSI on 0 laskelmissa, tyhjiä soluja ei lasketa lukijaksi 0 (EPÄTOSI).
Nyt kun meillä on kaavan perusrunko valmiina, voimme mennä askeleen pidemmälle ja laskea erilaisia tietotyyppejä.
Tekstiä sisältävien ainutlaatuisten arvojen laskeminen Excelissä
Käytämme samaa käsitettä yllä, kun luomme kaavan, joka laskee vain ainutlaatuiset tekstiarvot.
Tässä on kaava, joka laskee ainutlaatuiset tekstiarvot Excelissä:
= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))
Kaikki, mitä olemme tehneet, on käyttänyt laskijana kaavaa ISTEXT (NAMES). Se palauttaa arvon TRUE, kun solu sisältää tekstiä, ja FALSE, jos ei. Se ei laske tyhjiä soluja, mutta laskee solut, joissa on tyhjä merkkijono (“”).
Numeeristen ainutlaatuisten arvojen laskeminen Excelissä
Tässä on kaava, joka laskee ainutlaatuiset numeeriset arvot Excelissä
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NIMES, NAMES & ””))
Tässä käytetään numerointia ISNUMBER (NAMES). Se palauttaa TOSI, kun solu sisältää numeerista tietotyyppiä, ja EPÄTOSI, jos ei. Se ei laske tyhjiä soluja.
