VBA-Beispiele > Excel: > Datenanalyse

Tipp 221: Allgemein

Trotz der Vielzahl von Formeln und Funktionen reichen diese nicht aus aus, um umfangreiche Datenanalysen zu schaffen, oder man erreicht dieses nur über komplizierte Umwege. Excel bietet daher verschiedene Möglichkeiten zur Datenanalyse:

  1. Szenario-Manager: Der Szenario-Manager entwickelt für Prognosewerte einzelne Protokolle mit deren Auswirkung auf das Gesamtergebnis.
  2. Zielwertsuche: Findet den Wert, der benötigt wird, um zu einem bestimmten Ergebnis zu kommen.
  3. Solver: Der Solver ermittelt Näherungswerte für gewisse Zustände, deren Faktoren selbst definiert werden können.
  4. Pivot-Tabellen: Mit Hilfe von Pivot-Tabellen kann man unterschiedliche Beziehungen der Tabellendaten analysieren.

[Einzellink]

Tipp 222: Teilergebnisse

(Die Funktion Teilergebnisse findet man im Menü Daten)

Oft steht man vor der Aufgabe, Ergebnisse bestimmter Datengruppen zu ermitteln, zum Beispiel, um den Umsatz verschiedener Filialen zu ermitteln.

Nehmen wir an, wir haben eine Tabelle mit Überschrift. In Spalte A steht die Filiale, in Spalte B das Datum und in Spalte C der Umsatz. Um nun schnell an die entsprechenden Zahlen zu kommen, gehen wir wie folgt vor:

  1. Wir sortieren die Tabelle nach dem Kriterium, für das wir unsere Teilergebnisse erhalten wollen (in diesem Fall Filiale).
  2. Wir gehen auf die Funktion Teilergebnisse. Es erscheint ein Dialogfeld, in dem 3 Listen zur Verfügung stehen:
Gruppieren nach: Hier wird das Kriterium markiert, nach dem die Teilergebnnisse erstellt werden sollen (Filiale).
Unter Verwendung von: In dieser Liste stehen neun Berechnungsarten zur Verfügung (Summe).
Bezogen auf: Die Spalte, deren Zellwerte für das Teilergebnis benötigt werden (Umsatz).

Mit der Funktion Teilergebnisse wird die Tabelle gleichzeitig gegliedert. Dadurch bietet sich für den Anwender ein besserer Überblick über die einzelnen Ergebnisse.

Achtung: Wird die Tabelle vorher nicht sortiert, wird zu jedem zeilenmäßigen Unterschied ein Teilergebnis erzeugt.

[Einzellink]

Tipp 223: Konsolidierung

(Die Funktion Konsolidierung findet man im Menü Daten)

Die Konsolidierung von Daten funktioniert im Grunde ähnlich der Teilergebnisse, nur kann man im Gegensatz dazu mehrere Tabellen zusammenführen, wobei die zu konsolidierenden Daten unsortiert auf mehrere Tabellenblätter unterschiedlicher Arbeitsmappen verteilt sein können.

Man nehme an, man habe Tabellen einzelner Filialen, die Daten zu Produkt und Umsatz enthalten. Dabei ist es egal, ob ein Produkt in einer Filiale nicht erscheint, da es nicht verkauft worden ist.

Nun wählt man ein neues Tabelleblatt aus und geht auf Daten/Konsolidierung. Es erscheint ein Dialogfeld mit folgenden Auswahlmöglichkeiten:

Funktion: Auch hier stehen wieder neun Berechnungsarten zur Verfügung, für das angeführte Beispiel wird Summe gewählt.
Bezug: Hier werden die zu konsolidierenden Tabellenbereiche eingegeben und mit Hinzufügenin die Liste der zu konsolidierenden Tabellen übernommen (man sollte nur von einem Bereich die Überschrift übernehmen und bei allen anderen nur die Daten). Für unser Beispiel werden in den Tabellen die Spalten von Produkt und Umsatz markiert.
Beschriftung aus: Mit Beschriftung sind hier nicht die Überschriften (Produkt und Umsatz) gemeint, sondern die Beschriftung der zu konsolidierenden Daten. In unserem Beispiel wären dies die Produktnamen. Man muss daher unbedingt die entsprechende Spalte im Bezug mit angeben.
Verknüpfung mit Quelldaten: Mit Hilfe dieser Funktion kann man eine Verknüpfung zu den Quelldaten erstellen und somit eine automatsiche Aktualisierung realisieren. Allerdings macht dieses nur Sinn, wenn sich nur die eigentlichen zu konsolidierenden Werte (hier der Umsatz) ändern und der rest im Ursprung erhalten bleibt, ansonsten muss die Konsolidierung erneut durchgeführt werden.

[Einzellink]

Tipp 224: Zielwertsuche

(Die Zielwertsuche findet man im Menü Extras)

Mit Hilfe der Zielwertsuche kann man den Wert finden, der benötigt wird, um zu einem festgelegten Ergebnis zu kommen. Diese Funktion wird häufig verwendet, um Laufzeiten, Zinseszinsen und andere Prognosen zu ermitteln.

