Resenje excel problema?

[Majstor]

Hocu da postavim makro (record iz menija, ne preko Visual Basica)
koji ce da mi automatizuje sledeci proces:
1) filtrira tabelu po odredjenom kriterijumu
2) selektuje tako filtriranu tabelu
3) kopira selekciju u naredni sheet

Nema problema za tacku 1 i 3, vec je problem u tacki 2. Ne znam na koji nacin da automatizujem selekciju, s obzirom da ce u nekim slucajevima rezultat filtriranja biti 4 reda, a u nekim drugim 44 reda?? Da li mozda postoji neka funkcija na osnovu koje se vrsi selektovanje celija?

Nadam se da sam dobro pojasnio problem
tnx

 

[dbaja]

Da li si probao nefiltrirani deo tabele da selektujes i nazoves nekim imenom. Zatim posle filtriranja da pozoves po imenu tu selekciju i kopiras je na drugo mesto. Trebalo bi da se kopira samo filtrirani deo.

 

[VIA]

dbajin predlog radi sve dok se tabela ne proširi novim podacima, a da nisu insertovani.
Snimaj:
Selekcija prve gornje leve ćelije
Ctrl + Shift + strelica desno
Ctrl + Shift + strelica dole

 

[dbaja]

Nema veze, ako si kao bazu obelezio red sa imenima kolona - polja, sve redove sa podacima i poslednji prazan red. Tu selekciju nazovi "baza".
Nove redove insertuj ispred onog poslednjeg praznog i "baza" ce se sama prosirivati automatski.

 

[Majstor]

OK to sam resio. Sad imam drugi problem:

kako da automatizujem da u toj filtirarnoj selekciji prebrojim broj redova koji su selektovani?? Formulu mogu da napisem uz pomoc funkcije count, ali ne mogu da definisem opseg jer cu nekad da isfiltriram 5 a neki drugi put 55 redova!

 

[Majstor]

OK to sam resio. Sad imam drugi problem:

kako da automatizujem da u toj filtirarnoj selekciji prebrojim broj redova koji su selektovani??

odnosno filtrirani 🙂

 

[VIA]

Da li ti funkcija Subtotal završava posao - može da sabira, broji ... ćelije koje sadrže podatak a nisu filtrirane (ne i prazne, stoga je treba postaviti u kolonu koja uvek ima podatak).

@dbaja
Pa rekoh "... a da nisu insertovani"

 

[Majstor]

Da li ti funkcija Subtotal završava posao - može da sabira, broji ... ćelije koje sadrže podatak a nisu filtrirane (ne i prazne, stoga je treba postaviti u kolonu koja uvek ima podatak).

@dbaja
Pa rekoh "... a da nisu insertovani"

Ne znam kako se koristi, recimo u ovom slucaju (ako ne koristim filter) bi trebala da prebroji koliko celija sadrzi neki karakter (nonblank) u jednoj koloni (koja bi trebala da se uvecava kako se uvecava baza). Kako bi trebao da je definisem? Kolko vidim prvi karakter u funkciji predstavlja function number - a to ne znam sta znaci.

 

[VIA]

=Subtotal(3;A:A) prebroji sve ne filtrirane, ne prazne ćelije u A kolini.
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

 

[Majstor]

=Subtotal(3;A:A) prebroji sve ne filtrirane, ne prazne ćelije u A kolini.
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Ok, mada je ovde potrebno da definisem fiksni opseg (A:A), a u mom slucaju ce se kolona s vremena na vreme povecavati. Zato sam isao na to da prvo filtriram nonblank cells pa ih onda nekako prebrojim. Mada bi mi i ovako odgovaralo samo da mogu da definisem formulu na varijabilni opseg.

 

[VIA]

Pa onda, kako ti je dbaja predlagao, imenuj opseg - brojanu kolonu i unesi dato ime umesto A:A iz primera. Vodi računa da nove podatke uvek insertuješ - ne dopisuješ koloni.
Samo, ne vidim zašto ti ne igra cela kolona od koje ćeš oduzeti broj ćelija koje se uvek pojavljuju (naslov i sl.) i time dobiti tačan broj filtriranih redova.
Inače, za podatak u koliko ćelija imaš nekakav podatatk ti je dovoljna funkcija Counta (svih 11 podfunkcija Subtotala postoje i zasebno), Count za samo brojčane vrednosti.

 

