PDA

Vollständige Version anzeigen : Komplexe Analyse/Auswertung


chris308
16.07.2014, 23:36
Hallo liebe Excel-Profis!

Ich schreibe gerade meine Bachelorarbeit und brauche diesbezüglich dringend Hilfe! Vielen Dank schon mal im Voraus!!!

Ich habe eine Mustertabelle angehängt um die es hauptsächlich geht und um euch die Fragestellung genauer erläutern zu können.

Diese Tabelle ist grundsätzlich so aufgebaut:
A3 = MaterialNr (diese gibt es nur einmal!)
B3 = Lieferant (hier kann ein Lieferant mehrere MaterialNr liefern)
C3:L3 = Sind beispielhaft aufgeführte Maschinen, in welche die jew. MaterialNr geliefert werden um die Mehrfachverwendung von MaterialNr aufzuzeigen. Der Umsatzwert der jew. Maschine ist dabei rechts daneben aufgeführt wenn die jew. MaterialNr in die Maschine eingeht und somit in der Spalte angezeigt wird. Die Formeln wie dieser Wert etc. bestimmt wird sind in den einzelnen Zellen vorhanden.
--> Beispiel: MaterialNr 1 wird von Lieferant 1 geliefert und geht in die Maschinen 1,2 und 4 ein.
Über den Maschinen (Zellen C,E,G,I,K 1) habe ich durch die Funktion Teilergebnis(Formel 104) den Umsatzwert aus der jew. dazugehörigen Spalte (D,F,H,J,L) einmal angezeigt, da dieser Wert nur einmal benötigt wird.
Diese Werte werden dann in der Zelle B1 summiert um den Gesamten Umsatzwert zu bestimmen.
Nun kann eine Analyse durchgeführt werden, indem ein Filter bei Lieferant gesetzt wird. Dadurch kann herausgefunden werden in welche Maschinen der Lieferant welche MaterialNr liefert und welchen Gesamtumsatz das nun ausmacht.
--> Beispiel: MaterialNr 1 wird von Lieferant 1 geliefert und geht in die Maschinen 1,2 und 4 ein. In Summe ist dadurch ein Gesamtumsatz von 8000 betroffen.
Nun ist meine große Frage: Ich möchte wissen welcher Lieferant nun den höchsten Gesamtumsatz ausmacht. So wie die Tabelle jetzt ist, müsste ich jeden einzelnen Lieferanten über den Filter auswählen und den Gesamtumsatz ausrechnen lassen um herauszufinden wer den größten Umsatz ausmacht.

Gibt es durch Funktionen die Möglichkeit den Lieferanten der den größten Gesamtumsatz ausmacht herauszufinden und dann den Lieferanten der den zweitgrößten Gesamtumsatz ausmacht etc. ohne wie oben beschrieben jeden Lieferant einzeln über den Filter auszuwählen und den Gesamtumsatz berechnen lassen...

Ich weiß das war viel Text, nur ich wollte es einigermaßen verständlich machen:)

Vielen Vielen Dank schon mal im Voraus!!

Grüße
Chris

Hajo_Zi
17.07.2014, 06:04
Hallo Chris,

<a href="http://hajo-excel.de/crossposting.htm" title="Crossposting" >Zu Crossposting lese diese Seite Hajo-Excel.de</a>
Du hast Glück das JINX nicht mehr da ist, ansonsten würde Dein Beitrag wegen Crossposting geschlossen werden.
Ein Zitat aus der Netiquette (hier im Forum):
Unerwünscht sind auch Crosspostings - also dieselbe Frage gleichzeitig in mehreren Foren (nicht nur im MSOF).
Denn auf diese Weise werden mehrere Gruppen von Leuten mit dem gleichen Thema befasst, ohne dass sie voneinander wissen.
Naturgemäß laufen dann die Antworten, die im einen Forum "zu spät" gegeben wurden, ins Leere und bleiben ohne Resonanz.
Es reicht also, zunächst in einem Forum zu posten - wenn die Antworten dann unbefriedigend sein sollten, steht es einem anschließend immer noch offen, ein anderes Forum zu Rate zu ziehen.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

R J
17.07.2014, 06:24
Hi Chris,

schau Dir mal diesen Thread (http://www.ms-office-forum.net/forum/showthread.php?t=311539)an. Der könnte Dir als Leitfaden dienen...

Mc Santa
17.07.2014, 07:51
Hallo,

ich denke durch eine Pivottabelle kannst du den Problem schon lösen, ich habe die im Anhang eine solche erstellt :)

Hilft dir das weiter?
VG

Kollaps
17.07.2014, 12:13
Die PURE Klassifizierung der Platzierung kannst du durch =Rang(Zahl, Bezug), Summewenn(Bereich, Suchkriterium, [Bereich_Summe]) und einer Material-Gesamt-Summenspalte herausfinden, wenn's unbedingt per Formel sein muss.

