es geht um die Auswertung unserer Projekte, welche ich gerne aufgeschlüsselt veranschaulichen möchte.
--> Beispiel Tabelle im Anhang.
Die Problemstellung: sämtliche unser Aufträge sind im ERM System hinterlegt, allerdings wird nicht auftragsbezogen eingekauft, sondern bedarfsorientiert.
Nun könnte ich hier Abhilfe schaffen, indem ich mittels Abfrage auf unser ERM System die relevanten Daten pro Auftrag beziehe. Da ein Auftrag bei uns fast immer
auf einen Fertigartikel bezogen ist (Fertigartikel besteht aus unterschiedlicher Menge Einzelartikel), ziehe ich die Daten nach Parameter Auftragsnummer.
Dies ergibt eine Excel Liste mit dem Fertigartikel, den Einzelartikeln, wie oft diese auf dem Fertigartikel verbaut sind und zu welchen Preisen ich diese bezogen habe.
Wäre das Ganze für eine Tabelle, kein Problem - aber da wir viele unterschiedliche Fertigartikel (auch unterschiedliche Versionen eines) haben, und noch dazu bei jedem neuen Einkauf
die Werte neu berechnet werden müssten, wollte ich fragen, ob man eine Excel Tabelle erstellen kann, welche mit den unterschiedlichen Varianten klar kommt?
Übersicht Tabellenblatt Daten:
Für die Berechnung relevant sind:
Belegnummer - Nummer der jeweiligen Bestellung
Kurs - Teilweise Fremdwährung, der zu dem Zeitpunkt der Bestellung gültige Kurs zu Euro ist dann hier hinterlegt
Artikel - Nummer des Artikels für Fertigartikel auf der Bestellung
Bestellmenge - Bestellmenge des Einzelartikels in der jeweiligen Bestellung
Teileliste - der Fertigartikel, hier bei den meisten Aufträgen ein ein Artikel
EK Preis /% - der Einkaufspreis des Einzelartikels pro 100
Positionskosten - Zusatzkosten (Fracht, Zoll, etc) zu der Lieferung gesamt
Menge auf Teileliste - wie oft der Einzelartikel auf der Teileliste verbaut ist
Positionskosten pro Stück - Preis pro Einzelartikel -> (EK Preis /% durch 100)
Positionskosten pro Teileliste - Preis der Einzelartikel pro Teileliste
Überscht Tabellenblatt Auswertung:
Hier soll die Auftragsnummer eingegeben werden, mittels Abfrage auf die DB erfolgt dann die Ausgabe in Tabellenblatt "Daten"
Die blau hinterlegten Felder sollten vernünftig Formatiert sein, die grünen Felder sind die Ausgabe aus der Datenauswertung.
Also wie gesagt, mein Problem: die Auswertung des Ganzen bei unterschiedlichen Abfragen - wie bringe ich Excel bei, bei abweichender Anzahl Artikel, Bestellungen, Preise etc
auf das richtige Ergebnis zu kommen und das dann auch noch "ansehlich" formatiert zu haben?
das liegt daran, dass es sich bei dem Testauftrag nur um einen Artikel handelt.
Ich habe die Tabelle etwas überarbeitet und nun ein einen weiteren Artikel angehängt.
In der Übersicht habe ich das ganze noch um die Auftragsmenge erweitert.
Pivot Tabellen hatte ich mir angeschaut, muss aber sagen dass ich hiervon kaum eine Ahnung habe.
Die von mir erzielten Ergebnisse waren etwas ... seltsam.
schau Dir mal die angefügte Datei an.
Im Moment gibt es Funktionalität in den Zellen C4 und C7.
C7 wird in Abhängigkeit von der Auswahl in C4 gefüllt. So weit so schlecht .
Wobei C4 initial beim Öfnnen der Datei eingelesen wird. Das bedeutet wenn Du Änderungen an der Tabelle "Daten" nach dem Öffnen vornimmst,
werden diese aktuell nicht berücksichtigt. Das muss ggf. noch geändert werden. Je nach dem wie und wann Du die Tabelle "Daten" füllst.
Was mir nicht klar ist, welche Informationen benötigst Du im "Bereich" Positionen?!
Vielleicht kannst Du diese Informationen noch einmal in den Bereich aus "Daten" hineinkopieren.
Solltest Du keine weitere Informationen im Bereich "Positionen" benötigen, erkläre mir doch bitte noch einmal was wie berechnet wird,
bzw. welche Informationen noch aus "Daten" übernommen werden sollen.
Hallo ihr drei, und erstmal vielen Dank für eure Antworten ;)
ich sehe gerade dass ich nicht eindeutig war - bei Daten immer ein großer Fehler, sorry.
1. Die Datenausgabe über die Abfrage bringt nur die Spalten A bis L, Spalten N bis Q (jetzt orange hinterlegt) habe ich manuell erstellt um Rechenwerte zu haben.
Diese Werte müssten automatisch errechnet werden, mein Problem ist hier, dass ich nicht weiß wie ich bei unterschiedlichen Abfragen vorgehe.
Bsp. es kommt eine neue Bestellung von Artikel 21120, wird unter Zeile 4 eingefügt, alle darunter verschieben sich um eines nach unten -> die Formeln im
hinteren Bereich sind falsch.
2. Ich habe in der Mappe einzelne Spaltenbezeichnungen umbenannt, um sie eindeutiger zu machen. zB "Belegnummer" in "Belegnummer Bestellung Einzelteil"
Die Abfrage war auf eine Auftragsnummer, In Spalte A waren die Belegnummer zu den jeweiligen Bestellvorgängen der Einzelteile. Wenn der Einfachheit halber die Auftragsnummer "1" ist,
und der Auftrag die Fertigartikel "1234" und "4321" betrifft, dann würde mir die Abfrage ein Ergebnis liefern, wie es in Tabellenblatt "Daten" zu sehen ist. Die Bestellvorgänge zu den jeweiligen
Einzelteilen welche auf den Fertigartikeln verbaut sind, und deren Kosten.
3. Mir wäre auch schon sehr viel damit geholfen, wenn das Ganze anstatt auf einen Auftrag nur auf einen Fertigartikel läuft. Die Abfrageparameter hierzu kann ich ohne viel Aufwand in SQL ändern.
Ausgabe wäre dann vergleichbar wie bisher, einzige Ausnahme wäre, dass es nur einen Artikel betrifft, und nicht zwei wie derzeit in Tabellenblatt "Daten".
@ Jockel, dein Vorschlag für Spalten C & D kommt meinem Ziel schon sehr nahe, ich gebe zu dass ich die Formel nicht komplett verstehe, aber kann man die Berechnung aus Tabellenblatt "Daten" Spalten
M bis P auch direkt in die Formel übernehmen? Gegeben sind Spalten A bis L, Ziel ist es, in Tabellenblatt "Auswertung" für jeden Einzelartikel die durchschnittlichen Kosten pro Stück Fertigartikel zu haben.
Diese errechnen sich mittels Bestellmenge * EK Preis pro hundert durch 100 = EK Preis der Bestellung, dazu die Positionskosten (Fracht etc.) = Bezugskosten der Bestellung. Das durch die Bestellmenge ergibt
die Bezugskosten pro Einzelteil bei der jeweiligen Bestellung. Um die Durchschnittskosten bei mehreren Bestellvorgängen zu ermitteln müssen die jeweiligen Bezugskosten nun gemittelt werden. Die würde ich mittels
der Rechnung (Bezugskosten Bestellung A mal Bestellmenge Bestellung A plus Bezugskosten B mal Bestellmenge B plus Bezugskosten N mal Bestellmenge N) durch Gesamtbestellmenge ermitteln
-> (Ka*Ma+Kb*Mb+Kn*Mn)/Mgesamt. Nur habe ich hier wieder keine Ahnung, wie ich es bei sich verändernden Datenwerten bewerkstelligen soll.
@ IchBinIch, das Feld "C4" in "Auswertung" wird mit einer Abfrage auf die Datenbank versehen -> Auftragsnummer eingeben -> Daten Aktualisieren -> Daten zu dem gewählten Auftrag werden in Tabellenblatt
"Daten" ausgegeben -> Formeln in Tabellenblatt "Auswertung" werden aktualisiert.
Mit "Positionen" sind die jeweiligen Artikel auf der Teileliste gemeint, eine Teileliste besteht aus mehreren Einzelartikeln (jeder hat eine aufsteigende Positionsnummer). Ich habe das nicht in der Tabelle klar ausgedückt,
sorry. Die Positionsnummer auf der Teileliste kann ich in der Abfrage hinzufügen.
@ Fennek Danke, die Formatierung in Tabellenblatt Auswertung ist nur ein Vorschlag meinerseits - wichtig ist, dass man nach Eingabe des Parameters (Auftrags- oder Fertigartikelnummer) eine Auflistung der durchschnittlichen
Bezugskosten pro bestückten Einzelartikels und damit die Materialkosten je Fertigartikel erhält. Falls dies mit einer Pivot Tabelle geht - gerne. Ich steige dort nur nicht ganz durch ;)
Änderungen an der Tabelle:
Tabellenblatt Auswertung: Formeln von Jockel übernommen, Spalten Positionsnummern und Anzahl Bestückung hinzugefügt. Die grün markierten Felder sollten autimatisch aus Tabellenblatt Daten bezogen werden.
Tabellenblat Daten: Bezeichnungen überarbeitet (hoffentlich eindeutiger), Spalte Positionsnummer eingefügt, die manuell erstellen Einträge orange markiert (diese sind später in der Abfrage nicht vorhanden!)
Also kurz: wie komme ich mit den Werten aus Tabellenblatt Daten zu den Ergebnissen in Tabellenblatt Auswertung, wenn die Werte in Tabellenblatt Daten je nach Abfrageparameter unterschiedlich zurück kommen ?
danke für die ausführliche Antwort. Das macht es für mich aber leider nicht einfacher die Zusammenhänge zu erfassen .
Ich fasse mal zusammen wie ich das verstanden habe.
Du willst also nicht eine Belegnummer eingeben sondern eine Fertigteilenummer.
Ein Fertigteil (= "Teileliste" in Tabelle Daten) betsteht aus mehrere "Bauteilen" (= "Einzelartikel" in Tabelle Daten).
Im unteren Bereich "Positionen" erwartest Du eine Auflistung aller Einzelartikel für den jeweiligen Fertigartikel
aus allen Aufträgen mit den dazugehörigen durchschnittlichen "Beschaffungs-/Produktionskosten"?!
Es wäre also erforderlich zu ermitteln wie oft wurde ein Einzelartikel benötigt und wie waren die jeweiligen "Beschaffungskosten".
Der durchschnittliche Preis ergibt sich dann aus Summe Beschaffungskosten / Anzahl Verwendung Einzelartikel.
So weit korrekt?
Um das dann wirklich testen zu können, wäre es schön wenn Deine Tabelle mehr als einen Fertigartikel enthält :32:.
Das schrieb Fennek auch schon. Wie Fennek ebenfalls schrieb, sollte das mit einer Pivot-Tabelle klappen.
ich werde mir morgen auf der Arbeit mal ein paar weitere Fertigartikel ausgeben lassen - hier habe ich gerade keinen Zugriff, und mit Fantasie Artikeln fange ich lieber garnicht erst an ;)
Grundsätzlich werden aber alle die selbe Struktur aufweisen. Unterschiedlich sind nur die Werte.
Was das verstehen angeht: japp ^^
Eine Auswertung der Daten aus einer SQL Abfrage in der ich als Ergebnis die Durschnittlichen Materialkosten für einen Fertigartikel erhalte (Nehmen wir lieber Fertigartikel und entfernen uns von Aufträgen).
Die Excel Tabelle ist mit der Abfrage verknüpft - Parameter ist die Nummer des Fertigartikels, Werte werden in Tabellenblatt "Daten" ausgegeben.
In der Abfrage habe ich mehrere Datenbanken unseres ERM Systems verknüpft, so dass als Ausgabe eben alle durchgeführten Bestellungen von Einzelartikels des Fertigartikels angezeigt werden - welche, wann, wie viele, zu welchem Preis und was waren die Zusatzkosten - und wie oft brauche ich den Einzelartikel auf dem Fertigartikel.
Als Ergebnis erhoffe ich mir eine Übersicht, in welcher nur der Fertigartikel eingegeben werden muss (Parameter für Abfrage), und als Ergebnis die Durchschnittlichen Materialkosten pro Fertigartikel angezeigt werden.
Am Besten wie in der aktuellen Tabelle angezeigt mir Auflistung der einzelnen Fertigartikel, ihrer Positionsnummer auf der Teileliste, ihre Anzahl auf der Teileliste sowie ihrem Durschnittlichen Bezugspreis (Einkaufs- plus Beschaffungskosten). Diese Werte kann man aus den Daten beziehen (Artikel Nummer, Positonsnummer, Anzahl bestückt) bzw berechnen (Bezugskosten). Die Summe dieser Werte sollte dann die Materialkosten des Fertigartikels ergeben. Sehr gut wäre es, wenn der / die Endanwender garnicht erst in das Tabellenblatt "Daten" müssen.
Mein Problem ist, dass diese Ausgabe bei möglichst allen Fertigartikeln funktionieren sollte - der Fertigartikel in dem Beispiel ist mit 29 unterschiedlichen Einzelartikeln bestückt, genausogut können es nur 4 oder über 400 sein.
Für einen einzelnen Artikel wäre es kein Problem für mich, mein Problem ist eine saubere Tabelle bei unterschiedlichen Daten ;)
Mit Pivot Tabellen hatte ich es versucht, aber wie gesagt seltsame Ergebnisse erhalten. Falls es damit einfach geht - immer her damit ;)
Ich habe eine neue Tabelle angehängt, welche meinen Vorstellungen entspricht - In "Daten" ist nun nur noch die Ausgabe aus der Abfrage - keine manuell bearbeiteten Felder mehr. In "Auswertung" sind die
Ergebnisse (ohne Formeln) von mir händisch eingetragen, hier sollten die Werte mittels Formeln ermittelt und zurück gegeben werden.
.
.