Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

Vlookup ili slične funkcije

[es] :: Office :: Excel :: Vlookup ili slične funkcije

Strane: 1 2

[ Pregleda: 4653 | Odgovora: 27 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Vlookup ili slične funkcije11.07.2017. u 23:54 - pre 81 meseci

Pozdrav ekipi znalaca!

Dobio sam zadatak u kojem iz baze podataka centr.skladista moram dobiti za svakog skladistara zaduzene artikle.
U bazi su skladistari svaki zaduzen po sifri tako su sifre duple ili troduple, ovisi koliko ih je dobilo taj artikl.
(Baza je dosla od invent.stanja)
Također , za svaki novi upisani artikl , trebalo bi biti moguće povući podatak po skladistaru.
Mučim se vlookupom ali ne funkcionira ni po imenu ni po sifri.
tab je u prilogu.

Molim veliku pomoć .
Tnx unaprijed.
Prikačeni fajlovi
 
Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898



+66 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 09:50 - pre 81 meseci
ARRAY formula koju bi mogao koristiti je slijedeća
Code:
=IFERROR(INDEX('BAZA OS'!$A$2:$G$500;SMALL(IF('BAZA OS'!$D$2:$D$500=$B$1;ROW('BAZA OS'!$A$2:$A$500));ROW('BAZA OS'!1:1))-1;COLUMN('BAZA OS'!A1));"")

Formulu samo kopiraš u desnu stranu pa dolje.
S obzirom da si umetnuo stupac "Poslovnica" obrati pažnju na preskakanje dotičnog stupca tj. u formuli funkcija COLUMN treba preskočiti jedan stupac prilikom kopiranja. Ako već mora biti ja bi taj stupac postavio na zadnje mjesto (iako ne vidim odakle će vući podatak, osim ako ćeš ga ručno upisati).

nakon kreiranja svih formula na prvom sheetu, jednostavno taj sheet skladištara kopiraš i samo preimenuješ. Ostalo ide sve automatski jer je u B1 ćeliji formula koja automatski vraća naziv sheeta.

BTW: Prekontroliraj rezultate jer ja nisam.
Prikačeni fajlovi
 
Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2267
89.216.49.*

Sajt: www.gowi.rs


+109 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 10:36 - pre 81 meseci
Ivek33 je već dao rešenje. Samo da navedem da je nešto slično u vezi razvrstavaljem po listovima bilo je ovde http://www.elitesecurity.org/t406400-0#2673105. Tu sam pokušao da objasnim formule ako ti pomogne.

[Ovu poruku je menjao Jpeca dana 12.07.2017. u 12:18 GMT+1]
Nije to loše Rembrante, samo što ne bi dodao još malo boje?
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 11:13 - pre 81 meseci

Hvala punoooo Ivek33. Sve radi.
Nije mi bitna poslovnica, ali imam još 2 kolone koje nisam na tab pokazao (ne smijem javno objaviti); to su cijene i dobavljač.
Pokušat ću sam to napraviti ak mi bude radilo.
Tnx.
 
Odgovor na temu

ib.kroki
Beograd

Član broj: 188338
Poruke: 804
*.adsl.verat.net.



+35 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 13:57 - pre 81 meseci
@Ivek33
Sjajno!
Biću slobodan da te zamolim za pojašnjenje, kako si došao do rešenja.
Zvoni, kucanje ne radi ...
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 14:35 - pre 81 meseci

Ivek33, probao sam dobiti podatke iz stupaca s cijenama i dobavljačima ali nisam uspio.

Sad sam ubacio Dobavljače i 'cijene'.
Kopirao sam formulu, promijenio stupac na koji se odnosi, ali-nista.
Molim te, možeš li to povezati na Tab u privitku.
Isprika na gnjavaži i hvala puno unaprijed.
Prikačeni fajlovi
 
Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898



+66 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 19:19 - pre 81 meseci
Citat:
BornaŠ: probao sam dobiti podatke iz stupaca s cijenama i dobavljačima ali nisam uspio.

1. Kao prvo, moraš obratiti pažnju na raspon ćelija iz kojeg funkcija INDEX vraća rezultate (prvi argument). )
Dakle, u prvoj formuli postavljen je raspon ćelija 'BAZA OS'!$A$2:$G$500 jer smo odlučili uvrstiti rezultate iz stupaca A:G
U drugoj formuli trebaš proširiti ovaj raspon ćelija na 'BAZA OS'!$A$2:$I$500.
Tako sada u drugom rješenju imamo ARRAY formulu
Code:
=IFERROR(INDEX('BAZA OS'!$A$2:$I$500;SMALL(IF('BAZA OS'!$D$2:$D$500=$B$1;ROW('BAZA OS'!$A$2:$A$500));ROW('BAZA OS'!1:1))-1;COLUMN('BAZA OS'!A1));"")


