Spoznavanje Exela - Lekcija 1

Pomoč pri delu z MS Excelom
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Najprej bi se rad zahvalil, ker ste me sprejeli na vaš forum, saj je bila vaša stran priporočena iz parih virov, ko pride do vprašanj s področja MS Office.

Sem v fazi učenja in spoznavanja Excela tako, da se še moram naučiti vseh osnovnih funkcionalnosti Excel, zato pišem sedaj tukaj, ker potrebujem nekaj pomoči.

Imam dve ločeni tabeli:
- V prvi tabeli so sledeči stolpci (distributer, program a, program b, program c, program d) - List 1
- V drugi tabeli so sledeči stolpci (distributer, šifra programa, skrbnik programma) - List 2

Ali se da s pomočjo Excela, da bi mi s pomočjo nekih funkcij samodejno izpisalo (v tabelo na 1 Listu, ki sem jo dodal) skrbnike programov pod posameznega Distributerja? Hvala vsem že v naprej za vso pomoč. Na spodnjem linku sem naredil Excel primer datoteko.

http://www.megafileupload.com/en/file/5 ... -xlsx.html

P.S. Ob registraciji je potrebno vnesto kodo (števlke in črke) in pod sliko piše: "Vtipkaj kodo, ki jo vidiš na sliki. Vse črke so male, ničle ni.". Pri meni je bilo tako, da sem moral vpisati velike črke.
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

Pozdravljeni,

To kar želite vam bo rešila funkcija VLOOKUP o kateri si lahko veliko preberete tudi v tem forumu in na spletišču matjazev.net. Klasičen primer uporabe funkcije pa je takšnele.

Će v vašem konkretnem primeru predpostavimo, da se podatki na listu 2 nahajajo na območji List2!A2:C100, potem je formula, ki jo morate zapisat v celico F2 na listu1 sledeča:

Koda: Izberi vse

=VLOOKUP(A1, List2!$A$2:$C$100; 3; false)
Neznanec napisal/-a:P.S. Ob registraciji je potrebno vnesto kodo (števlke in črke) in pod sliko piše: "Vtipkaj kodo, ki jo vidiš na sliki. Vse črke so male, ničle ni.". Pri meni je bilo tako, da sem moral vpisati velike črke.
Hvala za opozorilo. Bom pogledal kako je s tem. Sicer pa tudi ne škodi - se je potrebno vsaj malce potruditi in tako se znebimo 99% ljudi, ki bi želeli smetiti po forumu ;)
lp,
Matjaž Prtenjak
Administrator
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Prebral sem si kar ste mi svetovali naj si preberem na vaši strani. Ali ste v ukazu v vašem postu naredili napako ali je sintaksa takšna (so v oklepajih samo vejice (,) ali samo podpičja(;))? Kot lahko vidite iz slike mi ni uspelo pravilno uporabiti VLOOKUP funkcije. Ali sem definiral kaj narobe?

http://shrani.si/f/B/O4/25ax45lT/1/zajeta-slika.jpg

Ali se da isto stvar doseči tudi z IF stavki?
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

Pozdravljeni,

V mojem primeru je napaka, saj bi moralo za A1 biti podpičje. SLO Excel uporablja podpičja, angleški Excel pa vejice.

Kar pa se tiče vašega primera, pa je funkcija napačna, saj želite najti vrednost celice K1 med celicami K2:K19... Vi pa seveda tam iščete vrednost celice A2. Pa tudi območje od K do M nima 13 kolon temveč 3 in skrbnik programa je zapisan v zadnji - tretji!

Prava VLookup funkcija za vaš primer je torej takšna:

Koda: Izberi vse

=VLOOKUP(A2;$K$2:$M$19;3;False)
lp,
Matjaž Prtenjak
Administrator
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Hvala za pojasnilo. Sedaj deluje, vendar jaz želim, da bodo prikazani pod posameznimi programi (A,B,C,D) samo skrbniki, ki so za njih zadlolženi. Na sliki sem barvno označil rezultate. Kako lahko dosežem sledeče?

Probal sem s sledečo funkcijo, vendar ni delovalo (za stolpec "Skrbnik programa D"):

Koda: Izberi vse

=VLOOKUP(E1;$K$2:$M$19;3;FALSE)
http://shrani.si/f/2Q/4z/CKCXH7R/zajeta-slika.jpg


Ali se da isto stvar doseči tudi z IF stavki?
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

