Excel dvojniki

Članek pred vami vsebuje navodila, kako označiti, prikazati in odstraniti Excel podvojene vrednosti, ki jim rečemo tudi Excel dvojniki.

Kazalo

• Označi podvojene celice – Kako barvno označiti posamezne celice, katerih vrednosti so podvojene.
• Označi podvojene vrstice – Kako barvno označiti celotno vrstico, če je podvojen del vrstice ali pa celotna vrstica.
• Prikaži zgolj dvojnike – Kako uporabiti filtre in prikazati zgolj podvojene vrednosti brez enoličnih vrednosti.
• Odstrani dvojnike – Kako odstraniti dvojnike ročno in kako na avtomatiziran način preko funkcionalnosti “Odstrani dvojnike”.

Vse to si bomo pogledali na primeru seznama knjig za osebno rast. Preden pričnemo operirati z dvojniki, pa je dobro vedeti dve stvari.

Kako narediti tabelo v Excelu

Ko želimo označiti, prikazati ali odstraniti dvojnike iz nabora podatkov, je priporočljivo, da teh podatkov v Excel delovni zvezek ne postavimo “prosto”, temveč jih strukturiramo kot tabelo. Če namreč podatki vsebujejo kakšno prazno vrstico, se zna zgoditi, da bodo dvojniki odstranjeni zgolj iz dela podatkov namesto iz vseh podatkov. Tabelo ustvarimo tako, da izberemo celoten nabor podatkov in pritisnemo tipki Ctrl+T.

Varnostna kopija

Preden pričnemo odstranjevati dvojnike, je priporočljivo ustvariti varnostno kopijo lista, iz katerega jih bomo odstranjevali. Na ta način podatke lažje poustvarimo, če se pri odstranjevanju dvojnikov slučajno kaj zalomi. Če gre vse kot zamišljeno, pa varnostno kopijo na koncu preprosto pobrišemo in je to to.

1. Desni klik na ime lista, za katerega želimo ustvariti varnostno kopijo, in nato izberemo Premakni ali kopiraj (Move or copy).

2. Obkljukamo Ustvari kopijo (Create a copy).

3. Po želji dvojni klik na ime lista, da ga ustrezno preimenujemo in tako vemo, da gre za varnostno kopijo, ki jo lahko izbrišemo.

Excel dvojniki – 1. Označi podvojene celice

Podvojene celice je zelo preprosto označiti, saj ima Excel to funkcionalnost že vgrajeno. Ko rečem “podvojene celice”, imam v mislih označevanje oz. obarvanje podvojenih posameznih celic in ne celotnih vrstic. Če torej dve ali več celic vsebuje identično vrednost, bodo obarvane vse, ne glede na to, kje v naboru podatkov se nahajajo.

1. Izberemo celice, katerih podvojenost želimo preveriti.

2. V zavihku Osnovno (Home) > 
skupini Slogi (Styles) > 
kliknemo Pogojno oblikovanje (Conditional formatting) > 
miško pomaknemo nad Pravila za označevanje celic (Highlight cells rules) >
in kliknemo Podvojene vrednosti (Duplicate values).

3. V modalnem oknu po želji spremenimo privzeto oblikovanje in nato kliknemo V redu (OK).

Na ta način barvno označimo vse podvojene celice znotraj izbranega nabora podatkov.

Excel dvojniki – 2. Označi podvojene vrstice

Za označitev podvojenih vrstic Excel na žalost nima vgrajene funkcionalnosti tako kot za označitev podvojenih celic. Namesto tega bomo ustvarili dva dodatna stolpca in eno pravilo pogojnega oblikovanja, ki bo na podlagi ustvarjenih stolpcev označilo podvojene vrstice.

Prvi dodatni stolpec

Ker lahko med seboj primerjamo zgolj vrednosti posameznih celic in ne vrednosti celotnih vrstic, bomo v prvem dodatnem stolpcu združili vrednosti vseh stolpcev, ki sestavljajo vrstice, katerih podvojenost želimo preveriti.

1. Prvi dodatni stolpec poimenujemo recimo “Dvojniki (združeni stolpci)”.

2. V prvo vrstico tega stolpca vpišemo začetek formule:

=CONCATENATE(

Formula CONCATENATE() bo združila vrednosti, ki jih navedemo znotraj oklepajev, v en dolg niz.

3. Še vedno med vpisovanjem formule preklikamo vse stolpce od leve proti desni in vsakič vnesemo podpičje “;”.

Če preklikamo vse stolpce, bomo med seboj primerjali celotne vrstice. Če želimo med seboj primerjati zgolj dele vrstic, preklikamo zgolj želene stolpce in ne vseh. Preklikamo torej tisti del vrstice, za katerega želimo preveriti, ali je podvojen.

4. Še vedno med vpisovanjem formule vpišemo zaklepaj “)” in pritisnemo tipko Enter.

