Come si possono classificare i valori per gruppo in Excel?
Quando si lavora con dati raggruppati in Excel, spesso è necessario confrontare i valori all'interno di ciascun gruppo, ad esempio classificare le vendite per area geografica, i punteggi dei test per classe o gli importi delle transazioni per categoria. Sebbene Excel offra strumenti avanzati per la classificazione dei dati, la classificazione all'interno dei gruppi (nota anche come «classificazione per gruppo» o «classificazione condizionale») richiede un approccio specifico. Questo è particolarmente utile quando occorre valutare le prestazioni o identificare i record migliori e peggiori tra diverse categorie senza mescolare i risultati tra i gruppi. I metodi seguenti illustrano soluzioni pratiche per classificare i valori per gruppo, facilitando l'interpretazione e l'analisi accurata dei dati nelle attività quotidiane.
Classificare i valori per gruppo
Codice VBA – Utilizzare una macro per automatizzare la classificazione dei valori all'interno di ciascun gruppo
Classificare i valori per gruppo
Quando è necessario classificare i valori all’interno di gruppi distinti — ad esempio assegnare punteggi agli studenti per classe o ordinare le vendite per area geografica — Excel non offre una funzione diretta per la «classifica per gruppo». Tuttavia, con una formula ben strutturata è possibile ottenere in modo rapido ed efficiente una classifica per gruppo, senza dover manipolare ulteriormente i dati.
A tal fine, è possibile utilizzare una formula matriciale che combina test logici con funzioni di aggregazione, consentendo di confrontare ciascun valore esclusivamente all’interno del proprio gruppo e di generare così il rango richiesto per ogni punto dati.
Seguire questi passaggi:
- Prepara i dati raggruppati in colonne, ad esempio Gruppo (A2:A11) e Valore (B2:B11).
- Seleziona una cella vuota adiacente ai dati—di solito nella prima riga accanto ai valori, ad esempio la cella C2.
- Immettere la seguente formula:
=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1Questa formula funziona contando quanti valori appartenenti allo stesso gruppo risultano inferiori al valore corrente. Ecco il significato di ciascun parametro:
- ($A$2:$A$11=A2)
→ Verifica se ogni cella nell'intervallo A2:A11 corrisponde al valore in A2.
→ Restituisce un array di valori VERO/FALSO (o 1/0) che indica se ciascuna riga appartiene allo stesso gruppo di A2. - (B2<$B$2:$B$11)
→ Conta quanti valori nell'intervallo B2:B11 sono maggiori di B2.
→ Restituisce VERO (1) se B2 è minore di un valore specifico, altrimenti FALSO (0). - * (Moltiplicazione)
→ Combina le due condizioni: - Corrispondenza del gruppo (A2)
Il valore in B2 è inferiore agli altri
→ Vengono quindi considerate soltanto le righe appartenenti allo stesso gruppo e con un valore inferiore. - SOMMA.PRODOTTO(...)
→ Somma il numero di righe che soddisfano entrambe le condizioni. - +1
→ La classificazione inizia da 1 (invece che da 0), quindi si aggiunge 1 al conteggio dei valori inferiori.
Una volta inserita la formula in C2, trascina la maniglia di riempimento verso il basso per applicarla a tutte le righe pertinenti del set di dati: la formula si adatterà automaticamente al gruppo e al valore di ciascuna riga, restituendo il relativo rango all’interno del gruppo.
Suggerimenti e precauzioni:
- Se l’intervallo è ampio, ricordati di aggiornare di conseguenza i riferimenti di cella.
- Per ottenere una classificazione in ordine decrescente (ad esempio, con il valore più alto al primo posto), modifica il confronto nella formula da
B2<$B$2:$B$11aB2>$B$2:$B$11. - Per gestire i valori duplicati, questa formula assegna lo stesso rango ai valori identici all’interno dello stesso gruppo. Se sono richiesti ranghi sequenziali univoci, valuta l’uso di colonne helper aggiuntive.
Questo metodo basato su formule è flessibile e si applica facilmente alla maggior parte delle strutture di tabelle raggruppate in Excel. Tuttavia, con set di dati molto grandi, le prestazioni di calcolo potrebbero rallentare a causa dell’uso della logica matriciale.
Codice VBA – Utilizzare una macro per automatizzare la classificazione dei valori all'interno di ciascun gruppo
Per chi desidera automatizzare la classificazione o gestire set di dati più ampi in modo più efficiente, scrivere una macro VBA rappresenta un’ottima soluzione. Le macro permettono di automatizzare operazioni ripetitive, offrono una maggiore personalizzazione ed elaborano i dati più rapidamente rispetto all’uso di formule complesse. Questo approccio è ideale per scenari come la generazione pianificata di report, attività di classificazione ricorrenti o quando si vuole evitare di appesantire il foglio di lavoro con troppe formule.
Prima di procedere, assicurati di salvare il tuo lavoro e di abilitare le macro nelle impostazioni di Excel. Di seguito ti mostriamo come creare ed eseguire questa soluzione:
- Premere Alt + F11 per aprire l'editor VBA. Nella finestra di Microsoft Visual Basic for Applications visualizzata, fare clic su Inserisci > Modulo, quindi incollare il codice seguente nel modulo aperto:
Sub RankValuesByGroup()
Dim DataRange As Range
Dim GroupRng As Range
Dim ValueRng As Range
Dim OutCol As Range
Dim dictGroups As Object
Dim arrValues, arrRanks
Dim i As Long, j As Long
Dim GroupKey As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data table range (including group and value columns)", xTitleId, Selection.Address, Type:=8)
If DataRange Is Nothing Then Exit Sub
Set GroupRng = Application.InputBox("Select the group column within your range", xTitleId, DataRange.Columns(1).Address, Type:=8)
Set ValueRng = Application.InputBox("Select the value column to rank within your range", xTitleId, DataRange.Columns(2).Address, Type:=8)
Set OutCol = DataRange.Offset(0, DataRange.Columns.Count).Resize(DataRange.Rows.Count, 1)
OutCol.Cells(1).Value = "RankByGroup"
Set dictGroups = CreateObject("Scripting.Dictionary")
arrValues = ValueRng.Value
arrRanks = ValueRng.Value
' Build group dictionaries for ranking
For i = 2 To UBound(arrValues, 1)
GroupKey = GroupRng.Cells(i, 1).Value
If Not dictGroups.Exists(GroupKey) Then
dictGroups.Add GroupKey, CreateObject("System.Collections.ArrayList")
End If
dictGroups(GroupKey).Add arrValues(i, 1)
Next i
' Rank within each group
For i = 2 To UBound(arrValues, 1)
GroupKey = GroupRng.Cells(i, 1).Value
Dim countLower As Long
countLower = 0
For j = 0 To dictGroups(GroupKey).Count - 1
If dictGroups(GroupKey)(j) < arrValues(i, 1) Then
countLower = countLower + 1
End If
Next j
arrRanks(i, 1) = countLower + 1
Next i
' Output results
For i = 2 To UBound(arrRanks, 1)
OutCol.Cells(i, 1).Value = arrRanks(i, 1)
Next i
MsgBox "Ranking by group completed.", vbInformation, xTitleId
End Sub- Fai clic su Esegui. Verrà visualizzata una finestra di dialogo in cui ti verrà chiesto di selezionare l’intero intervallo dati, la colonna del gruppo e quella dei valori. La macro genererà quindi una nuova colonna con i ranghi assegnati a ciascun valore all’interno del proprio gruppo.
Note e risoluzione dei problemi:
- Assicurati che le colonne selezionate corrispondano correttamente ai dati: quelle del gruppo e del valore devono essere perfettamente allineate.
- Se l'intestazione dei dati è inclusa, modifica l'indice iniziale del ciclo nel codice per ottenere una classificazione corretta, in base alla struttura dei dati.
- Per ordinare in ordine decrescente, modifica di conseguenza la condizione di confronto nel codice:
If dictGroups(GroupKey)(j) < arrValues(i,1). - In caso di avvisi relativi ad autorizzazioni o alla sicurezza delle macro, controlla le impostazioni di sicurezza delle macro di Excel in File > Opzioni > Centro protezione.
Questo metodo VBA offre flessibilità e prestazioni affidabili, ideali per applicazioni avanzate o su larga scala, specialmente quando integrato in flussi di lavoro automatizzati per la generazione di report.
Sblocca la magia di Excel con KUTOOLS AI
- Esecuzione intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici, il tutto con comandi semplici e intuitivi.
- Formule personalizzate: crea formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA in modo semplice e immediato.
- Interpretazione delle formule: Comprendi con facilità anche le formule più complesse.
- Traduzione del testo: Superate le barriere linguistiche direttamente nei vostri fogli di calcolo.
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