Excel funkcija VLOOKUP

Excel funkcija VLOOKUP - 1. razlaga v sliki

Excel funkcija VLOOKUP je ena najbolj uporabnih Excel funkcij. Z njo lahko pridobimo točno določene podatke iz enega konca Excel datoteke in jih prikažemo na drugem koncu. Na ta način si lahko sestavimo najrazličnejša poročila, lažje analiziramo podatke, ali pa jih povsem preoblikujemo in si sestavimo recimo nadzorno ploščo, ki nam pride prav pri vsakodnevnem delu.

V tem članku si bomo pogledali primer pošiljanja ponudb potencialnim strankam, katerih podatke imamo shranjene v Excel datoteki, ki ima dva lista. Prvi list vsebuje splošne podatke potencialnih strank (med njimi tudi e-poštni naslov), drugi list pa prodajne priložnosti, ki jim manjka ravno e-poštni naslov.

Excel funkcija VLOOKUP - 2. list s kontakti
Excel funkcija VLOOKUP - 3. list s priložnostimi

Tekom pošiljanja ponudb bomo morali torej odpreti drugi list, da izvemo, kaj moramo za stranko narediti in nato prvi list, da dobimo njen email. Enako za drugo stranko, in tretjo, in četrto… skratka nenehno preklapljanje med obema listoma.

Tega si zagotovo ne želimo in bomo raje uporabili funkcijo VLOOKUP, ki bo strankin email našla v prvem listu in ga izpisala ob ustrezni prodajni priložnosti v drugem listu. 👈 Ful boljš!

Preden pa gremo na ta primer, si poglejmo, kako je VLOOKUP funkcija sestavljena.

Excel funkcija VLOOKUP - 1. razlaga v sliki
=VLOOKUP(iskana_vrednost; obseg_iskanja; številka_stolpca; [način_iskanja])
  • iskana_vrednost: Vrednost, ki jo želimo poiskati.
  • obseg_iskanja: Obseg celic, kjer bomo iskali. Prvi stolpec mora biti tisti, ki vsebuje iskano vrednost. Obseg mora obenem vsebovati tudi stolpec, katerega vrednost želimo prikazati, v kolikor funkcija VLOOKUP najde iskano vrednost.
  • številka_stolpca: Številka stolpca, katerega vrednost želimo prikazati.
  • način_iskanja: Neobvezno. Pove, ali želimo izvesti natančno ujemanje (FALSE) ali približno ujemanje (TRUE ali izpuščeno). V veliki večini primerov želimo izvesti natančno ujemanje (FALSE).

Nekaj primerov formul:

=VLOOKUP(A2;$A$2:$C;3;FALSE)
=VLOOKUP("Banana";B2:E7;1;FALSE)
=VLOOKUP($G$2;Seznam!$A:$F;2;FALSE)

Še to:

  • Funkcija VLOOKUP vrne natanko en rezultat. Če se torej iskana vrednost pojavi večkrat, bo funkcija VLOOKUP vrnila prvo, ki jo najde, in to ta zgornjo, saj iskanje poteka od zgoraj navzdol.
  • Posledično je najbolje, če funkcijo VLOOKUP uporabimo na nizu unikatnih vrednostih tj. vrednosti, kjer se vsaka pojavi natanko enkrat, saj smo edino tako lahko prepričani, da bomo vsakič dobili pravi rezultat.
  • Če iskana vrednost ni najdena, bo funkcija VLOOKUP vrnila #N/A.

Postopek

V drugi list dodamo stolpec, kjer želimo videti emaile potencialnih strank. Z desnim gumbom miške kliknemo stolpec D, ga tako označimo in nato izberemo Vstavi (Insert).

Excel funkcija VLOOKUP - 4. vstavi stolpec

Novemu stolpcu damo naslov “Email”.

Excel funkcija VLOOKUP - 5. novi stolpec

V celico D2 vnesemo naslednjo formulo:

