Michele è un insegnante e spesso deve assegnare dei test ai suoi studenti. In un caso aveva 20 punteggi di test che andavano da 76 a 12. Michele vuole lasciare il punteggio più alto a 76 ma aumentare il punteggio più basso di 5 (a 17) e quindi aggiustare proporzionalmente tutti i punteggi intermedi. Si chiede se Excel disponga di una funzione che possa eseguire la modifica per lui.
Non esiste alcuna funzione che possa farlo, ma esistono numerose formule che possono essere utilizzate per ottenere i punteggi corretti dei test. In effetti, ci sono un numero sorprendentemente elevato di formule che puoi utilizzare. Il motivo per cui ce ne sono così tante è perché la parola “proporzionalmente” è ambigua: che aspetto ha esattamente un aggiustamento proporzionale?
Ad esempio, potrebbe significare una parte della differenza tra il punteggio più basso e quello più alto. Oppure potrebbe significare che i punteggi più bassi sono “ponderati” in qualche modo per ottenere un incremento maggiore rispetto ai punteggi più alti. Oppure potrebbe significare semplicemente calcolare la percentuale del punteggio rispetto al punteggio massimo e quindi aumentare il punteggio di quella cifra.
Tenendo questo a mente, diamo un’occhiata ad alcuni modi in cui puoi modificare i punteggi. Ti consigliamo di esaminare i risultati di ciascuna formula e determinare se corrispondono alle tue aspettative su ciò che dovrebbe accadere ai punteggi. Queste tre formule producono esattamente gli stessi aggiustamenti ai punteggi, con i punteggi più bassi ponderati in modo più favorevole rispetto ai punteggi più alti (si presume che i risultati dei test siano in colonna e partano dalla cella A2) :
=A2+(1-(A2-MIN(A:A))/(MAX(A:A)-MIN(A:A)))*5
=A2+(5*(MAX(A:A)-A2))/(MAX(A:A)-MIN(A:A))
=A2+(MAX(A:A)-A2)/(MAX(A:A)-MIN(A:A))*5
Queste formule aggiustano i punteggi più alti e i più bassi come richiesto da Michele e tutti gli altri punteggi in modo proporzionale. Ne consegue, ad esempio, che il punteggio medio (a metà strada tra 12 e 76, ovvero un punteggio di 42) viene corretto di 2,5 punti.
La seguente semplice formula presta attenzione solo al valore massimo nell’intervallo dei punteggi originali. Calcola quanto lontano dal massimo è il punteggio, quindi aggiusta il punteggio verso l’alto. Il risultato è un punteggio minimo corretto di 17,33, ma può essere facilmente arrotondato per ottenere il numero desiderato.
=((MAX(A:A)-A2)/A2)+A2
La seguente formula esamina la classifica dei 20 punteggi originali, su una scala da 0 a 19. Quindi calcola quale percentuale di 5 rappresenta quella classifica e quindi aggiunge il valore al punteggio originale:
=(RANGO(A2;A:A)-1)*5/19+A2
Puoi anche utilizzare la regressione lineare per risolvere il problema. Excel dispone di due funzioni integrate (PENDENZA e INTERCETTA) che possono essere utilizzate per trovare la formula per determinare i punteggi corretti. Nel caso di Michele, l’intervallo dei punteggi prima della correzione è compreso tra 12 e 76. Questi devono essere adattati a un intervallo compreso tra 17 e 76. Se inserisci 12 nella cella E2, 76 nella cella E3, 17 nella cella F2 e 76 nella cella F3, ti ritroverai con i minimi e i massimi sia dell’intervallo iniziale che di quello finale. È quindi possibile utilizzare queste formule per trovare la pendenza e il punto di intersezione:
=PENDENZA(F2:F3;E2:E3)
=INTERCETTA(F2:F3;E2:E3)
Questo calcola la pendenza a 0,921875 e il punto di intersezione a 5,9375. È quindi possibile applicare questi valori ai punteggi del test originale in questo modo:
=0.921875 * A2 + 5.9375
È interessante notare che questo approccio di regressione lineare restituisce esattamente gli stessi valori ottenuti dalle prime tre formule presentate all’inizio di questo suggerimento.
