Użycie filtru zaawansowanego w programie Excel do rozwiązania problemu Morta. Chociaż zwykłe filtry stały się bardziej wydajne, nadal zdarza się, że filtr zaawansowany może wykonać pewne sztuczki, których inni nie potrafią.
Obejrzyj wideo
- Filtr zaawansowany jest bardziej „zaawansowany” niż zwykły filtr, ponieważ:
- 1) Może kopiować do nowego zakresu
- 2) Możesz zbudować bardziej złożone kryteria, takie jak Pole 1 = A lub Pole 2 = A
- 3) Jest szybki
- Mort próbuje przetworzyć 100 tys. Wierszy w VBA, przeglądając rekordy lub używając tablicy
- Korzystanie z wbudowanych funkcji programu Excel zawsze będzie szybsze niż pisanie własnego kodu.
- Potrzebujesz zakresu wejściowego, a następnie zakresu kryteriów i / lub zakresu wyjściowego
- Dla zakresu wejściowego: pojedynczy wiersz nagłówków nad danymi
- Dodaj tymczasowy wiersz na nagłówki
- Dla zakresu wyjściowego: wiersz nagłówków kolumn, które chcesz wyodrębnić
- Dla zakresu kryteriów: nagłówki w wierszu 1, wartości począwszy od wiersza 2
- Komplikacja: starsze wersje programu Excel nie pozwalały na umieszczenie zakresu wyjściowego na innym arkuszu
- Jeśli piszesz makro, które może zostać uruchomione w 2003 r., Użyj nazwanego zakresu do obejścia zakresu wejściowego
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2060: Zaawansowany filtr programu Excel
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie zadał Mort. Mort, ma 100 000 wierszy danych i interesują go kolumny A, B i D, w których kolumna C pasuje do konkretnego roku. Chce więc, aby osoba wprowadziła rok, a następnie otrzymała kolumny A, B i D. A Mort ma trochę języka VBA, w którym używa do tego tablic, a ja powiedziałem: „Poczekaj sekundę, wiesz, zaawansowany filtr zrobiłby to. dużo lepiej ”. W porządku, a teraz, żeby przejrzeć, wróciłem, przejrzałem swoje filmy. Dawno nie omawiałem zaawansowanego filtra, więc powinniśmy o tym porozmawiać.
Filtr zaawansowany wymaga zakresu wejściowego, a następnie przynajmniej jednego z nich: zakresu kryteriów lub zakresu wyjściowego. Chociaż dzisiaj będziemy używać obu tych. W porządku, więc zakres wejściowy to twoje dane i musisz mieć nagłówki nad danymi. Więc Mort nie ma nagłówków, więc tymczasowo wstawię tutaj wiersz i po prostu postąpię tak, jak Pole 1. Mort wie, jakie są jego dane, więc może umieścić tam prawdziwe nagłówki. I nie używamy niczego, co się nazywa - te dane w kolumnach od E do O, więc nie muszę tam dodawać nagłówków, dobrze? Więc teraz zakres od A1 do D, 100000 staje się moim zakresem wejściowym. A następnie zakres wyjściowy i zakres kryteriów - Cóż, zakres wyjściowy to tylko lista nagłówków, które chcesz. Więc wstawię tutaj zakres wyjściowy i nie potrzebujemy Pola 3, więcPo prostu zdejmę to na bok. Więc teraz ten zakres, od A1 do C1, staje się moim zakresem wyjściowym, który informuje program Excel, które pola chcę z zakresu wejściowego. I mogą być w innej kolejności, jeśli chcesz zmienić kolejność rzeczy, na przykład jeśli chcę najpierw Pole 4, a następnie Pole 1, a następnie Pole 2. I znowu, byłyby to prawdziwe nagłówki, takie jak numer faktury. Po prostu nie wiem, jak wyglądają dane Morta.
A następnie zakres kryteriów to nagłówek i żądana wartość. Powiedzmy, że próbowałem cokolwiek uzyskać w roku 2014. To staje się takim zakresem kryteriów. W porządku, tylko słowo ostrzeżenia. Jestem w Excelu 2016 i jest możliwe zrobienie zaawansowanego filtru między dwoma arkuszami w Excelu 2016, ale jeśli cofniesz się i nie pamiętam, jaka jest droga wstecz, może 2003, nie jestem pewien. Kiedyś w przeszłości nie można było wykonać zaawansowanego filtru z jednego arkusza do drugiego, więc trzeba było przyjść tutaj i nazwać zakres wejściowy. Musiałbyś tutaj stworzyć nazwę. MyName czy coś w tym stylu, dobrze? I to byłby sposób, w jaki byłbyś w stanie to zrobić, w porządku. Niekoniecznie w programie Excel 2016, ale znowu,Nie jestem pewien, czy Mort będzie to uruchamiał w starszych wersjach danych.
W porządku, więc wracając do Data, przechodzimy do filtru zaawansowanego, w porządku. I zamierzamy skopiować do innej lokalizacji, która udostępnia tam nasz zakres wyjściowy. W porządku, więc zakres listy, gdzie są dane? Ponieważ jestem w programie Excel 2016, zamierzam wskazać dane, zamiast używać zakresu nazw - więc to mój zakres wejściowy. Zakres kryteriów to te komórki właśnie tam, a następnie, gdzie mamy zamiar - wynik do, po prostu będą to te trzy komórki. A następnie klikamy OK. W porządku, BAM! Tak szybko, szybko. A co by było, gdybyśmy chcieli innego roku? Gdybyśmy chcieli innego roku, usunęlibyśmy wyniki, wstawili 2015, a następnie ponownie wykonalibyśmy filtr zaawansowany, Skopiuj do innej lokalizacji, kliknij OK i mamy wszystkie rekordy z 2015 roku. Szybki jak błyskawica.
W porządku, chociaż jestem fanem zaawansowanego filtru w zwykłym Excelu, byłem wielkim fanem zaawansowanego filtra w VBA, w porządku, ponieważ VBA sprawia, że zaawansowany filtr jest naprawdę, naprawdę, bardzo prosty. W porządku, więc napiszemy tutaj kod dla Morta, zakładając, że dane Morta nie mają nagłówków i będziemy musieli tymczasowo dodać nagłówki, dobrze? Więc przełączę się na VBA, Alt + F11 i uruchomimy to z arkusza roboczego, który zawiera dane. A więc: Dim WS As Worksheet, Set WS = ActiveSheet. A następnie wstaw wiersz 1 i po prostu dodaj kilka nagłówków: A, B, Year i D. Sprawdź, ile wierszy danych mamy dzisiaj, a następnie zaczynając od komórki A1 wychodzącej z 4 kolumn w dół do ostatniego wiersza, nazwij to być zakresem wejściowym. W porządku, to jest kod Morta tutaj, w którym poprosił o InputBox,dostaje rok, którego chcą, a potem pyta, który rok lub jak chcą nazwać nowy arkusz, dobrze. Więc faktycznie wstawi arkusz w locie, a następnie I- Wymiaruj nowy arkusz, WSN, jako ActiveSheet. Wiem więc, że WS to oryginalny arkusz, WSN to nowy arkusz, który właśnie został dodany. W nowym arkuszu umieść zakres kryteriów, więc w kolumnie E znajduje się nagłówek, który pasuje do tego nagłówka, a następnie, niezależnie od odpowiedzi, którą nam dali, trafia do E2. Zakresem wyjściowym będą moje pozostałe trzy nagłówki: A, B i D. I znowu, jeśli ty lub Mort zmienicie je na prawdziwe nagłówki, co prawdopodobnie jest lepszą rzeczą do zrobienia niż A, B, D, a także zmień to na prawdziwe nagłówki, dobrze? Więc to wszystko to tylko trochę pracy przygotowawczej. Ta jedna niesamowita linia kodu wykona cały zaawansowany filtr. Więc,z InputRange robimy AdvancedFilter, zamierzamy skopiować. To nasz wybór filtra na miejscu lub skopiuj. CriteriaRange to E1 do E2, CopyToRange to A do C. Unikalne wartości -Nie, chcemy wszystkich wartości. W porządku, ta jedna linia kodu zapewnia całą magię przechodzenia przez wszystkie rekordy lub zastępuje zapętlanie wszystkich rekordów lub tworzenie tablic. A potem skończymy, wyczyścimy zakres kryteriów, a następnie usuniemy wiersz 1 z powrotem w oryginalnym arkuszu.A potem skończymy, wyczyścimy zakres kryteriów, a następnie usuniemy wiersz 1 z powrotem w oryginalnym arkuszu.A potem skończymy, wyczyścimy zakres kryteriów, a następnie usuniemy wiersz 1 z powrotem w oryginalnym arkuszu.
OK, więc wróćmy do naszych danych. Ułatwimy to uruchomienie, więc: Wstaw, Kształt i nazwij ten Filtr, Dom, Środek, Środek, Większy, Większy, Większy, kliknij prawym przyciskiem myszy, Przypisz makro i przypisz go do MacroForMort. W porządku, więc zaczynamy. Zrobimy test. Widzisz, jesteśmy w arkuszu danych, kliknij Filtr, który rok chcemy? Chcemy 2015 roku. Jak to nazwać? Chcę to nazwać 2015, dobrze. I BAM! Gotowe. Tak szybko, tak szybko to jest.
Skoro oryginalne dane Morta nie miały nagłówków, może te dane nie powinny mieć nagłówków. Więc chodźmy Alt + F11, tutaj chcemy wyczyścić zakres kryteriów. Będziemy również Rows (1) .Delete. W porządku, więc teraz następnym razem, gdy będziemy na tym, usunie się te nagłówki. I po prostu - zamiast szybko uruchomić całą sprawę, spójrzmy tutaj na 2014. Wybieram więc jedną komórkę w Data, Alt + F11, i chcę przejść do punktu, w którym zrobimy zaawansowany filtr. Możemy więc spojrzeć i zobaczyć, co robi tutaj całe makro. Więc klikniemy Uruchom, a ja chcę dostać 2014. 2014, dobrze. I tak, wciśnij F8, zaraz zrobimy filtr zaawansowany. Możemy tu wrócić do programu Excel i zobaczyć, co się stało.
Pierwsza rzecz, która się wydarzyła - pierwszą rzeczą, która się wydarzyła, jest dodanie nowego tymczasowego wiersza z nagłówkami. Wstawiłem ten arkusz roboczy, zbudowałem zakres kryteriów z nagłówkiem i rokiem, które wprowadzili, wybrałem pola, które chcemy zrobić, a następnie z powrotem w VBA, uruchomię następną linię kodów, to jest F8, który robi tam zaawansowany filtr . Jest niesamowicie szybki i zobaczysz, że to właśnie przyniosło nam wszystkie płyty. Stamtąd to tylko trochę porządkowania, usuń to, usuń to. Wrócę do danych i usunę wiersz 1 i będzie dobrze. Więc po prostu pozwolę reszcie tego uruchomić, usunąć ten punkt przerwania, dobrze? Więc jest VBA. Dla mnie jest to najszybsza i najszybsza droga.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
W porządku, dobrze, masz to. Chcę podziękować Mortowi za przesłanie tego pytania. Dziękuję za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2060.xlsm