[Majstor]

Pa onda, kako ti je dbaja predlagao, imenuj opseg - brojanu kolonu i unesi dato ime umesto A:A iz primera. Vodi računa da nove podatke uvek insertuješ - ne dopisuješ koloni.
Samo, ne vidim zašto ti ne igra cela kolona od koje ćeš oduzeti broj ćelija koje se uvek pojavljuju (naslov i sl.) i time dobiti tačan broj filtriranih redova.
Inače, za podatak u koliko ćelija imaš nekakav podatatk ti je dovoljna funkcija Counta (svih 11 podfunkcija Subtotala postoje i zasebno), Count za samo brojčane vrednosti.

Ne igra mi ovo nikako. Zasto?
Imam bazu od 5 kolona. U drugoj koloni korisnik sam bira odredjene redove dodeljujuci poljima (u drugoj koloni) odredjenu oznaku. Cilj je da se izracuna koliko je korisnik izabrao redova, zatim da se saberu sve vrednosti iz kolone 4 ali samo za redove koje je korisnik prethodno izabrao.
Ako primenim ovu tehniku koju si napisao, ne bi bio problem da se izracuna broj redova koji su izabrani, ali ne postoji nacin da se za te redove saberu vrednosti iz kolone 4 (ne bih da otvaram novi sheet u kome bih sa VLOOKUP vukao paralelne vrednosti zbog velicine fajla koji bi trebao biti sto manji).

Moj plan je bio sledeci:
1. otvorim makro
2. filtriram kolonu 2 po oznaci korisnika
3. selektujem kolonu 4 (tako filtrirane tabele) od pocetka do kraja (uz pomoc shift+ctrl)
4. insert/name/define, napisem 'broj', add, enter
5. zatvorim makro
6. napisem formule count(broj) i sum(broj)

problem je u tome sto pri razlicitim selekcijama korisnika makro uopste ne menja opseg 'broj', vec ostaje isti kao sto je definisan prvom selekcijom - ne znam zasto.

Znaci treba nekako da automatizujem da excel pri razlicitim selekcijama u koloni 2 tacno sabere korespondirajuce vrednosti u koloni 4, i taj zbir da prikaze u nekom polju.
Sad sam malo detaljnije definisao problem, ako neko ima resenje ili neka dodatna pitanja tu sam... 🙂

 

[VIA]

A zašto u ćelijama u kolini 4 nemaš upit if(ĆelijaIzKolone2-IstiRed <> "";ŠtaAkoDa;"") i samo negde izvlačiš zbir kolone 4?

Pri ponovnom defininisanju imena opsegu uz pomoć snimanja makroa obriši prvo postojeći, pa ponovo napravi novi sa istim imenom.
Čini mi se da bi mogao da prestaneš da se baviš snimanjem makroa i počneš da koristiš VB editor jer su mu mogućnosti daleko veće.

 

[Majstor]

A zašto u ćelijama u kolini 4 nemaš upit if(ĆelijaIzKolone2-IstiRed <> "";ŠtaAkoDa;"") i samo negde izvlačiš zbir kolone 4?

Ima vise razloga. Prvo, u koloni 4 treba uvek da mi budu originalne vrednosti bez obzira na selekciju u koloni 2, mada ok, mogu uvek negde da napravim pandan 4oj koloni i odatle da vucem zbir preko IF. Medjutim imam 5 000 redova tako da bi mi dodatnih 5 000 formula znatno uvelicalo izlazni file, koji bi trebao biti sto manji zbog prenosa preko neta, a s obzirom da se baza cesto filtrira veliki broj formula bi dosta usporio celu bazu.

Pri ponovnom defininisanju imena opsegu uz pomoć snimanja makroa obriši prvo postojeći, pa ponovo napravi novi sa istim imenom.
Čini mi se da bi mogao da prestaneš da se baviš snimanjem makroa i počneš da koristiš VB editor jer su mu mogućnosti daleko veće.

