[Excel] Kleines Beispiel zur Lösung eines Gleichungssystems mit OLS und Matrix Formeln

Thomas Darimont

Erfahrenes Mitglied
Hallo,

hier mal ein kleines Beispiel wie man ein lineares Gleichungssystem in Excel mit
der Methode der kleinsten Quadrate (Least Squares, http://en.wikipedia.org/wiki/Least_squares)
unter Verwendung von Excels-Matrixformeln (MTRANS, MMULT; MINV) berechnen kann.

Siehe Excel Sheet im Anhang (im Zip .xlsx Excel 2007 Format)


Gruß Tom
 

Anhänge

  • excel_least_squares_beispiel.PNG
    excel_least_squares_beispiel.PNG
    22,3 KB · Aufrufe: 88
  • gleichungssysteme_lösen_ols.xlsx.zip
    9,6 KB · Aufrufe: 59
Ich möchte mit dieser Funktion das Mischungsverhältnis von 3 Flüssigkeiten mit jeweils einer bestimmten Konzentration von 3 Komponenten bestimmen.
Als Beispiel (fiktive Werte, fiktive Flüssigkeiten)
Flüssigkeit 1 enthält 4% Zucker, 3% Kohlenhydrate und 5% Salz
Flüssigkeit 2 enthält 2% Zucker, 3% Kohlenhydrate und 2% Salz
Flüssigkeit 3 enthält 0% Zucker, 13% Kohlenhydrate und 14% Salz
Und möchte auf eine Endkonzentration kommen von 16% Zucker, 7% Kohlenhydrate und 20% Salz

Geb ich dies in Excel Datei ein, errechnet diese ein Mischungsverhältnis von 4 zu 5,4 zu -0,4.
Da ich in der Realität aber keine negativen Anteile zumischen kann, wäre ich an einer Lösung interessiert,
die unter Verwendung realer Ergebnisse eine möglichst genaue Näherung an das Zielverhältnis liefert.
Einfach die -0,4 durch 0 ersetzen kommt nicht in Frage, da je nach verwendeter Flüssigkeit auch mal ein Anteil von -20 errechnet wird.

Dabei darf das gewünschte Zielverhältnis um plus / minus 20% variieren, also Zucker von 13 bis 19%,
Kohlenhydrate 6 bis 8% und Salz 16 bis 24% (gerundet)

Gibt es da eine Idee oder zumindest Ansatz dafür?
Oder muss ich von Hand in n Schritten (z.B 2% Schritten) ein 3-dimensionale Ergebnis-Array berechnen und mir den schönsten Wert aussuchen?

Und wenn dies die einzigste Möglichkeit ist, wie bestimme ich den schönsten Wert?
Ich kann ja in einem Makro mit 3 Schleifen 21*21*21 (10 Schritte pro Zutat in Plus oder Minus Richtung + Normalwert) Gleichungssysteme aufstellen und ausrechnen lassen, aber wie bestimme ich die Lösung, die am nähsten am Wunschergebnis dran ist?

Schritt 1 ist klar, die mit negativem Ergebnis streichen, aber von den restlichen?
Standardabweichung vom Wunschverhältnis?
Durchschnittliche Abweichung?

Gruss
Morgy
 
Zuletzt bearbeitet:
Zurück