Come classificare facilmente i numeri in Excel ignorando le celle vuote?
Quando si lavora con dati in Excel, è comune imbattersi in elenchi che contengono celle vuote. Se utilizzi le normali funzioni di classificazione di Excel come RANK o RANK.EQ su tali elenchi, le celle vuote generano spesso errori o risultati indesiderati nel ranking. Questo rende più difficile interpretare i dati, soprattutto se preferisci lasciare le celle vuote effettivamente vuote, evitando errori o posizioni arbitrarie. Classificare correttamente i numeri ignorando automaticamente le celle vuote migliora chiarezza e usabilità dei risultati, rendendo il tuo foglio di calcolo più professionale e immediatamente leggibile.
In questo articolo troverai istruzioni dettagliate per utilizzare formule e macro VBA per portare a termine questa operazione. Le soluzioni complete includono anche spiegazioni sui parametri, suggerimenti pratici e indicazioni per risolvere i problemi più comuni.
- Ordina i valori ignorando le celle vuote in ordine crescente utilizzando formule
- Ordina i valori ignorando le celle vuote in ordine decrescente utilizzando una formula
- Ordina i valori ignorando le celle vuote utilizzando VBA
Ordina i valori ignorando le celle vuote in ordine crescente utilizzando formule
Quando devi assegnare un ranking in ordine crescente ma vuoi ignorare le celle vuote, un approccio comune prevede l’uso di più colonne di supporto e logiche formularie per escludere i valori vuoti dal calcolo del ranking.
Scenario applicabile: Utilizza questo metodo quando desideri ottenere un ranking incrementale (dal più basso al più alto) mantenendo invariata la posizione delle celle vuote, specialmente per intervalli continui in cui le voci mancanti non devono influenzare la numerazione del ranking.
Per eseguire un ranking crescente ignorando le celle vuote, segui questi passaggi, che prevedono due colonne di supporto per costruire il risultato:
1. Seleziona una cella vuota adiacente ai tuoi valori – ad esempio, la cella B2 se il tuo elenco inizia in A2 – e inserisci la seguente formula:
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))La formula restituisce una cella vuota se A2 è vuota; altrimenti genera numeri decimali basati sul valore di A2, aggiungendo .0, .1, .2, .3 e così via man mano che trascini la maniglia di riempimento verso il basso per copiare la formula in tutte le righe contenenti dati.
Spiegazione dei parametri e suggerimenti:
- $A2: La prima cella da ordinare. Modificala se il tuo elenco inizia su una riga diversa.
- $B$2: La cella in cui inserisci questa formula. Presta attenzione ai riferimenti assoluti (ad esempio, $A2 e $B$2) per garantire che la formula funzioni correttamente quando la copi verso il basso.
2. Nella colonna successiva, ad esempio C2, inserisci questa formula per generare un elenco ordinato di valori di supporto:
=SMALL($B$2:$B$8,ROW()-ROW($C$1))Questa formula estrae in sequenza i valori più piccoli, poi i successivi più piccoli e così via da B2:B8 (nota: modifica l’intervallo se i tuoi dati si estendono oltre) man mano che copi la formula verso il basso.
Spiegazione dei parametri:
- $B$2:$B$8: L’intervallo in cui viene utilizzata la precedente (prima) formula di supporto.
- $C$1: la cella immediatamente sopra quella in cui stai inserendo la formula; questo offset determina l’ordine del ranking.
3. Nella cella D2, inserisci la seguente formula per assegnare i ranking, lasciando inalterate le celle vuote:
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")Questa formula confronta il valore in B2 con i risultati ordinati in C2:C8. Se trova una corrispondenza, restituisce il ranking; altrimenti — ad esempio per le celle vuote — non restituisce nulla, lasciando l’aspetto pulito e vuoto. Trascina la maniglia di riempimento verso il basso per applicarla a tutte le righe pertinenti.
Parametri:
- $B2: La cella che contiene il valore di supporto per il ranking.
- $C$2:$C$8: L’intervallo dei valori di supporto, ordinati.
Precauzioni: Se aggiungi o rimuovi dati, ricorda di aggiornare tutti gli intervalli in ogni formula affinché corrispondano alla nuova dimensione dei dati. Per elenchi molto lunghi, valuta l’uso di intervalli dinamici o tabelle di Excel per ridurre le regolazioni manuali degli intervalli.
Risoluzione dei problemi: Se i ranking risultano mancanti o disallineati, assicurati che tutti gli intervalli delle formule di supporto siano perfettamente allineati. Un disallineamento tra le colonne può infatti causare ranking errati o errori imprevisti.
Ordina i valori ignorando le celle vuote in ordine decrescente utilizzando una formula
Quando desideri assegnare un ranking in ordine decrescente (il valore più alto ottiene il rank 1), esiste un approccio più rapido che utilizza una sola formula. Questo è particolarmente utile per punteggi di test, obiettivi di vendita e dataset simili in cui le celle vuote rappresentano informazioni mancanti o non disponibili e non devono occupare posizioni nel ranking né mostrare errori.
Seleziona una cella sulla stessa riga della prima voce di dati, dove desideri visualizzare il risultato, e inserisci:
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))Dopo aver inserito la formula, usa la maniglia di riempimento per copiarla lungo i tuoi dati. Questa formula verifica se la funzione RANKrestituisce un errore (ad esempio, quando)A2 è vuota); in tal caso, lascia il risultato vuoto anziché visualizzare “#N/D”. Se la cella contiene un valore valido, mostra il rank appropriato.
Parametri:
- A2: La cella da classificare (adattala al tuo intervallo dati).
- A$2:A$8: L’intervallo completo dei tuoi dati (usa un riferimento assoluto per poterlo copiare facilmente).
Promemoria sugli errori: Se continui a visualizzare errori “#N/D”, ricontrolla che i riferimenti della formula corrispondano all’intervallo dati previsto e che nelle celle oggetto di ranking non siano presenti valori non numerici.
Ordina i valori ignorando le celle vuote utilizzando VBA
Per gli utenti esperti di macro che desiderano automatizzare il ranking su un intervallo contenente celle vuote – sia in ordine crescente che decrescente – una macro VBA personalizzata semplifica notevolmente il processo, eliminando la necessità di colonne di supporto multiple e la continua manutenzione delle formule.
Come utilizzarla:
1. Vai alla scheda Sviluppo e fai clic su Visual Basic per aprire l’editor Microsoft Visual Basic, Applications. Se la scheda Sviluppo non è visibile, consulta questa guida: Mostra la scheda Sviluppo in Excel.
2. Nella nuova finestra Microsoft Visual Basic, Applications, fai clic su Inserisci > Modulo e incolla uno dei seguenti codici nella finestra del modulo:
- Per eseguire un ranking crescente ignorando le celle vuote:
Sub RankSkipBlank_Ascending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) > NumArr(k) Then ' ← CHANGE HERE temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k ' 1 = smallest Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub - Per eseguire un ranking decrescente ignorando le celle vuote:
Sub RankSkipBlank_Descending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) < NumArr(k) Then temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub
3. Premi F5 per eseguire la macro. Una finestra di dialogo ti chiederà di selezionare l’intervallo da classificare. Successivamente, un’altra finestra ti chiederà di scegliere la prima cella in cui inserire i risultati del ranking. La macro genererà quindi i rank a partire dalla cella selezionata, lasciando vuote le celle corrispondenti agli eventuali valori mancanti nell’intervallo di origine.
Suggerimenti:
- Se non accade nulla, assicurati che le macro siano abilitate e di disporre dell’autorizzazione necessaria per eseguire codice nella tua cartella di lavoro.
- L’esecuzione di una macro VBA non può essere annullata: assicurati di copiare o salvare una copia di backup dei tuoi dati prima di procedere.
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi