Általános képlet
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Összegzés
Az Excel tartalmaz egy beépített előbeállítást az ismétlődő értékek feltételes formázással történő kiemeléséhez, de csak cellaszinten működik. Ha teljes sorokat szeretne kiemelni, amelyek duplikátumok, akkor saját képletét kell használnia, az alábbiakban leírtak szerint.
Ha kiemelni kívánja az ismétlődő sorokat a válogatás nélküli adathalmazban, és nem szeretne hozzáadni egy segítő oszlopot, használhat egy olyan képletet, amely a COUNTIFS függvény segítségével számolja az ismétlődő értékeket az adatok minden oszlopában.
Például, ha vannak értékei a B4: D11 cellákban, és teljes duplikált sorokat szeretne kiemelni, akkor meglehetősen csúnya képletet használhat:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
A tisztább szintaxis elnevezett tartományai
A fenti képlet annyira csúnya, hogy minden oszloptartományt teljesen le kell zárnunk, majd vegyes referenciával teszteltük az egyes oszlopok egyes celláit. Ha az adatok minden oszlopához elnevezett tartományokat hoz létre: col_a, col_b és col_c, a képlet sokkal tisztább szintaxissal írható:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Magyarázat
A képletben a COUNTIFS megszámolja, hogy egy cella egyes értékei hányszor jelennek meg a "szülő" oszlopban. Definíció szerint minden értéknek legalább egyszer meg kell jelennie, tehát amikor a szám> 1, akkor az értéknek duplikátumnak kell lennie. A hivatkozásokat gondosan lezárjuk, így a képlet csak akkor tér vissza igazra, ha a sorban mind a 3 cella többször is megjelenik a megfelelő oszlopokban.
A segítő oszlop opció „csal” azáltal, hogy a sorban lévő összes értéket összefűzi egyetlen cellában összefűzéssel. Ezután a COUNTIF egyszerűen megszámolja, hogy ez az összefűzött érték hányszor jelenik meg a D oszlopban.
Segítő oszlop + összefűzés
Ha nem bánja, ha hozzáad egy segítő oszlopot az adataihoz, meglehetősen leegyszerűsítheti a feltételes formázási képletet. Egy segítő oszlopban összefűzheti az összes oszlop értékeit. Például vegyen fel egy képletet az E oszlopba, amely így néz ki:
=B4&C4&D4
Ezután használja a következő képletet a feltételes formázási szabályban:
=COUNTIF($E$4:$E$11,$E4)>1
Ez sokkal egyszerűbb szabály, és ha akarja, elrejtheti a segítő oszlopot.
Ha valóban sok oszlopa van, akkor a TEXTJOIN függvény (Excel 2016 365) segítségével összefűzhető egy tartomány segítségével:
=TEXTJOIN(",",TRUE,A1:Z1)
Ezután használhatja a COUNTIF-et a fentiek szerint.
ÖSSZEFOGLALÁS
Ha az Excel 2007 előtti verzióját használja, a következőképpen használhatja a SUMPRODUCT-ot:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