Zum besseren Verständnis wird hier jedoch auf ein einfacheres Beispiel zurückgegriffen und zwar auf eine Break-Even-Analyse. Diese Analyse wird benötigt, um die Absatzmenge eines Produktes zu ermitteln, bei der der Gewinn nach Abzug der Kosten gleich Null ist.

Folgende Daten werden dazu benötigt:

  • Produktpreis (Annahme, das Produkt würde 12,00 € kosten, also A1 = 12,00 €)
  • Absatzmenge (B1)
  • Umsatz = Produktpreis * Absatzmenge (C1 = A1 * B1)
  • Fixkosten (Annahme, die Fixkosten betragen 50.000,- €, also D1 = 50.000,00 €)
  • variable Stückkosten (Annahme, die variablen Stückkosten betragen 8,00 €, E1 = 8,00 €)
  • Gesamtkosten = Fixkosten + (variable Stückkosten * Absatzmenge) (F1 = D1 + E1 * B1)
  • Gewinn = Umsatz - Gesamtkosten (G1 = C1 - F1)

Die einzige Zelle die keinen Wert enthält, ist die Zelle B1 (Absatz).

Um nun die Break-Even-Analyse durchzuführen, wählt man unter dem Menü Extras die Zielwertsuche.

Es erscheint ein Dialogfeld mit 3 Eingabefeldern:

Zielzelle:
Die Zielzelle ist die Zelle, für die der Zielwert ermittelt werden soll, also G1 (Gewinn).
Zielwert:
Hier wird der Wert eingetragen, den die Zielzelle annehmen soll, für die Break-Even-Analyse Null.
Veränderbare Zelle:
Hier wird die Zelle markiert, deren Wert variabel ist, so dass der Zielwert erreicht wird.

Bestätigt man nun mit OK, wird die Lösung gesucht und es erscheint ein Dialogfeld Status der Zielwertsuche. Da Excel die Lösung Iterationen findet, kann man diesen Prozess mit Pause unterbrechen und mit dem nun zur Verfügung stehenden Button Schritt die einzelnen Iterationsschritte anschauen.

Ist eine Lösung gefunden, so kann man diese mit OK direkt übernehmen, sonst muss man Abbrechen wählen.

[Einzellink]

Tipp 225: Mehrfachoperationen

Eine Mehrfachoperation kann mit ein oder zwei Variablen durchgeführt werden, es wird dabei die Berechnung für eine Reihe von Werten automatisiert.

Beispiel für eine Mehrfachopration:

Um die Ausschüttungssumme einer Anlage abhängig vom Zinsatz und der Laufzeit zu berechnen, benutzt man die Formel Anlagevermögen * ((1 + Zinsatz) ^ Laufzeit). Hat man nun verschiedene Zinssätze, so würde man normalerweise den Bezug zur Laufzeit absolut setzen und die Formel entsprechend kopieren. Allerdings kann man das Ganze auch mit Hilfe der Mehrfachoperation lösen.

  • A1 = Anlagevermögen
  • A2 = Laufzeit
  • A3 = Zinsatz
  • A4 = Ausschüttung = A1 * ((1 + A3) ^ A2)

In den Zellen C2 bis C10 trägt man nun die verschiedenen Zinssätze ein, und in Zelle D1 trägt man nochmal die gleiche Formel für die Ausschüttung ein, wie sie in Zelle A4 steht. Nun markiert man den Bereich C2:D10 und wählt Daten/Mehrfachoperation. Es erscheint ein Dialogfeld mit 2 Feldern, in der man die veränderbare Variable, also den Zinsatz (A3), einträgt:

Werte aus Zeile: Würden usnere verschiedenen Zinssätze in einer Zeile stehen, z. B. C2:K2, so müsste man hier die Variable eintragen.
Werte aus Spalte: Veränderbare Werte sind in einer Spalte angeordnet.

Da unsere Werte spaltenmäßig eingetragen sind, schreiben wir in Werte aus Spalte $A$3. Das Ergebnis für die verschiedenen Zinssätze erscheint in unserer Matrix C2:D10.

Wenn man nun auch noch die Laufzeit variabel gestalten will, so trägt man in die Zellen D1:I1 verschiedene Laufzeiten ein. Die Formel aus dem obigen Beispiel bleibt bestehen, nur muß sie in der Schnittstelle der beiden Wertereihen Zinssätze (C2:C10) und Laufzeiten (D1:I1) stehen, also in Zelle C1. Nach dem Markieren des Bereichs D1:I10 geht man wieder auf Daten/Mehrfachoperation und trägt nun in das Feld Werte aus Zeile die Zelle A2 für die Laufzeit und in das Feld Werte aus Spalte die Zelle A3 für den Zinssatz ein. Das Ergebnis erscheint wieder in der Matrix C1:I10.

Der Vorteil der Mehrfachoperation gegenüber der alternativen Methode liegt darin: Muss die Formel geändert werden, so braucht man dies nur einmal tun, die neue Formel wird dann sofort übernommen. Sie soll auch weniger Speicherplatz benötigen, als wenn in jeder Zelle eine Formel stünde, jedoch kann ich dies bisher nicht bestätigen.

Autor: Holger Ostlender

[Einzellink]

 

Das Chemtrailhandbuch von Jörg Lorenz mit einem Vorwort von Jörg Kachelmann

Susanne Hammann-Sayilir - Modedesign  Modeillustration