Reklama portale

Kaip pašalinti dublikatus „Excel“ - visi galimi metodai. „Microsoft Excel“ dublikatų radimas ir pašalinimas „Excel“ eilučių su tomis pačiomis reikšmėmis pašalinimas

Šiame straipsnyje mes apžvelgsime kaip pašalinti dublikatus„Excel 2007“, „Excel 2010“, „Excel 2013“ ir „Excel 2016“. Sužinosite kelis skirtingus būdus, kaip rasti ir pašalinti dublikatus programoje „Excel“. su pirmaisiais pasireiškimais arba be jų, pašalinti pasikartojančias eilutes, Kaip pašalinti dublikatus stulpelyje, kaip aptikti absoliučius dublikatus ir dalinius atitikmenis. Straipsnyje aptariami šie punktai:

Pašalinkite pasikartojančias eilutes iš „Excel“ naudodami funkciją Pašalinti pasikartojančius elementus

Jei naudojate naujausias „Excel 2007“, „Excel 2010“, „Excel 2013“ arba „Excel 2016“ versijas, turite pranašumą, nes šiose versijose yra integruota funkcija, skirta rasti ir pašalinti dublikatus – Pašalinti dublikatus funkcija.

Ši funkcija leidžia rasti ir pašalinti absoliučius dublikatus (ląsteles arba ištisas eilutes), taip pat iš dalies atitinkančius įrašus (eilutes, kurios turi tokias pačias reikšmes nurodytame stulpelyje ar stulpeliuose). Pažvelkime į pavyzdį, kaip jį naudoti žingsnis po žingsnio Pašalinkite „Excel“ dublikatų funkciją.

Pastaba: Nuo funkcijos Pašalinkite dublikatus visam laikui ištrina identiškus įrašus, prieš ištrinant pasikartojančias eilutes, rekomenduojama sukurti pirminių duomenų kopiją.

  1. Norėdami pradėti, pasirinkite norimą diapazoną pašalinti dublikatus. Norėdami pasirinkti visą lentelę, paspauskite Ctrl+A.
  2. Tada eikite į skirtuką „DUOMENYS“ --> grupę „ Darbas su duomenimis“ ir spustelėkite „ Pašalinkite dublikatus».
Pašalinti dublikatus programoje „Excel“ – „Excel“ dublikatų pašalinimo funkcija
  1. Dialogo langas " Pašalinkite dublikatus“ Pasirinkite stulpelius, kad patikrintumėte, ar nėra dublikatų, ir spustelėkite Gerai.
  • Į pašalinti pasikartojančias eilutes Turėdami lygiai tokias pačias reikšmes visuose stulpeliuose, palikite žymimuosius laukelius šalia visų stulpelių, kaip parodyta paveikslėlyje žemiau.
  • Į pašalinti dalinius dublikatus atsižvelgdami į vieną ar daugiau pagrindinių stulpelių, pasirinkite tik atitinkamus stulpelius. Jei lentelėje yra daug stulpelių, geriausia pirmiausia spustelėti mygtuką „Atšaukti pasirinkimą“, o tada pasirinkti stulpelius, kuriuose norite patikrinti, ar jie nepasikartoja.
  • Jei jūsų lentelėje nėra antraščių, pašalinkite vėliavėlę iš „ Mano duomenyse yra antraštės“ viršutiniame dešiniajame dialogo lango kampe, kuris paprastai pasirenkamas pagal numatytuosius nustatymus.

Pašalinti dublikatus programoje „Excel“ – pasirinkite stulpelį (-ius), kuriame (-iuose) norite patikrinti, ar nėra dublikatų

Pasiruošę! Visi pasikartojančios eilutės Ištrinami pasirinktame diapazone ir rodomas pranešimas, nurodantis, kiek jų buvo dublikatai pašalinti įrašus ir kiek liko unikalių verčių.

Pašalinti dublikatus programoje „Excel“ – pranešimas apie tai, kiek dublikatų buvo pašalinta