S obzirom da si rekao da ti poslovnica nije potrebna, ja sam uklonio taj stupac.
Sada jednostavno na svakom sheetu skladištara ARRAY formulu iz ćelije A4 kopiraš u desno pa dolje.
btw: ne znam što će ti stupac 'D' ime djelatnika, mislim da ti je on suvišan (već imaš ime u ćeliji B1 a isto tako je imenovan i dotični sheet)

2. Obrati pažnju na format teksta, datuma i brojeva. Neki datumi nisu ispravno napisani što ti može u budućnosti stvarati problem kod nekih eventualnih radnji.
Ovo je formula kojom sređuješ datume u ispravan format (naravno ja sam uzeo u obzir greške koje sam uočio u datom fajlu).
Code:
=IFERROR(IF(LEN(RIGHT(E4;LEN(E4)-FIND("#";SUBSTITUTE(E4;".";"#";LEN(E4)-LEN(SUBSTITUTE(E4;".";""))))))=3;(MID(E4;1;6)&20&RIGHT(E4;2))*1;IF(LEN(SUBSTITUTE(E4;"..";"."))=9;MID(SUBSTITUTE(E4;"..";".");1;8)*1;MID(SUBSTITUTE(E4;"..";".");1;10)*1));E4)

Sređeni datum kopiraj u stupac E nakon odrađenog posla da bi datumi bili ispravno napisani.
Copy 'M' stupac => Paste Special => As Values u 'E' stupac

Ako želiš možeš u formulu iz stupca 'M' ugnijezditi formulu iz stupca 'E'
Tada bi ARRAY formula u 'E' stupcu glasila, (jeste dugačka ali ti riješava problem pogrešno napisanog datuma).
Code:
sorry ali ne molgu postaviti cijelu formulu jer je predugačka, forum ne dozvoljava. VIDI ATTACH

4. Ne znam koliko u stvarnosti imaš podataka (rows) jer je ovo ARRAY formula kojoj treba vremena da obradi podatke nakon neke izmjene. Možda da se držiš limita redova (raspona ćelija) ako znaš da je fixan.
Jer ova sama po sebi dosta "teška" ARRAY formula sadrži Volatile funkcije?

vidi attach
Prikačeni fajlovi
 
Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898



+66 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 19:44 - pre 81 meseci
Citat:
ib.kroki: Biću slobodan da te zamolim za pojašnjenje, kako si došao do rešenja.

@ib.kroki
Pokušat ću, mada vjerujem da je objašnjenje na linku koji je dao @JPECA u postu iznad, približno dovoljno (sve se svodi na isto).
Ja možda malo drugačije pristupam rješavanju zadatka pa evo otprilike ovako, (ono što ne znam proguglam).
O apsolutnim i relativnim adresama neću pisati jer pretpostavljam da si upoznat s njima.

Jako je bitno da znaš korisiti Evaluate formula u slučaju kada želiš nešto provjeriti, kao i tipku 'F9' za trenutni pogled na rezultat neke ugniježđene formule.

Rekli smo da je ovo ARRAY formula. Na internetu ima dosta pojašnjenja vezano za ovaj pojam a ima linkova i na ovom forumu.
Uglavnom ova formula još tzv. "CSE formula", završava se sa Ctrl+Shift+Enter umjesto samo "Enter" tipke

