PDA

Vollständige Version anzeigen : WM Tabelle sortieren


daniel88
09.07.2014, 19:26
Hallo miteinander,
ich habe folgendes Problem:

Ich habe mir einen WM Spielplan erstellt mit den gegebenen Paarungen und über WENN Vergleiche, wann es wieviele Punkte gibt. Das hat auch alles prima funktioniert.

Daraufhin habe ich mir auf einem anderen Tabellenblatt die Tabelle der einzelnen Gruppen über "SUMMEWENN" Vergleiche zusammengestellt. Auch alles ohne Probleme.

Nun kommt der Punkt an dem ich nicht weiterkomme:
Sortiere ich nun die Tabelle nach Punkten, bringt Excel die Mannschaften zwar in die richtige Reihenfolge, die dazugehörigen Punkte jedoch stimmen nicht überein (siehe Gruppe B im Anhang) und sind auch nicht absteigend sortiert (obwohl ich dies ausgewählt habe).

Kann mir hier bitte jemand weiterhelfen?

MfG
Daniel

daniel88
09.07.2014, 21:02
Ich habe gerade herausgefunden, dass immer nach was anderem sortiert wird, als das was ich auswähle.

Hasso
10.07.2014, 09:16
Hallo Daniel,

in deinen Formeln hast du feste Zeilenbezüge stehen. Das gibt beim Sortieren natürlich Probleme. Du musst dort mit INDIREKT arbeiten:

<b>Gruppen</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:103px;" /><col style="width:89px;" /><col style="width:89px;" /><col style="width:40px;" /><col style="width:103px;" /><col style="width:89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff00; font-weight:bold; ">Gruppe B</td><td style="background-color:#99cc00; ">Team</td><td style="background-color:#99cc00; ">Punkte</td><td style="background-color:#99cc00; ">Tore</td><td style="background-color:#99cc00; ">Gegentore</td><td style="background-color:#99cc00; ">Differenz</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">1.</td><td >Niederlande</td><td style="text-align:right; ">9</td><td style="text-align:right; ">10</td><td style="text-align:right; ">3</td><td style="text-align:right; ">7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">2.</td><td >Chile</td><td style="text-align:right; ">6</td><td style="text-align:right; ">5</td><td style="text-align:right; ">7</td><td style="text-align:right; ">-2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">3.</td><td >Spanien</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">4.</td><td >Australien</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">9</td><td style="text-align:right; ">-9</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >C10</td><td >=SUMMEWENN(Gruppenphase!H3:H8;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span> )</span>;Gruppenphase!L3:L8)+SUMMEWENN(Gruppenphase!K3:K8;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!M3:M8)</td></tr><tr><td >D10</td><td >=SUMMEWENN(Gruppenphase!H3:H8;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!I3:I8)+SUMMEWENN(Gruppenphase!K3:K8;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!J3:J8)</td></tr><tr><td >E10</td><td >=SUMMEWENN(Gruppenphase!H3:H8;Gruppen!B11;Gruppenphase!J3:J8)+SUMMEWENN(Gruppenp hase!K3:K8;Gruppen!B11;Gruppenphase!I3:I8)</td></tr><tr><td >F10</td><td >=D10-E10</td></tr><tr><td >C11</td><td >=SUMMEWENN(Gruppenphase!H4:H9;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span> )</span>;Gruppenphase!L4:L9)+SUMMEWENN(Gruppenphase!K4:K9;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!M4:M9)</td></tr><tr><td >D11</td><td >=SUMMEWENN(Gruppenphase!H4:H9;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!I4:I9)+SUMMEWENN(Gruppenphase!K4:K9;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!J4:J9)</td></tr><tr><td >E11</td><td >=SUMMEWENN(Gruppenphase!H3:H8;Gruppen!B12;Gruppenphase!J3:J8)+SUMMEWENN(Gruppenp hase!K3:K8;Gruppen!B12;Gruppenphase!I3:I8)</td></tr><tr><td >F11</td><td >=D11-E11</td></tr><tr><td >C12</td><td >=SUMMEWENN(Gruppenphase!H5:H10;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span> )</span>;Gruppenphase!L5:L10)+SUMMEWENN(Gruppenphase!K5:K10;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!M5:M10)</td></tr><tr><td >D12</td><td >=SUMMEWENN(Gruppenphase!H5:H10;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!I5:I10)+SUMMEWENN(Gruppenphase!K5:K10;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!J5:J10)</td></tr><tr><td >E12</td><td >=SUMMEWENN(Gruppenphase!H3:H8;Gruppen!B10;Gruppenphase!J3:J8)+SUMMEWENN(Gruppenp hase!K3:K8;Gruppen!B10;Gruppenphase!I3:I8)</td></tr><tr><td >F12</td><td >=D12-E12</td></tr><tr><td >C13</td><td >=SUMMEWENN(Gruppenphase!H6:H11;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span> )</span>;Gruppenphase!L6:L11)+SUMMEWENN(Gruppenphase!K6:K11;INDIREKT<span style=' color:008000; '>("Gruppen!B" & ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!M6:M11)</td></tr><tr><td >D13</td><td >=SUMMEWENN(Gruppenphase!H6:H11;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!I6:I11)+SUMMEWENN(Gruppenphase!K6:K11;INDIREKT<span style=' color:008000; '>("Gruppen!B"&ZEILE<span style=' color:#0000ff; '>()</span>)</span>;Gruppenphase!J6:J11)</td></tr><tr><td >E13</td><td >=SUMMEWENN(Gruppenphase!H3:H8;Gruppen!B13;Gruppenphase!J3:J8)+SUMMEWENN(Gruppenp hase!K3:K8;Gruppen!B13;Gruppenphase!I3:I8)</td></tr><tr><td >F13</td><td >=D13-E13</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

