Excel ha già un paio di modi per ordinare rapidamente i dati.
È possibile ordinare facilmente un set di dati utilizzando le icone di ordinamento nella barra multifunzione o nella finestra di dialogo di ordinamento.
Allora perché hai bisogno di sapere come farlo usando VBA?
Sapere come ordinare i dati utilizzando VBA può essere utile se incluso come parte del codice. Ad esempio, supponi di ottenere un set di dati giornaliero/settimanale che devi formattare e ordinare in un ordine specifico.
Puoi creare una macro per fare tutto questo per te con un solo clic. Ciò ti farà risparmiare un sacco di tempo e fatica ogni volta che lo fai.
Inoltre, se crei dashboard di Excel, puoi portare la capacità di ordinamento di Excel a un nuovo livello consentendo all'utente di ordinare i dati semplicemente facendo doppio clic sull'intestazione (come mostrato di seguito).
Tratterò come crearlo più avanti in questo tutorial. Per prima cosa, mettiamo subito in chiaro le basi.
Comprensione del metodo Range.Sort in Excel VBA
Quando si ordina utilizzando VBA, è necessario utilizzare il metodo Range.Sort nel codice.
L'"Intervallo" sarebbe i dati che stai cercando di ordinare. Ad esempio, se stai ordinando i dati in A1: A10, allora "Intervallo" sarà Intervallo ("A1: A10").
Puoi anche creare un intervallo denominato e utilizzarlo al posto dei riferimenti di cella. Ad esempio, se creo un intervallo denominato "DataRange" per le celle A1: A10, posso anche utilizzare Range ("DataRange")
Con il metodo di ordinamento, è necessario fornire alcune informazioni aggiuntive tramite parametri. Di seguito sono riportati i parametri chiave che è necessario conoscere:
- Chiave - qui è necessario specificare la colonna che si desidera ordinare. Ad esempio, se si desidera ordinare la colonna A, è necessario utilizzare key:=Range(“A1”)
- Ordine - qui si specifica se si desidera l'ordinamento in ordine crescente o decrescente. Ad esempio, se desideri l'ordinamento in ordine crescente, utilizzerai Order:=xlAscending
- Intestazione - qui specifichi se il tuo set di dati ha o meno intestazioni. Se ha intestazioni, l'ordinamento inizia dalla seconda riga del set di dati, altrimenti inizia dalla prima riga. Per specificare che i tuoi dati hanno intestazioni, utilizzerai Header:=xlYes
Sebbene questi tre siano sufficienti nella maggior parte dei casi, puoi leggere di più sui parametri in questo articolo.
Ora vediamo come utilizzare il metodo Range.Sort in VBA per ordinare i dati in Excel.
Ordinamento di una singola colonna senza intestazione
Supponiamo di avere una singola colonna senza intestazione (come mostrato di seguito).
Puoi utilizzare il codice seguente per ordinarlo in ordine crescente.
Sub SortDataWithoutHeader() Range("A1:A12").Chiave di ordinamento1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Si noti che ho specificato manualmente l'intervallo di dati come Range ("A1: A12").
Nel caso in cui potrebbero esserci modifiche nei dati e i valori potrebbero essere aggiunti/eliminati, è possibile utilizzare il codice seguente che si adatta automaticamente in base alle celle riempite nel set di dati.
Sub SortDataWithoutHeader() Range("A1", Range("A1").End(xlDown)).Chiave di ordinamento1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Nota che invece di Range ("A1: A12"), ho usato Range ("A1", Range ("A1"). Fine (xlDown)).
Questo controllerà l'ultima cella riempita consecutivamente nella colonna e la includerà nell'ordinamento. Nel caso ci siano spazi vuoti, prenderà in considerazione solo i dati fino alla prima cella vuota.
Puoi anche creare un intervallo denominato e utilizzare quell'intervallo denominato invece dei riferimenti di cella. Ad esempio, se l'intervallo denominato è DataSet, il codice sarà ora come mostrato di seguito.
Sub SortDataWithoutHeader() Range("DataRange").Chiave di ordinamento1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Ora lascia che ti spieghi rapidamente i parametri utilizzati negli esempi precedenti:
- Key1:=Range("A1") - A1 specificato in modo che il codice sappia quale colonna ordinare.
- Order1:=xlAscending - Specificato l'ordine come xlAscending. Se vuoi che sia in ordine decrescente, usa xlDescending.
- Header:= xlNo - Specificato che non ci sono intestazioni. Questo è anche il valore predefinito. Quindi, anche se lo ometti, i tuoi dati verranno ordinati considerando che non hanno intestazioni.
Ti chiedi dove inserire questo codice VBA e come eseguire la macro? Leggi questo tutorial!
Ordinamento di una singola colonna con intestazione
Nell'esempio precedente, il set di dati non aveva un'intestazione.
Quando i tuoi dati hanno intestazioni, devi specificarlo nel codice in modo che l'ordinamento possa iniziare dalla seconda riga del set di dati.
Supponiamo di avere un set di dati come mostrato di seguito:
Di seguito il codice che ordinerà i dati in ordine decrescente in base alle vendite dei negozi.
Sub SortDataWithHeader() Range("DataRange").Chiave di ordinamento1:=Range("C1"), Order1:=xlDescending End Sub
Nota che ho creato un intervallo denominato - "DataRange" e ho utilizzato questo intervallo denominato nel codice.
Ordinamento di più colonne con intestazioni
Finora in questo tutorial abbiamo visto come ordinare una singola colonna (con e senza intestazioni).
Ora, cosa succede se vuoi ordinare in base a più colonne.
Ad esempio, nel set di dati seguente, cosa succede se voglio prima ordinare per codice di stato e poi per negozio.
Ecco il codice che ordinerà più colonne contemporaneamente.
Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Intestazione = xlSì .Applica Fine con End Sub
Di seguito è riportato il risultato che otterrai.
Nell'esempio sopra, i dati vengono prima ordinati per codice di stato (colonna A). Quindi, all'interno dei dati del codice di stato, viene nuovamente ordinato dallo Store (colonna B). Questo ordine è determinato dal codice in cui lo menzioni.
Ordinamento dei dati utilizzando il doppio clic sull'intestazione
Se stai creando una dashboard o desideri una maggiore facilità d'uso nei tuoi rapporti, puoi scrivere un codice VBA che ordinerà i dati quando fai doppio clic sulle intestazioni.
Qualcosa come mostrato di seguito:
Di seguito è riportato il codice che ti consentirà di farlo:
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) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes End If End Sub
Nota che ho creato un intervallo denominato ("DataRange") e l'ho usato nel codice invece di usare i riferimenti di cella.
Non appena si fa doppio clic su una delle intestazioni, il codice disabilita la consueta funzionalità di doppio clic (che consiste nell'entrare in modalità di modifica) e utilizza quella cella come chiave durante l'ordinamento dei dati.
Si noti inoltre che a partire da ora, questo codice ordinerà tutte le colonne solo in ordine crescente.
Si noti che il doppio clic è un trigger che consente a Excel di eseguire il codice specificato. Questi trigger come il doppio clic, l'apertura di una cartella di lavoro, l'aggiunta di un nuovo foglio di lavoro, la modifica di una cella, ecc. sono chiamati eventi e possono essere utilizzati per eseguire macro in Excel. Puoi leggere di più sugli eventi VBA di Excel qui.Dove mettere questo codice?
È necessario incollare questo codice nella finestra del codice del foglio in cui si desidera questa funzionalità di ordinamento con doppio clic.
Per fare questo:
- Fare clic con il pulsante destro del mouse sulla scheda del foglio.
- Fare clic su Visualizza codice.
- Incolla il codice nella finestra del codice del foglio in cui risiedono i tuoi dati.
Ora cosa succede se vuoi ordinare le prime due colonne ("Stato" e "Negozio") in ordine crescente, ma la colonna "Vendite" in ordine decrescente.
Ecco il codice che lo farà:
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: =Ordina End If End Sub
Nel codice sopra, controlla se la cella su cui si fa doppio clic è l'intestazione Sales o meno. Se sì, allora assegna il valore xlDescending alla variabile SortOrder, altrimenti lo rende xlAscending.
Ora facciamo un ulteriore passo avanti e mostriamo un Marker visivo (freccia e cella colorata) nell'intestazione quando viene ordinato.
Qualcosa come mostrato di seguito:
Per ottenere ciò, ho aggiunto un nuovo foglio di lavoro e apportato le seguenti modifiche (puoi scaricare il file di esempio e seguire):
- Modificato il nome del nuovo foglio in "BackEnd".
- Nella cella B2, inserisci un simbolo di freccia (per farlo, vai su Inserisci e fai clic sull'opzione "Simbolo").
- Copia e incolla le intestazioni dal set di dati nella cella A3: C3 nel foglio "Backend".
- Utilizzare la seguente funzione nella cella A4:AC4:
=SE(LA3=$C$1,A3&" "&$B$1,A3)
- Il resto delle celle verrà automaticamente riempito dal codice VBA quando fai doppio clic sulle intestazioni per ordinare la colonna.
Il tuo foglio di backend assomiglierebbe a qualcosa come mostrato di seguito:
Ora puoi utilizzare il codice seguente per ordinare i dati facendo doppio clic sulle intestazioni. Quando fai doppio clic su un'intestazione, otterrà automaticamente la freccia nel testo dell'intestazione. Nota che ho anche usato la formattazione condizionale per evidenziare anche la cella.
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 Annulla = 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).Value Next i End If End Sub
Nota che questo codice funziona bene per il modo in cui sono costruiti i miei dati e la mia cartella di lavoro. Se cambi la struttura dei dati, dovrai modificare il codice di conseguenza.
Scarica il file di esempio
