Klientų sąrašas, sujungtas iš kelių failų, prekių kodai iš dviejų eksportų, el. pašto adresai iš kelių renginių registracijų. Visur atsiranda tos pačios eilutės po kelis kartus, o sumos ir skaičiavimai dėl to būna per dideli. Tokios pasikartojančios reikšmės vadinamos dublikatais, kartais ir duplikatais, o Excel jas randa ir pašalina keliais būdais.
Meniu pavadinimus rašau angliškai. Lietuviškame Excel jie išversti, bet yra tose pačiose vietose.
1 būdas: pirmiausia pažiūrėkite, kas kartojasi
Prieš ką nors trinant verta dublikatus pamatyti. Pažymėkite stulpelį ir pasirinkite Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Visos reikšmės, kurios pasikartoja bent du kartus, nusidažys.
Šis būdas nieko nekeičia, todėl jis saugus. Jo trūkumas: jis tikrina tik vieną stulpelį ir nusidažo visi egzemplioriai, ne tik antrasis ir tolesni.
2 būdas: Remove Duplicates, kai reikia ištrinti
Pažymėkite bet kurį langelį lentelėje ir pasirinkite Data → Remove Duplicates. Atsidariusiame lange pažymėkite stulpelius, pagal kuriuos eilutė laikoma pasikartojančia.
Tai svarbiausias sprendimas visame procese:
- Pažymėję tik „El. paštas“ paliksite po vieną eilutę kiekvienam adresui, net jei kitos skiltys skiriasi.
- Pažymėję visus stulpelius pašalinsite tik visiškai vienodas eilutes.
Excel palieka pirmą kiekvienos reikšmės eilutę ir ištrina tolesnes. Jei norite palikti naujausią įrašą, prieš šalindami surikiuokite lentelę pagal datą nuo naujausio. Dublikatų šalinimo įrankyje to daryti nereikia: ten galima tiesiog pasirinkti, kad liktų eilutė su naujausia data.
Pašalintos eilutės dingsta. Anuliuoti galima tik iš karto su Ctrl+Z, todėl prieš tai nukopijuokite lapą. Jei norite matyti, kas buvo pašalinta, įrankis pašalintas eilutes išsaugo atskirame lape.
3 būdas: UNIQUE, kai originalas turi likti
Jei duomenis vis papildote ir sąrašas be pasikartojimų turi atsinaujinti pats, naudokite funkciją UNIQUE:
=UNIQUE(A2:A500)
Formulė į žemiau esančius langelius išveda kiekvieną reikšmę po vieną kartą. Jei nurodysite kelis stulpelius, pavyzdžiui =UNIQUE(A2:C500), gausite unikalias eilučių kombinacijas. Surikiuotą sąrašą gausite apgaubę formulę funkcija SORT: =SORT(UNIQUE(A2:A500)).
UNIQUE yra Microsoft 365, Excel 2021 ir naujesnėse versijose, taip pat Google Sheets.
4 būdas: COUNTIF, kai reikia pažymėti eilutes
Kartais dublikatų negalima tiesiog ištrinti: reikia pažiūrėti, kuris įrašas teisingas, arba perduoti sąrašą kolegai. Tada pridėkite pagalbinį stulpelį.
Kiek kartų reikšmė pasikartoja visame sąraše:
=COUNTIF($A$2:$A$500;A2)
Ar ši eilutė yra pakartojimas, o ne pirmas egzempliorius:
=COUNTIF($A$2:A2;A2)>1
Antroje formulėje intervalo pradžia užfiksuota, o pabaiga slenka kartu su eilute, todėl pirmas egzempliorius gauna FALSE, o visi tolesni TRUE. Tada užtenka filtruoti pagal TRUE ir peržiūrėti tik tas eilutes.
Jei dublikatas nustatomas pagal kelis stulpelius, pavyzdžiui vardą ir įmonę, naudokite COUNTIFS su keliomis sąlygomis:
=COUNTIFS($A$2:A2;A2;$B$2:B2;B2)>1
Remove Duplicates lange taip pat galima pažymėti kelis stulpelius. Tada eilutė laikoma dublikatu tik tada, kai sutampa visi pažymėti stulpeliai.
Jei norite dublikatus tik pamatyti, o ne žymėti stulpelyje, juos galima nuspalvinti sąlyginiu formatavimu. Kaip nuspalvinti visą eilutę pagal formulę, aprašiau straipsnyje sąlyginis formatavimas Excel.
Kodėl dublikatų Excel „neranda“
Dažniausiai dvi reikšmės žmogui atrodo vienodos, bet Excel jos skiriasi:
- Tarpai. „UAB Medis“ ir „UAB Medis “ su tarpu gale yra skirtingos reikšmės. Pagalbiniame stulpelyje
=TRIM(A2)pašalins tarpus pradžioje, gale ir dvigubus tarpus viduryje. - Skaičius ir tekstas. Kodas 1045, išsaugotas kaip skaičius, ir „1045“, išsaugotas kaip tekstas, nesutampa. Kaip tai atpažinti, aprašiau straipsnyje kodėl Excel nesumuoja skaičių ir neatpažįsta datų.
- Skirtinga rašyba. „UAB Medis“ ir „Medis, UAB“ Excel niekada nesujungs. Čia padeda tik bendras raktas, pavyzdžiui įmonės kodas.
Didžiosios ir mažosios raidės Remove Duplicates, COUNTIF ir UNIQUE nesvarbios. Power Query yra kitaip: ten „vilnius“ ir „Vilnius“ laikomi skirtingomis reikšmėmis, todėl prieš šalinant dublikatus verta tekstą suvienodinti.
Google Sheets
Google Sheets dublikatus šalina Data → Data cleanup → Remove duplicates. Ten pat yra Trim whitespace, kuris pašalina perteklinius tarpus. Formulės UNIQUE, COUNTIF ir COUNTIFS veikia taip pat kaip Excel, o dublikatus nuspalvinti galima per Format → Conditional formatting su formule =COUNTIF($A:$A;$A2)>1.
Kai dublikatai atsiranda kas savaitę
Jei tą patį sąrašą valote reguliariai, nes jis kaskart sujungiamas iš kelių failų, geriau tvarkyti ne pasekmę, o patį sujungimą. Power Query gali sujungti failus, pašalinti tarpus ir dublikatus vienu atnaujinimu. Kaip tai padaryti, rašiau straipsnyje kaip sujungti kelis Excel failus į vieną lentelę.