Office: gegenseitige Verrechnung von Lagereinheiten

Hallo,
ich fühle mich ein wenig überfordert und bräuchte einen kleinen Schubser in die richtige Richtung.
Ich habe einen Wust an Daten sortiert und heraus ist eine wunderschöne Pivot Tabelle gekommen. :-D
Im nächsten Schritt möchte ich die Lagereinheiten miteinander verrechen.
Also eine gegenseitige Saldierung z.B.:
Hat der Hagener in Berlin 2108 Einheiten gut - der Berliner in Hagen 343.
Wie kann ich das darstellen, berechen etc.? Und gibt es durch die pivotierte Form Probleme?
Vielen Dank für euer Anregungen

MfG
Florian

Saldo Lagerort
GST-Name
Berlin Brühl Hagen Hamburg

Berlin x 2536 343 5111
Brühl -2443 x 149 -220
Hagen 2108 420 x -1146
Hamburg -1961 212 1867 x

 

Hmmm... Ich versteh die Aufgabe vielleicht auch nicht so recht. Aber, anhand Deiner Beispiel-Tabelle...

a) Hannover hat in allen anderen Orten zusammen 431 Einheiten verteilt. Das ist die Zeilen-Summe in L9.
b) Alle anderen Orte haben in Hannover insgesamt 177 Einheiten liegen. Das sagt die Spalten-Summe in F15 aus.
c) Ziehe ich jetzt b von a ab, bekomme ich +254 Einheiten als Saldo. Was sagt mir das?

Wenn ich auf meinem Konto die Abgänge von den Zugängen abziehe und damit einen Saldo bilde, erhalte ich als Ergebnis meinen Kontostand, der (hoffentlich) positiv aber auch negativ sein kann.

In diesem Beispiel heißt das also, daß Hannover einen "Überschuß" von 254 Einheiten hat.

Problematisch oder zumindest gehirnwindungsverwindend sehe ich es dort, wo als Zeilen- oder Spalten-Saldo eine negative Zahl steht. Ok, kaufmännisch gesehen ist eine negative Belastung letztendlich auch nur ein Zugang. Schauen wir uns also mal Berlin an...

a) L5 (Berlin's Guthaben in allen anderen Orten) sagt 9229
b) B15 sagt, alle anderen wollen von Berlin insgesamt -7267 haben.
c) 9229 minus -7267 ergibt 16496

Wenn Dir diese Zahlen irgendwas sagen und schlüssig erscheinen, kannst du folgende Formel in B17 setzen und bis K17 nach rechts ziehen:

Code:

=SVERWEIS(B4;$A5:$L14;12;FALSCH)-B15

Bei der Gelegenheit könntest du dann auch gleich den Schreibfehler in J4 korrigieren... ;-)

Gruß, Michael

 

Also zuerst vielen Dank Michael für deine schnelle Antwort.
Und nach wiederholtem lesen, finde ich meine Fragestellung auch unklar.
Du hast aber dennoch viel daraus gemacht.

Ich glaube deine Formel trifft es nicht so ganz. Berlin kann ja nicht ein Guthaben von 9229 haben und Schulden von -7267 (negativ bedeutet in diesem Fall Schulden) und dann am Ende 16496 Einheiten haben.
Die Gesamtergebnisse sind auch nicht ausschlaggebend die individuelle Verrechnung ist wichtiger.

Aber auch das verschuldet meine schwammige Formulierung.

Ich bräuchte eine genauere Differenzierung. Also, dass es in der Tabelle miteinander verrechnet wird:

Der Hagener hat in Berlin 2108 Einheiten gut - der Berliner in Hagen 343. D.h. der Hagener hat nur noch 2108-343=1765 Einheiten gut und umgekehrt hat der Berliner 1765 Einheiten Schulden.
Gibt es eine Möglichkeit dieses darzustellen/zu berechnen?



Für weitere Hilfe wäre ich sehr verbunden.

Den Fehler habe ich in meiner Tabelle berichtet. Hier habe ich es mal dezent weggelassen.

schönen Abend
Florian

 

Hallo Florian,

wenn Du etwas gegeneinander aufrechnen willst, dann musst Du halt das eine vom anderen abziehen...
Ist eigentlich ganz einfach, in Deiner Tabelle aber etwas Fleißarbeit.

Ich habe unter Deiner Liste eine "Aufrechnungstabelle" angefügt.
Ich hoffe das ist so wie Du es brauchst.
Ansonsten kann es hoffentlich wenigstens Dir oder anderen helfen die gewünschte Lösung zu erarbeiten.

Die Zelle J14 hast Du nicht ausgefüllt.
Die Summenformeln in Spalte L sind meiner Meinung nach falsch, weil sie die Spalte B nicht mitrechnen. Ich habe das angepasst.
In Zeile 15 habe ich die entsprechenden Summenformeln eingefügt.
Und ich habe die senkrechten und waagerechten Summierungen noch einmal aufaddiert um die absoluten Gesamtsummen zu vergleichen (Fehler ausschließen).

