Holen Sie sich einen Wert aus dem aktuellsten vorherigen Datensatz, in dem in einer der beiden Spalten Text vorkommt.

Question

Ich versuche, die Berechnung von Werten und Zusammenfassungsdaten in einer Tabelle, die ich über die Ergebnisse von Spielen in einer Billardliga führe, stärker zu automatisieren.

Ich habe eine Tabelle mit zahlreichen Informationen zu jedem Spiel. Die relevanten Felder sind: Spieldatum, Gewinner, Starthandicap des Gewinners, Endhandicap des Gewinners, Verlierer, Starthandicap des Verlierers, Endhandicap des Verlierers, Startzeit des Spiels.

Handicaps werden am Ende jedes Spiels und vor dem nächsten angepasst. Es ist mühsam, den aktuellsten Rekord eines Spielers (könnte ein Gewinner oder Verlierer gewesen sein) zu finden und sein Endhandicap aus diesem Rekord in das Starthandicap (Gewinner oder Verlierer) für das Spiel zu kopieren, an dem ich gerade teilnehme.

Ich hätte gerne eine Formel, die den aktuellsten Rekord (höchstes Datum und höchste Startzeit, falls er zweimal an einem Tag gespielt hat) findet, bei dem er gewonnen oder verloren hat, und die dann das End-Handicap (vom jeweiligen Gewinner oder Verlierer) berechnet.

Gemäß Teylyns Vorschlag ist hier ein Dropbox-Link zur Datei. Die entsprechende Registerkarte ist „Match Results“:https://www.dropbox.com/s/1j9c6zsxjd3q4dt/Sample%20for%20Excel%20Question%20on%20Superuser.xlsx?dl=0

Ich habe eine leere Spalte L hinzugefügt, um Dinge zu testen und die Ergebnisse mit denen in K zu vergleichen, um zu sehen, ob sie funktionieren. Deshalb ist sie da. Habe vergessen, sie zu entfernen, als ich sie in Dropbox abgelegt habe.

Answer 1

Problemstellung

Ein Arbeitsblatt hat Namen in Spalten Eund X. Für jede Zeilen, ≠ . Es gibt Zahlen in Spalte , die den Namen in Spalte entsprechen , und Zahlen in Spalte , die den Namen in Spalte entsprechen . Für jede Zeile nach der ersten (sagen wir Zeile 42) möchten wir, wenn möglich, Werte für und aus vorherigen Zeilen erhalten .EnXnMEAGXK42AF42

• Wenn E42„John“ ist, suchen Sie die letzte Zeile, die „John“ enthält (in Spalte Eoder X). Rufen Sie diese Zeile aufn. Wenn = „John“, setzen Sie es gleich . Wenn = „John“, setzen Sie es gleich .EnK42MnXnK42AGn

• Wenn X42„Scott“ ist, suchen Sie die letzte Zeile, die „Scott“ enthält (in Spalte Eoder X). Rufen Sie diese Zeile aufn. Wenn = „Scott“, setzen Sie es gleich . Wenn = „Scott“, setzen Sie es gleich .EnAF42MnXnAF42AGn

                 

Lösung

Um ein wenig Vernunft zu bewahren, verwenden wir Hilfsspalten; sagen wir ARund AS. Nehmen wir an, dass (wie in der Beispieldatei) die Daten in Zeile 2 beginnen. Geben Sie

=MAX(($E$2:$E2=$E3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$E3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

in AR3(überspringen AR2). Beenden Sie mit Ctrl+ Shift+ Enter, um eine Array-Formel zu erstellen. Ebenso setzen Sie AS3auf

=MAX(($E$2:$E2=$X3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$X3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

als Array-Formel. (Dies ist dasselbe wie, AR3 außer dass die beiden Vorkommen von $E3durch ersetzt wurden $X3.)

EinstellenK3​

=IF($AR3=0, "?", INDEX($A$1:$BG$999, INT($AR3/100), MOD($AR3,100)))

und AF3zu

=IF($AS3=0, "?", INDEX($A$1:$BG$999, INT($AS3/100), MOD($AS3,100)))

(nicht als Array-Formeln). Diese sind gleich, außer dass die drei Vorkommen von $AR3durch ersetzt wurden $AS3.

Und natürlich nach unten ziehen/füllen.

Die Hilfsspalten finden die aktuellsten vorherigen Vorkommen der Namen — findet das aktuellste vorherige Vorkommen von und findet das aktuellste vorherige Vorkommen von — im Grunde indem sie das Maximum in den vorherigen Zeilen finden vonARnEnASnXn

(vorheriger Wert=dieser Wert) * REIHE())

d. h. die höchste Zeilennummer, in der der Name übereinstimmt. Anschließend wird die Position, an der der Name gefunden wurde, kodiert als

100*ZEILE() + SPALTE(Daten, die wir kopieren möchten)

Beide Formeln suchen sowohl in den Spalten Eals auch Xund geben die codierten Koordinaten der entsprechenden Spalten Moder AGZellen zurück. Anschließend decodieren die Formeln Kund AFeinfach die Zellenadresse und rufen den Wert ab.

               

Das AR6gilt auch für 213, da „John“ ( E6) zuletzt in Zeile 2 gesehen wurde und wir, da er in E2(und nicht in X2) gesehen wurde, den Wert aus Spalte 13 (Spalte ) kopieren möchten M.