=VLOOKUP(E2;Kontakti!$A:$D;4;FALSE)
  • iskana_vrednost = E2
    E-poštne naslove strank bomo najlažje našli preko identifikacijskih številk strank, saj so te unikatne in nahajajo se v obeh listih.
  • obseg_iskanja = Kontakti!$A:$D
    Obseg iskanja obsega celotne stolpce A, B, C in D v listu “Kontakti”. Iskana vrednost se nahaja v prvem stolpcu (A), prikazana vrednost pa v četrtem stolpcu (D), kar določimo z argumentom številka_stolpca. Obseg napišemo z absolutno referenco, se pravi “Kontakti!$A:$D” in ne “Kontakti!A:D”, saj bomo drugače formulo v naslednjem koraku pokvarili.
  • številka_stolpca = 4
    Ko funkcija VLOOKUP najde identifikacijsko številko stranke, prikaže vrednost iz četrtega stolpca.
  • način_iskanja = FALSE
    Želimo natančno ujemanje (FALSE) identifikacijskih številk in ne približnega ujemanja (TRUE).

Izberemo celico, kjer imamo pripravljeno formulo s funkcijo VLOOKUP, primemo njen desni spodnji rob oz. tako imenovano zapolnitveno ročico in jo povlečemo čez vse celice, kamor želimo vnesti to formulo. Alternativno lahko zapolnitveno ročico kliknemo dvakrat.

Excel funkcija VLOOKUP - 6. zapolnitvena ročica

Če v tem koraku ne bi imeli formule spisane z absolutnimi referencami (dolar znaki ala $A:$D), bi dobili pomanjkljive rezultate. To lahko preizkusite na lastnih podatkih: Več vrstic kot imate, bolj očitne bodo napake oz. več bo nenajdenih vrednosti.

VLOOKUP formule na koncu izgledajo takole:

Excel funkcija VLOOKUP - 7. VLOOKUP formule

Podatki pa takole:

Excel funkcija VLOOKUP - 8. dodani emaili

Zdaj bomo lažje pošiljali ponudbe našim potencialnim strankam, saj imamo e-poštne naslove točno tam, kjer jih potrebujemo in nam posledično ne bo potrebno nenehno preklapljati med obema listoma.

In to je to, zdaj znamo uporabljati Excel funkcijo VLOOKUP. 🎉

~ ~ ~

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

Excel vodiči:
Dvojniki · Filtriranje · Pogojno oblikovanje · Sortiranje · Spustni seznam · VLOOKUP

Excel dvojniki

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

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”.

Slednje si bomo pogledali na primeru spiska 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 vseh podatkov. Tabelo ustvarimo tako, da izberemo celoten nabor podatkov in pritisnemo tipki Ctrl+T.

Excel dvojniki - 1. ustvari tabelo

Varnostna kopija

Preden pričnemo odstranjevati dvojnike, je priporočljivo ustvariti varnostno kopijo zavihka, 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 zavihek, kateremu želiš ustvariti varnostno kopijo, in nato izbereš Premakni ali kopiraj (Move or copy).

Excel dvojniki - 2. premakni ali kopiraj

2. Obkljukaš Ustvari kopijo (Create a copy).

Excel dvojniki - 3. premakni ali kopiraj list

3. Potencialno dvojni klik na zavihek, da ga ustrezno preimenuješ in tako veš, da gre za varnostno kopijo, ki jo lahko izbrišeš.

Excel dvojniki - 4. varnostna kopija lista

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 vsebujejo identično vrednost, bodo obarvane vse, ne glede na to, kje v naboru podatkov se nahajajo.

1. Izbereš celice, katerih podvojenost želiš preveriti.

Excel dvojniki - 5. izberi celice

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

Excel dvojniki - 6. pogojno oblikovanje pravila za označevanje celic

3. V modalnem oknu lahko spremeniš privzeto oblikovanje, če želiš, in nato klineš V redu (OK).

