Excel Perfektion
© Mag. Christian Zahler
1 Finanzmathematische Funktionen
3.2 Ergebnis der Zielwertsuche
4.2.1 Definition von Ausgangswerten
4.6 Solver Parameter zurücksetzen
4.7.1 Solver Vollzugsmeldungen
5.4 Bearbeiten eines Szenarios
5.6 Erstellen eines Szenarioberichtes
6.2 Schema der Makroaufzeichnung:
6.3.1 Starten des Makrorecorders:
6.3.2 Aufzeichnen der Arbeitsschritte:
6.3.3 Beenden der Aufzeichnung:
7.2.1 Syntax von Matrixformeln
7.2.2 Matrizen erstellen und bearbeiten
7.2.6 Ergebnisse der Matrix in konstante Werte umwandeln
8.1.1 Von den ganzen Zahlen zu den komplexen Zahlen
8.2 Definition von komplexen Zahlen
8.2.2 Komplexe Zahlen und die Normalform
8.2.3 Kartesische Darstellung komplexer Zahlen
8.3.1 Addition und Subtraktion
8.3.2 Multiplikation und Division
8.4.2 Imaginary (Imaginärteil)
8.4.4 Imconjugate (Imkonjugierte)
8.4.11 Imargument (Imargument)
8.4.12 Imsin (Imsin) bzw. Imcos (Imcos)
Folgend werden die wichtigsten finanzmathematischen Funktionen, die EXCEL beherrscht, kurz dargestellt.
Da es zwischen den einzelnen Funktionen sehr viele Zusammenhänge gibt, finden sich in den einzelnen Abschnitten sehr viele Verweise auf andere Punkte. Es wurde von den Verfassern dieses Skriptums versucht, die Übersicht so gut wie möglich zu wahren. Aus Gründen der Verständlichkeit wird daher empfohlen, die zur Verfügung gestellten Beispiele unbedingt durchzuarbeiten.
Die im Syntax der einzelnen Funktionen fett dargestellten Argumente müssen unbedingt eingegeben werden, die dünn gedruckten Argumente können eingegeben werden.
Es ist darauf zu achten, dass für Zins (Zinssatz pro Periode, siehe Punkt ) und Zzr (Anzahl der Zahlungszeiträume) von der gleichen Zeitbasis ausgegangen wird.
(z.B. Gefahr bei jährlichem Zinssatz und monatlicher Zahlung mit 2 jähriger Laufzeit, denn hier müsste der Zinssatz Zins durch 12 dividiert, und die Laufzeit Zzr mit 2 multipliziert werden.).
Für Annuitätenfunktionen[1] gilt, dass Zahlungen, die getätigt werden durch negative Zahlen, eingenommene Beträge durch positive Zahlen dargestellt werden.
Der Barwert BW berechnet den aktuellen Wert einer Reihe zukünftiger Zahlungen und liefert den Barwert einer Investition. Die nachfolgenden Zahlungen werden mit dem Periodenzins exponentiell abgezinst.
Eine wichtige Bedingung ist, dass es regelmäßige Zahlungen in gleicher Höhe und immer gleichen zeitlichen Abständen sind.
=BW(Zins;Zzr;Rmz;Zw;F) oder engl. =PV(rate;nper;pmt;fv;type)
Zins ist der Zinssatz pro Periode (=Zahlungszeitraum). Näheres siehe Punkt 7.
Zzr gibt an, über wie viele Perioden die jeweilige Annuität (=Rente) gezahlt wird. Zzr ist die Anzahl der Zahlungszeiträume. Näheres siehe Punkt 9.
Rmz ist der konstante Betrag (Annuität), der in jeder Periode gezahlt wird. Rmz bedeutet regelmäßige, konstante Zahlungen. Näheres siehe Punkt 11.
Zw ist der zukünftige Wert (Endwert) oder der Kassenbestand, den man nach der letzten Zahlung erreicht haben möchte. Näheres siehe Punkt 10.
F bedeutet Fälligkeit und kann nur die Werte 0 oder 1 annehmen. 0 oder nicht angegeben bedeutet, dass die Zahlungen am Ende der Periode fällig sind. Eine 1 hingegen bedeutet, dass die Fälligkeit der Zahlungen am Anfang der Periode ist.
Abbildung 1: Ausschnitt aus dem Beispiel für Barwert
Durch den IKV wird der interne Zinsfuß einer Investition berechnet. Zinsfuß ist der Zinssatz, der für eine Investition erreicht wird, die in regelmäßigen Abständen erfolgt. Die Zahlungen müssen nicht gleich groß sein, jedoch der Zinsfuß muss in regelmäßigen Intervallen auftreten.
=IKV(Werte;Schätzwerte) oder engl. =IRR(values;guess)
Werte ist eine Matrix von Zellen oder ein Bezug von Zellen, in denen Zahlen stehen, für die man den internen Zinsfuß berechnen möchte. Sie muss mindestens einen positiven und einen negativen Wert enthalten. Enthält die Matrix Text, Wahrheitswerte oder leere Zellen, werden diese Werte ignoriert.
IKV nimmt an, dass die Zahlungen in der Reihenfolge erfolgen, in der sie in „Werte“ gegeben sind.
Schätzwert ist eine Zahl, von der man annimmt, dass sie dem Ergebnis nahe kommt.
Kann IKV kein geeignetes Ergebnis finden, wird der Fehlerwert „#Zahl!“ angezeigt. Diesen Fehler kann man beheben, indem man den Schätzwert verändert.
In den meisten Fällen ist es nicht erforderlich den Schätzwert anzugeben. Wird er nicht angegeben, wird er als 10% vom Ergebnis angenommen.
KAPZ liefert die Kapitalrückzahlung (Tilgung) einer Investition für die angegebene Periode. Ein konstanter Zinssatz und konstante periodische Zahlungen werden vorausgesetzt.
Die Funktion ermittelt den Tilgungsanteil der x-ten Annuität.
=KAPZ(Zins;Zr;Bw;Zw;F) oder engl. =PPMT(rate;per;nper;pv;fv;type)
Die Bedingungen sind analog zu denen des BWs.
Abbildung 2: Ausschnitt aus dem Beispiel für Kapitalrückzahlung
NBW errechnet den Nettobarwert einer Investition mit Hilfe eines Abzinsungsfaktors.
=NBW(Zins; Wert1; Wert2;...) oder engl. =NPV(rate;value1;value2;…)
Wert 1; Wert 2 sind unterschiedlich hohe Zahlungen, die aber regelmäßig am Ende der Periode getätigt werden. Dazu gehören Einzahlungen und Auszahlungen. Damit der richtige NBW ermittelt werden kann, müssen die Zahlungen in der richtigen Reihenfolge stehen.
Weiters ist es wichtig, dass leere Zellen, Wahrheitswerte, Texte oder Fehlerwerte in einer Matrix ignoriert werden.
Die Grundlage des NBW sind zukünftige Zahlungen. Eine Investition, die mit NBW errechnet wird, beginnt eine Periode vor der Leistung des Wert 1. Beendet wird sie mit dem letzten Wert. Erfolgt eine Zahlung schon in der ersten Periode, dann darf sie nicht zu den Werten hinzugefügt werden, sondern muss zum Ergebnis addiert werden.
NBW ist vergleichbar mit BW. Der Unterschied besteht darin, dass bei BW die Zahlungen sowohl am Anfang, als auch am Ende der Periode beginnen können, aber dafür konstante Beträge sein müssen. Bei NBW hingegen können variable Zahlungen stattfinden.
NBW ist außerdem mit der Funktion IKV verwandt. IKV berechnet den Zinssatz, für den der NBW einer Investition den Wert 0 annimmt:
=NBW(IKV(...);...)=0
Abbildung 3: Ausschnitt aus dem Beispiel für Nettobarwert
ZINS liefert den Zinssatz einer regelmäßigen Zahlung mithilfe eines Iteratiosverfahren wobei keine oder mehrere Lösungen möglich sind.
=ZINS(Zzr;Rmz;Bw;Zw;F;Schätzwert)
oder engl. =RATE(nper;pmt;pv;fv;type;guess)
Zzr bedeutet Anzahl der Zahlungszeiträume. Die genaue Beschreibung ist unter Punkt 9 zu finden.
RMZ bedeutet regelmäßige Zahlung. Die genaue Beschreibung ist unter Punkt 11 zu finden.
BW bedeutet Barwert. Die genaue Beschreibung ist unter Punkt 3 zu finden.
ZW bedeutet zukünftiger Wert und stellt praktisch den Endwert dar. Genaueres siehe Punkt 10.
F bedeutet Fälligkeit und kann entweder den Wert 0 oder den Wert 1 annehmen. Wird der Wert 0 eingesetzt oder keine Fälligkeit angegeben, so geht Excel vom Anfang der Periode aus, wird 1 eingetragen, geht Excel vom Ende der Periode aus.
Schätzwert bezieht sich auf die Höhe des zu berechnenden Zinssatzes.
„=Zahl!“ wird ausgegeben, wenn Excel keine Lösung innerhalb eines bestimmten Intervalls finden kann, d.h. der Zinssatz konvergiert nicht.
Dann sollte man einen anderen Schätzwert eingeben, wobei normalerweise Konvergenz bei einem Schätzwert zwischen 0 und 1 erreicht wird.
Wird kein Wert für Schätzwert eingegeben, geht Excel von 10% aus.
Abbildung 4: Ausschnitt aus dem Beispiel für Zinssatz
ZINSZ gibt die Höhe der zu entrichtenden Zinsen für das Restkapital eines Kredites in einer bestimmten Periode aus.
=ZINSZ(Zins;Zr;Zzr;Bw;Zw;F) oder engl. =IPMT(rate;per;nper;pv;fv;type)
Zins bedeutet Zinssatz pro Periode. Die genaue Beschreibung dieses Argumentes ist unter Punkt 7 zu finden.
Zr bedeutet Zeitraum und gibt die Periode an, für die der Zinsbetrag berechnet werden soll. Zr kann nur einen Wert im Bereich zwischen 1 und Zzr annehmen.
Zzr ist die Anzahl der Zahlungszeiträume. Eine genaue Beschreibung ist unter Punkt 9 zu finden.
Bw ist der Barwert. Genaue Beschreibung: Siehe Punkt 3.
Zw ist der zukünftige Wert, in diesem Fall der Endwert. Wird für Zw nichts eingegeben, wird automatisch 0 angenommen. Näheres siehe Punkt 10.
F ist die Fälligkeit, Beschreibung siehe Punkt 7.
Abbildung 5: Ausschnitt aus dem Beispiel für ZINSZ
Mithilfe der Funktion ZZR (Zahlungszeiträume) kann die Anzahl der Zahlungsperioden einer Investition ermittelt werden, d.h. wie oft eine RMZ getätigt werden muss, um einem Endwert ZW zu erreichen.
=ZZR(Zins;Rmz;Bw;Zw;F) oder engl. =NPER(rate;pmt;pv;fv;type)
Zins ist der Zinssatz pro Periode. Eine genauere Beschreibung ist unter Punkt 7 zu finden.
Rmz ist die regelmäßige Zahlung. Die genaue Beschreibung dieses Arguments ist unter Punkt 11 zu finden.
Bw ist der Barwert. Genaue Beschreibung: Siehe Punkt 3.
Zw ist der zukünftige Wert, hier Endwert. Eine genaue Beschreibung dieses Argumentes ist unter Punkt 10 zu finden.
F ist die Fälligkeit. Für die genaue Beschreibung, siehe Punkt 7.
Abbildung 6: Ausschnitt aus dem Beispiel für Zählungszeiträume
ZW bedeutet zukünftiger Wert und gibt den Endwert einer Investition zurück.
=ZW(Zins;Zzr;Rmz;Bw;F) oder engl. =FV(rate;nper;pmt;pv;type)
Zins ist der Zinssatz pro Periode. Genau Beschreibung: Siehe Punkt 7.
Zzr ist die Anzahl der Zahlungszeiträume. Eine genaue Beschreibung ist unter Punkt 9 zu finden.
Rmz bedeutet regelmäßige Zahlung. Genaueres zu diesem Argument ist unter Punkt 11 zu finden.
Bw ist der Barwert. Die genaue Beschreibung dieses Argumentes findet sich unter Punkt 3.
F ist die Fälligkeit. Genaueres: Siehe Punkt 7.
Abbildung 7: Ausschnitt aus dem Beispiel für zukünftiger Wert
Die Funktion Regelmäßige Zahlung liefert die Höhe eines regelmäßig zu entrichtenden Betrages, wobei jedoch ein konstanter Zinssatz und Zahlungen in jeweils derselben Höhe vorausgesetzt werden.
=RMZ(Zins;Zzr;Bw;Zw;F) oder engl. =PMT(rate;nper;pv;fv;type)
Zins ist der Zinssatz pro Periode. Näheres zu diesem Argument ist unter Punkt 7 zu finden.
Zzr ist die Anzahl der Zahlungszeiträume. Eine genauere Beschreibung ist unter Punkt 9 zu finden.
Bw ist Barwert. Die genauere Beschreibung dieses Argumentes ist unter Punkt 3 zu finden.
Zw ist der zukünftige Wert, hier Endwert. Genauere Beschreibung: Siehe Punkt 10.
F gibt die Fälligkeit an. Die genaue Beschreibung dieses Arguments ist unter Punkt 7 zu finden.
Abbildung 8: Ausschnitt aus dem Beispiel für regelmäßige Zahlung
Trendlinien
Bekannt: Datenreihe der Vergangenheit
Unbekannt: Datenreihe der Zukunft
Trendlinien prognostizieren zukünftige Werte aufgrund existierender Datenwerte.
„Wie entwickelt sich der Umsatz in den nächsten Jahren?“
Datentabellen
Bekannt: Eine Variable oder zwei Variablen
Unbekannt: Ergebnis
Datentabellen zeigen das Ergebnis einer Formel, wenn Sie eine oder zwei Variablen dieser Formeln ändern.
„Wie hoch ist die jährliche Abschreibung des Computers, wenn die Laufzeit 3, 4 oder 5 Jahre beträgt und die Anschaffungskosten 2500 EUR oder 4300 EUR betragen?“
Zielwertsuche
Bekannt: Ergebnis
Unbekannt: Variable
Mit dieser Methode können Sie bestimmen, wie sich eine Variable einer Formel ändert, wenn Sie ein bestimmtes Ergebnis erhalten möchten.
„Wie lange muss der Sparvertrag bei einem Startkapital von 4000 EUR und einem Zinssatz von 8 % laufen, bis sich das Kapital verdoppelt hat?“
Solver-Berechnungen
Bekannt: Ergebnis
Unbekannt: Mehrere Variablen und Bedingungen
Der Solver zeigt, wie sich bei einem bestimmten Ergebnis mehrere Variablen unter bestimmten Bedingungen ändern können.
„Welchen Preis muss das Unternehmen ansetzen, um bei bestimmten gegebenen Kosten den Gewinn zu maximieren?“
Szenarien
Unbekannt: Variablen und Ergebnis
Szenarien zeigen die Auswirkung auf ein Ergebnis, wenn sich bestimmte Werte, die das Ergebnis beeinflussen, ändern.
„Wie viele Arbeitsstunden benötigt ein Projekt, wenn 10, 20, 30, … Mitarbeiter mit verschiedenen Arbeitsmitteln zur Verfügung stehen?“
Bei der Zielwertsuche ist das Ergebnis (=Zielwert) bekannt und eine Variable (=Parameter), von der das Ergebnis abhängt, wird gesucht.
1. Aufrufen über das Menü [Tools] – [Goal Seek] bzw. [Extras] – [Zielwertsuche]
2. Die einzelnen Parameter eingeben:
• In das Feld set cell bzw. Zielzelle den Bezug für die Zelle eingeben, die die Formel enthält, die aufgelöst werden soll.
• In das Feld To value bzw. Zielwert das gewünschte Ergebnis eingeben.
• In das Feld By changing cell bzw. Veränderbare Zelle den Zellbezug für die Zelle mit dem zu verändernden Wert eingeben.
3. OK klicken.
Abbildung 9: Zielwertsuche Parameter festlegen
Anschließend wird das Fenster Goal Seek Status bzw. Status der Zielwertsuche geöffnet.
Zu beachten:
• Die Zielzelle muss eine Formel enthalten.
• Die veränderbare Zelle muss einen Wert enthalten, da die Formel in der Zielzelle die Berechnung mit diesem Wert durchführt. Sie darf jedoch keine Formel enthalten.
Im Dialogfeld Goal Seek Status bzw. Status der Zielwertsuche sind Informationen über den Erfolg der Zielwertsuche sowie den gefundenen Wert angegeben.
Abbildung 10: Ergebnis der Zielwertsuche
Weitere Optionen des Fensters Status der Zielwertsuche:
Pause
Mit der Schaltfläche Pause können Berechnungen angehalten werden. Danach wird daraus die Schaltfläche Weiter.
Step bzw. Schritt
Mit dieser Schaltfläche lassen sich mit Pause angehaltene Berechnungen schrittweise weiter ausführen.
Next bzw. Weiter
Mit der Schaltfläche Weiter können mit Pause angehaltene Berechnungen wieder gestartet werden.
Zum Unterschied zur Zielwertsuche können beim Solver mehrere Zellen variabel sein. Die Funktion des Solvers liegt darin, so lange die Werte in den veränderbaren Zellen anzupassen, bis er den Optimalwert findet. Folgende Elemente werden dazu benötigt:
• Zielzelle
• Veränderbare Zellen
• ev. Nebenbedingungen
Der Solver wird über das Menü [Tools] – [Solver] bzw. [Extras] – [Solver] aufgerufen. Es erscheint folgendes Bild:
Abbildung 11: Solver Parameter
Im Folgendem werden die einzelnen Solver Parameter genauer erklärt.
Diese Zelle soll durch Einsetzen unterschiedlicher Werte in die veränderbaren Zellen optimiert werden. Die Zielzelle muss immer eine Formel enthalten.
Zuerst wird die Zielzelle eingegeben bzw. die Zelle markiert. Anschließend wählt man eine der folgenden Optionen aus:
Max
Inhalt der Zielzelle soll durch Verändern der variablen Zellen maximiert werden (z.B. maximaler Gewinn)
Min
Inhalt der Zielzelle soll durch Verändern der variablen Zellen minimal werden (z.B. minimale Kosten)
Value of bzw. Wert
Hier kann man einen bestimmten Wert festlegen (z.B. 0)
Diese Zellen werden vom Solver so lange verändert, bis alle Nebenbedingungen erfüllt sind und die Zielzelle den Zielwert erreicht hat. Die variablen Zellen müssen direkt oder indirekt mit der Zielzelle verbunden sein. Es können bis zu 200 veränderbare Zellen definiert werden.
Im Feld By changing cells bzw. Veränderbare Zellen sind die variablen Zellen einzugeben.
Bei Betätigung der Schaltfläche Guess bzw. Schätzen werden die veränderbaren Zellen automatisch festgelegt. Excel sucht nach allen formellosen Zellen, die in der Formel der Zielzelle verwendet werden.
Bei der Lösung des Problems werden die Werte in Abhängigkeit von den Anfangswerten der veränderbaren Zellen berechnet.
Wenn der Solver eine Lösung für ein nichtlineares Problem gefunden hat, die erheblich von den Erwartungen abweicht, kann ein Versuch mit anderen Ausgangswerten für die veränderbaren Zellen hilfreich sein. Auch die Lösungszeit kann reduziert werden, indem die veränderbaren Zellen auf Werte gesetzt werden, die dem Optimum vermutlich sehr nahe kommen.
Bei linearen Modellen haben die Anfangswerte in den veränderbaren Zellen keinen Einfluss auf die Endwerte oder die Lösungszeit.
Für jede Zelle bzw. jeden Zellbereich können Bedingungen formuliert werden, die den Wert der Zelle genauer beschreiben. Diese Nebenbedingungen werden vom Solver bei der Lösung des Problems berücksichtigt. Bei nichtlinearen Problemen können bis zu 100 Nebenbedingungen festgelegt werden und bei aktiviertem Kontrollkästchen Assume Linear Model bzw. Lineares Modell voraussetzen im Dialogfeld Optionen ist die Anzahl der Nebenbedingungen unbegrenzt.
Durch Drücken der Schaltfläche Add kann eine Nebenbedingung hinzugefügt werden. Eine bestehende Bedingung wird mit Change verändert und mit Delete gelöscht.
Durch Betätigung der Schaltfläche Add erscheint folgendes Dialogfenster:
Abbildung 12: Solver: Nebenbedingung hinzufügen
Cell reference bzw. Zellbezug
Der Bezug der Zelle dessen Wert durch Nebenbedingungen eingegrenzt wird.
Constraint bzw. Nebenbedingung
Ein Wert, der zur Problemlösung erfüllt sein muss; es kann auch auf eine Zelle oder eine Formel verwiesen werden.
Folgende Beziehungen zwischen Bezugsquelle und Bedingung sind möglich:
<= Kleiner gleich
>= Größer gleich
= Gleich
Int/Ganzz. Ganzzahlig
Bin Binäre Zahl (0 oder 1)
Die Beziehungen int und bin können nur in Nebenbedingungen für veränderbare Zellen verwendet werden.
Um die Nebenbedingung zu bestätigen, muss die Schaltfläche OK gedrückt werden. Zu Eingabe weiterer Nebenbedingungen muss wiederum die Schaltfläche Add betätigt werden.
Durch Betätigung der Schaltfläche Optionen können Voreinstellungen des Solvers, Schnelligkeit und Genauigkeit der Berechnung beeinflusst werden.
Abbildung 13: Solver Optionen
Max Time bzw. Höchstzeit
gibt die maximale Lösungszeit in Sekunden an. Es können höchstens 32.767 Sekunden (ca. 9 Stunden) eingegeben werden. Falls die maximale Zeit überschritten wird, bevor der Solver eine Lösung gefunden hat, erscheint folgendes Dialogfenster:
Abbildung 14: Solver Zwischenergebnis Höchstzeit
Hier hat man nun die Möglichkeit den Lösungsprozess zu stoppen. Über die Schaltfläche Continue bzw. Weiter wird der Rechenprozess fortgesetzt und die Zeitbeschränkung wird nicht mehr beachtet.
Iterations bzw. Iterationen
Damit wird die maximal zulässige Anzahl von Zwischenberechnungen beschränkt. Bei Überschreitung der Beschränkung zeigt sich folgendes Fenster:
Abbildung 15: Solver Zwischenergebnis Iterationsgrenze
Precision bzw. Genauigkeit
Hier kann die Genauigkeit des Ergebnisses festgelegt werden. Die Zahl, die hier eingegeben wird, bestimmt, wie genau der Zellbezug mit der Bedingung übereinstimmen muss, damit die Nebenbedingung erfüllt ist. Je kleiner die Zahl ist, desto größer ist die Genauigkeit. Diese Zahl muss zwischen 0 und 1 liegen.
Tolerance bzw. Toleranz
Dieser Wert gibt die zulässige Fehlerabweichung in Prozent für Ganzzahlen an und wird verwendet, da manche Lösungsvorgänge (z.B. bei vielen Nebenbedingungen) sehr viel Zeit in Anspruch nehmen.
Convergence bzw. Konvergenz
Bei Unterschreitung der Zahl in diesem Feld durch die relative Änderung in der Zielzelle bei den letzten 5 Iterationen, hält der Solver an. Die Konvergenz kann nur bei nichtlinearen Problemen angewendet werden.
Assume Linear Model bzw. Lineares Modell voraussetzen
Bei Aktivierung des Kontrollkästchens wird ein lineares Modell vorausgesetzt. Dadurch wird der Lösungsvorgang beschleunigt, wenn alle Beziehungen im Modell linear sind und entweder ein lineares Optimierungsproblem gelöst oder ein linearer Näherungswert für ein nichtlineares Problem gesucht wird. Diese Option ist für das Lösen von Optimierungsaufgaben nicht unbedingt notwendig. Wenn man sich nicht sicher ist, dann sollte man dieses Kontrollkästchen nicht aktivieren. Auf diese Weise kann man ebenso lineare Optimierungsaufgaben lösen.
Assume Non-Negativ bzw. Nicht-Negativ voraussetzen
Der Solver nimmt hier einen unteren Grenzwert von Null an für die variablen Zellen, die keine Nebenbedingungen mit einem unteren Grenzwert haben.
Use Automatic Scaling bzw. Automatische Skalierung anwenden
Eine Einstellung, die verwendet werden soll, wenn die Werte der variablen Zellen von den Werten der Zielzelle stark abweichen, z.B. bei der Maximierung des prozentuellen Gewinns auf der Grundlage von Investitionen in Millionenhöhe.
Show Iteration Results bzw. Iterationsergebnisse anzeigen
Zeigt nach jeder Zwischenberechnung folgendes Fenster und das Ergebnis am Arbeitsblatt an:
Abbildung 16: Solver Zwischenergebnis Iterationsergebnisse
Auch hier ist es wieder möglich den Solver bei seinen Berechnungen zu unterbrechen.
Estimates bzw. Schätzung
Diese Einstellung gibt den Lösungsansatz, der bei der Ermittlung der Schätzwerte der Grundvariablen bei jeder eindimensionalen Suche verwendet wird an.
· Tangent bzw. Linear: Verwendung der linearen Extrapolation
· Quadratic bzw. Quadratisch: Verwendung der quadratischen Extrapolation (führt unter Umständen bei extrem nichtlinearen Problemen zu verbesserten Ergebnissen)
Derivatives bzw. Differenz
· Forward bzw. Vorwärts: Der Punkt der vorhergehenden Iteration wird für die Berechnung des nächsten Punktes genutzt. Dadurch wird die Berechnungszeit verkürzt.
· Central bzw. Zentral: Die Berechnungszeit dauert hier länger, jedoch ist diese Form besser wenn sich die Differenzen der Iterationspunkte stark ändern.
Search bzw. Suchen
Diese Einstellung gibt den für die Iterationen verwendeten Algorithmus an, um die Suchrichtung festzulegen.
· Newton: Hier wird ein Quasi-Newton-Verfahren verwendet. Der Vorteil von diesem Verfahren ist, dass weniger Zwischenergebnisse benötigt werden, dafür aber mehr Arbeitsspeicher erforderlich ist.
· Conjugate bzw. Gradient: Bei diesem Verfahren wird weniger Arbeitsspeicher benötigt, jedoch ist im Allgemeinen eine größere Anzahl von Zwischenergebnissen notwendig, um eine bestimmte Genauigkeit zu erzielen. Diese Option ist dann sinnvoll, wenn das Problem umfangreich ist und der zur Verfügung stehende Speicherplatz eventuell nicht ausreicht.
Die Einstellungen Schätzung, Differenz und Suchen betreffen nur den Standard GRG Solver und verbessern den Lösungsprozess meist nur in wissenschaftlichen oder technischen Anwendungen. Für den Normalgebrauch ist es deshalb meist besser, diese Voreinstellungen des Solvers beizubehalten und eher die vorher genannten Optionen zu verändern (obwohl eine Änderung der Einstellungen Schätzung, Differenz und Suchen sicher keinen „Schaden“ anrichten wird).
Das erste Solver-Modell wird durch Excel automatisch mit der Tabelle gespeichert. Durch die Schaltfläche Save Model bzw. Modell speichern in den Optionen können mehrere Modelle gesichert werden.
Die Zellbezüge der Zielzelle und der veränderbaren Zellen und auch die Nebenbedingungen können gespeichert werden, um sie zu einem späteren Zeitpunkt wieder zu verwenden.
Abbildung 17: Solver-Modell speichern
Hier wird der Tabellenbereich ausgewählt, in dem man das Modell speichern will.
Durch Betätigung der Schaltfläche Load Model bzw. Modell laden können diese Einstellungen für die aktuelle Tabelle angewendet werden.
Die geänderten Solver-Einstellungen bleiben während der Arbeitssitzung von Excel erhalten. Bei einer neuen Problemlösung werden wieder automatisch die Standardeinstellungen herangezogen. Es ist aber auch möglich die ursprüngliche Einstellungen manuell wiederherzustellen durch Betätigung der Schaltfläche Reset All bzw. Zurücksetzen.
Das Ergebnis wird in der Tabelle angezeigt und gleichzeitig wird folgendes Dialogfeld sichtbar:
Abbildung 18: Solver Ergebnis
Keep Solver Solution
Hier wird die Lösung beibehalten.
Restore Original Values
Mit Anklicken dieses Optionsfeldes werden die Ausgangswerte wiederhergestellt.
Hat der Solver eine Lösung gefunden, wird im Dialogfeld Solver Results bzw. Ergebnis eine der folgenden Meldungen angezeigt:
•
Solver hat eine Lösung gefunden. Alle Nebenbedingungen und Optionen
wurden eingehalten
Alle Nebenbedingungen wurden genau erfüllt. Für die Zielzelle wurde ein Höchst-
oder Mindestwert gefunden.
• Solver hat die aktuelle Lösung durch Konvergieren erreicht. Alle Nebenbedingungen wurden eingehalten
Die relative Änderung in der Zielzelle liegt unter dem im Dialogfeld Optionen im Feld Konvergenz eingestellten Wert für die letzten fünf Zwischenergebnisse. Wenn Sie die Konvergenz auf einen kleineren Wert einstellen, kann Solver bei verlängerter Lösungszeit eine bessere Lösung ermitteln.
Wird keine optimale Lösung erzielt, zeigt Solver eine der folgenden Meldungen im Dialogfeld Ergebnis an:
• Solver kann die aktuelle Lösung nicht verbessern. Alle Nebenbedingungen wurden eingehalten
Obwohl nur eine annähernde Lösung gefunden wurde, können mit Hilfe des Iterationsprozesses keine besseren als die angezeigten Werte erreicht werden. Entweder kann keine höhere Genauigkeit erzielt werden oder die Genauigkeit ist zu niedrig eingestellt.
• Solver wurde beim Erreichen der Höchstzeit abgebrochen
In der maximal vorgesehenen Zeit wurde keine befriedigende Lösung gefunden.
• Solver wurde beim Erreichen der Iterationsgrenze abgebrochen
Mit der maximal zulässigen Anzahl von Iterationen wurde keine befriedigende Lösung gefunden.
• Werte der Zielzellen konvergieren nicht
Der Wert der Zielzelle wächst oder fällt gegen Unendlich, obwohl alle Nebenbedingen erfüllt sind.
• Solver konnte keine realisierbare Lösung finden
Solver konnte kein Zwischenergebnis ermitteln, das alle Nebenbedingungen im Rahmen der angegebenen Genauigkeit erfüllt. Die Nebenbedingungen sind vermutlich unvereinbar.
• Solver wurde auf Veranlassung des Anwenders unterbrochen
Im Dialogfeld Zwischenergebnis wurde nach dem Unterbrechen des Lösungsprozesses oder beim schrittweisen Anzeigen der Zwischenergebnisse auf Stopp geklickt.
• Die Linearitätsbedingung wurde nicht eingehalten
Das Kontrollkästchen Lineares Modell voraussetzen wurde aktiviert, aber die letzte Berechnung ergibt Werte, die mit dem linearen Modell nicht übereinstimmt.
• Solver hat in einer Zielzelle oder in einer Nebenbedingung einen Fehlerwert gefunden.
Eine oder mehrere Formeln haben bei der letzten Berechnung einen Fehlerwert ergeben. In der fehlerhaften Ziel- oder Nebenbedingungszelle muss die betreffende Formel geändert werden, um einen brauchbaren numerischen Wert zu erhalten.
• Es ist nicht genügend Speicherplatz zur Lösung des Problems vorhanden.
Microsoft Excel konnte Solver den erforderlichen Arbeitsspeicher nicht zuteilen. Man sollte einige Dateien oder Anwendungen schließen und den Vorgang erneut durchführen.
• Eine andere Microsoft Excel-Instanz verwendet bereits SOLVER.DLL.
Mehrere Microsoft Excel-Sitzungen werden gleichzeitig ausgeführt und die Datei SOLVER.DLL wird bereits in einer anderen Sitzung verwendet.
Im Listenfeld Reports bzw. Berichte können folgende Einträge gewählt werden:
Answer bzw. Antwort
Der Bericht listet die Zielzelle und die veränderbaren Zellen mit den Ausgangswerten und Endergebnissen und den Nebenbedingungen auf. Man kann genau erkennen, ob eine Nebenbedingung die Lösung eingeschränkt hat. Die Differenz gibt einen Wert zwischen dem Lösungswert und der oberen bzw. unteren Grenze, die von dieser Nebenbedingung vorgegeben ist, an. Eine einschränkende Nebenbedingung, die optimal angeglichen werden konnte, hat den Differenzwert 0.
Abbildung 19: Solver Antwortbericht
Anmerkung: Für den Eintrag in der Spalte Name wählt der Solver die erste Textzelle links und die erste Textzelle oberhalb der Zelle aus, auf die er Bezug nimmt.
Sensitivity bzw. Empfindlichkeit
Dieser Bericht liefert Informationen darüber, wie empfindlich die Lösung auf kleine Änderungen in der angegebenen Formel im Feld Zielzelle oder in den Nebenbedingungen reagiert. Zusätzlich werden statistische Berechnungen vorgenommen.
Limits bzw. Grenzwert
In diesem Bericht werden alle oberen und unteren Grenzwerten sowie die Auswirkung auf die Zielzelle angezeigt. Der untere Grenzwert entspricht dem kleinsten Wert, den die veränderbare Zelle annehmen kann, während die Werte aller anderen veränderbaren Zellen beibehalten und die Nebenbedingungen erfüllt werden. Der obere Grenzwert ist der größte Wert.
Mit Klick auf die Schaltfläche Szenario speichern, erscheint das gleichnamige Dialogfeld. Damit können die veränderbaren Zellwerte als Szenario gespeichert werden. Anschließend im Feld Szenarioname den Namen des Szenarios eingeben.
Genaueres zu Szenarien im Kapitel 3.
Abbildung 20: Solver Szenario speichern
Szenarien ermöglichen das Ersetzen einer Reihe von Werten durch andere Werte, um die unterschiedlichen Ergebnisse vergleichen und beurteilen zu können. Es können pro Szenario 32 veränderbare Zellen eingegeben werden.
1. Aufrufen über das Menü [Tools] – [Scenarios] bzw. [Extras] – [Szenarien].
Abbildung 21: Szenariomanager starten
2. Auf Hinzufügen klicken.
3. In das Feld Szenarienname einen Namen für das Szenario eingeben.
4. In das Feld Veränderbare Zellen die zu ändernden Zellbezüge eingeben.
5. Unter Schutz die gewünschten Optionen auswählen.
6. Anschließend auf OK klicken.
7. Eventuell einen Kommentar schreiben, um die Werte auch zu einem späteren Zeitpunkt noch nachvollziehen zu können.
Abbildung 22: Neues Szenario hinzufügen
8. Im Dialogfeld Szenariowerte die Werte für die veränderbaren Zellen eingeben.
9. Auf OK klicken, um das Szenario zu erstellen.
Abbildung 23: Szenariowerte eingeben
10. Sollen zusätzliche Szenarien erstellt werden, Schritt 2-8 wiederholen. Wenn alle gewünschten Szenarien erstellt wurden, auf OK und anschließend im Dialogfeld Szenarien auf Schließen klicken.
Damit Szenarien geschützt werden können, muss das Tabellenblatt geschützt sein. Dazu müssen folgende Voreinstellungen vorgenommen werden.
1. Aufrufen über das Menü [Tools] – [Protection] – [Protect Sheet] bzw. [Extras] – [Schutz] – [Blatt schützen]
2. Kennwort eingeben
3. Das Kontrollfeld Scenarios bzw. Szenarios bearbeiten aktivieren.
Unter dieser Voraussetzung kann das Szenario mit den folgenden Kontrollfeldern vor unberechtigtem Zugriff geschützt werden:
Prevent Changes
Andere Benutzer können nicht auf das Szenario zugreifen.
Hide
Der Name im Listenfeld Szenarien wird ausgeblendet. Damit kann das Szenario nicht ausgewählt werden.
1. Im Menü [Tools] auf [Scenario] bzw. [Extras] auf [Szenarien] klicken
2. Auf den Namen des gewünschten Szenarios klicken.
3. Auf Anzeigen klicken, um das Szenario anzuzeigen. Die aktuellen Werte der veränderbaren Zellen werden durch die Werte des Szenarios ersetzt.
1. Im Menü Menü [Tools] – [Scenario] bzw. [Extras] – [Szenarien] klicken.
2. Auf den Namen des zu bearbeitenden Szenarios und dann auf Edit bzw. Bearbeiten klicken.
3. Die gewünschten Änderungen durchführen.
4. OK klicken, um die Werte im ursprünglichen Szenario durch die neuen Werte zu ersetzen.
1. Die Arbeitsmappen öffnen, die Szenarien enthalten, die zusammengeführt werden sollen.
2. In das Arbeitsblatt wechseln, in das die Szenarien zusammengeführt werden sollen.
3. Im Menü [Tools] auf [Scenario] bzw. [Extras] auf [Szenarien] klicken.
4. Auf die Schaltfläche Merge bzw. Zusammenführen klicken.
5. Im Listenfeld Book bzw. Mappe die Datei auswählen, die das Tabellenblatt enthält, aus dem die Szenarien dem aktuellen Tabellenblatt hinzugefügt werden sollen.
6. Im Listenfeld Sheet bzw. Blatt das gewünschte Tabellenblatt auswählen.
7. Den Vorgang wiederholen, wenn Szenarien aus weiteren Arbeitsblättern zusammengeführt werden sollen.
Abbildung 24: Szenario zusammenführen
In einem Übersichtsbericht können die ersten 251 Szenarien angezeigt werden.
1. Im Menü Menü [Tools] auf [Scenario] bzw. [Extras] auf [Szenarien] klicken.
2. Auf Zusammenfassung klicken.
3. Durch Aktivieren des Optionsfeldes Szenariobericht bzw. Szenario-Pivot-Table-Bericht den Berichtstyp auswählen.
4. In das Feld Result cells bzw. Ergebniszellen die Zellbezüge eingeben, die auf Zellen verweisen, deren Werte durch die Szenarien verändert wurden. Mehrere Bezüge müssen durch Semikolon getrennt werden.
5. OK klicken und der Szenariobericht wird auf einem neuen Tabellenblatt erstellt.
Abbildung 25: Szenariobericht erstellen
Abbildung 26: Szenariobericht
Makros haben die Aufgabe häufig verwendete Arbeitsschritte automatisch auszuführen. Dies funktioniert indem die einzelnen Schritte zuerst aufgezeichnet und gespeichert werden und dann zu einem späteren Zeitpunkt automatisch ausgeführt werden und so dem Anwender viel Zeit ersparen können.
Prinzipiell können mit Makros alle beliebigen Aktionen automatisiert werden. Mögliche Einsatzgebiete könnten zum Beispiel sein:
· Formatieren von Zellen
· Automatisches Erstellen von Diagrammen
· Sortieren von Zellen
Das Aufzeichnen von Makros läuft immer in drei Schritten ab. Zuerst wird die Aufzeichnung gestartet, dann werden die einzelnen, zu automatisierenden Arbeitsschritte ausgeführt und zum Schluss wird die Aufzeichnung beendet.
Um keine unnötigen Schritte im Makro abzuspeichern, sollte man sich vorher ein Konzept überlegen, nach dem man vorgeht.
Abbildung 27: Schema der Aufzeichnung
Der Makrorecorder befindet sich im Menü Extras- Makro- Makro aufzeichnen
Man hat hier die Möglichkeit vor der Aufzeichnung dem Makro einen Namen und eine Tastenkombination zuzuweisen. Ebenfalls kann eine kurze Beschreibung des Makros angegeben werden (als Standard steht hier das Datum und der Name des Erstellers). All diese Informationen können auch noch nachträglich abgeändert werden.
Abbildung 28: Starten der Aufzeichnung
Der nächste Schritt ist nun die Aufzeichnung der einzelnen Arbeitsschritte. Das erscheinende Fenster( Abbildung 29: Aufzeichnung beenden) zeigt an, dass sich Excel nun im Aufnahmemodus befindet. Alle Schritte die nun ausgeführt werden, werden aufgezeichnet. Sollte es passieren, dass ein nicht beabsichtigter Arbeitsschritt getätigt wird, kann dieser wieder mit dem Rückgängig- Button aus der Aufzeichnung entfernt werden.
Abbildung 29: Aufzeichnung beenden
Der Button neben dem Stopp- Button gibt an ob ein absoluter oder relativer Bezug verwendet wird
Der relative Bezug ist vom Programm als Standard definiert, und wird auch in den meisten Fällen benötigt. Das Makro bezieht sich dann immer auf die Zelle, die beim Ausführen aktiv ist.
Beim absoluten Bezug bezieht sich Excel immer auf die Zelle die während der Aufzeichnung aktiviert war.
Hat man alle gewünschten Schritte ausgeführt wird die Aufzeichnung mit dem Stopp- Button beendet.
Man hat nun die Möglichkeit das aufgezeichnete Makro auf unterschiedliche Arten zu starten.
· Die erste Möglichkeit ist, das Makro manuell zu starten. Dies geschieht im Menü Extras- Makro-Makros. Hier wählt man nun das gewünschte Makro aus und startet es durch den Button: Ausführen.
Abbildung 31: Ausführen und Bearbeiten eines Makros
· Das Makro kann ebenfalls durch die zugewiesene Tastenkombination ausgeführt werden.
![]() |
Abbildung 32: Die "Steuerelement-Toolbox" Leiste
Dazu aktiviert man die Symbolleiste "Steuerelement-Toolbox" und wählt
das Symbol "Befehlsschaltfläche".
Mit der Maus wird dann direkt im Arbeitsblatt eine Befehlsschaltfläche
aufgezogen. Im Kontextmenü dieser Befehlsschaltfläche kann man unter "Code
anzeigen" den Visual Basic Code für die Schaltfläche eingeben und
bearbeiten.
Man kann auch einer importierten Grafik ein Makro zuweisen, indem man es mit
der rechten Maustaste anklickt, und dann „Makro zuweisen“ auswählt.
·
Die letzte Möglichkeit ein Makro zu aktivieren ist, dass man dem
Makro in der Symbolleiste ein Symbol zuordnet.
Dies geschieht unter dem Menüpunkt EXTRAS-ANPASSEN, wo sich unter dem
Menüblatt "Befehle" auch der Punkt "Makros" befindet.
Nun zieht man entweder den Befehl "Schaltfläche anpassen" in eine der
Symbolleisten. Um nun ein Makro zuzuweisen, klickt man mit der rechten Maustaste
auf die neue Schaltfläche und weist im darauf folgenden Menü ein Makro zu.
Unter EXTRAS-ANPASSEN-AUSWAHL ÄNDERN kann man der neuen Schaltfläche
einen benutzerdefinierten Namen geben oder ihr ein neues Symbol zuweisen,
beziehungsweise selbst eines entwerfen.
Alternativ lässt sich auf die selbe Weise ein benutzerdefiniertes Menüelement
in die Symbolleisten einbauen.
Die Aufzeichnung der Makros erfolgt durch die Microsoft-Office Programmiersprache Visual Basic. Die einzelnen, vom Benutzer gesetzten Arbeitsschritte werden als Anweisungen in Visual Basic verarbeitet. Excel kreiert automatisch den dazupassenden Code.
Der Visual Basic Code ist mittels dem in Excel enthaltenen Visual Basic Editor jederzeit bearbeitbar und veränderbar.
Es ist daher auch möglich, Makros ohne den Recorder zu erzeugen, allerdings ist dafür Kenntnis von Visual Basic und auch von den Parametern von Excel nötig
Abbildung 33: Visual Basic Editor
Mittels EXTRA-MAKRO-MAKROS und der Schaltfläche "Bearbeiten" kommt man direkt zum Visual Basic Code des Makros.
Dieser enthält:
· Einige Zeilen an Kommentar, die die Funktion des Makros beschreiben
· "Sub" und "End Sub", um Anfang und Ende des Makros zu kennzeichnen
· Sowie dem Namen des Makros
Aufgezeichnete Makros werden in Module gespeichert, wird die Arbeitsmappe zwischendurch geschlossen, wird ein neues Modul angelegt. Module können auch anderen Arbeitsmappen zur Verfügung gestellt werden.
Da man mit Makros eigentlich jede Aktion automatisieren kann besteht auch die Möglichkeit Makros zu erzeugen, die bei der Ausführung Daten manipulieren oder auch löschen. Um sich davor zu schützen, dass solche Makros aktiv werden, prüft Excel beim Laden jedes gespeicherten Arbeitsblattes ob Makros vorhanden sind und fragt den Anwender ob diese sicherheitshalber deaktiviert werden sollen.
Microsoft Excel für Windows Automatisierung und Programmierung, Herdt Verlag, 2000
Eine Matrix ist ein rechteckiges Schema von n * m Elementen, die in m Zeilen und n Spalten angeordnet sind.
Eine Matrix, bei der die Zeilenzahl gleich der Spaltenanzahl ist, heißt quadratische Matrix.
Eine Matrix, in der alle Elemente null sind, nennt man Nullmatrix.
Zwei Matrizen sind genau dann gleichartig, wenn sie in Zeilen- und Spaltenanzahl übereinstimmen.
Zwei Matrizen sind dann gleich, wenn sie gleichartig sind und in den entsprechenden Elementen übereinstimmen.
Zwei Matrizen sind genau dann zueinander transponiert (oder gespiegelt), wenn jeweils eine Matrix dadurch entsteht, dass bei der anderen Matrix die Zeilen mit den Spalten vertauscht werden.
Eine quadratische Matrix, bei der alle Elemente „außerhalb“ der Hauptdiagonale null sind, heißt Diagonalmatrix.
Die Hauptdiagonale einer Matrix ist die längste, von links oben nach rechts unten verlaufende Diagonale der Matrix.
Eine quadratische Matrix, bei der alle Elemente die auf der Hauptdiagonale liegen, 1 sind (und alle anderen Elemente 0 sind!), bezeichnet man als Einheitsmatrix.
Eine quadratische Matrix, die ihrer transponierten Matrix gleich ist, heißt symmetrische Matrix.
Ungleichartige Matrizen können nicht addiert werden. Gleichartige Matrizen werden addiert, indem man jedes Element der Matrix mit der entsprechenden Zahl der anderen Matrix addiert wird.
Eine Matrix wird mit einer Zahl multipliziert, indem man jedes Element der Matrix mit der Zahl multipliziert.
Um zwei Matrizen miteinander multiplizieren zu können, muss die Spaltenanzahl der ersten Matrix gleich der Zeilenanzahl der zweiten Matrix sein.
|
2 |
||
1 |
|||
6 |
|||
3 |
0 |
2 |
18 |
4 |
2 |
1 |
16 |
5 |
4 |
6 |
50 |
Die inverse Matrix A-1 ist jene Matrix, mit der die ursprüngliche Matrix A von rechts oder von links multipliziert, die Einheitsmatrix liefert.
Mit Matrixformeln können mehrere Berechnungen gleichzeitig durchgeführt werden. Sie liefern je nach Anwendung entweder ein Ergebnis in einer Zelle oder unterschiedliche Ergebnisse in mehreren Zellen. Dabei greifen diese Formeln auf einen Zellenbereich (Matrix) zu. Matrixformeln erleichtern das Erstellen von gleichen Formeln für unterschiedliche Zellen und reduzieren fehlerhafte Eingaben. Um alle Formeln zu ändern, braucht der Anwender nur eine der Zellen mit Matrixformeln zu bearbeiten und wieder als Matrixformel einzugeben.
§ Matrixformeln sind an den geschweiften Klammern zu Beginn und am Ende der Formel zu erkennen.
§ Innerhalb der geschweiften Klammern steht die gewünschte Berechnung bzw. Funktion, deren Syntax einer einfachen Formel entspricht.
§ Enthält die Matrixformel Argumente, die sich auf Matrixbereiche beziehen, müssen die Bezüge den gesamten Matrixbereich betreffen.
(1) Markieren der Zelle, die das Ergebnis der Matrixformel beinhalten soll. Sollen verschiedene Ergebnisse in mehreren Zellen angezeigt werden, muss der Bereich der Ergebniszellen markiert sein.
(2) Eingeben der gewünschten Formel als einfache Formel, d.h. ohne geschweifte Klammern. Dabei muss sich die Formel auf die Matrix beziehen.
(3) Schließen der Eingabe mit der Tastenkombination STRG + Umschalten + Return
Abbildung 34: Eingabe einer einfachen Matrixformel
Excel erkennt die Formel als Matrixformel und setzt sie automatisch in geschweifte Klammern. Die Formeln werden in jede Zelle des zuvor markierten Bereichs eingefügt.
Man kann mit Matrizen NICHT ...
§ Zellen in einen Matrixbereich einfügen.
Man kann mit Matrizen ...
Um die Matrixformel zu bearbeiten, muss der Anwender den Bereich mit den Ergebniszellen vorher markieren. In umfangreichen Tabellen ist dieser Bereich oft schwer zu finden. Als Alternative zum Markieren mit der Maus kann man den Bereich über das Dialogfenster INHALTE AUSWÄHLEN markieren.
Markieren einer beliebigen Zelle des Ergebnisbereiches.
Aufrufen des Menüpunktes BEARBEITEN – GEHE ZU (Alternative: STRG + G od. F5)
Betätigen der Schaltfläche INHALTE.
Im Dialogfenster INHALTE AUSWÄHLEN das Kontrollfeld AKTUELLES ARRAY aktivieren.
Abbildung 35: Matrixbereiche markieren
(1) Markieren der Matrix mit den Ergebniszellen oder einer einzelnen Zelle der Matrix.
(2) Bearbeiten der Matrixformel, bzw. Neueingabe.
(3) Abschließen der Bearbeitung mit der Tastenkombination STRG + Umschalten + Return.
(1) Markieren der gesamten Matrix.
(2) Taste ENTF betätigen.
(1) Markieren der Matrix, d.h. aller Zellen, die Matrixformeln beinhalten.
(2) Kopieren des Inhaltes der Zellen in die Zwischenablage.
(3) Aufrufen des Menüpunktes BEARBEITEN – INHALTE EINFÜGEN.
(4) Innerhalb der Optionsgruppe EINFÜGEN das Optionsfeld WERTE aktivieren und mit OK bestätigen.
Abbildung 36: Umwandeln der Matrix in konstante Werte
Die Formeln werden an der gleichen Stelle mit den konstanten Werten überschrieben.
Excel kennt eine Vielzahl von Funktionen, die speziell zum Bearbeiten von Matrizen geeignet sind. Diese Funktionen können mit dem Funktionsassistenten erstellt werden. Die Matrixfunktionen lassen sich nach der Art der Ergebnisse kategorisieren. Anders als Datenbankfunktionen stellen Matrixfunktionen keine einheitliche Syntax dar. Die Argumente sowie deren Reihenfolge, die für die verschiedenen Funktionen notwendig sind, werden im Hilfesystem von Excel ausführlich beschrieben.
=VLOOKUP(lookup_value;table_array;col_index_num;true/false)
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Mit Hilfe des SVerweises ist es möglich, in einer Matrix einen bestimmten Wert auszugeben. Excel beginnt an der linken oberen Zelle der Matrix und sucht nach dem Kriterium in der ersten Spalte. Ist dieser Wert gefunden, geht Excel in die angegebene Spaltennummer der Matrix und gibt den dort enthaltenen Wert in der Zielzelle aus.
Der letzte Wert in der Formel heißt Wahr oder Falsch. Falsch bedeutet, dass das angegebene Suchkriterium genau gefunden werden muss. Wahr bedeutet, dass die Werte in der Matrix aufsteigend sortiert sind und ein Wert gefunden werden soll, der dem Suchkriterium am nächsten ist.
=HLOOKUP(lookup_value;table_array;row_index_num;...)
=WVERWEIS(Suchkriterium;Matrix;Zeilenindex;Bereich_Verweis)
Der Unterschied zum SVerweis besteht darin, dass Excel beim WVerweis das Kriterium in der ersten Zeile sucht und dann in der gefundenen Spalte in die Zeile der Matrix geht, die in der Formel angegeben ist.
=TRANSPOSE(array)
=MTRANS(Matrix)
Liefert die transponierte Matrix der angegebenen Matrix. MTRANS muss als Matrixformel in einem Bereich eingegeben werden, der so viele Zeilen und Spalten hat wie die ursprüngliche Matrix Spalten und Zeilen. Mit MTRANS werden die Zeilen und Spalten einer Matrix vertauscht. Der Anwender kann insbesondere einen horizontalen Vektor in einen vertikalen Vektor und umgekehrt überführen.
=MINV(array)
=MINV(Matrix)
Liefert die Inverse einer Matrix (die zur Matrix gehörende Kehrmatrix).
§ Wenn Zellen in der Matrix leer sind oder Text enthalten, gibt MINV den Fehlerwert #Wert! Zurück.
§ MINV gibt außerdem den Fehlerwert #Wert zurück, wenn die Anzahl der Zeilen und Spalten der Matrix nicht gleich ist.
§ Inverse Matrizen werden im Allgemeinen zur Lösung von Gleichungssystemen mit mehreren Variablen verwendet. Das Produkt einer Matrix und ihrer Inverse ist die Identitätsmatrix, eine quadratische Matrix, in der die Elemente auf der Hauptdiagonale gleich 1 und alle anderen Elemente gleich 0 sind.
=MMULT(array1;array2)
=MMULT(Matrix1;Matrix2)
§ Matrix1;Matrix2 sind die Matrizen, die multipliziert werden sollen.
§ Die Anzahl der Spalten von Matrix1 muss mit der Anzahl der Zeilen von Matrix2 übereinstimmen, beide Matrizen dürfen nur Zahlen enthalten.
§ Matrix1 und Matrix2 können als Zellbereiche, Matrix-Konstanten oder Bezüge gegeben sein.
§ Gibt es Zellen, die leer sind oder Test enthalten, oder ist die Spaltenanzahl von Matrix1 nicht gleich der Zeilenanzahl von Matrix2, liefert MMULT den Fehlerwert #WERT!.
=SUMPRODUCT(array1;array2;...)
=SUMMENPRODUKT(Matrix1;Matrix2;…)
§ Matrix1; Matrix2; … sind 2 bis 30 Matrizen, deren Komponenten zunächst multipliziert und anschließend addiert werden sollen.
§ Die als Argumente angegebenen Matrizen müssen bezüglich der Zeilen- und Spaltenanzahl identisch sein. Ist dies nicht der Fall, liefert SUMMENPRODUKT den Fehlerwert #WERT!.
§ Matrixelemente, die keine numerischen Ausdrücke sind, behandelt SUMMENPRODUKT so, als wären sie mit 0 belegt.
Wenn man nur mit ganzen Zahlen (...,-2,-1,0,1,2,...) rechnet, stößt man bald auf ein Problem: Man kann zwar Gleichungen wie 4x = 20 lösen (die Lösung wäre x = 5), doch bei 4x = 3 ist das nicht mehr so einfach, denn diese Gleichung hat in den ganzen Zahlen keine Lösung.
Damit solche Gleichungen lösbar werden, muss man eine neue Art von Zahlen einführen, die Bruchzahlen. Daher definiert man: ¾ ist die Zahl, welche die Gleichung 4x = 3 löst.
Bruchzahlen und ganze Zahlen zusammen nennt man rationale Zahlen.
Doch auch mit den
rationalen Zahlen gibt es noch Probleme. Man kann zwar die Gleichung x² = 4
lösen (Lösung ist x = 2), aber x² = 2 hat in den rationalen Zahlen keine
Lösung. Also müssen wieder neue Zahlen her: die irrationalen Zahlen. Und man
definiert wieder: "die Wurzel aus 2" ist die Zahl, welche die
Gleichung x² = 2 löst.
Irrationale und rationale Zahlen zusammen nennt man reelle Zahlen.
Aber jetzt kommt
gleich das nächste Problem: die Gleichung x² = 2 kann man jetzt zwar lösen,
aber die Gleichung x² = -1 hat immer noch keine Lösung. Es werden erneut neue
Zahlen benötigt. Zu diesem Zweck führte Leonhard Euler die imaginäre Zahl i ein.
Sie ist definiert als Lösung der Gleichung x² = -1.
Imaginäre und reelle Zahlen zusammen nennt man komplexe Zahlen.
Ø Moderne Zahlenforschung
Ø Chaostheorie und Fraktalgeometrie
Ø Elektrotechnik
Die imaginären Zahlen sind Vielfache der sogenannten imaginären Einheit i, wobei i definiert ist durch:
i² = -1 bzw. i =
Eine imaginäre Zahl hat also die Form z = ai mit a Î R
Eine komplexe Zahl z setzt sich aus einem Realteil Re(z) und einem Imaginärteil Im(z) zusammen.
z = a + ib wobei a und b reelle Zahlen sind.
a...der Realteil
ib...der Imaginärteil von z
Diese Form der Darstellung heißt Normalform einer komplexen Zahl.
Ist z1 = a – ib so heißt z1 die konjugiert komplexe Zahl zu z.
Die Menge der komplexen Zahlen wird durch den Großbuchstaben C abgekürzt.
Figure 1: Zahlenstrahl für reelle Zahlen
Bei komplexen Zahlen reicht ein Zahlenstrahl nicht mehr aus, man benötigt jetzt eine Zahlenebene. Um komplexe Zahlen in einem Koordinatensystem darstellen zu können, bedient man sich der Methode von Carl Friedrich Gauß (1777-1855).
Auf der x-Achse trägt man den Realteil und auf der y-Achse den Imaginärteil der komplexen Zahl auf.
Dadurch ergibt sich folgende Vektordarstellung einer komplexen Zahl z:
Figure 2: Gauß'sche Zahlenebene
Da die y-Achse nun nicht mehr aus reellen Zahlen besteht, sondern eben aus komplexen Zahlen, nennt man das zugehörige „komplexe“ Achsenkreuz auch die komplexe Ebene oder die Gauß’sche Zahlenebene.
Durch diese neue Form der Darstellung ergibt sich eine weitere Beschreibungsmöglichkeit für komplexe Zahlen. Anstatt Real- und Imaginärteil anzugeben, stellt man eine komplexe Zahl z durch ihre Länge r im komplexen Koordinatensystem und den Winkel (f) dar, den der Vektor mit der reellen Achse einschließt. Diese Form der Darstellung nennt man die Polarform einer komplexen Zahl (Satz von Pythagoras).
Die Länge des Vektors ist
r =
=
(Funktion
IMABS)
Eine komplexe Zahl z lässt sich folgendermaßen darstellen:
Es gilt:
Re(z) = a = rcos (f) (Funktion
IMREAL)
Im(z) = ib = rsin (f) (Funktion
IMAGINARY)
Daraus folgt:
z = Re(z) +
Im(z) = r(cos (f) + sin (f)) (Funktion
COMPLEX)
Man kann mit komplexen Zahlen fast genauso rechnen, wie mit reellen Zahlen. Dazu lässt man i einfach als Variable stehen.
Figure 3: Addition und Subtraktion komplexer Zahlen
Die Addition bzw. Subtraktion zweier Zahlen z = a + bi und w = c + di funktioniert folgendermaßen:
z ± w = (a + bi) ± (c + di) = (a + c) ± (bi + di) = (a + c) ± (b + d)i
Man kann also zwei komplexe Zahlen addieren, indem man ihre Realteile und ihre Imaginärteile einzeln addiert bzw. subtrahiert.
Beispiel:
(3 + 7i) + (5 + 2i) = 8 + 9i
(3 + 7i) – (5 + 2i) = -2 + 5i
Auch für die Multiplikation gelten die gleichen Regeln wie bei reellen Zahlen. Bei der Multiplikation und Division ist zu beachten, dass i² = -1 ist.
zw = (a + bi)(c + di) = ac + adi + bic + bidi = ac + (ad + bc)i + bdi²
Weil i so definiert, dass i² = -1 ist, gilt:
zw = ac + (ad + bc)i + bdi² = ac - bd + (ad + bc)i
Beispiel:
(3 + 7i)(5 - 2i) = 15 –(14 i2) + [3(-2) + 35]i = 15 + 14 + (-6 + 35)i = 29 + 29i
Figure 4: Multiplikation von komplexen Zahlen
Man kann eine komplexe Zahl auch mit sich selbst multiplizieren, also quadrieren:
z² = (a + bi)² = aa - bb + (ab + ba)i = a² - b² + 2abi
Beispiel:
(5 – 2i)² = 5² - (-2)² + 10(-2)i = 25 - 4 + (-20)i = 21 - 20i
Bei der Division zweier komplexer Zahlen muss der Nenner geeignet erweitert werden.
Diese Funktion liefert den Realteil einer komplexen Zahl, die als Zahlenfolge x + yi oder x + yj eingegeben wird.
Syntax:
=IMREAL(inumber)
=IMREALTEIL(Komplexe Zahl)
Beispiel:
=IMREAL("7-8i") ergibt 7
Diese Funktion liefert den Imaginärteil einer komplexen Zahl.
Syntax:
=IMAGINARY(inumber)
=IMAGINÄRTEIL(Komplexe Zahl)
Beispiel:
=IMAGINARY("7-8i") ergibt 8
Mit Hilfe dieser Funktion wird der Real- und der Imaginärteil in eine komplexe Zahl umgewandelt.
Syntax:
=COMPLEX(Real_num; I_num; suffix)
=KOMPLEXE(Realteil; Imaginärteil; Suffix)
Suffix: ist der Buchstabe, der für die imaginäre Einheit der komplexen Zahlen (i bzw. j) verwendet werden soll. Fehlt das Argument Suffix, wird es als "i" angenommen.
Beispiele:
Figure 5: Zusammensetzung einer komplexen Zahl
=COMPLEX(5; 8; "j") ergibt 5 + 8j
Diese Funktion liefert die konjugiert komplexe Zahl zu einer komplexen Zahl.
Syntax:
=IMCONJUGATE(inumber)
=IMKONJUNGIERTE(Komplexe Zahl)
Beispiel:
=IMCONJUGATE("6 + 3i") ergibt 6 – 3i
Liefert den Absolutbetrag (Modulus) einer komplexen Zahl.
Syntax:
=IMABS(inumber)
=IMABS(Komplexe Zahl)
Beispiel:
=
z = 5 + 12i
=
r = 13
=IMABS("5 + 12i") ergibt 13
Liefert die Summe komplexer Zahlen.
Syntax:
=IMSUM(inumber 1; inumber 2,...)
=IMSUMME(Komplexe Zahl 1; Komplexe Zahl 2,…)
Beispiel:
(a + bi) + (c + di) = (a + c) + (b + d)i
=IMSUM("6 + 3i"; "5 + i") ergibt 11 + 4i
Liefert die Differenz zweier komplexer Zahlen.
Syntax:
=IMSUB(inumber 1; inumber 2,...)
=IMSUB(Komplexe Zahl 1; Komplexe Zahl 2,…)
Beispiel:
(a + bi) – (c + di) = (a – c) + (b – d)i
=IMSUB("6 + 3i"; "5 + i") ergibt 1 + 2i
Liefert das Produkt komplexer Zahlen.
Syntax:
=IMPRODUCT(inumber 1; inumber 2,...)
=IMPRODUKT(Komplexe Zahl 1; Komplexe Zahl 2,…)
Beispiel:
(a + bi)(c + di) = (ac – bd) + (ad + bc)i
=IMPRODUCT("3 + 4i")("5 – 3i") = 15 – 9i + 20i – 12 i2
= 15 + 11i +12
= 27 + 11i
=IMPRODUCT("5 + 3i")("3 – 2i") = 15 + 9i – 10i – 6i2
= 15 – i +6
= 21 – i
Liefert den Quotienten zweier komplexer Zahlen.
Syntax:
=IMDIV(inumber 1; inumber2)
=IMDIV(Komplexe Zahl 1; Komplexe Zahl 2)
Komplexe Zahl 1 = der komplexe Zähler oder der Dividend.
Komplexe Zahl 2 = der komplexe Nenner oder Divisor.
Beispiel:
IMDIV (z1, z2) =
Liefert die Quadratwurzel einer komplexen Zahl.
Syntax:
=IMSQRT(inumber)
=IMWURZEL(Komplexe Zahl)
Beispiel:
=IMSQRT("6 + 3i") ergibt 2,520734 + 0,595065i
Gibt das Argument, ausgedrückt im Winkel Theta, als Bogenmaß an.
Syntax:
=IMARGUMENT(inumber)
=IMARGUMENT(Komplexe Zahl)
Beispiel:
IMARGUMENT (z) =Theta
=IMARGUMENT("10 + 4i") ergibt 0,380506 (Rad).
Grad =
=21,8 º
Imsin liefert den Sinus, die Funktion Imcos den Kosinus einer komplexen Zahl.
Syntax:
=IMSIN(inumber) =IMSIN(Komplexe Zahl)
=IMCOS(inumber) =IMCOS(Komplexe Zahl)
Beispiel:
=IMSIN("3 + 2i") ergibt 0,530921 – 3,590565i
=IMCOS("5 + 2i") ergibt 1,067193 + 3,477884i
Potenziert eine komplexe Zahl.
Syntax:
=IMPOWER(inumber; number)
=IMAPOTENZ(Komplexe Zahl; Potenz)
Die Potenz kann eine ganze Zahl sein, eine rationale Zahl (Bruchzahl) oder negativ sein.
Diese Funktion liefert die algebraische Form einer in exponentieller Form vorliegenden komplexen Zahl.
Syntax:
=IMEXP(inumber)
=IMEXP(Komplexe Zahl)
Beispiel:
Für eine in exponentieller Schreibweise vorliegende komplexe Zahl gilt wegen der Eulerschen Formel:
IMEXP (z) =
=IMEXP("1 + 5i") ergibt 0,771074 – 2,606626i
Imln liefert den natürlichen Logarithmus, Imlog2 den Logarithmus zur Basis 2, Imlog10 den Logarithmus zur Basis 10, einer komplexen Zahl.
Syntax:
=IMLN(inumber) =IMLN(Komplexe Zahl)
=IMLOG2(inumber) =IMLOG2(Komplexe Zahl)
=IMLOG10(inumber) =IMLOG10(Komplexe Zahl)
Excel XP Hilfe
„Excel, Grundlagen Tabellenkalkulation“, Herdt-Verlag, 3.Auflage Jänner 2000
„Excel, Fortgeschrittene Anwendungen“, Herdt-Verlag, 3.Auflage Februar 2000
http://iwi.uibk.ac.at/ftp/Jarz/
http://home.pages.at/cramer/excel/start.htm
http://www.gm.fh-koeln.de/~wwwfb19/boehm/public/download.htm
http://www.wipnet.at/db/db287.asp
[1] Annuität: Ist ein Tilgungsanteil, der sich aus den Zinsen und dem konstanten Betrag zusammensetzt, der in einer Periode zu entrichten ist.