Ovaj wikiHow vas uči kako koristiti uvjetno oblikovanje za usporedbu svih ćelija na dvije odvojene liste u Excel proračunskoj tablici i označiti ćelije koje se pojavljuju na obje liste. Ova je funkcija dostupna samo u Excel verziji za računala, a mobilna aplikacija je ne podržava.
Koraci
Metoda 1 od 2: Korištenje formule CountIf
Korak 1. Otvorite Excel proračunsku tablicu koju želite urediti
Pronađite datoteku proračunske tablice sa popisima koje želite uporediti i dvaput kliknite datoteku da biste je otvorili u programu Microsoft Excel.
Korak 2. Odaberite svoju prvu listu
Pritisnite prvu ćeliju na svom prvom popisu i povucite mišem sve do posljednje ćelije liste da biste odabrali cijeli raspon podataka.
Korak 3. Kliknite karticu Formule na traci s alatnim trakama
Ovu karticu možete pronaći iznad trake s alatima pri vrhu proračunske tablice. Otvorit će vaše alate za formule na traci s alatnim trakama.
Korak 4. Kliknite na Define Name na traci sa alatkama
Ovu opciju možete pronaći u sredini vrpce "Formule". Otvorit će se novi skočni prozor i omogućiti vam da imenujete svoju listu.
Korak 5. U polje List upišite List1
Kliknite na tekstualno polje pri vrhu iskačućeg prozora i ovdje unesite naziv liste.
- Kasnije možete koristiti ovaj naziv za umetanje svoje liste u formulu za usporedbu.
- Alternativno, ovdje možete svojoj listi dati drugačije ime. Na primjer, ako je ovo popis lokacija, možete mu dati naziv "locations1" ili "locationList".
Korak 6. Kliknite U redu u iskačućem prozoru
Ovo će potvrditi vašu radnju i imenovati vašu listu.
Korak 7. Imenujte svoju drugu listu kao List2
Slijedite iste korake kao i prvu listu, a drugoj listi dajte ime. To će vam omogućiti da kasnije kasnije brzo upotrijebite ovu drugu listu u svojoj usporedbenoj formuli.
Spisku možete dati bilo koje ime koje želite. Upamtite ili zapišite ime koje dajete svakoj od vaših lista ovdje
Korak 8. Odaberite svoju prvu listu
Pritisnite prvu ćeliju na prvom popisu i povucite prema dolje da biste odabrali cijeli raspon podataka.
Prije početka postavljanja uvjetnog oblikovanja provjerite je li odabran vaš prvi popis
Korak 9. Kliknite karticu Početna na alatnoj traci
Ovo je prva kartica u gornjem lijevom kutu trake s alatnim trakama. Otvorit će vaše osnovne alate za proračunske tablice na alatnoj traci.
Korak 10. Kliknite Uvjetno oblikovanje na alatnoj traci
Ova opcija izgleda kao mala ikona proračunske tablice s nekim ćelijama istaknutim crveno i plavo. Otvorit će se padajući izbornik sa svim vašim mogućnostima oblikovanja.
Korak 11. Na padajućem izborniku kliknite Novo pravilo
Otvorit će novi skočni prozor i omogućiti vam da ručno postavite novo pravilo oblikovanja za odabrani raspon.
Korak 12. Odaberite opciju "Koristi formulu za određivanje ćelija koje ćete formatirati"
Ova opcija će vam omogućiti da ručno upišete formulu za oblikovanje radi usporedbe vaše dvije liste.
- Uključeno Windows, naći ćete ga pri dnu liste pravila u okviru "Odabir vrste pravila".
- Uključeno Mac, odaberite Classic u padajućem izborniku "Stil" na vrhu skočnog prozora. Zatim pronađite ovu opciju u drugom padajućem izborniku ispod izbornika Stil.
Korak 13. Kliknite polje formule u iskačućem prozoru
Ovdje možete unijeti bilo koju važeću Excel formulu za postavljanje pravila uvjetnog oblikovanja.
Korak 14. U traku s formulama upišite = countif (List2, A1) = 1
Ova formula će skenirati vaše dvije liste i označiti sve ćelije na vašoj prvoj listi koje se također pojavljuju na drugoj listi.
- Zamijenite A1 u formuli brojem prve ćelije vaše prve liste.
- Na primjer, ako je prva ćelija vaše prve liste ćelija D5, tada će vaša formula izgledati kao = countif (List2, D5) = 1.
- Ako ste drugom popisu dali drugačije ime, obavezno zamijenite List2 u formuli sa stvarnim imenom vaše vlastite liste.
- Alternativno, promijenite formulu u = countif (List2, A1) = 0 ako želite označiti ćelije koje nemoj se pojavljuju na drugoj listi.
Korak 15. U traku s formulama upišite = countif (List1, B1) = 1 (izborno)
Ako želite pronaći i označiti ćelije na svom sekunda popis koji se također pojavljuju na prvoj listi, koristite ovu formulu umjesto prve.
Zamijenite List1 imenom prve liste, a B1 prvom ćelijom vaše druge liste
Korak 16. Odaberite prilagođeni format za označavanje ćelija (opcionalno)
Možete odabrati prilagođenu boju ispune pozadine i različite stilove fonta za označavanje ćelija koje vaša formula nalazi.
- Uključeno Windows, kliknite na Format dugme u donjem desnom uglu iskačućeg prozora. Boju pozadine možete odabrati na kartici "Popuni", a stilove fontova na kartici "Font".
- Uključeno Mac, odaberite unaprijed postavljeni format na padajućem izborniku "Oblikovanje s" pri dnu. Takođe možete izabrati prilagođeni format ovdje za ručni odabir stilova ispune pozadine i fonta.
Korak 17. Kliknite U redu u iskačućem prozoru
Ovo će potvrditi i primijeniti vašu formulu za usporedbu. Sve ćelije na vašoj prvoj listi koje se također pojavljuju na drugoj listi bit će označene odabranom bojom i fontom.
- Na primjer, ako odaberete svijetlocrveno ispunu s tamnocrvenim tekstom, sve ćelije koje se ponavljaju pretvorit će se u ovu boju na vašoj prvoj listi.
- Ako koristite drugu gornju formulu, uvjetno oblikovanje označit će ćelije koje se ponavljaju na vašoj drugoj listi umjesto prve.
Metoda 2 od 2: Korištenje formule VLookup
Korak 1. Otvorite Excel proračunsku tablicu
Pronađite Excel datoteku sa listama koje želite uporediti i dvaput kliknite na naziv datoteke ili ikonu da biste otvorili proračunsku tablicu u programu Microsoft Excel.
Korak 2. Kliknite praznu ćeliju pored prve stavke na vašoj drugoj listi
Pronađite svoj drugi popis u proračunskoj tablici i kliknite praznu ćeliju pored prve stavke popisa na vrhu.
- Ovdje možete umetnuti svoju formulu VLookup.
- Alternativno, možete odabrati bilo koju praznu ćeliju u proračunskoj tablici. Ova ćelija će vam samo olakšati da pored drugog popisa vidite svoju usporedbu.
Korak 3. Upišite = vlookup (u praznu ćeliju
Formula VLookup će vam omogućiti da uporedite sve stavke na dvije odvojene liste i vidite je li vrijednost ponovljena ili nova vrijednost.
Nemojte zatvarati zagrade formule dok formula ne bude potpuna
Korak 4. Odaberite prvu stavku na svojoj drugoj listi
Bez zatvaranja zagrada formule, kliknite na prvu stavku na drugoj listi. Ovo će umetnuti prvu ćeliju vaše druge liste u formulu.
Korak 5. Upišite a, zarez u formulu
Nakon što odaberete prvu ćeliju druge liste, unesite zarez u formulu. Zatim ćete moći odabrati svoj raspon za usporedbu.
Korak 6. Držite pritisnuto i odaberite cijelu prvu listu
Ovo će umetnuti raspon ćelija vaše prve liste u drugi dio formule VLookup.
Ovo će vam omogućiti da na prvoj listi pretražite odabranu stavku sa druge liste (prva stavka na vrhu druge liste) i vratite se ako se radi o ponovljenoj ili novoj vrijednosti
Korak 7. Upišite a, zarez u formulu
Ovo će zaključati raspon usporedbe u vašoj formuli.
Korak 8. Upišite 1 u formulu iza zareza
Ovaj broj predstavlja broj indeksa kolone. To će zatražiti od formule VLookup da pretraži stvarnu kolonu liste umjesto različite kolone pored nje.
Ako želite da vaša formula vrati vrijednost iz stupca tik do vaše prve liste, upišite 2 ovdje
Korak 9. Upišite a, zarez u formulu
Ovo će zaključati vaš indeksni broj stupca (1) u formuli VLookup.
Korak 10. U formulu upišite FALSE
Ovim će se na popisu tražiti točno podudaranje odabrane stavke pretraživanja (prva stavka na vrhu druge liste) umjesto približnih podudaranja.
- Umjesto FALSE možete koristiti 0, potpuno je isto.
- Alternativno, možete upisati TRUE ili 1 ako želite tražiti približno podudaranje.
Korak 11. Upišite) na kraju da biste zatvorili formulu
Sada možete pokrenuti formulu i vidjeti je li odabrana stavka pretraživanja na vašoj drugoj listi ponovljena ili nova vrijednost.
Na primjer, ako vaša druga lista počinje od B1, a vaša prva lista ide od ćelija A1 do A5, vaša formula će izgledati ovako = vlookup (B1, $ A $ 1: $ A $ 5, 1, lažno).
Korak 12. Pritisnite ↵ Enter ili ⏎ Vratite se na tastaturi.
Ovo će pokrenuti formulu i pretražiti vašu prvu listu za prvu stavku sa vaše druge liste.
- Ako se ovo ponavlja, vidjet ćete istu vrijednost ponovno ispisanu u ćeliji formule.
- Ako je ovo nova vrijednost, vidjet ćete " #N / A"štampano ovdje.
- Na primjer, ako tražite prvi popis za "John", a sada vidite "John" u ćeliji formule, to je ponovljena vrijednost koja se pojavljuje na obje liste. Ako vidite "#N/A", to je nova vrijednost na drugoj listi.
Korak 13. Odaberite ćeliju formule
Nakon što pokrenete formulu i vidite rezultate za prvu stavku liste, kliknite ćeliju formule da biste je odabrali.
Korak 14. Kliknite i povucite prema dolje zelenu točku u donjem desnom kutu ćelije
Ovo će proširiti ćeliju formule duž liste i primijeniti formulu na svaku stavku liste na vašoj drugoj listi.
- Na ovaj način možete uporediti svaku stavku na drugoj listi sa cijelom prvom listom.
- Ovo će pretraživati vašu prvu listu za svaku stavku na vašoj drugoj listi pojedinačno i prikazati rezultat pored svake ćelije zasebno.
- Ako želite vidjeti drugačiji marker za nove vrijednosti umjesto "#N/A", upotrijebite ovu formulu: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "Nova vrijednost"). Ovo će ispisati "Nova vrijednost" za nove vrijednosti umjesto "#N/A."
