Üldine valem
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Kokkuvõte
Excel sisaldab sisseehitatud eelseadistust topeltväärtuste esiletõstmiseks tingimusliku vormindusega, kuid see töötab ainult lahtrite tasandil. Kui soovite esile tõsta duplikaatidena terveid ridu, peate kasutama oma valemit, nagu allpool selgitatud.
Kui soovite sortimata andmekogumis esile tuua duplikaatread ja te ei soovi lisada abiveergu, võite kasutada valemit, mis kasutab andmete igas veerus dubleeritud väärtuste funktsiooni COUNTIFS.
Näiteks kui teil on lahtrites B4: D11 väärtused ja soovite kogu duplikaatridu esile tõsta, võite kasutada üsna koledat valemit:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Nimetatud vahemikud puhtama süntaksi jaoks
Põhjus, miks ülaltoodud valem on nii kole, on see, et peame iga veeru vahemiku täielikult lukustama, seejärel kasutasime iga veeru iga lahtri testimiseks segatud viidet. Kui loote andmete igas veerus nimega vahemikud: col_a, col_b ja col_c, saab valemi kirjutada palju puhtama süntaksiga:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Selgitus
Valemis loeb COUNTIFS, mitu korda lahtris iga väärtus kuvatakse selle veerus „ema”. Definitsiooni järgi peab iga väärtus ilmuma vähemalt üks kord, nii et kui arv> 1, peab väärtus olema duplikaat. Viited on hoolikalt lukustatud, nii et valem vastab tõele alles siis, kui kõik kolm lahtrit järjest ilmuvad vastavatesse veergudesse rohkem kui üks kord.
Abistaja veeru valik "petab", ühendades kõik rea väärtused ühes lahtris liitmise abil. Seejärel loeb COUNTIF lihtsalt, kui mitu korda see liidetud väärtus veerus D ilmub.
Abistaja veerg + liitmine
Kui te ei viitsi oma andmetele abiveergu lisada, saate tingimusliku vormingu valemit üsna palju lihtsustada. Abistaja veerus ühendage kõigi veergude väärtused. Näiteks lisage veergu E valem, mis näeb välja järgmine:
=B4&C4&D4
Seejärel kasutage tingimusliku vormindamise reeglis järgmist valemit:
=COUNTIF($E$4:$E$11,$E4)>1
See on palju lihtsam reegel ja saate abistaja veeru peita, kui soovite.
Kui teil on tõesti palju veerge, saate vahemiku abil liitmise teostamiseks kasutada funktsiooni TEXTJOIN (Excel 2016 365):
=TEXTJOIN(",",TRUE,A1:Z1)
Seejärel saate kasutada COUNTIF-i nagu eespool.
SUMPRODUCT
Kui kasutate Exceli versiooni enne 2007. aastat, saate SUMPRODUCTi kasutada järgmiselt:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