Eins noch:
wer hier Dateien hoch läd, ist immer gut beraten Ihnen auch aussagekräftige (und möglichst unverwechselbare) Namen zu geben.
Das vereinfacht die Sache für die helfenden...

 

Hallo Achim,

danke das du dir die Mühe gemacht hast.
Die Tabelle sollte jedoch nur als Skizze gedacht sein(deswegen die schlampige Ausführung und fehlende Bezeichnung).
Sie ist bei weitem größer und muss regelmäßig aktualisiert werden. Ich hatte gehofft, dass es eine Formel gibt die mir diese Fleißarbeit ersparrt.

Gruß
Florian

 

Doch, im Prinzip schon. Das ist das, was ich mit gehirnwindungsverwindend meinte... ;-) "negative Schulden" sind per se wiederum Guthaben.

Was ich letztes mal noch vergessen hatte: Woher kommen die Zahlen in Zeile 15? Summen über die darüber liegenden Zellen können es irgendwie nicht sein. Die Summe über B15:K15 müßte eigentlich gleich der Summe über L5:L14 sein. Zumindest, wenn die Summen-Formel in Spalte L korrigiert wurde... ;-) (Danke, Elfe, war mir gar nicht aufgefallen.)

Wenn es aber Summen sein sollen, dann bekommst Du mit der SVerweis-Formel einen sauberen Saldo. Zumindest schmeißt mir die nach korrigierter Summe in B15 sauber den Saldo 12388 raus, was dem entspricht, was Elfe auch raus hat.

Wenn Du die Saldierung aber Ort gegen Ort einzeln brauchst, dann hat Elfe ja schon was gebastelt. Die Fleißarbeit bei den Formeln liegt darin, daß die so nicht "ziehbar" sind. Da könnte man aber sicher auch was mit indirekt(), bereich.verschieben() oder so was werden...

Gruß, Michael

 

Hallo Florian,

ich vermute doch mal, dass die Lagerorte gleichbleibend sind.
Es werden sich doch nur die Salden ändern, oder?

Dann wäre es ja eine einmalige "Fleißarbeit" so eine Tabelle zu erstellen, denn die Aufrechnung passiert ja über Formeln.
Sprich, wenn Du in der oberen Tabelle etwas änderst, ändern sich auch die Zahlen in der unteren.

Möglicherweise könnte die untere auch als Hilfstabelle genutzt werden um Formeln/Bezüge zu vereinfachen (falls Sie Dir als Infoquelle nicht geeignet erscheint).

Mit Pivot-Tabellen kenne ich mich nicht aus, deshalb kann ich Dir wohl nicht weiter behilflich sein, da ich keine Vorstellung habe wie Du es letztendlich haben möchtest.

 

Zuerst vielen Dank für eure Hilfe!

...und das ist genau das, was ich benötige und wovon ich keine Ahnung habe. Und was elfe gemacht hat ist gut, aber nicht der Sinn der Sache, weil es zu aufwendig ist.
Die beiden Punkte die du angesprochen hast:
indirekt(), bereich.verschieben() bringen mich auch nicht weiter. Also sie sagen mir nicht wirklich etwas und aus der ExcelHilfe werde ich auch nicht schlau.

Aber nochmal Danke für eure Bemühungen.

 

@ achim

Ja, so mache ich es auch.
Nur noch eine Frage:
Die Verrechnung Ort für Ort hast du manuell gemacht? Also Zelle für Zelle durchgegangen?

schönen Tag noch
Florian

 

Klaro.
Wie miriki bereits schrieb: die Formeln sind in so einer Tabelle nicht "ziehbar".
Deshalb meinte ich auch "Fleißarbeit".
Aber halt nur einmal, wenn immer nur die Forderungen/Schulden aktualisiert werden.


Ich habe mal in eine etwas andere Richtung gedacht:
wie wäre es mit einem Makro?

Zum Beispiel könnte ich in Zelle B21 (meine Hilfstabelle) auch folgende Formel schreiben:

das ist zwar immer noch nicht "ziehbar", aber was würde sich in B22 ändern?
A6 würde zu A7 oder (A6;0;1;1;1) würde zu (A6;1;1;1;1)
und
C4 würde zu D4 oder (C4;1;0;1;1) würde zu (C4;1;1;1;1)

Das bedeutet:
über hochzählen von Variablen müsste man doch eigentlich einen Code zum Ausfüllen der unteren (Hilfs-)Tabelle basteln können.

Wäre sowas eine Option?

Dann müstest Du Dich aber mit VBA gut genug auskennen um evtl. angebotene Lösungsvorschläge selber an Deine Originaldatei anpassen zu können, oder Du müsstest hier Deine Originaldatei hochladen (evtl. ohne sensible Daten), damit sich die helfenden genau darauf beziehen können.

 

Danke Achim!

