Yleinen kaava
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Yhteenveto
Voit laskea alueen yksilölliset teksti-arvot kaavalla, joka käyttää useita toimintoja: FREQUENCY, MATCH, ROW ja SUMPRODUCT. Esitetyssä esimerkissä kaava F5: ssä 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äyttää COUNTIF-toimintoa. Tämä on paljon yksinkertaisempi kaava, mutta se voi toimia hitaasti suurilla tietojoukoilla. Excel 365: n avulla voit käyttää yksinkertaisempaa ja nopeampaa kaavaa, joka perustuu ainutkertaiseen.
Selitys
Tämä kaava on monimutkaisempi kuin vastaava kaava, joka käyttää FREQUENCY-toimintoa yksilöllisten numeeristen arvojen laskemiseen, 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)
MATCH: n tulos on tällainen taulukko:
(1;1;1;4;4;6;6;6;9;9)
Koska MATCH palauttaa aina ensimmäisen ottelun sijainnin, tiedoissa useammin kuin kerran näkyvät arvot palauttavat saman sijainnin. 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-kohtaan data_array- argumenttina. Bins_array väite on rakennettu tämän osan, jolla on kaava:
ROW(B5:B14)-ROW(B5)+1)
joka rakentaa peräkkäisen numeroluettelon jokaiselle datan arvolle:
(1;2;3;4;5;6;7;8;9;10)
Tässä vaiheessa FREQUENCY määritetään seuraavasti:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY palauttaa joukon numeroita, jotka ilmaisevat kunkin matriisin dataryhmässä olevan numeron, järjestyksessä bin. Kun numero on jo laskettu, FREQUENCY palauttaa nollan. Tämä on keskeinen piirre tämän kaavan toiminnassa. FREQUENCY-tulos on tällainen taulukko:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Huomaa: FREQUENCY palauttaa aina matriisin, jossa on yksi kohde enemmän kuin bins_array .
Voimme nyt kirjoittaa kaavan uudelleen näin:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Seuraavaksi tarkistamme, ovatko arvot suurempia kuin nolla (> 0), joka muuntaa luvut TOSI tai EPÄTOSI, ja sitten kaksoisnegatiivin (-) avulla muunnetaan TOSI ja EPÄTOSI arvoksi 1 ja 0. Nyt meillä on:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
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 kaavan, joka vaatii control-shift-enter.
Lisätietoja on tällä sivulla.
Muita tapoja laskea yksilölliset arvot
Jos sinulla on Excel 365, voit UNIQUE-funktion avulla laskea ainutlaatuiset arvot paljon yksinkertaisemmalla kaavalla.
Pivot-taulukko on myös erinomainen tapa laskea ainutlaatuiset arvot.
