Žiūrėkite vaizdo įrašą - palyginkite du „Excel“ stulpelius, kad gautumėte atitikčių ir skirtumų
Viena užklausa, kurią gaunu daug, yra „kaip palyginti du„ Excel “stulpelius?“.
Tai galima padaryti įvairiais būdais, o naudojamas metodas priklausys nuo duomenų struktūros ir to, ko vartotojas nori iš jos.
Pvz., Galbūt norėsite palyginti du stulpelius ir surasti arba paryškinti visus atitinkančius duomenų taškus (kurie yra abiejuose stulpeliuose) arba tik skirtumus (kai duomenų taškas yra viename stulpelyje, o ne kitame) ir pan.
Kadangi manęs apie tai klausia tiek daug, nusprendžiau parašyti šią didžiulę pamoką, siekdama apimti daugumą (jei ne visus) galimus scenarijus.
Jei manote, kad tai naudinga, perduokite ją kitiems „Excel“ vartotojams.
Atminkite, kad šiame vadove parodyti stulpelių palyginimo būdai nėra vieninteliai.
Remiantis jūsų duomenų rinkiniu, jums gali tekti pakeisti arba pakoreguoti metodą. Tačiau pagrindiniai principai išliktų tie patys.
Jei manote, kad ką nors galima pridėti prie šios pamokos, praneškite man komentarų skiltyje
Palyginkite du stulpelius, kad gautumėte tikslią eilučių atitiktį
Tai yra paprasčiausia palyginimo forma. Tokiu atveju turite palyginti eilutes pagal eilutes ir nustatyti, kurios eilutės turi tuos pačius duomenis, o kurios - ne.
Pavyzdys: palyginkite tos pačios eilutės ląsteles
Žemiau yra duomenų rinkinys, kuriame turiu patikrinti, ar pavadinimas A stulpelyje yra tas pats B stulpelyje, ar ne.
Jei yra rungtynių, man reikia rezultato kaip „TIESA“, o jei nesutampa, man reikia rezultato kaip „NETIESA“.
Žemiau pateikta formulė tai padarys:
= A2 = B2
Pavyzdys: palyginkite tos pačios eilutės ląsteles (naudodami IF formulę)
Jei norite gauti daugiau aprašomojo rezultato, galite naudoti paprastą IF formulę, kad grąžintumėte „Atitikti“, kai pavadinimai yra vienodi, ir „Neatitikimas“, kai pavadinimai skiriasi.
= IF (A2 = B2, „Atitikimas“, „Neatitikimas“)
Pastaba: jei norite, kad lyginamosios knygos būtų didžiosios ir mažosios raidės, naudokite šią IF formulę:
= IF (TIKSLAS (A2, B2), „Atitikimas“, „Neatitikimas“)
Naudojant aukščiau pateiktą formulę, „IBM“ ir „ibm“ būtų laikomi dviem skirtingais pavadinimais, o aukščiau pateikta formulė grąžintų „Neatitikimas“.
Pavyzdys: paryškinkite eilutes su atitinkančiais duomenimis
Jei norite paryškinti eilutes, kuriose yra atitinkamų duomenų (o ne gauti rezultatą atskirame stulpelyje), tai galite padaryti naudodami sąlyginį formatavimą.
Štai žingsniai, kaip tai padaryti:
- Pasirinkite visą duomenų rinkinį.
- Spustelėkite skirtuką „Pagrindinis“.
- Grupėje Stiliai spustelėkite parinktį „Sąlyginis formatavimas“.
- Išskleidžiamajame meniu spustelėkite „Nauja taisyklė“.
- Dialogo lange „Nauja formatavimo taisyklė“ spustelėkite „Naudokite formulę, kad nustatytumėte, kurias langelius reikia formatuoti“.
- Formulės lauke įveskite formulę: = $ A1 = $ B1
- Spustelėkite mygtuką Formatuoti ir nurodykite formatą, kurį norite taikyti atitinkamiems langeliams.
- Spustelėkite Gerai.
Taip bus paryškintos visos ląstelės, kurių kiekvienos eilutės pavadinimai yra vienodi.
Palyginkite du stulpelius ir paryškinimo atitiktis
Jei norite palyginti du stulpelius ir paryškinti atitinkančius duomenis, galite naudoti pasikartojančias funkcijas sąlyginio formato formatu.
Atminkite, kad tai skiriasi nuo to, ką matėme lyginant kiekvieną eilutę. Šiuo atveju mes nedarysime eilutės palyginimo.
Pavyzdys: palyginkite du stulpelius ir paryškinkite atitinkančius duomenis
Dažnai gausite duomenų rinkinius ten, kur yra atitikčių, tačiau jie gali būti ne toje pačioje eilutėje.
Kažkas, kaip parodyta žemiau:
Atkreipkite dėmesį, kad sąrašas A stulpelyje yra didesnis nei B.
Jei norite paryškinti visus atitinkančius įmonių pavadinimus, galite tai padaryti naudodami sąlyginį formatavimą.
Štai žingsniai, kaip tai padaryti:
- Pasirinkite visą duomenų rinkinį.
- Spustelėkite skirtuką Pagrindinis.
- Grupėje Stiliai spustelėkite parinktį „Sąlyginis formatavimas“.
- Užveskite žymeklį ant parinkties Paryškinti langelio taisykles.
- Spustelėkite Pasikartojančios vertės.
- Dialogo lange Pasikartojančios vertės įsitikinkite, kad pasirinktas „Dublikatas“.
- Nurodykite formatavimą.
- Spustelėkite Gerai.
Pirmiau minėti veiksmai suteiks jums rezultatą, kaip parodyta žemiau.
Pastaba: Sąlyginio formatavimo dublikato taisyklė neskiria didžiųjų ir mažųjų raidžių. Taigi „obuolys“ ir „obuolys“ laikomi vienodais ir būtų paryškinti kaip dublikatai.
Pavyzdys: palyginkite du stulpelius ir paryškinkite neatitinkančius duomenis
Jei norite paryškinti pavadinimus, kurie yra viename sąraše, o ne kitame, taip pat galite naudoti sąlyginį formatavimą.
- Pasirinkite visą duomenų rinkinį.
- Spustelėkite skirtuką Pagrindinis.
- Grupėje Stiliai spustelėkite parinktį „Sąlyginis formatavimas“.
- Užveskite žymeklį ant parinkties Paryškinti langelio taisykles.
- Spustelėkite Pasikartojančios vertės.
- Dialogo lange Pasikartojančios vertės įsitikinkite, kad pasirinktas „Unikalus“.
- Nurodykite formatavimą.
- Spustelėkite Gerai.
Tai suteiks jums rezultatą, kaip parodyta žemiau. Jame paryškintos visos ląstelės, kurių pavadinimas yra kitame sąraše.
Palyginkite du stulpelius ir raskite trūkstamus duomenų taškus
Jei norite nustatyti, ar duomenų taškas iš vieno sąrašo yra kitame sąraše, turite naudoti paieškos formules.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite identifikuoti įmones, kurios yra A stulpelyje, bet ne B stulpelyje,
Norėdami tai padaryti, galiu naudoti šią VLOOKUP formulę.
= KLAIDA (PAKEITIMAS (A2, $ B $ 2: $ B $ 10,1,0))
Ši formulė naudoja funkciją VLOOKUP, kad patikrintų, ar A stulpelyje nurodytas įmonės pavadinimas yra B stulpelyje. Jei jis yra, jis grąžins tą pavadinimą iš B stulpelio, kitu atveju grąžins #N/A klaidą.
Šių pavadinimų, kurie grąžina klaidą #N/A, trūksta B stulpelyje.
Funkcija ISERROR grąžintų TRUE, jei VLOOKUP rezultatas yra klaida, o FALSE, jei tai ne klaida.
Jei norite gauti visų pavadinimų, kuriuose nėra atitikties, sąrašą, galite filtruoti rezultatų stulpelį, kad gautumėte visus langelius su TRUE.
Norėdami tai padaryti, taip pat galite naudoti funkciją MATCH;
= NE (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Pastaba: Asmeniškai aš naudoju funkciją „Match“ (arba INDEX/MATCH derinį), o ne „VLOOKUP“. Manau, kad tai lankstesnė ir galingesnė. Čia galite perskaityti skirtumą tarp „Vlookup“ ir „Index/Match“.
Palyginkite du stulpelius ir ištraukite atitinkančius duomenis
Jei turite du duomenų rinkinius ir norite palyginti vieno sąrašo elementus su kitu ir gauti atitinkamą duomenų tašką, turite naudoti paieškos formules.
Pavyzdys: ištraukite atitinkančius duomenis (tiksliai)
Pavyzdžiui, žemiau esančiame sąraše noriu gauti 2 stulpelio rinkos vertę. Norėdami tai padaryti, turiu surasti tą vertę 1 stulpelyje ir tada gauti atitinkamą rinkos vertę.
Žemiau yra formulė, kuri tai padarys:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
arba
= INDEKSAS ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Pavyzdys: ištraukite atitinkamus duomenis (iš dalies)
Jei gausite duomenų rinkinį, kuriame abiejų stulpelių pavadinimai šiek tiek skiriasi, naudojant aukščiau pateiktas peržiūros formules neveiks.
Šioms paieškos formulėms reikia tikslios atitikties, kad būtų pateiktas teisingas rezultatas. Funkcijoje VLOOKUP arba MATCH yra apytikslė atitikties parinktis, tačiau čia jos naudoti negalima.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau. Atminkite, kad 2 stulpelyje yra neužbaigtų pavadinimų (pvz., JPMorgan vietoj JPMorgan Chase ir Exxon vietoj ExxonMobil).
Tokiu atveju galite naudoti dalinę peržiūrą naudodami pakaitos simbolius.
Ši formulė suteiks teisingą rezultatą šiuo atveju:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
arba
= INDEKSAS ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
Pirmiau pateiktame pavyzdyje žvaigždutė (*) yra pakaitos simbolis, kuris gali reikšti bet kokį simbolių skaičių. Kai paieškos vertė yra lygiagreti su ja iš abiejų pusių, bet kokia 1 stulpelio vertė, kurioje yra paieškos vertė 2 stulpelyje, bus laikoma atitiktimi.
Pvz., * „Exxon *“ atitiktų „ExxonMobil“ (kaip * gali būti bet koks simbolių skaičius).
Jums taip pat gali patikti šie „Excel“ patarimai ir vadovėliai:
- Kaip palyginti du „Excel“ lapus (dėl skirtumų)
- Kaip paryškinti tuščias ląsteles „Excel“.
- „Excel“ paryškinkite KIEKVIENĄ EILUTĘ.
- Išplėstinis „Excel“ filtras: išsamus vadovas su pavyzdžiais.
- Paryškinkite eilutes pagal langelio vertę „Excel“.