Excel je združil vrednosti vseh stolpcev znotraj ene celice, ki jo lahko zdaj primerjamo z ostalimi. 

5. Če se formula ni avtomatsko razširila po celotnem stolpcu, to naredimo tako, da izberemo celico, ki ima vneseno pravo formulo, in dvojno kliknemo njen desni spodnji kot (Autofill handle). Alternativno primemo taisti desni spodnji kot in ga ročno povlečemo čez celoten stolpec.

Če želimo biti natančni, med vrednosti vstavimo še ločilo.

Namesto formule:

=CONCATENATE(A7;B7;C7;D7;E7)

ki bo vrnila:

Psycho-CyberneticsMaxwell MaltzTarcherPerigee2016Angleščina

uporabimo formulo:

=CONCATENATE(A7;"|";B7;"|";C7;"|";D7;"|";E7)

ki bo vrnila:

Psycho-Cybernetics|Maxwell Maltz|TarcherPerigee|2016|Angleščina

Na ta način se izognemo scenariju, kjer funkcija CONCATENATE vrne enak rezultat za vrstici, ki sicer vsebujeta enako vsebino, a jo imata po stolpcih razporejeno različno.

Primer:

   A         B         C         D
1  ZANI      MI        VO        = ZANIMIVO
2  Z         ANI       MIVO      = ZANIMIVO

Funkcija CONCATENATE za ti dve vrstici vrne isti rezultat, čeprav se vrstici med seboj razlikujeta. Uporaba ločil bi to preprečila:

   A         B         C         D
1  ZANI      MI        VO        = ZANI|MI|VO
2  Z         ANI       MIVO      = Z|ANI|MIVO

Drugi dodatni stolpec

1. Drugi dodatni stolpec poimenujemo recimo “Dvojniki (večji od 1)”.

2. V prvo vrstico tega stolpca vpišemo začetek formule:

