Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - 38. Problemy przy filtrowaniu dat za pomocą Autofiltra

Artik · Posty: 3463

Sam temat interpretowania daty przez Excela i VBA to problem dość złożony i zawiły. A jeszcze lepszy pasztet przygotował nam M$ w przypadku filtrowania.

Zajmijmy się w pierwszym rzędzie autofiltrowaniem w Excelu, bezpośrednio w arkuszu.
Jak wiemy, XL przechowuje daty w arkuszu w postaci liczb. W trakcie filtrowania oczekiwalibyśmy co najmniej takiego traktowania dat przez XL. Jest z tym dość dziwnie.

Do rozważań przyjmijmy pewne warunki wstępne. Na komputerze mamy ustawienia:
- format krótkiej daty systemowej Win to "dd.MM.rrrr" (Start/Ustawienia/Panel sterowania/Opcje regionalne i językowe/przycisk [Dostosuj...]/zakładka Data/pole "Format daty krótkiej" )
- komórki w kolumnie z datami sformatowane jako "rrrr-mm-dd" (bo taki mam kaprys, ;-)

a poważniej - pozwoli sprawę nieco łatwiej wyjaśnić, mam nadzieję)

1. Gdy kryterium Autofiltra to dokładna data (jedna), Excel poszukuje w zakresie tekstu takiego, jaki podaliśmy w kryterium. Ponieważ na ogół wybieramy datę z listy rozwijanej lub ComboBox-a (w Niestandardowe...), więc format daty wybieranej jest taki jak wyświetlany w komórce, czyli na naszym przykładzie np. 2010-01-20. I dokładnie takiego tekstu poszukuje XL przy filtrowaniu. :shock:

Proszę dla przykładu negatywnego poszukać (przefiltrować) niby prawidłowej daty, bo zgodnej z ustawieniami systemowymi Win - w Niestandardowe, w pierwszym kryterium, wybrać "równa się" i "z palca" wpisać datę 20.01.2010.
2. Gdy kryterium Autofiltra jest zakresem dat, to XL zachowuje się już inaczej. Poszukuje liczb reprezentujących datę (o sposobie przechowywania dat w arkuszu wspomniałem wcześniej).

Myślę, że poniższy eksperyment wiele wyjaśni.
Niech jedna komórka w filtrowanej kolumnie zawiera datę wyświetlaną w formacie systemowym i niech to będzie data np. 20.01.2010. W innej komórce tej samej kolumny niech będzie ta sama data wyświetlana w formacie "rrrr-mm-dd", czyli 2010-01-20.
1. Proszę wybrać do filtrowania jedną datę (czyli 20 stycznia 2010 w jednym z powyższych formatów). Jak widać mimo, że obie komórki posiadają tę samą datę, wynik filtrowania pokazuje tylko jedną z nich.
2. Proszę wybrać kryterium Niestandardowe i w pierwszym warunku wybrać "jest większe niż lub równe" (o dacie - za chwilę), operator "I" oraz w drugim warunku "jest mniejsze niż lub równe". Dla obu warunków proszę spróbować wybrać datę 20 stycznia 2010 w jednolitym formacie (czyli raz "dd.mm.rrrr", a w następnym doświadczeniu "rrrr-mm-dd") i zatwierdzić. Teraz spróbować wybrać w/w datę dla jednego warunku w jednym, a dla drugiego w drugim formacie. Jak byśmy nie kombinowali to zawsze d... z tyłu. :-)

Wynikiem są zawsze dwie komórki, co w zasadzie jest tym czego od XL oczekiwaliśmy.

Z powyższych rozważań wynika, że w pewnych sytuacjach (gdy szukamy tylko jednej daty) bardzo istotnym jest, by wszystkie komórki do filtrowania miały ten sam format daty (warunek na ogół spełniony - chyba, że ktoś jest bałaganiarzem) oraz, by wpisując datę "z palca" wpisać ją w takim formacie jak filtrowana kolumna.

Uff. Zadyszki dostałem, a to dopiero połowa. :-)

Zerknijmy teraz co zarejestruje excelowa "nagrywarka".
Dla poszukiwanej jednej daty, w zależności od wybranego formatu, otrzymamy:

Kod:

