Kaip palyginti du stulpelius programoje „Excel“ ir pašalinti dublikatus (paryškinti, nuspalvinti, perkelti)

Šiam straipsniui perskaityti užtruks apie 10 minučių. Per kitas 5 minutes galėsite lengvai palyginti du stulpelius programoje „Excel“ ir sužinoti, ar juose nėra dublikatų, ištrinti arba paryškinti spalva. Taigi, atėjo laikas!

„Excel“ yra labai galinga ir tikrai šauni programa, skirta dideliems duomenų kiekiams kurti ir apdoroti. Jei turite kelias darbaknyges su duomenimis (arba tik vieną didžiulę lentelę), tikriausiai norėsite palyginti 2 stulpelius, rasti pasikartojančias reikšmes ir ką nors su jomis daryti, pavyzdžiui, ištrinti, paryškinti arba išvalyti turinį . Stulpeliai gali būti toje pačioje lentelėje, būti gretimi arba negretimi, gali būti ant 2 skirtingų lapų ar net skirtingose ​​knygose.

Įsivaizduokite, kad turime 2 stulpelius su žmonių vardais – 5 vardai kiekviename stulpelyje A ir 3 vardai stulpelyje B. Turite palyginti pavadinimus šiuose dviejuose stulpeliuose ir rasti dublikatus. Kaip jūs suprantate, tai yra fiktyvūs duomenys, paimti tik pavyzdžiui. Realiose lentelėse susiduriame su tūkstančiais ar net dešimtimis tūkstančių įrašų.

A variantas: abu stulpeliai yra tame pačiame lape. Pavyzdžiui, stulpelis A ir stulpelis B.

B variantas: Stulpeliai yra ant skirtingų lapų. Pavyzdžiui, stulpelis A ant lapo Sheet2 ir stulpelis A ant lapo Sheet3.

„Excel 2013“, 2010 ir 2007 turi integruotą įrankį Pašalinti dublikatus (Pašalinti dublikatus), tačiau šioje situacijoje jis yra bejėgis, nes negali palyginti duomenų 2 stulpeliuose. Be to, jis gali pašalinti tik dublikatus. Nėra kitų galimybių, tokių kaip paryškinti ar keisti spalvas. Ir taškas!

Toliau parodysiu galimus būdus, kaip palyginti du „Excel“ stulpelius, kurie leis rasti ir pašalinti pasikartojančius įrašus.

• Palyginkite 2 stulpelius ir ieškokite dublikatų naudodami formules
  
  • A variantas: abu stulpeliai yra tame pačiame lape
  • B parinktis: stulpeliai yra skirtinguose lapuose arba skirtingose ​​darbaknygėse
• Rastų dublikatų apdorojimas
  
  • Rodyti tik pasikartojančias eilutes A stulpelyje
  • Pakeiskite spalvą arba paryškinkite rastus dublikatus
  • Pašalinkite dublikatus iš pirmojo stulpelio

Palyginkite 2 stulpelius programoje „Excel“ ir raskite pasikartojančius įrašus naudodami formules

A variantas: abu stulpeliai yra tame pačiame lape

1. Pirmame tuščiame langelyje (mūsų pavyzdyje tai yra langelis C1) rašome šią formulę:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Mūsų formulėje A1 tai yra pirmasis pirmojo stulpelio, kurį ketiname palyginti, langelis. 1 USD B USD и 10000 USD B USD tai antrojo stulpelio pirmos ir paskutinės langelių adresai, su kuriais atliksime palyginimą. Atkreipkite dėmesį į absoliučias nuorodas – prieš stulpelių raides ir eilučių numerius yra dolerio ženklas ($). Naudoju absoliučias nuorodas, kad kopijuojant formules langelių adresai liktų tokie patys.

   Jei norite rasti dublikatus stulpelyje B, pakeiskite nuorodas, kad formulė atrodytų taip:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Vietoj to "Tik"Ir"dublikatas» Galite rašyti savo etiketes, pavyzdžiui, «Nerasta"Ir"Surasta"arba palikite tik"dublikatas“ ir vietoj antrosios reikšmės įveskite tarpo simbolį. Pastaruoju atveju langeliai, kurių dublikatai nerandami, liks tušti, ir, manau, toks duomenų pateikimas yra patogiausias tolesnei analizei.

