Генеричка формула
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Резиме
Екцел садржи уграђену унапред подешену поставку за истицање дупликата вредности условним форматирањем, али ради само на нивоу ћелије. Ако желите да истакнете читаве редове који су дупликати, мораћете да користите своју формулу, као што је објашњено у наставку.
Ако желите да истакнете дупликате редова у несортованом скупу података, а не желите да додате помоћни ступац, можете да користите формулу која користи функцију ЦОУНТИФС за бројање дуплираних вредности у свакој колони података.
На пример, ако имате вредности у ћелијама Б4: Д11 и желите да истакнете читаве дупликате редова, можете користити прилично ружну формулу:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Именовани опсези за чистију синтаксу
Разлог због којег је горња формула толико ружна је тај што морамо у потпуности закључати сваки опсег колона, а затим користити мешовиту референцу за тестирање сваке ћелије у свакој колони. Ако креирате именоване опсеге за сваку колону у подацима: цол_а, цол_б и цол_ц, формула се може написати са много чистијом синтаксом:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Објашњење
У формули, ЦОУНТИФС броји колико се пута свака вредност у ћелији појављује у својој „родитељској“ колони. По дефиницији, свака вредност мора да се појави најмање једном, па када је број> 1, вредност мора бити дупликат. Референце се пажљиво закључавају, па ће формула вратити вредност труе само када се све 3 ћелије у низу појаве више пута у одговарајућим колонама.
Опција помоћне колоне „вара“ комбиновањем свих вредности у низу заједно у једној ћелији помоћу спајања. Тада ЦОУНТИФ једноставно броји колико се пута ова спојена вредност појављује у колони Д.
Помоћна колона + спајање
Ако вам не смета додавање помоћне колоне вашим подацима, можете прилично поједноставити формулу условног форматирања. У помоћној колони спојите вредности из свих колона. На пример, додајте формулу у колону Е која изгледа овако:
=B4&C4&D4
Затим користите следећу формулу у правилу условног форматирања:
=COUNTIF($E$4:$E$11,$E4)>1
Ово је много једноставније правило и ако желите, можете сакрити помоћни ступац.
Ако имате заиста велики број колона, можете да користите функцију ТЕКСТЈОИН (Екцел 2016 365) да бисте извршили спајање помоћу опсега:
=TEXTJOIN(",",TRUE,A1:Z1)
Тада можете да користите ЦОУНТИФ као горе.
СУМПРОДУЦТ
Ако користите верзију Екцела пре 2007. године, СУМПРОДУЦТ можете користити овако:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
