I går aftes i Craig Crossmans Computer America radioshow havde Joe fra Boston et spørgsmål:
Jeg har en kolonne med fakturanumre. Hvordan kan jeg bruge Excel til at markere duplikaterne?
Jeg foreslog at bruge betingede formater og COUNTIF-formlen. Her er detaljerne om, hvordan du får det til at fungere.
Vi vil oprette betinget formatering for hele intervallet, men det er lettere at oprette et betinget format til den første celle i området og derefter kopiere det betingede format. I vores tilfælde har celle A1 en overskrift på fakturanummer, så jeg vælger celle A2 og vælg Format> Betinget formatering i menuen. Dialogen Betinget formatering starter med den indledende rullemenu, der siger "Cell Value Is". Hvis du rører ved pilen ved siden af denne, kan du vælge "Formula Is".
Efter valg af "Formula Is" ændrer dialogboksen udseende. I stedet for felter til "Mellem x og y" er der nu en enkelt formelboks. Denne formelboks er utrolig kraftfuld. Du kan indtaste en hvilken som helst formel, som du kan drømme om, så længe den formel vurderes til SAND eller FALSK.
I vores tilfælde skal vi bruge en COUNTIF-formel. Formlen til at skrive i feltet er
=COUNTIF(A:A,A2)>1
På engelsk siger dette, "se gennem hele kolonnens rækkevidde. Tæl hvor mange celler i området, der har samme værdi som hvad der er i A2. (Det er virkelig vigtigt, at" A2 "i formlen peger på nuværende celle - cellen, som du indstiller den betingede formatering i. Så - hvis dine data er i kolonne E, og du indstiller den første betingede formatering i E5, ville formlen være =COUNTIF(E:E,E5)>0). Derefter sammenligner vi for at se, om det tæller er> 1. Ideelt set, uden duplikater, vil antallet altid være 1 - fordi celle A2 er i området - skal vi finde nøjagtigt en celle i kolonne A, der indeholder den samme værdi som A2.
Klik på knappen Format …
Nu er det tid til at vælge et modbydeligt format. Der er tre faner øverst i dialogboksen Formatér celler. Fanen Font er normalt først, så du kan vælge en fed, rød skrifttype, men jeg kan godt lide noget mere modbydeligt. Jeg klikker normalt på fanen Mønstre og vælger enten lys rød eller lys gul. Vælg farven, og klik derefter på OK for at lukke dialogboksen Formatér celler.
Du vil se det valgte format i feltet "Eksempel på format, der skal bruges". Klik på OK for at lukke dialogboksen Betinget formatering …
… og intet sker. Wow. Hvis dette er din første gang, du konfigurerer betinget formatering, ville det være rigtig rart at få feedback her om, at det fungerede. Men medmindre du er heldig nok til, at 1098 i celle A2 er en kopi af en anden celle, er betingelsen ikke sand, og det ser ud til, at der ikke skete noget.
Du skal kopiere den betingede formatering fra A2 ned til de andre celler i dit interval. Med markørkarmen i A2 skal du redigere> kopiere. Tryk på Ctrl + mellemrumstasten for at vælge hele kolonnen. Gør Rediger> Indsæt specielt. Klik på Formater i dialogboksen Indsæt speciel. Klik på OK.
Dette kopierer den betingede formatering til alle celler i kolonnen. Nu - endelig - ser du nogle celler med den røde formatering, hvilket indikerer, at du har et duplikat.
Det er informativt at gå til celle A3 og se på det betingede format efter kopien. Vælg A3, tryk på od for at hente betinget formatering. Formlen i feltet Formel er ændret for at tælle, hvor mange gange A3 vises i kolonnen A: A.
Bemærkninger
På Joes spørgsmål havde han kun 1700 fakturaer i intervallet. Jeg har oprettet 65536 celler med betinget formatering, og hver celle sammenligner den aktuelle celle med 65536 andre celler. I Excel 2005 - med flere rækker - bliver problemet endnu værre. Teknisk set kunne formlen i det første trin have været:=COUNTIF($A$2:$A$1751,A2)>1
Også når du kopierer det betingede format til hele kolonnen, kunne du i stedet have valgt kun rækkerne med data, før du udfylder Særlige formater.
Mere
Det andet problem, som jeg beskrev efter spørgsmålet, er, at du virkelig ikke kan sortere en kolonne på basis af et betinget format. Hvis du har brug for at sortere disse data, så duplikaterne er i et område, skal du følge disse trin. Først skal du tilføje en overskrift til B1 kaldet "Duplicate?". Skrive denne formel i B2: =COUNTIF(A:A,A2)>1.
Med cellemarkøren i B2 skal du klikke på autofyldhåndtaget (det lille firkant i celleens nederste højre hjørne) for at kopiere formlen helt ned i området.
Du kan nu sortere efter kolonne B faldende og A stigende for at have problemfakturaerne øverst i området.
Denne løsning forudsætter, at du vil fremhæve BEGGE af de duplikerede fakturaer, så du manuelt kan finde ud af, hvilken du vil slette eller rette. Hvis du ikke ønsker at markere den første forekomst af to eksemplarer, kan du justere formlen til at være: =COUNTIF($A$2:$A2,A2)>1. Det er vigtigt at indtaste dollartegnene nøjagtigt som vist. Dette vil kun se på alle celler fra den aktuelle celle op og på udkig efter duplikatposter.
Tak til Joe fra Boston for spørgsmålet!
