Excel Kreuztabelle automatisch ausfüllen lassen?
Hallo zusammen,
ich habe eine klassische Turniertabelle, da je Excel leider nur Eingabe oder Ausgabefeld sein kann und niemals(zumindest nicht variierend) beides lässt sich nur eine Seite berechnen, während die andere eingetragen werden muss, somit habe für mein "Problem" beschlossen den gelben Bereich händisch einzutragen...
....und den anderen Bereich berechnen zu lassen. Nun gibt es bei dieser Tabelle nur 3 Möglichkeiten entweder Sieg (1), Niederlerage (0) oder Unentschieden (0,5), klar könnte ich das über eine simple WENN Abfrage erledigen, allerdings muss ich dabei fest auf eine Zelle festlegen, ich würde aber gerne eine Formel entwickeln mit der dynamisch unabhängig von der Spieleranzahl, also auch bei 6 oder 8 oder 10 Spielern das ganze eintragen kann ohne die Formel für jede Zelle erneut anzupassen.
Ich dachte somit an eine Forme die die Anhand eine Bezugszelle (in meinem Falle die erste oben links) die Position im Bereich kennt und nun ist es ja bei einer Kreuztabelle so dass die beiden von einander abhängigen Ergebnisse immer an der gleichen Stelle liegen nur Spalte und Zeile sind vertauscht.
Sprich wenn Spieler 2 gegen Spieler 3 spielt und gewinnt und dann trage ich in der 2 Zeile und in der 3 Spalte eine 1 ein und an der 3 Zeile und der 2 Spalte eine 0 - versteht ihr was ich meine?
Nur bin ich leider daran gescheitert, ich habe schon ein wenig versucht mit Indirekt und Index oder Vergleich herumzufummeln, ggf. Bereich verschieben, aber bin bisher kläglich gescheitert, da ich ja kein Ergebnis haben sondern eine aktuelle Position und die daraus resultierende Gegenposition.
Wenn ich das anschaulich nochmal darstellen soll, dann kann ich das gerne nochmal nachreichen, aber ich habe gehofft, dass man mir mit den Infos schon ein wenig weiterhelfen kann.
Kann mir da einer einen Tipp geben, wo ich ansetzen könnte, ich brauche keine vorgekaute Lösung, wenn jemand einen guten Ansatz hat, den er mir schmackhaft machen kann wäre mir schon sehr geholfen.
Vielen Dank schonmal an alle helfenden
2 Antworten
Ich hatte eine ähnliche Idee wie DeeDee07, verwende aber die Funktion Bereich.Verschieben. Dadurch wird es egal, wie viele Spieler mitspielen. Natürlich muss die Matrix quadratisch sein. Leere Zellen werden als Null interpretiert.
Auch die Position der linken oberen Ecke ist dynamisch. Ich habe sie willkürlich auf A13 gelegt. Man kann aber auch Zeilen/Spalten einfügen oder löschen.
Der Grundgedanke ist ähnlich: 1 - der gespiegelten Zelle. Wenn die Zielzelle beispielsweise 1 Spalte rechts und drei Zeilen unterhalb der oberen linken Ecke liegt, muss sie auf die Zelle zugreifen, die 1 Zeile unterhalb und drei Spalten rechts von der oberen linken Ecke liegt.
DeeDee07's Vorschlag, mit Wenn abzufragen, ob in der Quellzelle schon etwas eingetragen ist, geht hier natürlich auch.
Spoiler Alarm:
=1-BEREICH.VERSCHIEBEN($A$13;SPALTE()-SPALTE($A$13);ZEILE()-ZEILE($A$13))
Gruß
Hannes
Du kannst tatsächlich per Suchen/Ersetzen alle $A$13 durch A13 ersetzen.
Dann kopierst Du die gesamte Matrix an die Stelle, an der Du sie haben willst. Den Eingabebereich kannst Du per Mehrfachauswahl bei gedrückter STRG Taste (Mac: CMD Taste) selektieren und dann löschen. Diese halbleere Matrix würde ich als Template irgendwo (z.B. andere Tabelle) abspeichern. Von dort kannst Du sie Dir dann immer per Copy/Paste der gesamten Matrix neu aufbauen. Die einzelnen Formeln brauchst Du dann nicht anzufassen.
Viele Jahre später habe ich mich der Problematik nochmal angenommen und das noch etwas zu optimieren mit einem größeren Erfahrungschatz und kam auf die folgende Formel.
=LET(Matrix;MTRANS(""&BEREICH.VERSCHIEBEN(C5:C7;0;0;VERGLEICH(WAHR;C5:C100="";0)-1-(ZEILE(D3)-ZEILE(D$3));));WENN(Matrix="";"";$C$2-Matrix) )
Das LET dient in dem Falle hier nur der Übersichtlichkeit es geht auch komplett ohne, zieht die Formel nur unnötig in die Länge. Aber für den Fall einer älteren Excel Version ohne LET Funktion geht es dann ebenso.
Über die Vergleichsfunktion zähle ich die Anzahl der Zellen und ziehe davon eine ab, da man nicht gegen sich selbst spielen kann und hab daher die richtige Größe für den Bereich, egal wie groß dieser ist.
Mit Index ist es schon möglich. Vergleich braucht man nicht, man kann die Mathematik nutzen. Hier ein Beispiel
Den grünen Bereich habe ich händisch eingegeben wie du, im rosafarbenen sind Formeln hinterlegt, nämlich
=1-INDEX($A$1:$F$6;SPALTE();ZEILE())
Man zieht also von 1 den eingegebenen Gegenwert ab und verwendet SPALTE() für den Zeilenindex sowie ZEILE() für den Spaltenindex.
Die Tabelle ist noch nicht dynamisch bezüglich Anzahl der Spieler. Das kann man lösen, indem man die Anzahl abfrägt und mit der aktuellen Spaltennummer vergleicht. Die Tabelle wird dann nur soweit ausgefüllt, wie es Spieler in Spalte A gibt. Das wäre dann etwa so:
=WENN(ANZAHL2($A:$A)>=SPALTE();1-INDEX($A:$Z;SPALTE();ZEILE());"")
Ok, war jetzt dann doch vorgekaut :)
Im übrigen, mit Makros kann man die gleiche Zelle sowohl für Eingabe als auch Ausgabe von Werten verwenden (bezogen auf deinen ersten Satz).
schon mal danke dafür, die Formel wenn ich sie mir im nachhinein so betrachte ist nicht annähernd so kompliziert wie ich gedacht habe.
Ja da mit den Makros das weiß ich, aber nicht immer ist ein Makro zielführend und bei normalem Excel denke ich halt immer noch an Formeln ;-)
Nachtrag:
Möglicherweise ist es sinnvoller, statt die Anzahl der Spieler abzufragen, zu überprüfen, ob im hellgrünen Bereich etwas eingetragen ist, und nur dann den Wert zu berechnen, sonst leer lassen, etwa
=WENN(INDEX...<>"";1-INDEX...;"")
Also ich habe das für meinen Fall nochmal etwas probiert, aber stoße da für mich auf kleine Probleme, da auf jedem Arbeitsblatt mehrere solcher Kreuztabellen untereinander kommen, ist die Auswahl über A:A wenig Hilfreich, da sonst die unteren mitgezählt werden. Außerdem habe ich über jeder Tabelle Führungszellen, welche die Tabelle kurz beschreiben, auf deine Formel angewandt musste ich die dementsprechend abändern.
=1-INDEX($A$1:$F$6;SPALTE()+2;ZEILE()-2)
das Problem ist leider nur das Zeile und Spalte ja die Zeile bezogen auf das gesamte Arbeitsblatt ermittelt, ich hätte es aber gerne, dass es sich nur auf den aktuellen Bereich bezieht, da es somit die Kopierbarkeit gewährleistet für die darunterliegenden Tabellen. die kann ich im Falle deiner Formel leider nicht erkennen oder mache ich da einen Denkfehler? Schaue mir nunmal die Lösung von @Hannes62a an
Ich wusste nicht, dass es mehrere Tabellen untereinander gibt. Du musst dann den Index-Bereich beschränken. Für ZEILE und SPALTE kannst du auch Bezüge als Argumente verwenden, also etwa ZEILE(A1).
Wenn du die Formel dann einmal für jede Tabelle anpasst, kannst du sie (mit Bezugsargumenten) in die restlichen Zellen kopieren.
Vielen Dank dafür, diese Formelvariante gefällt mir wesentlich besser als die oben genannte, aus genau dem oben kritisierten Punkt, das einzige was ich hier immer anpassen muss ist der Bezugspunkt.
Leider lässt es Excel nicht zu nicht quaderförmige Bereiche zu kopieren, sodass ich dann doch jede Formel einzeln wieder kopieren muss.
Ansonsten könnte ich den Bezugspunkt nämlich ohne die "$" schreiben und kopiere mir dann den kompletten Bereich, indem die Formel Anwendung findet in jeden anderen Bereich, der die gleiche Größe aufweist, egal wo sie auf dem Arbeitsblatt wäre und es würde ohne Anpassung des Bezugspunktes funktionieren.
Ich werde hierzu mal ein Mechanismus überlegen auch das zu dynamisieren, vielleicht komme ich auf eine Idee.
Auf jeden Fall danke für deine Anregung.