Come classificare i valori per gruppo in Excel?

Lavorare con dati raggruppati in Excel richiede spesso il confronto di valori all'interno di ciascun gruppo, come ad esempio la classificazione delle cifre di vendita per regione, i punteggi dei test per classe o gli importi delle transazioni per categoria. Sebbene Excel offra strumenti potenti per la classificazione dei dati, la classificazione all'interno di 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 record migliori e peggiori 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 classificazione 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 ad esempio la valutazione degli studenti per classe o l'elenco delle vendite per diverse regioni, Excel non ha una funzione diretta di "classifica per gruppo". Tuttavia, una formula ben strutturata può raggiungere efficacemente la classifica per gruppo senza manipolazioni aggiuntive dei dati.

Per fare questo, 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 ciascun 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—di solito, nella prima riga accanto ai 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 ogni 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 di 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 partono 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 rilevanti 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 è ampio, 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 la stessa classifica a valori uguali all'interno dello stesso gruppo. Se richiedi classifiche sequenziali uniche, considera l'uso di colonne di supporto aggiuntive.

Questo metodo basato su formule è 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 potrebbero rallentare a causa dell'affidamento sulla logica matriciale.

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

Per gli utenti che desiderano automatizzare il processo di classificazione, 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 più rapidamente rispetto a formule complesse. Questo è ideale per scenari come la generazione di report programmati, attività di classificazione ripetute o quando si vuole evitare il disordine delle 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 i tasti Alt + F11 per abilitare l'editor VBA. Nella finestra Microsoft Visual Basic for Applications che appare, clicca Inserisci > Modulo, quindi incolla 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

2. Clicca Esegui. Una finestra di dialogo ti chiederà di selezionare l'intero intervallo dei dati, la colonna del gruppo e la colonna del valore. La macro genererà quindi una nuova colonna con le classifiche per ciascun 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 classificazione corretta (in base alla struttura dei tuoi 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 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 di reporting automatizzati.

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!