Generička formula
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Sažetak
Excel sadrži ugrađenu unaprijed postavljenu postavku za isticanje dvostrukih vrijednosti s uvjetnim oblikovanjem, ali djeluje samo na razini ćelije. Ako želite istaknuti cijele retke koji su duplikati, morat ćete upotrijebiti vlastitu formulu, kao što je objašnjeno u nastavku.
Ako želite istaknuti dvostruke retke u nerazvrstanom skupu podataka, a ne želite dodati pomoćni stupac, možete upotrijebiti formulu koja koristi funkciju COUNTIFS za brojanje dupliciranih vrijednosti u svakom stupcu podataka.
Na primjer, ako imate vrijednosti u ćelijama B4: D11 i želite istaknuti cijele duplikate redaka, možete upotrijebiti prilično ružnu formulu:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Imenovani rasponi za čistiju sintaksu
Razlog zašto je gornja formula toliko ružna jest taj što moramo u potpunosti zaključati svaki raspon stupaca, a zatim upotrijebiti mješovitu referencu za testiranje svake ćelije u svakom stupcu. Ako izradite imenovane raspone za svaki stupac u podacima: col_a, col_b i col_c, formula se može napisati s mnogo čišćom sintaksom:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Obrazloženje
U formuli, COUNTIFS broji koliko se puta svaka vrijednost u ćeliji pojavljuje u svom "roditeljskom" stupcu. Prema definiciji, svaka se vrijednost mora pojaviti barem jednom, pa kad je broj> 1, vrijednost mora biti duplikat. Referencije su pažljivo zaključane, pa će formula vratiti vrijednost true samo kada se sve 3 ćelije u nizu pojave više puta u odgovarajućim stupcima.
Opcija pomoćnog stupca "vara" kombinirajući sve vrijednosti u nizu zajedno u jednoj ćeliji pomoću spajanja. Tada COUNTIF jednostavno broji koliko se puta ova spojena vrijednost pojavljuje u stupcu D.
Stupac pomagača + spajanje
Ako vam ne smeta dodavanje pomoćnog stupca vašim podacima, možete prilično pojednostaviti formulu uvjetnog oblikovanja. U pomoćnom stupcu spojite vrijednosti iz svih stupaca. Na primjer, dodajte formulu u stupac E koja izgleda ovako:
=B4&C4&D4
Zatim upotrijebite sljedeću formulu u pravilu uvjetnog oblikovanja:
=COUNTIF($E$4:$E$11,$E4)>1
Ovo je puno jednostavnije pravilo i ako želite, možete sakriti pomoćni stupac.
Ako imate stvarno velik broj stupaca, možete koristiti funkciju TEXTJOIN (Excel 2016 365) za izvršavanje spajanja pomoću raspona:
=TEXTJOIN(",",TRUE,A1:Z1)
Tada možete koristiti COUNTIF kao gore.
SUMPRODUKT
Ako koristite verziju Excela prije 2007. godine, SUMPRODUCT možete koristiti ovako:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
