Daten-Abfrage dynamisieren und automatisieren

[shift-del]

wie kann ich die Jahresspalten ent-pivotisieren?

Unter Transformieren -> Beliebige Spalte oder Rechtsklick auf eine oder mehrere Spalten.

[mhryvo]

Kann man irgendwie die PIVOTDATENZUORDNEN Funktionen verkürzen? oder umbennen oder so? Summen bilden geht ja scheinbar auch nicht, so dass ich 10 mal hintereinander die gleiche Funktionen aufrufen muss, um dann ein 10 Jahres Durchschnitt zu bilden, oder? Oder kann man sich das irgendwie mit VBA vereinfachen, sodass VBA dann das ganze in die große Funktion einsetzt? An sich ist das eine tolle Funktion, aber dadurch, dass man immer nur einzelne Daten mit einer langen Formel aufruft, dann wird es halt schwierig daraus die eh schon komplexen Wenn Formeln zu basteln, die ich brauche.

Und wie trennt man die Namen so, dass wirklich nur der Typ/die Art in der zweiten Spalte ist. Denn manchmal haben auch ein " - " im Namen, sodass dann dort getrennt wird, und dann ein Teil des Namen in der Spalte "Typ" enthalten ist.

[Rabe]

Hi,

Kann man irgendwie die PIVOTDATENZUORDNEN Funktionen verkürzen? oder umbennen oder so? Summen bilden geht ja scheinbar auch nicht, so dass ich 10 mal hintereinander die gleiche Funktionen aufrufen muss, um dann ein 10 Jahres Durchschnitt zu bilden, oder? Oder kann man sich das irgendwie mit VBA vereinfachen, sodass VBA dann das ganze in die große Funktion einsetzt? An sich ist das eine tolle Funktion, aber dadurch, dass man immer nur einzelne Daten mit einer langen Formel aufruft, dann wird es halt schwierig daraus die eh schon komplexen Wenn Formeln zu basteln, die ich brauche.

Und wie trennt man die Namen so, dass wirklich nur der Typ/die Art in der zweiten Spalte ist. Denn manchmal haben auch ein " - " im Namen, sodass dann dort getrennt wird, und dann ein Teil des Namen in der Spalte "Typ" enthalten ist.

der Begriff PIVOTDATENZUORDNEN ist ein feststehender Excel-Begriff und kann nicht verkürzt oder geändert werden.
Das Summenbilden geht sicher über Matrix-Formeln oder auch Hilfsspalten oder die normalen Excel-Formeln. In Deinen ellenlangen Formeln verwendest Du Bezüge auf einzelne Zellen, diese werden durch die PIVOTDATENZUORDNEN ersetzt und dadurch verallgemeinert.

Den jeweiligen Formelteil der PIVOTDATENZUORDNEN kannst Du dann ja auch mittels Copy und Paste einfügen.
Auch wenn Du die ellenlangen Formeln mittels VBA einsetzt, sind sie trotzdem ellenlang.

Den 10-Jahresdurchschnitt errechnest Du doch hoffentlich nicht durch Addition von den 10 Jahreswerten mit vielen WENNs geteilt durch 10?
Die WENN-Formeln können sicher auch vereinfacht werden von den Formel-Experten.
Aber ohne Deine Formeln zu kennen, kann Dir hier sicher niemand genauer helfen.

Namen-Trennung:
am einfachsten machst Du das manuell. Das muß ja auch nur ein Mal pro Name gemacht werden und die sind dann für alle 50 Blätter gleich.

[mhryvo]

die 10 Jahres Durchschnitte benötigen an sich keine WENN Formel, allerdings sind nicht alle Datenreihen gleich, weshalb ich WENN Formeln benötige, um zu prüfen ob bereits aktuelle Daten für 2017, 2016, 2015 vorliegen. Und je nachdem, für welches Jahr die neuesten Daten vorliegen, brauche ich eine andere Formel zur Berechnung der Durchschnitte. Dort wird dann geprüft, ob "NA" im Jahr 2017 steht, wenn ja, dann wird geprüft für 2016 und wenn da auch "NA" steht, also keine Daten vorhanden sind, dann wird 2015 genommen. Dafür brauche ich die WENN-Formeln.


Kann einer von den Formel-Experten eventuell zeigen, wie man Summen von 10 Jahreswerten berechnet, ohne 10 mal PIVOTDATENZUORDNEN() zu schreiben?

[Rabe]

Hi,

dann lege doch endlich einfach mal eine Auswertungsdatei vor, in der Deine Formeln stehen oder stelle die relevanten Ausschnitte und die Makro-Codes hier dar, siehe die als Wichtige Themen: markierten Beiträge im Forum "Nutzungs- und Bedienungshinweise" bei "Forumstechnik".

