Formula generică
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
rezumat
Excel conține o presetare încorporată pentru evidențierea valorilor duplicate cu formatare condiționată, dar funcționează doar la nivelul celulei. Dacă doriți să evidențiați rânduri întregi care sunt duplicate, va trebui să utilizați propria formulă, așa cum se explică mai jos.
Dacă doriți să evidențiați rândurile duplicate într-un set de date nesortate și nu doriți să adăugați o coloană de ajutor, puteți utiliza o formulă care utilizează funcția COUNTIFS pentru a număra valorile duplicate în fiecare coloană a datelor.
De exemplu, dacă aveți valori în celulele B4: D11 și doriți să evidențiați rânduri duplicate întregi, puteți utiliza o formulă destul de urâtă:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Intervalele denumite pentru o sintaxă mai curată
Motivul pentru care formula de mai sus este atât de urâtă este că trebuie să blocăm complet fiecare gamă de coloane, apoi să folosim o referință mixtă pentru a testa fiecare celulă din fiecare coloană. Dacă creați intervale denumite pentru fiecare coloană din date: col_a, col_b și col_c, formula poate fi scrisă cu o sintaxă mult mai curată:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Explicaţie
În formulă, COUNTIFS numără de câte ori fiecare valoare dintr-o celulă apare în coloana „părinte”. Prin definiție, fiecare valoare trebuie să apară cel puțin o dată, deci atunci când numărul> 1, valoarea trebuie să fie un duplicat. Referințele sunt blocate cu atenție, astfel încât formula să revină adevărată numai atunci când toate cele 3 celule dintr-un rând apar de mai multe ori în coloanele lor respective.
Opțiunea de coloană de ajutor „înșeală” prin combinarea tuturor valorilor într-un rând împreună într-o singură celulă utilizând concatenarea. Apoi COUNTIF numără pur și simplu de câte ori apare această valoare concatenată în coloana D.
Coloana de ajutor + concatenare
Dacă nu vă deranjează să adăugați o coloană de ajutor la datele dvs., puteți simplifica destul de mult formula de formatare condițională. Într-o coloană de ajutor, concatenează valorile din toate coloanele. De exemplu, adăugați o formulă în coloana E care arată astfel:
=B4&C4&D4
Apoi utilizați următoarea formulă în regula de formatare condiționată:
=COUNTIF($E$4:$E$11,$E4)>1
Aceasta este o regulă mult mai simplă și puteți ascunde coloana de ajutor dacă doriți.
Dacă aveți un număr foarte mare de coloane, puteți utiliza funcția TEXTJOIN (Excel 2016 365) pentru a efectua concatenarea utilizând un interval:
=TEXTJOIN(",",TRUE,A1:Z1)
Puteți utiliza apoi COUNTIF ca mai sus.
SUMPRODUCT
Dacă utilizați o versiune de Excel înainte de 2007, puteți utiliza SUMPRODUCT astfel:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
