[es] - Evidencija i pregled troškova / padajuće liste i macro

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Evidencija i pregled troškova / padajuće liste i macro

^{01.10.2020. u 13:10 - pre 55 meseci}

Pokušavam da napravim nekakav pregled troškova. Imam različite kategorije sa različitim troškovima a sve je na mjesečnoj bazi. Troškove ručno dodajem tako što sabirem upisanu vrijednost prethodnih troškova sa vrijednošću novog troška. Na prvoj tablici mi je sve zbirno prikazano. Problem je što ima dosta pojedinačnih tablica (kategorija) pa je nezgrapno prebacivati se s jedne na drugu a tu je i ručno sabiranje. Namjeravam da na prvoj tablici gdje je cjelokupni pregled omogućim unos novog troška pomoću 3 padajuće liste, polja za unos iznosa i dugmeta s kojim bi taj iznos dodao u ćeliju koju sam odredio pomoću listi. Unešeni iznos bi se odmah vidio u osnovnoj tablici. Nažalost, za excel imam tek osnovna znanja, nedovoljna za ovaj projekat. Molio bih za pomoć kako da riješim ovaj problem.
Uz pomoć tutorijala i savjeta nađenih na internetu, napravio sam za primjer skraćenu verziju, kako sam zamislio da to funkcioniše. Kategorije sam zamijenio s kupcima, troškove s artiklima a mjesece sa kvartalima. Umjesto troškova sabirem uplate. Dodao sam padajuće liste i dugme za unos. Izabrane vrijednosti s listi sam prikazao u ćelijama: Kupac-B15, Artikal-C15, Kvartal-D15. U ćeliji B17 je sastavljen naziv/adresa odredišne ćelije (Tablica!ćelija). Kada u makro ubacim konkretno adresu odredišne ćelije sve funkcionira (tako je sad u primjeru) ali kad pokušam da umetnem adresu dobivenu pomoću polja sa sastavljenom adresom (Pregled!B17 - removana linija u makru) dobijem grešku Run-time error '13': – Type mismatch
Može li se ovo napraviti kako sam zamislio ili na bilo koji drugi način?
Svakako bi bilo dobro dodati mogućnost da su troškovi (artikli) vezani za kategoriju (kupci) pa da se ne prikazuju na padajućoj listi ako ih kupac nema. Ovako se može izabrati nepostojeći trošak (artikal) ali ciljno polje prikaže #/D pa se vidi da taj trošak (artikal) ne postoji za odabranu kategoriju (kupca). Isto bi bilo dobro da ima dugme za oduzimanje a da prije unosa iznosa oba traže potvrdu namjere preko nekog dijaloga (MsgBox).
Primjer: https://www.dropbox.com/s/fgaqk4lzxyjponu/Primjer1.xlsm?dl=0

Odgovor na temu

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

Moderator
Član broj: 25683
Poruke: 2286
*.mediaworksit.net.

Sajt: www.gowi.rs

+112 Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{02.10.2020. u 15:34 - pre 55 meseci}

Konkretan problem koji ti se javlja u makrou vezan je za korišćenje Range("Pregled!B17").Value
Rezultat ovoga je tekst (adresa) koju si formirao u B17, što možeš da proveriš u Immediate prozoru

Kad pokušaš da sabereš taj tekst sa numeričkom vrednošću unesenom u E13 dobiješ navedenu grešku "Type mismatch"

Rešenje je da dobijenu adresu koristiš (ponovo) u Range objektu da bi dobio vrednost iz ćeljije u koju i u upisuješ (npr. B2). Zbog preglednost ja bih to stavio u dva koraka

Code:

Sub Button4_Click()
cellAddress = Range("Pregled!B17").Text
Range(cellAddress).Value = Range(cellAddress).Value + Range("Pregled!E13").Value
End Sub

Što se tiče zavisnih drop down listi mislim da je bilo i na forumu, ali evo jedan zgodan video https://www.youtube.com/watch?...Sg&ab_channel=LeilaGharani.
U navedenom primeru ne vidim potrebu za tim - jer kako bi unosio novu kupovinu - kupac kupio neki artikl koji nije nikad do sada?

Ja bih formular za upis stavio na poseban list, ili iznad tabele pregleda kako bi tabela mogla nesmetano da raste u budućnosti.

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Prikačeni fajlovi

ImmediatWin.png - 57.08k

Odgovor na temu

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{02.10.2020. u 18:37 - pre 55 meseci}

