[es] - Razvrstavanje i sabiranje bez ponavljanja

Ivek33

Član broj: 66174
Poruke: 2898

+66 Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{18.04.2011. u 17:47 - pre 158 meseci}

Citat:

edinpepic: tako da mi sabira iznose za jednu osobu ali da se ne ponavlja njegovo ime dva i vise puta.

Pitam se što je sa šiframa koje znaju biti različite za jednu osobu (200 i 100)

Nešto mi ne štima sa organizacijom podataka. Po meni JMBG treba uvijek ići ispred imena ili iza imena i držati se tog rasporeda. Ti si miješao. Ovo govorim radi vlookup funkcije ako bi je koristio.

Moja ideja rješenja je da imenuješ potrebne raspone imenom radi lakšeg snalaženja u formulama.

koristio sam slijedeće formule na sheetu "Zbir"

B5: =INDEX(jmbg;MATCH(0;COUNTIF(jmbg;"<"&jmbg);0))
B6: =IF(COUNTIF(jmbg;">"&B5);INDEX(jmbg;MATCH(COUNTIF(jmbg;"<="&B5);COUNTIF(jmbg;"<"&jmbg);0));"")
formulu u B6 kopiraš prema dolje (btw: ove formule završavaš sa Ctrl+Shift+Enter)

nadalje:
C5: =IF(B5<>"";VLOOKUP(B5;lista!B4:I14;2;FALSE);"") => kopiraš dolje
I5: =IF(C5="";"";SUMIF(ime;"="&C5;neto)) => kopiraš dolje

jedini mogući problem ovdje može biti je taj što sve podatke na sheetu "zbir" skuplja jedan do drugoga, nema praznih redova, što možda u tvojoj koncepciji ne odgovara za daljnje računanje

vidi primjer

Prikačeni fajlovi

Primjer1.xls - 98.5k

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.923.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{18.04.2011. u 17:50 - pre 158 meseci}

Mozda nisam dao dobar primjer, sad kacim ono sto mi treba. Znaci, imam listu radnika i njihove ziro racun, banke i iznose za isplatu. E, sad da mi se ne bi ponavljali ime i ziroracun vise puta, kao sto je to u primjeru, nego da mi sabira iznose za jednog radnika, da ne ponavlja dakle njegovo ime i z.r. Konkretno. Dejan se pojavljuje 3 puta u primjeru, sa iznosima 20, 10 i 80, a trebao bi samo jednom i da bude iznos 110. Hvala.

Prikačeni fajlovi

Razvrstavanje.xls - 100k

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.923.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{18.04.2011. u 20:26 - pre 158 meseci}

Citat:

Ivek33: Pitam se što je sa šiframa koje znaju biti različite za jednu osobu (200 i 100)

Nešto mi ne štima sa organizacijom podataka. Po meni JMBG treba uvijek ići ispred imena ili iza imena i držati se tog rasporeda. Ti si miješao. Ovo govorim radi vlookup funkcije ako bi je koristio.

Moja ideja rješenja je da imenuješ potrebne raspone imenom radi lakšeg snalaženja u formulama.

koristio sam slijedeće formule na sheetu "Zbir"

B5: =INDEX(jmbg;MATCH(0;COUNTIF(jmbg;"<"&jmbg);0))
B6: =IF(COUNTIF(jmbg;">"&B5);INDEX(jmbg;MATCH(COUNTIF(jmbg;"<="&B5);COUNTIF(jmbg;"<"&jmbg);0));"")
formulu u B6 kopiraš prema dolje (btw: ove formule završavaš sa Ctrl+Shift+Enter)

nadalje:
C5: =IF(B5<>"";VLOOKUP(B5;lista!B4:I14;2;FALSE);"") => kopiraš dolje
I5: =IF(C5="";"";SUMIF(ime;"="&C5;neto)) => kopiraš dolje

jedini mogući problem ovdje može biti je taj što sve podatke na sheetu "zbir" skuplja jedan do drugoga, nema praznih redova, što možda u tvojoj koncepciji ne odgovara za daljnje računanje

vidi primjer

Ovo tvoje jeste dobro ali nije to sto meni treba. Ne razvrstava mi kako ja zelim, zbir jeste u redu ali nije razvrstavanje onakvo kakvo meni treba. Ako pogledas ovaj moj drugi primjer bice ti jasnije sto zelim i siguran sam da ces rijesit. Ako ne ti onda neko drugi garant, da ne prozivam sad odje sve vas koji znate dobro excell.

Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898

+66 Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{19.04.2011. u 08:50 - pre 158 meseci}

Citat:

