Sortowanie danych w Excel VBA
Program Excel ma doskonały sposób sortowania szeregu danych tabelarycznych za pomocą wstążki w interfejsie programu Excel, a w pewnym momencie prawdopodobnie zechcesz użyć tej funkcji w kodzie VBA. Na szczęście jest to bardzo łatwe.
Okno dialogowe interfejsu użytkownika można znaleźć, klikając ikonę „Sortuj” w grupie „Sortuj i filtruj” na karcie „Dane” na wstążce programu Excel. Najpierw musisz wybrać zakres danych tabelarycznych.
Możesz także użyć Alt-A-S-S, aby wyświetlić okno dialogowe dla niestandardowego sortowania.
Metoda sortowania została znacznie ulepszona w późniejszych wersjach programu Excel. Kiedyś sortowanie było ograniczone do trzech poziomów, ale teraz możesz wprowadzić tyle poziomów, ile potrzebujesz, i dotyczy to również VBA.
Możesz włączyć wszystkie oferowane funkcje sortowania w oknie dialogowym Excel Sort do swojego kodu VBA. Funkcja sortowania w programie Excel jest szybka i szybsza niż wszystko, co można napisać samodzielnie w VBA, więc skorzystaj z tej funkcji.
Zwróć uwagę, że podczas sortowania w VBA parametry sortowania pozostają takie same w oknie dialogowym sortowania frontonu. Są one również zapisywane podczas zapisywania skoroszytu.
Jeśli użytkownik wybierze ten sam zakres danych tabelarycznych i kliknie ikonę Sortuj, zobaczy wszystkie parametry, które zostały wprowadzone przez kod VBA. Jeśli chcą zrobić coś w rodzaju własnego projektu, będą musieli najpierw usunąć wszystkie poziomy sortowania, co będzie dla nich bardzo denerwujące.
Ponadto, jeśli nie zmienisz parametrów w kodzie i polegasz na wartościach domyślnych, może się okazać, że użytkownik wprowadził zmiany, które będą odzwierciedlone w sortowaniu VBA i mogą dać nieoczekiwane wyniki, które mogą być bardzo trudne do debugowania .
Na szczęście w VBA istnieje metoda Clear, aby ponownie ustawić wszystkie parametry sortowania, aby użytkownik zobaczył okno dialogowe czystego sortowania
| 1 | Arkusze("Arkusz1").Sort.SortFields.Clear |
Dobrą praktyką jest wyczyszczenie parametrów sortowania w VBA przed i po zakończeniu sortowania.
Praktyczne zastosowanie metody sortowania w VBA
Kiedy dane tabelaryczne są importowane do Excela, często są one w bardzo losowej kolejności. Może być importowany z pliku CSV (wartości rozdzielane przecinkami) lub może pochodzić z linku do bazy danych lub strony internetowej. Nie możesz polegać na tym, że jest w ustalonej kolejności od jednego importu do drugiego.
Jeśli przedstawiasz te dane użytkownikowi w swoim arkuszu, użytkownik może mieć trudności z przyjrzeniem się i zrozumieniem ogromnej ilości danych, które pod względem porządku są wszędzie. Mogą chcieć pogrupować dane lub wyciąć i wkleić niektóre ich sekcje do innej aplikacji.
Mogą również chcieć zobaczyć na przykład najlepiej opłacanego pracownika lub pracownika o najdłuższym stażu.
Korzystając z metody Sort w VBA, możesz zaoferować opcje umożliwiające użytkownikowi łatwe sortowanie.
Przykładowe dane, aby zademonstrować sortowanie w programie Excel za pomocą VBA
Najpierw potrzebujemy kilka przykładowych danych, które należy wprowadzić do arkusza roboczego, aby kod mógł zademonstrować wszystkie funkcje dostępne w VBA.
Skopiuj te dane do arkusza roboczego (zwanego „Arkusz1”) dokładnie tak, jak pokazano.
Zwróć uwagę, że zastosowano różne kolory tła komórki i kolory czcionek, ponieważ mogą one być również używane jako parametry sortowania. Sortowanie przy użyciu kolorów komórek i czcionek zostanie zademonstrowane w dalszej części artykułu. Zwróć też uwagę, że w komórce E3 nazwa działu jest w całości pisana małymi literami.
Nie potrzebujesz wnętrza komórki i kolorów czcionki, jeśli nie chcesz korzystać z przykładów sortowania według komórki i koloru czcionki.
Nagrywanie makra dla sortowania VBA
Kod VBA do sortowania może być dość skomplikowany i czasami dobrym pomysłem może być wykonanie sortowania w interfejsie programu Excel i nagranie makra, aby pokazać, jak działa kod.
Niestety funkcja nagrywania może generować ogromną ilość kodu, ponieważ ustawia praktycznie każdy dostępny parametr, mimo że domyślne wartości wielu parametrów są akceptowalne dla operacji sortowania.
Daje to jednak bardzo dobre wyobrażenie o tym, co jest związane z pisaniem kodu sortowania VBA, a jedną z zalet jest to, że nagrany kod zawsze będzie dla Ciebie działał. Twój własny kod może wymagać testowania i debugowania, aby działał poprawnie.
Pamiętaj, że w przypadku operacji wykonywanej w VBA nie ma funkcji cofania, więc dobrym pomysłem jest wykonanie kopii danych tabelarycznych do innego arkusza roboczego przed rozpoczęciem pisania kodu sortowania.
Na przykład, jeśli wykonasz proste sortowanie na przykładowych danych powyżej, sortując według pracownika, nagranie wygeneruje następujący kod:
| 123456789101112131415161718 | Submakro1()Zakres ("A1:E6").WybierzActiveWorkbook.Worksheets("Arkusz1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Arkusz1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormalZ ActiveWorkbook.Worksheets("Arkusz1").Sort.SetRange Range("A1:E6").Nagłówek = xlTak.MatchCase = Fałsz.Orientacja = xlTopToBottom.Metoda sortowania = xlPinYin.ZastosowaćKończyć zNapis końcowy |
Jest to całkiem spory kawałek kodu, a wiele z niego jest niepotrzebnych ze względu na używane parametry domyślne. Jeśli jednak jesteś pod presją czasu, aby ukończyć projekt i potrzebujesz szybko działającego kodu, możesz łatwo wkleić go do własnego kodu VBA.
Jeśli jednak chcesz, aby Twój kod był zrozumiały i bardziej elegancki, dostępne są inne opcje.
Kod VBA do sortowania jednopoziomowego
Jeśli chcesz posortować przykładowy kod na podstawie pracownika tylko tak jak poprzednio przy nagrywaniu makra, kod jest bardzo prosty:
| 1234567 | Podrzędne sortowanie jednopoziomowe()Arkusze("Arkusz1").Sort.SortFields.ClearZakres("A1:E6").Klucz sortowania1:=Zakres("A1"), Nagłówek:=xlTakNapis końcowy |
Jest to o wiele łatwiejsze do zrozumienia niż nagrany kod, ponieważ akceptuje wartości domyślne, np. sortowanie rosnąco, więc nie ma potrzeby ustawiania parametrów na wartości domyślne. Zakłada się, że wcześniej użyłeś oświadczenia „Wyczyść”.
Metoda „Wyczyść” jest używana początkowo, aby zapewnić, że każdy parametr sortowania dla tego arkusza roboczego zostanie przywrócony do wartości domyślnych. Użytkownik mógł wcześniej ustawić parametry na różne wartości lub wcześniejsze sortowanie w VBA mogło je zmienić. Ważne jest, aby podczas sortowania zacząć od domyślnej pozycji, w przeciwnym razie możesz łatwo uzyskać nieprawidłowe wyniki.
Metoda Clear nie resetuje parametru Header i zaleca się uwzględnienie go w kodzie, w przeciwnym razie program Excel może spróbować zgadnąć, czy wiersz nagłówka jest obecny, czy nie.
Uruchom ten kod na przykładowych danych, a arkusz będzie wyglądał tak:
Kod VBA do sortowania wielopoziomowego
Możesz dodać tyle poziomów sortowania, ile jest to wymagane w kodzie. Załóżmy, że chcesz posortować najpierw według działu, a następnie według daty rozpoczęcia, ale w kolejności rosnącej dla działu i malejącej dla daty początkowej:
| 12345678 | Podrzędne sortowanie wielopoziomowe()Arkusze("Arkusz1").Sort.SortFields.ClearZakres("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlTak, _Zamówienie1:=xlRosnąco, Zamówienie2:=xlMalejącoNapis końcowy |
Zauważ, że w instrukcji sort są teraz dwa klucze (Klucz1 i Klucz2). Najpierw sortowany jest Klucz1 (kolumna E działu), a następnie Klucz2 (kolumna C daty rozpoczęcia) jest sortowany na podstawie pierwszego sortowania.
Istnieją również dwa parametry zamówienia. Zamówienie1 łączy się z Kluczem1 (Dział) i Zamówienie2 łączy z Kluczem2 (Data rozpoczęcia). Ważne jest, aby upewnić się, że klucze i zamówienia są ze sobą zgodne.
Uruchom ten kod na przykładowych danych, a arkusz będzie wyglądał tak:
Kolumna Dział (E) jest w porządku rosnącym, a kolumna Data rozpoczęcia (C) jest w porządku malejącym.
Efekt tego rodzaju jest najbardziej zauważalny, gdy spojrzymy na Jane Halfacre (rząd 3) i Johna Sutherlanda (rząd 4). Oboje są w finansach, ale Jane Halfacre zaczęła przed Johnem Sutherlandem, a daty są pokazane w porządku malejącym.
Jeśli zakres danych tabelarycznych może mieć dowolną długość, można użyć obiektu UsedRange do zdefiniowania zakresu sortowania. Będzie to działać tylko wtedy, gdy w arkuszu znajdują się tylko dane tabelaryczne, ponieważ wszelkie wartości poza danymi dadzą nieprawidłowe wyniki dla liczby wierszy i kolumn.
| 1234567 | Podrzędne sortowanie wielopoziomowe()Arkusze("Arkusz1").Sort.SortFields.ClearArkusze("Arkusz1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlTak, _Zamówienie1:=xlRosnąco, Zamówienie2:=xlMalejącoNapis końcowy |
Zapobiega to problemowi, jeśli użyjesz metody „End(xlDown)” do zdefiniowania zakresu sortowania. Jeśli w środku danych znajduje się pusta komórka, wszystko po pustej komórce nie zostanie uwzględnione, podczas gdy UsedRange przejdzie do ostatniej aktywnej komórki w arkuszu.
Sortowanie według koloru komórki
Od programu Excel 2007 możliwe jest teraz sortowanie według koloru tła komórki, co zapewnia ogromną elastyczność podczas projektowania kodu sortowania w VBA.
| 123456789101112 | Podrzędne sortowanie jednopoziomowe według koloru komórki()Arkusze("Arkusz1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Arkusz1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormalZ ActiveWorkbook.Worksheets("Arkusz1").Sort.SetRange Range("A1:E6").ZastosowaćKończyć zNapis końcowy |
Ten kod posortuje przykładowy zakres danych (A2:A6) na podstawie koloru tła komórki. Zauważ, że istnieje teraz dodatkowy parametr o nazwie „SortOn”, który ma wartość „xlSortOnCellColor”.
Zauważ, że parametr „SortOn” może być używany tylko przez obiekt arkusza, a nie przez obiekt zakresu.
Z tego powodu kod jest bardziej skomplikowany niż w przypadku sortowania przy użyciu wartości komórek.
Ten kod używa wartości klucza dla sortowania, które obejmuje cały zakres danych, ale możesz określić poszczególne kolumny jako klucz dla sortowania według koloru tła i użyć wielu poziomów, jak pokazano wcześniej.
Po uruchomieniu tego kodu twój arkusz będzie teraz wyglądał tak:
Sortowanie według koloru czcionki
Funkcja sortowania w Excel VBA oferuje jeszcze większą elastyczność, ponieważ możesz sortować według kolorów czcionek:
| 1234567891011121314 | Podrzędne sortowanie jednopoziomowe według koloru czcionki()Arkusze("Arkusz1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Arkusz1").Sort.SortFields.Add(Range("A2:A6"), _xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)Z ActiveWorkbook.Worksheets("Arkusz1").Sort.SetRange Range("A1:E6").Nagłówek = xlTak.Orientacja = xlTopToBottom.ZastosowaćKończyć zNapis końcowy |
Kod sortowania według koloru czcionki jest znacznie bardziej skomplikowany niż w przypadku koloru tła komórki. Parametr „SortOn” zawiera teraz wartość „xlSortOnFontColor”.
Pamiętaj, że musisz określić orientację jako „xlTopToBottom” i musisz określić kolor do sortowania. Jest to określone w kategoriach RGB (czerwony, zielony, czarny) z wartościami od 0 do 255.
Po uruchomieniu tego kodu na przykładowych danych arkusz będzie teraz wyglądał tak:
Sortowanie przy użyciu kolorów w VBA jest znacznie bardziej skomplikowane niż sortowanie wielopoziomowe, ale jeśli twój kod sortowania nie zadziała (co może się zdarzyć, jeśli brakuje parametru lub nie wpisałeś kodu poprawnie), zawsze możesz wrócić do nagrywania makro i zintegrowanie nagranego kodu z twoim VBA.
Używanie innych parametrów w sortowaniu VBA
Istnieje wiele opcjonalnych parametrów, których można użyć w kodzie VBA, aby dostosować sortowanie.
Sortuj
SortOn określa, czy podczas sortowania będą używane wartości komórek, kolory tła komórek, czy kolory czcionek komórek. Ustawienie domyślne to Wartości komórek.
| 1 | Sortuj na = xlSortuj na wartości |
Zamówienie
Kolejność określa, czy sortowanie będzie wykonywane w kolejności rosnącej czy malejącej. Wartość domyślna to Rosnąco.
| 1 | Kolejność = xlRosnąco |
Opcja danych
DataOption wybiera sposób sortowania tekstu i liczb. Parametr xlSortNormal sortuje oddzielnie dane liczbowe i tekstowe. Parametr xlSortTextAsNumbers traktuje tekst jako dane liczbowe do sortowania. Wartość domyślna to xlSortNormal.
| 1 | Opcja danych = xlSortuj normalnie |
nagłówek
Nagłówek określa, czy zakres danych tabelarycznych ma wiersz nagłówka, czy nie. Jeśli istnieje wiersz nagłówka, nie chcesz, aby był on uwzględniany w sortowaniu.
Wartości parametrów to xlYes, xlNo i xlYesNoGuess. xlYesNoGuess pozostawia Excelowi sprawdzenie, czy istnieje wiersz nagłówka, co może łatwo prowadzić do niespójnych wyników. Użycie tej wartości nie jest zalecane.
Wartość domyślna to XNo (brak wiersza nagłówka w danych). W przypadku importowanych danych zwykle występuje wiersz nagłówka, więc upewnij się, że ustawiłeś ten parametr na xlYes.
| 1 | Nagłówek = xlTak |
Sprawa meczu
Ten parametr określa, czy w sortowaniu jest rozróżniana wielkość liter, czy nie. Wartości opcji to prawda lub fałsz. Jeśli wartością jest False, wówczas wartości pisane małymi literami są traktowane tak samo, jak wartości pisane dużymi literami. Jeśli wartość to True, sortowanie pokaże różnicę między wartościami wielkimi i małymi literami w ramach sortowania. Wartość domyślna to Fałsz.
| 1 | MatchCase = Fałsz |
Orientacja
Ten parametr określa, czy sortowanie będzie odbywać się w dół w wierszach, czy we wszystkich kolumnach. Wartość domyślna to xlTopToBottom (sortowanie wierszy). Możesz użyć xlLeftToRight, jeśli chcesz sortować poziomo. Wartości takie jak xlRows i xlColumns nie działają dla tego parametru.
| 1 | Orientacja = xlGóraDo Dołu |
Metoda sortowania
Ten parametr jest używany tylko do sortowania języków chińskich. Ma dwie wartości, xlPinYin i xlStroke. xlPinYin jest wartością domyślną.
xlPinYin sortuje znaki używając fonetycznego chińskiego porządku sortowania. xlStroke sortuje według ilości pociągnięć w każdym znaku.
Jeśli nagrasz makro sortowania, ten parametr będzie zawsze zawarty w kodzie i być może zastanawiałeś się, co to oznacza. Jednak o ile nie masz do czynienia z danymi w języku chińskim, jest to mało przydatne.
| 1 | Metoda sortowania = xlPinYin |
Używanie zdarzenia podwójnego kliknięcia do sortowania danych tabelarycznych
We wszystkich funkcjach, które Microsoft uwzględnił w metodach sortowania dla VBA, nie zawierał prostego sposobu dwukrotnego kliknięcia nagłówka kolumny i posortowania wszystkich danych tabelarycznych na podstawie tej konkretnej kolumny.
Jest to naprawdę przydatna funkcja i łatwo jest napisać odpowiedni kod.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Anuluj jako Boolean)„Założono, że dane zaczynają się w komórce A1”'Utwórz trzy zmienne, aby przechwycić wybraną kolumnę docelową oraz maksymalną kolumnę i wiersz _'dane tabelaryczne'Dim Col jako liczba całkowita, RCol jako długa, RRow jako długa'Sprawdź, czy użytkownik dwukrotnie kliknął wiersz nagłówka - wiersz 1, w przeciwnym razie wyjdź z podrzęduJeśli Target.Row 1 to wyjdź z Sub„Przechwyć maksymalną liczbę wierszy w zakresie danych tabelarycznych za pomocą obiektu „UsedRange”RCol = ActiveSheet.UsedRange.Columns.Count'Przechwyć maksymalną liczbę kolumn w zakresie danych tabelarycznych za pomocą obiektu 'UsedRange'RRow = ActiveSheet.UsedRange.Rows.Count'Sprawdź, czy użytkownik nie kliknął dwukrotnie kolumny poza zakresem danych tabelarycznychJeśli Target.Column > RCol, to wyjdź z Sub'Przechwyć kolumnę, którą użytkownik kliknął dwukrotnieKol = Cel. Kolumna'Usuń poprzednie parametry sortowaniaActiveSheet.Sort.SortFields.Clear'Sortuj zakres tabelaryczny według maksymalnej liczby wierszy i kolumn z obiektu 'UsedRange''Sortuj dane tabelaryczne, używając kolumny klikniętej dwukrotnie przez użytkownika jako klucza sortowaniaActiveSheet.Range(Cells(1,1), Cells(RCol, RRow)).Sort Key1:=Cells(1,Col), Header:=xlYes'Wybierz komórkę A1 - ma to na celu zapewnienie, że użytkownik nie pozostanie w trybie edycji po sortowaniu _'zakończoneActiveSheet.Range("A1").WybierzNapis końcowy |
Ten kod należy umieścić w zdarzeniu podwójnego kliknięcia w arkuszu zawierającym dane tabelaryczne. Robisz to, klikając nazwę arkusza roboczego w oknie Eksploratora projektu (lewy górny róg ekranu VBE), a następnie wybierając „Arkusz roboczy” w pierwszym menu rozwijanym w oknie kodu. Wybierz „BeforeDoubleClick” w drugim menu rozwijanym, a następnie wprowadź swój kod.
Zwróć uwagę, że w tym kodzie nie są zakodowane żadne nazwy, zakresy ani odwołania do komórek, z wyjątkiem przeniesienia kursora do komórki A1 na końcu kodu. Kod ma na celu uzyskanie wszystkich wymaganych informacji ze współrzędnych komórki, które użytkownik kliknął dwukrotnie, oraz rozmiaru zakresu danych tabelarycznych.
Nie ma znaczenia, jak duży jest zakres danych tabelarycznych. Kod nadal będzie pobierał wszystkie wymagane informacje i może być używany na danych przechowywanych w dowolnym miejscu w skoroszycie bez konieczności wpisywania wartości na stałe.
Jedynym założeniem jest to, że w danych tabelarycznych znajduje się wiersz nagłówka, a zakres danych zaczyna się od komórki A1, ale pozycję początkową zakresu danych można łatwo zmienić w kodzie.
Każdy użytkownik będzie pod wrażeniem nowej funkcji sortowania!
Rozszerzenie funkcji sortowania za pomocą VBA
Microsoft umożliwił ogromną elastyczność w sortowaniu przy użyciu szerokiej gamy parametrów. Jednak w VBA możesz to zrobić dalej.
Załóżmy, że chcesz posortować dowolne wartości pogrubioną czcionką na początku danych. Nie ma sposobu, aby to zrobić w programie Excel, ale możesz napisać kod VBA, aby to zrobić:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub Sortuj według pogrubienia()'Utwórz zmienne do przechowywania liczby wierszy i kolumn dla danych tabelarycznychDim RR tak długi, RCol tak długi, N tak długi„Wyłącz aktualizowanie ekranu, aby użytkownik nie widział, co się dzieje - może zobaczyć _"wartości ulegają zmianie i zastanawiam się dlaczego"Application.ScreenUpdating = Fałsz'Przechwyć liczbę kolumn w zakresie danych tabelarycznychRCol = ActiveSheet.UsedRange.Columns.Count'Przechwyć liczbę wierszy w zakresie danych tabelarycznychRRow = ActiveSheet.UsedRange.Rows.Count'Iteruj przez wszystkie wiersze w zakresie danych tabelarycznych, ignorując wiersz nagłówkaDla N = 2 do RRow„Jeśli komórka ma pogrubioną czcionkę, umieść wiodącą wartość 0 przed wartością komórki”Jeśli ActiveSheet.Cells(N, 1).Font.Bold = True WtedyActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).ValueZakończ, jeśliNastępny N'Wyczyść wszystkie poprzednie parametry sortowaniaActiveSheet.Sort.SortFields.Clear'Posortuj zakres danych tabelarycznych. Wszystkie wartości z wiodącą wartością 0 zostaną przeniesione na góręActiveSheet.Range(Komórki(1, 1), Komórki(RCol, RRow)).Klucz sortowania1:=Komórki(1,1), Nagłówek:=xlTak'Iteruj przez wszystkie wiersze w zakresie danych tabelarycznych, ignorując wiersz nagłówkaDla N = 2 do RRow'Jeśli komórka ma pogrubioną czcionkę, usuń wiodącą wartość 0 z wartości komórki do _'przywróć oryginalne wartościJeśli ActiveSheet.Cells(N, 1).Font.Bold = True WtedyActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1)).Value, 2)Zakończ, jeśliNastępny N'Włącz ponownie aktualizowanie ekranuApplication.ScreenUpdating = PrawdaNapis końcowy |
Kod oblicza rozmiar zakresu danych tabelarycznych za pomocą obiektu „UsedRange”, a następnie iteruje wszystkie zawarte w nim wiersze. Gdy zostanie znaleziona pogrubiona czcionka, przed wartością komórki umieszczane jest wiodące zero.
Następnie ma miejsce sortowanie. Ponieważ sortowanie odbywa się w porządku rosnącym, wszystko, co ma przed sobą zero, trafi na początek listy.
Kod następnie przechodzi przez wszystkie wiersze i usuwa wiodące zera, przywracając dane do ich oryginalnych wartości.
Ten kod sortuje przy użyciu pogrubionych czcionek jako kryterium, ale możesz z łatwością użyć innych cech komórki w ten sam sposób, np. Czcionka kursywa, rozmiar tekstu w punktach, czcionka podkreślenia, nazwa czcionki itp.