Veliko hvala! Kao što sam napisao, vladam tek osnovama excela i sigurno ne bih sam došao do rješenja, bar ne zadugo. Tek što sam skočio od sreće, pronašao sam grešku. Sve savršeno radi dok sam kod kupca1. Greška je u formuli ćelije B17. "=CONCATENATE(Pregled!B15;"!";VLOOKUP(Pregled!E10;Šifarnik!E2:F5;2;FALSE);ROW(INDEX(Kupac1!A2:A4;MATCH(Pregled!C15;Kupac1!A2:A4;0))))"
Tu treba biti naziv izabrane tablice (B15) a sad je fiksno "Kupac1". Cijelo popodne pokušavam da nešto iskombiniram da dobijem traženi rezultat ali jednostavno ne znam kako to napraviti. Ako može pomoć oko ovoga bio bih jako zahvalan.

O formularu za unos na posebnom listu sam i sam razmišljao. Vidio sam tvoj primjer "UnosUList2.xlsm" i to mi se jako dopalo. Nisam još stigao da proučavam kako si to napravio a mislim da sam još daleko od toga da to sam napravim. Sad bih bio prezadovoljan da mi i ovako proradi primjer, pa da pokušam to iskoristiti u pravoj tablici.

Hvala i pozdrav

Odgovor na temu

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

Moderator
Član broj: 25683
Poruke: 2286
*.mediaworksit.net.

Sajt: www.gowi.rs

+112 Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{03.10.2020. u 10:53 - pre 55 meseci}

Možeš da zameniš u formuli "Kupac1!A2:A4" sa INDIRECT(Pregled!B15&"!A2:A4"). Formula je dosta komplikovana i teška za održavanje. Problem je i što je A2:A4 ospeg fiksiran što pretpostavljam da ne mora. Kad već koristiš VBA možda je lakše da to uradiš u okviru koda nego kroz formulu. Recimo da napraviš posebnu funkciju koja vraća ćeiju koju treba ažurirati i onda je iskoristiš u glavnoj proceduri

Code:

Option Explicit
Sub Button4_Click()
    Dim sh As Worksheet
    Dim cell As Range
    Set sh = ActiveSheet
    Set cell = GetCell(sh.Range("Pregled!B10").Text, sh.Range("Pregled!E10").Value, sh.Range("Pregled!B13").Text)
    cell.Value = cell.Value + sh.Range("Pregled!E13").Value
End Sub

Function GetCell(kupac As String, kvartal As Integer, artikal As String) As Range
    Dim shKupac As Worksheet
    Dim rw As Variant, cl As Variant

    Set shKupac = ThisWorkbook.Sheets(kupac)
    rw = Application.Match(artikal, shKupac.Columns(1), 0)
    cl = Application.Match(kvartal, shKupac.Rows(1), 0)
    Set GetCell = shKupac.Cells(rw, cl)
End Function

Ovaj kod treba doraditi da vraća neke greške kad artikal ne postoji za kupca ili da dodaje taj artikl u listu

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{03.10.2020. u 11:40 - pre 55 meseci}

Nešto definitivno nije dobro. Kao da samo radi sa Kupac1 i voćem. Kad izmjenjam 'Artikal' po kupcima ovako izgleda ćelija B17
Kupac2: Luk=#N/D; Krompir=#N/D; Kupus=#N/D; Jabuka=B2; Kruška=B3; Jagoda=#N/D; Banana=B4
Kupac3: Luk=#N/D; Krompir=#N/D; Kupus=#N/D; Jabuka=B2; Kruška=B3; Jagoda=#N/D; Banana=B4

Za Kupac1 izgleda sve OK. Za artikle na listi uredno dodaje iznos a 'Jagoda' koja nije na spisku ćelija B17 prikazuje #N/D.

Odgovor na temu

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{03.10.2020. u 12:14 - pre 55 meseci}

Probao sam u formulu ćelije zamijeniti "Kupac1!A2:A4" sa INDIRECT(Pregled!B15&"!A2:A4") i to izgleda radi savršeno.
Još ću sad probati i proučiti pa kad probam na pravoj tablici javim.
Veliko hvala na trudu i odvojenom vremenu.
Lijep pozdrav

Odgovor na temu

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

Moderator
Član broj: 25683
Poruke: 2286
*.mediaworksit.net.

Sajt: www.gowi.rs

+112 Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{03.10.2020. u 16:44 - pre 55 meseci}

