Ši „wikiHow“moko jus, kaip naudoti sąlyginį formatavimą, kad būtų galima palyginti visus „Excel“skaičiuoklės dviejų atskirų sąrašų langelius ir pažymėti langelius, rodomus abiejuose sąrašuose. Ši funkcija galima tik „Excel“darbalaukio versijoje, o programa mobiliesiems jos nepalaiko.
Žingsniai
1 metodas iš 2: „CountIf“formulės naudojimas
Žingsnis 1. Atidarykite „Excel“skaičiuoklę, kurią norite redaguoti
Raskite skaičiuoklės failą su norimais palyginti sąrašais ir dukart spustelėkite failą, kad jį atidarytumėte „Microsoft Excel“.
2 žingsnis. Pasirinkite savo pirmąjį sąrašą
Spustelėkite pirmąjį pirmojo sąrašo langelį ir vilkite pelę iki pat paskutinio sąrašo langelio, kad pasirinktumėte visą duomenų diapazoną.
Žingsnis 3. Įrankių juostos juostoje spustelėkite skirtuką Formulės
Šį skirtuką rasite virš įrankių juostos skaičiuoklės viršuje. Įrankių juostos juostoje bus atidaryti jūsų formulės įrankiai.
Žingsnis 4. Įrankių juostoje spustelėkite Apibrėžti vardą
Šią parinktį rasite juostos „Formulės“viduryje. Bus atidarytas naujas iššokantis langas ir galėsite pavadinti savo sąrašą.
Žingsnis 5. Į lauką Pavadinimas įveskite List1
Spustelėkite iššokančio lango viršuje esantį teksto lauką ir čia įveskite sąrašo pavadinimą.
- Vėliau galite naudoti šį pavadinimą, norėdami įterpti savo sąrašą į palyginimo formulę.
- Arba čia galite savo sąrašui suteikti kitą pavadinimą. Pvz., Jei tai yra vietų sąrašas, galite jį pavadinti „locations1“arba „locationList“.
Žingsnis 6. Iššokančiajame lange spustelėkite Gerai
Tai patvirtins jūsų veiksmus ir pavadins jūsų sąrašą.
Žingsnis 7. Antrąjį sąrašą pavadinkite „List2“
Atlikite tuos pačius veiksmus, kaip ir pirmasis sąrašas, ir suteikite antrajam sąrašui pavadinimą. Tai leis vėliau greitai naudoti šį antrąjį sąrašą savo palyginimo formulėje.
Sąrašui galite suteikti bet kokį norimą vardą. Būtinai prisiminkite arba užsirašykite kiekvieno čia pateikto sąrašo pavadinimą
8. Pasirinkite pirmąjį sąrašą
Spustelėkite pirmąjį pirmojo sąrašo langelį ir vilkite žemyn, kad pasirinktumėte visą duomenų diapazoną.
Prieš nustatydami sąlyginį formatavimą įsitikinkite, kad pasirinktas pirmasis sąrašas
Žingsnis 9. Įrankių juostoje spustelėkite skirtuką Pagrindinis
Tai yra pirmasis skirtukas viršutiniame kairiajame įrankių juostos juostos kampe. Įrankių juostoje bus atidaryti pagrindiniai skaičiuoklės įrankiai.
Žingsnis 10. Įrankių juostoje spustelėkite Sąlyginis formatavimas
Ši parinktis atrodo kaip maža skaičiuoklės piktograma, kai kurios ląstelės paryškintos raudonai ir mėlynai. Bus atidarytas visų formatavimo parinkčių išskleidžiamasis meniu.
Žingsnis 11. Išskleidžiamajame meniu spustelėkite Nauja taisyklė
Bus atidarytas naujas iššokantis langas ir galėsite rankiniu būdu nustatyti naują pasirinkto diapazono formatavimo taisyklę.
Žingsnis 12. Pasirinkite parinktį „Naudokite formulę, kad nustatytumėte, kurias langelius reikia formatuoti“
Ši parinktis leis rankiniu būdu įvesti formatavimo formulę, kad galėtumėte palyginti du sąrašus.
- Įjungta „Windows“, tai rasite taisyklių sąrašo apačioje laukelyje „Pasirinkti taisyklės tipą“.
- Įjungta „Mac“, pasirinkite Klasika iššokančiojo lango viršuje esančiame išskleidžiamajame meniu „Stilius“. Tada raskite šią parinktį antrame išskleidžiamajame meniu po stiliumi.
Žingsnis 13. Iššokančiajame lange spustelėkite formulės lauką
Čia galite įvesti bet kokią galiojančią „Excel“formulę, kad nustatytumėte sąlyginio formatavimo taisyklę.
Žingsnis 14. Į formulės juostą įveskite = countif (List2, A1) = 1
Ši formulė nuskaitys du jūsų sąrašus ir pažymės visas pirmojo sąrašo ląsteles, kurios taip pat rodomos antrame sąraše.
- Pakeiskite A1 formulėje pirmojo sąrašo pirmo langelio numeriu.
- Pvz., Jei pirmasis jūsų pirmojo sąrašo langelis yra langelis D5, jūsų formulė atrodys taip: = countif (List2, D5) = 1.
- Jei antrajam sąrašui suteikėte kitokį pavadinimą, būtinai pakeiskite „List2“formulėje faktiniu savo sąrašo pavadinimu.
- Arba, jei norite pažymėti langelius, pakeiskite formulę į = countif (List2, A1) = 0 nereikia pasirodys antrame sąraše.
Žingsnis 15. Į formulės juostą įveskite = countif (List1, B1) = 1 (neprivaloma)
Jei norite rasti ir pažymėti langelius savo antra sąrašas, kuris taip pat rodomas pirmajame sąraše, naudokite šią formulę, o ne pirmąją.
Pakeiskite List1 savo pirmojo sąrašo pavadinimu, o B1 - antrojo sąrašo pirmuoju langeliu
Žingsnis 16. Pasirinkite langeliams pažymėti pasirinktinį formatą (neprivaloma)
Galite pažymėti pasirinktą fono užpildymo spalvą ir skirtingus šriftų stilius, kad pažymėtumėte langelius, kuriuos nustato jūsų formulė.
- Įjungta „Windows“, spustelėkite Formatas mygtuką, esantį iššokančio lango apačioje dešinėje. Fono spalvą galite pasirinkti skirtuke „Užpildyti“, o šrifto stilių - skirtuke „Šriftas“.
- Įjungta „Mac“, apačioje esančiame išskleidžiamajame meniu „Formatas su“pasirinkite iš anksto nustatytą formatą. Taip pat galite pasirinkti pasirinktinis formatas čia rankiniu būdu pasirinkti fono užpildymo ir šrifto stilių.
Žingsnis 17. Iššokančiajame lange spustelėkite Gerai
Tai patvirtins ir pritaikys jūsų palyginimo formulę. Visos jūsų pirmojo sąrašo ląstelės, kurios taip pat rodomos antrame sąraše, bus pažymėtos pasirinkta spalva ir šriftu.
- Pavyzdžiui, jei pasirinksite šviesiai raudoną užpildą su tamsiai raudonu tekstu, visos pasikartojančios ląstelės taps tokios spalvos jūsų pirmajame sąraše.
- Jei naudojate antrąją aukščiau pateiktą formulę, sąlyginis formatavimas pažymės pasikartojančius langelius jūsų antrajame sąraše, o ne pirmąjį.
2 metodas iš 2: naudojant „VLookup“formulę
Žingsnis 1. Atidarykite „Excel“skaičiuoklę
Raskite „Excel“failą su norimais palyginti sąrašais ir dukart spustelėkite failo pavadinimą arba piktogramą, kad atidarytumėte skaičiuoklę „Microsoft Excel“.
Žingsnis 2. Spustelėkite tuščią langelį šalia pirmojo antrojo sąrašo elemento
Skaičiuoklėje raskite antrąjį sąrašą ir spustelėkite tuščią langelį šalia pirmojo sąrašo elemento viršuje.
- Čia galite įterpti savo „VLookup“formulę.
- Arba galite pasirinkti bet kurį tuščią skaičiuoklės langelį. Naudojant šį langelį jums bus patogiau matyti palyginimą šalia antrojo sąrašo.
3 žingsnis. Į tuščią langelį įveskite = vlookup
„VLookup“formulė leis palyginti visus dviejų atskirų sąrašų elementus ir pamatyti, ar vertė yra pasikartojanti, ar nauja.
Neuždarykite formulės skliaustų, kol formulė neužbaigta
Žingsnis 4. Antrame sąraše pasirinkite pirmąjį elementą
Neuždarę formulės skliaustelių, spustelėkite pirmąjį antrojo sąrašo elementą. Tai į formulę įterps pirmąjį antrojo sąrašo langelį.
Žingsnis 5. Formulėje įveskite a, kablelį
Pasirinkę pirmą antrojo sąrašo langelį, formulėje įveskite kablelį. Toliau galėsite pasirinkti palyginimo diapazoną.
Žingsnis 6. Laikykite nuspaudę ir pasirinkite visą pirmąjį sąrašą
Tai įterps jūsų pirmojo sąrašo langelių diapazoną į antrąją „VLookup“formulės dalį.
Tai leis jums ieškoti pirmajame sąraše pasirinkto elemento iš antrojo sąrašo (pirmojo elemento antrojo sąrašo viršuje) ir grįžti, jei tai yra pasikartojanti ar nauja vertė
Žingsnis 7. Formulėje įveskite a, kablelį
Tai užrakins jūsų formulės palyginimo diapazoną.
8. Įveskite 1 formulėje po kablelio
Šis skaičius parodo jūsų stulpelio rodyklės numerį. Jis paragins „VLookup“formulę ieškoti tikrojo sąrašo stulpelio, o ne kito stulpelio šalia jo.
Jei norite, kad formulė grąžintų vertę iš stulpelio, esančio šalia pirmojo sąrašo, įveskite 2 čia
9. Žingsnis formulėje įveskite a, kablelį
Tai užrakins jūsų stulpelio indekso numerį (1) „VLookup“formulėje.
Žingsnis 10. Formulėje įveskite FALSE
Taip sąraše bus ieškoma tiksli pasirinkto paieškos elemento atitiktis (pirmasis elementas antrojo sąrašo viršuje), o ne apytikslės atitiktys.
- Vietoj FALSE galite naudoti 0, tai yra tas pats.
- Arba galite įvesti TRUE arba 1, jei norite ieškoti apytikslės atitikties.
Žingsnis 11. Įveskite) pabaigoje, kad uždarytumėte formulę
Dabar galite paleisti formulę ir pamatyti, ar pasirinktas antrojo sąrašo paieškos elementas yra pakartotinė ar nauja vertė.
Pavyzdžiui, jei jūsų antrasis sąrašas prasideda nuo B1, o pirmasis sąrašas pereina iš langelių A1 į A5, jūsų formulė atrodys taip = vlookup (B1, $ A $ 1: $ A $ 5, 1, false).
Žingsnis 12. Paspauskite ↵ Enter arba Grįžkite į klaviatūrą.
Bus paleista formulė ir jūsų pirmajame sąraše ieškoma pirmo elemento iš antrojo sąrašo.
- Jei tai yra pasikartojanti vertė, formulės langelyje vėl matysite tą pačią reikšmę.
- Jei tai nauja vertė, pamatysite " #N/A"atspausdinta čia.
- Pvz., Jei pirmajame sąraše ieškote „Jonas“, o dabar formulės langelyje matote „Jonas“, tai yra pakartotinė vertė, kuri atsiranda abiejuose sąrašuose. Jei matote „#N/A“, tai nauja vertė antrajame sąraše.
Žingsnis 13. Pasirinkite formulės langelį
Paleidę formulę ir pamatę pirmojo sąrašo elemento rezultatus, spustelėkite formulės langelį, kad jį pasirinktumėte.
Žingsnis 14. Spustelėkite ir vilkite žemyn žalią tašką apatiniame dešiniajame langelio kampe
Tai išplės jūsų formulės langelį iš sąrašo ir pritaikys formulę kiekvienam antrojo sąrašo sąrašo elementui.
- Tokiu būdu galite palyginti kiekvieną antrojo sąrašo elementą su visu pirmuoju sąrašu.
- Pirmame sąraše bus ieškoma kiekvieno antrojo sąrašo elemento atskirai, o rezultatas bus rodomas šalia kiekvieno langelio.
- Jei norite matyti kitokį naujų verčių žymeklį, o ne „#N/A“, naudokite šią formulę: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), „New Value“). Bus spausdinama „Nauja vertė“naujoms vertėms, o ne „#N/A“.
