Obecný vzorec
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
souhrn
Excel obsahuje vestavěnou předvolbu pro zvýraznění duplicitních hodnot s podmíněným formátováním, ale funguje pouze na úrovni buňky. Pokud chcete zvýraznit celé řádky, které jsou duplikáty, budete muset použít svůj vlastní vzorec, jak je vysvětleno níže.
Pokud chcete zvýraznit duplicitní řádky v netříděné sadě dat a nechcete přidat pomocný sloupec, můžete použít vzorec, který používá funkci COUNTIFS k počítání duplicitních hodnot v každém sloupci dat.
Například pokud máte hodnoty v buňkách B4: D11 a chcete zvýraznit celé duplicitní řádky, můžete použít poměrně ošklivý vzorec:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Pojmenované rozsahy pro čistší syntaxi
Důvod, proč je výše uvedený vzorec tak ošklivý, spočívá v tom, že musíme plně uzamknout každý rozsah sloupců a poté použít smíšený odkaz k testování každé buňky v každém sloupci. Pokud vytvoříte pojmenované rozsahy pro každý sloupec v datech: col_a, col_b a col_c, vzorec lze zapsat s mnohem čistší syntaxí:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Vysvětlení
Ve vzorci COUNTIFS spočítá, kolikrát se každá hodnota v buňce objeví ve sloupci „nadřazený“. Podle definice se každá hodnota musí objevit alespoň jednou, takže když počet> 1, hodnota musí být duplikát. Odkazy jsou pečlivě uzamčeny, takže vzorec vrátí hodnotu true pouze v případě, že se všechny 3 buňky v řádku objeví v příslušných sloupcích více než jednou.
Možnost pomocného sloupce „podvádí“ kombinací všech hodnot v řadě společně v jedné buňce pomocí zřetězení. Pak COUNTIF jednoduše spočítá, kolikrát se tato zřetězená hodnota objeví ve sloupci D.
Pomocný sloupec + zřetězení
Pokud vám nevadí přidání pomocného sloupce do vašich dat, můžete poměrně zjednodušit vzorec podmíněného formátování. Ve sloupci pomocníka zřetězte hodnoty ze všech sloupců. Například do sloupce E přidejte vzorec, který vypadá takto:
=B4&C4&D4
Potom použijte následující vzorec v pravidle podmíněného formátování:
=COUNTIF($E$4:$E$11,$E4)>1
Toto je mnohem jednodušší pravidlo a pokud chcete, můžete sloupec pomocníka skrýt.
Pokud máte opravdu velký počet sloupců, můžete k provedení zřetězení pomocí rozsahu použít funkci TEXTJOIN (Excel 2016 365):
=TEXTJOIN(",",TRUE,A1:Z1)
Poté můžete použít COUNTIF, jak je uvedeno výše.
SUMPRODUKT
Pokud používáte verzi aplikace Excel před rokem 2007, můžete použít SUMPRODUCT takto:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
