Excel: rank dei Behaviour Points per stato senza buchi con FILTER e UNIQUE

Devi assegnare un rank crescente ai Behaviour Points per ogni Application Status in Excel evitando buchi quando ci sono duplicati? Qui trovi la soluzione moderna con FILTER + UNIQUE, le varianti compatibili con versioni precedenti e una guida passo‑passo con esempi e buone pratiche.

Indice

Il contesto e l’obiettivo

In molti report operativi, CRM e fogli di monitoraggio, i punteggi di comportamento (Behaviour Points) vengono assegnati a elementi associati a uno stato applicativo: Complete, Started, Not Started. L’esigenza tipica è creare una classifica interna a ciascuna categoria, ordinata in modo crescente e priva di salti, anche quando più record condividono lo stesso punteggio.

Se consideriamo il rank come il conteggio dei valori distinti minori o uguali al valore corrente, otteniamo il cosiddetto dense ranking: i duplicati condividono lo stesso numero e non si creano “buchi”. È esattamente ciò che serve per graduatorie lineari come 1‑2‑2‑3‑4 invece di 1‑3‑3‑4‑5.

Perché il rank salta

Una formula molto diffusa parte da COUNTIFS per contare quante righe hanno un valore inferiore a quello corrente più quante hanno lo stesso valore. Un esempio noto è:

=IF($B2="","",COUNTIFS($B:$B,"&lt;"&amp;$B2,$A:$A,$A2)+1+COUNTIFS($B:$B,$B2,$A:$A,$A2)-1)

Con dati raggruppati per Application Status e possibilità di duplicati nei Behaviour Points, questa impostazione finisce per assegnare lo stesso rank a tutti i duplicati ma “calcolandolo come se fosse il successivo disponibile in una classifica tradizionale”, saltando numeri intermedi. Tipico esempio: due elementi Complete con ‑30 ottengono entrambi 3 e il 2 sparisce dal gruppo. Non è l’effetto desiderato quando serve un ordine progressivo e senza interruzioni.

Soluzione con FILTER e UNIQUE

La strada più pulita nelle versioni moderne di Excel (Microsoft 365 o Excel 2021 e successivi) consiste nel contare i valori distinti dei Behaviour Points che, all’interno dello stesso Application Status, sono minori o uguali al valore corrente. La combinazione di FILTER + UNIQUE lo rende semplice e leggibile:

=IF(B2="","",
     COUNT(
         UNIQUE(
             FILTER($B$2:$B$1000,
                    ($A$2:$A$1000=A2)*($B$2:$B$1000&lt;=B2))
         )
     )
)

Come funziona:

FILTER estrae tutti i Behaviour Points che appartengono allo stesso Application Status della riga corrente ($A$2:$A$1000=A2) e che non superano il valore corrente ($B$2:$B$1000<=B2).
UNIQUE rimuove i duplicati dal risultato filtrato, lasciando solo i valori distinti.
COUNT restituisce quanti sono i valori univoci così filtrati: quel numero è il rank “denso”.

Vantaggi pratici:

Rank senza buchi all’interno di ciascun Application Status.
Compatto e chiaro grazie alle funzioni dinamiche.
Stabile in presenza di duplicati, negativi e zeri.

Consiglio: se preferisci una funzione indipendente dal tipo di dato (non solo numeri), puoi sostituire COUNT con ROWS:

=IF(B2="","",
     ROWS(
         UNIQUE(
             FILTER($B$2:$B$1000,
                    ($A$2:$A$1000=A2)*($B$2:$B$1000&lt;=B2))
         )
     )
)

Versione con LET per leggibilità e velocità

Per rendere la formula più manutentiva (e spesso più veloce), incapsula gli intervalli in variabili locali con LET:

=LET(
  st,$A$2:$A$1000,
  bp,$B$2:$B$1000,
  s,A2,
  v,B2,
  IF(v="","",ROWS(UNIQUE(FILTER(bp,(st=s)*(bp&lt;=v)))))
)

