Vispārēja formula
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Kopsavilkums
Lai atzīmētu pirmo dublikātu sarakstā, varat izmantot formulu, kuras pamatā ir funkcija COUNTIF. Pēc izvēles varat atzīmēt nākamos dublikātus ar citu marķieri. Parādītajā piemērā formula šūnā C4 ir:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Šī formula ir nokopēta kolonnā no C4 līdz C11.
Paskaidrojums
Būtībā šo formulu veido divas funkcijas COUNTIF kopas, kas ietītas IF funkcijā. Ārējais IF + COUNTIF vispirms pārbauda, vai attiecīgā vērtība (B4) sarakstā parādās vairāk nekā vienu reizi:
=IF(COUNTIF($B$4:$B$11,B4)>1
Ja nē, ārējā IF funkcija gala rezultātā atgriež tukšu virkni (""). Ja vērtība parādās vairāk nekā vienu reizi, mēs izpildām citu IF + COUNTIF kombināciju. Šis veic dublikātu atzīmēšanu:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Šajā formulas daļā tiek izmantota paplašinoša atsauce ($ B $ 4: B4), kas paplašinās, formulu nokopējot slejā. (Pirmais diapazona B4 ir absolūts (bloķēts), otrais ir relatīvs, tāpēc tas mainās, formulu nokopējot sarakstā.
Atcerieties, ka šī formulas daļa tiek izpildīta tikai tad, ja pirmais COUNTIF atgriež skaitli, kas ir lielāks par 1. Tātad katrā rindā formula pārbauda skaitu diapazonā līdz pašreizējai rindai. Ja skaits ir 1, dublikātu atzīmējam ar "x", jo tas ir pirmais, ko esam redzējuši. Ja tas nav 1, mēs zinām, ka tam jābūt nākošam dublikātam, un mēs atzīmējam ar "xx"
Pamata formula
Lai atzīmētu pirmo saraksta dublikātu tikai ar 0 vai 1, varat izmantot šo noņemto formulu, kurā tiek izmantots paplašināšanas diapazons un funkcija COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
Šī formula atgriezīs vērtību 1 tikai tad, ja vērtība būs divreiz sastopama - pirmā parādība atgriezīs nulli:
Lai atzīmētu otro un visus nākamos gadījumus, iepriekš F5 formula ir šāda:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Piezīme. Abos piemēros nulles pievienošana ir tikai vienkāršs veids, kā piespiest TRUE un FALSE vērtības uz 1 un 0.
Izmantojot COUNTIFS, nevis COUNTIF, dublikātu pārbaudes ietvaros ir iespējams novērtēt vērtības citās kolonnās. Katra papildu kolonna jāievada arī kā paplašināms diapazons.