Excel dvojniki - 7. podvojene vrednosti celic

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

Excel dvojniki - 8. označene podvojene celice

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 poimenuješ recimo “Dvojniki (združeni stolpci)”.

Excel dvojniki - 9. združi stolpce

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

=CONCATENATE(
Excel dvojniki - 10. formula concatenate

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

3. Še vedno tekom vpisovanja formule preklikaš vse stolpce od leve proti desni in vsakič vneseš podpičje “;”.

Excel dvojniki - 11. formula concatenate združi stolpce

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

4. Še vedno tekom vpisovanja formule vpišeš zaklepaj “)” in pritisneš tipko “Enter”.

Excel dvojniki - 12. formula concatenate rezultat

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 narediš tako, da izbereš celico, ki ima vnešeno pravo formulo in dvojno klikneš njen desni spodnji kot (Autofill handle). Alternativno primeš taisti desni spodnji kot in ga ročno preneseš čez celoten stolpec.

Excel dvojniki - 13. formula concatenate autofill handle
Excel dvojniki - 14. formula concatenate bliskovita zapolnitev

Drugi dodatni stolpec

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

Excel dvojniki - 15. preštej podvojene vrednosti

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

=COUNTIF(
Excel dvojniki - 16. formula 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 tekom vpisovanja formule izbereš celotni stolpec “Dvojniki (združeni stolpci)” (na spodnji sliki obarvano modro), vpišeš podpičje “;” izbereš prvo celico stolpca “Dvojniki (združeni stolpci)” (na spodnji sliki obarvano rdeče).

Excel dvojniki - 17. formula countif izberi stolpec

4. Pritisneš tipko “Enter”.

Excel dvojniki - 18. formula countif rezultat

5. Če se formula ni avtomatsko razširila po celotnem stolpcu, to narediš tako, da izbereš celico, ki ima vnešeno pravo formulo in dvojno klikneš njen desni spodnji kot (Autofill handle). Alternativno primeš taisti desni spodnji kot in ga ročno preneseš čez celoten stolpec.

Excel dvojniki - 19. formula countif autofill handle
Excel dvojniki - 20. formula countif samodejno ustvarjanje izračunanih stolpcev

Š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 vrstice podvojena: 2 = dva primerka te vrstice, 3 = tri primerki te vrstice, itd.

Pravilo pogojnega oblikovanja

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

1. Označiš vse vrstice, ki jih želiš primerjati med seboj.

Excel dvojniki - 21. izberi vrstice

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

Excel dvojniki - 22. pogojno oblikovanje novo pravilo

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

=$G2>1
Excel dvojniki - 23. novo pravilo oblikovanja

4. Klikneš Oblika (Format) in določiš, kako naj Excel oblikuje podvojene vrednosti.

Excel dvojniki - 24. oblikuj celice

5. Na obeh modalnih oknih klikneš V redu (OK).

6. Pravilo pogojnega oblikovanja zgleda takole:

Excel dvojniki - 25. upravitelj pravil pogojnega oblikovanja

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

Excel dvojniki - 26. označene podvojene vrstice

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. V kolikor pa gre za preglednico, kjer je vsake toliko potrebno pregledati podatke za podvojene vrednoti 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 želiš prikazati zgolj podvojene vrednosti, moraš 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)
Excel dvojniki - 26. označene podvojene vrstice 2

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

Excel dvojniki - 27. številski filtri večje kot

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

Excel dvojniki - 28. samodejno filtriranje po meri

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

Excel dvojniki - 29. prikaži dvojnike

Excel dvojniki - 4. Odstrani dvojnike

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

Ročno odstranjevanje dvojnikov

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

Pri brisanju moraš biti pozoren še na to, da v vsaki skupini dvojnikov ohraniš po en primerek in ne pobrišeš vseh.

Avtomatizirano odstranjevanje dvojnikov

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