2. Dabar nukopijuokime formulę į visus stulpelio langelius C, iki pat apatinės eilutės, kurioje yra stulpelio duomenys A. Norėdami tai padaryti, perkelkite pelės žymeklį į apatinį dešinįjį langelio kampą C1, rodyklė bus juodo kryželio pavidalo, kaip parodyta paveikslėlyje žemiau:Spustelėkite ir laikykite nuspaudę kairįjį pelės mygtuką ir vilkite rėmelio kraštą žemyn, pažymėdami visus langelius, į kuriuos norite įterpti formulę. Pasirinkę visus reikiamus langelius, atleiskite pelės mygtuką:

   

Patarimas: Didelėse lentelėse formulės kopijavimas bus greitesnis, jei naudosite sparčiuosius klavišus. Pažymėkite langelį C1 ir paspauskite Ctrl + C (jei norite nukopijuoti formulę į mainų sritį), tada spustelėkite Ctrl + Shift + End (norėdami pažymėti visus netuščius langelius C stulpelyje) ir galiausiai paspauskite Ctrl + V (norėdami įterpti formulę į visas pasirinktas ląsteles).

3. Puiku, dabar visos pasikartojančios reikšmės pažymėtos kaip „dublikatas":

B variantas: du stulpeliai yra skirtinguose lapuose (skirtingose ​​darbaknygėse)

1. Pirmajame darbo lapo tuščio stulpelio pirmajame langelyje Sheet2 (mūsų atveju tai yra B stulpelis) įveskite šią formulę:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Čia Sheet3 yra lapo, kuriame yra 2 stulpelis, pavadinimas ir 1 USD: 10000 USD yra langelių adresai nuo 1 iki paskutinio šiame 2 stulpelyje.

2. Nukopijuokite formulę į visus stulpelio langelius B (tas pats kaip A variantas).
3. Gauname tokį rezultatą:

Rastų dublikatų apdorojimas

Puiku, pirmame stulpelyje radome įrašų, kurie yra ir antrame stulpelyje. Dabar turime ką nors su jais daryti. Rankiniu būdu peržiūrėti visus pasikartojančius lentelės įrašus yra gana neefektyvu ir užima per daug laiko. Yra geresnių būdų.

Rodyti tik pasikartojančias eilutes A stulpelyje

Jei jūsų stulpeliuose nėra antraščių, turite jas pridėti. Norėdami tai padaryti, užveskite žymeklį ant numerio, žyminčio pirmąją eilutę, ir jis pavirs juoda rodykle, kaip parodyta paveikslėlyje žemiau:

Dešiniuoju pelės mygtuku spustelėkite ir kontekstiniame meniu pasirinkite Įterpimas (Įdėti):

Suteikite stulpelių pavadinimus, pavyzdžiui, "Vardas"Ir"Pasikartoti?» Tada atidarykite skirtuką Duomenys (Duomenys) ir paspauskite Filtruoti (Filtras):

Po to spustelėkite mažą pilką rodyklę šalia „Pasikartoti?« norėdami atidaryti filtro meniu; panaikinkite visų šio sąrašo elementų žymėjimą, išskyrus dublikatasir paspauskite OK.

Tai viskas, dabar matote tik tuos stulpelio elementus А, kurie dubliuojami stulpelyje В. Mūsų treniruočių lentelėje yra tik dvi tokios ląstelės, tačiau, kaip suprantate, praktiškai jų bus daug daugiau.

