INDEX und VERGLEICH in Aktion – an einem Beispiel
Nehmen wir folgende Ausgangsposition, für die ich die WTB-Werte und entsprechenden Z-Werte des 9.-11. Spieltags verwendet habe.
| 2018-11-24-libre-calc-1-index-vergleich-01-ausgangsversion.ods |
Ich habe drei Beispielpaarungen der Spieltage 9 – 11 ausgewählt, in denen du für jedes Team den passenden Z-Wert mit der INDEX-Formel abholen möchtest. Beginnen wir mit dem SC Freiburg. Da es sich bei SC Freiburg gegen Borussia Mönchengladbach um eine Paarung des 9. Spieltags handelt, benötigst du den Z-Wert des SC Freiburgs nach dem achten Spieltag, in diesem Fall also den Wert 0.155 aus Zelle D16.
Um diesen Wert abzuholen, kannst du die INDEX-Funktion verwenden. Diese besteht aus drei Komponenten, und funktioniert nach dem Muster
- =INDEX(Bereich,Zeile,Spalte):
Definition des Bereichs
Hier definierst du einen zusammenhängenden Zellbereich, in dem sich alle Werte befinden, unter denen deine Formel den richtigen finden soll. Für die Zwecke dieser Tabelle wäre dies also der Bereich C4 bis H21, was du als C4:H21 eingibst. Im Bild habe ich den Bereich blau markiert:
Eingabe der Zeile
Als Zeile benötigst du einen numerischen Wert. Da der Z-Wert des SC Freiburgs in der 13. Zeile des definierten Bereichs C4 bis H21 zu finden ist, gibst du hier 13 ein.
Eingabe der Spalte
Da du den Z-Wert nach dem 8. Spieltag suchst, benötigst du die 2. Spalte des Definitionsbereichs und gibst daher hier eine 2 ein.
Die fertige INDEX-Funktion
Zusammengesetzt ergibt das also folgende Formel, die du in Zelle L4 einfügst:
- =INDEX(C4:H21,13,2)
Schematisch betrachtet sucht die Formel innerhalb des definierten Bereichs den Schnittpunkt aus vorgegebener Zeile und Spalte:
Warum das nur die halbe Miete ist
Wenn du jetzt Zeile und Spalte entsprechend anpasst, kannst du dir also jeden beliebigen Wert aus der Tabelle abholen. Für den Z-Wert für Borussia Dortmund nach dem 9. Spieltag verwendest du also
- =INDEX(C4:H21,4,4)
Allerdings hat die Sache einen entscheidenden Nachteil: Bei Zeile und Spalte handelt es sich um statische Werte, die du immer für jedes Team manuell ändern musst. Das erbringt dir also keine Arbeitsersparnis, genauso gut könntest du die passenden Z-Werte auch gleich von Hand eintragen.
Glücklicherweise bieten dir Libre Calc und Excel aber auch die Möglichkeit, die Werte für Zeile und Spalte je nach Bedarf dynamisch zu verändern. Dazu verwendest du die VERGLEICH-Funktion.
Wie du Zeile und Spalte mit VERGLEICH dynamisch ermitteln kannst
Mit der VERGLEICH-Funktion kannst du die Teamnamen der Begegnungen mit den Teamnamen aus der Tabelle abgleichen, und daher automatisch die passende Zeile auswählen. Nach dem selben Muster kannst du dann die VERGLEICH-Funktion verwenden, um über den Spieltag die korrekte Spalte zu ermitteln.
Der Aufbau von VERGLEICH
Die VERGLEICH-Funktion besteht aus drei Komponenten, und ist nach folgendem Muster aufgebaut:
- =VERGLEICH(Suchkriterium,Suchmatrix,Art)
Das Suchkriterium
Hier gibst du ein, nach welchem Kriterium gesucht werden soll. Du könntest hier beispielsweise einen Teamnamen eingeben. Da du aber ein dynamisches Kriterium möchtest, das immer das passende Heim- oder Auswärtsteam vergleicht, gibst du hier die Zelle ein, in der sich das Team befindet.
Hier im Bild oben beispielsweise würdest du für das Heimteam der ersten Paarung als Suchkriterium K4 eingeben (statt "SC Freiburg") [1]
Die Suchmatrix
Als Suchmatrix definierst du den Bereich, in dem nach dem passenden Teamnamen gesucht werden soll. Für das Beispiel hier wäre dies also der Bereich B4 bis B21, was du in die Funktion als B4:B21 eingibst (im Bild unten blau markiert).
Die Bedeutung von „Art”
Das letzte Element der VERGLEICH-Funktion, das „Art” heißt, ist ein optionales Element – das heißt, dass du es nicht angeben musst um ein Ergebnis zu erhalten, und die meiste Zeit wird das auch einigermaßen funktionieren. Das kann allerdings im Einzelfall zu frustrierenden Fehlern führen, ich persönlich hatte so beispielsweise Probleme damit, Atlético Madrid zu finden.
Um auf Nummer sicher zu gehen, gib hier eine Null an. 0 sagt Libre Calc, dass das Suchkriterium exakt erfüllt sein muss, was bei dieser Sorte Tabelle normalerweise auch wünschenswert ist.
Die fertige VERGLEICH-Funktion
Zusammengesetzt ergibt das also folgende Formel, mit der du in der Zeile des SC Freiburgs landest:
- =VERGLEICH(K4,B4:B21,0)
Wenn du diese Formel in eine beliebige Zelle eingibst, erhältst du als Ergebnis 13 – die passende Zeile also für den SC Freiburg.
Nach dem selben Muster verfährst du mit mit dem Spieltag, für den du die korrekte Spalte ermitteln musst. Als Suchmatrix verwendest du C3:H3, und J4 als Suchkriterium. Beachte dabei aber, dass du für Freiburg den Wert vor dem 9. Spieltag abholen möchtest, weshalb du 1 von deinem Suchkriterium abziehen musst. Das sieht im Ergebnis dann so aus:
- =VERGLEICH(J4-1,C3:H3,0)
Die Kombination von INDEX und VERGLEICH
Jetzt bleibt nur noch, INDEX und VERGLEICH zusammenzuführen. Hier ist noch einmal die oben beschriebene INDEX-Funktion für Zelle L4:
- =INDEX(C4:H21,13,2)
In diese fügst du jetzt an den passenden Stellen die jeweiligen VERGLEICH-Funktionen für Zeile und Spalte ein.
Die Funktion für Zelle L4 sieht jetzt also so aus:
- =INDEX(C4:H21,VERGLEICH(K4,B4:B21,0),VERGLEICH(J4-1,C3:H3,0))
Für die Gastmannschaften sieht die Formel nahezu identisch aus, im Fall von Gladbach musst du lediglich das Suchkriterium K4 durch M4 ersetzen.
- =INDEX(C4:H21,VERGLEICH(M4,B4:B21,0),VERGLEICH(J4-1,C3:H3,0))
Um schließlich den Z-Wert für das Spiel zu erhalten, ziehst du nur noch den Z-Wert der Auswärtsmannschaft von dem der Heimmannschaft ab, was in meinem Beispielspreadsheet in Zelle O4 geschieht (=L4-N4).
Vorbereiten für das automatische Auffüllen
In meinem Beispielspreadsheet befinden sich nur drei Paarungen, aber logischerweise ist das Ziel in aller Regel, eine oder mehrere Saisons abzudecken. Entsprechend wirst du die Zellen für die restlichen Begegnungen automatisch auffüllen wollen. Dazu musst du allerdings in den Formeln erst noch alle Werte konstant setzen, die eine Matrix definieren.
Spezifisch sind das diese Bereiche:
- =INDEX(C4:H21,VERGLEICH(K4,B4:B21,0),VERGLEICH(J4-1,C3:H3,0))
Da das automatische Auffüllen vertikal stattfindet, musst du hier die Zahlen konstant setzen[2], was du mit einem Dollarzeichen direkt davor erreichst. Die letzte Fassung der Formeln für Heim- und Gastteam sollten also so aussehen:
- Heim: =INDEX(C$4:H$21,VERGLEICH(K4,B$4:B$21,0),VERGLEICH(J4-1,C$3:H$3,0))
- Gast: =INDEX(C$4:H$21,VERGLEICH(M4,B$4:B$21,0),VERGLEICH(J4-1,C$3:H$3,0))
Jetzt kannst du die restlichen Spiele auffüllen, in dem du die passenden Zellen in Zeile 4 auswählst, und das kleine, schwarze Kästchen rechts unten so weit wie gewünscht nach unten ziehst. Alternativ kannst du auch einen Doppelklick verwenden[3].
Hier findest du den fertigen Spreadsheet:
| 2018-11-24-libre-calc-1-index-vergleich-02-endversion.ods |
Fazit
Diese Kombination der INDEX- und VERGLEICH-Funktion erlaubt dir die Erstellung sehr komplexer Spreadsheets, weil du Daten in einer Tabelle ablegen, und von dort wieder je nach Bedarf auf Spielebene aufrufen kannst. Mit Hilfe der Spieltage (die du beispielsweise auch durch ein Datum ersetzen kannst) kannst du dies auch im Lauf der Zeit tun, von Spieltag zu Spieltag.
Fußnoten:
[1] Du kannst stattdessen natürlich auch einen statischen Begriff nehmen, in diesem Beispiel also direkt den Teamnamen verwenden – typischerweise ist es aber besser und flexibler, Spreadsheets so dynamisch wie möglich aufzubauen, damit spätere Änderungen leichter zu bewerkstelligen sind.
[2] Falls du horizontal auffüllst, und du dabei die Buchstaben konstant halten möchtest, kommt das Dollarzeichen direkt vor den Buchstaben.
[3] Bei einem Doppelklick füllt Libre Calc automatisch Zeile für Zeile auf, bis in der Spalte links, direkt neben dem Auffüllbereich, eine leere Zelle auftaucht.
RSS-Feed