V Excelu s preprosti funkcijami ne morete iskati podatkov po dveh kolonah. Vas namreč zanima vrednost (skrbnik programa), ki je odvisna od dveh parametrov. Najprej od partenrja in potem od šifre programa.

V tem primeru je najlažje, da naredite nov stolpec, v katerega združite tako distributerja, kot šifro programa (npr: =K2&"_"&L2) in tako dobite nov ključ "Distributer1_A"... v osnovni tabeli pa potem iščete s tem ključem. Vendar pa sami pišete, da ste začetnik in to je pretežka naloga za začetnika, zatorej s tem raje počakajte in se naučite naprej uporabljati osnovne VLOOKUP funkcije.

Kar pa se tiče pogojnih (IF) stavkov pa je odgovor ne. Pogojni stavki niso namenjeni reševanju tovrstnih problemov.
lp,
Matjaž Prtenjak
Administrator
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Hvala za vso pomoč, vendar če ste pripravljeni bi se sedaj rad naučil rešiti to zagato do konca. Sem naredil nov stolpec "Distributer_SifraPrograma" in združil stolpca distributer in sifra programa (s funkcijo, ki sto jo vi napisali in jo potegnil čez cel stolpec). Nato sem napisal sledečo funkcijo, da bi mi izpisalo samo imena skrbnikov za program D, vendar pa se mi ponovno javi N/V napako. Mi lahko svetuje naprej?

Koda: Izberi vse

=VLOOKUP(N2;$K$2:$M$19;3;FALSE)
http://shrani.si/f/3N/QT/2tY88e5c/zajeta-slika.jpg
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

Pozdravljeni,

to "zagato" vam lahko rešim, a kot sem vam že povedal; Takšnih zagat boste imeli vedno več, v kolikor se ne boste naučili osnov! Spet želite iskati po stolpcu N, kar je seveda povsem nesmiselno, saj se vaši podatki nahajajo v stolpcih oa A do I, kar pomeni, da je prvi parameter funkcije VLOOKUP kvečjemu lahko eden izmed stolpcev od A do I! A "zagat" je še kar nekaj in da bi stvar delovala je potrebno tabeli zasnovati drugače.

Najprej se je potrebno zavedati (to je tudi v tem forumu omenjeno že mnogokrat), da VLOOKUP nikakor ne more dešifrirati elementov levo od ključa, temveč vedno in samo desno od slednjega. V vašem primeru to pomeni, da ke želite dešifrirati skrbnika programa se mora slednji nahajati desno od ključa! Najprej morate torej zamenjati koloni M in N! Stanje boste torej imeli sledeče:

Koda: Izberi vse

   K                 L             M               N
Distributer    Šifra programa   Ključ         Skrbnik Programa
xxxx           yyyy             =K2&"_"&L2    Skrbnik 1
Sedaj imate šifrant urejen. Da bi po tem šifrantu lahko iskali, pa je seveda tudi v osnovni tabeli potrebno sestaviti pravilen ključ, zato je potrebno v celice F2 do I2 zapisati sledeče formule:

Koda: Izberi vse

[F2] =VLOOKUP(A2&"_A";$M$2:$N$19;2;false)
[G2] =VLOOKUP(A2&"_B";$M$2:$N$19;2;false)
[H2] =VLOOKUP(A2&"_C";$M$2:$N$19;2;false)
[I2] =VLOOKUP(A2&"_D";$M$2:$N$19;2;false)
lp,
Matjaž Prtenjak
Administrator
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Hvala Matjaž. Mi lahko enostavno razložiš kako bi interpritiral pri sledeči funkciji [F2] =VLOOKUP(A2&"_A";$M$2:$N$19;2;false), kaj dela ta del: A2&"_A". Ostale tri parametre si znam razložiti z "besedami" med tem ko 1 parameter pa ne. Torej prvi parameter je tisto kar mora funkcija "primerjati" v stolpcu ("Distributer_SifraPrograma"), ali prav interpretitram?
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

Neznanec napisal/-a:...kaj dela ta del: A2&"_A"...
Vrednosti v celici A2 pridruži niz '_A' da dobi iskani ključ, ki je sestavljen kot +Distributer' + "_" + črka
lp,
Matjaž Prtenjak
Administrator
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

admin napisal/-a:
Neznanec napisal/-a:...kaj dela ta del: A2&"_A"...
Vrednosti v celici A2 pridruži niz '_A' da dobi iskani ključ, ki je sestavljen kot +Distributer' + "_" + črka
Hvala za obrazložitev. Zvedel sem, da se da dobiti isti rezultat tud z IF stavkom. Bom prilepil funkcijo tukaj:

Koda: Izberi vse

=IF(AND('Tabela 2'!$B4=$B4; 'Tabela 2'!C4="A");'Tabela 2'!D4; "")
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Matjaž vse kar si me naučil prejšni teden sem implementiral in dela vse tako kot mora. Imam pa novo vprašanje in bi te rad prosil za pomoč. Sedaj imam primere, kjer se distributer z istim programom večkrat ponavlja (naprimer: Mercator_A). Kolikor razumem VLOOKUP funkcijo, Excel išče do prvega zadetka in nato gre naprej, medtem, ko bi meni prišlo prav, da bi prečekiral vse vnose in izpisal IME skrbnika, ko se pojavi. Katero funkcijo lahko za to uporabim? In kje v sami VLOOKUP funkciji jo je potrebno napisati? Sedaj zgleda celotna funkcija tako: =IFERROR(VLOOKUP(A2&"_312";$O$2:$P$90000;2;FALSE);"")

Koda: Izberi vse

PODJETEJE	SKRBNIK
Mercator_A	-
Mercator_A	-
Mercator_A	-
Mercator_A	-
Mercator_A	-
Mercator_A	-
Mercator_A	-
Mercator_A	-
Mercator_A	Miha
Mercator_A	-
Mercator_A	-
Mercator_A	-
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

Pozdravljeni,

V Excelu je to sicer rešljivo z uporabo formul, vendar pa so slednje zelo zapletene in zelo posebne - matrične funkcije. Rešitev vašega problema torej nikakor ni trivialna in jaz se takšnih problemov ne lotevam z uporabo funkcij, temveč napišem makro, saj mi je to lažje.

Rešitev s funkcijami recimo najdete tukaj. Sem vam povedal da je zapleteno! :)
lp,
Matjaž Prtenjak
Administrator
Neznanec
Prispevkov: 9
Pridružen: Po Okt 27, 2014 11:22 am

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a Neznanec »

Po prebranem tvojem članku in tudi drugem, če pravilno razumem kar je napisano mi bo s matričnimi funkcijami Excel podal odgovor le v primeru, če mu v naprej definiram kaj iščem (npr. Americano; v mojem primeru Miha). Samo ker govorim jaz v excelu kjer je 90 000 vrstic se mi zdi nesmiselno, da bi jaz iskal odgovor/iskalni niz sam. V mojem konkretnem primeru je tako, da je nek distributer (naprimer: Mercator_A) ima 99% polj kjer bi moral pisati SKRBNIK v polju napisano vrednost (-) in le v 1% vrstic je mogoče napisano ime skrbnika. Jaz pa bi rad samo to, da Excel pri poizvedbi zanemari v iskanju (-) in išče samo črke/besedo ozioma ime skrbnika.

Ali me lahko naučiš to narediti z makri saj, če je bi se TI tega tako lotil, potem se želim isto stvar naučiti tudi jaz.


Imam še eno vprašanje ne povezano s tem :D . Koliko let se ti že izobražuješ v Excelu, da imaš tako bogato znanje?
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Spoznavanje Exela - Lekcija 1

Odgovor Napisal/-a admin »

Pozdravljeni,
Neznanec napisal/-a:Po prebranem tvojem članku in tudi drugem, če pravilno razumem kar je napisano mi bo s matričnimi funkcijami Excel podal odgovor le v primeru, če mu v naprej definiram kaj iščem (npr. Americano; v mojem primeru Miha).[/b].
Ne, če bi bilo tako, potem bi bilo nesmiselno. Zakaj bi iskali nekaj, kar že veste. zakaj bi iskali Miha, če to že veste. Predstavljene matrične funkcije bi v vašem primeru vrnile dva rezultata (v dve celici!) v prvi celici bi pisalo '-' v drugi pa 'Miha'. Kot rezultat bi torej dobili vsa različna imena skrbnikov.
Neznanec napisal/-a:Ali me lahko naučiš to narediti z makri saj, če je bi se TI tega tako lotil, potem se želim isto stvar naučiti tudi jaz.
Ne, tega pa vas ne morem naučiti - se boste pa morali sami.
Neznanec napisal/-a:Imam še eno vprašanje ne povezano s tem :D . Koliko let se ti že izobražuješ v Excelu, da imaš tako bogato znanje?
V Excelu se ne izobražujem temveč ga samo uporabljam, sicer pa sem razvijalec in programe razvijam že 20 let.
lp,
Matjaž Prtenjak
Administrator
Odgovori