Šioje pamokoje sužinosite, kaip skaičiuoti unikalias „Excel“ vertes naudojant formules (COUNTIF ir SUMPRODUCT funkcijos).
Kaip suskaičiuoti unikalias vertes „Excel“
Tarkime, kad turime duomenų rinkinį, kaip parodyta žemiau:
Šios pamokos tikslais aš pavadinsiu diapazoną A2: A10 kaip PAVADINIMAI. Toliau formulėse naudosime šį pavadintą diapazoną.
Taip pat žiūrėkite: Kaip sukurti pavadintus diapazonus „Excel“.
Šiame duomenų rinkinyje yra pasikartojimų diapazone NAMES. Norėdami gauti unikalių vardų iš šio duomenų rinkinio (A2: A10), galime naudoti COUNTIF ir SUMPRODUCT funkcijų derinį, kaip parodyta žemiau:
= SUMPRODUCT (1/COUNTIF (PAVADINIMAI, PAVADINIMAI))
Kaip veikia ši formulė?
Išskaidykime šią formulę, kad geriau suprastume:
- COUNTIF (VARDAI, VARDAI)
- Ši formulės dalis grąžina masyvą. Pirmiau pateiktame pavyzdyje tai būtų {2; 2; 3; 1; 3; 1; 2; 3; 2}. Čia esantys skaičiai rodo, kiek kartų reikšmė atsiranda tam tikrame ląstelių diapazone.
Pvz., Vardas yra Bobas, kuris sąraše pasitaiko du kartus, todėl Bobui būtų pateiktas skaičius 2. Panašiai Steve'as pasitaiko tris kartus, todėl Styvui grąžinamas 3.
- Ši formulės dalis grąžina masyvą. Pirmiau pateiktame pavyzdyje tai būtų {2; 2; 3; 1; 3; 1; 2; 3; 2}. Čia esantys skaičiai rodo, kiek kartų reikšmė atsiranda tam tikrame ląstelių diapazone.
- 1/COUNTIF (VARDAI, VARDAI)
- Ši formulės dalis grąžintų masyvą - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Kadangi mes padalijome 1 iš masyvo, jis grąžina šį masyvą.
Pavyzdžiui, pirmasis aukščiau pateiktas masyvo elementas buvo 2. Kai 1 yra padalintas iš 2, jis grąžina .5.
- Ši formulės dalis grąžintų masyvą - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (PAVADINIMAI, PAVADINIMAI))
- SUMPRODUCT tiesiog prideda visus šiuos skaičius. Atminkite, kad jei Bobas sąraše įvyksta du kartus, aukščiau pateiktas masyvas grąžina .5 visur, kur sąraše buvo Bobo vardas. Panašiai, kadangi Steve'as sąraše pasirodo tris kartus, masyvas grąžina .3333333 kiekvieną kartą, kai pasirodo Steve'o vardas. Kai pridedame kiekvieno vardo skaičius, jis visada grąžina 1. O jei pridėsime visus skaičius, tai grąžins bendrą unikalių vardų skaičių sąraše.
Ši formulė veikia gerai, kol diapazone nėra tuščių langelių. Bet jei turite tuščių langelių, tai grąžins #DIV/0! klaida.
Kaip elgtis su tuščiomis ląstelėmis?
Pirmiausia suprasime, kodėl ji pateikia klaidą, kai diapazone yra tuščias langelis. Tarkime, kad turime duomenų rinkinį, kaip parodyta žemiau (A3 langelis tuščias):
Dabar, jei naudosime tą pačią formulę, kurią naudojome aukščiau, formulės dalis COUNTIF grąžina masyvą {2; 0; 3; 1; 3; 1; 2; 3; 1}. Kadangi langelyje A3 nėra teksto, jo skaičius grąžinamas kaip 0.
Ir kadangi mes padalijame 1 iš viso šio masyvo, tai grąžina #DIV/0! klaida.
Norėdami išspręsti šią padalijimo klaidą tuščių langelių atveju, naudokite šią formulę:
= SUMPRODUCT ((1/COUNTIF (PAVADINIMAI, VARDAI & "")))
Vienas šios formulės pakeitimas yra funkcijos COUNTIF kriterijų dalis. Mes naudojome NAMES & ”” vietoj NAMES. Tai darydama, formulė grąžintų tuščių langelių skaičių (anksčiau ji grąžino 0, kur buvo tuščias langelis).
PASTABA: Ši formulė tuščias ląsteles skaičiuotų kaip unikalią vertę ir grąžintų jas į rezultatą.
Anksčiau pateiktame pavyzdyje rezultatas turėtų būti 5, tačiau jis grąžina 6, nes tuščias langelis skaičiuojamas kaip viena iš unikalių verčių.
Čia yra formulė, kuri rūpinasi tuščiomis ląstelėmis ir neįskaito į galutinį rezultatą:
= SUMPRODUKTAS ((PAVADINIMAI ”)”/SKAIČIUS (VARDAI, VARDAI & ””))
Šioje formulėje vietoj 1 kaip skaitiklio naudojome PAVADINIMUS ““. Tai grąžina TIESŲ ir NETIESIŲ masyvą. Ji grąžina FALSE, kai yra tuščias langelis. Kadangi skaičiavimuose TRUE yra lygi 1, o FALSE - 0, tuščios ląstelės neskaičiuojamos, nes skaitiklis yra 0 (FALSE).
Dabar, kai turime pagrindinį formulės karkasą, galime žengti žingsnį toliau ir suskaičiuoti skirtingus duomenų tipus.
Kaip skaičiuoti unikalias „Excel“ vertes, kurios yra tekstas
Mes naudosime tą pačią aukščiau aptartą koncepciją, kad sukurtume formulę, kuri skaičiuos tik unikalias teksto vertes.
Čia yra formulė, kuri skaičiuos unikalias „Excel“ teksto vertes:
= SUMPRODUCT ((ISTEXT (PAVADINIMAI)/COUNTIF (PAVADINIMAI, VARDAI & "")))
Viskas, ką mes padarėme, kaip skaitiklis naudojome formulę ISTEXT (PAVADINIMAI). Grąžina TRUE, kai langelyje yra teksto, ir FALSE, jei jo nėra. Jis neskaičiuos tuščių langelių, bet skaičiuos ląsteles, kuriose yra tuščia eilutė („“).
Kaip skaičiuoti „Excel“ unikalias reikšmes, kurios yra skaitinės
Čia yra formulė, kuri skaičiuos unikalias skaitines vertes „Excel“
= SUMPRODUCT ((ISNUMBER (PAVADINIMAI))/COUNTIF (PAVADINIMAI, VARDAI & ""))
Čia kaip skaitiklį naudojame ISNUMBER (PAVADINIMAI). Grąžina TRUE, kai langelyje yra skaitinių duomenų tipas, ir FALSE, jei jo nėra. Jis neskaičiuoja tuščių langelių.
