Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 15446 Skopiuj do schowka 38. Problemy przy filtrowaniu dat za pomocą Autofiltra
Autor Wiadomość
Artik 



Wersja: Win Office 365
Posty: 8611
Wysłany: 27-01-2010, 03:25   38. Problemy przy filtrowaniu dat za pomocą Autofiltra

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
ID posta: 82667 Skopiuj do schowka
 
 
Wormsek 



Zaproszone osoby: 2
Wersja: Win Office 2016
Posty: 5267
Wysłany: 02-04-2012, 07:36   

Artik napisał/a:
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,

Mała ciekawostka. (może i uzupełnienie ;-) )

Do zmiennej DataRap przypisałem datę w formacie: "YYYY-MM-DD".
W kolumnie wszystkie daty w takim samym formacie.

W ex2010 (w 2003 i 2007 podobno nie mieli takiego problemu end-userzy) mimo już takich samych dat w kolumnie, musiałem jeszcze raz wymusić format daty, ponieważ i tak zmieniał mi niejawnie na format amerykański.
Czyli
Kod:
Criteria1:=Format(DataRap, "YYYY-MM-DD")

Czyli format nadawać dopiero we wrzucaniu do filtra.
_________________
Pozdro
Worm

FAQ - Najczęściej zadawane pytania.
JAK KORZYSTAĆ Z SZUKAJKI
Słownik funkcji

Znajdź nas na Facebook'u

A może fajny dodatek do excela?
ID posta: 154569 Skopiuj do schowka
 
 
Artik 



Wersja: Win Office 365
Posty: 8611
Wysłany: 22-04-2014, 00:41   

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.
ID posta: 224644 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe