Office 2016 - Excel: ZÄHLENWENN und Verwendung vom Solver

[Bonefish]

Hallo nochmal,

ich arbeite nebenbei an einer privaten Spielestatistik und habe mich gefragt ob Excel mir die manuelle Berechnung abnehmen kann. Für mich als Laie undenkbar wie man das lösen könnte, aber evtl. gibt es dafür tatsächlich eine Formel bzw. Möglichkeit.

Folgender Sachverhalt:

Je Turnier werden bestimmte Punkte für einen Sieg vergeben.  Turnier A: 5 Punkte, Turnier B: 8 Punkte, Turnier C: 12 Punkte usw. Am Ende des Tages habe ich dann die Anzahl meiner Siege und die Gesamtpunktzahl.

Beispiel:
3 Siege Turnier A = 15 Punkte
2 Siege Turnier B = 16 Punkte
3 Siege Turnier C = 36 Punkte

Ich hatte folglich 8 Siege und konnte dadurch 67 Punkte holen.

Kann ich maschinell ausrechnen lassen wie sich die 67 Punkte zusammensetzen? Also eine Formel die mir tatsächlich ausrechnet, dass die Punktzahl aus 3 Siegen im Turnier A, 2 Siegen im Turnier B und 3 Siegen im Turnier C resultiert? Evtl. über den Faktor 8.375 (67 Punkte/8 Siege)? Kann ich evtl. damit was anstellen?

Danke euch vielmals.

[steve1da]

Hola,

zeig bitte eine Beispieldatei - dann sehen wir was wir machen können ;-)

Gruß,
steve1da

[Bonefish]

Hallo Steve,

anbei eine Beispieldatei mit einem Hinweis wie sich die Punktzahl zusammensetzen würde.

In der Excel-Datei sollten die Werte aus dem Hinweistext maschinell in den Spalten D, E und F ermittelt werden.

[DL]

Moin

Code: [Auswählen]

=SUMMENPRODUKT(D2:F2;{5.8.12})

[Bonefish]

Guten Morgen DL,

erstmal vielen Dank für deine Unterstützung.

Die Formel wirft bei mir kein Ergebnis aus. Vielleicht habe ich mich aber auch falsch ausgedrückt.

In den Spalten D bis F sollten die Werte aus dem Hinweistext ermittelt werden. Also in Spalte D2=2, E2=3 und F2=3.

Habe auch versucht die Formel abzuändern in =SUMMENPRODUKT(C2:B2;{5.8.12}), aber irgendwie wird das so nicht übernommen. Die Formel wird immer als =SUMMENPRODUKT(B2:C2;{5.8.12}) übernommen.

Wobei ich nicht abschätzen kann ob ich damit das gewünschte Ergebnis erhalte bzw. ob ich die Formel jeweils in die Spalten D bis F hinterlegen soll.

[DL]

In den Spalten D bis F sollten die Werte aus dem Hinweistext ermittelt werden. Also in Spalte D2=2, E2=3 und F2=3.
die Formel jeweils in die Spalten D bis F hinterlegen soll.

Welcher Hinweistext?
Die Formel ist für Spalte C.

[Bonefish]

Guten Morgen DL,

in A15-A18 habe ich notiert, wie sich die Punkte in der Spalte C zusammensetzen, als Hinweis für euch. Die Formel von dir kann ich für mein Anliegen nicht nutzen.

Ich erfasse in Spalte B die Anzahl der Siege und in Spalte B die Punkte, welche aus den Siegen resultieren.

In den Spalten D, E und F soll nun maschinell ermittelt werden, wie sich die Punkte in Spalte B zusammensetzen, basierend auf den Daten D1 (Turnier A=5 Punkte), E1 (Turnier B=8 Punkte), D3 (Turnier C=12 Punkte) usw. Demnach für das Ergebnis in Spalte C (70 Punkte) aus 2 Siegen Turnier A, 3 Siegen Turnier B und 3 Siegen Turnier C.

In Spalte D1 soll also eine "2" stehen, in Spalte E1 eine "3" und in Spalte F1 eine "3".

Das Ganze ist natürlich recht mathematisch, aber ich kann nicht abschätzen was Excel in der Lage ist zu leisten.

Danke dir.

[DL]

Jetzt habe ich das verstanden.
Du willst aus der Formel  x*5+y*8+z*12 = 70 die Werte für x, y und z ermitteln.
Da habe ich eine gute Nachricht für dich. Es gibt mehr als eine Lösung.
Z.B:
14-0-0
2-6-1
6-5-0
usw.

[Bonefish]

Guten Morgen DL,

die 70 Punkte kommen ja aus 8 Siegen (Spalte B2) zustande. Somit kann es rechnerisch nur eine Lösung geben.

Ich will aus der Kombination Siege und Punkte ermittelt bekommen, welche Turniere (Spalte D1, E1, F1) wie oft gewonnen wurden. Und diese Ergebnisse sollen dann in die Zellen D2 usw.

[DL]

Das wäre dann wohl ein Fall für den Solver.

[Bonefish]

Kennst du dich damit aus? Ich habe das Add-In installiert und bisschen danach gegoogelt, aber eine passende Lösung für meine Anforderung gab es natürlich nicht.