Das war mir wirklich eine große Hilfe. VBAs ist nicht unbedingt meine Sache. Ich nehme die Makros in alter Weise auf.

Ich kann gerne nochmal die original Datei hochladen. Es sind eh nur Zahlen enthalten, die wenig aussagekräftig ohne Hintergrundwissen sind.

 

Hallo Florian,

ich/wir benötige/n noch ein paar Infos:

- ist das wirklich Dein original? Oder hast Du mal eben ein irgendwelche Zahlen irgendwo eingesetzt?
- warum steht in B4 eine Zahl und kein "x" ? Arnstadt schuldet sich selber 5031 Packmittel ??!!??
- warum stehen in der senkrechten Auflistung der Orte andere als in der waagerechten? Die beiden Listen stimmen nicht überein!
- was soll Spalte "AI" (Leer) ? Dort stehen Zahlen... aber wohin gehören Sie?
- sind das eventuell die Daten bevor Du sie in Pivotform sortiert hast? Falls ja, dann sortier bitte erstmal, und komm dann mit einer Datei.
- sind das alle orte, oder kann sich die Liste erweitern, sprich muss die Datei dynamisch werden?

Wenn mir noch etwas einfallen sollte (oder jemand anderem)... ich weiß (wir wissen) ja wo Du liest... ;O)


Edit:

- wie kommen die Zahlen in Deine Liste? Trägst Du sie von Hand ein? Werden sie irgendwie eingelesen? Oder werden sie durch eine Verweis von einem anderen Blatt übertragen?

 

Das ist ein Excel Auszug aus unserem internen Programm. Die pivotierte Form ist zu groß, deswegen habe ich es rauskopiert. Die Zahlen stimmen. Über die Form habe ich keine Verfügung.

Internes Problem, welches wir am lösen sind.

Weil manche Geschäftsstellen an Orten Einheiten haben, die Orte aber nicht bei den Geschäftsstellen. Hat auch keine Relevanz.

ignorieren

Wie gesagt, pivotiert zu groß.

Wird mit sicherheit passieren. In welchem Rahmen ist unklar.

Die Daten werden regelmäßig aus dem internen Programm eingelesen.

 

Das ist ein KO-Argument. Zur Verarbeitung einer Matrix mit "ziehbarer" Formel müssen Zeilen und Spalten sich gegenseitig entsprechen. Wenn ein Ort im anderen nichts hat, steht dort eben "0", aber die Spalte muß vorhanden sein.

Gruß, Michael

 

Hallo Eierhals,

ich fasse mal ein wenig zusammen:
- die Daten kommen in unregelmäßige und "chaotischer" Form aus einem externen Programm.
- es gibt in diesem externen Programm, oder beim Auslesen und transformieren der Daten Anzeige oder sonstige Probleme, die erst noch gelöst werden müssten.
- dieses externe Programm zeigt die Kombinationen Geschäfsstellen/Orte nur in "beiden Richtungen" an, wenn auch in beiden Richtungen unausgeglichene Salden vorhanden sind. Und das hat serwohl Relevanz, sehr sogar.
- es gibt eine Spalte (Leer) die ignoriert werden soll
- die pivotierte Form ist zu groß... "grübel", also bietest Du uns die Daten unsortiert an
- die Liste und ihre Auswertung muss dynamisch bleiben, da sich die Orte, bzw. ihre Anzahl verändern kann


Hm...

Ich sehe es genauso wie miriki.
Die Tabelle muss einen Regelmäßigen Aufbau haben, wie willst Du sonst irgendwelche Bezüge definieren?
Allerdings habe ich den Eindruck, dass Du eine Lösung suchst den importierten Datensalat automatisch sortieren zu lassen.
Das wäre auch die Voraussetzung für eine Auswertung.
Allerdings war davon bisher nie die Rede.

Die Voraussetzungen sind in meinen Augen "zum davonlaufen".
Ich vermute, das es irgendwie möglich ist, aber das erfordert, so glaube ich, sehr umfangreiches und kompliziertes VBA.
Die Erstellung wäre sehr zeitaufwendig und fehleranfällig.
Das ist nicht "mal eben" in einer halben Stunde erledigt.
Ich persönlich würde vermutlich (abends nach Feierabend) Wochen dafür brauchen, falls ich es überhaupt schaffen würde.
Meinen Rahmen von Hilfe in einem Forum sprengt soetwas auf jeden Fall.

Weiterhin vermute ich, das Du bei einer größeren Firma arbeitest, wenn ich mir die Anzahl der Geschäftsstellen und Orte so anschaue.
Da sollte es doch entweder firmeninterne IT-Menschen geben, oder die Flocken für ein passendes Programm (fertig oder Auftragsarbeit) sollten irgendwie drinn sein.

Sorry wenn ich mich irren sollte, das ist halt mein Eindruck.
Und nimm meine "offenen" Worte bitte nicht persönlich, ich bin halt so.