• Eine Beispieldatei muss vom Aufbau her zwingend dem Original gleichen, sonst wird's mit gezielter Hilfe i. d. R. nichts oder erst nach zig Nachfragen
  
• Meistens reichen 10-15 Datensätze
  
• Sensible Daten sollen anonymisiert werden
  
• Bei einigen Datensätzen sollte unbedingt händisch das Wunschergebnis eingetragen sein
  
• Und last but not least ist es wichtig, im Post zugeschnitten auf die Beispieldatei eine ordentliche Problembeschreibung abzugeben.
  

Die farbigen Texte sind anklickbare Links:

Hier steht, wie es geht:
Beitrag 1 WICHTIG: Arbeitsmappen zur Verfügung stellen
Beitrag 2 WICHTIG: Tabellenausschnitte und VBA-Codes im Forum einstellen

Eine Bitte:
Anstatt Screenshots ist eine Datei oder ein Ausschnitt besser!
"Du gehst ja auch nicht in die Werkstatt und gibst ein Foto Deines kaputten Autos ab!"

[schauan]

Hallo Ralf,

Den 10-Jahresdurchschnitt errechnest Du doch hoffentlich nicht durch Addition von den 10 Jahreswerten mit vielen WENNs geteilt durch 10?

Wieso nicht? Wenn ich voriges Jahr 100 hatte und dieses Jahr 50, ist mein 2-Jahres-Durchschnitt 75 oder sehe ich da was falsch?

@mhryvo,

schaue Dir z.B. mal die Funktionen MITTELWERTWENN und MITTELWERTWENNS an, da kannst Du den Mittelwert mit einer oder mehreren Bedingungen errechnen.

[Rabe]

Hi,

schaue Dir z.B. mal die Funktionen MITTELWERTWENN und MITTELWERTWENNS an, da kannst Du den Mittelwert mit einer oder mehreren Bedingungen errechnen.

eben, nicht durch Addition vieler Wenn-Verschachtelungen und Division durch 10!

[mhryvo]

Sorry für das späte Antworten, ich war ein paar Tage verhindert.

Ich habe mal eine Beispieldatei mit den Daten und den Formeln hochgeladen.
Die Wenn Funktion brauche ich nur, damit je nach dem, für welches Jahr aktuelle Daten vorliegen, auch das aktuelle Jahr verwendet wird. Aber dies macht die Formeln sehr unübersichtlich und auch nicht flexibel, weshalb ich dort eure Hilfe brauche. Ich habe in den jeweiligen Spalten auch beschrieben, wozu die Formeln da sind, bzw. was sie berechnen sollen.

Ziel ist es, dass die Formeln mit PIVOTDATENZUORDNEN() funktionieren und auch automatisch das aktuelle Jahr als Ausgangsjahr für die Berechnung benutzt werden bzw. sich die 10 Jahres Durchschnitte sich automatisch verschieben, wenn ein neues Jahr mit Daten(!) dazu kommt.

Es geht halt nicht immer um Durchschnitte, sondern auch andere komplexe Formeln, die aber wirklich noch nicht optimal sind. So sollen z.b. die ersten beiden Formeln zählen, wieviele aufeinander folgende Jahre(von heute aus) mit gesteigerten Dividenden oder konstanten Dividenden es gibt. Da bei müssen halt durch die Wenn Formeln die Zellbezüge geändert werden, wenn ein neues Jahr mit Daten vorliegt. Bei der 2. Spalte zählt die Formel leider alle aufeinander folgenden Jahre wo die Dividende nicht gesenkt wurde, allerdings auch Jahre mit Werten von 0 - 0 - 0 - 0, das bedeutet jedoch dass keine Dividende gezahlt wurde. Dies sollte eigentlich ausgeschlossen werden.

Vielen Dank für die Hilfe bisher! Ich denke ich schaffe das Projekt zu beenden, wenn die Formeln nun noch optimiert werden bzw. mit der Pivottabelle kompatibel werden.

Lassen sich die Daten wo die Formeln dann drine stehen, eigentlich später auch komplex filtern? sodass man für jede Spalte spezielle Bedingungen stellt, sodass man so die Unternehmen mit guter Bewertung / guten Kennzahlen filtert? Also so, dass man den Filter auslagern kann und nicht direkt in der AutoFilter-Spalte eingeben muss?

[mhryvo]

sorry für den push

aber könnten sich das die Formel-Profis hier mal ansehen? ;D

Danke nochmal für all die Hilfe!