Con LET definisci una sola volta gli intervalli, eviti ripetizioni e aiuti Excel a calcolare più efficientemente.

Ordinamento opposto

Se vuoi un rank su ordine decrescente (punteggi più alti con rank più basso) scambia il verso del confronto:

=IF(B2="","",
     ROWS(
         UNIQUE(
             FILTER($B$2:$B$1000,
                    ($A$2:$A$1000=A2)*($B$2:$B$1000&gt;=B2))
         )
     )
)

In alternativa, mantieni la formula base e controlla l’ordinamento finale con SORT o SORTBY in un’area di output.

Variante compatibile con versioni precedenti

Se non disponi delle funzioni dinamiche, puoi ottenere lo stesso esito con SUMPRODUCT e COUNTIFS. È un approccio meno elegante e potenzialmente più pesante sui calcoli, ma efficace:

=IF(B2="","",
     SUMPRODUCT(
        1/COUNTIFS($A$2:$A$1000,$A$2:$A$1000,
                   $B$2:$B$1000,$B$2:$B$1000) *
        ($A$2:$A$1000=A2) *
        ($B$2:$B$1000&lt;=B2))
)

La logica: COUNTIFS conta quante volte appare ciascun valore all’interno del gruppo; il reciproco (1/COUNTIFS) pesa ogni riga in modo che la somma fino al valore corrente equivalga al conteggio dei valori distinti ≤ quello corrente. È di fatto un dense rank calcolato aritmeticamente.

Ulteriori alternative senza funzioni dinamiche includono una colonna di appoggio con FREQUENZA o l’uso di matrici CSE; risultano però meno immediate da mantenere e spiegare a chi utilizza il file.

Esempio dati con confronto visivo

Immaginiamo la seguente struttura tabellare (colonne in A e B), con duplicati e valori negativi. A destra, confrontiamo il rank desiderato con quello errato tipico della formula basata su COUNTIFS.

Application Status	Behaviour Points	Rank desiderato	Rank errato
Complete	-60	1	1
Complete	-30	2	3
Complete	-30	2	3
Complete	-10	3	4
Complete	0	4	5
Started	-45	1	1
Started	-45	1	1
Started	-20	2	3
Started	5	3	4
Not Started	-50	1	1
Not Started	-30	2	3
Not Started	-30	2	3
Not Started	10	3	4

Nel gruppo Complete, i due -30 condividono il rank 2 con la soluzione corretta, mentre con l’approccio errato entrambi finiscono a 3 e il 2 scompare. Lo stesso fenomeno si ripete negli altri gruppi con duplicati.

Passaggi operativi chiari

Prepara i dati: disponi Application Status in colonna A e Behaviour Points in colonna B, con intestazioni alla riga uno e i dati dalla riga due in poi.
Decidi l’intervallo: per prestazioni migliori, preferisci intervalli chiusi (ad esempio $2:$1000) rispetto alle intere colonne; potrai estenderli all’occorrenza.
Inserisci la formula nella colonna rank (per esempio in C2) usando la versione con FILTER + UNIQUE o, se non disponibile, la variante con SUMPRODUCT.
Trascina verso il basso per applicare la formula a tutte le righe. L’IF iniziale impedisce il calcolo su celle vuote.
Eventuale ordinamento: se ti serve mostrare i record in ordine di rank, applica SORTBY sull’output o usa l’ordinamento della tabella.

Riferimenti strutturati nelle tabelle

Se i dati sono in una Tabella di Excel (consigliato), la formula è ancora più leggibile grazie ai riferimenti strutturati. Supponendo una tabella chiamata TblDati con colonne Application Status e Behaviour Points, nella colonna Rank puoi scrivere:

=IF([@[Behaviour Points]]="","",
     ROWS(
         UNIQUE(
             FILTER(TblDati[Behaviour Points],
                    (TblDati[Application Status]=[@[Application Status]])*
                    (TblDati[Behaviour Points]&lt;=[@[Behaviour Points]]))
         )
     )
)

