Mike è un insegnante e spesso deve dare dei test ai suoi studenti. In un caso aveva 20 punteggi del test che vanno da 76 a 12. Mike vuole lasciare il punteggio più alto a 76 ma aumentare il punteggio più basso di 5 (a 17)

e quindi regolare proporzionalmente tutti i punteggi intermedi. Si chiede se Excel abbia una funzione che eseguirà la regolazione per lui.

Non esiste una funzione che lo faccia, ma ci sono un numero qualsiasi di formule che possono essere utilizzate per ottenere i punteggi dei test modificati. In effetti, ci sono un numero sorprendentemente elevato di formule che puoi usare. Il motivo per cui ce ne sono così tanti è 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 il punteggio più alto. Oppure, potrebbe significare che i punteggi più bassi sono “ponderati” in qualche modo in modo da ottenere un incremento maggiore rispetto ai punteggi più alti. Oppure, potrebbe significare che si calcola semplicemente la percentuale del punteggio dal punteggio massimo e quindi si aumenta il punteggio di tanto.

Con questo in mente, diamo un’occhiata ad alcuni modi in cui puoi regolare 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 pesati in modo più favorevole rispetto ai punteggi più alti:

=A1+(1-(A1-MIN(A:A))/(MAX(A:A)-MIN(A:A)))5 =A1+(5(MAX(A:A)-A1))/(MAX(A:A)-MIN(A:A))

=A1+(MAX(A:A)-A1)/(MAX(A:A)-MIN(A:A))*5

Queste formule regolano i punteggi più alti e più bassi come richiesto da Mike e tutti gli altri punteggi in modo proporzionale. Risulta, ad esempio, nel punteggio medio (a metà tra 12 e 76, che è un punteggio di 42)

essere aggiustato di 2,5 punti.

La semplice formula seguente presta attenzione solo al valore massimo nell’intervallo dei punteggi originali. Calcola la distanza dal massimo del punteggio, quindi regola il punteggio verso l’alto. Il risultato è un punteggio inferiore aggiustato di 17,33, ma che può essere facilmente arrotondato per ottenere l’importo desiderato.

=((MAX(A:A)-A1)/A1)+A1

La seguente formula esamina la classifica dei 19 punteggi originali, su una scala da 0 a 19. Quindi calcola la percentuale di 5 rappresentata da quella classifica e quindi aggiunge il valore al punteggio originale:

=(RANK(A1,A:A)-1)*5/19+A1

È inoltre possibile utilizzare la regressione lineare per risolvere il problema. Excel ha due funzioni incorporate (PENDENZA e INTERCETTA) che possono essere utilizzate per trovare la formula per determinare i punteggi aggiustati. Nel caso di Mike, l’intervallo di punteggi prima dell’aggiustamento è compreso tra 12 e 76. Questi devono essere regolati su un intervallo compreso tra 17 e 76. Se inserisci 12 nella cella E1, 76 nella cella E2, 17 nella cella F1 e 76 nella cella cella F2, quindi si finisce con i minimi e gli alti sia nell’intervallo iniziale che in quello finale. È quindi possibile utilizzare queste formule per trovare la pendenza e intercettare:

=SLOPE(F1:F2,E1:E2)

=INTERCEPT(F1:F2,E1:E2)

Questo calcola la pendenza a 0.921875 e l’intercetta a 5.9375. È quindi possibile applicare questi valori ai punteggi del test originale in questo modo:

=0.921875 * A1 + 5.9375

È interessante notare che questo approccio di regressione lineare restituisce esattamente gli stessi valori derivati ​​dalle prime tre formule presentate all’inizio di questo suggerimento.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (1024) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 ed Excel in Office 365. Puoi trovare una versione di questo suggerimento per l’interfaccia del menu precedente di Excel qui:

Regolazione proporzionale dei punteggi dei test.