Mike est enseignant et doit souvent donner des tests à ses élèves. Dans un cas, il a obtenu 20 résultats de test allant de 76 à 12. Mike veut laisser le meilleur score à 76 mais augmenter le score du bas de 5 (à 17)

puis ajustez proportionnellement tous les scores intermédiaires. Il se demande si Excel a une fonction qui accomplira l’ajustement pour lui.

Il n’y a aucune fonction qui fera cela, mais il existe un certain nombre de formules qui peuvent être utilisées pour obtenir les scores de test ajustés. En fait, il existe un nombre étonnamment grand de formules que vous pouvez utiliser. La raison pour laquelle il y en a autant est que le mot «proportionnellement» est ambigu – à quoi ressemble exactement un ajustement proportionnel?

Par exemple, cela pourrait signifier une partie de la différence entre le score le plus bas et le score le plus élevé. Ou, cela pourrait signifier que les scores les plus faibles sont «pondérés» d’une manière ou d’une autre, de sorte qu’ils obtiennent un coup de pouce plus important que les scores plus élevés. Ou, cela pourrait signifier que vous calculez simplement le pourcentage du score par rapport au score maximum, puis que vous augmentez le score d’autant.

Dans cet esprit, examinons quelques façons d’ajuster les scores.

Vous voudrez regarder les résultats de chaque formule et déterminer s’ils correspondent à vos attentes quant à ce qui devrait arriver aux scores. Ces trois formules produisent exactement les mêmes ajustements des scores, les scores les plus faibles étant pondérés plus favorablement que les scores les plus élevés:

=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

Ces formules ajustent les scores les plus élevés et les plus bas selon les besoins de Mike, et tous les autres scores proportionnellement. Il en résulte, par exemple, le score moyen (à mi-chemin entre 12 et 76, soit un score de 42)

étant ajusté de 2,5 points.

La formule simple suivante ne fait attention qu’à la valeur maximale dans la plage des scores originaux. Il calcule la distance du score maximum, puis ajuste le score à la hausse. Il en résulte un score ajusté le plus bas de 17,33, mais qui peut être facilement arrondi pour obtenir le montant souhaité.

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

La formule suivante examine le classement des 19 scores originaux, sur une échelle de 0 à 19. Elle détermine ensuite quel pourcentage de 5 ce classement représente et ajoute ensuite la valeur au score d’origine:

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

Vous pouvez également utiliser la régression linéaire pour résoudre le problème. Excel a deux fonctions intégrées (SLOPE et INTERCEPT) qui peuvent être utilisées pour trouver la formule pour déterminer les scores ajustés. Dans le cas de Mike, la plage des scores avant ajustement va de 12 à 76. Ceux-ci doivent être ajustés à une plage de 17 à 76. Si vous mettez 12 dans la cellule E1, 76 dans la cellule E2, 17 dans la cellule F1 et 76 dans cellule F2, alors vous vous retrouvez avec les bas et les hauts dans les plages de début et de fin. Vous pouvez ensuite utiliser ces formules pour trouver la pente et l’interception:

=SLOPE(F1:F2,E1:E2)

=INTERCEPT(F1:F2,E1:E2)

Cela calcule la pente à 0,921875 et l’intersection à 5,9375. Vous pouvez ensuite appliquer ces valeurs aux résultats du test d’origine de cette manière:

=0.921875 * A1 + 5.9375

Fait intéressant, cette approche de régression linéaire renvoie exactement les mêmes valeurs dérivées des trois premières formules présentées au début de cette astuce.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (1022) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Adjusting_Test_Scores_Proportionately [Ajustement des scores de test proportionnellement].