I riferimenti strutturati rendono la formula auto‑esplicativa e si adattano automaticamente se aggiungi o rimuovi righe.

Gestione dei casi particolari

Valori negativi: nessun problema; il confronto <= gestisce naturalmente anche i valori sotto lo zero.
Celle vuote: l’IF in testa impedisce il calcolo su record ancora da compilare; se vuoi essere più restrittivo, usa IF(OR(A2="",B2=""),"",…).
Errori nei dati: per evitare propagazioni, incapsula la formula in IFERROR e restituisci stringa vuota o un messaggio utile.
Valori testuali: se può capitare che i Behaviour Points siano salvati come testo, uniformali con VALUE o applica un doppio meno (--) per forzare il tipo numerico.
Localizzazione: se il tuo Excel usa il punto e virgola come separatore di elenco, sostituisci le virgole (,) con il punto e virgola (;) e utilizza i nomi funzione localizzati.
Interi vs intere colonne: gli intervalli su intere colonne sono comodi ma possono essere più lenti; definisci limiti ragionevoli (ad esempio fino a riga mille o diecimila) e aggiorna quando serve.

Confronto sintetico tra metodi

Metodo	Compatibilità	Prestazioni	Manutenibilità	Note
FILTER + UNIQUE	Microsoft 365 e versioni recenti	Ottime su dataset medio‑grandi	Alta	La più chiara; denso naturale
LET con FILTER + UNIQUE	Microsoft 365 e versioni recenti	Molto buone	Molto alta	Riduce ripetizioni, migliora la leggibilità
SUMPRODUCT con COUNTIFS	Versioni precedenti	Medie	Media	Più pesante, ma risolve senza funzioni dinamiche
FREQUENZA con colonna di appoggio	Versioni precedenti	Medie	Bassa	Funziona ma è più laborioso da documentare

Verifica concettuale rapida

Stesso stato: il ranking riparte da uno per ciascun Application Status.
Confronto corretto: si contano solo i valori nel gruppo che sono ≤ del corrente.
Duplicati: non creano buchi; condividono lo stesso rank.
Vuoti: non vengono valutati finché non si inserisce un numero.
Ripetibilità: trascinando la formula, l’esito è coerente in tutto l’elenco.

Estensioni utili

Ordinamento finale: usa SORTBY per ottenere una vista pronta da stampare, ad esempio ordinando prima per Application Status, poi per rank e infine per Behaviour Points.
Classifica globale: per un rank unico sull’intero dataset (senza distinguere lo stato), rimuovi la condizione sullo stato dal filtro.
Classifiche separate con output dinamico: con DROP, TAKE e VSTACK puoi creare sezioni che elencano solo i primi n per ciascun stato, mantenendo la logica di rank già calcolata.
Convalida dei dati: limita gli inserimenti a numeri e stati ammessi per evitare errori di digitazione che rompono il filtro.
Power Query: se i dati arrivano da fonti esterne, prepara i campi e normalizza i tipi in fase di importazione; la formula lavorerà su un dataset pulito.

Guida passo passo su un caso reale

Supponi di avere tre gruppi (Complete, Started, Not Started) con punteggi anche ripetuti. Inserisci la formula con FILTER + UNIQUE in C2 e trascina. Verifica con questi controlli:

Coerenza nel gruppo: per ogni stato, il primo valore minimo ha rank 1.
Duplicati ravvicinati: tutti i record con lo stesso punteggio esibiscono lo stesso rank.
Passo costante: il successivo punteggio più alto ottiene il rank precedente + 1, non importa quanti duplicati ci siano prima.
Invarianti quando si aggiungono righe: l’inserimento di nuovi record con punteggio già esistente non crea buchi; il rank si riallinea automaticamente.

Consigli pratici per file robusti

