Selles õpetuses saate teada, kuidas Excelis ainulaadseid väärtusi valemite abil arvutada (funktsioonid COUNTIF ja SUMPRODUCT).
Unikaalsete väärtuste loendamine Excelis
Oletame, et meil on allpool näidatud andmekogum:
Selle õpetuse jaoks nimetan vahemiku A2: A10 NIMEKS. Edaspidi kasutame valemites seda nimega vahemikku.
Vaata ka: Kuidas nimega vahemikke Excelis luua.
Selles andmekogumis on kordus vahemikus NAMES. Selle andmestiku ainulaadsete nimede arvu saamiseks (A2: A10) saame kasutada COUNTIF ja SUMPRODUCT funktsioonide kombinatsiooni, nagu allpool näidatud:
= SUMPRODUCT (1/COUNTIF (NIMED, NAMES))
Kuidas see valem töötab?
Parema mõistmise huvides jagame selle valemi:
- COUNTIF (NIMED, NAMES)
- See valemi osa tagastab massiivi. Ülaltoodud näites oleks see {2; 2; 3; 1; 3; 1; 2; 3; 2}. Siin olevad numbrid näitavad, mitu korda väärtus antud lahtrite vahemikus esineb.
Näiteks nimi on Bob, mis esineb loendis kaks korda, seega tagastaks see Bobi jaoks numbri 2. Samamoodi esineb Steve kolm korda ja seega tagastatakse Steve'ile 3.
- See valemi osa tagastab massiivi. Ülaltoodud näites oleks see {2; 2; 3; 1; 3; 1; 2; 3; 2}. Siin olevad numbrid näitavad, mitu korda väärtus antud lahtrite vahemikus esineb.
- 1/COUNTIF (Nimed, nimed)
- See valemiosa tagastaks massiivi - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Kuna oleme massiiviga 1 jaganud, tagastab see selle massiivi.
Näiteks ülaltoodud massiivi esimene element oli 2. Kui 1 jagatakse 2 -ga, tagastab see väärtuse .5.
- See valemiosa tagastaks massiivi - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (NIMED, NAMES))
- SUMPRODUCT lihtsalt lisab kõik need numbrid. Pange tähele, et kui Bob esineb loendis kaks korda, tagastab ülaltoodud massiiv .5 kõikjal, kus loendis on Bobi nimi. Samamoodi, kuna Steve ilmub loendis kolm korda, tagastab massiiv .3333333 iga kord, kui Steve'i nimi ilmub. Kui lisame igale nimele numbrid, tagastab see alati 1. Ja kui liidame kõik numbrid, tagastab see loendis olevate unikaalsete nimede koguarvu.
See valem töötab hästi, kuni vahemikus pole tühje lahtreid. Aga kui teil on tühje lahtreid, tagastab see #DIV/0! viga.
Kuidas käsitseda tühje lahtreid?
Mõistame kõigepealt, miks see tagastab vea, kui vahemikus on tühi lahter. Oletame, et meil on allpool näidatud andmekogum (lahter A3 on tühi):
Kui kasutame sama valemit, mida kasutasime ülal, tagastab COUNTIFi valemiosa massiivi {2; 0; 3; 1; 3; 1; 2; 3; 1}. Kuna lahtris A3 pole teksti, tagastatakse selle arv 0 -ks.
Ja kuna me jagame 1 kogu selle massiiviga, tagastab see #DIV/0! viga.
Selle jaotusvea käsitlemiseks tühjade lahtrite korral kasutage järgmist valemit:
= SUMPRODUCT ((1/COUNTIF (NIMED, NIMED & ””)))
Üks muudatus selles valemis on funktsiooni COUNTIF kriteeriumide osa. Oleme kasutanud NAMES asemel NAMES & ””. Seda tehes tagastab valem tühjade lahtrite arvu (varem tagas see 0, kus oli tühi lahter).
MÄRKUS. See valem loeks tühjad lahtrid unikaalseks väärtuseks ja tagastab selle tulemuseks.
Ülaltoodud näites peaks tulemus olema 5, kuid tagastab 6, kuna tühi lahter loetakse üheks unikaalseks väärtuseks.
Siin on valem, mis hoolitseb tühjade lahtrite eest ja ei arvesta seda lõpptulemuses:
= SUMPRODUCT ((NIMED ””)/COUNTIF (NIMED, NIMED & ””))
Selles valemis oleme lugeja 1 asemel kasutanud NIMES ””. See tagastab hulga tõdesid ja valesid. See tagastab FALSE alati, kui lahtrit on tühi. Kuna TRUE võrdub arvutustes 1 ja FALSE võrdub 0 -ga, ei loeta tühje lahtreid, kuna lugeja on 0 (FALSE).
Nüüd, kui meil on valemi põhikarkass valmis, saame astuda sammu edasi ja loendada erinevaid andmetüüpe.
Kuidas arvutada Excelis unikaalseid väärtusi, mis on tekst
Kasutame sama eespool käsitletud kontseptsiooni, et luua valem, mis arvestab ainult unikaalseid tekstiväärtusi.
Siin on valem, mis loeb Exceli ainulaadsed tekstiväärtused:
= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NIMES, NAMES & ””)))
Kõik, mida oleme teinud, on lugejana kasutanud valemit ISTEXT (NAMES). Tagastab väärtuse TRUE, kui lahter sisaldab teksti, ja FALSE, kui see seda ei tee. See ei loe tühje lahtreid, vaid loeb lahtrid, millel on tühi string (“”).
Kuidas arvutada Excelis ainulaadseid väärtusi, mis on numbrilised
Siin on valem, mis loeb Excelis ainulaadseid arvväärtusi
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NIMES, NAMES & ””))
Siin kasutame lugejana ISNUMBER (NAMES). Tagastab väärtuse TRUE, kui lahter sisaldab numbrilisi andmetüüpe, ja FALSE, kui see seda ei tee. See ei arvesta tühje lahtreid.
