Viena iš daugelio „Microsoft Excel“galimybių yra galimybė palyginti du duomenų sąrašus, identifikuoti sąrašų atitiktis ir nustatyti, kurie elementai yra tik viename sąraše. Tai naudinga lyginant finansinius įrašus arba tikrinant, ar tam tikras vardas yra duomenų bazėje. Galite naudoti funkciją MATCH, kad nustatytumėte ir pažymėtumėte atitinkančius ar nesutampančius įrašus, arba galite naudoti kondicionavimo formatavimą naudodami funkciją COUNTIF. Šie veiksmai nurodo, kaip juos naudoti, kad atitiktų jūsų duomenis.
Žingsniai
1 metodas iš 2: įrašų identifikavimas naudojant funkciją MATCH
Žingsnis 1. Nukopijuokite duomenų sąrašus į vieną darbalapį
„Excel“gali dirbti su keliais darbalapiais vienoje darbaknygėje arba su keliomis darbaknygėmis, tačiau jums bus lengviau palyginti sąrašus, jei nukopijuosite jų informaciją į vieną darbalapį.
Žingsnis 2. Suteikite kiekvienam sąrašo elementui unikalų identifikatorių
Jei jūsų du sąrašai neturi bendro jų identifikavimo būdo, gali reikėti prie kiekvieno duomenų sąrašo pridėti papildomą stulpelį, kuris identifikuoja tą elementą „Excel“, kad jis galėtų pamatyti, ar tam tikro sąrašo elementas yra susijęs su elementu kitame sąraše. Šio identifikatoriaus pobūdis priklausys nuo to, kokius duomenis bandote atitikti. Jums reikės kiekvieno stulpelių sąrašo identifikatoriaus.
- Finansiniai duomenys, susiję su tam tikru laikotarpiu, pvz., Su mokesčių įrašais, gali būti turto aprašymas, turto įsigijimo data arba abu. Kai kuriais atvejais įrašas gali būti identifikuotas su kodo numeriu; tačiau jei ta pati sistema nenaudojama abiem sąrašuose, šis identifikatorius gali sukurti atitiktis ten, kur jų nėra, arba ignoruoti atitiktis, kurios turėtų būti sudarytos.
- Kai kuriais atvejais galite paimti elementus iš vieno sąrašo ir sujungti juos su kito sąrašo elementais, kad sukurtumėte identifikatorių, pvz., Fizinio turto aprašą ir pirkimo metus. Norėdami sukurti tokį identifikatorių, turite sujungti (pridėti, sujungti) duomenis iš dviejų ar daugiau langelių naudodami simbolį „&“. Jei norite sujungti elemento aprašą langelyje F3 su data langelyje G3, atskirtame tarpais, įveskite formulę '= F3 & "" & G3' kitoje tos eilutės ląstelėje, pvz., E3. Jei į identifikatorių norėjote įtraukti tik metus (nes viename sąraše naudojamos visos datos, o kitame - tik metai), funkciją YEAR turėtumėte įtraukti į langelį E3 įvesdami „= F3 &" "& YEAR (G3)“. (Neįtraukite pavienių kabučių; jos skirtos tik kaip pavyzdys.)
- Sukūrę formulę, galite ją nukopijuoti į visas kitas identifikatoriaus stulpelio ląsteles, pasirinkę langelį su formule ir vilkdami užpildymo rankenėlę per kitas stulpelio, kuriame norite nukopijuoti formulę, langelius. Kai atleisite pelės klavišą, kiekviena perkelta ląstelė bus užpildyta formule, o ląstelių nuorodos bus pritaikytos prie atitinkamų tos pačios eilutės langelių.
3 žingsnis. Jei įmanoma, standartizuokite duomenis
Nors protas pripažįsta, kad „Inc. ir „Įtraukta“reiškia tą patį, „Excel“to nedaro, nebent turite iš naujo suformatuoti vieną ar kitą žodį. Taip pat galite laikyti tokias vertes kaip $ 11, 950 ir $ 11, 999,95 pakankamai artimas, kad atitiktų, tačiau „Excel“to nepadarys, nebent to pasakysite.
- Galite sutrumpinti kai kurias santrumpas, pvz., „Co“- „Company“ir „Inc“- „Incorporated“, naudodami kairės eilutės funkciją, kad sutrumpintumėte papildomus simbolius. Kitos santrumpos, pvz., „Assn“, skirtos „Association“, gali būti tinkamiausios išspręstų duomenų įvedimo stiliaus vadovą ir tada parašytų programą, skirtą ieškoti ir ištaisyti netinkamus formatus.
- Jei naudojate skaičių eilutes, pvz., Pašto kodus, kai kai kuriuose įrašuose yra ZIP+4 priesaga, o kituose - ne, vėl galite naudoti kairės eilutės funkciją, kad atpažintumėte ir suderintumėte tik pirminius pašto kodus. Jei norite, kad „Excel“atpažintų artimas, bet ne tas pačias skaitines reikšmes, galite naudoti funkciją ROUND, kad suapvalintumėte artimas vertes iki to paties skaičiaus ir jas suderintumėte.
- Papildomus tarpus, pvz., Vietoj vieno žodžio įvedus du žodžius, galima pašalinti naudojant TRIM funkciją.
Žingsnis 4. Sukurkite palyginimo formulės stulpelius
Kaip ir turėjote sukurti stulpelius sąrašo identifikatoriams, taip pat turėsite sukurti stulpelius pagal formulę, kuri lygins jus. Kiekvienam sąrašui reikės vieno stulpelio.
Šiuose stulpeliuose norite pažymėti kažką panašaus į „Trūksta?“
Žingsnis 5. Kiekviename langelyje įveskite palyginimo formulę
Palyginimo formulėje naudosite funkciją MATCH, įdėtą į kitą „Excel“funkciją, ISNA.
- Formulė yra „= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))“, kur pirmojo sąrašo identifikatoriaus stulpelio langelis lyginamas su kiekvienu iš antrojo sąrašo identifikatorių pažiūrėkite, ar jis atitinka vieną iš jų. Jei jis nesutampa, trūksta įrašo ir toje ląstelėje bus rodomas žodis „TRUE“. Jei jis sutampa, įrašas yra ir bus rodomas žodis „FALSE“. (Įvesdami formulę neįtraukite pridedamų kabučių.)
- Formulę galite nukopijuoti į likusius stulpelio langelius taip pat, kaip nukopijavote langelio identifikatoriaus formulę. Šiuo atveju keičiasi tik identifikatoriaus langelio langelio nuoroda, nes uždėjus dolerio ženklus prieš eilutės ir stulpelio nuorodas į pirmą ir paskutinį langelį antrojo langelio identifikatorių sąraše, jie tampa absoliučiomis nuorodomis.
- Galite nukopijuoti pirmojo sąrašo palyginimo formulę į pirmąjį antrojo sąrašo stulpelio langelį. Tada turėsite redaguoti langelių nuorodas, kad „G3“būtų pakeistas antrojo sąrašo pirmo identifikatoriaus langelio nuoroda, o „$ L $ 3: $ L $ 14“- pirmuoju ir paskutiniu identifikatoriaus langeliu antrasis sąrašas. (Palikite dolerio ženklus ir dvitaškį ramybėje.) Tada galite nukopijuoti šią redaguotą formulę į likusias antrojo sąrašo palyginimo eilutės ląsteles.
Žingsnis 6. Rūšiuokite sąrašus, kad prireikus lengviau pamatytumėte nesuderinamas vertes
Jei jūsų sąrašai yra dideli, gali tekti juos surūšiuoti, kad visos nesuderinamos vertės būtų sujungtos. Toliau pateiktose instrukcijose pateiktos instrukcijos formules pavers vertėmis, kad būtų išvengta perskaičiavimo klaidų, o jei jūsų sąrašai yra dideli, išvengsite ilgo perskaičiavimo laiko.
- Norėdami jį pasirinkti, vilkite pelę virš visų sąrašo langelių.
- „Excel 2003“meniu „Redaguoti“pasirinkite „Kopijuoti“arba „Excel 2007“arba „2010“pagrindinės juostos iškarpinės grupėje.
- „Excel 2003“meniu „Redaguoti“arba „Įklijuoti“pasirinkite „Įklijuoti specialųjį meniu“arba „Excel 2007“arba „2010 -ųjų pradžios juostos“iškarpinės grupės išskleidžiamąjį mygtuką.
- Dialogo lango Specialusis įklijavimas sąraše Įklijuoti pasirinkite „Vertės“. Spustelėkite Gerai, kad uždarytumėte dialogo langą.
- „Excel 2003“meniu Duomenys pasirinkite Rūšiuoti arba „Excel 2007“ar „2010“duomenų juostos Rūšiuoti ir filtruoti grupę.
- Dialogo lange Rūšiuoti pagal sąrašą „Mano duomenų diapazonas turi“pasirinkite „Antraštės eilutė“, pasirinkite „Trūksta?“(arba pavadinimą, kurį iš tikrųjų suteikėte palyginimo stulpelio antraštei) ir spustelėkite Gerai.
- Pakartokite šiuos veiksmus kitam sąrašui.
Žingsnis 7. Vizualiai palyginkite neatitinkančius elementus, kad pamatytumėte, kodėl jie nesutampa
Kaip minėta anksčiau, „Excel“sukurta ieškoti tikslių duomenų atitikčių, nebent nustatėte ją apytikslių paieškai. Jūsų nesutapimas gali būti toks paprastas, kaip atsitiktinis raidžių ar skaitmenų perkėlimas. Taip pat gali reikėti nepriklausomo patikrinimo, pvz., Patikrinti, ar pirmiausia reikia pranešti apie išvardytą turtą.
2 metodas iš 2: sąlyginis formatavimas naudojant COUNTIF
Žingsnis 1. Nukopijuokite duomenų sąrašus į vieną darbalapį
2 žingsnis. Nuspręskite, kuriame sąraše norite paryškinti atitinkančius ar nesutampančius įrašus
Jei norite paryškinti įrašus tik viename sąraše, tikriausiai norėsite paryškinti tik šiam sąrašui būdingus įrašus; tai yra įrašai, kurie neatitinka kito sąrašo įrašų. Jei norite paryškinti įrašus abiejuose sąrašuose, norėsite paryškinti įrašus, kurie atitinka vienas kitą. Šiame pavyzdyje darysime prielaidą, kad pirmasis sąrašas užima langelius G3 – G14, o antrasis - L3 – L14.
Žingsnis 3. Sąraše pasirinkite elementus, kuriuose norite paryškinti unikalius ar atitinkančius elementus
Jei norite paryškinti atitinkamus elementus abiejuose sąrašuose, turėsite pasirinkti sąrašus po vieną ir kiekvienam sąrašui taikyti palyginimo formulę (aprašytą kitame žingsnyje).
Žingsnis 4. Taikykite atitinkamą palyginimo formulę
Norėdami tai padaryti, turėsite pasiekti „Excel“versijos dialogo langą Sąlyginis formatavimas. Programoje „Excel 2003“tai darote meniu Formatas pasirinkę Sąlyginis formatavimas, o programoje „Excel 2007“ir 2010 spustelėję mygtuką Sąlyginis formatavimas juostos „Pagrindinis“grupėje „Stiliai“. Pasirinkite taisyklės tipą „Formulė“ir įveskite formulę lauke Redaguoti taisyklės aprašą.
- Jei norite paryškinti pirmajame sąraše unikalius įrašus, formulė būtų „= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)“, o antrojo sąrašo langelių diapazonas pateikiamas kaip absoliučios vertės ir nuoroda į pirmojo sąrašo pirmą langelį kaip santykinę vertę. (Neįveskite uždarytų kabučių.)
- Jei norite paryškinti antrajame sąraše unikalius įrašus, formulė būtų „= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)“, o pirmojo sąrašo langelių diapazonas pateikiamas kaip absoliučios vertės ir nuoroda į pirmąjį antrojo sąrašo langelį kaip santykinę vertę. (Neįveskite uždarytų kabučių.)
- Jei norite paryškinti kiekvieno sąrašo įrašus, esančius kitame sąraše, jums reikės dviejų formulių: viena pirmajam sąrašui ir kita antrajam. Pirmojo sąrašo formulė yra "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", o antrojo sąrašo formulė yra COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". kaip pažymėta anksčiau, pasirenkate pirmąjį sąrašą, kad pritaikytumėte jo formulę, tada pasirinkite antrąjį sąrašą, kad pritaikytumėte jo formulę.
- Taikykite bet kokį formatą, kurį norite pažymėti pažymėtus įrašus. Spustelėkite Gerai, kad uždarytumėte dialogo langą.
Vaizdo įrašas - naudojant šią paslaugą kai kuri informacija gali būti bendrinama su „YouTube“
Patarimai
- Užuot naudoję langelio nuorodą su sąlyginio formatavimo metodu COUNTIF, galite įvesti vertę, kurios reikia ieškoti, ir pažymėti vieną ar kelis tos vertės pavyzdžių sąrašus.
- Norėdami supaprastinti palyginimo formas, galite sukurti savo sąrašo pavadinimus, pvz., „Sąrašas1“ir „Sąrašas2“. Tada, rašydami formules, šie sąrašų pavadinimai gali pakeisti absoliučius langelių diapazonus, naudojamus aukščiau pateiktuose pavyzdžiuose.