1. Izbereš nabor podatkov iz katerega želiš odstraniti dvojnike.

Excel dvojniki - 30. izberi tabelo

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

Excel dvojniki - 31. funkcija odstrani dvojnike

3. Obkljukaš stolpce, ki želiš, da jih upošteva pri primerjanju podvojenosti. Če želiš odstraniti vrstice, kjer se ujemajo vrednosti v vseh stolpcih, obkljukaj vse stolpce. Če želiš odstraniti vrstice, kjer se ujemajo vrednosti samo v enem ali dveh stolpcih, obkljukaj samo ta dva.

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

Excel dvojniki - 32. odstranjevanje dvojnikov

5. Klikneš V redu (OK).

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

Excel dvojniki - 33. sporočilo o odstranjenih dvojnikih

7. Klikneš V redu (OK).

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

Excel dvojniki - 34. prečiščeni podatki brez dvojnikov

Pomanjkljivost avtomatskega odstranjevanja dvojnikov je ta, da ti Excel ne pokaže, katere vrstice je odstranil, ti pa preko tega sporočila da vsaj občutek, ali se številka giblje v pričakovanem območju, ali pa nečesa nisi najbolje nastavil 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 se označuje, prikazuje in odstranjuje podvojene vrednosti ter dela z Excel dvojniki.

~ ~ ~

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

Excel vodiči:
Dvojniki · Filtriranje · Pogojno oblikovanje · Sortiranje · Spustni seznam · VLOOKUP

Excel pogojno oblikovanje vrstic

V Excel tabelah pogosto pride prav, da se celotna vrstica samodejno obarva z določeno barvo, v kolikor je izpolnjen nek pogoj. To doseže Excel pogojno oblikovanje vrstic, kjer kot pogoj uporabimo status, osebo, datum, številčno vrednost ali karkoli drugega.

Excel pogojno oblikovanje vrstic si bomo pogledali na primeru, kjer se celotna vrstica obarva glede na izposojenost knjige. Ko je knjiga na voljo, je vrstica bele barve, če je knjiga izposojena pa se vrstica obarva rdečkasto.

Excel pogojno oblikovanje vrstic

1. Izbereš vse vrstice, katere želiš, da se obarvajo, ko izpolnijo zastavljen pogoj. Ponavadi to pomeni označitev vseh vrstic razen povsem zgornje, kjer se nahajajo naslovi stolpcev. To narediš tako, da klikneš številko “2” na začetku druge vrstice in nato tipki Ctrl+dol.

Excel pogojno oblikovanje vrstic - 2. izberi vrstice

2. Klikneš zavihek Osnovno (Home).

3. V skupini Slogi (Styles) klikneš možnost Pogojno oblikovanje (Conditional formatting).

Excel pogojno oblikovanje vrstic - 3. pogojno oblikovanje

4. Klikneš Novo pravilo (New rule).

Excel pogojno oblikovanje vrstic - 4. novo pravilo

5. Klikneš Uporabi formulo za določanje celic za oblikovanje (Use a formula to determine which cells to format).

Excel pogojno oblikovanje vrstic - 5. uporabi formulo

6. Pod Oblikuj vrednosti, kjer velja ta formula (Format values where this formula is true) vneseš:

=$A2="Izposojeno"
Excel pogojno oblikovanje vrstic - 6. formula

7. Klikneš gumb Oblika (Format).

Excel pogojno oblikovanje vrstic - 7. oblika

8. Klikneš zavihek Polnilo (Fill), izbereš želeno barvo in klikneš gumb V redu (OK).

Excel pogojno oblikovanje vrstic - 8. oblikuj celice

9. Ponovno klikneš V redu (OK).

Excel pogojno oblikovanje vrstic - 9. v redu

10. Pravilo pogojnega oblikovanja nastaviš tako, da se vrednosti v stolpcih Pravilo (Rule) ter Velja za (Applies to) pričneta v isti vrstici. V našem primeru se morata obe pričeti v 2. vrstici.

