Daten-Abfrage dynamisieren und automatisieren

[mhryvo]

Vielen Dank auch dafür!
Ich glaube dass ist vielleicht doch der Weg, den ich gehen sollte.

Denn das Ziel ist folgendes: Auch wenn ich erstmal nur testen wollte immer die gleiche Spalte (2016) zu importieren, ist das nicht immer zweckmäßig oder ausreichend.
Daher brauche ich eine Möglichkeit um mehrere Jahresdaten zu kombinieren. Sodass man 5 Jahres Durchschnitte bilden kann oder Steigerungsraten ermitteln kann.
Also die Frage: Wie setzt man sowas um?
Aus der Tabelle, wie du sie formatierst hast, wären ja alle Daten enthalten. Ich kenne mich da nicht so aus, aber nutzt man dafür das Datenmodell bei Excel Queries? Also das man die Daten immer irgendwie vorhält, wenn sie gebraucht werden? Wobei man sie ja nicht immer braucht. Sondern ein mal zur Berechnung.

Also aussehen soll das dann irgendwie so (vereinfacht):
[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Wie formatiere ich das nun um? Und ändert man die Firmennamen auf einheitliche Namen?

Achso übrigens: Das Errors gelöscht werden geht nicht. Also zumindest bei dem Vorhaben was ich hatte, in dem man die Spalten aneinander fügt, weil dann würden ja manche Zeilen wegfallen und dann wären nicht mehr die richtigen Zeilen auf der richtigen "Höhe", sodass Werte von einem Unternehmen bei denen dadrüber stehen würden, weil Errors dazwischen gelöscht wurden. Aber mein Vorhaben scheint mir sowieso nicht allzu sinnvoll zu sein.

[shift-del]

Mit der bereinigten Tabelle kannst du in Excel dann (Pivot-)Auswertungen machen.

Mit Datenmodell oder ohne? Dazu gibt es ein Video von Mike Girvin in dem er die Vor- und Nachteile diskutiert. Wenn du PowerPivot-Auswertungen (DAX-Measures) benötigst dann geht das nur mit dem Datenmodell.

Bei meiner Vorgehensweise ist es kein Problem die ERROR-Zeile zu löschen.

Die Firmennamen würde ich bereits in der Quelldatei vereinheitlichen.

[mhryvo]

Ah ok. 
Ja Pivot Auswertungen hatte ich auch schonmal versucht, allerdings hatten die in meinem Quellformat nie das ermöglicht was ich wollte.
Aber wie funktioniert das jetzt in meinem Fall konkret?

Es soll ja letztendlich eine Art Datenbank werden, wo zu jedem Unternehmen zahlreiche Informationen(immerhin habe ich da 50 Datenreihen vor mir :D) und Kennzahlen gebündelt und ausgewertet werden.
Also sinngemäß brauche ich eine Funktion/Formel, die Daten variabel auswertet. z.B. Durchschnitt("Typ", "Startjahr", "Endjahr"), sodass man dann in der 5 Jahres EPS Durschnitt - Spalte dann letztendlich erhält Durschnitt(EPS,2012, 2017) und bei einer 10 Jahres-DPS Durchschnitt - Spalte: Durchschnitt(DPS,2007,2017)
Das ist jetzt wirklich nur sinngemäß gemeint, also es geht nicht um die Umsetzung mit einer eigenen VBA-Funktion.

PS: Werden bei PowerPivot nicht automatisch solche Gesamtergebnis gebildet, die ich ja nicht brauche? Und viele Möglichkeiten zur Berechnung habe ich da auch nicht gesehen, also nur so vorgegebene Funktionen wie Durchschnitte, die dann aber alle Datenauswerten und nicht nur die, die man für die einzelne Kennzahl braucht.

[shift-del]

Ja Pivot Auswertungen hatte ich auch schonmal versucht, allerdings hatten die in meinem Quellformat nie das ermöglicht was ich wollte.

Du hast ja wohl das Kreuztabellenformat belassen. Damit bekommt man keine Pivot-Auswertung hin. Man benötigt immer eine Datensatzliste. Und die erhältst du wenn du wie oben beschrieben vorgehst.

Mit DAX-Measures habe ich noch keine Erfahrungen aber Videos zeigen dass man Formeln konstruieren kann die innerhalb einer Pivot-Tabelle dann auf Zeilen- und Filterkontext reagieren.
Möglicherweise kann man auch Measures entwickeln die direkt einen 5-Jahres-Durchschnitt ermitteln.

[mhryvo]

Ja genau, deine Beschreibung habe ich durchgeführt. 
Aber ich glaube nicht so ganz daran, dass PowerPivot der richtige Weg dafür ist.
Denn es wird auch dazu kommen, dass bei einigen Kennzahlen, die berechnet werden sollen, der aktuelle Wert(2016) von EPS dividiert wird mit dem Wert von DPS oder ähnlichem. Und ich sehe bisher keine Möglichkeit das halbwegs bequem hinzukriegen(bei entsprechenden YoutubeVideos mussten immer "Hilfs"Spalten erstellt werden für nahezu allesmögliche) und damit meine ich nicht dass es nicht leicht ist, sondern dass es bei der Berechnung und Auswertung von dutzenden Kennzahlen nicht effektiv ist. Da sehe ich das ganze bei Powerquery schon eher, dort kann man ganz einfach Durchschnitte in einer Spalte hinzufügen und quasi mit den Jahren "rechnen".

[Rabe]

Hi,
Du kannst Dir Auswertungsformeln und -tabellen 'basteln', die auf die Pivot zugreifen. Dort kannst Du dann genau die Formel erstellen, die Du haben willst und immer dann anstelle der Zahl auf die entsprechende Zelle der Pivot klicken.
Dort steht dann etwas wie "Pivotdaten.zuordnen" und die absoluten Bezüge (wie: Firma=Müller), da kannst Du dann die Zelle angeben, in der der tatsächlich gesuchte Name steht. Wenn der sich ändert, ändert sich auch das Ergebnis der Formel. Diese Formel kann dann gezogen werden.

[mhryvo]

Ah ok,
gibts dazu irgendwie nen Video oder so?
Ich kann mir noch nicht genau vorstellen wie du das meinst ;(

Also wo soll ich die Auswertungstabellen erstellen? Im PivotTool? Oder ganz normale Excel-Tabelle? Und Auswertungsformeln mit VBA erstellen oder wie meinst du das?
Ich sehe noch nicht ganz den Vorteil den eine PivotTabelle bringt. Denn ich will ja nicht einfach nur Gesamtsumme für die einzelnen Jahre oder so, sondern im Grunde brauche ich die Quelldaten als solche nur um sie in die Formeln zu speisen.

[Rabe]

Hi,

ich habe Dir Deine Dateien mal umgebaut:

1. In den einzelnen Blättern der Quelldatei die Spalte A aufgesplittet in den Aktiennamen und die Art des Wertes.
   
2. Dann habe ich die Namen in den drei Blättern vereinheitlicht.
   
3. Anschließend alle Daten mit der zusätzlichen Spalte auf ein Sammelblatt zusammengeholt.
   
4. Dann daraus eine Pivot erstellt.
   
5. nun kannst Du in Deinem Auswertungsblatt die Formeln reinschreiben und an den Stellen, aus denen Du Werte aus den Quelldaten brauchst, jeweils auf den Wert in der Pivot klicken.
   

Dann kommt so eine Formel raus:
=PIVOTDATENZUORDNEN("2016";Pivot!$A$3;"Name";"11 88 0 SOLUTIONS AG";"Art";"DIV.PER SHR.")

Wenn Du nun anstelle von: "11 88 0 SOLUTIONS AG"
auf den Namen der Aktie in Deiner Auswertung klickst, hast Du die Dividende der jeweiligen Aktie in der Tabelle stehen:
=PIVOTDATENZUORDNEN("2016";Pivot!$A$3;"Name";[@Aktie];"Art";"DIV.PER SHR.")

Dies kannst Du mit allen Deinen (auch komplizierten) Formeln so machen, daß Du Dich dann immer auf den Namen pro Zeile beziehst.

Da Du überhaupt keine Formeln oder Werte in Deiner Auswertungstabelle stehen hast, kann ich nichts Genaueres sagen.
  Quelldatei - Rabe.xlsx (Größe: 47,37 KB / Downloads: 2)

[edit]
Wenn die Datei so aufgebaut ist, wie Detlef (shift-del) gezeigt hat, ist das natürlich noch viel universeller.

[Rabe]

Hi Detlef,

Z.B. die Spalte A splitten, NA durch null ersetzen, die Jahres-Spalten entpivotisieren, die Fehlerzeile entfernen, Überschriften ändern.

wie kann ich die Jahresspalten ent-pivotisieren?

[mhryvo]

Ah super, das meintest du also mit "PIVOTDATENZUORDNEN" :D
Vielen Dank, dass scheint ja so sehr gut zu funktionieren.


Trotzdem noch eine Frage: Kann man die Funktionen irgendwie umbennen oder verkürzen oder so? Weil ich zum Teil komplexe WENN funktionen brauche oder halt einfach die SUMME von 10 Jahreswerten rechnen muss und dann werden die Formeln unübersichtlich lang und sehr fehleranfällig. Wenn ich es richtig verstanden habe, kann ich ja nur auf ein einzelnen Wert zurückgreifen, also nicht =SUMME(von 2007-2017) rechnen? Das wird nämlich oft notwendig sein, um das zu bearbeiten.

Geht es da vielleicht einfacher, die Formeln, die dann in der Auswertungstabelle sind, mit VBA irgendwie umzusetzen bzw. einzusetzen?

Und wie kann ich die Namen am besten vereinheitlichen? Einfach die Namen spalten in der Quelldatei überschreiben ginge nicht, weil da ja auch immer der Typ mit drin steht.

PS: Kennt ihr nen Trick wie man komplexe Formeln besser erstellen?

Bezüglich der Jahresspalten, einfach im Abfrage Editor alle Spalten markieren und dann auf 'Spalten entpivotisieren' klicken. shift-del hat die Tabellenblätter ja über eine Abfrage importiert und dann in die PivotTable, und nicht in die Auswertungsdatei eingefügt.