Excel

Laske alueen yksilölliset tekstiarvot

Count Unique Text Values Range

Excel-kaava: Laske alueen yksilölliset tekstiarvotYleinen kaava
= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))
Yhteenveto

Voit laskea yksilölliset tekstiarvot alueella käyttämällä kaavaa, joka käyttää useita toimintoja: TAAJUUS , OTTELU , RIVI ja YHTEENVETO Esitetyssä esimerkissä kaava kohdassa F5 on:

 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

joka palauttaa 4, koska B5: ssä on 4 yksilöllistä nimeä: B14.



Huomaa: Toinen tapa laskea yksilölliset arvot on käytä COUNTIF-toimintoa . Tämä on paljon yksinkertaisempi kaava, mutta se voi toimia hitaasti suurilla tietojoukoilla. Kanssa Excel 365 , voit käyttää a yksinkertaisempi ja nopeampi kaava perustuen AINUTLAATUINEN .



Selitys

Tämä kaava on monimutkaisempi kuin vastaava kaava, joka käyttää FREQUENCY: tä laskea yksilölliset numeeriset arvot koska FREQUENCY ei toimi ei-numeeristen arvojen kanssa. Tämän seurauksena suuri osa kaavasta muuntaa ei-numeerisen datan yksinkertaisesti numeeriseksi dataksi, jota FREQUENCY pystyy käsittelemään.

Työskentely sisältä ulospäin, MATCH-toimintoa käytetään saamaan jokaisen datassa näkyvän kohteen sijainti:



 
 MATCH (B5:B14,B5:B14,0)

MATCHin tulos on taulukko kuten tämä:

 
{1114466699}

Koska MATCH palauttaa aina ensimmäinen täsmää, arvoissa, jotka näkyvät useammin kuin kerran tiedoissa, palautetaan sama sijainti. Esimerkiksi, koska 'Jim' näkyy 3 kertaa luettelossa, hän näkyy tässä taulukossa 3 kertaa numerona 1.

Tämä taulukko syötetään FREQUENCY-muotoon data_array Perustelu. roskakorit argumentti muodostetaan kaavan tästä osasta:



käyttämällä exceliä keskihajonnan löytämiseen
 
 ROW (B5:B14)- ROW (B5)+1)

joka rakentaa a peräkkäinen numeroluettelo jokaiselle datan arvolle:

 
{12345678910}

Tässä vaiheessa FREQUENCY määritetään seuraavasti:

 
 FREQUENCY ({1114466699},{12345678910})

FREQUENCY palauttaa joukon numeroita, jotka ilmaisevat binäärin järjestämän dataryhmän jokaisen luvun määrän. Kun numero on jo laskettu, FREQUENCY palauttaa nollan. Tämä on keskeinen piirre tämän kaavan toiminnassa. FREQUENCY-tulos on tällainen taulukko:

kaikki tietokantatoiminnot edellyttävät kentän nimeä yhtenä argumenttina.
 
{30020300200} // output from FREQUENCY

Huomautus: FREQUENCY palauttaa aina matriisin, jossa on yksi kohde enemmän kuin roskakorit .

Voimme nyt kirjoittaa kaavan uudelleen näin:

 
= SUMPRODUCT (--({30020300200}>0))

Seuraavaksi tarkistamme, ovatko arvot suurempia kuin nolla (> 0), joka muuntaa luvut TOSiksi tai EPÄTOSI, ja sitten kaksoisnegatiivin (-) avulla muunnetaan TOSI ja EPÄTOSI arvoiksi 1 ja 0. Nyt meillä on:

 
= SUMPRODUCT ({10010100100})

Lopuksi SUMPRODUCT yksinkertaisesti lisää numerot ylös ja palauttaa kokonaismäärän, joka tässä tapauksessa on 4.

Tyhjien solujen käsittely

Alueen tyhjät solut saavat kaavan palauttamaan # N / A -virheen. Voit käsitellä tyhjiä soluja käyttämällä monimutkaisempaa matriisikaavaa, joka käyttää IF-funktiota suodattamaan tyhjät arvot:

 
{= SUM ( IF ( FREQUENCY ( IF (data'',  MATCH (data,data,0)), ROW (data)- ROW (data.firstcell)+1),1))}

Huomaa: IF: n lisääminen tekee tästä taulukon kaava joka vaatii control-shift-enter.

Lisätietoja, katso tämä sivu .

Alkaen Mike Givin erinomainen taulukko taulukkoista, Control-Shift-Enter.

Muita tapoja laskea ainutlaatuiset arvot

Jos sinulla on Excel 365, voit käyttää UNIQUE-toiminto että laskea yksilölliset arvot paljon yksinkertaisemmalla kaavalla.

TO kääntöpöytä on myös erinomainen tapa laskea ainutlaatuiset arvot.

Kirjailija Dave Bruns


^