Pašalinkite „Excel“ dublikatų funkciją pašalina antrąjį ir visus vėlesnius pasikartojančius egzempliorius, palikdamas visas unikalias eilutes ir pirmuosius identiškų įrašų egzempliorius. Jeigu nori pašalinti pasikartojančias eilutes, įskaitant pirmuosius pasireiškimus, t.y. jei norite ištrinti visus pasikartojančius langelius. Arba kitu atveju, jei yra dvi ar daugiau pasikartojančių eilučių ir norite pasilikti pirmąją ir visas paskesnes pašalinti dublikatus , tada naudokite vieną iš toliau aprašytų sprendimų.

Pašalinkite dublikatus nukopijuodami unikalius įrašus į kitą vietą

Kitas būdas pašalinti dublikatus iš „Excel“.- Tai yra unikalių reikšmių atskyrimas ir jų nukopijavimas į kitą lapą arba pasirinktą diapazoną esamame lape. Pažvelkime į šį metodą.

  1. Pasirinkite norimą diapazoną arba visą lentelę pašalinti dublikatus .
  2. Eikite į skirtuką „DUOMENYS“ --> grupę „ Rūšiavimas ir filtravimas“ ir spustelėkite mygtuką „Išplėstinė“.

Pašalinkite dublikatus programoje „Excel“ – naudodami išplėstinį filtrą dublikatams pašalinti
  1. Dialogo lange " Išplėstinis filtras» atlikite šiuos veiksmus:
  • pasirinkite " nukopijuokite rezultatą į kitą vietą».
  • Patikrinkite, ar rodomas tinkamas diapazonas Originalus asortimentas. Tai turėtų būti diapazonas, kurį pasirinkote atlikdami 1 veiksmą.
  • Lauke Įdėkite rezultatą į diapazonąįveskite diapazoną, kuriame norite kopijuoti unikalias reikšmes (tiesą sakant, tiesiog pasirinkite viršutinį kairįjį paskirties diapazono langelį).
  • Pasirinkite Tik unikalūs įrašai

Pašalinkite dublikatus programoje „Excel“ – dublikatų filtras
  1. Galiausiai spustelėkite „Gerai“. „Excel“ pašalins dublikatus ir nukopijuos unikalias reikšmes į naują nurodytą vietą:

Pašalinkite dublikatus programoje „Excel“ – unikalūs įrašai, nukopijuoti iš kitos vietos

Tokiu būdu jūs gaunate naujus duomenis pagal nurodytus, bet su pašalinti dublikatus .

Atminkite, kad išplėstinis filtras leidžia tik nukopijuoti filtruotas reikšmes į kitą aktyvaus darbalapio vietą.

Pašalinkite pasikartojančias eilutes iš „Excel“ naudodami formules ir filtrą

Kitas būdas pašalinti dublikatus iš „Excel“.- yra apibrėžti juos naudojant formulę, filtrą ir pašalinti pasikartojančias eilutes.

Šio požiūrio privalumas yra jo universalumas – jis leidžia rasti ir pašalinti dublikatus stulpelyje arba dubliuoti eilutes pagal reikšmes keliuose stulpeliuose. Neigiama yra tai, kad reikės įsiminti kelias pasikartojančias formules.

  1. Atsižvelgdami į užduotį, naudokite vieną iš toliau pateiktų formulių, kad rastumėte dublikatus.

Formulės, kaip rasti dublikatus 1 stulpelyje

  • Pasikartojantys, išskyrus 1-ąjį atvejį:

IF(COUNTIF($A$2:$A2, $A2)>1, "dublikatas"; "")

  • Pasikartojimai iš pirmo karto:

IF(COUNTIF($A$2:$A$10, $A2)>1, "dublikatas", "unikalus")

Kur langelis A2 yra pirmasis, o A10 yra paskutinis diapazono langelis, kuriame galima rasti dublikatus.

Formulės, kaip rasti pasikartojančias eilutes

  • Pasikartojančios eilutės, išskyrus 1-ąjį atvejį. Tai yra, jei rezultate yra dvi ar daugiau identiškų eilučių, pirmoji iš jų bus pažymėta kaip unikali, o visos vėlesnės kaip dublikatai:

IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1"Dubliuoti eilutę","Unikalūs")

  • Pasikartojančios eilutės su 1 įvykiu. Tokiu atveju, jei paieškos rezultate yra dvi ar daugiau identiškų eilučių, jos visos bus pažymėtos kaip dublikatai:

IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2)>1, "Dubliuoti eilutę"; "unikali")

Kur A, B ir C yra stulpeliai, kuriuos reikia patikrinti, ar nėra dublikatų.

Pavyzdžiui, taip galite nustatyti pasikartojančios eilutės, išskyrus pirmąjį atvejį:

Pašalinkite dublikatus programoje „Excel“ – formulė, kad nustatytų pasikartojančias eilutes, išskyrus pirmuosius atvejus
  1. Pasirinkite bet kurį lentelės langelį ir pritaikykite automatinį filtrą spustelėdami mygtuką „Filtras“ skirtuke „DUOMENYS“ arba „ Rūšiavimas ir filtravimas" --> "Filtras" skirtuke "PADŽIA".

Pašalinkite dublikatus programoje „Excel“ – pritaikykite filtrą langeliams (skirtukas DUOMENYS)
  1. Filtras pasikartojančios eilutės spustelėdami rodyklę stulpelio antraštėje " Pasikartoja“, tada pažymėkite žymimąjį laukelį „Dubliuoti eilutę“.

Pašalinkite dublikatus programoje „Excel“ – dublikatų eilutės filtras
  1. Ir, galiausiai pašalinti pasikartojančias eilutes. Norėdami tai padaryti, pažymėkite filtruojamas eilutes, perkeldami pelės žymeklį ant eilučių numerių, dešiniuoju pelės mygtuku spustelėkite jas ir kontekstiniame meniu pasirinkite „Ištrinti eilutę“. Šiuo atveju už pašalinti dublikatus Neturėtume naudoti klaviatūros klavišo „Ištrinti“, nes turime ištrinti visas eilutes, o ne tik langelių turinį:

Pašalinkite dublikatus programoje „Excel“ – filtruokite ir pašalinkite pasikartojančias eilutes

Na, dabar jūs išmokote keletą būdų, kaip tai padaryti pašalinti dublikatus iš „Excel“.. Ir jūs galite naudoti vieną iš jų, priklausomai nuo jūsų situacijos.

Dirbant su duomenimis, atsiranda poreikis periodiškai išgauti unikalius įrašus. Norėdami tai padaryti, „Excel“ vartotojas turi daug būdų, kaip pašalinti pasikartojančias reikšmes ir filtruoti su galimybe paslėpti arba rodyti. Kiekvienas metodas gali būti naudojamas kasdieniame darbe arba apdorojant sudėtingus statistinius duomenis. Šiuo atveju specialių žinių nereikia, pakanka minimalių žinių apie Excel skaičiuoklių procesorių.

Kaip pašalinti dublikatus iš „Excel“.

Norėdami greitai išspręsti šią problemą, vartotojas turi prieigą prie standartinės funkcijos “ Dublikatų pašalinimas“ Tokiu atveju operaciją galima atlikti tik pasirinktuose laukuose.

Norėdami pašalinti pasikartojančias eilutes, turite:

Dublikatų pašalinimas išmaniojoje lentelėje

Šis metodas naudoja „Excel Designer“ (išplėstinis režimas su papildomomis funkcijomis). Dizaineris suaktyvinamas, kai pasirenkamas bet kuris lentelės srities langelis. Kaip ir pirmuoju atveju, pasikartojančios vertės pašalinamos pritaikius.

Nežinantiems: išmanioji lentelė yra tam tikras formatavimo tipas, po kurio visi lentelės langeliai įgauna tam tikras savybes. Tuo pačiu metu „Excel“ tokią lentelę laiko viena visuma, o ne langelių ir elementų rinkiniu.

Norėdami pašalinti pasikartojančias tokios lentelės eilutes, turėtumėte:


Mes naudojame filtravimą

Naudodami filtravimą galite paslėpti pasikartojančius duomenis, tačiau reikšmės neištrinamos – jas galima bet kada grąžinti.