Selection.AutoFilter Field:=1, Criteria1:="2010-01-20"
'lub
Selection.AutoFilter Field:=1, Criteria1:="20.01.2010"

Pierwsza linia, gdy wybraliśmy format daty wyświetlanej w kolumnie (rrrr-mm-dd), druga gdy wybraliśmy format daty systemowej (dd.MM.rrrr).
Dla zakresu dat:

Kod:

Selection.AutoFilter Field:=1, Criteria1:=">=20.01.2010", _
Operator:=xlAnd, Criteria2:="<=20.01.2010"

Proszę zauważyć, że w przypadku wybrania zakresu, bez względu na wybierany format, zawsze otrzymamy w nagranym kodzie datę w formacie systemowym.

To teraz zobaczmy jak to działa, gdy uruchomimy nagraną procedurę. Proszę tą procedurę rozbić na trzy odrębne - po jednej linii kodu do każdej. Można też, dla bardziej wprawnych, uruchomić nagraną procedurę w trybie krokowym. W dalszym opisie przyjmuję, że rozbito ją na trzy odrębne.
Uruchamiamy pierwszą. Jest OK, zgodnie z wcześniej wypowiedzianym zdaniem: "I dokładnie takiego tekstu poszukuje XL przy filtrowaniu.".
Uruchamiamy drugą. A tu zonk. :-(

Chcieliśmy, by wynikiem była data 20 stycznia 2010 w formacie systemowym "dd.MM.rrrr" czyli 20.01.2010. W kodzie jest tak sformatowana data, uwzględniamy cytat z pierwszej procedury (o tekście), czyli niby wszystko OK. A jednak nie bardzo, nie otrzymaliśmy oczekiwanego wyniku. Wyjaśnienie za chwilę.
Uruchamiamy trzecią procedurę. Tu też zonk. W kodzie, daty niby w formacie systemowym, ale wyniki jakieś marne, rzec by można - żadne. ;-)

W czym problem jeśli chodzi o drugą i trzecią procedurę?
Otóż VBA oczekuje od nas, owszem daty systemowej, ale w formacie amerykańskim. Wszak VB(A) wywodzi się z USA, więc nie powinno nas to tak bardzo dziwić.
I tu znowu lekka zadyma. Gdy poszukujemy dokładnej daty, oczywiście mówimy tu o kodzie, musimy ją doprowadzić do formatu "m/d/yyyy". Dokładnie takiego! Natomiast kiedy poszukujemy zakresu dat, akceptowany jest format przed chwilą wspomniany oraz "mm/dd/yyyy".

Reasumując.
1. Jeśli daty w filtrowanej kolumnie są wyświetlane w takim samym formacie jak krótka data systemowa Win stosując autofiltr za pomocą kodu VBA powinniśmy:
a) gdy poszukujemy dokładnej daty, w kryterium filtrowania przedstawić datę w formacie amerykańskim "m/d/yyyy" lub zastosować funkcję DateSerial.
b) gdy poszukujemy zakresu dat, w kryteriach użyć także format amerykański "m/d/yyyy" lub "mm/dd/yyyy".
2. Jeśli daty w filtrowanej kolumnie są wyświetlane w innym formacie niż systemowy powinniśmy:
a) gdy poszukujemy dokładnej daty, w kryterium filtrowania przedstawić datę w takim samym formacie jak jest widoczna w kolumnie,
b) gdy poszukujemy zakresu dat - formaty w kryteriach analogicznie jak w pkt. 1b.
3. Jeśli daty w filtrowanej kolumnie mają różne formaty (to dla bałaganiarzy ;-)

) a chcemy w wynikach autofiltra wyświetlić prawidłowe daty bez względu na format:
a) dla dokładnej daty zastosować filtrowanie z wykorzystaniem dwóch kryteriów, gdzie pierwsze "jest większe niż lub równe", a drugie "jest mniejsze niż lub równe" z oparatorem xlAnd podając tę samą datę dla obu,
b) dla zakresu dat - analogicznie jak w pkt 1b.

I to by było na tyle. :-)

Artykuł dedykuję Tomkowi, który mam nadzieję, od dziś nie będzie miał problemów z filtrowaniem dat. :mrgreen:

Artik

Wormsek · Excel Expert Zaproszone osoby: 1 Posty: 2657