Excel pogojno oblikovanje vrstic - 10. upravljaj pravila

In tako ustvariš Excel pogojno oblikovanje vrstic.

Excel pogojno oblikovanje vrstic - 11. končni rezultat

PS: Če koga zanimajo knjige s tega spiska, si lahko pogleda članek Knjige za osebno rast.

~ ~ ~

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

Excel vodiči:
Dvojniki · Filtriranje · Pogojno oblikovanje · Sortiranje · Spustni seznam · VLOOKUP

Excel filtriranje

Microsoft Excel omogoča filtriranje podatkov, kar je zelo uporabno, ko jih imamo ogromno, želimo pa videti zgolj določene. Excel filtriranje si bomo pogledali na primeru spiska družabnih iger.

Excel filtriranje

Recimo, da prirejamo prijeten družabni večer s prijatelji. Igrali bomo družabne igre, vseh skupaj nas bo 5 in zdaj nas zanima: Katere igre so najbolj primerne za 5 igralcev?

1. Za filtriranje morajo podatki imeti glave tj. vrstico z naslovi stolpcev.

Excel filtriranje - 2. glave

2. Ko imamo glave, lahko vklopimo filtre tako, da izberemo vse podatke, kliknemo zavihek Podatki (Data) in nato Filter (Filter).

Excel filtriranje - 3. filter

Nato filtriramo tako, da kliknemo enega od spustnih menijev.

Excel filtriranje - 4. filtri

3. Alternativno lahko podatke spremenimo v tabelo tako, da jih izberemo, pritisnemo tipki Ctrl+T in nato gumb V redu (OK). Pretvorba podatkov v tabelo je v veliki večini primerov najboljša izbira.

Excel filtriranje - 5. ustvarjanje tabele
Excel filtriranje - 6. tabela

4. Na vrhu kliknemo spustni meni stolpca po katerem želimo filtrirati in nastavimo filtre.

Excel filtriranje - 7. filter spustni meni

V našem primeru v stolpcu “Najboljše število igralcev min.” odkljukamo 3 in 6, v stolpcu “Najboljše število igralcev max.” pa 3, 4 in 8. Potem razvrstimo družabne igre po stolpcu “BoardGameGeek ocena” od najbolje do najslabše ocenjene in dobimo odličen pregled, katera igra bi bila najboljša za naš družabni večer.

Excel filtriranje - 8. končni rezultat

In to je Excel filtriranje podatkov na kratko in jedrnato.

~ ~ ~

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

Excel vodiči:
Dvojniki · Filtriranje · Pogojno oblikovanje · Sortiranje · Spustni seznam · VLOOKUP

Excel sortiranje

Microsoft Excel omogoča sortiranje podatkov po abecedi, po datumu, od A do Ž, Ž do A, največjega do najmanjšega in najmanjšega do največjega. Zelo uporabna funkcionalnost, ki si jo bomo pogledali na primeru spiska družabnih iger.

Excel sortiranje

Družabne igre so trenutno razporejene od najbolje ocenjene do najslabše ocenjene (stolpec “BoardGameGeek ocena”), mi pa jih bomo razvrstili po naslovu tj. stolpec “Naslov družabne igre” bomo sortirali po abecedi od A do Ž.

Excel sortiranje po abecedi

1. Izbereš podatke (nabor celic ali celoten stolpec), ki jih želiš razporediti po abecedi.

Excel sortiranje po abecedi - 1. izberi celice

2. Klikneš zavihek Podatki (Data).

Excel sortiranje po abecedi - 2. zavihek podatki

3. Klikneš Razvrsti od A do Ž (Sort A to Z).

Excel sortiranje po abecedi - 3. razvrsti od A do Ž

4. Izbereš Razširiti izbor (Expand the selection). V kolikor bi izbral “Nadaljevati s trenutnim izborom” (Continue with the current selection) bi Excel razvrstil zgolj izbrane podatke in ne celotnih vrstic, kar bi podatke potencialno pokvarilo.