Habe ich dir mal in einem Plumpen Beispiel angehängt.

Eine Rangfolgenauflistung wird per Formel etwas umständlicher...

Ich hoffe das hilft dir trotzdem schonmal ^^

chris308
17.07.2014, 16:32
Danke für die Hinweise!! Ich werde mir diese jetzt mal anschauen und probieren ob ich dies auch auf die Originaltabelle anwenden kann!

chris308
17.07.2014, 18:21
@Mc Santa
Von den Werten her müsste es mit der Pivot klappen!
Nur wie bist du genau vorgegangen bei der Erstellung der Pivot?
Was ist bei dir Feld1?

Ich habe nochmal die gleiche Tabelle mit ein paar Werten ergänzt, ist es hier auch noch möglich mit der Pivot?

Viele Grüße

chris308
17.07.2014, 18:30
@Kollaps

Den Ansatz hab ich auch schon versucht.
Mir ist nur aufgefallen, dass in der Mustertabelle ein paar Werte fehlen um die eigentliche Herausforderung aufzuzeigen. Daher hab ich in deine Mustertabelle noch ein paar Werte eingefügt.

Das Problem ist dann bei dem Ansatz, dass wenn ein Lieferant in eine Maschine 2 oder mehr verschiedene Teile liefert, werden diese letztendlich rechts in der Spalte summiert und dann im Gesamtumsatz 2 oder mehr mals miteinberechnet.

Das kannst du auch testen indem du einmal den Filter bei Lieferant setzt auf Lieferant 5. Dann ergibt es ein Gesamtumsatz von 11000. In der Tabelle darunter mit der Platzierung wird jedoch 12000 angezeigt, weil eben der Wert aus Spalte D zweimal mit einberechnet wird, jedoch nur einmal berücksichtigt werden sollte.

Kann man das durch geschachtelte Summewenn o.ä. Funktion schaffen?!

Viele Grüße

Mc Santa
17.07.2014, 18:35
Hallo,

Ja ich denke schon :)
Ich beschreibe einmal für Excel 2010 (Windows 8.1) mein Vorgehen. Zum Verständnis von PivotTabellen und für weitere Anwendungen empfehle ich außerdem den Link aus meiner Signatur.

Markiere die Zellen A3:L13 in deiner Tabelle
Einfügen > PivotTable > OK
Rechts siehst du nun eine "PivotTabele-Feldliste". Ziehe dort "Lieferant" in den Bereich "Zeilenbeschriftung"
Oben im Menü gibt es außerdem einen neuen Punkt PivotTabele-Tools. Wähle Hier: Optionen > Berechnungen > Felder, Elemente und Gruppen > Berechnetes Feld...
Vergib einen Namen (Ich habe hier Feld1) stehen gelassen, dass ist natürlich nicht sonderlich praktisch ;) ). Als Formel verwendest du =Umsatzwert + Umsatzwert2 + .... + Umsatzwert5 (Du kannst die Namen eintippen, indem du unten in der Liste einen Doppelklick machst, dann musst du nicht abtippen)
Klicke auf eine Zelle in der PivotTabelle (Spalte von Feld1) und sortiere absteigend


Kommst du mit dieser Anleitung zurecht?
Fragen gerne :)
VG

Kollaps
18.07.2014, 08:31
Aber wenn ich mir die Zahlen anschaue passt eher etwas in der Spaltensumme nicht. Bei Maschine 1 macht Lieferant 5 2.000 Umsatz, aber es werden nur 1.000 berechnet?

Wenn man mal schaut

Maschine 1: 2.000
-Material 7 : 1.000
-Material 8 : 1.000

Maschine 2: 2.000
-Material 6 : 2.000

Maschine 3: 3.000
-Material 7 : 3.000

Maschine 5: 5.000
-Material 7 : 5.000

für mich ergibt das 12.000 in Summe (also als Gesamt Umsatz des Lieferanten)?


Außerdem ist das mit der "Max"-Funktion über den Spalten vielleicht nicht ganz so glücklich gewählt, wenn du als "Überschrift" für die Zeile "Ges. Umsatz" schreibst (wegen dem Bachelor, ich weiß nicht inwiefern dort auf sowas geachtet wird.). Du nimmst ja nur den Gesamt Umsatz der maximalen Material-Erträge pro Maschine und ich glaube, das ist nicht unbedingt Sinn und Zweck, also ich würde es zumindest nicht verstehen, sollte es so sein.

Das kannst du dir auch rausfiltern wenn du Summewenn, Max und nenn SVerweis [oder Index] verschachtelst (Würde ich so auf die Schnelle sagen.), dann würdest du auch im "Ges. Umsatz", der keiner ist, 12.000 rausbekommen.

Ist dann halt alles sehr Formellastig, würde ich auch nur machen, wenn's verlangt wird, ansonsten ist eine Pivot-Tabelle, wie McSanta das beschrieben hat vorteilhafter und weniger Fehlerquellen behaftet.