Office: Mehrere Sheetsinhalte in eine Auswertungstabelle (Datei!) zur Berechnung importieren.

Hallo Anton,


also die Sortierung habe ich soweit (manuel!) hinbekommen. Zwar das Gesamtergebnis in der Pivottabelle lässt sich keinerlei filtern aber manuel lässt sich wenigestens sortieren.


naja.. ich habe jetzt in kombination zu deiner Mittelwertberechnung versucht, die Tendenzberechnung rein zu bringen aber da bekomme ich immerwieder fehler!?!


also ich muss immer die Tendenz der Platzierung vom letzten Jahr (bspl. hier 2011) zu den vergangenen 3 Jahren berechnen und mit farbige pfeilen ausgeben. also die platz 1er müssen immer oben und mit nem grünen pfeil augegeben werden.


Es soll also auch wie die Durchscnittsberechnung natürlich dynamisch sein.


So habe ich versucht deine Durchschnittsberechnung einfach mal in der Hinsicht zu missbrauchen, jedoch lande ich immer wieder an der sackgasse :-(

Was habe ich denn hier falsch geschrieben??:
=WENN(BEREICH.VERSCHIEBEN(D7;0;$S$3-$S$4;1;$S$4)>1,1*WENN(ISTFEHL(MITTELWERTA(BEREICH.VERSCHIEBEN(D7;0;$S$3-$S$4;1;$S$4)));0;MITTELWERT(BEREICH.VERSCHIEBEN(D7;0;$S$3-$S$4;1;$S$4)));"");"h";WENN(BEREICH.VERSCHIEBEN(D7;0;$S$3-$S$4;1;$S$4)<0,9*WENN(ISTFEHL(MITTELWERTA(BEREICH.VERSCHIEBEN(D7;0;$S$3-$S$4;1;$S$4)));0;MITTELWERT(BEREICH.VERSCHIEBEN(D7;0;$S$3-$S$4;1;$S$4)));"");"i";"n"))

die angepasste datei lege ich bei :-)
Den Anhang Auswertung_mit_Tendenz.xls betrachten


viele liebe grüße
Armin

 

Hallo Armin,

in der beiliegenden Excel-Datei habe ich versucht, Deine Formel umzusetzen und zu vereinfachen.
1) Ich habe im Arbeitsblatt "Auswertung" 3 benannte Bereiche definiert ("BisJahr" in S2, "SpNr" in S3, "SpAnz" in S4), um die Lesbarkeit zu verbessern.
2) Ich habe ab Zeile 26 dann einige Methoden dargestellt, wie man solchen Monsterformeln zu Leibe rückt und das Fehlerrisiko minimiert. Denn in einer langen Excel-Formel den Fehler aufzustöbern, ist eine mühselige Arbeit.
3) Sonst, so hoffe ich, habe ich an Deiner Formel zur Tendenzberechnung nichts verändert.
4) Die neuen Formeln befinden sich in U7:U16 zusammen mit den bedingten Formatierungen.

 

Hallo Anton!!

zunächst würde ich dich sehr gerne auch persönlich kennen lernen und mich bei dir einfach RECHT HERZLICH BEDANKEN!!!

mir fehlen dir Worte wie ich mich sonst bei dir bedanken kann :-)

Ich werde nun das ganze mit den Echtdaten umsetzen und gleich am Montag mein Chef vorlegen!!

Ich wünsche dir ein super restliche und hoffentlich sonniges Wochenende!

Es ist unglaublich beruhigend heute Abend, wenn ich daran denke, dass ich das ganze am Montag vom Tisch habe ;-)

Besten herzlichen Dank!!

viele liebe Grüße

Armin :-)

 

@Administrator: Bitte 100000xxxx Sterne Exl121150 geben!!!

Dankeschön :-)

 

Hallo Anton :-)

zuerst wünsche ich dir ein gesundes und erfolgreiches neues Jahr 2014!

Alle Jahre wieder... es sind nun fast zwei Jahre vergangen als ich hier meine Problematik geschildert habe und du mir eine TOP-Lösung gegeben hast.

Nun habe ich (bzw. meinen Chef) folgende Thematik, welche ich nicht trotz stunden lange Bemühungen nicht lösen kann.

Folgende Situation:

In der vergangenen Zeit haben sich die "Zuordnungsnummern" der Firmen geändert. Spricht, wenn z.B. die Firma Söntheim und söhne GmbH im Jahr 2011 die Zuordnungsort Nr. 851 gehabt hat besitzt nun seit 2012 die Zuordnungsort Nr. 856.

Dies führt dazu, dass in PivotTabelle zwei Zeilen automatisch in Sheet "Auswertung" erzeugt werden und somit die restlichen Berechnungen u.a. auch die Tendenzberechnungen durcheinander kommen und nicht mehr stimmen.

Die "Firmen Nr." bleibt jedoch immer gleich!

