Anksčiau mes išmokome skaičiuoti unikalias reikšmes diapazone. Mes taip pat išmokome išgauti unikalias vertes iš diapazono. Šiame straipsnyje mes sužinosime, kaip skaičiuoti unikalią vertę diapazone su „Excel“ būkle.
Bendra formulė
{= SUMA (-(FREQUENCY (IF (sąlyga, MATCH (diapazonas, diapazonas, 0))), ROW (diapazonas) -ROW (firstCell in range) +1)> 0)}}
Tai masyvo formulė, naudokite CTRL+SHIFT+ENTER
Būklė : Kriterijai, pagal kuriuos norite gauti unikalių vertybių.
diapazonas : diapazonas, kuriame norite gauti unikalių verčių.
„FirstCell“ diapazone: Tai yra pirmojo langelio nuoroda diapazonas. Jei diapazonas yra A2: A10, tai yra A2.
Pavyzdys:
Čia aš turiu šiuos vardų duomenis. Atitinkamos klasės paminėtos gretimame stulpelyje. Kiekvienoje klasėje turime suskaičiuoti unikalius vardus.
Naudodami aukščiau pateiktą bendrąją formulę, parašykite šią formulę E2
{= SUMA (-(DAŽNUMAS (IF (B2: B19 = "1 klasė", MATCH (A2: A19, A2: A19,0))), EILUTĖ (A2: A19) -ROW (A2) +1)> 0 ))}
Aukščiau pateikta formulė grąžina unikalią vertę „Excel“ diapazone A2: A19 su sąlyga, kad B2: B19 = „1 klasė“.
Norėdami gauti unikalių vertybių skirtingose klasėse, pakeiskite kriterijus. Čia jį užkoduodavome, bet taip pat galite nurodyti langelio nuorodą. Jei nenorite, kad jie taip pat keistųsi, naudokite pavadintus diapazonus arba absoliučią nuorodą.
Kaip tai veikia?
Išardykime jį iš vidaus.
JEI(B2: B19 = "1 klasė",Rungtynės(A2: A19, A2: A19,0))
B2: B19 = "1 klasė": Ši dalis grąžins masyvą teisingų ir klaidingų. TIKRA kiekvienos rungtynės.
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE….}
Rungtynės(A2: A19, A2: A19,0): ši dalis grąžins pirmąją kiekvienos diapazono vertės vietą A2: A19 pagal MATCH nuosavybę.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Dabar už kiekvieną TIKRĄ vertę gausime poziciją, o už klaidingą - FALSE. Taigi už visą IF pareiškimą gausime
{1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE}.
Toliau pereiname prie dažnio dalies.
DAŽNUMAS(JEI(B2: B19 = "1 klasė",Rungtynės(A2: A19, A2: A19,0)),EILUTĖ(A2: A19)-EILUTĖ(A2) +1)
EILUTĖ (A2: A19): Tai grąžina kiekvieno langelio eilutės numerį diapazone A2: A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
ROW (A2: A19) -ROW (A2): Dabar iš kiekvienos eilutės numerio atimame pirmosios eilės numerį. Tai grąžina serijos numerių masyvą, pradedant nuo 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Kadangi norime, kad serijos numeris prasidėtų nuo 1, prie jo pridedame 1.
EILUTĖ (A2: A19) -RUNA (A2) +1. Tai suteikia mums serijos numerių masyvą, pradedant nuo 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Tai padės mums susikurti unikalią būklę.
Dabar mes turime: DAŽNUMAS({1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Tai grąžina kiekvieno skaičiaus dažnį tam tikrame masyve. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Čia kiekvienas teigiamas skaičius nurodo unikalios vertės atsiradimą, kai kriterijai yra įvykdyti. Šiame masyve turime skaičiuoti didesnes nei 0 reikšmes. Norėdami tai padaryti, mes jį patikriname> 0. Tai grąžins TIESA ir NETIESA. Mes konvertuojame tikrąją klaidą naudodami - (dvigubas dvejetainis operatorius).
SUMA(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) tai reiškia SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
Ir pagaliau gauname unikalų vardų skaičių pagal kriterijus kaip 5.
Žinau, kad tai šiek tiek sudėtinga suprasti, bet jūs tai patikrinate naudodami formulės vertinimo parinktį.
Norėdami suskaičiuoti unikalias vertes pagal kelis kriterijus, galime naudoti loginę logiką:
Skaičiuokite unikalią vertę su keliais kriterijais ir logika
{= SUMA (-(DAŽNUMAS (IF (sąlyga1 * sąlyga2, MATCH (diapazonas, diapazonas, 0))), ROW (diapazonas) -ROW (pirmoji ląstelė diapazone) +1)> 0)}}
Aukščiau pateikta bendroji formulė gali suskaičiuoti unikalias vertes keliomis sąlygomis ir kai visos jos yra teisingos.
Skaičiuokite unikalią vertę naudodami kelis kriterijus su arba logika
{= SUMA (-(DAŽNUMAS (IF (sąlyga1 + sąlyga2, MATCH (diapazonas, diapazonas, 0))), ROW (diapazonas) -ROW (pirmoji ląstelė diapazone) +1)> 0)}}
Šią bendrą formulę galima naudoti skaičiuojant unikalias vertes naudojant „Ar“ logiką. Tai reiškia, kad bus skaičiuojama, ar kuri nors iš sąlygų yra teisinga.
Taigi taip, vaikinai, taip jūs skaičiuojate unikalias vertes įvairiomis sąlygomis. Tai šiek tiek sudėtinga, tačiau greita. Kai tik pradėsite jį naudoti, sužinosite, kaip jis veikia.
Jei turite kokių nors abejonių dėl šio „Excel“ formulės straipsnio, praneškite man toliau pateiktame komentarų skyriuje.
Atsisiųsti failą:
Kaip skaičiuoti unikalias vertes „Excel“ naudojant kriterijus„Excel“ formulė, leidžianti išskirti unikalias vertes iš sąrašo
Skaičiuokite unikalias vertes „Excel“
Populiarūs straipsniai:
„VLOOKUP“ funkcija „Excel“
COUNTIF „Excel 2016“
Kaip naudoti „SUMIF“ funkciją „Excel“