Generisk formel
=SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1
Sammanfattning
Excel innehåller en inbyggd förinställning för att markera dubbla värden med villkorlig formatering, men den fungerar bara på cellnivå. Om du vill hitta och markera dubbla kolumner måste du använda din egen formel, som förklaras nedan.
För att markera dubbla kolumner kan du använda en formel baserad på SUMPRODUCT-funktionen. I exemplet som visas är formeln som används för att markera dubbla kolumner:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
Förklaring
Denna metod använder SUMPRODUCT för att räkna förekomsten av varje värde i tabellen, en rad i taget. Bara när samma värde visas på samma plats i alla tre raderna genereras ett antal. För cell B4 löses formeln så här:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1 =SUMPRODUCT(((1,1,1,1))*((1,0,1,0))*((1,0,1,0)))>1 =SUMPRODUCT((1,0,1,0))>1 =2>1 =TRUE
Observera att radreferenser är helt absoluta, medan cellreferenser blandas, med endast raden låst.
Med en hjälprad
Om du inte har något emot att lägga till en hjälprad i dina data kan du förenkla formeln för villkorlig formatering ganska mycket. Sammanfoga alla värden i kolumnen i en hjälprad. Sedan kan du använda COUNTIF på den ena raden för att räkna värden som visas mer än en gång och använda resultatet för att utlösa villkorlig formatering i hela kolumnen.
