Benötigen Sie eine Excel-Formel, die den aktuellsten von 5 Werten in einer Spalte vom ältesten subtrahiert

Question

Ich bin Kraft- und Konditionstrainer und brauche Hilfe bei der Aufschlüsselung der Bewertungsergebnisse meiner Athleten. Ich brauche Hilfe beim Schreiben einer Formel, die das älteste von 5 Testergebnissen vom aktuellsten abzieht. Das Blatt hat fast 3000 Zeilen, wobei jeder Athlet jeweils 5 Zeilen hat (Semester 1, 2, 3, 4 und Sommer), unabhängig von seiner tatsächlichen Teilnahme an den besagten Tests. Die Formel muss also im Wesentlichen die Spalte nach unten durchgehen und den ersten verfügbaren Wert aus den 5 Zellen nehmen und ihn vom letzten verfügbaren Wert aus den 5 Zellen abziehen. Das Blatt ist wie folgt aufgebaut:

Bitte helfen Sie! Es tut mir leid, wenn dies nicht genügend Informationen enthält, ich bin neu auf der Site.

Answer 1

Ich habe eine Formel, die funktioniert, wenn Sie sie in eine der grauen Zeilen mit dem Titel ÄNDERN eingeben.

Es ist ein Knaller, es sieht so aus:

=INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), ROWS(Values)) - ROW(StartValue) + 1,1)- INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), COUNTIF(Values, "")+1) - ROW(StartValue)+1,1)

Wo

• Valuesist der Wertebereich, in meinem Beispiel ist dasH5:H9
• StartValueist die erste Zelle im Wertebereich, für mich ist dasH5

Wie es funktioniert

• Die Anweisung INDEX((Values<>0)*ROW(Values), 0)gibt ein Array zurück, das sich in 0der Zelle befindet, wenn sie leer ist, und die Zeilennummer der Zelle, wenn sie nicht leer ist
• Die SMALL()Funktion sortiert diese in aufsteigender Reihenfolge und gibt dann den Wert im Array zurück, der ihrem zweiten Argument entspricht

In der Beispieltabelle unten INDEX((Values<>0)*ROW(Values), 0)gibt

0
6
7
8
0

SMALL()sortiert es so, dass es wie folgt aussieht:

0
0
6  <- Row of first nonzero value, second argument to SMALL(): =COUNTIF(Values, "")+1 = 3
7
8  <- Row of last nonzero value, second argument to SMALL(): ROWS(Values) = 5

• Da wir nun die Zeile kennen, verwenden wir, INDEX()um den Wert abzurufen. Die Zeile - ROW(StartValue) + 1übersetzt die von zurückgegebene Zeile SMALL()in einen Index
• Wir haben also den aktuellsten Wert ungleich Null vom ersten Aufruf von INDEX()und ziehen davon den am weitesten entfernten Wert ungleich Null vom zweiten Aufruf von ab.INDEX()

Bildschirmfoto

Link zur Tabelle

Hierist ein Link zum Herunterladen dieser Beispieltabelle. Normalerweise verwende ich FormulaChop, um diese Formeln zu erklären, aber es funktioniert nicht mit der Funktion ROW(). (Vollständige Offenlegung: Ich habe FormulaChop geschrieben). Daher habe ich eine Version der Formel eingefügt, die funktioniert, und Sie können sie auf der Registerkarte FormulaChop aufgeschlüsselt sehen.

Answer 2

Mit den Daten in Spalte A (A2:A3001), den Namen in Spalte B und den Werten in Spalte C geben Sie den nachzuschlagenden Namen in F2 ein und verwenden diese Formel in G2.

=SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(14, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))-
 SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(15, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))

Answer 3

Ich hoffe, das ist, wonach Sie suchen?

[ ]

Im Bild haben wir zwei Benutzer. Gelbe Daten sind das, was Sie in Ihrem Blatt haben (glaube ich, mit vielen Benutzern), und andere Spalten sind da, um diese mit einfachen Formeln auszuwerten.

Answer 4

Das könnte das sein, wonach Sie suchen Bild, das zeigt, wie die Ergebnisse aussehen

So sieht die Formel aus, sie ist ganz einfach anzuwenden Bild mit Formeln

Wie erstelle ich eine Formel? Drücken Sie Alt+F11 und kopieren Sie Folgendes

Öffentliche Funktion Diff2(n1 als Double, n2 als Double, n3 als Double, n4 als Double, n5 als Double)

index1 = 0 index2 = 0

'Erste Zahl suchen Wenn n1 > 0 Dann FirstNum = n1 Index1 = 1 ElseIf n2 > 0 Dann FirstNum = n2 Index1 = 1 ElseIf n3 > 0 Dann FirstNum = n3 Index1 = 3 ElseIf n4 > 0 Dann FirstNum = n4 Index1 = 4 Ende If

'Suche die zweite Zahl Wenn n5 > 0 Dann SecNum = n5 index2 = 5 ElseIf n4 > 0 Dann SecNum = n4 index2 = 4 ElseIf n3 > 0 Dann SecNum = n3 index2 = 3 ElseIf n2 > 0 Dann SecNum = n2 index2 = 2 Ende Wenn

'Fehlerprüfung Wenn (Index1 = 0) Oder (Index2 = 0) Oder (Index1 - Index2 = 0) Dann Diff2 = "FEHLER" Sonst Diff2 = SecNum - FirstNum Ende Wenn

Endfunktion

Bild, das Excel VBA zeigt