daniel88
10.07.2014, 17:51
Vielen Dank Hasso,

könntest du mir bitte noch erklären, was ich genau an welchen Stellen einsetzen muss? Habe mir deine bearbeitete Tabelle angeschaut, blicke aber noch nicht so recht durch :D
Bzw. muss ich in "INDIREKT("Gruppen!B"&ZEILE()) noch irgendetwas einfügen?

Gruß
Daniel

Hajo_Zi
10.07.2014, 18:34
<a href="http://hajo-excel.de/crossposting.htm" title="Crossposting" >Zu Crossposting lese diese Seite Hajo-Excel.de</a>
Du hast Glück das JINX nicht mehr da ist, ansonsten würde Dein Beitrag wegen Crossposting geschlossen werden.
Ein Zitat aus der Netiquette (hier im Forum):
Unerwünscht sind auch Crosspostings - also dieselbe Frage gleichzeitig in mehreren Foren (nicht nur im MSOF).
Denn auf diese Weise werden mehrere Gruppen von Leuten mit dem gleichen Thema befasst, ohne dass sie voneinander wissen.
Naturgemäß laufen dann die Antworten, die im einen Forum "zu spät" gegeben wurden, ins Leere und bleiben ohne Resonanz.
Es reicht also, zunächst in einem Forum zu posten - wenn die Antworten dann unbefriedigend sein sollten, steht es einem anschließend immer noch offen, ein anderes Forum zu Rate zu ziehen.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

Hasso
10.07.2014, 18:43
Hallo Daniel,

inINDIREKT("Gruppen!B"&ZEILE())sorgt das &ZEILE() dafür, das hinter sder Spalte B immer die Zahl der Zeile, in der die Formel steht, eingefügt wird. Du hattest diese Zeilennummern ja fest vergeben und das führte eben zu den Problemen beim Sortieren.

Sieh dir doch in der Excel-Hilfe mal die Erklärung zur Funktion INDIREKT an.

daniel88
10.07.2014, 19:05
Danke Hasso,

bedeutet das, dass ich dann bei Gruppe A ein "A" dafür einsetzen muss oder hat das damit nichts zutun?
Und noch eine Frage ;) : muss ich in den Klammern bei -> &ZEILE() <- noch etwas einfügen oder ist die Datei, die du angehangen hattest so komplett fertig?

MfG
Daniel

daniel88
10.07.2014, 20:14
Jetzt komme ich so langsam dahinter :D
Ich füge gerade INDIREKT auch noch bei den Gegentoren mit ein. Da hat sich bei Punkteänderung noch nicht alles verschoben.
Wie müsste die Formel bei der Differenz aussehen? Kannst du da bitte noch einmal schauen?

MfG
Daniel

Hasso
10.07.2014, 20:14
Hallo Daniel,

das B hat nichts mit der Gruppe zu tun! Das ist die Spalte, in der die Ländernamen stehen!

Die Formel in Zelle C3 in deiner ursprünglichen Tabelle lautete:=SUMMEWENN(Gruppenphase!A3:A8;Gruppen!B3;Gruppenphase!E3:E8)+SUMMEWENN(G ruppenphase!D3:D8;Gruppen!B3;Gruppenphase!F3:F8)Diese 3 ist die Zeile, in der die Ländernamen stehen. Wenn du die Tabelle nun sortierst, steht die Zelle, die ursprünglich in C3 stand, jetzt möglicherweise in einer anderen Zeile, die Formel bezieht sich aber immer noch auf B3. Dadurch erhältst du ein falsches Ergebnis. Also muss die Zeilennummer in der Formel flexibel sein. Genau das wird durch die Funktion INDIREKT erreicht. Dort wird mitINDIREKT("Gruppen!B" & ZEILE() ) an der der Stelle die Zeilennummer 3 durch die Zeile ersetzt, in der die Formel steht. Also passt sich das beim Sortieren immer an.

Ich weiß nicht, ob du das Prinzip verstanden hast, aber bessser kann ich es nicht erklären.

Hasso
10.07.2014, 20:32
Hallo Daniel,

unsere letzten Beiträge haben sich wohl überschnitten. Bei den Gegentoren müsste die Formel in E3 analog heißen:=SUMMEWENN(Gruppenphase!A3:A8;INDIREKT("Gruppen!B" & ZEILE());Gruppenphase!C3:C8)+SUMMEWENN(Gruppenphase!D3:D8;INDIREKT("Gruppen!B" & ZEILE());Gruppenphase!B3:B8)

Aber meinst du nicht, dass du mit deiner Tabelle ein bisschen spät dran bist? :D

daniel88
10.07.2014, 20:32
Doch ich habe es verstanden. Super erklärt von dir. Danke nochmals :top:

Bin ich auf dem richtigen Weg, wenn ich dasselbe jetzt auch noch für die Gegentore und die Differenz hinterlege?
Wie würde das bei der Differenz aussehen? Dort ist ja eine komplett andere Formel hinterlegt.

Hasso
10.07.2014, 20:42
Hallo Daniel,

die Formel bei der Differenz kannst du stehen lassen, dort werden ja einfach die Werte zweier Zellen subtrahiert. Das passt Excel automatisch an.

daniel88
10.07.2014, 21:13
Ja stimmt, ist schon ein bisschen spät :grins:
Ich will mir nur anhand so einer Tabelle mehr Wissen aneignen, um auch mal den Hintergrund zu erfahren, wie solche Tabellen funktionieren :p

Jetzt habe ich doch wieder eine Frage:
In jeder Zelle steht jetzt ein und dieselbe Formel und trotzdem kommen verschiedene Werte raus! (siehe Datei im Anhang)
Wie funktioniert das bzw. woher weiß Excel von welchem Verein es sich die Punkte ziehen muss? Funktioniert das alles durch die INDIREKT Funktion?

Hasso
10.07.2014, 22:02
Hallo Daniel,

gehe ich richtig in der Annahme, dass du die Formeln nicht selbst erstellt, sondern irgendwo rauskopiert hast?