Excel sortiranje po abecedi - 4. razširiti izbor

5. Klikneš Razvrsti (Sort).

Excel sortiranje po abecedi - 5. razvrsti

V kolikor se podatki nahajajo v tabeli ali pa imate vključene filtre, jih lahko razvrstite po abecedi s klikom na filtre zgornje vrstice. Podatke pretvorite v tabelo tako, da jih izberete in kliknete tipki Ctrl+T.

Excel sortiranje po abecedi - 6. tabela filtri

In tako izvedeš Excel sortiranje po abecedi.

~ ~ ~

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

Excel vodiči:
Dvojniki · Filtriranje · Pogojno oblikovanje · Sortiranje · Spustni seznam · VLOOKUP

Excel spustni seznam

Excel spustni seznam (imenovan tudi Excel spustni meni) omogoča hiter in prijeten vnos podatkov ter zmanjšuje število napak pri vnosu le-teh. Sledijo najkrajša možna navodila, kako ga ustvariti:

  1. Izberi celice.
  2. Klikni zavihek Podatki.
  3. Klikni Preveri veljavnost podatkov.
  4. Pod Dovoli izberi Seznam.
  5. Pod Vir vnesi izbire (ločene s podpičji).
  6. Klikni V redu.
Excel spustni seznam

Poglejmo še v slikah, kako ustvariti preprost spustni seznam v Excelu.

1. Izbereš celice, kamor želiš vstaviti spustni seznam.

Excel spustni seznam - 2. izberi celice

2. Klikneš zavihek Podatki (Data).

Excel spustni seznam - 3. zavihek podatki

3. Klikneš Preveri veljavnost podatkov (Data validation).

Excel spustni seznam - 4. preveri veljavnost podatkov

4. Pod Dovoli (Allow) izbereš Seznam (List).

Excel spustni seznam - 5. dovoli seznam

5. Pod Vir (Source) vneseš izbire in jih ločiš s podpičji.

Excel spustni seznam - 6. vir

6. Klikneš V redu (OK).

Excel spustni seznam - 7. potrditev

In na ta način ustvariš preprost Excel spustni seznam.

Excel spustni seznam - 8. rezultat

Slednji seveda ni edini način, kako ustvariti spustni seznam v Excelu. Naj vam pokažem naprednejši način, ki omogoča odličen pregled vseh ustvarjenih spustnih seznamov, enostavno dodajanje novih izbir in brezskrbno uporabo, saj ga je težko ponesreči pokvariti.

Napreden Excel spustni seznam

1. Ustvariš nov zavihek in ga poimenuješ “Nastavitve” (po angleško “Settings”).

Excel spustni seznam - 9. ustvari nov list
Excel spustni seznam - 10. ustvarjen nov list

Ta zavihek bomo uporabljali za “nastavljanje” izbir spustnega seznama, toda ne samo trenutnega, temveč prav vseh spustnih seznamov, ki jih bomo ustvarili v tej Excel datoteki. Vsak spustni seznam v svoj stolpec.

2. Označiš 9. vrstico in pod njo narišeš črto.

Excel spustni seznam - 11. podčrtaj vrstico

Zakaj črta? Da vizualno ločimo navodila od podatkov za spustne sezname. Prvih 9 vrstic bomo namreč namenili:

  • navodilom, kako uporabljati podatke
  • razlagi, za kakšne podatke gre in
  • raznoraznim zapiskom, ki lahko pridejo prav drugim uporabnikom te Excel datoteke ali nam v prihodnosti, ko bomo pozabili, kako in zakaj smo spustne sezname sestavili tako kot smo jih.
Excel spustni seznam - 12. ločena navodila in podatki

Zakaj devet vrstic? Ker je to ravno dovolj prostora za navodila in ker si je enostavno zapomniti, da se podatki za spustne sezname pričnejo v 10. vrstici tj. prvi dvomestni številki.

