Yleinen kaava
=SUMPRODUCT(1/COUNTIF(data,data))
Yhteenveto
Voit laskea yksittäisten arvojen lukumäärän solualueella käyttämällä kaavaa, joka perustuu COUNTIF- ja SUMPRODUCT-funktioihin. Esimerkkiesityksessä kaava kohdassa F6 on:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Selitys
Työskentelemällä sisältä ulospäin, COUNTIF on määritetty arvoille alueella B5: B14 käyttäen kaikkia näitä samoja arvoja kriteereinä:
COUNTIF(B5:B14,B5:B14)
Koska annamme kriteereille 10 arvoa, saamme takaisin taulukon, jolla on 10 tällaista tulosta:
(3;3;3;2;2;3;3;3;2;2)
Jokainen numero edustaa määrää - "Jim" näkyy 3 kertaa, "Sue" näkyy 2 kertaa ja niin edelleen.
Tämä taulukko on määritetty jakajaksi, jonka osoittaja on 1. Jaon jälkeen saamme toisen taulukon:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Kaikki arvot, jotka esiintyvät vain kerran alueella, näkyvät 1-arvoina, mutta useita kertoja esiintyvät arvot näkyvät murto-osina, jotka vastaavat useita. (ts. arvo, joka näkyy 4 kertaa tiedoissa, tuottaa 4 arvoa = 0,25).
Lopuksi SUMPRODUCT-funktio summaa kaikki matriisin arvot ja palauttaa tuloksen.
Tyhjien solujen käsittely
Yksi tapa käsitellä tyhjiä tai tyhjiä soluja on säätää kaava seuraavasti:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Liittämällä tyhjät merkkijonot ("") tietoihin estämme nollia pääsemästä COUNTIF: n luomaan taulukoon, kun tiedoissa on tyhjiä soluja. Tämä on tärkeää, koska nolla jakajassa saa kaavan heittämään # DIV / 0 -virheen. Se toimii, koska tyhjän merkkijonon ("") käyttö ehdoissa laskee tyhjät solut.
Vaikka tämä kaavan versio ei heitä # DIV / 0 -virhettä tyhjien solujen kanssa, se sisältää tyhjät solut laskentaan. Jos haluat jättää tyhjät solut pois laskennasta, käytä:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Tämän seurauksena tyhjien solujen määrä peruutetaan tekemällä osoitin nollaksi liitetyille laskelmille.
Hidas suorituskyky?
Tämä on hieno ja tyylikäs kaava, mutta se laskee paljon hitaammin kuin kaavat, jotka käyttävät FREQUENCY-toimintoa ainutlaatuisten arvojen laskemiseen. Suurempien tietojoukkojen kohdalla kannattaa ehkä vaihtaa kaavaan, joka perustuu FREQUENCY-toimintoon. Tässä on kaava numeerisille arvoille ja yksi teksti-arvoille.