Frage: wäre es möglich, dass man sagt, es soll die letzte und aktuellste Zuordnungsnummer von vorjahr mit den restlichen Zuordnungsnummer von den vergangenen Jahren verglichen werden und als Ausgabe soll nur die aktuellste Zuordnungsnummer (indem Fall 856) angezeigt wird? oder am einfachsten: es soll nur die letzte Zuordnungsnummer nämlich von Vorjahr angezeigt werden?

Vielen lieben Dank schon mal im Voraus für deine Mühe! :-)

Gruß

Armin

 

http://www.office-hilfe.com/support/newreply.php?do=newreply&p=134440

Hallo Anton,

ich habe folgendes festgestellt und hoffe, dass du mir dabei helfen kannst:

1. die RDs also die Zuordnungsnummern lassen sich nicht zusammenfassen bzw. ich bekomme es einfach nicht hin in der Auswertungstabelle nur die aktuelle Zahl von Vorjahr anzeigen zu lassen. So wie ich's sehe muss ich wohl auf die Zuordnungsnummern komplett verzichten..!?! ist das so??

2. Bei der Tendenzberechnung vermute ich, dass dort an der Stelle "h" "i" rein muss bzw. umgekehrt, damit die Pfeilrichtungen richtig zeigen aber sicher bin ich mir nicht!?!

Danke für deine Hilfe im Voraus! :-)

Gruß

Armin

PS: Bspl. Datei habe ich angehängt!
Den Anhang Auswertung_Pivot.zip betrachten

 

Hallo Armin,

Achtung: Abänderung:
1) In Zelle F6 der Spalte "ZuNr_Neu" des Arbeitsblattes "Datenbank" kannst Du folgende Formel eingeben:
{=INDEX($B$6:$B$65536;MAX(ZEILE($E$6:$E$65536)*($D$6:$D$65536=D6))-5)}
Achtung: Die geschweiften Klammern dieser Formeln weisen darauf hin, dass es sich um eine Matrixformel handelt: diese Klammern sind nicht einzugeben, sondern die Formeleingabe in der Formeleditierzeile ist mit der Tastenkombination Umschalt+Strg+Eingabe abzuschließen.
Diese Formel dann anschließend soweit hinunter kopieren wie benötigt. Ich habe sie in der nachfolgenden Datei bereits ein Stück weit eingefügt.
Die "-5" am Ende der Formel ist notwendig, weil in der Formel die Bereiche in Spalte E bzw. D jeweils in Zeile 6 ($E$6 bzw. $D$6) beginnen. Diese beiden Zellen liegen aber jeweils in Zeile 1 des Bereiches, der in der INDEX-Funktion verwendet wird.
Diese Formel ermittelt aus der Spalte "Zuordnungsort Nr" die neuen von Dir gewünschten Zuordnungsnummern.
Als Test habe ich in Zelle B14 die Zuordn.Nummer auf 1111 geändert, sodass man die Auswirkungen in der Spalte "ZuNr_Neu" sofort sehen kann.
Voraussetzung: Spalte E (Jahr) muss aufsteigend nach Jahren sortiert sein!

2) Im Arbeitsblatt "Auswertung" habe in der Pivot-Tabelle diese neue Spalte "ZuNr_Neu" (Spalte F) anstelle der alten Spalte B als Feld "RD_Neu" aufgenommen.

3) Bei künftigen Auswertungen wird es vermutlich günstig sein (insbesondere bei großen Datenbeständen), die Formeln in der Spalte "ZuNr_Neu" in feste Zahlenwerte umzuwandeln, sobald einmal die neuen Nummern errechnet wurden.

 

Hallo Anton,

das ist einfach super Lösung!!! mir fehlen die Worte.. Ich DANKE Dir :-)

Nun nachdem ich die Echtdaten eingefügt habe, dürfte ich was neues feststellen.

Undzwar die Namen werden nicht mehr angezeigt bzw. erkannt.. Ich habe dort an dem Formel nichts geändert aber bekomme die Meldung "Der Wert ist nicht verfügbar"!! ??? (Siehe Screenshot) die Namen sind doch alle im Arbeitsblatt "Datenbank" drin und keiner hat an dem Formel was geändert und die Pivottabelle habe ich auch aktuallisiert. Was ist nun falsch? ;-(

und die Thematik mit der Tendenzberechnung.. meinst du liegt das wirklich an "h" und "i"? soll ich sie tauschen? Probeweise habe ich's getan und das ergebnis war ok aber sicher bin ich mir nicht, ob das wirklich auch korrekt ist..

Viele Grüße und top Wochenende wünsche ich dir! :-)

Armin

 

Hallo Armin,

