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

Artik · Artik Wersja: Win Office 365 Posty: 10702

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 · Zaproszone osoby: 2 Wersja: Win Office 2016 Posty: 5295

Artik · Artik Wersja: Win Office 365 Posty: 10702

Tytułem uzupełnienia, tekst jaki otrzymałem od Bill Szysza.

Cytat:

Chciałem tylko zwrócić uwagę że problem nie dotyczy wyłącznie dat. To samo można powiedziec o liczbach co dość łatwo sprawdzić ( mówimy o dziwnym traktowaniu przez filtr niestandardowy kryterium "Równa się"). Systemowy format czy to liczb czy dat nie ma tu nic do rzeczy. Liczy sie wyłącznie to jaki jest wybrany w Excelu.
Dla przykładu gdybyśmy spróbowali przefiltrować taką listę
4000,2 - format ogólny
4 000,20 - format liczbowy z separtorem tysięcy i dwoma miejscami po przecinku
4000,20 - format liczbowy z dwoma miejscami po przecinku
pod kątem "równa się" 4000,2 to otrzymamy wyłącznie pierwszy element. Znaczy to dokładnie to samo co w przypadku opisywanego przez Artika filtrowania dat czyli szukanie nie liczb a dokładnie takiego ciągu tekstowego jaki wpisaliśmy w filtrze.
Co ciekawe, nie da się ani dat ani liczb wyszukiwać pod kątem "zawiera" czy "kończy się na" i podobnych. Czyli w tych przypadkach excel prawidłowo rozpoznaje datę (liczbę) i nie traktuje jej jak tekstu.

Maciej Gonet · Excel Expert Wersja: Win Office 365 Posty: 9330

Temat już stary i dawno nie ruszany, ale ponieważ Artik odwołał się do niego niedawno, to przeczytałem i postanowiłem się odnieść, bo parę szczegółów nie zostało dopowiedzianych.

Daty mogą być wprowadzane do kryterium autofiltra kilkoma sposobami:
1) jako pojedyncze kryterium lub kryterium złożone z dwóch elementów połączonych operatorami xlAnd lub xlOr,
2) jako kryterium w postaci tablicy Array lub innej wierszowej, z zastosowaniem operatora xlFiterValues,
3) jako kryterium określone nazwą (lub liczbą) z zastosowaniem operatora xlFilterDynamic,
4) niestandardowo za pomocą operatorów xlBottom10Items, xlBottom10Percent, xlTop10Items, xlTop10Percent (nie da się użyć tych operatorów do dat w arkuszu, ale w VBA można).
Każdy z tych sposobów wprowadzania dat ma swoją specyfikę związaną z interpretacją używanych formatów dat. W pewnym stopniu zachowanie programu zależy również od używanej wersji Excela.

Ad 1) W ramach tego punktu można wyróżnić dwa podpunkty:
a) gdy szukamy konkretnej pojedynczej daty zapisujemy ją w Criteria1 lub Criteria2 jako tekst w cudzysłowie poprzedzony znakiem równości albo bez tego znaku,
b) gdy szukamy przedziału dat, również wykorzystujemy Criteria1 lub Criteria2, lecz poprzedzamy datę graniczną znakiem relacji nierówności <, <=,>, >= lub <>, ujmując całość w cudzysłów. Gdy używamy nierówności, możemy połączyć co najwyżej 2 kryteria.
W przypadku a) data w VBA powinna być zapisana jako tekst w formacie daty zgodnej co do znaku z wyświetlaną w arkuszu. W arkuszu zapis może mieć format daty lub tekstowy. Jeżeli w VBA zastosujemy generator daty w postaci funkcji np. DateSerial, to należy pamiętać, że wynikiem jest data w formacie systemowym, którą należy jeszcze obudować funkcją CStr lub Str albo w inny sposób skonwertować na tekst (na przykład dopisując tekstowo z przodu znak =).
Jeśli do konwersji daty stosujemy funkcje CDate lub DateValue, to z reguły następuje tylko zamiana separatorów na systemowe i ewentualnie zamiana kolejności dmr na rmd, jeśli wymaga tego kolejność elementów daty systemowej. Jeśli tak uzyskana data jest niepoprawna, dopiero wtedy następuje zamiana kolejności dnia i miesiąca. Z tego powodu w europejskich systemach z kolejnością dmr lub rmd daty amerykańskie z kolejnością mdr są często interpretowane niepoprawnie. Również i w tym przypadku potrzebna jest jeszcze konwersja na tekst.
W przypadku b) mamy zupełnie inną sytuację. Tu wymagany jest amerykański format daty

Kod:

m/d/yyyy

lub format

Kod:

yyyy/m/d

