Kryteria filtrowania poza nagłówkiem tabeli

by Marcin

Pytanie jakie otrzymałem od Małgorzaty:

Posiadam tabelę z następującymi kolumnami: nr dokumentu, branża, typ dokumentu, adresat, data, opis pisma. Tabela ma być filtrowana wg branży i typu dokumentu. Czy dałoby się zrobić tak, żeby warunki filtrowania znajdowały się powyżej tabeli, a nie bezpośrednio w nagłówku tabeli?

Problem (w moim odczuciu) jest głównie estetyczny, niezależnie bowiem od tego jak go rozwiążemy docelowa funkcjonalność pozostanie ta sama; przefiltrować tabelę z danymi według podanych kryteriów. Ponieważ jednak pewien jestem, że lepiej kiedy coś jest ładne niż brzydkie – zajmiemy się dzisiaj pytaniem Małgorzaty (rozwiązanie widzisz na animacji poniżej).

Kryteria filtrowania poza nagłówkiem tabeli
Najprostsza metoda przefiltrowania tabeli z danymi to filtr automatyczny.  Włączając go otrzymujemy tabelkę wyglądającą mniej więcej tak:

Tabela z włączonym filtrem automatycznym

Jest to nasz punkt wyjścia. Działa, ale …. nie podoba się. Małgorzata chce, aby tabelka i warunki filtrowania wyglądały tak:

Kryteria filtrowania przeniesione poza nagłówek tabeli

Do rozwiązania problemu wykorzystamy filtr zaawansowany i kilkanaście linijek kodu VBA.

Jak zapewne wiesz, parametry dla filtra zaawansowanego podajemy w komórkach znajdujących się poza tabelą z danymi. W naszym przypadku wyglądałoby to mniej więcej tak:

Sposób ustawienia parametrów filtra zaawansowanego

Ponieważ komórki z kryteriami dla filtra zaawansowanego muszą mieć określony układ, którym akurat nie chcemy być ograniczeni, podlinkowałem je do komórek, w których będziemy wybierać branżę i typ dokumentu.  Każda zmiana na liście rozwijanej powodować będzie oczywiście automatyczną zmianę w kryteriach filtra zaawansowanego.

Wszystko, co trzeba teraz zrobić, to automatyczne zastosowanie filtra zaawansowanego po każdej zmianie zawartości jednej z  komórek z warunkami filtrowania (branża lub typ dokumentu).

Zatrzymajmy się jeszcze na chwilę przy komórkach, w których będziemy wybierali branżę i typ dokumentu. Nasze rozwijane listy z opcjami do wyboru to po prostu zastosowanie sprawdzania poprawności.  Poszedłem tutaj po linii najmniejszego oporu i moje kryteria poprawności dla branży (dla typu dokumentu jest to dokładnie to samo podejście) wyglądają następująco:

Przygotowanie listy wyboru za pomocą sprawdzania poprawności

Dodatkowo, dla ułatwienia sobie pracy zdefiniowałem kilka nazw:

Zdefiniowane nazwy

Zakres „NaszeKryteria” podzieliłem jeszcze na dwie części:

Zdefiniowane nazwy

Arkusz mamy już przygotowany. Ostatni etap to kod VBA obsługujący zdarzenie „Change”. Nie wchodząc w zbytnie szczegóły, nasz kod będzie się wykonywał za każdym razem kiedy użytkownik zmieni zawartość komórki w arkuszu.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WybranaBranza As Range
Dim WybranyTyp As Range
Dim Kryteria As Range
Dim DoPrzefiltrowania As Range

Set WybranaBranza = Range("WybranaBranza")
Set WybranyTyp = Range("WybranyTypDokumentu")
Set DoPrzefiltrowania = Range("Dane") ' zakres danych, ktory bedziemy filtrowac

