Ranije smo naučili kako brojati jedinstvene vrijednosti u rasponu. Također smo naučili kako izvući jedinstvene vrijednosti iz raspona. U ovom ćemo članku naučiti kako računati jedinstvenu vrijednost u rasponu s uvjetom u excelu.
Opća formula
{= SUM (-(FREQUENCY (IF (stanje, MATCH (raspon, raspon, 0)), ROW (raspon) -ROW (prva ćelija u rasponu) +1)> 0))}
To je formula niza, koristite CTRL+SHIFT+ENTER
Stanje : Kriteriji prema kojima želite dobiti jedinstvene vrijednosti.
Domet : raspon u kojem želite dobiti jedinstvene vrijednosti.
firstCell u dosegu: To je referenca prve ćelije u domet. Ako je raspon A2: A10 onda je to A2.
Primjer:
Ovdje imam ove podatke o imenima. Odgovarajuće klase navedene su u susjednom stupcu. Moramo brojati jedinstvena imena u svakoj klasi.
Koristeći gornju generičku formulu napišite ovu formulu u E2
{= SUM (-(FREKVENCIJA (IF (B2: B19 = "Klasa 1", MATCH (A2: A19, A2: A19,0)), ROW (A2: A19) -ROW (A2) +1)> 0 ))}
Gornja formula vraća jedinstvenu vrijednost u Excelovom rasponu A2: A19 pod uvjetom B2: B19 = "Klasa 1".
Da biste dobili jedinstvene vrijednosti u različitim klasama, promijenite kriterije. Ovdje smo ga kodirali tvrdo, ali možete dati i referencu ćelije. Upotrijebite imenovane raspone ili apsolutno referenciranje raspona ako ne želite da se i oni promijene.
Kako radi?
Slomimo ga iznutra.
AKO(B2: B19 = "Klasa 1",UTAKMICA(A2: A19, A2: A19,0))
B2: B19 = "Klasa 1": Ovaj dio vraća niz vrijednosti true i false. ISTINA za svaku utakmicu.
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE….}
UTAKMICA(A2: A19, A2: A19,0): ovaj dio će vratiti prvo mjesto svake vrijednosti u rasponu A2: A19 prema vlasništvu MATCH -a.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Sada ćemo za svaku TRUE vrijednost dobiti poziciju, a za false ćemo dobiti FALSE. Dakle, za cijelu IF izjavu ćemo dobiti
{1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE}.
Zatim prelazimo na frekvencijski dio.
FREKVENCIJA(AKO(B2: B19 = "Klasa 1",UTAKMICA(A2: A19, A2: A19,0)),RED(A2: A19)-RED(A2) +1)
RED (A2: A19): Ovo vraća broj retka svake ćelije u rasponu 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): Sada od svakog broja retka oduzimamo broj prvog reda. Ovo vraća niz serijskog broja počevši od 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Budući da želimo imati serijski broj koji počinje od 1, tome dodamo 1.
RED (A2: A19) -RED (A2) +1. To nam daje niz serijskih brojeva počevši od 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
To će nam pomoći u dobivanju jedinstvenog računa.
Sada imamo: FREKVENCIJA({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})
Ovo vraća frekvenciju svakog broja u danom nizu. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Ovdje je svaki pozitivan broj označavao pojavu jedinstvene vrijednosti kada su kriteriji zadovoljeni. Moramo brojati vrijednosti veće od 0 u ovom nizu. Za to provjeravamo> 0. Ovo će vratiti TRUE i FALSE. Pretvaramo istinito lažno pomoću - (dvostruki binarni operator).
IZNOS(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) to se prevodi u SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
I na kraju dobivamo jedinstveni broj imena u rasponu prema kriterijima kao 5.
Znam da je to malo složeno za razumjeti, ali provjeravate to iz opcije procjene formule.
Za brojanje jedinstvenih vrijednosti s više kriterija možemo koristiti logičku logiku:
Računajte jedinstvenu vrijednost s više kriterija s logikom
{= SUM (-(FREQUENCY (IF (condition1 * Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Gore navedena generička formula može računati jedinstvene vrijednosti u više uvjeta i kada su svi istiniti.
Računajte jedinstvenu vrijednost s više kriterija ili logikom
{= SUM (-(FREQUENCY (IF (condition1 + Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Ova generička formula može se koristiti za brojanje jedinstvenih vrijednosti s logikom Or. To znači da će se računati ako je bilo koji od uvjeta istinit.
Pa da, dečki, ovako računate jedinstvene vrijednosti u rasponu u više uvjeta. Ovo je malo složeno, ali je brzo. Kad ga počnete koristiti, shvatit ćete kako radi.
Ako imate bilo kakvih nedoumica u vezi s ovim člankom o excel formuli, javite mi u donjem odjeljku komentara.
Preuzmi datoteku:
Kako brojati jedinstvene vrijednosti u Excelu s kriterijimaExcel formula za izdvajanje jedinstvenih vrijednosti s popisa
Brojanje jedinstvenih vrijednosti u Excelu
Popularni članci:
Funkcija VLOOKUP u Excelu
COUNTIF u Excelu 2016
Kako koristiti funkciju SUMIF u Excelu