Norėdami formatuoti ir rasti pasikartojančias reikšmes tokiu būdu:


Sąlyginis formatavimas

Šį metodą galite naudoti norėdami ieškoti identiškų įrašų ląstelėse. Patogumui galite tinkinti paryškinimo spalvą. Norėdami tai įgyvendinti, turite taikyti iš anksto nustatytą atrankos taisyklę - " Pasikartojančios vertės...».

Norėdami rasti dublikatus programoje „Excel“, turite:


Naudojant formulę

Naudojant integruotą funkciją " PAIEŠKA» galite rasti pasikartojančius elementus pasirinktame diapazone. „MATCH“ – pateikia santykinę vietą elemento masyve, atitinkantį nurodytą reikšmę, atsižvelgiant į nurodytą tvarką.

Norėdami paryškinti pasikartojančias eilutes programoje „Excel“, turite:


Paaiškinimas: Formulėje nurodyta funkcija „MATCH“, kuri „B“ stulpelyje ieško pakartojimų, pradedant nuo langelio „B3“.


Nukopijuokite unikalias eilutes į naują vietą

Norėdami tai užbaigti, jums reikės:


Suvestinių lentelių naudojimas

Norėdami sugrupuoti pasikartojančius elementus, galite naudoti suvestinę lentelę (paprastesnė sąvoka apibendrinta).

Kad šis metodas veiktų, jums reikės:


Be to, galite žiūrėti vaizdo įrašą apie dublikatų pašalinimą programoje „Excel“.

Pasikartojančių kopijų pašalinimas „Google“ skaičiuoklėse

„Google“ galite ieškoti tik unikalių įrašų langeliuose (nėra jokių dublikatų pašalinimo metodų):

  • naudojant sukamosios lentelės(skaičiuojant pasikartojančias reikšmes);
  • naudojant funkcijasUnikalus(išvesti rezultatą iš masyvo);
  • naudojant trečiųjų šalių plėtiniai;

Funkcijos "" naudojimo pavyzdys Unikalus»:

Norėdami rodyti unikalius įrašus, turite taikyti formulę «= unikalus (patikrinkite diapazoną)»:

Dažnas klausimas yra, kaip rasti ir pašalinti dublikatus programoje „Excel“. Tarkime, kad atsisiuntėte mėnesinę ataskaitą iš savo apskaitos sistemos, bet galiausiai turite suprasti, kurios sandorio šalys apskritai bendravo su įmone per šį laikotarpį – be pasikartojimo sudarykite sandorio šalių sąrašą. Kaip išsirinkti unikalias vertybes?

Ar galima pašalinti dvigubas, trigubas ir kt. reikšmes „Excel“ keliuose stulpeliuose?

Tai įmanoma, ir labai paprasta. Tam yra speciali funkcija. Iš anksto pasirinkite diapazoną, kuriame norite pašalinti dublikatus. Juostelėje eikite į Duomenys – Pašalinkite dublikatus (žr. paveikslėlį straipsnio pradžioje).

Pirmojo stulpelio pasirinkimas

Svarbu suprasti, kad jei pasirinksite tik pirmąjį stulpelį, visi nepasirinktų stulpelių duomenys bus ištrinti, jei jie nėra unikalūs.

Labai patogiai!

2. Kaip pasirinkti visus dublikatus programoje Excel?

Ar jau girdėjote apie? Tai padės ir čia! Pasirinkite stulpelį, kuriame norite pažymėti dublikatus, pasirinkite iš meniu Pagrindinis - Sąlyginis formatavimas - Ląstelių paryškinimo taisyklės - Pasikartojančios reikšmės...

Atsidariusiame lange Pasikartojančios reikšmės pasirinkite, kuriuos langelius pasirinkti (unikalius ar pasikartojančius), taip pat pasirinkimo formatą iš siūlomų arba sukurkite tinkintą formatą. Numatytasis formatas bus raudonas užpildymas ir raudonas tekstas.

Spustelėkite Gerai, jei nenorite keisti formatavimo. Dabar visi pasirinktų sąlygų duomenys bus nuspalvinti.