Norėdami vėl rodyti visas stulpelio eilutes А, spustelėkite filtro simbolį stulpelyje В, kuris dabar atrodo kaip piltuvas su maža rodykle, ir pasirinkite Pasirinkti visus (Pasirinkti viską). Arba tą patį galite padaryti per juostelę spustelėdami Duomenys (Duomenys) > Pasirinkite ir filtruokite (Rūšiuoti ir filtruoti) > aiškus (Išvalyti), kaip parodyta toliau esančioje ekrano kopijoje:

Pakeiskite spalvą arba paryškinkite rastus dublikatus

Jei užrašai „dublikatas“ jūsų tikslams nepakanka ir norite pažymėti pasikartojančius langelius kita šrifto spalva, užpildymo spalva ar kitu būdu…

Tokiu atveju filtruokite dublikatus, kaip parodyta aukščiau, pažymėkite visus filtruotus langelius ir spustelėkite Ctrl + 1norėdami atidaryti dialogo langą Langelių formatavimas (ląstelių formatas). Pavyzdžiui, pakeiskime langelių užpildymo spalvą eilutėse su dublikatais į ryškiai geltoną. Žinoma, užpildymo spalvą galite pakeisti naudodami įrankį Pildyti (Užpildymo spalva) skirtuką Pagrindinis (Pagrindinis), bet dialogo lango pranašumas Langelių formatavimas (Ląstelės formatas), nes vienu metu galite konfigūruoti visas formatavimo parinktis.

Dabar tikrai nepraleisite jokių langelių su dublikatais:

Pasikartojančių verčių pašalinimas iš pirmojo stulpelio

Filtruokite lentelę taip, kad būtų rodomi tik langeliai su pasikartojančiomis reikšmėmis, ir pažymėkite tuos langelius.

Jei 2 stulpeliai, kuriuos lyginate, yra skirtinguose lapuose, tai yra, skirtingose ​​lentelėse dešiniuoju pelės mygtuku spustelėkite pasirinktą diapazoną ir pasirinkite Ištrinti eilutę (Pašalinti eilutę):

spauda OKkai „Excel“ paprašys patvirtinti, kad tikrai norite ištrinti visą lapo eilutę, tada išvalykite filtrą. Kaip matote, lieka tik eilutės su unikaliomis reikšmėmis:

Jei tame pačiame lape yra 2 stulpeliai, arti vienas kito (greta) arba ne arti vienas kito (ne greta), tada dublikatų pašalinimo procesas bus šiek tiek sudėtingesnis. Negalime pašalinti visos eilutės su pasikartojančiomis reikšmėmis, nes tai pašalins ir antrojo stulpelio langelius. Taigi stulpelyje palikti tik unikalius įrašus А, daryk tai:

1. Filtruokite lentelę, kad būtų rodomos tik pasikartojančios reikšmės, ir pažymėkite tuos langelius. Dešiniuoju pelės mygtuku spustelėkite juos ir kontekstiniame meniu pasirinkite Aiškus turinys (aiškus turinys).
2. Nuvalykite filtrą.
3. Pasirinkite visus stulpelio langelius А, pradedant nuo ląstelės A1 iki apačios, kurioje yra duomenys.
4. Spauskite Duomenys (Duomenys) ir paspauskite Rūšiuoti nuo A iki Z (Rūšiuoti nuo A iki Z). Atsidariusiame dialogo lange pasirinkite Tęsti dabartinį pasirinkimą (Rūšiuoti pagal nurodytą pasirinkimą) ir spustelėkite mygtuką Juodas (Rūšiavimas):
5. Ištrinkite stulpelį su formule, jums jos nebereikės, nuo šiol turite tik unikalias reikšmes.
6. Štai viskas, dabar stulpelis А yra tik unikalūs duomenys, kurių nėra stulpelyje В:

Kaip matote, pašalinti dublikatus iš dviejų „Excel“ stulpelių naudojant formules nėra taip sunku.