Kako sakriti (ne prikazati) prazne kolone a nakon filtriranja?

[ Jpeca @ 28.12.2012. 11:00 ] @

Ne pada mi napamet drugo rešenje nego preko makroa
a/ Dodaš negde jedan red sa subtotalima koji broje popunjene ćelije u koloni (za redove koji nisu hide). Nije bitno gde staviš subtotale, ali ako ti smeta stavi npr. da je font belo.

b/ Napraviš makro koji provera opseg subtotala (ulazni parametar) i sakriva kolone gde je rezultat subtotala 0, a otkriva one gde rezultat nije 0

Code:
Sub HideEmptyColumns(rngControl As Range)
' Makro sakriva kolone za 0 u odgovarajucoj celiji  iz rngControl
' rngControl je opseg celija  visine jednog reda sa subtotalima
' P.Jovanovic za elitesecurity.org
'
  Dim cl  As Integer

  For cl = 1 To rngControl.Count
     If rngControl.Cells(ColumnIndex:=cl).Value = 0 Then
       rngControl.Cells(ColumnIndex:=cl).EntireColumn.Hidden = True
     Else
       rngControl.Cells(ColumnIndex:=cl).EntireColumn.Hidden = False
     End If
  Next cl

End Sub

c/ Ovaj makro možeš da pozivaš na recalulate događaj worksheeta - on će se aktivirati na promenu filtera

U primeru je ceo kod u Sheet1 modulu

[ riskantni @ 28.12.2012. 11:18 ] @

Druže nisam siguran da je to izvodljivo na taj način na koji si ti zamislio, običan filter nema tu opciju. U makroe se i ne razumem puno ali možda može nešto da se uradti, a dotle je jedini način "peške"

[ savicbn59 @ 28.12.2012. 23:06 ] @

Ja sam postavio istu temu pod slicnim nazivom ali izgleda da nisamo dobro objasnio kroz primer sta zelim. Zato ponovo postavljam isto samo sa bolje objasnjenim primerima. Zahvaljujem se kolegama koji su pokusali da odgovore na prethodnu temu. POZDRAV SVIMA!!!!

[ Ivek33 @ 29.12.2012. 12:57 ] @

Citat:

savicbn59: Ja sam postavio istu temu pod slicnim nazivom ali izgleda da nisamo dobro objasnio kroz primer sta zelim. Zato ponovo postavljam isto samo sa bolje objasnjenim primerima. Zahvaljujem se kolegama koji su pokusali da odgovore na prethodnu temu. POZDRAV SVIMA!!!!

Jesi li ti uopće pogledao attach file koji je @Peca prikačio? To je toliko fantastično riješeno da sam oduševljen njegovim pristupom problemu, a to je upravo ono što si tražio (barem po tvom drugom attachu) . Ja sam pronašao jedan macro i prilagodio tvojoj želji pa pogledaj tutorijal korak po korak

- Kako sakriti stupce koji sadrže praznu ćeliju nakon filtriranja podataka

[edit]: naknadno sam uočio da ima problema u tvom file a i gdje griješiš. Nisi prilagodio range rasporedu podataka u drugom primjeru
Ako koristiš Excel 2007 tada snimi kao Macro-enabled Workbook

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

[ savicbn59 @ 29.12.2012. 14:02 ] @

Hvala puno poštovani Ivek33. Ja sam početnik u ovim stvarima i moram priznati da sa Makroima ne stojim najbolje. Zato mi rešenje koje je ponudio @Peca nije bilo najjasnije. Iako još uvek posedujem skromno znanje a tek 2 dana sam član ovog foruma odmah sam ukapirao da je @Peca osoba koja odlično poznaje ove stvari. Principijelno mi je jasno tvoje objašnjenje samo sad vidim da moram mnogo bolje proučiti rad sa makroima. Posle toga neće biti problema, barem sa ovakvim stvarima. Veliki pozdrav!!!!

[ 3okc @ 29.12.2012. 14:06 ] @

Ja sam nastavio od Pecinog rešenja i uklonio neke elemente koji su bili duplirani -pretpostavljam da su bili suvišni pošto nije uticalo da makro nastavi da radi. Jedino što sam izmenio jeste definicija opsega rngSubtotal, da ne bude apsolutna nego da se i ona određuje preko makroa.

Citat:

Jpeca: Ne pada mi napamet drugo rešenje nego preko makroa

Apropos alternativa, ono kako bih ja rešavao -a bez makroa- jeste neka izvedena tabela (pivot). Ubacio sam jednu sa 'slajserima' (predefinisani upiti) na imena gradova. Nije u potpunosti ovo što je traženo ali verujem da bi se moglo napraviti uz nešto više znanja (ali i vremena), nego što ja imam; tj makar dok se ne ovlada materijom.

[ Ivek33 @ 29.12.2012. 14:28 ] @

Citat:

savicbn59:Ja sam početnik u ovim stvarima i moram priznati da sa Makroima ne stojim najbolje.

OK, evo ti nazad tvoj file sa izmjenjenim kodom za macro. Ako želiš učiti makronaredbe ili barem nešto znati o njima pogledaj ove tutorijale možda ti mogu pomoći (naravno uz forumska pitanja)

- VBA za Excel početnike
- Makronaredbe u Excelu

btw: rješenje koje je ponudio @3okc je također interesantno, ako te zanimaju Pivot tablice kao i kombinacija Subtotal+PivotTable
pozz

[ savicbn59 @ 05.01.2013. 12:59 ] @

Nedavno sam postavio sličan problem na koji sam dobio odgovor. Rešenje je podrazumevalo upotrebu Makroa ili Pivot tabela. Sada me interesuje da li je ovo moguće rešiti bez Makroa i Pivot tabela. Rešenje može biti i "POLUATOMATSKO". U prilogu dostavljam i primer. Pozdrav Svima!!!

[ 3okc @ 05.01.2013. 17:58 ] @

Rešenje matričnom formulom

Formula za zaglavlje:

Code:
=IFERROR(INDEX({""\"Pon"\"Uto"\"Sre"\"Čet"\"Pet"\"Sub"\"Ned"};;SMALL(IF(INDEX(B1:H15;MATCH(B17;A1:A15;0););
COLUMN(B1:H15);FALSE);COLUMN(INDIRECT("1:"&COUNT(INDEX(B1:H15;MATCH(B17;A1:A15;0);))))));"")

Formula za filtrirane podatke:

Code:
=IFERROR(INDEX(INDEX(A1:H15;MATCH(B17;A1:A15;0););;SMALL(IF(INDEX(B1:H15;MATCH(B17;A1:A15;0););
COLUMN(B1:H15);FALSE);COLUMN(INDIRECT("1:"&COUNT(INDEX(B1:H15;MATCH(B17;A1:A15;0);))))));"")

Obe matrične formule unose se preko obeležnog opsega (tzv Range Entered Array Formula), što je mala ali bitna razlika u odnosu na matrične formule koje unesemo u pojedinačnu ćeliju i onda eventualno kopiramo preko opsega.

_{[Ovu poruku je menjao 3okc dana 05.01.2013. u 20:42 GMT+1]}

[ savicbn59 @ 05.01.2013. 19:21 ] @

Hvala puno 3okc, ovo je izgleda baš ono što sam tražio. Međitim, nisam monogo vešt u ovim stvarima pa zato ovo moram da proverim na primeru sa mnogo više redova i kolona koji mi je potreban. Pretpostavljam da je princip isti. Kada to budem proverio jeviću se, pozdrav!!!

[ 3okc @ 05.01.2013. 19:48 ] @

Vidi & proveri.

Formula, ovakva kakva je, radiće ti za 15 redova podataka. -Ako treba više, izmeni samo sve pojave brojem koji ti treba, najbolje sa ReplaceAll: sve 15 sa npr 1000.