Pastebiu, kad įrankis taikomas tik pasirinktam vienam (!) stulpeliui.

Beje, jei jums reikia pamatyti unikalius, tada kairėje esančiame lange pasirinkite - unikalus.

3. Unikalios reikšmės naudojant suvestinės lenteles

Tiesą sakant, aš kažkada neįtariau, kad egzistuoja galimybė „pašalinti dublikatus“ ir naudojau suvestinės lenteles. Kaip aš tai padariau? Pasirinkite lentelę, kurioje turite rasti unikalias reikšmes - Įterpti -

Kai iškyla užduotis optimizuoti duomenų bazę arba pasikeičia jos struktūra, kartais iškyla susijusi užduotis sutvarkyti jau sukauptus duomenis. Gerai, jei kūrimo metu lentelė jau yra normalios formos, o visa sistema sutvarkyta taip, kad nesikauptų nereikalinga pasikartojanti informacija. Jei taip nėra, tuomet baigiant kurti tokią sistemą norisi atsikratyti visų perteklinių duomenų ir viską atlikti kuo kokybiškiau.

Šiame straipsnyje aptarsime duomenų bazės lentelės pasikartojančių eilučių pašalinimo užduotį. Iš karto norėčiau pažymėti, kad mes kalbame apie poreikį pašalinti pasikartojančias eilutes. Pavyzdžiui, užsakymų lentelės įrašai su laukeliais „užsakymo kodas“, „prekės kodas“, „kliento kodas“, „užsakymo data“ gali skirtis tik užsakymo kodu, nes vienas klientas tą pačią prekę gali užsisakyti kelis kartus. tą pačią dieną vieną kartą. Ir pagrindinis rodiklis, kad viskas yra teisinga, yra rakto lauko buvimas.

Jei matome lentelę, pilną pasikartojančių laukų, kuriose nėra aiškaus kiekvieno įrašo poreikio, tai yra būtent tai, ką reikia pataisyti.

Aiškiai perteklinės lentelės pavyzdys:

Dabar pažiūrėkime, kaip galime išspręsti šią problemą. Čia gali būti taikomi keli metodai.


1. Galite parašyti funkciją, kad palygintumėte ir kartotumėte visus duomenis. Tai užtrunka ilgai, ir jūs ne visada norite parašyti kodą vienkartiniam naudojimui.


2. Kitas sprendimas yra sukurti pasirinkimo užklausą, kuri sugrupuoja duomenis taip, kad būtų grąžinamos tik unikalios eilutės:

PASIRINKITE šalies_id, miesto_pavadinimą
IŠ mytable
GROUP BY šalies_id, miesto_pavadinimas

Gauname tokį pavyzdį:

Tada gautą duomenų rinkinį įrašome į kitą lentelę.


3. Šie sprendimai naudoja papildomą programos kodą arba papildomas lenteles. Tačiau patogiau būtų viską daryti naudojant tik SQL užklausas be papildomų lentelių. Ir štai tokio sprendimo pavyzdys:

IŠTRINTI a.* IŠ mytable a,
(PASIRINKITE

IŠ mano lentelės b

)c
KUR
a.country_id = c.country_id
IR a.miesto_pavadinimas = c.miesto_pavadinimas
IR a.id > c.mid

Atlikus tokią užklausą, lentelėje liks tik unikalūs įrašai:

Dabar pažvelkime atidžiau, kaip visa tai veikia. Prašydami ištrinti, turite nustatyti sąlygą, kuri nurodys, kuriuos duomenis reikia ištrinti, o kuriuos palikti. Turime pašalinti visus neunikalius įrašus. Tie. jei yra keli vienodi įrašai (jie yra vienodi, jei turi vienodas šalies_id ir miesto_pavadinimo reikšmes), tuomet reikia paimti vieną iš eilučių, prisiminti jos kodą ir ištrinti visus įrašus su tomis pačiomis šalies_id ir miesto_pavadinimo reikšmėmis, bet skirtingu kodu (id).

SQL užklausos eilutė:

