I går i Craig Crossmans Computer America radioprogram hadde Joe fra Boston et spørsmål:
Jeg har en kolonne med fakturanummer. Hvordan kan jeg bruke Excel til å merke duplikatene?
Jeg foreslo å bruke betingede formater og COUNTIF-formelen. Her er detaljene om hvordan du får det til å fungere.
Vi ønsker å sette opp betinget formatering for hele området, men det er lettere å sette opp et betinget format for den første cellen i området og deretter kopiere det betingede formatet. I vårt tilfelle har celle A1 en overskrift på fakturanummer, så jeg velger celle A2 og fra menyen velger du Format> Betinget formatering. Dialogboksen Betinget formatering starter med den første rullegardinmenyen som sier "Cell Value Is". Berører du pilen ved siden av denne, kan du velge "Formula Is".
Etter at du har valgt "Formula Is", endrer dialogboksen utseende. I stedet for bokser for "Mellom x og y", er det nå en enkelt formelboks. Denne formelboksen er utrolig kraftig. Du kan skrive inn en hvilken som helst formel du kan drømme om, så lenge den formelen vil evaluere til SANT eller FALSK.
I vårt tilfelle må vi bruke en COUNTIF-formel. Formelen for å skrive i boksen er
=COUNTIF(A:A,A2)>1
På engelsk står det: "se gjennom hele spekteret av kolonne A. Tell hvor mange celler i det området som har samme verdi som det som er i A2. (Det er veldig viktig at" A2 "i formelen peker på nåværende celle - cellen som du setter den betingede formateringen opp i. Så - hvis dataene dine er i kolonne E og du setter den første betingede formateringen i E5, vil formelen være det =COUNTIF(E:E,E5)>0). Deretter sammenligner vi for å se om det teller er> 1. Ideelt sett, uten duplikater, vil tellingen alltid være 1 - fordi celle A2 er i området - bør vi finne nøyaktig en celle i kolonne A som inneholder samme verdi som A2.
Klikk på Format… -knappen
Nå er det på tide å velge et motbydelig format. Det er tre faner øverst i denne dialogboksen Format celler. Font-fanen er vanligvis først, så du kan velge en fet, rød skrift, men jeg liker noe mer motbydelig. Jeg klikker vanligvis på kategorien Mønstre og velger enten rød eller lys gul. Velg farge, og klikk deretter OK for å lukke dialogboksen Formater celler.
Du vil se det valgte formatet i boksen "Forhåndsvisning av format som skal brukes". Klikk OK for å lukke dialogboksen Betinget formatering …
… og ingenting skjer. Wow. Hvis dette er første gang du konfigurerer betinget formatering, ville det vært veldig hyggelig å få tilbakemelding her om at det fungerte. Men med mindre du er heldig nok til at 1098 i celle A2 er en duplikat av en annen celle, er tilstanden ikke sant, og det ser ut som ingenting skjedde.
Du må kopiere den betingede formateringen fra A2 ned til de andre cellene i området ditt. Med markøren i A2 gjør du Rediger> Kopier. Trykk på Ctrl + mellomrom for å velge hele kolonnen. Gjør Rediger> Lim inn spesial. Klikk Formater i dialogboksen Lim inn spesial. Klikk OK.
Dette vil kopiere den betingede formateringen til alle celler i kolonnen. Nå - endelig - ser du noen celler med rød formatering, noe som indikerer at du har et duplikat.
Det er informativt å gå til celle A3 og se på det betingede formatet etter kopien. Velg A3, trykk od for å få frem betinget formatering. Formelen i boksen Formel er som endret for å telle hvor mange ganger A3 vises i kolonnen A: A.
Merknader
På Joes spørsmål hadde han bare 1700 fakturaer i området. Jeg har satt opp 65536 celler med betinget formatering, og hver celle sammenligner den nåværende cellen med 65536 andre celler. I Excel 2005 - med flere rader - vil problemet være enda verre. Teknisk sett kunne formelen i første trinn ha vært:=COUNTIF($A$2:$A$1751,A2)>1
Når du kopierer det betingede formatet til hele kolonnen, kan du i stedet ha valgt bare radene med data før du limer inn spesielle formater.
Mer
Det andre problemet som jeg beskrev etter spørsmålet er at du virkelig ikke kan sortere en kolonne på grunnlag av et betinget format. Hvis du trenger å sortere disse dataene slik at duplikatene er i ett område, følg disse trinnene. Først legger du til en overskrift til B1 kalt "Duplicate?". Skriv dette formelen i B2: =COUNTIF(A:A,A2)>1.
Med cellepekeren i B2 klikker du på autofyllhåndtaket (det lille firkanten i nedre høyre hjørne av cellen) for å kopiere formelen helt ned i området.
Du kan nå sortere etter kolonne B synkende og A stigende for å ha problemfakturaene øverst i området.
Denne løsningen forutsetter at du vil markere BEGGE av duplikatfakturaene, slik at du manuelt kan finne ut hvilken du vil slette eller rette. Hvis du ikke vil merke den første forekomsten av duplikat, kan du justere formelen for å være: =COUNTIF($A$2:$A2,A2)>1. Det er viktig å angi dollartegnene nøyaktig som vist. Dette vil bare se på alle celler fra den nåværende cellen og se etter dupliserte oppføringer.
Takk til Joe fra Boston for spørsmålet!
