Ordinamento dei dati in Excel VBA
Excel ha un ottimo mezzo per ordinare una serie di dati tabulari utilizzando la barra multifunzione sul front-end di Excel e, a un certo punto, probabilmente vorrai utilizzare questa funzionalità all'interno del tuo codice VBA. Fortunatamente, questo è molto facile da fare.
La finestra di dialogo front-end si trova facendo clic sull'icona "Ordina" nel gruppo "Ordina e filtra" della scheda "Dati" sulla barra multifunzione di Excel. È necessario selezionare prima un intervallo di dati tabulari.
Puoi anche usare Alt-A-S-S per mostrare la finestra di dialogo per un ordinamento personalizzato.
Il metodo di ordinamento è stato notevolmente migliorato nelle versioni successive di Excel. L'ordinamento era limitato a tre livelli, ma ora puoi inserire tutti i livelli di cui hai bisogno, e questo vale anche all'interno di VBA.
Puoi incorporare tutte le funzioni di ordinamento offerte nella finestra di dialogo Ordina di Excel nel tuo codice VBA. La funzione di ordinamento in Excel è veloce e più veloce di qualsiasi cosa tu possa scrivere in VBA, quindi sfrutta la funzionalità.
Tieni presente che quando esegui un ordinamento in VBA, i parametri di ordinamento rimangono gli stessi nella finestra di dialogo di ordinamento front-end. Vengono salvati anche quando viene salvata la cartella di lavoro.
Se un utente seleziona lo stesso intervallo di dati tabulari e fa clic sull'icona Ordina, vedrà tutti i parametri che sono stati inseriti dal codice VBA. Se vogliono fare una sorta di loro proprio design, allora dovranno prima eliminare tutti i tuoi livelli di ordinamento, il che sarà molto fastidioso per loro.
Inoltre, se non si modificano i parametri all'interno del codice e si fa affidamento sui valori predefiniti, è possibile che l'utente abbia apportato modifiche che si rifletteranno nell'ordinamento VBA e potrebbero fornire risultati imprevisti, che potrebbero essere molto difficili da eseguire il debug .
Fortunatamente, esiste un metodo Clear in VBA per reimpostare tutti i parametri di ordinamento in modo che l'utente possa visualizzare una finestra di dialogo di ordinamento pulita
| 1 | Fogli di lavoro("Foglio1").Sort.SortFields.Clear |
È buona norma cancellare i parametri di ordinamento in VBA prima e dopo il completamento dell'ordinamento.
Uso pratico del metodo di ordinamento in VBA
Quando i dati tabulari vengono importati in Excel, sono spesso in un ordine molto casuale. Potrebbe essere importato da un file CSV (valori separati da virgola) o potrebbe provenire da un collegamento a un database o a una pagina web. Non puoi fare affidamento sul fatto che sia in un ordine prestabilito da un'importazione all'altra.
Se stai presentando questi dati a un utente all'interno del tuo foglio di lavoro, l'utente potrebbe avere difficoltà a guardare e comprendere un'enorme quantità di dati che in termini di ordine sono dappertutto. Potrebbero voler raggruppare i dati o tagliare e incollare alcune sezioni di essi in un'altra applicazione.
Potrebbero anche voler vedere, ad esempio, il dipendente più pagato o il dipendente con il servizio più lungo.
Utilizzando il metodo Ordina in VBA, puoi offrire opzioni per consentire un facile ordinamento per l'utente.
Dati di esempio per dimostrare l'ordinamento di Excel con VBA
Per prima cosa abbiamo bisogno di alcuni dati di esempio da inserire in un foglio di lavoro, in modo che il codice possa dimostrare tutte le funzionalità disponibili all'interno di VBA.
Copia questi dati in un foglio di lavoro (chiamato "Foglio1") esattamente come mostrato.
Si noti che sono stati utilizzati diversi colori di sfondo delle celle e colori dei caratteri, in quanto possono essere utilizzati anche come parametri di ordinamento. L'ordinamento utilizzando i colori delle celle e dei caratteri verrà illustrato più avanti nell'articolo. Si noti inoltre che nella cella E3, il nome del dipartimento è tutto minuscolo.
Non sono necessari i colori dell'interno della cella e del carattere se non si desidera utilizzare gli esempi di ordinamento per cella e colore del carattere.
Registrazione di una macro per un ordinamento VBA
Il codice VBA per l'ordinamento può diventare piuttosto complicato e talvolta può essere una buona idea eseguire l'ordinamento sul front-end di Excel e registrare una macro per mostrarti come funziona il codice.
Sfortunatamente, la funzione di registrazione può generare un'enorme quantità di codice perché imposta praticamente tutti i parametri disponibili, anche se i valori predefiniti per molti parametri sono accettabili per l'operazione di ordinamento.
Tuttavia, ti dà un'ottima idea di ciò che è coinvolto nella scrittura del codice di ordinamento VBA e un vantaggio è che il codice registrato funzionerà sempre per te. Il tuo codice potrebbe aver bisogno di test e debug per farlo funzionare correttamente.
Ricorda che per un'operazione eseguita in VBA, non esiste una funzione di annullamento, quindi è una buona idea fare una copia dei dati tabulari su un altro foglio di lavoro prima di iniziare a scrivere il codice di ordinamento.
Ad esempio, se hai eseguito un ordinamento semplice sui dati di esempio sopra, ordinando per Dipendente, la registrazione genererebbe il seguente codice:
| 123456789101112131415161718 | Sottomacro1()Intervallo ("A1: E6"). SelezionaActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormalCon ActiveWorkbook.Worksheets ("Foglio1"). Ordina.SetRange Range ("A1: E6").Intestazione = xlSì.MatchCase = False.Orientamento = xlTopToBottom.SortMethod = xlPinYin.ApplicareTermina conFine sottotitolo |
Questo è un pezzo di codice piuttosto grande e molto non è necessario a causa dell'utilizzo dei parametri predefiniti. Tuttavia, se hai poco tempo per completare un progetto e hai bisogno di un codice che funzioni rapidamente, puoi facilmente incollarlo nel tuo codice VBA.
Tuttavia, se vuoi rendere il tuo codice comprensibile e più elegante, sono disponibili altre opzioni.
Codice VBA per eseguire un ordinamento a livello singolo
Se vuoi ordinare il codice di esempio solo in base a Impiegato come prima durante la registrazione di una macro, il codice è molto semplice:
| 1234567 | Sub SingleLevelSort()Fogli di lavoro("Foglio1").Sort.SortFields.ClearIntervallo("A1:E6").Chiave di ordinamento1:=Intervallo("A1"), Intestazione:=xlSìFine sottotitolo |
Questo è molto più facile da capire rispetto al codice registrato perché accetta i valori predefiniti, ad esempio l'ordinamento crescente, quindi non è necessario impostare i parametri sui valori predefiniti. Ciò presuppone che tu abbia utilizzato un'istruzione "Clear" in anticipo.
Il metodo "Cancella" viene utilizzato inizialmente per garantire che ogni parametro di ordinamento per quel foglio di lavoro venga reimpostato sui valori predefiniti. Un utente potrebbe aver precedentemente impostato i parametri su valori diversi o un ordinamento precedente in VBA potrebbe averli modificati. È importante iniziare da una posizione predefinita durante l'ordinamento, altrimenti potresti facilmente finire con risultati errati.
Il metodo Clear non reimposta il parametro Header ed è consigliabile includerlo nel codice, altrimenti Excel potrebbe tentare di indovinare se una riga di intestazione è presente o meno.
Esegui questo codice sui dati di esempio e il tuo foglio di lavoro sarà simile a questo:
Codice VBA per eseguire un ordinamento multilivello
Puoi aggiungere tutti i livelli di ordinamento richiesti all'interno del tuo codice. Supponiamo di voler ordinare prima per reparto e poi per data di inizio, ma in ordine crescente per il reparto e decrescente per la data di inizio:
| 12345678 | Sub MultiLevelSort()Fogli di lavoro("Foglio1").Sort.SortFields.ClearIntervallo("A1:E6").Chiave di ordinamento1:=Intervallo("E1"), Tasto2:=Intervallo("C1"), Intestazione:=xlSì, _Ordine1:=xlAscendente, Ordine2:=xlDiscendenteFine sottotitolo |
Nota che ora ci sono due chiavi nell'istruzione sort (Key1 e Key2). Prima di tutto viene ordinata la Chiave1 (colonna E del reparto) e poi la Chiave2 (colonna C della data di inizio) in base al primo ordinamento.
Ci sono anche due parametri d'ordine. Ordine1 si associa a Chiave1 (Reparto) e Ordine2 si associa a Chiave2 (Data di inizio). È importante assicurarsi che le chiavi e gli ordini siano mantenuti al passo l'uno con l'altro.
Esegui questo codice sui dati di esempio e il tuo foglio di lavoro sarà simile a questo:
La colonna Reparto (E) è in ordine crescente e la colonna Data di inizio (C) è in ordine decrescente.
L'effetto di questo tipo è più evidente guardando Jane Halfacre (riga 3) e John Sutherland (riga 4). Sono entrambi in Finanza, ma Jane Halfacre ha iniziato prima di John Sutherland e le date sono mostrate in ordine decrescente.
Se l'intervallo di dati tabulari può essere di qualsiasi lunghezza, è possibile utilizzare l'oggetto UsedRange per definire l'intervallo di ordinamento. Funzionerà solo se nel foglio di lavoro sono presenti solo i dati tabulari poiché qualsiasi valore al di fuori dei dati fornirà risultati errati per il numero di righe e colonne.
| 1234567 | Sub MultiLevelSort()Fogli di lavoro("Foglio1").Sort.SortFields.ClearFogli di lavoro("Foglio1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _Ordine1:=xlAscendente, Ordine2:=xlDiscendenteFine sottotitolo |
Ciò previene il problema se si utilizza il metodo 'End(xlDown)' per definire l'intervallo di ordinamento. Se è presente una cella vuota nel mezzo dei dati, qualsiasi cosa dopo la cella vuota non verrà inclusa, mentre UsedRange scende all'ultima cella attiva nel foglio di lavoro.
Ordinamento per colore della cella
Da Excel 2007, è ora possibile ordinare in base al colore di sfondo di una cella, il che offre un'enorme flessibilità durante la progettazione del codice di ordinamento in VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor()Fogli di lavoro("Foglio1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormalCon ActiveWorkbook.Worksheets ("Foglio1"). Ordina.SetRange Range ("A1: E6").ApplicareTermina conFine sottotitolo |
Questo codice ordinerà l'intervallo di dati di esempio (A2: A6) in base al colore di sfondo della cella. Nota che ora c'è un parametro aggiuntivo chiamato "SortOn" che ha il valore di "xlSortOnCellColor".
Si noti che il parametro "SortOn" può essere utilizzato solo da un oggetto foglio di lavoro e non da un oggetto intervallo.
Per questo motivo il codice è più complicato rispetto a un ordinamento che utilizza i valori delle celle.
Questo codice utilizza un valore chiave per l'ordinamento che copre l'intero intervallo di dati, ma è possibile specificare singole colonne come chiave per l'ordinamento del colore di sfondo e utilizzare più livelli come mostrato in precedenza.
Dopo aver eseguito questo codice, il tuo foglio di lavoro sarà ora simile a questo:
Ordinamento per colore del carattere
La funzione di ordinamento in Excel VBA offre ancora più flessibilità in quanto è possibile ordinare in base ai colori dei caratteri:
| 1234567891011121314 | Sub SingleLevelSortByFontColor()Fogli di lavoro("Foglio1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add(Range("A2:A6"), _xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)Con ActiveWorkbook.Worksheets ("Foglio1"). Ordina.SetRange Range ("A1: E6").Intestazione = xlSì.Orientamento = xlTopToBottom.ApplicareTermina conFine sottotitolo |
Il codice per l'ordinamento in base al colore del carattere è molto più complicato rispetto al colore di sfondo della cella. Il parametro "SortOn" ora contiene il valore di "xlSortOnFontColor".
Nota che devi specificare l'orientamento come "xlTopToBottom" e devi specificare un colore su cui ordinare. Questo è specificato in termini RGB (Rosso, Verde, Nero) con valori che vanno da 0 a 255.
Dopo aver eseguito questo codice sui dati di esempio, il foglio di lavoro sarà ora simile a questo:
L'ordinamento utilizzando i colori in VBA è molto più complicato di un ordinamento multilivello, ma se il tuo codice di ordinamento non funziona (cosa che può accadere se manca un parametro o se non hai inserito il codice correttamente), puoi sempre ricorrere alla registrazione una macro e integrando il codice registrato nel tuo VBA.
Utilizzo di altri parametri nell'ordinamento VBA
Ci sono una serie di parametri facoltativi che puoi usare nel tuo codice VBA per personalizzare il tuo ordinamento.
Ordina su
SortOn sceglie se l'ordinamento utilizzerà i valori delle celle, i colori di sfondo delle celle oi colori dei caratteri delle celle. L'impostazione predefinita è Valori cella.
| 1 | SortOn = xlSortOnValues |
Ordine
Ordine sceglie se l'ordinamento verrà eseguito in ordine crescente o decrescente. L'impostazione predefinita è Crescente.
| 1 | Ordine = xlAscendente |
DataOption
DataOption sceglie come vengono ordinati testo e numeri. Il parametro xlSortNormal ordina separatamente i dati numerici e di testo. Il parametro xlSortTextAsNumbers considera il testo come dati numerici per l'ordinamento. Il valore predefinito è xlSortNormal.
| 1 | DataOption = xlSortNormal |
Intestazione
Intestazione sceglie se l'intervallo di dati tabulari ha una riga di intestazione o meno. Se è presente una riga di intestazione, non vuoi che sia inclusa nell'ordinamento.
I valori dei parametri sono xlYes, xlNo e xlYesNoGuess. xlYesNoGuess lascia a Excel il compito di determinare se è presente una riga di intestazione, che potrebbe facilmente portare a risultati incoerenti. L'uso di questo valore non è raccomandato.
Il valore predefinito è XNo (nessuna riga di intestazione all'interno dei dati). Con i dati importati, di solito è presente una riga di intestazione, quindi assicurati di impostare questo parametro su xlYes.
| 1 | Intestazione = xlSì |
MatchCase
Questo parametro determina se l'ordinamento fa distinzione tra maiuscole e minuscole o meno. I valori delle opzioni sono Vero o Falso. Se il valore è False, i valori in minuscolo sono considerati uguali ai valori in maiuscolo. Se il valore è True, l'ordinamento mostrerà la differenza tra i valori maiuscoli e minuscoli all'interno dell'ordinamento. Il valore predefinito è falso.
| 1 | MatchCase = False |
Orientamento
Questo parametro determina se l'ordinamento avverrà verso il basso attraverso le righe o attraverso tutte le colonne. Il valore predefinito è xlTopToBottom (ordina per righe). Puoi usare xlLeftToRight se desideri ordinare orizzontalmente. Valori come xlRows e xlColumns non funzionano per questo parametro.
| 1 | Orientamento = xlTopToBottom |
OrdinaMetodo
Questo parametro viene utilizzato solo per l'ordinamento delle lingue cinesi. Ha due valori, xlPinYin e xlStroke. xlPinYin è il valore predefinito.
xlPinYin ordina utilizzando l'ordinamento fonetico cinese per i caratteri. xlStroke ordina in base alla quantità di tratti in ogni carattere.
Se registri una macro di ordinamento, questo parametro sarà sempre incluso nel codice e potresti esserti chiesto cosa significasse. Tuttavia, a meno che non si tratti di dati in cinese, è di scarsa utilità.
| 1 | SortMethod = xlPinYin |
Utilizzo di un evento doppio clic per ordinare i dati tabulari
In tutte le funzionalità che Microsoft includeva nei metodi di ordinamento per VBA, non includeva un semplice mezzo per fare doppio clic sull'intestazione di una colonna e ordinare l'intero dato tabulare in base a quella particolare colonna.
Questa è una funzionalità davvero utile ed è facile scrivere il codice per farlo.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)"Si presume che i dati inizino dalla cella A1"'Crea tre variabili per catturare la colonna di destinazione selezionata e la colonna e la riga massime di _'i dati tabulariDim Col As Integer, RCol As Long, Rrow As Long'Verifica che l'utente abbia fatto doppio clic sulla riga di intestazione - riga 1 altrimenti esci da subSe Target.Row 1 Quindi esci da Sub'Cattura le righe massime nell'intervallo di dati tabulari utilizzando l'oggetto 'UsedRange'RCol = ActiveSheet.UsedRange.Columns.Count'Cattura le colonne massime nell'intervallo di dati tabulari utilizzando l'oggetto 'UsedRange'RRow = ActiveSheet.UsedRange.Rows.Count"Verifica che l'utente non abbia fatto doppio clic su una colonna al di fuori dell'intervallo di dati tabulari"Se Target.Column > RCol quindi esci da Sub'Cattura la colonna su cui l'utente ha fatto doppio clicCol = Target.Colonna'Cancella parametri di ordinamento precedentiActiveSheet.Sort.SortFields.Clear'Ordina l'intervallo tabulare come definito dal numero massimo di righe e colonne dall'oggetto 'UsedRange''Ordina i dati tabulari utilizzando la colonna su cui ha fatto doppio clic l'utente come chiave di ordinamentoActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Chiave di ordinamento1:=Cells(1, Col), Header:=xlSì'Seleziona la cella A1 - questo serve per garantire che l'utente non venga lasciato in modalità di modifica dopo che l'ordinamento è _' completatoActiveSheet.Range("A1").SelezionaFine sottotitolo |
Questo codice deve essere inserito nell'evento di doppio clic sul foglio contenente i dati tabulari. Puoi farlo facendo clic sul nome del foglio di lavoro nella finestra Esplora progetti (angolo in alto a sinistra della schermata VBE), quindi selezionando "Foglio di lavoro" nel primo menu a discesa nella finestra del codice. Seleziona "BeforeDoubleClick" nel secondo menu a discesa, quindi puoi inserire il tuo codice.
Si noti che nessun nome, intervallo o riferimento di cella è hardcoded in questo codice tranne che per spostare il cursore sulla cella A1 alla fine del codice. Il codice è progettato per ottenere tutte le informazioni richieste dalle coordinate della cella su cui l'utente ha fatto doppio clic e dalla dimensione dell'intervallo di dati tabulari.
Non importa quanto sia grande l'intervallo di dati tabulari. Il codice raccoglierà comunque tutte le informazioni richieste e può essere utilizzato sui dati conservati ovunque all'interno della cartella di lavoro senza dover codificare i valori.
L'unico presupposto è che ci sia una riga di intestazione nei dati tabulari e che l'intervallo di dati inizi dalla cella A1, ma la posizione iniziale per l'intervallo di dati può essere facilmente modificata all'interno del codice.
Qualsiasi utente sarà adeguatamente impressionato da questa nuova funzionalità di ordinamento!
Estensione della funzione di ordinamento utilizzando VBA
Microsoft ha consentito un'enorme flessibilità nell'ordinamento utilizzando un'ampia gamma di parametri. Tuttavia, all'interno di VBA, puoi andare oltre.
Supponiamo di voler ordinare qualsiasi valore con un carattere in grassetto nella parte superiore dei dati. Non c'è modo di farlo in Excel, ma puoi scrivere il codice VBA per farlo:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortBold()'Crea variabili per contenere il numero di righe e colonne per i dati tabulariDim Rrow quanto a lungo, Rcol quanto a lungo, N quanto a lungo'Disattiva l'aggiornamento dello schermo in modo che l'utente non possa vedere cosa sta succedendo - potrebbe vedere _'valori alterati e mi chiedo perchéApplication.ScreenUpdating = False'Cattura il numero di colonne nell'intervallo di dati tabulariRCol = ActiveSheet.UsedRange.Columns.Count'Cattura il numero di righe all'interno dell'intervallo di dati tabulariRRow = ActiveSheet.UsedRange.Rows.Count'Esegui itera su tutte le righe nell'intervallo di dati tabulari ignorando la riga di intestazionePer N = 2 a RRow'Se una cella ha un carattere in grassetto, posiziona uno 0 iniziale contro il valore della cellaSe ActiveSheet.Cells(N, 1).Font.Bold = True AlloraActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).ValueFinisci seSuccessivo N'Cancella tutti i parametri di ordinamento precedentiActiveSheet.Sort.SortFields.Clear'Ordina l'intervallo di dati tabulari. Tutti i valori con uno 0 iniziale si sposteranno in altoActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Chiave di ordinamento1:=Cells(1, 1), Header:=xlSì'Esegui itera su tutte le righe nell'intervallo di dati tabulari ignorando la riga di intestazionePer N = 2 a RRow'Se una cella ha un carattere in grassetto, rimuovi il valore 0 iniziale dal valore della cella in _'ripristina i valori originaliSe ActiveSheet.Cells(N, 1).Font.Bold = True AlloraActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)Finisci seSuccessivo N"Riattiva l'aggiornamento dello schermo"Application.ScreenUpdating = TrueFine sottotitolo |
Il codice elabora la dimensione dell'intervallo di dati tabulari utilizzando l'oggetto "UsedRange" e quindi scorre tutte le righe al suo interno. Quando viene trovato un carattere in grassetto, viene posizionato uno zero davanti al valore della cella.
Avviene quindi una sorta. Poiché l'ordinamento è in ordine crescente, qualsiasi cosa con uno zero davanti andrà in cima all'elenco.
Il codice quindi scorre tutte le righe e rimuove gli zeri iniziali, ripristinando i dati ai valori originali.
Questo codice ordina utilizzando caratteri in grassetto come criterio, ma potresti facilmente utilizzare altre caratteristiche della cella allo stesso modo, ad esempio carattere corsivo, dimensione in punti del testo, carattere di sottolineatura, nome del carattere, ecc.