IŠTRINTI a.* IŠ mytable a,

rodo, kad bus ištrinta iš mytable lentelės.

Tada pasirinkimo užklausa sugeneruoja pagalbinę lentelę, kurioje sugrupuojame įrašus taip, kad visi įrašai būtų unikalūs:

(PASIRINKITE
b.šalies_id, b.miesto_pavadinimas, MIN(b.id) vid
IŠ mano lentelės b
GROUP BY b.country_id, b.city_name
)c

MIN(b.id) mid – sudaro stulpelį mid (santrumpa min id), kuriame yra minimali id ​​reikšmė kiekviename pogrupyje.

Rezultatas yra lentelė, kurioje yra unikalių įrašų ir kiekvienos pasikartojančių įrašų grupės pirmosios eilutės ID.

Dabar turime du stalus. Vienas bendras, kuriame yra visi įrašai. Iš jo bus pašalintos papildomos eilutės. Antrajame yra informacija apie eilutes, kurias reikia išsaugoti.

Belieka sukurti sąlygą, kurioje būtų nurodyta: reikia ištrinti visas eilutes, kuriose sutampa laukai country_id ir city_name, bet id nesutaps. Tokiu atveju pasirenkama minimali id ​​reikšmė, todėl visi įrašai, kurių id didesnis nei pasirinktas laikinojoje lentelėje, yra ištrinami.


Taip pat verta paminėti, kad aprašytą operaciją galima atlikti, jei lentelėje yra rakto laukas. Jei staiga aptikote lentelę be unikalaus identifikatoriaus, tiesiog pridėkite ją:

ALTER TABLE "mytable" PRIDĖTI "id" INT(11) NOT NULL AUTO_INCREMENT , PRIDĖTI PIRMINĮ RAKTĄ ("id")

Vykdydami tokią užklausą gauname papildomą stulpelį, užpildytą unikaliomis skaitinėmis reikšmėmis kiekvienai lentelės eilutei.

Atliekame visus reikiamus veiksmus. Atlikus pasikartojančių įrašų lentelės išvalymo operaciją, šį lauką taip pat galima ištrinti.

Ilgą laiką „Excel“ pageidavimų sąrašo elementas buvo galimybė automatiškai pašalinti pasikartojančias eilutes iš darbalapio. Ir dabar, pradedant „Excel 2007“, „Microsoft“ kūrėjai išpildė šį norą.

Fig. 33.1 paveiksle parodytas duomenų diapazonas po to, kai jie buvo konvertuoti į lentelę naudojant komandą Lentelės įterpimas Lentelė. Atminkite, kad šioje lentelėje yra keletas pasikartojančių eilučių.

Pradėkite pasirinkdami bet kurį langelį lentelėje. Tada paleiskite komandą Konstruktorių paslauga Pašalinkite dublikatus. „Excel“ parodys dialogo langą, parodytą paveikslėlyje. 33.2. Čia pateikiami visi lentelės stulpeliai. Pažymėkite stulpelių, kuriuos norite įtraukti į pasikartojančią paiešką, žymės langelius. Daugeliu atvejų reikia pažymėti visus stulpelius – tai reiškia, kad dublikatas bus apibrėžtas kaip turintis tuos pačius duomenis kiekviename stulpelyje.

Jei jūsų duomenys neįvedami kaip lentelė, naudokite komandą Duomenys Darbas su duomenimis Pašalinkite dublikatus kad būtų rodomas to paties pavadinimo dialogo langas.

Supraskite, kad pasikartojančias reikšmes lemia langelyje rodoma reikšmė, o nebūtinai langelyje saugoma reikšmė. Tarkime, dviejuose langeliuose yra ta pati data: viena data suformatuota kaip 2010-05-15, o kita – 2010 m. gegužės 15 d. Kai pašalinsite pasikartojančius duomenis, „Excel“ manys, kad šios eilutės skiriasi.

Spustelėkite mygtuką Gerai, o „Excel“ išfiltruos pasikartojančias eilutes ir parodys pranešimą, nurodantį, kiek dublikatų pašalino (33.3 pav.).