3. Napišeš navodila. Moja ponavadi zgledajo takole:

Excel spustni seznam - 13. navodila

4. Vneseš izbire za spustni seznam.

Excel spustni seznam - 14. vnešene izbire

5. Označiš vse vnesene izbire in na tipkovnici pritisneš Ctrl+T, da jih pretvoriš v tabelo.

Excel spustni seznam - 15. ustvari tabelo

Prednost sklicevanja na tabelo je, da lahko vanjo vnašamo podatke kadarkoli in ti so takoj prikazani v spustnem seznamu. V kolikor pa se na podatke sklicujemo, ko ti niso oblikovani kot tabela, bo verjetno potrebno popraviti tudi sklic nanje, kar je nepotrebno dodatno delo.

6. Naslov stolpca preimenuješ v “Ljudje”.

Excel spustni seznam - 16. ustvarjena tabela

7. Klikneš kamorkoli na tabelo, nato zavihek Načrt tabele (Table design) in levo zgoraj preimenuješ tabelo v “setLjudje”.

Excel spustni seznam - 17. ime tabele

Ta korak je poljuben, a ga vseeno priporočam, saj se na tabele lažje sklicujemo, če imajo smiselna imena, ki si jih zlahka zapomnimo. Vse tabele v zavihku “Nastavitve” posledično poimenujem s predpono “set”, da vem, da gre za nastavitve (po angleško “settings”). Preostanek imena prilagodim vsebini tabele.

8. Klikneš zavihek Formule (Formulas) in nato Upravitelj imen (Name manager).

Excel spustni seznam - 18. zavihek formule

9. Klikneš Novo (New).

Excel spustni seznam - 19. upravitelj imen

10. Pod Ime (Name) vneseš “Ljudje” in nato klikneš vnosno polje Se sklicuje na (Refers to).

Excel spustni seznam - 20. novo ime

Ime si je najlažje zapomniti, če uporabimo istega za stolpec in za sklic, ki ga bomo vnesli v preverjanje veljavnosti podatkov. V našem primeru obakrat uporabimo besedo “Ljudje”.

Excel spustni seznam - 21. ime stolpca in sklica

11. Izbereš vse celice ustvarjene tabele razen imena stolpca tako, da se v vnosnem polju Se sklicuje na (Refers to) pojavi sklic:

=setLjudje[Ljudje]

To pomeni, da se sklicuješ na tabelo “setLjudje”, specifično na njen stolpec “Ljudje”.

Excel spustni seznam - 22. se sklicuje na

12. Klikneš V redu (OK).

Excel spustni seznam - 23. potrditev novega imena

13. Klikneš Zapri (Close).

Excel spustni seznam - 24. prikaz novega imena

14. Slediš navodilom za ustvarjanje preprostega Excel spustnega seznama do vključno točke 4.


Dobrodošli nazaj!

15. V vnosno polje Vir (Source) tokrat ne vneseš izbir kot takšnih, temveč referenco na tabelo, ki si jo ravnokar dodal v Upravitelja imen (Name manager):

=Ljudje
Excel spustni seznam - 25. preverjanje veljavnosti podatkov

16. Klikneš V redu (OK).

Excel spustni seznam - 26. potrditev preverjanja veljavnosti podatkov

Preprosti in napredni Excel spustni seznam se na prvi pogled sicer ne razlikujeta…

Excel spustni seznam - 8. rezultat

… je pa v naprednejšo različico enostavneje dodajati nove izbire, imeti odličen pregled nad vsemi spustnimi meniji in težje pokvariti vse skupaj.

In to je to. Zdaj znate ustvarjati spustne sezname in si poenostaviti delo v Excelu 🎉

~ ~ ~

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

Excel vodiči:
Dvojniki · Filtriranje · Pogojno oblikovanje · Sortiranje · Spustni seznam · VLOOKUP