[DL]

Da muss ich auch erst mal passen.


Vielleicht helfen ja noch Lösungsansätze für "Gleichungssysteme mit 3 Variablen und 2 Gleichungen".

[MasterOfHardness]

Schau dir die Datei an.

Ich habe die Punktewertung für die einzelnen Turniere in eine eigene Zelle geschrieben, dann lässt sich damit auch rechnen. Diese Zelle habe ich dann noch ein wenig formatiert.

Das geht recht einfach, habe da gleich zwei verschiedene Formeln gefunden.

[Bonefish]

@ DL
Ich google mal konkret danach, zumal da viel Mathematik mit im Spiel ist. Danke dir.

@ MasterOfHardness
Herzlichen Dank für deine Mühe. Deine Formeln funktionieren einwandfrei. Allerdings brauche ich die umgekehrten Ergebnisse, nämlich die Anzahl der gespielten Turniere (Spalte D - F) auf
Basis der erfassten Anzahl der Siege (Spalte B) und der Gesamtpunkte (Spalte C).

Also die Daten in Spalte B und C werden von mir erfasst und die Spalten D bis F sollen maschinell ermittelt werden.


Siehe dazu auch:

in A15-A18 habe ich notiert, wie sich die Punkte in der Spalte C zusammensetzen, als Hinweis für euch. Die Formel von dir kann ich für mein Anliegen nicht nutzen.

Ich erfasse in Spalte B die Anzahl der Siege und in Spalte B die Punkte, welche aus den Siegen resultieren.

In den Spalten D, E und F soll nun maschinell ermittelt werden, wie sich die Punkte in Spalte B zusammensetzen, basierend auf den Daten D1 (Turnier A=5 Punkte), E1 (Turnier B=8 Punkte), D3 (Turnier C=12 Punkte) usw. Demnach für das Ergebnis in Spalte C (70 Punkte) aus 2 Siegen Turnier A, 3 Siegen Turnier B und 3 Siegen Turnier C.

In Spalte D1 soll also eine "2" stehen, in Spalte E1 eine "3" und in Spalte F1 eine "3".

Das Ganze ist natürlich recht mathematisch, aber ich kann nicht abschätzen was Excel in der Lage ist zu leisten.

[MasterOfHardness]

Oops, habe die Aufgabenstellung nicht richtig gelesen (bzw. verstanden).

Ich denbke, für dein Anliegen gibt es keine zweifelsfreie Lösung. Ich bin jetzt nicht "DER MATHEMATIKER VORM HERRN", aber ich denke, dass es durchaus vorkommen kann, dass bei einem Gsamtpunktestand X mehrere Möglichkeiten bestehen, diese erlangt zu haben.

[Bonefish]

Hi,

nicht in Verbindung mit der Anzahl der Spiele. Wenn man nur von der Gesamtpunktzahl ausgehen würde, gibt es sicherlich mehrere Möglichkeiten. Aber aus der Kombi Anzahl Spiele und Gesamtpunktzahl kann es nur eine Lösung geben.

[MasterOfHardness]

Habe nochmal ein wenig herumgespielt.

12 x 5 Pkt. + 5 x 12 Pkt. = 120 Pkt.

12 x 5 Pkt. + 3 x 8 Pkt. + 3 x 12 Pkt. = 120 Pkt.

8 x 5 Pkt. + 4 x 8 Pkt. + 4 x 12 Pkt. = 120 Pkt.

Aber Bonefish hat natürlich recht, wenn man die Gesamtanzahl der Spiele mit berücksichtigt, dann kann es in der Tat nur eine Lösung geben.

Sicher kann man das auch irgendwie berechnen, da bin ich aber mit meinem Latein am Ende 

[Bonefish]

Ich komme da leider auch nicht weiter. Hab zig mal versucht das halbwegs rechnerisch zu lösen, aber ich bin kläglich daran gescheitert. Irgendwann war nur dunkel im Kopf

[Bonefish]

Hallo Leute,

ich habe am Wochenende versucht für meine Anforderung mittels Google eine Lösung zu finden, aber keine Chance. Das einzig Nützliche was ich finden konnte, ist unter nachfolgendem Link aufgeführt:

https://sites.google.com/a/erhard-rainer.com/erhard-rainer/mathematik/lineare-gleichungssysteme/gleichungssysteme-excel-solver

Allerdings wird darin nicht explizit eine Lösung für meine Anforderung beschrieben, so dass ich kein bisschen voran gekommen bin.

Kann mir jemand auf Basis des Links ein paar Tipps geben, wie ich zum Ziel kommen könnte?

Dank euch.

[MasterOfHardness]

Hab mich mal ein wenig mit dem Solver beschäftigt. Damit lässt sich das Problem tatsächlich lösen. Schau dir den Anhang an.

Ich versuch mich aber auch noch an einem Gleichungssystem, damit sollte das vielleicht auch lösbar sein.

[Bonefish]

Guten Morgen MasterOfHardness,

vorab Tausend Dank für deine tatkräftige Unterstützung in dieser Sache, schätze ich wirklich sehr.

