Splošna formula
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Povzetek
Excel vsebuje vgrajeno prednastavitev za poudarjanje podvojenih vrednosti s pogojnim oblikovanjem, vendar deluje le na ravni celice. Če želite poudariti celotne vrstice, ki so podvojene, boste morali uporabiti svojo formulo, kot je razloženo spodaj.
Če želite poudariti podvojene vrstice v nerazvrščenem naboru podatkov in ne želite dodati pomožnega stolpca, lahko uporabite formulo, ki s funkcijo COUNTIFS šteje podvojene vrednosti v vsakem stolpcu podatkov.
Če imate na primer vrednosti v celicah B4: D11 in želite označiti celotne podvojene vrstice, lahko uporabite precej grdo formulo:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Poimenovani obsegi za čistejšo skladnjo
Razlog, da je zgornja formula tako grda, je ta, da moramo v celoti zakleniti vsak obseg stolpcev, nato pa uporabiti mešani sklic za testiranje vsake celice v vsakem stolpcu. Če ustvarite poimenovane obsege za vsak stolpec v podatkih: col_a, col_b in col_c, lahko formulo zapišemo s precej bolj čisto sintakso:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Pojasnilo
V formuli COUNTIFS šteje, kolikokrat se vsaka vrednost v celici prikaže v »nadrejenem« stolpcu. Po definiciji se mora vsaka vrednost pojaviti vsaj enkrat, zato mora biti pri štetju> 1 podvojena. Sklici so skrbno zaklenjeni, tako da bo formula vrnila true šele, ko se vse 3 celice zapored pojavijo večkrat v ustreznih stolpcih.
Možnost pomožnega stolpca "goljufa" tako, da združi vse vrednosti zaporedoma v eni celici s pomočjo združevanja. Nato COUNTIF preprosto prešteje, kolikokrat se ta združena vrednost prikaže v stolpcu D.
Pomožni stolpec + združitev
Če vas moti dodajanje stolpca pomočnika svojim podatkom, lahko formulo pogojnega oblikovanja precej poenostavite. V pomožnem stolpcu združite vrednosti iz vseh stolpcev. V stolpec E na primer dodajte formulo, ki je videti takole:
=B4&C4&D4
Nato v pravilu pogojnega oblikovanja uporabite naslednjo formulo:
=COUNTIF($E$4:$E$11,$E4)>1
To je veliko preprostejše pravilo in stolpec pomočnika lahko skrijete, če želite.
Če imate res veliko število stolpcev, lahko s funkcijo TEXTJOIN (Excel 2016 365) izvedete združevanje z obsegom:
=TEXTJOIN(",",TRUE,A1:Z1)
Nato lahko uporabite COUNTIF kot zgoraj.
POROČILO
Če uporabljate različico Excela pred letom 2007, lahko SUMPRODUCT uporabite takole:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
