Come utilizzare la funzione Rank e SUMPRODUCT in Excel con COUNTIF

La classificazione degli elementi in ordine crescente o decrescente utilizzando la combinazione delle funzioni COUNTIF, RANK.EQ e SUMPRODUCT è relativamente semplice. È possibile ordinare gli elementi con o senza duplicazione e mantenere il controllo per saltare o non saltare i ranghi nella sequenza.

Assegnando gli stessi gradi a più voci identiche, la funzione RANK.EQ salta i numeri nella sequenza complessiva. Per evitare questo salto, utilizzare la funzione SUMPRODUCT con la funzione COUNTIF. Questa combinazione classifica i valori duplicati senza saltare i numeri.

Diverse funzioni di rango in Excel

Prima di dare un’occhiata più da vicino alla sua implementazione, esaminiamo brevemente i suoi altri due moduli trovati in Excel; RANK.AVG e RANK.

Nelle versioni precedenti di Excel, era disponibile solo la funzione RANK. Tuttavia, da Excel 2010, sono state aggiunte due ulteriori funzioni di classificazione, ovvero RANK.AVG e RANK.EQ. Esploriamo le loro differenze, poiché probabilmente le vedrai tutte in Excel.

Funzione RANK.EQ: RANK.EQ è una versione nuova e migliorata della funzione RANK ed è supportata nelle ultime versioni di Excel. Entrambe le funzioni, tuttavia, sono le stesse e hanno lo stesso scopo. Qualunque cosa tu usi, dà gli stessi risultati.

Funzione RANK: La funzione RANK, d’altra parte, è disponibile per compatibilità con Excel 2007 e precedenti e può essere rimossa in qualsiasi nuova versione di Excel. Oggi tutti usano le versioni Excel più recenti, quindi usa sempre la funzione RANK.EQ quando devi condividere fogli con altri.

RANGO. AVG: quando una sequenza di classificazione contiene i duplicati, la funzione RANK.AVG assegna una classificazione media a ciascun set di duplicati. Pertanto, ha lo stesso scopo di classificare gli oggetti, ma il suo scopo principale è quello di catturare i duplicati.

Poiché questo articolo si occupa del calcolo dei ranghi per l’elenco degli elementi, vediamo come la funzione RANK.EQ li classificherà. Inoltre, vedrai il suo utilizzo in combinazione con la funzione CONTA.SE.

Prima di implementare la formula, diamo una breve panoramica della sintassi della funzione RANK.EQ.

Una panoramica della funzione di rango di Excel

Quindi, ci sono tre argomenti nella funzione RANK.EQ; numero, riferimento e ordine. I primi due argomenti sono obbligatori, mentre il terzo è facoltativo. Per eseguire RANK.EQ su un array di numeri, è necessario specificare sempre i primi due argomenti.

Diamo un’occhiata alla funzione di ogni argomento:

  1. Numero: questo argomento si riferisce al numero di cui si desidera conoscere il rango.
  2. Ref: l’ argomento Ref fa riferimento a un array di elenchi di numeri nella sequenza. Tieni presente che ref ignora tutti i valori non numerici trovati nell’array.
  3. Ordine: nonostante sia un argomento opzionale, è possibile specificare la funzione RANK per classificare gli elementi in ordine decrescente o crescente scegliendo 0 o 1 in questo argomento. Per impostazione predefinita, se lo lasci fuori, classificherà gli elementi in ordine decrescente.

Esempio di funzione di rango di Excel

Quindi, ora che hai compreso gli argomenti della funzione RANK.EQ, mettiamola in pratica su un set di dati per vedere come funziona.

Ad esempio, dai un’occhiata ai dati campione contenenti le percentuali di dieci studenti. È possibile utilizzare la funzione Rank.EQ per calcolare rapidamente i ranghi in base alle percentuali di questi studenti. Ciò elimina la necessità di calcolarli manualmente.

Tuttavia, salta i numeri quando sono coinvolti i duplicati, quindi potrebbe essere necessario utilizzare altre funzioni discusse più avanti nell’articolo. Di seguito l’elenco degli studenti con le loro percentuali finali.

Il primo argomento è il numero che vuoi classificare, che è la percentuale di James Walker e altri studenti nella riga per questo esempio. Quindi, seleziona la cella B2 come primo argomento.

L’intervallo di riferimento, in questo caso, sarà dalle celle B2 a B11 e scegliamo l’ordine crescente nell’argomento ordine. Di seguito puoi vedere l’implementazione della funzione RANK.EQ. La formula è simile a questa:

=RANK.EQ(B2,$B$2:$B$11,0)

Premendo Invio verrà eseguita la formula e otterrai il grado di James Walker.

In questo esempio, James Walker è al 5 ° posto nella classe in base alla sua percentuale, il che significa che la sua percentuale è 5 ° più alta rispetto al resto dei numeri in una matrice.

Uso della funzione RANK in combinazione con la funzione COUNTIF

È possibile utilizzare la funzione RANK.EQ combinata con la funzione CONTA.SE per interrompere il salto dei numeri, ma ignorerà anche la duplicazione del rango. Sebbene nessun numero si ripeterà due volte, due studenti con lo stesso numero possono ottenere gradi diversi.

Per capirlo meglio, vediamo come RANK.EQ funziona insieme a COUNTIF. La formula è simile a questa:

=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

L’implementazione di questa formula risolverà il problema del salto dei numeri.

Non ci sono duplicazioni nei ranghi sopra. Ma James Walker e Gillian Tillman, che avrebbero dovuto ottenere lo stesso grado, ora sono classificati in modo diverso.

Pertanto, l’utilizzo di RANK.EQ con COUNTIF ha risolto metà del problema, ma non è riuscito a generare il risultato desiderato.

Correlati: Nuove funzionalità in Excel per il Web per aumentare la produttività

Utilizzo della funzione SUMPRODUCT con la funzione COUNTIF

Per classificare gli studenti in un elenco assegnando gli stessi gradi per percentuali equivalenti senza saltare alcun numero, è possibile utilizzare la funzione SUMPRODUCT con CONTA.SE.

Dai un’occhiata alla formula qui sotto:

La formula può sembrare complicata, ma è il modo migliore per classificare correttamente gli oggetti. In questo modo, puoi ottenere i risultati desiderati con la duplicazione del rango e nessun salto di numero.

Mentre crei il risultato per i tuoi studenti, puoi utilizzare direttamente la formula SUMPRODUCT in sostituzione della funzione RANK. Per i calcoli di classificazione non duplicati, è possibile utilizzare la funzione RANK.EQ da sola o con la funzione CONTA.SE.

Modifica dell’ordine del risultato finale

Nella scheda Dati , fare clic sul gruppo Ordina e filtra e scegliere un ordine crescente per ordinare le classifiche.

Confronta i risultati in tre righe affiancate per capire meglio come funziona ciascun metodo di classificazione degli elementi.

Correlato: come la tabella dati in Microsoft Excel consente di confrontare i risultati

Classificare gli elementi facilmente utilizzando le funzioni RANK.EQ e SUMPRODUCT

Ecco come puoi utilizzare le funzioni RANK e SUMPRODUCT in combinazione con la funzione COUNTIF per classificare gli elementi, con o senza duplicazioni. Puoi anche decidere se saltare qualsiasi numero nella sequenza o meno.

A seconda della situazione, è possibile utilizzare una varietà di metodi per classificare i numeri in base a una serie di determinati criteri. Questo è un esempio di come le funzioni di Excel possono aiutarti a risparmiare molto tempo e fatica nei calcoli manuali. C’è molto altro da esplorare per progettare fogli di calcolo Excel dall’aspetto professionale.