Come classificare i valori per gruppo in Excel?

Lavorare con dati raggruppati in Excel richiede spesso di confrontare i valori all'interno di ogni gruppo, come ad esempio classificare i dati di vendita per regione, i punteggi dei test per classe o gli importi delle transazioni per categoria. Sebbene Excel offra strumenti potenti per classificare i dati, la classificazione all'interno dei gruppi (nota anche come "classifica per gruppo" o "classifica condizionale") richiede un approccio specifico. Questo è particolarmente utile quando è necessario valutare le prestazioni o identificare i migliori e i peggiori risultati tra diverse categorie senza mescolare i risultati tra i gruppi. I seguenti metodi esplorano soluzioni pratiche per classificare i valori per gruppo, rendendo più facile interpretare e analizzare accuratamente i dati nelle attività quotidiane.

Classifica i valori per gruppo
Codice VBA - Usa una macro per automatizzare la classifica dei valori all'interno di ogni gruppo

Classifica i valori per gruppo

Per situazioni in cui è necessario classificare i valori all'interno di gruppi distinti, come assegnare voti agli studenti per classe o elencare le vendite per diverse regioni, Excel non dispone di una funzionalità diretta "classifica per gruppo". Tuttavia, una formula ben strutturata può ottenere una classifica per gruppo in modo efficiente e senza manipolazioni extra dei dati.

Per fare ciò, puoi utilizzare una formula matriciale che combina test logici con funzioni di aggregazione. Questo approccio ti consente di confrontare ciascun valore solo all'interno del suo gruppo designato, producendo la classifica richiesta per ogni punto dati.

Segui questi passaggi:

• Organizza i tuoi dati raggruppati in colonne, come Gruppo (A2:A11) e Valore (B2:B11).
• Seleziona una cella vuota accanto ai tuoi dati—generalmente, nella prima riga accanto ai tuoi valori, ad esempio, la cella C2.
• Inserisci la seguente formula:

=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1

Questa formula funziona contando quanti valori all'interno dello stesso gruppo sono inferiori al valore corrente. Ecco cosa significa ciascun parametro:

• ($A$2:$A$11=A2)
  → Questo verifica se ogni cella nell'intervallo A2:A11 è uguale al valore in A2.
  → Restituisce un array di valori VERO/FALSO (o 1/0) che rappresentano se ogni riga appartiene allo stesso gruppo di A2.
• (B2<$B$2:$B$11)
  → Questo verifica quanti valori in B2:B11 sono maggiori di B2.
  → Restituisce VERO (1) se B2 è minore di un determinato valore, FALSO (0) altrimenti.
• * (Moltiplicazione)
  → Questo combina le due condizioni:
• Corrispondenza del gruppo (A2)
  Il valore in B2 è più piccolo degli altri
  → Quindi, verranno conteggiate solo le righe che appartengono allo stesso gruppo e hanno un valore più piccolo.
• SOMMAPRODOTTO(...)
  → Somma il numero di righe che soddisfano entrambe le condizioni.
• +1
  → Le classifiche iniziano da 1 (invece di 0), quindi aggiungiamo 1 al conteggio dei valori più piccoli.

Una volta inserita la formula in C2, trascina la maniglia di riempimento automatico verso il basso per applicare questa formula a tutte le righe pertinenti nel tuo set di dati. La formula si adatterà automaticamente per utilizzare il gruppo e il valore di ogni riga, restituendo la classifica all'interno di quel gruppo.

Consigli e precauzioni:

• Se il tuo intervallo è grande, ricorda di aggiornare di conseguenza i riferimenti alle celle.
• Per classifiche in ordine decrescente (ad esempio, il valore più alto è 1), modifica il confronto della formula da B2<$B$2:$B$11 a B2>$B$2:$B$11.
• Per gestire valori duplicati, questa formula assegna lo stesso rango a valori uguali all'interno dello stesso gruppo. Se richiedi ranghi sequenziali univoci, considera l'uso di colonne di supporto aggiuntive.

Questo metodo basato sulla formula è flessibile e si applica facilmente alla maggior parte delle strutture di tabelle raggruppate in Excel. Tuttavia, per set di dati molto grandi, le prestazioni di calcolo possono rallentare a causa della dipendenza dalla logica matriciale.

Codice VBA - Usa una macro per automatizzare la classifica dei valori all'interno di ogni gruppo

Per gli utenti che desiderano automatizzare il processo di classifica o gestire set di dati più grandi in modo più efficiente, scrivere una macro VBA può essere un approccio prezioso. Le macro possono automatizzare passaggi ripetitivi, offrire maggiore personalizzazione ed elaborare i dati rapidamente rispetto alle formule complesse. Questo è ideale per scenari come la generazione di report programmata, attività di classifica ripetute o quando si vuole evitare confusione di formule nel foglio di lavoro.

Prima di procedere, assicurati di salvare il tuo lavoro e abilitare le macro nelle impostazioni di Excel. Ecco come puoi scrivere ed eseguire questa soluzione:

1. Premi Alt + F11 per abilitare l'editor VBA. Nella finestra Microsoft Visual Basic for Applications che appare, clicca Inserisci > Modulo, quindi incolla il seguente codice 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

2. Clicca Esegui. Una finestra di dialogo ti chiederà di selezionare l'intero intervallo di dati, la colonna del gruppo e la colonna del valore. La macro genererà quindi una nuova colonna con i ranghi per ogni valore all'interno del suo gruppo.

Note e risoluzione dei problemi:

• Assicurati che le selezioni delle colonne corrispondano ai tuoi dati: le colonne del gruppo e del valore devono essere allineate correttamente.
• Se l'intestazione dei dati è inclusa, regola l'indice iniziale del ciclo nel codice per una corretta classifica (in base alla tua struttura dati).
• Per classificare in ordine decrescente, modifica il confronto If dictGroups(GroupKey)(j) < arrValues(i,1) di conseguenza.
• Se incontri avvisi di autorizzazione o sicurezza delle macro, controlla le impostazioni di Sicurezza delle Macro di Excel sotto File > Opzioni > Centro Protezione.

Questo metodo VBA offre flessibilità e prestazioni robuste per applicazioni più avanzate o su larga scala, specialmente quando integrato con flussi di lavoro automatizzati per la creazione di report.

Sblocca la Magia di Excel con Kutools AI

• Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
• Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
• Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
• Interpretazione delle Formule: Comprendi facilmente formule complesse.
• Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.

Potenzia le tue capacità di Excel con strumenti alimentati dall'IA. Scarica Ora ed esperisci un'efficienza mai vista prima!