soweit ich Deiner Auswertung.jpg-Datei entnehmen konnte, steht in der gekennzeichneten Zelle Auswertung!B13 folgende Formel:
=INDEX(Datenbank!$C$6:$C$23;VERGLEICH(C13;Datenbank!$D$6:$D$23))
Das sind demnach 18 Namen, die maximal angezeigt werden können - tatsächlich kommt es aber darauf an, welche Nummern aus dem Bereich Datenbank!$D$6:$D$23 verwendet werden in der Pivot-Tabelle.
Vermutlich wirst Du die Namensbereiche in der Formel erheblich ausweiten müssen, zB:
=INDEX(Datenbank!$C$6:$C$200;VERGLEICH(C13;Datenbank!$D$6:$D$200))
je nachdem, wie viele Namen Du dann tatsächlich im Arbeitsblatt "Datenbank" hast.

 

Guten Morgen Anton,

vielen lieben Dank!!! Also hat alles wunderbar geklappt. Jetzt funktionierts endlich sowie es sein sollte.

Tendenzberechnung war ja tatsächlich falsch, weil "h" und "i" nicht richtig gesetzt waren. Ich habe sie nun ausgetaucht und nun funktioniert sie auch!!

Gut die geschweiften Klammern habe ich wie du's beschrieben hast entfernt und leider danach dann hat nichts funktioniert. Dann habe ich sie wieder rein gemacht und die Formel möglichst weit nach unten kopiert und siehe da.. ES FUNKTIONIERT!! :D

Also ich wüsste nicht wie ich dir sonst bedanken soll aber du hast DEFINITIV bei mir was gut!!!

Viele liebe Grüße und eine super Woche wünsche ich dir! :-)

Gruß

Armin!

 

Hallo Anton,

leider muss ich mich wieder mal melden :-'(

Also es funktioniert alles prima (sogar teil von Tendenzberechnung!).

Nur alles bis auf Tendenzberechnung bzw. die Anzeige der Pfeilen!!

Also die Tendenzberechnung funktioniert solange die Platzierungen keine große Zalen sind (Einstellig bis Zweistellig).

Ab ca. dreistellige Bereiche fängt sie komische Pfeile nach rechts für "unverändert" anzeigen. Aber das ist falsch. Ich weiß auch nicht weshalb es so angezeigt wird?!

Ich habe die Datei jetzt mit einem weiteren Beispielkunden ergänzt, damit du sehen kannst was passiert, wenn die Zahlen der Platzierungen größer werden..

Siehe bitte dir dazu den Beispielkunde: Hans-Jörg an.

Ich hoffe, dass du mir hier auch helfen kannst..

Blöd ist, dass ich das beim Präsentieren gegenüber Chef feststellen dürfte, dass die Tendenzberechnung und demzufolge die Anzeige nicht stimmt!! :O

Ich weiß es jetzt überhaupt nicht mehr, ob die Anzeige unten im größeren Zahlenbereich stimmen oder nicht -.-

Liebe Grüße

Armin

 

Hallo Armin,

Du willst die Werte in der 2013-Spalte der Pivot-Tabelle mit dem Durchschnittswert aus den Spalten 2011-2013 vergleichen:
a) Ist der 2013-Wert größer als das 1,1-Fache des Durchschnittswertes, dann soll die Funktion "h" ergeben (=> Pfeil nach oben)
b) Ist der 2013-Wert kleiner als das 0,9-Fache des Durchschnittswertes, dann soll die Funktion "i" ergeben (=> Pfeil nach unten)
c) Gilt weder a) noch b), dann soll die Funktion "g" ergeben (=> Pfeil waagrecht), weil dann der 2013-Wert nicht stark genug vom Durchschnittswert abweicht.

Nimmt man also die Werte von Hans-Jörg (Zeile 9), so ergibt sich:
2013-Wert = 624
Durchschnittswert 2011-2013 = (695+431+624)/3 = 583,33
Test nach a): 624 > 1,1*583,33 ergibt 624 > 641,663 ergibt FALSCH.
Da der Test nach a) falsch ergibt, Test nach b): 624 < 0,9*583,33 ergibt 624 < 524,997 ergibt FALSCH.
Da der Test nach a) und b) "falsch" ergibt, gilt c). Das heißt, der Wert 624 liegt noch im gleichbleibenden Bereich; das ist nach der Formel der Bereich ±10% vom Mittelwert der letzten 3 Jahre (2011-2013):
524,9(=0,9*583,3) < 624 < 641,6(=1,1*583,3).

IMHO müsste das angezeigte Ergebnis ok sein. Dass natürlich bei einem größeren Mittelwert die absolute Größe der Schwankungsbreite (±58,33) sehr viel größer ist als bei einem kleinen Mittelwert (absolut betrachtet), sollte auch klar sein.

 

Guten Morgen Anton,

vielen Dank für deine Erklärung!!

Das werde ich dann so an meinen Chef weitergeben!!

Nochmals herzlichen Dank für alles!! :-)

Viele Grüße und eine angenehme Woche

Armin