Kao prvo potrebno je poznavati određene Excel funkcije, argumente određene funkcije i sintakse funkcije u Excelu, koji rezultat u stvari one vraćaju a samim time taj rezultat želimo koristiti na mjestu nekog argumenta u nekoj drugoj funkciji.

Konkretno INDEX funkcija ima tri argumenta
Code:
=INDEX(array;row_num;column_num)

- array - Prvi argument je raspon podataka u kojem se nalazi uvjet i iz kojeg želimo vratiti rezultat iz određenog stupca
- row_num - argument koji zahtjeva broj reda (ovdje se često koristi funkcija MATCH koja doslovno vraća broj reda kao rezultat, ali sve zavisi o situaciji a primjer vidi ovdje)
- column_num - broj stupca iz kojega želimo vratiti rezultat (često se koristi funkcija COLUMN jer se kopiranjem mijenja rezultat)

U našem slučaju formula bi izgledala ovako
Code:
=INDEX('BAZA OS'!$A$2:$I$500;row_num;column_num)

S obzirom da želimo vratiti rezultat iz prvog stupca umjesto broja stupca '1' možemo postaviti formulu =COLUMN('BAZA OS'!A1)
Pa bi naša formula izgledala ovako (na ovaj način osigurali smo ispravan redni broj stupca prilikom kopiranja formule u desno.
Code:
=INDEX('BAZA OS'!$A$2:$I$500;row_num;COLUMN('BAZA OS'!A1)

Dolazimo do argumenta u kojem želimo znati broj reda iz kojeg će formula vratiti rezultat
Da bi pokušao ispravno objasniti ovaj dio formule krenuti ću od unutrašnje strane (ugniježđenih funkcija) a ja vjerujem da će me već netko ispraviti ako pogriješim negdje (da ne bi ostalo netočno)

Ovdje možemo koristiti funkciju SMALL koja ima sintaksu
Code:
=SMALL(array;k)

Dakle, opet imamo raspon ćelija u prvom argumentu a drugi argument je najmanja konstanta za koju želimo vratiti rezultat, dakle 1,2,3,4...).
1 je prvi podatak, 2 je drugi podatak, 3 je treći podatak itd itd...

U ovu formulu ugnijezdimo umjesto drugog argumenta funkciju ROW tj. za 'k' konstantu
Code:
=ROW('BAZA OS'!1:1)

Ova formula kao rezultat vraća broj 1, Kopiranjem prema dolje brojevi se mijenjaju pa će vratiti 2, pa 3 itd...
Da bi ovo provjerio, klikni unutar formule/funkcije SMALL na 'k' argument i Excel će selektirati cijelu funkciju. Pritisni 'F9' i vidjet ćeš rezultat ovog dijela formule.
Da bi ti bilo jasnije pogledaj ovaj link koji ima primjer Evaluate formula korištenjem tipke 'F9'

Dolazimo do prvog argumenta 'array' unutar SMALL funkcije
Ovdje ćemo koristiti formulu koja se sastoji od uvjeta i vraćanja rezultata a to je IF funkcija.
Code:
=IF('BAZA OS'!$D$2:$D$500=$B$1;ROW('BAZA OS'!$A$2:$A$500))

Ako znamo da funkcija IF ima sintaksu
Code:
=IF(logical_test;value_if_true;value_if_false)

znači trebamo logički test za uvjet i rezultat ako je istinit.
Ako unutar ugniježđene formule SMALL selektiraš prvi argument 'array', Excel će markirati cijelu IF formulu, pritisni tipku 'F9' i Excel će vratiti slijedeći rezultat
Code:
{FALSE;FALSE;FALSE;FALSE;FALSE;..............??????.......................SE;FALSE;FALSE;FALSE}

Sorry ali nisam mogao postavitit cijeli rezultat zbog ograničenja

Dakle za svaki ispunjeni uvjet tj. ako je u stupcu 'D' jednako podatku u 'B1' Excel vraća vrijednost iz određenog reda u stupcu 'A' a za sve ostalo rezultat je 'FALSE'
Ovaj dio formule IF vraća sve podatke iz stupca 'A'
Code:
ROW('BAZA OS'!$A$2:$A$500)

Ako selektiramo drugi argument unutar funkcija INDEX (row_num) Excel će označiti ovaj dio formule
Code:
SMALL(IF('BAZA OS'!$D$2:$D$500=$B$1;ROW('BAZA OS'!$A$2:$A$500));ROW('BAZA OS'!1:1))-1

Ako pritisneš tipku 'F9' dok je označen argument Excel će prikazati rezultat, u ovom slučaju na sheetu "BILIĆ" u ćeliji A4 rezultat ove formule je broj reda 21.

Dolazimo do koraka kada Excel izračuna dva argumenta 'row_num' i 'col_num'
Code:
=INDEX('BAZA OS'!$A$2:$I$500;{21};{1})

Dakle, u formuli iznad Excel će vratiti podatak iz reda 21 u prvom stupcu iz raspona A2:I500

Uh, nadam se da se nisam izgubio u pojašnjenju, i da ćeš me barem donekle shvatiti.
Osobno bi volio da još netko pojasni ovu formulu na njegov način koji bi se vjerujem razlikovao od mog.

btw: Izvinjavam se moderatorima jer sam ovdje postavio puno linkova na sporedne stranice koje sadrže primjere, jer pretpostavljam da će ovo čitati i totalni početnici.
 
Odgovor na temu

jaskojsako
BIH

Član broj: 141632
Poruke: 224
37.203.76.*



+4 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 19:59 - pre 81 meseci
Pozdrav
mogao si putem pivot table dobiti report
pogledaj ovo da li ti odgovara
Jasmin
Prikačeni fajlovi
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 20:25 - pre 81 meseci

Hvala puno Ivek33.
Tako je : Ne trebam ime skaldistara (bio mi je Bazi ) i datumi su sigurno negdje pogrešni jer sam dobio tako 'sirovu' tab , šta sam mislio posrediti.
Raspon sam predvidio na 500 pozicija, to će biti dovoljno.
Probat ću večeras sve srihtati po tvojim naputcima.
Puno hvala, ti si excel kralj kraljeva.
 
Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898



+66 Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 20:46 - pre 81 meseci
Nema na čemu.
Citat:
BornaŠ: excel kralj kraljeva
Nemoj tako, takav epitet nikada ja neću imati a ovdje ima forumaša koji se daleko bolje snalaze u Excelu od mene. Ja sam prosječan korisnik Excela i samo sam si dao truda jer nemam trenutno pametnija posla.
pozz
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 23:27 - pre 81 meseci
Citat:
jaskojsako:
Pozdrav
mogao si putem pivot table dobiti report
pogledaj ovo da li ti odgovara



Budem pokušao i po tvom prijedlogu.
Hvala lijepo.
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije12.07.2017. u 23:33 - pre 81 meseci
Citat:
Ivek33:
Nema na čemu.
Citat:
BornaŠ: excel kralj kraljeva
Nemoj tako, takav epitet nikada ja neću imati a ovdje ima aša koji se daleko bolje snalaze u Excelu od mene. Ja sam prosječan korisnik Excela i samo sam si dao truda jer nemam trenutno pametnija posla.
pozz



Ivek33 :He, prosječan? Ni govora !

Sredio sam sve, sve klapa.
Imam još pitanje : Promijenio sam raspon ćelija na 1500 (zlu ne trebalo) na svim stupcima, svi rade osim na stupcu s šiframa.
Taj ne prihvaća promjenu raspona.
Zašto ?
Možeš li mi dati formulu za taj stupac na raspon do 1500 ?


[Kad tlačim, tlačim do kraja]

Tnx.
 
Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898



+66 Profil

icon Re: Vlookup ili slične funkcije13.07.2017. u 08:47 - pre 81 meseci
Pa ne znam što je problem, trebao si postaviti primjer da se vidi što si radio ili barem formulu koju si upisao u neku ćeliju.
Array formula u A4
Code:
=IFERROR(INDEX('BAZA OS'!$A$2:$I$1500;SMALL(IF('BAZA OS'!$D$2:$D$1500=$B$1;ROW('BAZA OS'!$A$2:$A$1500));ROW('BAZA OS'!1:1))-1;COLUMN('BAZA OS'!A1));"")
Vodi računa da će ti to usporavati rad u Excelu jer svaki puta kada mijenjaš neki podatak Excel preračunava cijeli range (a nekada možda nepotrebno ako nema podataka)



[EDIT]: Da ne bi bilo zabune u formuli (zbog mog pojašnjenja iznad) umjesto dijela formule (argument 'col_num' unutar funkcije INDEX)
Code:
COLUMN('BAZA OS'!A1)
može se postaviti ista funkcija u obliku
Code:
COLUMN(A1)
isto vrijedi i za
Code:
ROW('BAZA OS'!1:1)

 
Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898



+66 Profil

icon Re: Vlookup ili slične funkcije13.07.2017. u 09:00 - pre 81 meseci
Citat:
BornaŠ: He, prosječan? Ni govora !
Eh, da ti u stvarnosti znaš koliki je Excel, što sve može i koliko je opsežan, shvatio bi o čemu ja govorim. (a da ne pričam o VBA u Excelu, Power Query - Power BI itd...). Ovo što ja koristim u svom povremenom radu u Excelu je samo kap vode u moru. :)
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije13.07.2017. u 12:58 - pre 81 meseci

