Denne wikiHow lærer deg hvordan du bruker betinget formatering til å sammenligne alle cellene på to separate lister i et Excel -regneark, og markere cellene som vises på begge listene. Denne funksjonen er bare tilgjengelig på Excel -skrivebordsversjonen, og mobilappen støtter den ikke.
Trinn
Metode 1 av 2: Bruke CountIf -formelen
Trinn 1. Åpne Excel -regnearket du vil redigere
Finn regnearkfilen med listene du vil sammenligne, og dobbeltklikk på filen for å åpne den i Microsoft Excel.
Trinn 2. Velg din første liste
Klikk på den første cellen i den første listen, og dra musen helt ned til listens siste celle for å velge hele dataområdet.
Trinn 3. Klikk kategorien Formler på verktøylinjen
Du finner denne fanen over verktøylinjen øverst i regnearket. Det vil åpne formelverktøyene på verktøylinjen.
Trinn 4. Klikk Definer navn på verktøylinjen
Du finner dette alternativet midt på båndet "Formler". Det åpner et nytt popup-vindu, og lar deg navngi listen din.
Trinn 5. Skriv List1 i Navn -feltet
Klikk på tekstfeltet øverst i popup-vinduet, og skriv inn et navn på listen her.
- Du kan senere bruke dette navnet til å sette inn listen i sammenligningsformelen.
- Alternativt kan du gi listen et annet navn her. For eksempel, hvis dette er en liste over steder, kan du gi den navnet "locations1" eller "locationList".
Trinn 6. Klikk OK i popup-vinduet
Dette vil bekrefte handlingen din og gi navnet til listen din.
Trinn 7. Navngi den andre listen som List2
Følg de samme trinnene som den første listen, og gi den andre listen et navn. Dette lar deg raskt bruke denne andre listen i sammenligningsformelen senere.
Du kan gi listen et navn du vil. Sørg for å huske eller notere navnet du gir til hver av listene dine her
Trinn 8. Velg din første liste
Klikk på den første cellen i den første listen, og dra ned for å velge hele dataområdet.
Sørg for at din første liste er valgt før du begynner å konfigurere den betingede formateringen
Trinn 9. Klikk på kategorien Hjem på verktøylinjen
Dette er den første kategorien i øvre venstre hjørne av verktøylinjen. Det åpner de grunnleggende regnearkverktøyene på verktøylinjen.
Trinn 10. Klikk Betinget formatering på verktøylinjen
Dette alternativet ser ut som et lite regnearkikon med noen celler markert i rødt og blått. Den åpner en rullegardinmeny med alle formateringsalternativene.
Trinn 11. Klikk Ny regel på rullegardinmenyen
Det åpner et nytt popup-vindu, og lar deg manuelt sette opp en ny formateringsregel for det valgte området.
Trinn 12. Velg alternativet "Bruk en formel for å bestemme hvilke celler som skal formateres"
Dette alternativet lar deg manuelt skrive en formateringsformel for å sammenligne de to listene.
- På Windows, finner du den nederst på regellisten i "Velg en regeltype" -boksen.
- På Mac, plukke ut Klassisk i rullegardinmenyen "Style" øverst i popup-vinduet. Finn deretter dette alternativet i den andre rullegardinmenyen under Style-menyen.
Trinn 13. Klikk formelfeltet i popup-vinduet
Du kan angi en hvilken som helst gyldig Excel -formel her for å sette opp en betinget formateringsregel.
Trinn 14. Skriv = countif (List2, A1) = 1 i formellinjen
Denne formelen skanner de to listene dine, og merker alle cellene i den første listen som også vises på den andre listen.
- Erstatt A1 i formelen med nummeret til den første cellen i din første liste.
- For eksempel, hvis den første cellen i din første liste er celle D5, vil formelen din se ut som = countif (List2, D5) = 1.
- Hvis du ga et annet navn til den andre listen, må du bytte ut List2 i formelen med det faktiske navnet på din egen liste.
- Alternativt kan du endre formelen til = countif (List2, A1) = 0 hvis du vil markere cellene som ikke vises på den andre listen.
Trinn 15. Skriv = countif (List1, B1) = 1 i formellinjen (valgfritt)
Hvis du vil finne og merke cellene på din sekund listen som også vises på den første listen, bruker du denne formelen i stedet for den første.
Erstatt List1 med navnet på din første liste, og B1 med den første cellen i den andre listen
Trinn 16. Velg et tilpasset format for å markere cellene (valgfritt)
Du kan velge en tilpasset bakgrunnsfyllfarge og forskjellige skriftstiler for å markere cellene som formelen din finner.
- På Windows, Klikk på Format -knappen nederst til høyre i popup-vinduet. Du kan velge en bakgrunnsfarge i "Fyll" -fanen og skriftstiler i "Font" -fanen.
- På Mac, velg et forhåndsinnstilt format på rullegardinmenyen "Format med" nederst. Du kan også velge tilpasset format her for å manuelt velge bakgrunnsfyll og skriftstiler.
Trinn 17. Klikk OK i popup-vinduet
Dette vil bekrefte og anvende sammenligningsformelen. Alle cellene på din første liste som også vises på den andre listen, vil bli merket med den valgte fargen og skriften.
- For eksempel, hvis du velger en lys rød fylling med mørk rød tekst, vil alle de gjentagende cellene vende seg til denne fargen på din første liste.
- Hvis du bruker den andre formelen ovenfor, vil betinget formatering markere de gjentagende cellene på den andre listen i stedet for den første.
Metode 2 av 2: Bruke VLookup -formelen
Trinn 1. Åpne Excel -regnearket
Finn Excel-filen med listene du vil sammenligne, og dobbeltklikk på filnavnet eller ikonet for å åpne regnearket i Microsoft Excel.
Trinn 2. Klikk på den tomme cellen ved siden av det første elementet i den andre listen
Finn den andre listen i regnearket, og klikk på den tomme cellen ved siden av det første listeelementet øverst.
- Du kan sette inn din VLookup -formel her.
- Alternativt kan du velge hvilken som helst tom celle i regnearket. Denne cellen vil bare gjøre det mer praktisk for deg å se sammenligningen ved siden av den andre listen.
Trinn 3. Skriv = vlookup (inn i den tomme cellen
VLookup -formelen lar deg sammenligne alle elementene på to separate lister, og se om en verdi er en gjentagelse eller ny verdi.
Ikke lukk formelparentesen før formelen er fullført
Trinn 4. Velg det første elementet på den andre listen
Uten å lukke formelen i parentes, klikker du på det første elementet i den andre listen. Dette vil sette den andre listens første celle inn i formelen.
Trinn 5. Skriv a, komma i formelen
Etter å ha valgt den første cellen i den andre listen, skriver du et komma i formelen. Deretter kan du velge sammenligningsområdet.
Trinn 6. Hold nede og velg hele den første listen
Dette vil sette inn celleområdet til din første liste i den andre delen av VLookup -formelen.
Dette lar deg søke i den første listen etter det valgte elementet fra den andre listen (det første elementet øverst i den andre listen), og gå tilbake hvis det er en gjentagelse eller ny verdi
Trinn 7. Skriv a, komma i formelen
Dette låser sammenligningsområdet i formelen din.
Trinn 8. Skriv 1 i formelen etter kommaet
Dette tallet representerer kolonneindeksnummeret ditt. Det vil be VLookup -formelen om å søke i den faktiske listekolonnen i stedet for en annen kolonne ved siden av den.
Hvis du vil at formelen din skal returnere verdien fra kolonnen ved siden av din første liste, skriver du 2 her
Trinn 9. Skriv a, komma i formelen
Dette låser kolonneindeksnummeret (1) i VLookup -formelen.
Trinn 10. Skriv FALSK i formelen
Dette vil søke i listen etter en eksakt samsvar for det valgte søkeelementet (det første elementet øverst i den andre listen) i stedet for omtrentlige treff.
- I stedet for FALSE kan du bruke 0, det er nøyaktig det samme.
- Alternativt kan du skrive TRUE eller 1 hvis du vil søke etter en omtrentlig treff.
Trinn 11. Skriv) på slutten for å lukke formelen
Du kan nå kjøre formelen din og se om det valgte søkeelementet i den andre listen er en gjentagelses- eller ny verdi.
For eksempel, hvis den andre listen starter på B1, og den første listen går fra celler A1 til A5, vil formelen din se ut som = oppslag (B1, $ A $ 1: $ A $ 5, 1, usant).
Trinn 12. Trykk på ↵ Enter eller Gå tilbake på tastaturet.
Dette vil kjøre formelen og søke i den første listen etter det første elementet fra den andre listen.
- Hvis dette er en gjentagelsesverdi, vil du se den samme verdien skrives ut igjen i formelcellen.
- Hvis dette er en ny verdi, ser du " #I/A"trykt her.
- For eksempel, hvis du søker i den første listen etter "John", og nå ser "John" i formelcellen, er det en gjentatt verdi som kommer opp på begge listene. Hvis du ser "#N/A", er det en ny verdi på den andre listen.
Trinn 13. Velg formelcellen
Etter å ha kjørt formelen din og sett resultatene for det første listeelementet, klikker du på formelcellen for å velge den.
Trinn 14. Klikk og dra ned den grønne prikken nederst til høyre i cellen
Dette vil utvide formelcellen langs listen og bruke formelen på hvert listeelement på den andre listen.
- På denne måten kan du sammenligne hvert element på den andre listen med hele den første listen.
- Dette vil søke i den første listen for hvert element på den andre listen individuelt, og vise resultatet ved siden av hver celle separat.
- Hvis du vil se en annen markør for nye verdier i stedet for "#N/A", bruker du denne formelen: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "New Value"). Dette vil skrive ut "Ny verdi" for nye verdier i stedet for "#N/A."
