Ovaj wikiHow vas uči kako koristiti uvjetno oblikovanje za usporedbu svih ćelija na dva odvojena popisa u Excel proračunskoj tablici i označiti ćelije koje se pojavljuju na oba popisa. Ova je značajka dostupna samo u Excel verziji za stolna 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 usporediti i dvaput kliknite datoteku da biste je otvorili u programu Microsoft Excel.
Korak 2. Odaberite svoj prvi popis
Pritisnite prvu ćeliju na svom prvom popisu i povucite mišem sve do posljednje ćelije popisa kako biste odabrali cijeli raspon podataka.
Korak 3. Kliknite karticu Formule na vrpci s alatnom trakom
Ovu karticu možete pronaći iznad alatne trake pri vrhu proračunske tablice. Otvorit će vaše alate za formule na vrpci alatne trake.
Korak 4. Pritisnite Define Name na alatnoj traci
Ovu opciju možete pronaći usred vrpce "Formule". Otvorit će se novi skočni prozor i omogućiti vam imenovanje popisa.
Korak 5. U polje Name upišite List1
Pritisnite tekstualno polje pri vrhu skočnog prozora i ovdje unesite naziv popisa.
- Kasnije možete koristiti ovaj naziv za umetanje popisa u formulu za usporedbu.
- Alternativno, ovdje možete svom popisu dati drugo ime. Na primjer, ako je ovo popis lokacija, možete mu dati naziv "locations1" ili "locationList".
Korak 6. Pritisnite U redu u skočnom prozoru
Ovo će potvrditi vašu radnju i imenovati vaš popis.
Korak 7. Imenujte svoj drugi popis kao List2
Slijedite iste korake kao i prvi popis, a drugom popisu dajte ime. To će vam omogućiti da kasnije brzo upotrijebite ovaj drugi popis u svojoj usporedbenoj formuli.
Popisu možete dati bilo koje ime koje želite. Svakako zapamtite ili zapišite ime koje dajete svakom svom popisu ovdje
Korak 8. Odaberite svoj prvi popis
Pritisnite prvu ćeliju na prvom popisu i povucite prema dolje za odabir cijelog raspona 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 vrpce s alatne trake. 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 crvenom i plavom bojom. Otvorit će se padajući izbornik sa svim vašim mogućnostima oblikovanja.
Korak 11. Na padajućem izborniku kliknite Novo pravilo
Otvorit će se novi skočni prozor i omogućiti vam ručno postavljanje novog pravila oblikovanja za odabrani raspon.
Korak 12. Odaberite opciju "Koristi formulu za određivanje ćelija za oblikovanje"
Ova će vam opcija omogućiti da ručno upišete formulu za oblikovanje kako biste usporedili svoja dva popisa.
- Na Windows, pronaći ćete ga pri dnu popisa pravila u okviru "Odabir vrste pravila".
- Na Mac, Odaberi Klasična u padajućem izborniku "Stil" pri vrhu skočnog prozora. Zatim pronađite ovu opciju u drugom padajućem izborniku ispod izbornika Stil.
Korak 13. Kliknite polje formule u skočnom prozoru
Ovdje možete unijeti bilo koju valjanu formulu programa Excel za postavljanje pravila uvjetnog oblikovanja.
Korak 14. U traku s formulama upišite = countif (List2, A1) = 1
Ova će formula skenirati vaša dva popisa i označiti sve ćelije na vašem prvom popisu koje se također pojavljuju na drugom popisu.
- Zamijenite A1 u formuli s brojem prve ćelije vašeg prvog popisa.
- Na primjer, ako je prva ćelija vašeg prvog popisa ćelija D5, tada će vaša formula izgledati kao = countif (List2, D5) = 1.
- Ako ste drugom popisu dali drugačije ime, svakako zamijenite List2 u formuli stvarnim imenom vlastitog popisa.
- Alternativno, promijenite formulu u = countif (List2, A1) = 0 ako želite označiti ćelije koje nemoj se pojavljuju na drugom popisu.
Korak 15. U traku s formulama upišite = countif (List1, B1) = 1 (izborno)
Ako želite pronaći i označiti ćelije na svom drugi popis koji se također pojavljuju na prvom popisu, upotrijebite ovu formulu umjesto prve.
Zamijenite List1 s imenom vašeg prvog popisa, a B1 s prvom ćelijom vašeg drugog popisa
Korak 16. Odaberite prilagođeni format za označavanje ćelija (izborno)
Možete odabrati prilagođenu boju ispune pozadine i različite stilove fonta za označavanje ćelija koje vaša formula nalazi.
- Na Windows, kliknite na Format gumb u donjem desnom kutu skočnog prozora. Boju pozadine možete odabrati na kartici "Ispuna", a stilove fontova na kartici "Font".
- Na Mac, odaberite unaprijed postavljeni format na padajućem izborniku "Oblikovanje s" pri dnu. Također možete odabrati prilagođeni format ovdje za ručni odabir stilova ispune pozadine i fonta.
Korak 17. Pritisnite U redu u skočnom prozoru
To će potvrditi i primijeniti vašu formulu za usporedbu. Sve ćelije na vašem prvom popisu koje se također pojavljuju na drugom popisu 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šem prvom popisu.
- Ako koristite drugu gornju formulu, uvjetno oblikovanje označit će ćelije koje se ponavljaju na vašem drugom popisu umjesto na prvom.
Metoda 2 od 2: Korištenje formule VLookup
Korak 1. Otvorite Excel proračunsku tablicu
Pronađite Excel datoteku s popisima koje želite usporediti i dvokliknite naziv datoteke ili ikonu da biste otvorili proračunsku tablicu u programu Microsoft Excel.
Korak 2. Kliknite praznu ćeliju pored prve stavke na vašem drugom popisu
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 će vam ćelija samo olakšati da pored drugog popisa vidite svoju usporedbu.
Korak 3. Upišite = vlookup (u praznu ćeliju
Formula VLookup omogućit će vam da usporedite sve stavke na dva odvojena popisa i vidite je li vrijednost ponovljena ili nova vrijednost.
Nemojte zatvarati zagrade formule dok formula ne bude potpuna
Korak 4. Odaberite prvu stavku na svom drugom popisu
Bez zatvaranja zagrada formule, kliknite prvu stavku na drugom popisu. Ovo će umetnuti prvu ćeliju vašeg drugog popisa u formulu.
Korak 5. U formulu upišite a, zarez
Nakon što odaberete prvu ćeliju drugog popisa, unesite zarez u formulu. Sljedeći ćete moći odabrati svoj raspon usporedbe.
Korak 6. Držite pritisnuto i odaberite cijeli svoj prvi popis
Ovo će umetnuti raspon ćelija vašeg prvog popisa u drugi dio formule VLookup.
To će vam omogućiti da na prvom popisu pretražite odabranu stavku s drugog popisa (prva stavka na vrhu drugog popisa) i vratite se ako se radi o ponovljenoj ili novoj vrijednosti
Korak 7. Upišite a, zarez u formulu
Time ćete zaključati raspon usporedbe u formuli.
Korak 8. Upišite 1 u formulu iza zareza
Ovaj broj predstavlja broj indeksa vašeg stupca. To će zatražiti od formule VLookup da pretraži stvarni stupac popisa umjesto drugog stupca pored njega.
Ako želite da vaša formula vrati vrijednost iz stupca tik do vašeg prvog popisa, upišite 2 ovdje
Korak 9. Upišite a, zarez u formulu
Time ćete zaključati broj indeksa stupca (1) u formuli VLookup.
Korak 10. U formulu upišite FALSE
Time će se na popisu tražiti točno podudaranje odabrane stavke pretraživanja (prva stavka na vrhu drugog popisa) 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šem drugom popisu ponovljena ili nova vrijednost.
Na primjer, ako vaš drugi popis počinje s B1, a vaš prvi popis ide iz ćelija A1 u A5, vaša će formula izgledati ovako = vlookup (B1, $ A $ 1: $ A $ 5, 1, netočno).
Korak 12. Pritisnite ↵ Enter ili ⏎ Vratite se na tipkovnici.
Ovo će pokrenuti formulu i pretražiti vaš prvi popis za prvu stavku s vašeg drugog popisa.
- Ako je ovo ponovljena vrijednost, vidjet ćete istu vrijednost ponovno ispisanu u ćeliji formule.
- Ako je ovo nova vrijednost, vidjet ćete " #N/A"tiskano 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 oba popisa. Ako vidite "#N/A", to je nova vrijednost na drugom popisu.
Korak 13. Odaberite ćeliju formule
Nakon što pokrenete formulu i vidite rezultate za prvu stavku popisa, 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ž popisa i primijeniti formulu na svaku stavku popisa na vašem drugom popisu.
- Na ovaj način možete usporediti svaku stavku na drugom popisu sa cijelim prvim popisom.
- Ovo će pretraživati vaš prvi popis za svaku stavku na vašem drugom popisu pojedinačno i prikazati rezultat pored svake ćelije zasebno.
- Ako želite vidjeti drugi 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".
