Funkcija palyginti du „Excel“ stulpelius. Kaip palyginti du „Excel“ stulpelius - „Excel“ duomenų palyginimo metodai
Nurodymai
Jei norite palyginti lentelės stulpelio langelių teksto reikšmes su teksto pavyzdžiu ir perskaičiuoti visus, kurie atitinka, naudokite įmontuotą COUNTIF langelių palyginimo funkciją. Pradėkite užpildydami stulpelį teksto reikšmėmis, tada kitame stulpelyje spustelėkite langelį, kuriame norite pamatyti skaičiavimo rezultatą, ir įveskite atitinkamą formulę. Pvz., Jei patikrintos vertės yra A stulpelyje ir rezultatas turėtų būti dedamas į pirmąjį C stulpelio langelį, jo turinys turėtų būti toks: \u003d COUNTIF ($ A: $ A; "Vynuogė") Čia „Vynuogė“ yra eilutės reikšmė, su kuria lyginamos visų A stulpelio langelių vertės. Galite praleisti nurodydami ją formulėje, bet įdėkite ją į atskirą langelį (pvz., B1) ir į formulę įterpkite atitinkamą nuorodą: \u003d COUNTIF ($ A: $ A; B1)
Naudokite parinktis sąlyginis formatavimasjei reikia vizualiai paryškinti lentelės eilutės kintamųjų palyginimo rezultatą. Pavyzdžiui, jei A stulpelyje turite pasirinkti langelius, kurių tekstas atitinka B1 langelio modelį, pradėkite pasirinkdami šį stulpelį - spustelėkite jo antraštę. Tada „Excel“ meniu skirtuke Pagrindinis spustelėkite komandų grupės „Stiliai“ mygtuką Sąlyginis formatavimas. Eikite į skyrių „Ląstelių pasirinkimo taisyklės“ ir pasirinkite eilutę „Lygu“. Atsidariusiame lange nurodykite langelio pavyzdį (spustelėkite langelį B1) ir išskleidžiamajame sąraše pasirinkite eilučių derinimo parinktį. Tada spustelėkite mygtuką "Gerai".
Naudokite integruotų funkcijų „IF“ ir „CONCATENATE“ derinį, kai reikia suderinti daugiau nei vieną teksto langelį su šablonu. Funkcija CONCATENATE sujungia nurodytas vertes į vieną eilutės kintamąjį. Pvz., Komanda CONCATENATE (A1; "ir"; B1) pridės tekstą "ir" į eilutę iš langelio A1, o po jos įdės eilutę iš langelio B1. Tokiu būdu sukurtą eilutę galima palyginti su šablonu naudojant IF funkciją. Kai reikia palyginti daugiau nei vieną eilutę, mėginio langeliui patogiau suteikti savo vardą. Norėdami tai padaryti, spustelėkite jį ir kairėje stygos formules vietoj langelio pavadinimo (pvz., C1) įveskite naują jos pavadinimą (pvz., „pavyzdys“). Tada spustelėkite langelį, kuriame turėtų būti palyginimo rezultatas, ir įveskite formulę: IF (CONCATE (A1; "ir"; B1) \u003d pavyzdys; 1; 0) Čia vienetas yra reikšmė, kurią reikš ląstelė su formule nurodykite, ar palyginimas duos teigiamą rezultatą, o neigiamą rezultatą - nulį. Padauginkite šią formulę viskam stygos lentelės, kurias reikia palyginti su pavyzdžiu, yra labai paprastos - perkelkite žymeklį į apatinį dešinįjį langelio kampą ir, kai žymeklis pasikeis (taps juodu kryžiumi), paspauskite kairįjį pelės mygtuką ir vilkite šią ląstelę žemyn iki paskutinės kad būtų galima palyginti stygos.
Jei dirbi su skaičiuoklės dokumentus didelės apimties (daug duomenų / stulpelių), labai sunku kontroliuoti visos informacijos patikimumą / aktualumą. Todėl labai dažnai reikia išanalizuoti du ar daugiau „Excel“ dokumento stulpelių, kad būtų galima nustatyti dublikatus. Ir jei vartotojas neturi informacijos apie visus programos funkcionalumus, jam logiškai gali kilti klausimas: kaip palyginti du „Excel“ stulpelius?
Atsakymą jau seniai sugalvojo šios programos kūrėjai, kurie iš pradžių išdėstė joje komandas, kurios padėtų palyginti informaciją. Apskritai, jei peržengsite šio klausimo gelmes, galite rasti apie keliolika skirtingų būdų, įskaitant atskirų makrokomandų ir formulių rašymą. Tačiau praktika rodo: pakanka žinoti tris ar keturis patikimus būdus, kaip palyginti iškylančius poreikius.
Kaip palyginti du „Excel“ stulpelius dėl rungtynių
Duomenys programoje „Excel“ paprastai lyginami tarp eilučių, tarp stulpelių, su verte, nurodyta kaip nuoroda. Jei jums reikia palyginti stulpelius, galite naudoti įmontuotą funkciją, ty veiksmus „Atitikti“ ir „Jei“. Ir viskas, ko jums reikia, yra „Excel“ ne anksčiau kaip septintieji „išleidimo“ metai.
Pradedame nuo funkcijos „Rungtynės". Pavyzdžiui, lyginami duomenys yra stulpeliuose su adresais C3 ir B3. Palyginimo rezultatas turi būti dedamas į langelį, pavyzdžiui, D3. Spustelėkite šį langelį, įveskite meniu katalogą „formulės“, surandame eilutę „Funkcijų biblioteka“, atidarome išskleidžiamajame sąraše esančias funkcijas, randame žodį „tekstas“ ir spustelėkite „Rungtis“.
Po akimirkos ekrane pamatysite naują formą, kurioje bus tik du laukai: „text one“, „text two“. Juose reikia surinkti balus, tiesiog palygintų stulpelių adresus (C3, B3), tada spustelėkite pažįstamą mygtuką „Gerai“. Dėl to pamatysite rezultatą su žodžiais „Tiesa“ / „Netiesa“. Iš esmės nieko per daug sudėtinga net pradedantiesiems vartotojams! Bet tai toli gražu nėra vienintelis metodas. Pažvelkime į funkciją „If“.
Galimybė palyginti du stulpelius „Excel“ atitikmenyse naudojant „Jei“ leidžia įvesti reikšmes, kurios bus rodomos po operacijos. Žymeklis dedamas į langelį, kuriame bus atliekamas įvestis, atsidaro meniu katalogas „Funkcijų biblioteka“, išskleidžiamajame sąraše parenkama eilutė „logiška“, kurioje pirmąją poziciją užims komanda „Jei“. Mes ją pasirenkame.
Tada prasideda argumentuoto užpildymo forma. „Log_Expression“ yra pačios funkcijos formuluotė. Mūsų atveju tai yra dviejų stulpelių palyginimas, todėl įvedame „B3 \u003d C3“ (arba jūsų stulpelių adresus). Kiti laukai „value_if true“, „value_if_false“. Čia turėtumėte įvesti duomenis (etiketes / žodžius / skaičius), kurie turi atitikti teigiamą / neigiamą rezultatą. Užpildę spaudžiame, kaip įprasta, „ok“. Susipažinkime su rezultatu.
Jei jums reikia atlikti dviejų stulpelių eilutės analizę, trečiame stulpelyje mes patalpiname bet kokią funkciją, kuri buvo aptarta aukščiau ("jei" arba "sutampa"). Mes pratęsiame jo veikimą visame užpildytų kolonų aukštyje. Tada pasirinkite trečią stulpelį, spustelėkite skirtuką „Pagrindinis“, pasirodžiusioje grupėje ieškokite žodžio „stiliai“. Bus atidarytos „Stulpelių / langelių pasirinkimo taisyklės“.
Jose turite pasirinkti komandą „lygus“, tada spustelėkite pirmąjį stulpelį ir spustelėkite „Enter“. Dėl to paaiškėja, kad „tonuojami“ stulpeliai, kur yra atitinkantys rezultatai. Ir iškart pamatysite reikalingą informaciją. Toliau, analizuodami temą „kaip palyginti dviejų„ Excel “stulpelių reikšmes“, pereisime prie tokio metodo kaip sąlyginis formatavimas „Excel“.
„Excel“: sąlyginis formatavimas
Sąlyginis formatavimas leis ne tik palyginti du skirtingus stulpelius / langelius / eilutes, bet ir paryškinti skirtingus duomenis tam tikra spalva (raudona). Tai yra, mes ieškome ne atsitiktinumų, o skirtumų. Norėdami tai gauti, mes elgiamės taip. Pasirinkite reikiamus stulpelius neliesdami jų pavadinimų, eikite į „pagrindinio“ meniu katalogą, jame raskite poskyrį „stiliai“.
Joje bus eilutė „sąlyginis formatavimas“. Paspaudę ant jo, gausime sąrašą, kur mums reikalinga elemento funkcija „sukurti taisyklę“. Kitas žingsnis: eilutėje "formatas" reikia važiuoti pagal formulę \u003d $ A2<>B2 USD. Ši formulė padės „Excel“ tiksliai suprasti, ko mums reikia, būtent raudonai nuspalvinti visas A stulpelio reikšmes, kurios nėra lygios B stulpelio reikšmėms. Šiek tiek sudėtingesnis formulių taikymo būdas yra susijęs su tokius konstruktus kaip HLOOKUP / VLOOKUP. Šios formulės nurodo horizontalias / vertikalias verčių paieškas. Panagrinėkime šį metodą išsamiau.
HLOOKUP ir VLOOKUP
Šios dvi formulės leidžia ieškoti duomenų horizontaliai / vertikaliai. Tai yra, H yra horizontali, o V - vertikali. Jei palygintini duomenys yra kairiajame stulpelyje, palyginti su tuo, kuriame yra lyginamosios vertės, naudokite konstrukciją VLOOKUP. Bet jei palyginimo duomenys yra horizontaliai lentelės viršuje iš stulpelio, kuriame nurodytos pamatinės vertės, mes naudojame HLOOKUP konstrukciją.
Suprasti, kaip palyginti duomenis dviem „Excel“ stulpeliai vertikaliai, turėtumėte tai naudoti visa formulė: look_value, table_array, col_index_num, range_lookup.
Rasta vertė žymima kaip „look_value“. Paieškos stulpeliai yra valdomi kaip „lentelių masyvas“. Stulpelio numeris turėtų būti nurodytas kaip „col_index_num“. Be to, tai yra stulpelis, kurio vertė sutapo ir kurį reikia grąžinti / pataisyti. Diapazono paieškos komanda čia neprivaloma. Tai gali nurodyti, ar vertė turi būti tiksli, ar apytikslė.
Jei ši komanda nenurodyta, reikšmės bus grąžintos abiem tipams. Visa HLOOKUP formulė atrodo taip: look_value, table_array, row_index_num, range_lookup. Darbas su juo yra beveik identiškas aukščiau aprašytam. Tiesa, yra išimtis. Tai yra eilutės indeksas, apibrėžiantis eilutę, kurios reikšmes reikėtų grąžinti. Sužinojus, kaip aiškiai taikyti visus minėtus metodus, paaiškėja: nėra patogesnės ir universalesnės programos darbui su dideliu kiekiu skirtingų tipų duomenų nei „Excel“. Palyginti du „Excel“ stulpelius yra tik pusė darbo. Juk su gautomis vertybėmis reikia padaryti ką nors kita. Tai yra, rastas rungtynes \u200b\u200bvis tiek reikia kažkaip apdoroti.
Dublikatų verčių tvarkymo metodas
Taigi, pirmajame, tarkime, stulpelyje yra numeriai, visiškai kartojantys antrame stulpelyje. Akivaizdu, kad rankinis pakartojimų taisymas yra nerealus darbas, kuris užima daug brangaus laiko. Todėl automatinei korekcijai turėtumėte naudoti paruoštą techniką.
Kad tai veiktų, pirmiausia turite pavadinti stulpelius, jei jų nėra. Sureguliuokite žymeklį į pirmąją eilutę, dešiniuoju pelės mygtuku spustelėkite, pasirodžiusiame meniu pasirinkite „įklijuoti“. Tarkime, pavadinimai yra „vardas“ ir „dublikatas“. Tada jums reikia katalogo „data“, jame - „filtras“. Spustelėkite mažą rodyklę šalia „dublikato“ ir pašalinkite visus „paukščius“ iš sąrašo. Dabar spustelėkite „gerai“ ir A stulpelio vertės pasikartojančios vertės bus matomos.
Tada mes pasirenkame šias ląsteles, nes mums reikėjo ne tik palyginti „Excel“ dviejų stulpelių tekstą, bet ir pašalinti dublikatus. Mes pasirinkome, dešiniuoju pelės mygtuku spustelėkite, pasirinkome „ištrinti eilutę“, spustelėjome „gerai“ ir gavome lentelę be atitinkančių verčių. Metodas veikia, jei stulpeliai yra tame pačiame puslapyje, tai yra, jie yra greta.
Taigi mes išanalizavome keletą būdų palyginti du „Excel“ failo stulpelius. Tyčia nerodžiau jums ekrano kopijų, nes jose būtumėte sutrikusi.
BET aš paruošiau puikų vaizdo įrašą apie vieną iš populiariausių ir paprastais būdais lygindami du dokumento stulpelius ir dabar siūlau jums su jais susipažinti, kad būtų konsoliduota nagrinėjama medžiaga.
Jei straipsnis vis tiek buvo jums naudingas, pasidalykite juo socialiniuose tinkluose. tinklus arba įvertinkite spustelėdami jums tinkamų žvaigždžių skaičių. Ačiū, viskas už šiandieną, iki pasimatymo.
© Aleksandras Ivanovas.
Gebėjimas palyginti du „Excel“ duomenų rinkinius dažnai yra naudingas žmonėms, apdorojantiems didelius duomenų kiekius ir dirbantiems su didžiulėmis lentelėmis. Pavyzdžiui, palyginimas gali būti naudojamas kaip duomenų įvedimo teisingumas arba duomenų įvedimas į lentelę laiku. Toliau pateiktame straipsnyje aprašomi keli būdai, kaip palyginti du stulpelius su „Excel“ duomenimis.
Naudojant sąlyginį operatorių IF
Sąlyginio operatoriaus IF naudojimo būdas skiriasi tuo, kad dviem stulpeliams palyginti naudojama tik ta dalis, kuri reikalinga palyginimui, o ne visas masyvas. Šio metodo įgyvendinimo žingsniai aprašyti toliau:
Abu palyginamus stulpelius įdėkite į darbalapio A ir B stulpelius.
C2 langelyje įveskite šią formulę \u003d IF (ISERROR (SEARCH (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) ir išplėskite ją iki langelio C11. Ši formulė nuosekliai ieško kiekvieno elemento iš A stulpelio B stulpelio ir pateikia elemento vertę, jei ji yra B stulpelyje.
Naudojant VLOOKUP pakaitos formulę
Formulės principas yra panašus į ankstesnį metodą, skirtumas yra vietoj PAIEŠKA. Išskirtinis šio metodo bruožas taip pat yra galimybė palyginti dvi horizontalias masyvus naudojant HLP formulę.
Norėdami palyginti du stulpelius su duomenimis A ir B stulpeliuose (panašiai kaip ankstesniame metode), į langelį C2 įveskite šią formulę \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) ir vilkite ją į ląstelė C11.
Ši formulė žiūri į kiekvieną elementą iš palyginto masyvo bazinio masyvo ir grąžina jo vertę, jei ji buvo rasta B stulpelyje. Priešingu atveju „Excel“ grąžins klaidą # N / A.
Naudojant „VBA Macro“
Makrokomandų naudojimas dviem stulpeliams palyginti suvienodina procesą ir sutrumpina duomenų paruošimo laiką. Sprendimas, kuris palyginimo rezultatas turėtų būti rodomas, visiškai priklauso nuo jūsų vaizduotės ir įgūdžių naudojant makrokomandas. Žemiau pateikiama technika, paskelbta oficialioje „Microsoft“ svetainėje.
1 | Papildomi radimo_atitikimai () |
Šiame kode „CompareRange“ kintamajam priskiriamas diapazonas su lyginamu masyvu. Tada ji vykdo kilpą, kuri peržengia kiekvieną elementą pasirinktame diapazone ir palygina jį su kiekvienu elementu, esančiu lyginamame diapazone. Jei buvo rasti elementai su tomis pačiomis reikšmėmis, makrokomanda įrašo elemento vertę C stulpelyje.
Norėdami naudoti makrokomandą, grįžkite į darbalapį, pasirinkite pagrindinį diapazoną (mūsų atveju tai yra langeliai A1: A11), paspauskite Alt + F8. Pasirodžiusiame dialogo lange pasirinkite makrokomandą Rasti_Degtukaiir spustelėkite mygtuką vykdyti.
Atlikus makrokomandą, rezultatas turėtų būti toks:
Naudodamiesi „Enquire“ priedu
Rezultatas
Taigi, mes išnagrinėjome kelis duomenų palyginimo „Excel“ būdus, kurie padės išspręsti kai kurias analitines problemas ir padės lengviau rasti pasikartojančias (arba unikalias) reikšmes.
Mes turime dvi užsakymų lenteles, nukopijuotas į vieną darbalapį. Būtina palyginti dviejų „Excel“ lentelių duomenis ir patikrinti, kurios pozicijos yra pirmoje lentelėje, bet ne antroje. Nėra prasmės rankiniu būdu palyginti kiekvienos langelio vertės.
Palyginkite du „Excel“ atitikmenų stulpelius
Kaip palyginti dviejų stulpelių reikšmes programoje „Excel“? Norėdami išspręsti šią problemą, rekomenduojame naudoti sąlyginį formatavimą, kuris gali greitai paryškinti elementus, esančius tik viename spalvos stulpelyje. Užduotis su lentelėmis:
Pirmasis žingsnis - abiejų lentelių priskyrimas vardams. Tai leidžia lengviau suprasti, kokie ląstelių diapazonai yra lyginami:
- Pasirinkite FORMULAS įrankį - Nustatyti vardai - Priskirti vardą.
- Pasirodžiusio lango lauke „Pavadinimas:“ įveskite vertę - Lentelė_1.
- Dešiniuoju pelės mygtuku spustelėkite įrašo lauką „Diapazonas:“ ir pasirinkite diapazoną: A2: A15. Ir spustelėkite Gerai.
Antram sąrašui atlikite tą patį, bet priskirkite pavadinimą - Lentelė_2. Ir nurodykite atitinkamai diapazoną C2: C15.
Naudingi patarimai! Diapazono pavadinimus galima greičiau priskirti naudojant pavadinimo lauką. Jis yra kairėje nuo formulės juostos. Tiesiog pasirinkite langelių diapazonus ir pavadinimo lauke įveskite atitinkamą diapazono pavadinimą ir paspauskite Enter.
Dabar naudokime sąlyginį formatavimą, kad palygintume du „Excel“ sąrašus. Turime gauti tokį rezultatą:
Pozicijos, esančios lentelėje_1, bet ne lentelėje_2, bus rodomos žaliai. Tuo pat metu pozicijos, esančios_2 lentelėje, bet kurių nėra lentelėje_1, bus paryškintos mėlynai.
Dviejų „Excel“ stulpelių duomenų palyginimo principas
Mes naudojome funkciją COUNTIF, kad apibrėžtume stulpelių langelių formatavimo sąlygas. Šiame pavyzdyje ši funkcija patikrina, kiek kartų antrojo argumento (pvz., A2) reikšmė atsiranda pirmojo argumento sąraše (pavyzdžiui, lentelė_2). Jei kartų skaičius \u003d 0, formulė grąžina TRUE. Tokiu atveju langeliui priskiriamas pasirinktinis formatas, nurodytas sąlyginio formatavimo parinktyse. Antrojo argumento nuoroda yra santykinė, o tai reiškia, kad visos pasirinkto diapazono ląstelės bus tikrinamos paeiliui (pavyzdžiui, A2: A15). Antroji formulė veikia panašiai. Tas pats principas gali būti taikomas atliekant įvairias panašias užduotis. Pavyzdžiui, lyginti dvi „Excel“ kainas net