Blocca gli estremi con $ se non usi tabelle: eviti scorrimenti accidentali dei riferimenti.
Definisci nomi per gli intervalli (o usa LET) quando la formula è utilizzata in più fogli; documenta il significato dei nomi nel foglio di note.
Evita ambiguità negli stati: uniforma i valori (Complete, Started, Not Started) con un elenco a discesa.
Prestazioni: preferisci gli intervalli chiusi; evita di referenziare intere colonne se il file è grande e contiene formule volatili.
Audit: aggiungi una colonna di diagnostica con il conteggio di distinte fino al valore corrente per controlli rapidi in fase di test.

Domande frequenti

Posso far condividere il rank ai duplicati ma saltare comunque i numeri successivi?
È il comportamento tipico di RANK.EQ o della formula iniziale con COUNTIFS che produce una “classifica da torneo”. Se invece vuoi dense ranking senza buchi, resta sulla combinazione FILTER + UNIQUE o sulla variante con SUMPRODUCT.

Serve ordinare i dati prima?
No. Il rank si basa sul confronto ≤ o ≥; l’ordinamento è solo estetico per la presentazione.

Funziona anche con decimali?
Sì. La logica non cambia; assicurati solo che il tipo dato sia riconosciuto come numerico.

In che modo gestisco un rank discendente?
Scambia <= con >= nella condizione del filtro o ordina a valle con SORT/SORTBY.

Posso usare questa tecnica in una Pivot?
Le tabelle pivot non applicano direttamente formule riga per riga; puoi però aggiungere la colonna di rank nella fonte dati e aggiornare la pivot, oppure calcolare il rank in Power Query/Power Pivot se necessario.

Checklist finale

Hai scelto il metodo in base alla versione di Excel disponibile.
Gli intervalli sono bloccati e coprono tutto il dataset.
La formula esclude righe vuote con un IF iniziale.
I duplicati mostrano lo stesso rank, senza buchi.
Il verso del confronto riflette l’ordinamento desiderato.

Sintesi della soluzione

Per assegnare un rank progressivo ai Behaviour Points all’interno di ciascun Application Status e senza saltare numeri, conta i valori distinti ≤ al valore corrente del gruppo. Nelle versioni moderne di Excel, la formula consigliata è:

=IF(B2="","",
     COUNT(
         UNIQUE(
             FILTER($B$2:$B$1000,
                    ($A$2:$A$1000=A2)*($B$2:$B$1000&lt;=B2))
         )
     )
)

Per versioni precedenti, adotta la variante con SUMPRODUCT:

=IF(B2="","",
     SUMPRODUCT(
        1/COUNTIFS($A$2:$A$1000,$A$2:$A$1000,
                   $B$2:$B$1000,$B$2:$B$1000) *
        ($A$2:$A$1000=A2) *
        ($B$2:$B$1000&lt;=B2))
)

Con questa impostazione, il rank procede regolarmente 1‑2‑3‑4… per ogni stato, anche quando più record presentano lo stesso punteggio di comportamento.

Note pratiche e possibili estensioni

Punto	Suggerimento
Intervalli	Blocca sempre gli estremi con `$` e assicurati che coprano l’intero set di dati.
Ordinamento decrescente	Sostituisci `<=` con `>=` o gestisci l’ordinamento tramite `SORT`/`SORTBY`.
Duplicati	Se desideri che i duplicati condividano lo stesso rank senza buchi, usa `FILTER` + `UNIQUE` o la variante con `SUMPRODUCT`. Se vuoi il comportamento da torneo, torna alla formula iniziale o usa `RANK.EQ`.
Compatibilità	`FILTER` e `UNIQUE` richiedono Microsoft 365 o Excel recenti.
Prestazioni	Preferisci intervalli chiusi e valuta l’uso di `LET` per definire una sola volta gli intervalli.
Tabelle	Trasforma l’origine in Tabella per riferimenti strutturati e aggiornamenti più semplici.

Seguendo queste linee guida, otterrai un rank ordinato, coerente e facile da mantenere nel tempo, perfetto per dashboard operative, analisi e reportistica che richiedono una graduatoria priva di “buchi” a fronte di duplicati nei punteggi.