Ako si ubacio kod iz prethodnog odgovora, formula u ćeliji B17 na listu pregled je irelevantna - možeš da je izbišeš, jer se adresa sada nalazi kroz kod. Proveri list Kupac2 i Pregled meni se čini da je uneseno kako treba u slučaju da artikal postoji za kupca. Kao što sam naveo potrebno je doraditi kad artikal za kupca ne posotoji.

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Prikačeni fajlovi

UnosForma.xlsm - 27.34k

Odgovor na temu

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{03.10.2020. u 18:50 - pre 55 meseci}

Svaka čast na strpljenju sa nama duducima. Ponovo sam pregledao i "UnosForma" radi kada postoji Artikal kod Kupca, u suprotnom izbaci grešku. Očigledno me je sadržaj B17 samo bunio.
Od onda pa do maloprije sam pokušavao primjeniti formulu iz B17 na pravu tablicu, čisto da vidim hoće li pravilno pokazivati na ćeliju za unos. Ta tablica je puno složenija i morao sam
neke prepravke raditi pa sam pravio dosta grešaka od tipfelera do pogrešnih formula. Na kraju sam sve povezao ali uvijek dobijem ispravnu tablicu i kolonu a red uvijek za 2 više.
Stavio sam u formulu -2 ali to mi je malo glupo. Zašto dobijem 2 reda više?

ovo je formula:
=CONCATENATE(PREGLED!C38;"!";VLOOKUP(PREGLED!E38;Šifarnik!F1:G12;2;FALSE);ROW(INDEX(INDIRECT(PREGLED!C38&"!B4:B25");MATCH(PREGLED!D38;INDIRECT(PREGLED!C38&"!A2:A18");0)))-2)

Za ovu doradu nema šansi da sam smislim ali to može sad i da sačeka.

LP

Odgovor na temu

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{03.10.2020. u 20:48 - pre 55 meseci}

Odlično! Uspio sam sve prebaciti u gotovo istovjetnu radnu knjigu i koliko vidim sve radi. Sad ostaje problem sa nepostojećim troškovima za određenu kategoriju.
Mislio sam da bi se to moglo riješiti pomoću nekog indeksa pa sam u šifarniku uz troškove dodao šifru troška i napravio još jednu listu Kategorija sa pripadajućom šifrom troška.
Vjerovatno postoji bolji način ali pokušaću nešto s ovim da napravim. Naravno tu je i dalje problem sa razlikom 2 za redove i stvarno bih volio da znam gdje sam pogriješio.
Onaj primitivni kalkulator je da olakša ručno dopisivanje iznosa i sad vjerovatno neće ni trebati.

Nova verzija:
https://www.dropbox.com/s/ga7i...q1ecw/TROSKOVI-proba.xlsm?dl=0

Još jedno banalno pitanje, mogu li nazivi sa više od 2 riječi, korišteni za kategorije i troškove, imati razmak između riječi? Stekao sam utisak da ne pa sam svugdje stavio "_".

LP

Odgovor na temu

kaza88
nezaposlen

Član broj: 345582
Poruke: 8

Profil

Re: Evidencija i pregled troškova / padajuće liste i macro

^{05.10.2020. u 19:08 - pre 55 meseci}

Stvarno me izluđuju formule i jedva ja to posložim kad je imalo složenija. Ipak sam uspio nekako dobiti drop listo ovisnu o drugoj listi. Sve je fino radilo i mislio sam da je to to. Danas sam prebacio na drugi PC i lista za trošak je najednom nestala iz ćelije. Stavio sam link na novi, ispravan fajl ali isto nisam siguran da će lista za trošak raditi Formula izgleda ovako:
=IF(MATCH(C32;Šifarnik!K1:K22;0)<16;OSOBE;INDIRECT(C32))
Kad pookušam da ponovo ubacim listu s ovom formulom izbaci mi grešku kao u prilogu.

Najgore je što je jutros bilo OK i na PC (2) na kojeg sam prebacio fajl. Još sam ga kopirao i na treći PC, preimenovao i opet je sve radilo. Kako sam uradio još neke nebitne izmjene kopirao sam taj fajl preko onog na prethodnom fajlu (PC (2)). Tada sam prvi put i primjetio da ne radi. Provjerio sam onaj na trećem PC i ni taj više nije radio. Sad sam napravio ovaj primjer i ovdje radi a hoće li kad se „skine“, ne znam.
Šta može biti uzrok ovakvom ponašanju. Da li je do fajla ili do excel-a?
Fajl:
https://www.dropbox.com/s/kvlh44p8f7en9o0/TROSKOVI_V1.xlsm?dl=0

Odgovor na temu