U B17 je referenca koja se ispituje, to je jasno.

Za broj kolona je već druga priča, formule nisu baš toliko robusne i to nećeš moći da proširuješ bez temeljne izmene.

[ Ivek33 @ 05.01.2013. 20:19 ] @

Citat:

3okc: Formula, ovakva kakva je, radiće ti za 15 redova podataka.

@3okc, izvanredna formula.
Međutim proučavao sam zašto ne filtrira podatke za zadnji popunjeni red, u ovom slučaju za Instanbul (Istambul).
Probao sam dodati ispod nove podatke i radi za njih ali za "Istambul" ne radi?

Jel to zato što je pogrešno napisano ime

[ 3okc @ 05.01.2013. 20:44 ] @

Toliko napredna baš i nije.. ;)
Nisam neki ekspert ali verujem da je Istambul pravilno; Istanbul je valjda 'original' u eng. transkripciji - tipa Pariz/Paris.

Do greške dolazi jer poslednja ćelija tamo nije prazna: sadrži jedan apostrof!

Ako i to spada u zahtev, modifikacija je da se onaj IF(INDEX(... pretvori u IF(ISNUMBER(INDEX(...)

[ Ivek33 @ 05.01.2013. 21:00 ] @

Citat:

3okc: Toliko napredna baš i nije.................Do greške dolazi jer poslednja ćelija tamo nije prazna: sadrži jedan apostrof!

Ma formula je odlična, nisam uočio taj apostrof

pozz

[ savicbn59 @ 05.01.2013. 23:01 ] @

Pre nego što sam video odgovore isprobao sam za više redova i kolona. Nije bilo problema kada su u pitanju redovi, međutim broj kolona nisam mogao da povećam. Meni u praksi treba 40-ak kolona pa ako bi mogao i to da mi eventualno rešiš bilo bi sjajno. Ineče ideja je zaista odlična, jer se mnogo brže i praktičnije radi nego preko makroa, Pozdrav!!!

[ 3okc @ 07.01.2013. 18:02 ] @

Izmenio sam da radi sa proizvoljnim dimenzijama tako što sam pol.podatke proglasio za dinamičku tabelu a u formulama koristim strukturne reference.

Code:

=IFERROR(INDEX(Table1[#Headers];;SMALL(IF(ISNUMBER(INDEX(Table1[[#All];[Pon]:[Ned]];MATCH(A1;Table1[[#All];[grad/dan]];0);));COLUMN(Table1[[#All];[Pon]:[Ned]]);FALSE);COLUMN(INDIRECT("1:"&COUNT(INDEX(Table1[[#All];[Pon]:[Ned]];MATCH(A1;Table1[[#All];[grad/dan]];0);))))));"")

=IFERROR(INDEX(INDEX(Table1[#All];MATCH(A1;Table1[[#All];[grad/dan]];0););;SMALL(IF(ISNUMBER(INDEX(Table1[[#All];[Pon]:[Ned]];MATCH(A1;Table1[[#All];[grad/dan]];0);));COLUMN(Table1[[#All];[Pon]:[Ned]]);FALSE);COLUMN(INDIRECT("1:"&COUNT(INDEX(Table1[[#All];[Pon]:[Ned]];MATCH(A1;Table1[[#All];[grad/dan]];0);))))));"")

Ako zameniš [Pon]:[Ned] u formulama sa proizvoljnom prvom i poslednjom kolonom, trebalo bi da radi; samo, prilagodi uvek i matričnu formulu da isprati širinu tabele.

Naravno, možeš krenuti i da proširuješ postojeću, i da menjaš podatke, ali zbog matrične formule u prvim redovima nećeš moći da umećeš nove kolone kao obično nego Select preko tabele, desni klik, Insert > Table Columns.

[ savicbn59 @ 07.01.2013. 20:44 ] @

Uvaženi 3okc, mnogo hvala na trudu. Međutim, kada povećam broj kolona pojavljuje mi se samo 7 (sedam) prvih karaktera, a na dalje u sledećim kolonama samo se ponavlja 1. (PRVI) od tih sedam karaktera. Do ovoga sam bio došao i sam onako (peške). Iako se ne razumem u Dinamičke formule, logički sam zaključio da je to moćna stvar. Verovatno mi fali samo jedan mali korak da do kraja rešim problem. Potrebna pomoć, Pozdrav!!!

[ Ivek33 @ 07.01.2013. 21:48 ] @

Citat:

savicbn59: kada povećam broj kolona pojavljuje mi se samo 7 (sedam) prvih karaktera, a na dalje u sledećim kolonama samo se ponavlja 1. (PRVI) od tih sedam karaktera. ................... Potrebna pomoć, Pozdrav!!!

Dok ti se @3okc ne javi evo male pomoći.

Kao prvo savjetujem ti da uvijek napraviš tablicu (mrežu) na rasponu na kojem radiš ako ne smeta radi lakše uočljivosti i snalaženja.

Pokušat ću ti laičkim jezikom pojasniti korake.

- Dakle imaš formulu koju ti je @3okc dao (btw: formula je izvanredna za promjenjivi range)
- izvršiš sve potrebne promjene (kao i što jesi u tvom primjeru)
- Idemo za prvi red B1:M1 i prvu formulu. Selektiraš cijeli raspon ćelija u kojima želiš rezultat, dakle B1:M1. Zalijepiš formulu u prvu ćeliju tj. u polje u kojem se unose fomule i pritisneš kombinaciju Ctrl+Shift+Enter. Sada se formula pojavila u svm ćelijama koje si selektirao.
- Za drugi red selektiraš B2:M2 i drugu formulu zalijepiš i opet završiš kao ARRAY sa Ctrl+Shift+Enter

I na kraju nakon filtriranja imaš željene rezultate.
Nadam se da sam uspio pojasniti osnove

pozz

[ 3okc @ 08.01.2013. 09:06 ] @

Kao što već rekoh, radi se o matričnoj formuli koja se unosi preko opsega; postoje dve vrste: 1/ Cell Entered i 2/ Range Entered na to obrati pažnju. Ovo kako si ti uneo je prvi slučaj, unutar pojedinačne ćelije i potom samo umnoženo.

Kao što i @Ivek33 reče, moraš obeležiti opseg prvo. Sledeći korak: pritisni F2 da uđeš u prvu ćeliju i tu unesi formulu. Izlazak/unos ostaje isti: Ctrl+Shift+Enter.

Što se formatiranja tiče, bolji je efekat da to radiš uslovno (umesto eksplicitno), tako će stvarno izgledati kao filtriranje.

Ovo zapravo i jeste 'pivot' izveštaj samo u potpunosti odrađen formulama -što se ne preporučuje upravo zbog mogućnosti da dođe do previda.

[ savicbn59 @ 08.01.2013. 12:48 ] @

Poštovani Ivek33, Odločno si objasnio i sve radi OK. Sada pitanje(molba) za još nešto u vezi sa ovim. Da li je moguće filtriranje po istom principu ako osim brojeva u kolonama imamo i tekst i formule (VIDI PRIMER), VELIKI POZDRAV!!!

[ 3okc @ 08.01.2013. 13:06 ] @

Samo zameni ISNUMBER sa LEN - najbolje jednim ReplaceAll, tako nećeš pogrešiti.

[ savicbn59 @ 08.01.2013. 15:57 ] @

Poštovani 3okc, Zamenio sam "INSUMBER" sa "LEN" i sve je OK. Ostao mi je još jedan(poslednji) problem, mada nisam siguran da je rešiv. Pošto imam 3 naziva "Atina" sa različitim karakterima, na filtriranje reaguje samo "prva Atina", dok ostale 2 ne reaguju. Vidi primer u prilogu. VELIKI POZDRAV!!!!!!