(w obu przypadkach dopuszczalne jest użycie zer wiodących przy miesiącu i dniu). Zamiast ukośnika separatorem może być łącznik (minus) i można te separatory dowolnie mieszać. Taka data jest zgodna z każdym formatem daty w arkuszu (byle by to była data, a nie tekst).
Tu mogą przeżyć rozczarowanie osoby, które zarejestrują sobie ustawienia takiego filtra stosując rejestrator makr. Zarejestruje on daty zgodne z lokalnym ustawieniem systemowym, ale to nie zawsze będzie działało.

Ad 2) W Excelu konstruując kryterium daty możemy wskazywać daty grupowo, jako obejmujące wybrany rok, miesiąc lub dzień. Odbywa się to na zasadzie zawężania wyboru, to znaczy wybrany miesiąc dotyczy zawsze konkretnego roku, a dzień - konkretnego miesiąca i roku. W VBA takie kryterium ma postać tablicy wierszowej (Array lub kompatybilnej) złożonej z występujących na przemian liczb (kodów, określających jak interpretować datę) i samych dat ujętych w cudzysłów. Takich par może być kilka, a cała tablica musi być zapisana jako Criteria2. W tablicy mogą występować duplikaty, nie powoduje to żadnych komplikacji.
Operatorem jest w tym przypadku xlFiterValues.
Kody mogą odnosić się do dat i do czasu, w sumie jest ich 6:

Kod:

0 - lata, 1 - miesiące, 2 - dni, 3 - godziny, 4 - minuty, 5 - sekundy.

Kody te są zapisywane zwykle jako liczby, ale dopuszczalny jest też format tekstowy. Data musi być podana w jednym z akceptowalnych formatów opisanych w punkcie 1 b). Data użyta w programie musi być pełna i w całości poprawna, chociaż przy kodzie 0 brany pod uwagę jest tylko rok, a przy kodzie 1 - miesiąc i rok.
Criteria1 można pominąć albo zapisać w nim zwykłą tablicę liczb (docelowo interpretowanych jako teksty) lub tekstów. Przez "zwykłą tablicę" rozumiem tu tablicę, w której każdy element oznacza siebie, nie ma kodów o specjalnym znaczeniu. Tu począwszy od wersji 2010 nastąpiła zmiana interpretacji. W Excelu 2007 liczby z częścią dziesiętną trzeba było zapisywać w formacie amerykańskim (z kropką dziesiętną), a od Excela 2010 liczby z częścią dziesiętną trzeba zapisywać jako teksty z przecinkiem.
Można łączyć daty i inne dane w jednym kryterium złożonym, zapisując daty w Criteria2, a inne dane w Criteria1. W części Criteria1 mogą też wystąpić pojedyncze daty, ale będą one zgodne tylko z datami sformatowanymi jako tekst w arkuszu i zgodność musi być dokładna co do znaku. W tym przypadku przy generowaniu dat za pomocą funkcji nie jest konieczna jawna konwersja na tekst.
W przypadku operatora xlFiterValues nie można poprzedzać danych znakiem nierówności.

Ad 3) Data może być również określona w sposób dynamiczny względem daty bieżącej lub ogólnie kalendarza. W tym przypadku nie podaje się żadnej konkretnej daty, lecz wartości parametrów

Kod:

Operator:=xlFilterDynamic

oraz jako Criteria1 jedną z 34 wartości liczbowych lub nazw określających sposób interpretacji daty, na przykład xlFilterYesterday (kod nr 2). Można użyć tylko jednego kryterium. Kryteria odnoszące się do miesięcy (w tej grupie) np. xlFilterAllDatesInPeriodOctober nie biorą pod uwagę roku. Zaletą kryteriów z tej grupy jest brak problemów z formatem daty, wadą – brak elastyczności (na przykład jest zdefiniowane kryterium na jutro, ale na pojutrze już nie ma).

Ad 4) Kryteria z grup Top i Bottom są w zasadzie przeznaczone do liczb. Gdy konfigurujemy filtr dat w arkuszu, takie opcje się nie pojawiają. Ale zapisując kryteria filtra w VBA można taki filtr zdefiniować wpisując jako Criteria1 liczbę wartości do odfiltrowania (może to być zwykła liczba lub liczba w cudzysłowie, ale nie może być poprzedzona znakiem równości wewnątrz cudzysłowu).

Powyższe uwagi zostały sformułowane na podstawie analizy działania Excela w wersjach 2007-2016. Są one rozszerzeniem, choć nie tak barwnym, opisu podanego przez Artika i w drobnych szczegółach różnią się od tamtego opisu. Nie mam w tej chwili możliwości weryfikacji, czy nowsze wersje Excela wprowadziły jakieś zmiany w tym względzie. Jeśli ktoś ma ochotę sprawdzić, to zachęcam do testów i podzielenia się uwagami.