' Interesuja nasz tylko zmiany komórek, w których wybieramy branze oraz typ dokumentu.
' Reszta kodu zostanie wykonana tylko wtedy jeżeli zmiana nastąpiła, w którejś z tych
' dwóch komórek (jedna lub druga z naszych list rozwijanych)

If Target.Address = WybranaBranza.Address Or _
Target.Address = WybranyTyp.Address Then

  ' Jezeli wybrano "Wszystkie" w jednym i drugim polu to wyswietlamy po prostu
  ' cala (nieprzefiltrowana) liste i konczymy procedure

  If WybranaBranza.Value = "Wszystkie" And WybranyTyp.Value = "Wszystkie" Then
    ' robimy to tylko wtedy kiedy lista jest przefiltrowana. Proba "odfiltrowania"
    ' nieprzefiltrowanej listy skonczylaby sie bowiem bledem
    If (ActiveSheet.FilterMode) Then ShowAllData
    Exit Sub
  End If

  If WybranaBranza.Value = "Wszystkie" Then
    Set Kryteria = Range("NaszeKryteriaR") 'filtrujemy tylko dla typu dokumentu
  ElseIf WybranyTyp.Value = "Wszystkie" Then
    Set Kryteria = Range("NaszeKryteriaL") ' filtrujemy tylko dla wybranej branzy
  Else
    Set Kryteria = Range("NaszeKryteria")  ' filtrujemy dla branzy i typu dokumentu
  End If

' na koniec stosujemy filtr zaawansowany do naszego zakresu

DoPrzefiltrowania.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Kryteria, Unique:=False

End If

End Sub

Przykładowy plik z rozwiązaniem, jak zwykle, w załączniku (dostępny po zalogowaniu).

Download

Marcin

{ 10 comments… read them below or add one }

fisz

Linia najmniejszego oporu, a nie najmniejsza linia oporu.

Marcin

>Linia najmniejszego oporu, a nie najmniejsza linia oporu

Bardzo dziękuję za wytknięcie błędu. Już poprawiony. Swoją drogą świetnie, że ktoś zwraca uwagę na takie rzeczy.

Marcin

RobertP

Owa linia doczekała się nawet sporej blognotki z komentarzami
http://wo.blox.pl/2009/08/Po-najmniejszej-linii-oporu.html

michal13031986

Witam. A jak bym chciał to dostosować do swoich potrzeb, zmienić z A na np. 9 to w kolumnach się zmieniło, ale w opcjach wyboru przy Typ dokumentu:
jest nadal A, jak kliknę w nie to pojawia się pusto, a jak zrobić żeby to A zmienić na np. 9

Marcin

@michal

Zastosowałem po prostu dla tej komórki sprawdzanie poprawnosci, a jako kryterium wybrałem „lista”. Poczytaj sobie gdzie o sprawdzaniu poprawności właśnie.

Marcin

Quasi

Świetny trick Marcin, z reguły ktoś korzysta z filtra zaawansowanego po to, aby przenieść dane do osobnego arkusza/skoroszytu – tutaj filtrujemy w miejscu. Mógłbyś napisać w jakim programie utworzyłeś tą animację?

Marcin

Cześć Quasi,

Animacje robiłem w Camtasia Studio. Strasznie fajny programik, ale raczej drogi. Ja miałem to szczęście, że miałem jakąś starą wersję i kupiłem sobie upgrade za 1/2 ceny (i tak wychodzi nietanio).

Marcin

lemur6666

nie działa w Excel 2000. Otwiera się bez filtra na dodatek nie odświeża danych zakładam więc, że makro nie działa.

armal84

Bardzo fajnie opisane – na koniec pozwoliło mi to dojść do wniosku, że można to zrobić w jednej linijce kodu 😀
Dzięki za opis, naprawdę pomógł.

Ariano

Zarejestrowałem się na exceblog,a nie mogę sę zalogować. Dlaczegoraz mi wyskakuje strona dologowania i tak na przemian z drugą inną. Ile trwa logowanie się, co jest nie tak?

Leave a Comment

Previous post:

Next post: