Excel ma już kilka sposobów szybkiego sortowania danych.
Zestaw danych można łatwo posortować za pomocą ikon sortowania na wstążce lub w oknie dialogowym sortowania.
Dlaczego więc musisz wiedzieć, jak to zrobić za pomocą VBA?
Wiedza o tym, jak sortować dane za pomocą VBA, może być pomocna, jeśli zostanie uwzględniona jako część kodu. Załóżmy na przykład, że codziennie/co tydzień otrzymujesz zestaw danych, który musisz sformatować i posortować w określonej kolejności.
Możesz utworzyć makro, które zrobi to wszystko za Ciebie jednym kliknięciem. Dzięki temu zaoszczędzisz dużo czasu i wysiłku za każdym razem, gdy to zrobisz.
Ponadto, jeśli tworzysz pulpity nawigacyjne programu Excel, możesz przenieść możliwości sortowania programu Excel na nowy poziom, umożliwiając użytkownikowi sortowanie danych przez dwukrotne kliknięcie nagłówka (jak pokazano poniżej).
Omówię, jak to stworzyć w dalszej części tego samouczka. Najpierw szybko zapoznajmy się z podstawami.
Zrozumienie metody Range.Sort w Excel VBA
Podczas sortowania przy użyciu VBA musisz użyć metody Range.Sort w swoim kodzie.
„Zakres” to dane, które próbujesz posortować. Na przykład, jeśli sortujesz dane w A1:A10, „Zakres” będzie miał wartość Zakres („A1:A10”).
Możesz także utworzyć nazwany zakres i używać go zamiast odwołań do komórek. Na przykład, jeśli utworzę nazwany zakres „DataRange” dla komórek A1:A10, mogę również użyć Range(„DataRange”)
W przypadku metody sort musisz podać dodatkowe informacje za pomocą parametrów. Poniżej znajdują się kluczowe parametry, które musisz znać:
- Klucz - tutaj musisz określić kolumnę, którą chcesz posortować. Na przykład, jeśli chcesz posortować kolumnę A, musisz użyć klucza:=Range("A1")
- Zamówienie - tutaj określasz, czy chcesz sortować w porządku rosnącym czy malejącym. Na przykład, jeśli chcesz sortować w kolejności rosnącej, użyjesz Order:=xlAscending
- nagłówek - tutaj określasz, czy Twój zestaw danych ma nagłówki, czy nie. Jeśli ma nagłówki, sortowanie rozpoczyna się od drugiego wiersza zestawu danych, w przeciwnym razie zaczyna się od pierwszego wiersza. Aby określić, że Twoje dane mają nagłówki, użyjesz Header:=xlYes
Chociaż te trzy wystarczą w większości przypadków, możesz przeczytać więcej o parametrach w tym artykule.
Zobaczmy teraz, jak używać metody Range.Sort w VBA do sortowania danych w programie Excel.
Sortowanie pojedynczej kolumny bez nagłówka
Załóżmy, że masz pojedynczą kolumnę bez nagłówka (jak pokazano poniżej).
Możesz użyć poniższego kodu, aby posortować go w kolejności rosnącej.
Sub SortDataWithoutHeader() Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Zauważ, że ręcznie określiłem zakres danych jako Zakres („A1: A12”).
W przypadku zmian w danych i możliwości dodania/usunięcia wartości, możesz użyć poniższego kodu, który automatycznie dostosowuje się na podstawie wypełnionych komórek w zestawie danych.
Sub SortDataWithoutHeader() Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlRosnąco, Header:=xlNo End Sub
Zauważ, że zamiast Range(„A1:A12”) użyłem Range(„A1”, Range(„A1”).End(xlDown)).
Spowoduje to sprawdzenie ostatniej kolejno wypełnionej komórki w kolumnie i włączenie jej do sortowania. W przypadku, gdy są puste pola, dane będą brane pod uwagę tylko do pierwszej pustej komórki.
Możesz także utworzyć nazwany zakres i użyć tego nazwanego zakresu zamiast odwołań do komórek. Na przykład, jeśli nazwany zakres to DataSet, Twój kod będzie teraz taki, jak pokazano poniżej.
Sub SortDataWithoutHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Teraz szybko wyjaśnię parametry użyte w powyższych przykładach:
- Key1:=Range(„A1”) - Określono A1, aby kod wiedział, którą kolumnę sortować.
- Order1:=xlAscending - Określono zamówienie jako xlAscending. Jeśli chcesz, aby była w porządku malejącym, użyj xlDescending.
- Header:= xlNo - określono, że nie ma nagłówków. Jest to również wartość domyślna. Więc nawet jeśli to pominiesz, Twoje dane zostaną posortowane, biorąc pod uwagę, że nie mają nagłówków.
Zastanawiasz się, gdzie umieścić ten kod VBA i jak uruchomić makro? Przeczytaj ten samouczek!
Sortowanie pojedynczej kolumny z nagłówkiem
W poprzednim przykładzie zestaw danych nie miał nagłówka.
Jeśli dane mają nagłówki, musisz określić to w kodzie, aby sortowanie mogło rozpocząć się od drugiego wiersza zestawu danych.
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Poniżej znajduje się kod, który posortuje dane w porządku malejącym na podstawie sprzedaży sklepów.
Sub SortDataWithHeader() Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending End Sub
Zauważ, że utworzyłem nazwany zakres - „DataRange” i użyłem tego nazwanego zakresu w kodzie.
Sortowanie wielu kolumn z nagłówkami
Do tej pory w tym samouczku widzieliśmy, jak sortować pojedynczą kolumnę (z nagłówkami i bez).
A co, jeśli chcesz sortować na podstawie wielu kolumn.
Na przykład w poniższym zestawie danych, co jeśli chcę najpierw posortować według kodu stanu, a następnie według sklepu.
Oto kod, który posortuje wiele kolumn za jednym razem.
Sub SortMultipleColumns() Z ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlTak .Apply End With End Sub
Poniżej znajduje się wynik, który otrzymasz.
W powyższym przykładzie dane są najpierw sortowane według kodu stanu (kolumna A). Następnie w danych kodu stanu są one ponownie sortowane według magazynu (kolumna B). Ta kolejność jest określona przez kod, w którym o nim mowa.
Sortowanie danych za pomocą podwójnego kliknięcia na nagłówek
Jeśli tworzysz pulpit nawigacyjny lub chcesz mieć większą łatwość obsługi w swoich raportach, możesz napisać kod VBA, który będzie sortował dane po dwukrotnym kliknięciu nagłówków.
Coś, jak pokazano poniżej:
Poniżej znajduje się kod, który pozwoli Ci to zrobić:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Ustaw KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes End If End Sub
Zauważ, że utworzyłem nazwany zakres („DataRange”) i użyłem go w kodzie zamiast używać odwołań do komórek.
Po dwukrotnym kliknięciu dowolnego nagłówka kod wyłącza zwykłą funkcję dwukrotnego kliknięcia (czyli przejście do trybu edycji) i używa tej komórki jako klucza podczas sortowania danych.
Pamiętaj też, że od teraz ten kod będzie sortował wszystkie kolumny tylko w kolejności rosnącej.
Należy zauważyć, że dwukrotne kliknięcie jest wyzwalaczem, który umożliwia programowi Excel uruchomienie określonego kodu. Te wyzwalacze, takie jak dwukrotne kliknięcie, otwarcie skoroszytu, dodanie nowego arkusza, zmiana komórki itp., nazywane są zdarzeniami i mogą być używane do uruchamiania makr w programie Excel. Możesz przeczytać więcej o wydarzeniach Excel VBA tutaj.Gdzie umieścić ten kod?
Musisz wkleić ten kod do okna kodu arkusza, w którym chcesz skorzystać z funkcji sortowania podwójnym kliknięciem.
Aby to zrobić:
- Kliknij prawym przyciskiem myszy kartę arkusza.
- Kliknij Wyświetl kod.
- Wklej kod w oknie kodu arkusza, w którym znajdują się Twoje dane.
A co, jeśli chcesz posortować pierwsze dwie kolumny („Stan” i „Sklep”) w kolejności rosnącej, ale kolumnę „Sprzedaż” w kolejności malejącej.
Oto kod, który to zrobi:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes, Order1: =Kolejność sortowania kończy się, jeśli kończy podrzędną
W powyższym kodzie sprawdza, czy kliknięta dwukrotnie komórka jest nagłówkiem Sales, czy nie. Jeśli tak, to przypisuje wartość xlDescending do zmiennej SortOrder, w przeciwnym razie zmienia ją na xlAscending.
Teraz przejdźmy o krok dalej i pokażmy wizualny znacznik (strzałkę i kolorową komórkę) w nagłówku, gdy jest sortowany.
Coś, jak pokazano poniżej:
Aby to uzyskać, dodałem nowy arkusz roboczy i wprowadziłem w nim następujące zmiany (możesz pobrać przykładowy plik i podążać dalej):
- Zmieniono nazwę nowego arkusza na „BackEnd”.
- W komórce B2 wpisz symbol strzałki (w tym celu przejdź do Wstaw i kliknij opcję „Symbol”).
- Skopiuj i wklej nagłówki ze zbioru danych do komórki A3:C3 w arkuszu „Zaplecze”.
- Użyj następującej funkcji w komórce A4:AC4:
=JEŻELI(A3=$C$1,A3&""&$B$1,A3)
- Reszta komórek zostanie automatycznie wypełniona kodem VBA po dwukrotnym kliknięciu nagłówków, aby posortować kolumnę.
Twój arkusz zaplecza będzie wyglądał tak, jak pokazano poniżej:
Teraz możesz użyć poniższego kodu do sortowania danych, klikając dwukrotnie nagłówki. Dwukrotne kliknięcie nagłówka spowoduje automatyczne wyświetlenie strzałki w tekście nagłówka. Zauważ, że użyłem również formatowania warunkowego, aby podświetlić komórkę.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Worksheets("Backend").Range("C1") = Target.Value Set KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes Worksheets("BackEnd ").Range("A1") = Target.Column For i = 1 To ColumnCount Range("DataRange").Cells(1, i).Value = Worksheets("Backend").Range("A4").Offset (0, i - 1).Wartość Następna i Koniec Jeśli Koniec Sub
Zauważ, że ten kod działa dobrze w przypadku sposobu konstruowania moich danych i skoroszytu. Jeśli zmienisz strukturę danych, będziesz musiał odpowiednio zmodyfikować kod.
Pobierz przykładowy plik