Ich versuche mal, dir das am Beispiel der Formel in C3 zu erklären:=SUMMEWENN(Gruppenphase!A3:A8;INDIREKT("Gruppen!B"&ZEILE());Gruppenphase!E3:E8)+SUMMEWENN(Gruppenphase!D3:D8;INDIREKT("Gruppen!B"&ZEILE());Gruppenphase!F3:F8)Der erste Teil bedeutet:
Suche im Bereich A3:A8 des Tabellenblatts Gruppenphase alle Zellen mit dem Land, das in Spalte B des Tabellenblatts Gruppen in der Zeile mit der Formel steht und addiere die Werte, die im Bereich E3:E8 des Tabellenblatts Gruppenphase in der gleichen Zeile wie das Land stehen. Für den zeiten Teil (nach dem Pluszeichen) gilt das analog.
Ich hoffe, das hilft dir weiter.

daniel88
12.07.2014, 20:52
Hallo Hasso,

habe soweit alles verstanden und es läuft ohne Probleme.
Gibt es eine Funktion mit der sich die Tabelle bei Änderungen automatisch aktualisiert ohne jedesmal den Schritt über das benutzerdefinierte Sortieren zu gehen? (Tabelle im Anhang)

Gruß
Daniel

Hasso
13.07.2014, 08:42
Hallo Daniel,

das würde per VBA gehen. Wenn das für dich in Ordnung ist, werde ich das programmieren.

daniel88
14.07.2014, 15:04
Hallo,
ja das wäre super, danke :)

Gruß
Daniel

Hasso
14.07.2014, 17:39
Hallo Daniel,

ich habe dir dazu mal etwas geschrieben.

Wenn du im Blatt "Gruppenphase" ein Ergebnis einträgst bzw. änderst oder wenn du das Blatt "Gruppen" aktivierst, werden alle Tabellen neu sortiert.

Der Code ist zwar nicht sehr elegant, aber bei dem Aufbau deines Blatts "Gruppenphase" ist das sonst auch recht kompliziert.

Probier das mal aus und wenn's dir gefällt, freue ich mich.

Für Verbesserungsvorschläge oder Fehlermeldungen bin ich immer dankbar.

daniel88
14.07.2014, 19:01
Hallo, passt alles wunderbar und funktioniert auch soweit :top:

Könntet ihr euch bitte mal diese Tabelle im Anhang anschauen und mir Tipps geben, wie ich die einzelnen Punkte und Tore anders zusammenrechnen kann ohne jede Zeile für die jeweilige Mannschaft anzuklicken?
Habe es im Moment so hinterlegt (=M3+U9+AA4+AI7+AO3 usw.)

MfG
Daniel

Hasso
14.07.2014, 21:08
Hallo Daniel,

es würde die Auswertung wesentlich erleichtern, wenn die Spieltage mit den Ergebnissen alle untereinander in den Spalten I:N stehen würden.

daniel88
14.07.2014, 21:27
Ok, werde mich dann dran setzen und sie umändern.
Wie müsste man mit der jetzigen Tabelle vorgehen?

Hasso
15.07.2014, 12:41
Hallo Daniel,

hier kommt mein neustes Machwerk.

Ich habe die Bundesligatabelle jetzt mal umgestrickt. In Zelle D23 wählst du den Spieltag aus, wenn du neue Ergebnisse eingeben möchtest. Dieser Spieltag erscheint dann unterhalb der beiden ersten Spieltage. Nach der Eingabe wird die Tabelle automatisch neu berechnet und sortiert.

Ich würde vorschlagen, alle Zellen außer D23 und den Ergebniseingaben in den Spalte K und L zu sperren und dann das Blatt zu schützen, damit man sich nichts zerschießt. Ich habe das in der Beispielmappe mal so gemacht. Der Schutz ist ohne Passwort.

Sieh dir das Ganze mal an und melde dich bei Bedarf.

daniel88
16.07.2014, 16:10
Sehr geil die Tabelle :top:
funktioniert alles ohne Probleme. Mit Makros kenne ich mich leider überhaupt noch nicht aus.
Vielen Dank hast du dir dafür Zeit genommen hast, um so etwas zu erstellen :)