I går kväll i Craig Crossmans Computer America radioprogram hade Joe från Boston en fråga:
Jag har en kolumn med fakturanummer. Hur kan jag använda Excel för att markera dubbletterna?
Jag föreslog att använda villkorliga format och COUNTIF-formeln. Här är detaljerna om hur du får det att fungera.
Vi vill ställa in villkorlig formatering för hela intervallet, men det är lättare att ställa in ett villkorligt format för den första cellen i intervallet och sedan kopiera det villkorliga formatet. I vårt fall har cell A1 en rubrik med fakturanummer, så jag väljer cell A2 och från menyn väljer du Format> Villkorlig formatering. Dialogrutan Villkorlig formatering börjar med den första rullgardinsmenyn som säger "Cellvärde är". Om du trycker på pilen bredvid den här kan du välja "Formula Is".
Efter att ha valt "Formel är" ändras dialogrutan. Istället för rutor för "Mellan x och y" finns det nu en enda formelruta. Denna formelbox är otroligt kraftfull. Du kan skriva in vilken formel du kan drömma om, så länge den formeln utvärderas till SANT eller FALSK.
I vårt fall måste vi använda en COUNTIF-formel. Formeln för att skriva i rutan är
=COUNTIF(A:A,A2)>1
På engelska står det här, "titta igenom hela kolumnens intervall. Räkna hur många celler i det intervallet som har samma värde som det som finns i A2. (Det är verkligen viktigt att" A2 "i formeln pekar på nuvarande cell - cellen som du ställer in den villkorliga formateringen i. Så - om dina data finns i kolumn E och du ställer in den första villkorliga formateringen i E5, så skulle formeln vara =COUNTIF(E:E,E5)>0). Sedan jämför vi för att se om det räknas är> 1. Helst, utan dubbletter, kommer räkningen alltid att vara 1 - eftersom cell A2 är i intervallet - bör vi hitta exakt en cell i kolumn A som innehåller samma värde som A2.
Klicka på Format…
Nu är det dags att välja ett motbjudande format. Det finns tre flikar överst i dialogrutan Formatceller. Fliken Font är vanligtvis först, så du kan välja ett fet, rött typsnitt, men jag gillar något mer motbjudande. Jag klickar vanligtvis på fliken Mönster och väljer antingen ljusröd eller ljusgul. Välj färg och klicka sedan på OK för att stänga dialogrutan Formatera celler.
Du ser det valda formatet i rutan "Förhandsgranska format som ska användas". Klicka på OK för att stänga dialogrutan Villkorlig formatering …
… och ingenting händer. Wow. Om det här är första gången du konfigurerar villkorlig formatering, skulle det vara riktigt trevligt att få lite feedback här om att det fungerade. Men om du inte har turen att 1098 i cell A2 är en duplikat av någon annan cell, är villkoret inte sant, och det ser ut som att inget hände.
Du måste kopiera den villkorliga formateringen från A2 ner till de andra cellerna i ditt intervall. Gör Redigera> Kopiera med markören i A2. Tryck på Ctrl + mellanslag för att välja hela kolumnen. Gör Redigera> Klistra in special. Klicka på Formater i dialogrutan Klistra in special. Klicka på OK.
Detta kopierar den villkorliga formateringen till alla celler i kolumnen. Nu - äntligen - ser du några celler med den röda formateringen, vilket indikerar att du har en duplikat.
Det är informativt att gå till cell A3 och titta på det villkorliga formatet efter kopian. Välj A3, tryck od för att visa villkorlig formatering. Formeln i rutan Formel är ändrad för att räkna hur många gånger A3 visas i kolumnen A: A.
Anteckningar
I Joes fråga hade han bara 1700 fakturor inom intervallet. Jag har ställt in 65536 celler med villkorlig formatering och varje cell jämför den nuvarande cellen med 65536 andra celler. I Excel 2005 - med fler rader - blir problemet ännu värre. Tekniskt sett kunde formeln i det första steget ha varit:=COUNTIF($A$2:$A$1751,A2)>1
När du kopierar det villkorliga formatet till hela kolumnen kan du i stället ha valt bara raderna med data innan du klistrar in specialformat.
Mer
Det andra problemet som jag beskrev efter frågan är att du verkligen inte kan sortera en kolumn utifrån ett villkorligt format. Om du behöver sortera dessa data så att dubbletterna finns i ett område, följ sedan dessa steg. Lägg först till en rubrik i B1 som heter "Duplicera?". Skriver denna formel i B2: =COUNTIF(A:A,A2)>1.
Med cellpekaren i B2 klickar du på autofyllhandtaget (det lilla fyrkanten i cellens nedre högra hörn) för att kopiera formeln hela vägen.
Du kan nu sortera efter fallande kolumn B och stigande A för att få problemfakturorna högst upp i intervallet.
Denna lösning förutsätter att du vill markera BÅDE av de dubbla fakturorna så att du manuellt kan ta reda på vilken som ska raderas eller korrigeras. Om du inte vill att markera första förekomsten av dubblett kan du justera formeln för att vara =COUNTIF($A$2:$A2,A2)>1. Det är viktigt att ange dollartecknen exakt som visat. Detta kommer bara att titta på alla celler från den aktuella cellen och leta efter dubbletter.
Tack till Joe från Boston för frågan!