edinpepic: Ovo tvoje jeste dobro ali nije to sto meni treba. ............ Ako pogledas ovaj moj drugi primjer bice ti jasnije sto zelim i siguran sam da ces rijesit. Ako ne ti onda neko drugi garant

Hmmm, ti čekaš da ti netko drugi riješi, a nisi ništa sam pokušao :(

Primjer koji sam ti prethodno dao na jednom listu (SUM) izvlači iz baze imena (bez duplikata), zbraja sve vrijednosti (skupno za svako ime) i na osnovu imena daje ŽR.

Ako ti ovo ne pomogne u attachu sorry, toliko od mene (ili te ja nisam shvatio kako treba)
pozdrav

Prikačeni fajlovi

Razvrstavanje_Ivek33.xls - 111.5k

Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898

+66 Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{19.04.2011. u 12:06 - pre 158 meseci}

Citat:

edinpepic: Mozda nisam dao dobar primjer, sad kacim ono sto mi treba.

Gledajući ovaj tvoj primjer sada sam tek shvatio da ti želiš na svakom Sheetu za svaku Banku posebno izlistati skupne elemente.
Malo sam proučavao kako bi se to moglo riješiti pa evo primjera kako bi ja to izveo.

Baza sheet je osnova, sheets Banka1, Banka2, Banka3 i Banka4 su samo pomoćni sheets u koje se kopira prema uvjetu za dotičnu banku.
Sheet SUM je finalno razvrstavanje (gdje nema duplih imena) po Bankama i zbraja iznose.

U primjeru je korišten Macro ( a po potrebi sheets Banka1, Banka2 itd možeš sakriti ako ti smetaju)

Code:
Sub KopirajRazvrstaj()

Dim myrange As Range
Sheets("baza").Select
criteria = "Banka 1"
Set myrange = Range("B2:H37")
Range("A1").Select
For Each C In myrange
If C.Value = criteria Then
x = C.Row
Range(Cells(x, 1), Cells(x, 8)).Copy Destination:=Sheets("Banka 1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next C
Sheets("baza").Select
criteria = "Banka 2"
Set myrange = Range("B2:H37")
Range("A1").Select
For Each C In myrange
If C.Value = criteria Then
x = C.Row
Range(Cells(x, 1), Cells(x, 17)).Copy Destination:=Sheets("Banka 2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next C
Sheets("baza").Select
criteria = "Banka 3"
Set myrange = Range("B2:H37")
Range("A1").Select
For Each C In myrange
If C.Value = criteria Then
x = C.Row
Range(Cells(x, 1), Cells(x, 8)).Copy Destination:=Sheets("Banka 3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next C
Sheets("baza").Select
criteria = "Banka 4"
Set myrange = Range("B2:H37")
Range("A1").Select
For Each C In myrange
If C.Value = criteria Then
x = C.Row
Range(Cells(x, 1), Cells(x, 8)).Copy Destination:=Sheets("Banka 4").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next
End Sub

Macro kopira sve elemente vezane uz CRITERIA na određeni Sheet. Tu se nalaze dupla imena a u sheetu SUM nema duplih imena ali su zbrojene vrijednosti za dotično ime. Dakle na sheetu SUM su razvrstane sve banke pojedinačno. Prije NOVOG razvrstavanja obrišeš podatke na sheet-ovima Banaka (ne i naslove podataka)

Nadam se da će ti ovo pomoći ako ne primejrom barem smjerom razmišljanja, a možda netko drugi ima elegantnije rješenje.
btw: vidi attach

Prikačeni fajlovi

Kopiraj-Razvrstaj-Sortiraj.xls - 130.5k

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{19.04.2011. u 13:55 - pre 158 meseci}

Ono prvo rjesenje bi bilo dobro, ali ima jedan problem kod njega. Dajem u prilogu jedno rjesenje kako sam ja uradio, ali treba mi ta formula koja ce da mi sabere. Vidjeces u prilogu, ima samo jedan list Banka, a tri lista obracuna. U listu banka se preuzimaju podaci, ako pogledas malo bolje vidjeces kako sam ja sve to uredio. Sve mi funkcionise samo sto mi ne sabira ya jednog radnika nego razdvaja. E to ne umijem da rijesim, i ono prvo tvoje rjesenje je bilo blizu, ustvari bilo bi to to, da se ne mijenja rang, kao što si ti stavio da ti je rang baza1 samo dok ima imena, e problem je ako se uzme rang praznih polja i onda ne funkcionišu tvoje formule. Da nije toga bile bi dobre. I da se ne mijenja rang, kao pto ces vidjet u ovom prilogu, moglo bi da bude dobro rješenje, ali vidjeces da se rang mijenja, i da je u obracun1 19 radnika, u obracun2 20 radniika, u obracun3 22 radnika. Zato su problem tvoje formule, a da je svudje isti rang, onda bi rješenje bilo to. Nadam se da je sad jasnije šta mi treba, i da će imat rješenja. Inače, Jpeca mi je pomogao jednom oko ovog razvrstavanja i ove formule što imam to su od njega, samo sam ih ja doradio prema mojim potrebama, ali su izvorno njegovo djelo. To je to, pa ako ima rješenja i da je bez makroa po potrebi bilo bi dobro, a ako ne, što ćemo, ručno ćemo se mučit :). I nije da baš želim da mi drugi odradi posao, ja bih da naučim, ne da mi se servira gotovo rješenje. Želim da naučim da bih što manje pitao, ali dok ne pitam i ne pokaže mi se nikad neću naučit. Hvala unaprijed na svakoj pomoći i na trudu.

Prikačeni fajlovi

Razvrstavanje.xls - 84.5k

Odgovor na temu

Ivek33

Član broj: 66174
Poruke: 2898

+66 Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{19.04.2011. u 19:59 - pre 158 meseci}

Citat:

edinpepic: I da se ne mijenja rang,....................... pa ako ima rješenja i da je bez makroa po potrebi bilo bi dobro,........................ Želim da naučim da bih što manje pitao,

Ne vidim što je problem staviti veliki range za "bazu" pa tako uzmeš u obzir više redova.
No dobro, evo novog rješenja (ako će valjati) a siguran sam da postoji jednostavnije rješenje no nije mi se dalo razmišljati.

Ne znam koliko ti mogu pojasniti moju ideju da shvatiš (kada već želiš učiti, a učimo cijeli život

U primjeru na sheetu "BANKA" dodao sam jednu Validation list u C2 koja vuče podatke sa sheeta BAZA a u stvari to su nazivi ostalih sheets na kojima vršiš pojedinačne obračune. Kreiraj veći range za validation list (ne znam koliko možeš imati tih obračuna)
Izborom podatka u C2-Validation List automatski se povlače podaci sa dotičnog Sheeta. Znači ako si u C2 izabrao "Obracun1" automatski će ti se povući svi podaci sa tog sheeta. Imena (bez duplikata).

Formule koje rade ovo povlačenje imena vezane su za tvoj stupac H u kojem si fiksirao neke raspone adresa
dakle u stupcu C na sheetu BANKA imaš dvije formule

C7: =INDEX(INDIRECT($H$2&$H$4);MATCH(0;COUNTIF(INDIRECT($H$2&$H$4);"<"&INDIRECT($H$2&$H$4));0))
C8: =IF(COUNTIF(INDIRECT($H$2&$H$4);">"&C7);INDEX(INDIRECT($H$2&$H$4);MATCH(COUNTIF(INDIRECT($H$2&$H$4);"<="&C7);COUNTIF(INDIRECT($H$2&$H$4);"<"&INDIRECT($H$2&$H$4));0));"") => kopiraš prema dolje
[edit]: ove formule završavaš sa Ctrl+Shift+Enter

U stupcu D imaš formulu. Ova formula zbraja sve vrijednosti za dotično ime sa sheeta "obracun1"
D7: =IF(C7<>"";SUMIF(INDIRECT($H$2&$H$4);"="&C7;INDIRECT($H$2&$H$5));"")

U stupcu E imaš formulu koja povlači ŽR. sa sheeta BAZA
E7: =IF(C7<>"";VLOOKUP(C7;baza;6;FALSE);"")

U stupcu F imaš formulu koja povlači oznaku Banke (banka1, banka2 itd...)
F7: =IF(C7<>"";VLOOKUP(C7;baza;7;FALSE);"")

I na kraju imaš filtar na stupcu F pomoću kojega filtriraš po bankama.

_{[Ovu poruku je menjao Ivek33 dana 19.04.2011. u 21:10 GMT+1]}

Prikačeni fajlovi

Razvrstavanje_Ivek33.xls - 79k

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{19.04.2011. u 21:56 - pre 158 meseci}

Hvala na trudu u svakom slucaju. Dok se ne javi neko sa drugim rjesenjem, ili ti ne pogledas malo bolje, iz ovoga cu neku kombinaciju napravit, imaju odje kod ovog tvog rjesenja dvije situacije koje su mi u igri pa cu nesto vidjet. Zakrpicu uglavnom nesto :). Hvala za ovo do sad a i za ubuduce :)

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{20.04.2011. u 16:51 - pre 158 meseci}

Jedna stvar mi nije jasna kako se desava. A to je da kada primijenim ovu formulu za razvrstavanje, C18=INDEX(INDIRECT($J$17&$J$19);MATCH(0;COUNTIF(INDIRECT($J$17&$J$19);"<"&INDIRECT($J$17&$J$19));0)), bude mi prazno polje, J17 je konstana, a J19 je varijabla koja se krece u rasponu J19=!$B$13:$B$162. Ako stavim raspon da je J19=!$B$13:$B$65, onda mi ispuni celiju i u redu je, inace su sve celije iznad B65 prazne. E to je to sto ti ja kazem, da nece da mi prihvati taj rang. Nije mi jasno zbog cega dolazi do toga?

_{[Ovu poruku je menjao edinpepic dana 20.04.2011. u 18:03 GMT+1]}

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{21.04.2011. u 21:03 - pre 158 meseci}

Evo u cemu je problem kod ovih formula. A prilazem i primjer da vidis kako izgleda. U listovima obracun1, obracun2 i obracun3 preuzima podatke iz baze po osnovu rednog broja pomocu if i vlookup funkcije. Problem je sljedeci i vidjeces, stavio sam u obracun3 bez if funkcije, i podatke iz njega ce da preuzima podatke u listu banka, a u ova dva lista je sa if funkcijom i za njih nece da preuzima podatke. Da napomenem, da sam if funkciju koristio radi preglednosti, jer kada nema if funkcije samo vlookup ako je prazna celija vidite i sami da je rezultat #N/A, a sa If funkcijom nema nista. Eto to je ta "mana" te formule. I druga stvar je sto razvrstava po abecedi a ja bih radje da razvrstava po redu odakle preuzima podatke, da ih ne razvrstava po abecedi. Nadam se da sam bio jasniji da predocim problem sa ovim formulama. Pozdrav.

Prikačeni fajlovi

Razvrstavanje1111.xls - 100k

Odgovor na temu

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

Moderator
Član broj: 25683
Poruke: 2268
*.wimax.verat.net.

Sajt: www.gowi.rs

+109 Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{21.04.2011. u 21:12 - pre 158 meseci}

Pošto se radi o nekakvoj aplikaciji, ne vidim razloga da se ne koristi VBA kod, slično kao što je Ivek predložio. Formule koje se koristi su prilično teške za održavanje i nepregledne.

Pogledao sam originalne formule na http://www.elitesecurity.org/t406400-0#2673105 i tamo kaže da će se prilikom prepisivanja (razvrstavanja) iz lista baza u list banka, prikazati onoliko redova koliko imaš u CountIF uslovu. Trenutno se kod tebe broje svi redovi u kojima je upisana određena banka, pa dolazi do dupliranja.

Jedan način da se to spreči je da se uvede dodatna (pomoćna) kolona u bazi koja utvrđuje redni broj pojavljivanja određene osobe u listi. Nakon toga treba prepisati samo one redove gde se ta osoba pojavljuje prvi put. Formula za redni broj pojavljivanja mogla bi da bude:

Code:
=COUNTIF($B$4:B4;B4)

Sa tom pomoćnom kolonom možemo da odredimo broj redova koje treba prepisati u list banka sa lista baza, kao kombinaciju uslova da je osoba klijent banke i da je redni broj pojavljivanja 1. Npr.

Code:
=COUNTIFS(Baza!$H$4:$H$39;A2;Baza!$I$4:$I$39;1)

Ovo će (uz odgovarajući dodatini uslovu u formuli za prepisivanje) u rešiti da se osobe prepisuju samo jednom. Sumiranje iznosa po osobi možeš da obaviš na samom listu banka ili da pripremiš na listu baza u dodatnoj koloni J i onda umesto kolone E prepišeš sumarne iznose iz kolone J

U prikačenom primeru izmene sam uneo samo na listu Banka1 (i naravno Baza, kao što je navedeno).

_{[Ovu poruku je menjao Jpeca dana 22.04.2011. u 16:40 GMT+1]}Ispravljena greška u prikačenoj verziji.

_{[Ovu poruku je menjao Jpeca dana 22.04.2011. u 16:41 GMT+1]}

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

Prikačeni fajlovi

Razvrstavanje (2).xls - 107.5k

Odgovor na temu

edinpepic

Član broj: 88417
Poruke: 63
*.crnagora.net.

Profil

Re: Razvrstavanje i sabiranje bez ponavljanja

^{22.04.2011. u 19:29 - pre 158 meseci}

To je bilo to sto mi je odgovaralo. Hvala jos jednom, eto nekako se desilo da mi prva pomoc oko programa koji sam radio, bude od Jpeca, a cini mi se da je ovo zadnje sto mi je trebalo za ovaj program, a eto i bas od tebe je stigla i ta pomoc. Hvala ivek33 takodje, i ostalima koji su mi nesebicno izlazili u susret i pomagali oko svega. Ali javicu se ja opet :). Hvala jos jednom.

Odgovor na temu