Všeobecný vzorec
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Zhrnutie
Excel obsahuje vstavanú predvoľbu na zvýraznenie duplicitných hodnôt s podmieneným formátovaním, funguje však iba na úrovni bunky. Ak chcete zvýrazniť celé riadky, ktoré sú duplikáty, musíte použiť svoj vlastný vzorec, ako je vysvetlené nižšie.
Ak chcete zvýrazniť duplicitné riadky v netriedenej množine údajov a nechcete pridať pomocný stĺpec, môžete použiť vzorec, ktorý používa funkciu COUNTIFS na spočítanie duplicitných hodnôt v každom stĺpci údajov.
Napríklad ak máte hodnoty v bunkách B4: D11 a chcete zvýrazniť celé duplicitné riadky, môžete použiť pomerne škaredý vzorec:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Pomenované rozsahy pre čistejšiu syntax
Dôvod, prečo je vyššie uvedený vzorec taký škaredý, je ten, že musíme úplne uzamknúť každý rozsah stĺpcov, potom sme na testovanie každej bunky v každom stĺpci použili zmiešaný odkaz. Ak vytvoríte pomenované rozsahy pre každý stĺpec v dátach: col_a, col_b a col_c, vzorec je možné zapísať s oveľa čistejšou syntaxou:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Vysvetlenie
Vo vzorci COUNTIFS počíta, koľkokrát sa každá hodnota v bunke objaví v stĺpci „rodič“. Podľa definície sa každá hodnota musí objaviť aspoň raz, takže keď je počet> 1, musí ísť o duplikát. Odkazy sú starostlivo uzamknuté, takže vzorec bude pravdivý, iba ak sa všetky 3 bunky v riadku objavia v príslušných stĺpcoch viac ako raz.
Možnosť pomocného stĺpca „podvádza“ kombináciou všetkých hodnôt v riadku dohromady v jednej bunke pomocou zreťazenia. Potom COUNTIF jednoducho spočíta, koľkokrát sa táto zreťazená hodnota objaví v stĺpci D.
Pomocný stĺpec + zreťazenie
Ak vám neprekáža pridanie pomocného stĺpca k vašim údajom, môžete vzorec podmieneného formátovania trochu zjednodušiť. V pomocnom stĺpci zreťazte hodnoty zo všetkých stĺpcov. Napríklad do stĺpca E pridajte vzorec, ktorý vyzerá takto:
=B4&C4&D4
Potom použite nasledujúci vzorec v pravidle podmieneného formátovania:
=COUNTIF($E$4:$E$11,$E4)>1
Toto je oveľa jednoduchšie pravidlo a ak chcete, môžete stĺpec pomocníka skryť.
Ak máte skutočne veľký počet stĺpcov, môžete pomocou funkcie TEXTJOIN (Excel 2016 365) vykonať zreťazenie pomocou rozsahu:
=TEXTJOIN(",",TRUE,A1:Z1)
Potom môžete použiť COUNTIF ako je uvedené vyššie.
SUMPRODUKT
Ak používate verziu programu Excel pred rokom 2007, môžete program SUMPRODUCT použiť takto:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