Neverovatno, pokusao sam prvo da obrisem stari kao sto si rekao, pa onda napravim novi kao sto sam napisao prethodno u postu, medjutim makro se ponasa kao da preskace ovaj korak. Pokusao sam i preko tastature i misem medjutim nece da mi zapamti ovaj jako bitan korak! Da li ja nesto ne radim kako treba, ne znam - ne bih rekao, kao da postoji propust u rekorderu.
Nije da ne zelim da koristim VB editor, vec ne poznajem dovoljno visual basic, tako da sam sada ogranicen u vremenu po tom pitanju i moram da se snalazim sa recorderom makroa. U principu sam zavrsio 90% onoga sto mi treba, samo me dosta zeza ovaj korak.

 

[Majstor]

uzeo sam malo i prckao po vb editoru, cisto sta mogu da zapazim logicki:

stvar je u tome sto editor kada definise okvir nekog opsega dodeljuje mu fiksne oznake granicnih polja, bez obzira sto sam ja taj opseg selektovao u recorder makrou preko shift+ctrl+donja strelica u nadi da ce bez obzira kolika kolona bude dugacka, on dodeliti ime koloni od njenog pocetka pa do kraja. Umesto toga editor u prvom pokusaju zapamti granicnike te prve kolone (npr R2C3: R10C3) i posle u narednim pokusajima on stalno koristi te iste granicnike bez obzira na razlicitu duzinu tih kolona. Odnosno:

ActiveWorkbook.Names("broj").Delete - brise staru tabelu (kolonu)
Range("B7").Select - ovo ja selektujem vrh kolone
Range(Selection, Selection.End(xlDown)).Select - povlaci selekciju do kraja kolone
ActiveWorkbook.Names.Add Name:="broj", RefersToR1C1:="=Sheet1!R7C2:R14C2" - e ovaj zadnji deo formule R7C2:R14C2 je uvek fiksan bez obzira na buduce razlicite duzine kolona, odnosno ne menja se kako se menja ova gornja selekcija.
Kako to da ispravim?

 

[VIA]

Zato sam ti i rekao da otvoriš editor
umesto RefersToR1C1:="=Sheet1!R7C2:R14C2" upiši selection
Inače, posle ove "intervencije" nestaje potreba za prethodnim brisanjem imena.

Savet: da bi ti se "veći" makroi brže izvršavali, na početku kôda unesi
Application.ScreenUpdating=False i na kraju vrati Application.ScreenUpdating=True, tako da Excel ne iscrtava ono što radi.

 

[Majstor]

Zato sam ti i rekao da otvoriš editor
umesto RefersToR1C1:="=Sheet1!R7C2:R14C2" upiši selection
Inače, posle ove "intervencije" nestaje potreba za prethodnim brisanjem imena.

Savet: da bi ti se "veći" makroi brže izvršavali, na početku kôda unesi
Application.ScreenUpdating=False i na kraju vrati Application.ScreenUpdating=True, tako da Excel ne iscrtava ono što radi.

Svaka cast VIA, sad je sve proradilo iz prve. Posebno mi je zanimljiv ovaj drugi savet, zaista radi brze a i izgleda bolje - super! 🙂

Ostalo mi je jos da napravim makro za snimanje, i makro za slanje tako napravljene i izracunate selekcije. Sta god da krenem eto zaglavljivanja 🙂
U prvom sheetu mi je ona baza od 5 kolona, a u drugom sheetu izfiltrirana selekcija sa svim izracunavanjima o koje sam spominjao ranije. Sad treba jedan makro da mi snimi samo drugi sheet, a drugi makro da ga salje preko interneta.
Pokusao sam da uradim copy drugog sheeta u novi worksheet i onda da ga snimim/posaljem. Medjutim velicina celog worksheeta je 4,5 MB, a kada iskopiram samo drugi sheet koji je dosta dosta manji od prvog (u kome se nalazi baza od 5000 redova), u novi worksheet i takvog ga snimim njegova velicina je 3.7 MB sto je apsurdno i neprihvatljivo jer sheet obicno ima oko 10 - 15 redova!! U cemu je stvar?? Ako uradim obican copy->paste texta iz drugog sheeta u novi worksheet njegova velicina onda ispadne 20 KB sto je realna velicina takvog fajla.
Pokusao sam i da ne kopiram drugi sheet u novi worksheet, vec u novi sheet umetnut u istom worksheetu, medjutim makro se tu gubi, jer taj novi umetnuti sheet nekad ima naziv 'sheet1' a nekad 'sheet2' itd....(ne pomaze relativno selektovanje umetnutog sheeta, jer onda nece da poziva menu; file/save ili file/send)