Ich habe mir deine Lösung heute angeschaut - bin gestern leider nicht mehr dazu gekommen - und ich glaube, dass es leider nicht meiner Aufgabenstellung entspricht.

Deine Lösung berechnet die Werte in Spalte D3 und E3 auf Basis der Erfassungen in A3 bis C3. Ich habe allerdings die Werte D3 und E3 und muss daraus die Werte für A3 bis C3 ermitteln. Also genau der umgekehrte Weg.

Meine bekannten Faktoren anhand deines Beispiels sind die "festen" Werte A2 bis C2 und die Werte in D3 und E3, welche ich manuell täglich erfasse. Auf Basis dieser Werte sollen die Werte für A3 bis C3 ermittelt werden.

Ich kann mir auch sehr gut vorstellen, dass diese Berechnung über Excel nicht möglich ist. Wie ist deine Einschätzung dazu?

Danke dir vielmals.

PS: Benötigst du evtl. weitere Infos von mir hinsichtlich der Aufgabenstellung?

[MasterOfHardness]

Nee nee, der Solver ermittelt genau die in den Zellen A3 bis C3.

Dazu werden dem Solver der Wert übergeben, die in den Zellen D3 bis E3 (67 bzw. zu erreichen sind.

Ich bin derzeit noch dran, das, was der Solver tut in eine Formel zu fassen, brauche noch etwas Zeit.

Übrigens: Die Mathematik lässt mit den Zielwerten 67 bzw. 8 noch mind. zwei weitere Lösungen zu. Allerdings sind die Ergebnisse dann teilweise negativ bzw. Dezimalzahlen. Scheidet also in deinem Anwendungsfall aus. Kommste selbst drauf oder soll ich die Lösung verraten?

[Bonefish]

Um ehrlich zu sein war meine Befürchtung, dass deine Lösung tatsächlich korrekt ist, ich aber aufgrund meiner Defizite in Excel nicht in der Lage bin deine Lösung nachzuvollziehen 

Ich habe mich die letzten 2 Tage mit deiner Tabelle auseinandergesetzt und ich kann den Ablauf wirklich nicht nachvollziehen, zumal in deiner Tabelle die "festen Werte" in D3 und E3, welche für  die Berechnung erforderlich sind, durch eine Formel ermittelt werden und nicht manuell erfasst werden. Ich steig da momentan gedanklich noch nicht durch. Daher erübrigt sich auch deine Frage ob ich selber drauf komme 

PS: Die einzige Erklärung die ich für deinen Rechenweg habe ist, dass die Werte in D3 und E3 direkt im Solver erfasst werden müssen. Ist dem so?

[MasterOfHardness]

Völlig korrekt!

Die Werte in D3 und E3 müssen Formeln enthalten, damit der Solver die Parameter (bzw. Zahlen) ermitteln kann, damit das Formelergebnis deinen Wünschen (8 bzw. 67) entspricht.

Durch Try and Error sucht der Solver nach Lösungen. Hier wird zwar nur eine gefunden, das heißt aber nicht, dass es nicht auch mehrere gibt.

Komme leider beim Entwickeln des Gleichungssystems nicht weiter, muss da auch aufhören. Bin hier auf Arbeit, da hab ich tatsächlich manchmal auch was anderes zu tun 

drei weitere Lösungen (allerdings irrelevant):
a = -1
b = 9
c = 0

oder (mit Dezimalzahlen)
a = 4,14285714285714
b = 0
c = 3,85714285714286

oder auch
a = 2,49982228443649
b = 2,87531375223614
c = 2,624864963


Interessantes Problem, für mich aber leider nicht direkt lösbar 

[Bonefish]

Jetzt habe ich den Ablauf dahinter laaaaangsam begriffen. Nicht, dass ich in der Lage wäre so etwas selber zu entwickeln, aber zumindest kann ich deine Arbeit auf meine komplette Tabelle anwenden.

Und da in meiner originalen Tabelle natürlich viel mehr Turniere enthalten sind mit mehr unterschiedlichen Punkten, kann es tatsächlich unglaublich viel mehr verschiedene Lösungen geben. Ich habe das so nicht erwartet. Selbst wenn ich die Variablen von 12 auf 3 begrenze, berechnet mir der Solver ein anderes Ergebnis als das tatsächliche. Und beide Ergebnisse sind rechnerisch möglich. Ich bin total überrascht darüber.

Deine Solver-Lösung finde ich Klasse, macht Spaß den Solver laufen zu lassen. Kann ich mir durch den Solver ggf. mehrere mögliche Lösungen anzeigen lassen? Habe dazu keine Möglichkeit gefunden. Unter Optionen habe ich den Punkt "Max. machbare Lösungen" gefunden, aber es wird dennoch nur ein Ergebnis ermittelt bzw. angezeigt.

[MasterOfHardness]

Zitat:
Kann ich mir durch den Solver ggf. mehrere mögliche Lösungen anzeigen lassen?

Gute Frage, das ist mir auch nicht gelungen.

Scheint aber ein "Feature" des Solvers zu sein.

Guggst du hier: http://www.office-loesung.de/ftopic520828_0_0_asc.php