Mike ist Lehrer und muss seinen Schülern oft Tests geben. In einem Fall hatte er 20 Testergebnisse, die von 76 bis 12 reichen. Mike möchte das obere Ergebnis bei 76 belassen, aber das untere Ergebnis um 5 (auf 17) erhöhen

und passen Sie dann alle Zwischenwerte proportional an. Er fragt sich, ob Excel eine Funktion hat, die die Anpassung für ihn übernimmt.

Es gibt keine Funktion, die dies ausführt, aber es gibt eine beliebige Anzahl von Formeln, mit denen die angepassten Testergebnisse abgerufen werden können. Tatsächlich gibt es eine überraschend große Anzahl von Formeln, die Sie verwenden können. Der Grund, warum es so viele gibt, ist, dass das Wort „proportional“ nicht eindeutig ist – wie genau sieht eine proportionale Anpassung aus?

Zum Beispiel könnte dies einen Teil der Differenz zwischen der niedrigsten und der höchsten Punktzahl bedeuten. Oder es könnte bedeuten, dass die niedrigeren Punktzahlen auf irgendeine Weise „gewichtet“ werden, so dass sie einen größeren Schub erhalten als die höheren Punktzahlen. Oder es könnte bedeuten, dass Sie einfach berechnen, wie viel Prozent der Punktzahl von der maximalen Punktzahl abweicht, und dann die Punktzahl um diesen Betrag erhöhen.

Schauen wir uns vor diesem Hintergrund einige Möglichkeiten an, wie Sie die Punktzahlen anpassen können.

Sie sollten sich die Ergebnisse jeder Formel ansehen und feststellen, ob sie Ihren Erwartungen entsprechen, was mit den Ergebnissen geschehen soll. Diese drei Formeln führen zu genau den gleichen Anpassungen der Bewertungen, wobei die niedrigeren Bewertungen günstiger gewichtet werden als die höheren Bewertungen:

=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

Diese Formeln passen die höchsten und niedrigsten Werte gemäß den Anforderungen von Mike und alle anderen Werte proportional an. Es ergibt sich beispielsweise die mittlere Punktzahl (auf halbem Weg zwischen 12 und 76, was einer Punktzahl von 42 entspricht)

um 2,5 Punkte angepasst werden.

Die folgende einfache Formel berücksichtigt nur den Maximalwert im Bereich der Originalwerte. Es berechnet, wie weit vom Maximum der Punktzahl entfernt ist, und passt die Punktzahl dann nach oben an. Dies führt zu einer untersten angepassten Punktzahl von 17,33, die jedoch leicht gerundet werden kann, um die gewünschte Menge zu erreichen.

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

Die folgende Formel untersucht die Rangfolge der ursprünglichen 19 Punkte auf einer Skala von 0 bis 19. Sie ermittelt dann, wie viel Prozent von 5 diese Rangfolge darstellt, und addiert dann den Wert zur ursprünglichen Punktzahl:

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

Sie können auch eine lineare Regression verwenden, um das Problem zu lösen. Excel verfügt über zwei integrierte Funktionen (SLOPE und INTERCEPT), mit denen die Formel zur Ermittlung der angepassten Ergebnisse ermittelt werden kann. In Mikes Fall beträgt der Bereich der Bewertungen vor der Anpassung 12 bis 76. Diese müssen auf einen Bereich von 17 bis 76 eingestellt werden. Wenn Sie 12 in Zelle E1, 76 in Zelle E2, 17 in Zelle F1 und 76 in Zelle 1 eingeben Zelle F2, dann erhalten Sie die Tiefs und Hochs sowohl im Start- als auch im Endbereich. Sie können dann diese Formeln verwenden, um die Steigung und den Achsenabschnitt zu ermitteln:

=SLOPE(F1:F2,E1:E2)

=INTERCEPT(F1:F2,E1:E2)

Dies berechnet die Steigung bei 0,921875 und den Achsenabschnitt bei 5,9375. Sie können diese Werte dann folgendermaßen auf die ursprünglichen Testergebnisse anwenden:

=0.921875 * A1 + 5.9375

Interessanterweise liefert dieser lineare Regressionsansatz genau dieselben Werte, die von den ersten drei am Anfang dieses Tipps dargestellten Formeln abgeleitet wurden.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (1022) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Testergebnisse proportional anpassen.