Vispārēja formula
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Kopsavilkums
Excel satur iebūvētu sākotnējo iestatījumu, lai izceltu dublētās vērtības ar nosacītu formatējumu, taču tas darbojas tikai šūnu līmenī. Ja vēlaties izcelt visas rindas, kas ir dublikāti, jums jāizmanto sava formula, kā paskaidrots tālāk.
Ja vēlaties izcelt dublikātu rindas nešķirotajā datu kopā un nevēlaties pievienot palīga kolonnu, varat izmantot formulu, kas izmanto funkciju COUNTIFS, lai skaitītu dublētās vērtības katrā datu kolonnā.
Piemēram, ja šūnās B4: D11 ir vērtības un vēlaties izcelt visas rindu dublikātus, varat izmantot diezgan neglītu formulu:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Nosaukti tīrākas sintakses diapazoni
Iemesls, kāpēc iepriekš minētā formula ir tik neglīta, ir tas, ka mums ir pilnībā jānoslēdz katrs kolonnu diapazons, pēc tam izmantojām jauktu atsauci, lai pārbaudītu katru šūnu katrā kolonnā. Ja katrai datu kolonnai izveidojat nosauktus diapazonus: col_a, col_b un col_c, formulu var rakstīt ar daudz tīrāku sintaksi:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Paskaidrojums
Formulā COUNTIFS saskaita, cik reižu katra vērtība šūnā tiek parādīta kolonnā “vecāks”. Pēc definīcijas katrai vērtībai ir jāparādās vismaz vienu reizi, tāpēc, ja skaitlis> 1, vērtībai jābūt dublikātam. Atsauces ir rūpīgi bloķētas, tāpēc formula atgriezīsies patiesībā tikai tad, kad visas 3 šūnas pēc kārtas attiecīgajās kolonnās būs redzamas vairāk nekā vienu reizi.
Palīga kolonnas opcija "krāpjas", apvienojot visas rindas vērtības vienā šūnā, izmantojot savienojumu. Tad COUNTIF vienkārši saskaita, cik reižu šī saīsinātā vērtība tiek parādīta D slejā.
Palīga kolonna + savienojums
Ja jums nav iebildumu, ja datiem pievienojat palīga kolonnu, varat diezgan daudz vienkāršot nosacītā formatēšanas formulu. Palīgkolonnā savienojiet visu kolonnu vērtības. Piemēram, E slejā pievienojiet formulu, kas izskatās šādi:
=B4&C4&D4
Pēc tam nosacītā formatēšanas kārtulā izmantojiet šo formulu:
=COUNTIF($E$4:$E$11,$E4)>1
Tas ir daudz vienkāršāks noteikums, un, ja vēlaties, varat paslēpt palīga kolonnu.
Ja jums ir patiešām daudz kolonnu, varat izmantot funkciju TEXTJOIN (Excel 2016 365), lai veiktu savienošanu, izmantojot diapazonu:
=TEXTJOIN(",",TRUE,A1:Z1)
Pēc tam varat izmantot COUNTIF, kā norādīts iepriekš.
SUMPRODUCT
Ja izmantojat Excel versiju pirms 2007. gada, varat izmantot SUMPRODUCT šādi:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