Riješio sam , mada ne znam zasto mi samo u stupcu B nije dao promjenu raspona od 2-1500 .Svi ostali su odradili.
Kopirao sam formule u rikverc i primio je.
Tako da sve štima.
tnx
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije13.07.2017. u 13:02 - pre 81 meseci
Citat:
Ivek33:
Citat:
BornaŠ: He, prosječan? Ni govora !
Eh, da ti u stvarnosti znaš koliki je Excel, što sve može i koliko je opsežan, shvatio bi o čemu ja govorim. (a da ne pričam o VBA u Excelu, Power Query - Power BI itd...). Ovo što ja koristim u svom povremenom radu u Excelu je samo kap vode u moru. :)



A da, slutim da je excel skoro svemoćan.
S ovim tvojim linkovima mislim da se pomalo navlačim na excel.
Tnx.
Lijep pozdrav.
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije14.07.2017. u 12:21 - pre 81 meseci
Citat:
jaskojsako:
Pozdrav
mogao si putem pivot table dobiti report
pogledaj ovo da li ti odgovara


Pozdrav Jasmin !
Imaš pravo, dobio sam tražene podatke ali imam problem:

Napravio sam izračun UKUPNO u stupcu "F" ali Total za taj stupac daje netočan rezultat.
Možeš li, pls, vidjeti att

Tnx
Prikačeni fajlovi
 
Odgovor na temu

pera68
Bačka Palanka

Član broj: 98559
Poruke: 121
*.cws.sco.cisco.com.



+9 Profil

icon Re: Vlookup ili slične funkcije14.07.2017. u 12:28 - pre 81 meseci
Najbolje rešenje je da u bazi dodaš kolonu vrednosti u kojoj ćeš pomnožiti broj komada sa pojedinačnom vreednošću.
Prikačeni fajlovi
 
Odgovor na temu

Borna Š
novo
abab

Član broj: 335051
Poruke: 101
77.237.104.*



Profil

icon Re: Vlookup ili slične funkcije14.07.2017. u 12:38 - pre 81 meseci

To znam ali se u Pivotici može biti potrebani neki drugi izračun koji nije ovak jednostavan i ne radiš ga u bazi podataka.
Zanima me zašto dobivam pogrešan rezultat iako sam isključio podzbrojeve.
 
Odgovor na temu

[es] :: Office :: Excel :: Vlookup ili slične funkcije

Strane: 1 2

[ Pregleda: 4653 | Odgovora: 27 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.