=COUNTIF(

Formula COUNTIF() bo preštela, kolikokrat se neka vrednost v prvem dodatnem stolpcu ponovi, tj. ali je vrstica podvojena ali ne.

3. Še vedno med vpisovanjem formule izberemo celotni stolpec “Dvojniki (združeni stolpci)” (na spodnji sliki obarvan modro), vpišemo podpičje “;” in izberemo prvo celico stolpca “Dvojniki (združeni stolpci)” (na spodnji sliki obarvana rdeče).

4. Pritisnemo tipko Enter.

5. Če se formula ni avtomatsko razširila po celotnem stolpcu, to naredimo tako, da izberemo celico, ki ima vneseno pravo formulo, in dvojno kliknemo njen desni spodnji kot (Autofill handle). Alternativno primemo taisti desni spodnji kot in ga ročno povlečemo čez celoten stolpec.

Številka 1 pomeni, da gre za enolično vrstico, tj. vrstico, ki ni podvojena. Številka 2 ali večja pomeni, da gre za podvojeno vrstico, in obenem pove tudi, kolikokrat je ta vrstica podvojena: 2 = dva primerka te vrstice, 3 = trije primerki te vrstice itd.

Pri funkciji COUNTIF moramo biti pozorni, da iskana vrednost ne vsebuje več kot 255 znakov, saj takrat funkcija začne vračati napačne rezultate.

Pravilo pogojnega oblikovanja

Preko pogojnega oblikovanja bomo določili, kako naj Excel obarva oz. oblikuje vrstice, ki so podvojene.

1. Označimo vse vrstice, ki jih želimo primerjati med seboj.

2. V zavihku Osnovno (Home) >
skupini Slogi (Styles) >
kliknemo Pogojno oblikovanje (Conditional formatting) >
kliknemo Novo pravilo (New rule).

3. Izberemo Uporabi formulo za določanje celic za oblikovanje (Use a formula to determine which cells to format) in vnesemo formulo:

=$G2>1

4. Kliknemo Oblika (Format) in določimo, kako naj Excel oblikuje podvojene vrednosti.

5. V obeh modalnih oknih kliknemo V redu (OK).

6. Pravilo pogojnega oblikovanja zgleda tako:

7. Kliknemo V redu (OK) in podvojene vrstice se obarvajo glede na napisano pravilo.

Sedaj imamo odličen pregled nad podvojenimi vrsticami in lahko vse odvečne pobrišemo.

Pobrišemo lahko tudi oba dodatna stolpca in pogojno oblikovanje, če smo prepričani, da jih ne bomo več potrebovali. Če pa gre za preglednico, kjer je vsake toliko potrebno pregledati podatke za podvojene vrednosti in ta dva dodatna stolpca nista vizualno moteča, je najbolje, če jih kar pustimo notri in si tako v prihodnje prihranimo nekaj dela.

Excel dvojniki – 3. Prikaži zgolj dvojnike

1. Če želimo prikazati zgolj podvojene vrednosti, moramo najprej podatke strukturirati kot tabelo in ustvariti oba dodatna stolpca iz poglavja “Označi podvojene vrstice”:

• Dvojniki (združeni stolpci)
• Dvojniki (večji od 1)

2. Filtriramo po stolpcu “Dvojniki (večji od 1)” tako, da kliknemo puščico desno od naslova stolpca, se pomaknemo nad Številski filtri (Number filters) in kliknemo Večje kot (Greater than).

3. V desno zgornje vnosno polje vpišemo “1” in kliknemo V redu (OK).

Zdaj so prikazane zgolj podvojene vrstice. Če jih razporedimo še po abecedi po naslovu knjige, zgledajo tako:

Excel dvojniki – 4. Odstrani dvojnike

Dvojnike lahko odstranimo na dva načina: ročno ali avtomatizirano.

Ročno odstranjevanje dvojnikov

Dvojnike odstranimo ročno tako, da jih najprej najdemo in nato pobrišemo enega po enega. Pri iskanju si lahko pomagamo z obarvanjem, pri brisanju pa s filtriranjem. Oboje sem opisal v prejšnjih poglavjih.

Pri brisanju moramo biti pozorni še na to, da v vsaki skupini dvojnikov ohranimo po en primerek in ne pobrišemo vseh.

Avtomatizirano odstranjevanje dvojnikov

Dvojnike odstranimo avtomatizirano preko funkcionalnosti Odstrani dvojnike (Remove duplicates). Slednje je veliko hitrejše kot ročno odstranjevanje, moramo pa biti previdni pri nastavljanju parametrov, saj lahko hitro odstranimo preveč podatkov.

1. Izberemo nabor podatkov, iz katerega želimo odstraniti dvojnike.

2. V zavihku Podatki (Data) >
skupini Podatkovna orodja (Data tools) >
kliknemo Odstrani dvojnike (Remove duplicates).

3. Obkljukamo stolpce, za katere želimo, da jih Excel upošteva pri primerjanju podvojenosti. Če želimo odstraniti vrstice, kjer se ujemajo vrednosti v vseh stolpcih, obkljukamo vse stolpce. Če želimo odstraniti vrstice, kjer se ujemajo vrednosti samo v enem ali dveh stolpcih, obkljukamo samo te.

4. Če imajo stolpci naslove, obkljukamo Moji podatki imajo glave (My data has headers). V nasprotnem primeru to razkljukamo. Mislim, da Excel ponavadi že sam zazna pravilno izbiro, tako da v večini primerov te nastavitve ni potrebno spreminjati.

5. Kliknemo V redu (OK).

6. Excel nam tedaj sporoči, koliko podvojenih vrednosti je odstranil in koliko enoličnih vrednosti je ostalo.

7. Kliknemo V redu (OK).

Če smo pravilno odstranili podvojene vrstice, bodo v stolpcu “Dvojniki (večji od 1)” zgolj enice.

Pomanjkljivost avtomatskega odstranjevanja dvojnikov je ta, da nam Excel ne pokaže, katere vrstice je odstranil, nam pa preko tega sporočila da vsaj občutek, ali se številka giblje v pričakovanem območju, ali pa nečesa nismo najbolje nastavili in je bilo odstranjenih preveč vrstic. Ravno zato je včasih ročno odstranjevanje bolj zaželeno.

~ ~ ~

S tem smo prišli do konca članka o Excel dvojnikih. Pogledali smo si, kako strukturirati podatke kot tabelo, kako obarvati posamezne podvojene celice, kako dodati stolpce in pogojno oblikovanje, da obarvamo podvojene vrstice, kako prikazati zgolj podvojene vrednosti in kako jih odstraniti ročno ter kako avtomatizirano.

In to je to, tako označujemo, prikazujemo in odstranjujemo podvojene vrednosti ter delamo z Excel dvojniki.

~ ~ ~

Želite boljše tabele?
Za vas izdelam Google Sheets ali Excel tabele po naročilu.