Formuła ogólna
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Podsumowanie
Excel zawiera wbudowane ustawienie wstępne do wyróżniania zduplikowanych wartości za pomocą formatowania warunkowego, ale działa tylko na poziomie komórki. Jeśli chcesz podświetlić całe wiersze, które są duplikatami, musisz użyć własnej formuły, jak wyjaśniono poniżej.
Jeśli chcesz wyróżnić zduplikowane wiersze w nieposortowanym zestawie danych i nie chcesz dodawać kolumny pomocniczej, możesz użyć formuły korzystającej z funkcji LICZ.WARUNKI do zliczania zduplikowanych wartości w każdej kolumnie danych.
Na przykład, jeśli masz wartości w komórkach B4: D11 i chcesz podświetlić całe zduplikowane wiersze, możesz użyć raczej brzydkiej formuły:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Nazwane zakresy w celu uzyskania bardziej przejrzystej składni
Powodem, dla którego powyższa formuła jest tak brzydka, jest to, że musimy całkowicie zablokować każdy zakres kolumn, a następnie użyć mieszanego odwołania, aby przetestować każdą komórkę w każdej kolumnie. Jeśli utworzysz nazwane zakresy dla każdej kolumny w danych: col_a, col_b i col_c, formułę można zapisać przy użyciu znacznie bardziej przejrzystej składni:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Wyjaśnienie
W formule LICZ.WARUNKI zlicza, ile razy każda wartość w komórce pojawia się w kolumnie „nadrzędnej”. Z definicji każda wartość musi wystąpić co najmniej raz, więc gdy liczba> 1, wartość musi być zduplikowana. Odwołania są starannie zablokowane, więc formuła zwróci wartość true tylko wtedy, gdy wszystkie 3 komórki w wierszu pojawią się więcej niż raz w odpowiednich kolumnach.
Opcja kolumny pomocnika „oszukuje”, łącząc wszystkie wartości w wierszu razem w jednej komórce przy użyciu konkatenacji. Następnie LICZ.JEŻELI po prostu zlicza, ile razy ta połączona wartość pojawia się w kolumnie D.
Kolumna pomocnicza + konkatenacja
Jeśli nie masz nic przeciwko dodaniu kolumny pomocniczej do danych, możesz nieco uprościć formułę formatowania warunkowego. W kolumnie pomocniczej połącz wartości ze wszystkich kolumn. Na przykład dodaj formułę w kolumnie E, która wygląda tak:
=B4&C4&D4
Następnie użyj następującej formuły w regule formatowania warunkowego:
=COUNTIF($E$4:$E$11,$E4)>1
Jest to znacznie prostsza zasada i jeśli chcesz, możesz ukryć kolumnę pomocniczą.
Jeśli masz naprawdę dużą liczbę kolumn, możesz użyć funkcji TEXTJOIN (Excel 2016 365), aby wykonać konkatenację przy użyciu zakresu:
=TEXTJOIN(",",TRUE,A1:Z1)
Następnie możesz użyć LICZ.JEŻELI jak powyżej.
SUMPRODUCT
Jeśli używasz wersji programu Excel sprzed 2007 r., Możesz użyć SUMPRODUCT w następujący sposób:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
