Ta wikiHow vas uči, kako s pogojnim oblikovanjem primerjati vse celice na dveh ločenih seznamih v preglednici Excel in označiti celice, ki so prikazane na obeh seznamih. Ta funkcija je na voljo samo v namizni različici programa Excel in mobilna aplikacija je ne podpira.
Koraki
Metoda 1 od 2: Uporaba formule CountIf
Korak 1. Odprite Excelovo preglednico, ki jo želite urediti
Poiščite datoteko preglednice s seznami, ki jih želite primerjati, in dvokliknite datoteko, da jo odprete v programu Microsoft Excel.
Korak 2. Izberite svoj prvi seznam
Kliknite prvo celico na prvem seznamu in povlecite miško vse do zadnje celice seznama, da izberete celotno območje podatkov.
Korak 3. Kliknite zavihek Formule na traku orodne vrstice
Ta zavihek najdete nad orodno vrstico na vrhu preglednice. Odprla bo vaša orodja za formule na traku orodne vrstice.
Korak 4. V orodni vrstici kliknite Define Name
To možnost najdete na sredini traku "Formule". Odprlo se bo novo pojavno okno in vam omogočilo, da poimenujete svoj seznam.
Korak 5. V polje Ime vnesite List1
Kliknite besedilno polje na vrhu pojavnega okna in vnesite ime seznama.
- To ime lahko pozneje uporabite za vstavljanje seznama v primerjalno formulo.
- Druga možnost je, da svojemu seznamu tukaj daste drugo ime. Če je na primer seznam lokacij, ga lahko poimenujete "locations1" ali "locationList".
Korak 6. V pojavnem oknu kliknite V redu
S tem boste potrdili svoje dejanje in poimenovali svoj seznam.
Korak 7. Poimenujte svoj drugi seznam kot List2
Sledite istim korakom kot prvi seznam in drugemu poimenujte ime. To vam bo omogočilo, da pozneje hitro uporabite ta drugi seznam v svoji primerjalni formuli.
Seznamu lahko daste poljubno ime. Zapomnite si ali zapišite ime, ki ga dajete vsakemu od svojih seznamov tukaj
Korak 8. Izberite svoj prvi seznam
Kliknite prvo celico na prvem seznamu in povlecite navzdol, da izberete celotno območje podatkov.
Preden nastavite pogojno oblikovanje, se prepričajte, da je izbran vaš prvi seznam
Korak 9. Kliknite zavihek Domov v orodni vrstici
To je prvi zavihek v zgornjem levem kotu traku orodne vrstice. Odprl bo vaša osnovna orodja za preglednice v orodni vrstici.
Korak 10. V orodni vrstici kliknite Pogojno oblikovanje
Ta možnost je videti kot majhna ikona preglednice z nekaj celicami, označenimi v rdeči in modri barvi. Odprl se bo spustni meni z vsemi možnostmi oblikovanja.
Korak 11. V spustnem meniju kliknite Novo pravilo
Odprlo se bo novo pojavno okno in vam omogočilo ročno nastavitev novega pravila oblikovanja za izbrani obseg.
Korak 12. Izberite možnost "Uporabi formulo za določitev celic, ki jih želite formatirati"
Ta možnost vam bo omogočila ročno vnos formule za oblikovanje za primerjavo vaših dveh seznamov.
- Vklopljeno Windows, ga boste našli na dnu seznama pravil v polju "Izberite vrsto pravila".
- Vklopljeno Mac, izberite Klasična v spustnem meniju »Slog« na vrhu pojavnega okna. Nato poiščite to možnost v drugem spustnem meniju pod menijem Slog.
Korak 13. Kliknite polje formule v pojavnem oknu
Tukaj lahko vnesete katero koli veljavno formulo Excel, da nastavite pravilo pogojnega oblikovanja.
Korak 14. V vrstico s formulo vnesite = coutif (List2, A1) = 1
Ta formula bo pregledala vaša dva seznama in označila vse celice na vašem prvem seznamu, ki so prikazane tudi na drugem seznamu.
- A1 v formuli zamenjajte s številko prve celice vašega prvega seznama.
- Na primer, če je prva celica vašega prvega seznama celica D5, bo vaša formula videti kot = countif (List2, D5) = 1.
- Če ste drugemu seznamu dali drugo ime, v formuli obvezno zamenjajte List2 z dejanskim imenom svojega seznama.
- Druga možnost je, da spremenite formulo v = countif (List2, A1) = 0, če želite označiti celice, ki ne se pojavijo na drugem seznamu.
Korak 15. V vrstico s formulo vnesite = coutif (List1, B1) = 1 (neobvezno)
Če želite poiskati in označiti celice na svojem drugič seznama, ki se pojavi tudi na prvem seznamu, uporabite to formulo namesto prve.
List1 zamenjajte z imenom vašega prvega seznama, B1 pa s prvo celico drugega seznama
Korak 16. Izberite obliko po meri, da označite celice (neobvezno)
Izberete lahko barvo polnila ozadja po meri in različne sloge pisave, da označite celice, ki jih najde vaša formula.
- Vklopljeno Windows, kliknite na Oblika v spodnjem desnem kotu pojavnega okna. Barvo ozadja lahko izberete na zavihku "Izpolni", sloge pisav pa na zavihku "Pisava".
- Vklopljeno Mac, v spustnem meniju »Oblikuj z« na dnu izberite prednastavljeno obliko. Izberete lahko tudi format po meri tukaj za ročno izbiro slogov polnjenja ozadja in pisave.
Korak 17. V pojavnem oknu kliknite V redu
S tem boste potrdili in uporabili vašo primerjalno formulo. Vse celice na vašem prvem seznamu, ki se pojavijo tudi na drugem, bodo označene z izbrano barvo in pisavo.
- Če na primer izberete svetlo rdečo polnilo s temno rdečim besedilom, se vse ponavljajoče se celice spremenijo v to barvo na vašem prvem seznamu.
- Če uporabite drugo zgornjo formulo, bo pogojno oblikovanje označilo ponavljajoče se celice na vašem drugem seznamu namesto na prvem.
Metoda 2 od 2: Uporaba formule VLookup
Korak 1. Odprite Excelovo preglednico
Poiščite datoteko Excel s seznami, ki jih želite primerjati, in dvokliknite ime datoteke ali ikono, da odprete preglednico v programu Microsoft Excel.
Korak 2. Kliknite prazno celico poleg prve postavke na drugem seznamu
V preglednici poiščite svoj drugi seznam in kliknite prazno celico poleg prve postavke seznama na vrhu.
- Tukaj lahko vstavite formulo VLookup.
- Lahko pa izberete katero koli prazno celico v preglednici. Ta celica vam bo le olajšala ogled primerjave poleg drugega seznama.
Korak 3. Vnesite = vlookup (v prazno celico
Formula VLookup vam omogoča primerjavo vseh postavk na dveh ločenih seznamih in preverjanje, ali je vrednost ponavljajoča se ali nova vrednost.
Ne zapirajte oklepajev formule, dokler formula ni popolna
Korak 4. Izberite prvo postavko na svojem drugem seznamu
Ne da bi zaprli oklepaj formule, kliknite prvi element na drugem seznamu. S tem boste v formulo vstavili prvo celico drugega seznama.
Korak 5. V formulo vnesite a, vejico
Ko izberete prvo celico drugega seznama, v formulo vnesite vejico. Naslednji primerjalni obseg boste lahko izbrali.
Korak 6. Držite in izberite celoten prvi seznam
S tem boste obseg celic prvega seznama vstavili v drugi del formule VLookup.
To vam bo omogočilo, da na prvem seznamu poiščete izbrano postavko na svojem drugem seznamu (prvi element na vrhu drugega seznama) in se vrnete, če je to ponovljena ali nova vrednost
Korak 7. V formulo vnesite a, vejico
S tem boste v formuli zaklenili obseg primerjave.
Korak 8. V formulo za vejico vnesite 1
Ta številka predstavlja številko indeksa vašega stolpca. Formula VLookup bo pozvala, naj išče dejanski stolpec seznama namesto drugega stolpca poleg njega.
Če želite, da vaša formula vrne vrednost iz stolpca tik ob prvem seznamu, vnesite 2 tukaj
Korak 9. V formulo vnesite a, vejico
S tem boste zabeležili številko indeksa stolpca (1) v formuli VLookup.
Korak 10. V formulo vnesite FALSE
S tem boste na seznamu poiskali natančno ujemanje izbranega iskalnega elementa (prvi element na vrhu drugega seznama) namesto približnih ujemanj.
- Namesto FALSE lahko uporabite 0, je popolnoma enako.
- Lahko pa vnesete TRUE ali 1, če želite poiskati približno ujemanje.
Korak 11. Vnesite) na koncu, da zaprete formulo
Zdaj lahko zaženete svojo formulo in preverite, ali je izbrana iskalna postavka na vašem drugem seznamu ponovljena ali nova vrednost.
Na primer, če se vaš drugi seznam začne pri B1, prvi pa iz celic A1 do A5, bo vaša formula videti tako = vlookup (B1, $ A $ 1: $ A $ 5, 1, napačno).
Korak 12. Pritisnite ↵ Enter ali ⏎ Vrnite se na tipkovnico.
To bo zagnalo formulo in na prvem seznamu poiskalo prvo postavko z drugega seznama.
- Če je to ponavljajoča se vrednost, boste v celici s formulo znova natisnili isto vrednost.
- Če je to nova vrednost, boste videli " #N/A"natisnjeno tukaj.
- Na primer, če iščete "John" na prvem seznamu, zdaj pa v celici s formulo vidite "John", je to ponovljena vrednost, ki se pojavi na obeh seznamih. Če vidite "#N/A", je to nova vrednost na drugem seznamu.
Korak 13. Izberite celico formule
Ko zaženete formulo in vidite rezultate za prvo postavko seznama, kliknite celico formule, da jo izberete.
Korak 14. Kliknite in povlecite navzdol zeleno piko v spodnjem desnem kotu celice
S tem boste celico formule razširili vzdolž seznama in formulo uporabili za vsako postavko seznama na drugem seznamu.
- Na ta način lahko primerjate vse postavke na drugem seznamu s celotnim prvim seznamom.
- Tako boste na vašem prvem seznamu iskali vsak element na drugem seznamu posebej in rezultat prikazali poleg vsake celice posebej.
- Če želite namesto "#N/A" videti drugo oznako za nove vrednosti, uporabite to formulo: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "Nova vrednost"). To bo natisnilo »Nova vrednost« za nove vrednosti namesto »#N/A«.
