Excel VBA Macro do sortowania danych. Nie podoba mi się, jak rejestrator makr tworzy dodatkowy kod do sortowania. Sortowanie w Excel VBA powinno być proste. Jedna linia kodu z kolumną do sortowania, w którą stronę (rosnąco lub malejąco) oraz nagłówek.
Obejrzyj wideo
- Rejestrator makr programu Excel nie radzi sobie dobrze z sortowaniem nagrań.
- Pod warunkiem, że dane można wybrać za pomocą Ctrl + * (znany jako bieżący region)
- Pod warunkiem, że nie sortujesz według koloru, ikony ani nie więcej niż trzech poziomów
- Użyj starej szkoły Range (). CurrentRegion.Sort w programie Excel
Transkrypcja wideo
Naucz się Excela z MrExcel Podcast, odcinek 2093: Sorting With VBA
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie od Jamesa z Huntsville. James, użyłem rejestratora makr, aby zarejestrować akcję sortowania danych. Powiedzmy, że James musiał posortować te dane według sektora i klientów. Przechodzisz więc tutaj do zakładki Widok, Makra, Zarejestruj nowe makro, SortujMyRaporty, Klawisz skrótu Ctrl - wpiszę tam Shift + S i kliknę OK. W porządku, więc od tego miejsca robimy: dane, sortowanie i chcemy powiedzieć, że chcemy sortować według sektora, a następnie dodać poziom i sortować według klienta i kliknąć OK. W lewym dolnym rogu klikamy Zatrzymaj nagrywanie. W porządku, więc oto jest. Wydaje się, że zadziałało, prawda?
Ale oto problem: jutro będziesz mieć więcej lub mniej danych, albo wiesz, cokolwiek. A to Recorded Macro jest po prostu okropne. Rzućmy okiem, zrobię Alt + F8 i przyjrzę się SortMyReport, edytuję to. W porządku, i to jest wszystko, co nagrali, więc SortFields.Clear, a następnie skonfigurowali nowy sort z SortFields.Add i są zakodowani na stałe, i jest 568 wierszy i wszystko to.
Sortowanie w ciągu dnia było naprawdę, bardzo łatwe. W porządku, a następnie w programie Excel 2007 dodali Sortuj według ikony, Sortuj według koloru, Sortuj według koloru czcionki, możliwość sortowania 15 poziomów i wszystko stało się naprawdę, naprawdę szalone. Dlatego nie używam już zarejestrowanego makra. Po prostu idę do starej szkoły.
Teraz powrócę do programu Excel. Oto zasady działania tego oldschoolowego sortowania, w porządku. Nagłówek nad każdą kolumną: nagłówek musi znajdować się w jednym wierszu, a nie w dwóch wierszach. Jeśli masz tam tytuły i dobrze jest mieć tam tytuły. Potrzebujesz całkowicie pustego wiersza między tytułami a pierwszym nagłówkiem. Jeśli masz notatki po prawej stronie: twoja żona dzwoni z listą zakupów: „Hej, kochanie, zatrzymaj się w drodze do domu. Kup mleko, jajka i wódkę ”. Pomiędzy danymi a danymi musi być zupełnie pusta kolumna. A jeśli na dole znajdują się standardowe notatki, upewnij się, że między ostatnim fragmentem danych a tymi notatkami jest zupełnie pusty wiersz.
Moim głównym celem jest to, że powinniśmy być w stanie dotrzeć do dowolnej komórki: w lewym górnym rogu komórki te dane i naciśnij Ctrl + *, a ona wybierze dane do posortowania. Teraz mam zamiar nacisnąć Ctrl +. to prowadzi nas do tego rogu, a następnie Ctrl +. przeniesie nas do prawego dolnego rogu, Ctrl +. przenosi nas do lewego dolnego rogu. W porządku, więc jeśli Ctrl + * poprawnie wybierze dane, wszystko jest w porządku. Jeśli umieścisz listę zakupów w kolumnie H i zobaczymy, że podchodzimy tutaj i Ctrl + *, cóż, teraz sortujemy listę zakupów jako część rzeczy i twoja lista zakupów zostanie schrzaniona. Albo cofniemy: jeśli tego wiersza nie ma, teraz wykonujemy Ctrl + *, widzisz, że jesteśmy - teraz będą nieświadomi, ponieważ nie mają już żadnych nagłówków, dobrze?
Tak więc, jeśli zamierzasz użyć mojego kodu, upewnij się, że wszystkie te zasady są prawdziwe: bez sortowania mojego koloru, bez sortowania mojej ikony, 3 lub mniej poziomów sortowania. Cofnij, dobrze. Oto, co wiemy: wiemy, że każdego dnia nasze dane będą zaczynać się w A5. Jeśli nie wiemy, ile wierszy lub ile, a nawet ile kolumn możemy mieć. Nie wyobrażam sobie sytuacji, w której kolumny są zamienione, ale z pewnością liczba wierszy się zmieni. Więc Alt + F11, zaczniemy po prostu od tej komórki w lewym górnym rogu. Więc Range, w moim przypadku to „A5” .CurrentRegion. Obecny region to ta niesamowita właściwość budowlana, która mówi, że wciśniemy Ctrl + Shift + * i wszystko, co tam jest zawarte, zostanie posortowane. I robimy .Sortuj. .Sortuj, dobrze.
Oto rzecz. Jeśli chcesz zrobić sortowanie na jednym poziomie, jest to łatwe: Klucz1: =. : = i po prostu mówimy, że to będzie Range - Och, zapomniałem, co to jest. To był Sektor, gdzie Sektor? Sektor znajduje się w kolumnie C. Więc w moim przypadku C5, Range („C5”), a następnie Order1: = xlAscending. Nacisnąłem tam klawisz strzałki w dół, a następnie Tab. W porządku, mógłbym iść dalej w prawo, ale nie zamierzam tego robić. Mam zamiar przejść do nowej linii, więc spacja, podkreślenie, aby przejść do nowej linii, kontynuuje tę linię kodu, dobrze? A jeśli mam sortowanie drugiego poziomu: Key2: = iw tym przypadku chcę sortować według klienta, który jest w kolumnie D, więc D5. A następnie Order2: xlAscending. Piękny.
Nie mam sortowania trzeciego poziomu, ale gdyby tak było, byłby to Key3, a następnie Order3. A potem następny, ten, który musisz zrobić, to Header, w porządku? Więc, Header: = xlGuess, to jest to, gdzie masz cholernie dużo kłopotów. I tak powiemy xl Tak, na pewno jako nagłówek. Nawet w dawnych czasach Rejestrator makr używał xlGuess. Nienawidzę zgadywać Excela.
Otóż to. Jedna linia kodu, to wszystko, co musisz zrobić, a będzie działać z większą liczbą wierszy, mniejszą liczbą wierszy. To piękna, piękna rzecz. W porządku, więc wrócimy tutaj do programu Excel. Ctrl + Shift + S jest nadal przypisaną rzeczą. To teraz - Jeśli właśnie przełączyłeś się na VBA i sam to wpiszesz, możesz przejść do Alt + F8, znaleźć nazwę swojego makra, kliknąć Opcje i wpisać tam Ctrl + Shift + S lub możemy nawet przypisać go do Klawisz skrótu na pasku narzędzi szybkiego dostępu. Kliknij prawym przyciskiem myszy, Dostosuj pasek narzędzi szybkiego dostępu, w którym wybieram z naszych makr. Mam makro o nazwie SortMyReport, kliknie przycisk Dodaj - nienawidzę tam małego schematu blokowego. Zmodyfikujemy to i bardzo chciałbym, żeby była tam jakaś sytuacja od A do Z, ale oczywiście nie ma. Może ta strzała, która wie, kto wie, po prostu wybiera cokolwiek.Magiczna ósemka, nie wiem. Mam zamiar wybrać tego malucha tutaj, kliknij OK, kliknij OK. W porządku, więc teraz nasze dane są sortowane według daty, wybieram - i nie ma znaczenia, co wybiorę. Zawsze wróci i posortuje od A5, klikam małego gościa i moje dane są teraz sortowane według sektora, w ramach sektora, według klienta. Działa świetnie, dobrze?
Więc jeśli jesteś fanem Macro Recorder, cóż, moje serdeczne życzenia dla Ciebie. Ale kod rejestratora makr w dzisiejszych czasach do sortowania sortowania w VBA jest o wiele prostszy; aby po prostu wrócić, po prostu użyj zasadniczo tego jednego wiersza kodu.
Cóż, zwykle jest to miejsce, w którym staram się kupić tę książkę, ale myślę, że dziś powinieneś spojrzeć na tę książkę: Excel 2016 VBA i makra autorstwa Tracy i mnie. Łał! Spójrz na to. Nie zdawałem sobie sprawy, że jest wersja w innym języku. Doprowadzimy Cię całkowicie do krzywej uczenia się makr od nagrania pierwszego makra do potrzebnego kodu.
Cóż, proste podsumowanie na dziś: Rejestrator makr programu Excel nie radzi sobie dobrze z nagrywaniem, sortowaniem: pod warunkiem, że dane można wybrać za pomocą Ctrl + *, który jest znany jako bieżący region, pod warunkiem, że nie sortujesz jak kolor lub ikona lub więcej niż trzy poziomy, po prostu użyj starej metody Range (). CurrentRegion.Sort w języku VBA do sortowania.
Chcę podziękować Jamesowi za wysłanie tego pytania. Chcę podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2093.xlsm
