Excel Tabellenblätter verbinden, wobei gleiche Werte oft vorkommen

[NYC]

Hallo liebe Computerbase-Comunity,

ich stehe seit Tagen vor einer ziemlich großen Herausforderung. Kurze Schilderung des Themas:
Ich habe zwei Tabellenblätter: 1. Überbestände, 2. Fehlmengen. Das erste Blatt zeigt mir, in welchen Filialen von welchen Artikeln zu viel auf Lager liegt und vor allem wie viel zu viel auf Lager liegt. Das zweite Blatt zeigt mir, in welchen Filialen von welchen Artikeln zu wenig auf Lager liegt und wie viel (Anzahl) für einen optimalen Warenbestand benötigt/gebraucht wird.

Die Herausforderung: Nun gilt es, diese beiden Tabellenblätte so miteinander zu verknüpfen, dass mir Excel dann zeigt, in welche Filiale ich eine Artikel aus dem Überbestand umlagern soll. Beispiel: Filiale XY hat von Produkt A zu viel auf Lager. Ergebnis sollte sein: Filiale XY soll von Produkt A 10 Stück in Filiale VW umlagern.

Problem: Logischer Weise kommen sowohl die Filialnnummern öfters vor (eine Filiale hat von mehreren Artikeln zu viel/wenig auf Lager) als auch die Artikelnummern öfters vor (mehrere Filialen haben von dem selben Produkt zu viel/wenig auf Lager).

Ich steh seit ein Paar Tagen echt an und weiß überhaupt nicht mehr weiter. Ich wäre sooooo mega dankbar, wenn mir wer von euch bei diesem Problem helfen könnte.

Danke schon mal im Voraus für eure Tipps und Tricks

 

[ms007]

Stell mal ein Muster ein, damit man die Struktur der Tabellen sieht ...

 

[PHuV]

So was macht man eigentlich über Datenbanken und nicht Excel.

 

[bieneneber]

Guten Morgen,

ich glaube, das ist nicht ganz trivial und lässt sich ohne Makro wohl kaum automatisiert lösen. Besser wäre es, das ganze via Datenbank zu lösen. Aber Excel wird sowieso gerne für alles andere Missbraucht.

Du könntest auf jeweils einem weiteren Blatt, die über/unterbestände entweder via Pivot oder summewenn() je Artikel summieren. Dann musst du jeweils je Artikel und Filiale die Über/Unterbestände aufsummieren (weitere Tabellenblätter) und sortierst diese Absteigend je menge und pro Artikel. und nun verknüpfst du das ganze:
Via sverweis() auf artikelnummer aus der Überbestandssumme hängst du die bestände an die unterbestandsdaten an und subtrahierst die sukzessive. dies könnte ungefähr so aussehen:

[table="width: 500, class: grid, align: left"]
[tr]
[td]Artikel[/td]
[td]Filiale[/td]
[td]Überbestand Artikel (sverweis)[/td]
[td]Unterbestand Artikel per Filiale (sverweis)[/td]
[td]Ausgleich[/td]
[td]Restbestand Artikel (einfache berechnung)[/td]
[/tr]
[tr]
[td]Artikel_1[/td]
[td]Filiale_A[/td]
[td]+1000[/td]
[td]-200[/td]
[td]+200[/td]
[td]+800[/td]
[/tr]
[tr]
[td]Artikel_1[/td]
[td]Filiale_B[/td]
[td]+1000[/td]
[td]-300[/td]
[td]+300[/td]
[td]+500[/td]
[/tr]
[tr]
[td]Artikel_1[/td]
[td]Filiale_C[/td]
[td]+1000[/td]
[td]-100[/td]
[td]+100[/td]
[td]+400[/td]
[/tr]
[/table]
usw.

Dadurch kannst du nun relativ einfach durch Formeln schon die Bestandsaufteilung ermitteln und siehst, wieviel Artikel du in die Filialen liefern musst.

In einem weiteren Schritt musst du die Überbestände je Filiale und Artikel dazu mappen. Dies funktioniert ähnlich: via sverweis() auf Artikel+Filiale ziehst du solange die Überbestände ab, bis der Bedarf der Filiale gedeckt ist (und natürlich die Überbestände aufgebraucht sind). Aus der Kombination von Unterbestands_Filiale und Überbestands_Filiale erhältst du deine Umlagerungsanweisung.

Viel Glück!

 

[NYC]

Hallo

Ich hab den Auftrag, das ganze im Excel zu schaffen...leider. Darum will ich jetzt alles versuchen was geht und wenn ich es nicht schaffe bis ende August, dann kann ich nichts mehr daran ändern

Hier mal zwei Fotos mit Beispielen:
Überbestand:


Fehlmengen:


Das erste Tabellenblatt hat ca 2000 Zeilen und das zweite ca 350.000 Zeilen. Man sieht schön, dass natürlich sowohl Filialen als auch Artikel in beiden Blättern öfter vorkommen!!

Danke bieneneber für deinen Ausführlichen Tipp! Ich werde mal versuchen, ob ich das so hinbekomme (falls nicht dann werd ich mich nochmal melden wenn ich darf )

Weitere Anregungen und Tipps nehme ich gerne entgegen Danke euch allen!!!
LG

 

[ms007]

M.E. ist die Anwendungslogik noch längst nicht hinreichend exakt beschrieben:

Nehmen wir mal in Filiale 32 den Artikel 33455. Wie sollen denn die benötigten 20 Exemplare aus den übrigen Filialen zugeordnet werden, wenn da über alle Filialen ein Überbestand von 50 Exemplaren besteht? Welche Filiale darf ihren Überbestand abliefern, welche bleibt darauf sitzen?
Analog anders herum: Der Bedarf über alle Filialen ist größer als der Überbestand. Bei wem wird ausgeglichen, wer muss warten?
...

 

[NYC]

Hallo mx007,

tut mir leid für meine nicht so ausgereifte Beschreibung, aber es fällt mir nicht leicht, das Thema so gut zu beschreiben, dass sich jeder damit auskennt. Ich bin jetzt schon über 1 Monat in diesem Thema drin und gebe mein bestes, dieses so gut wie möglich darzustellen.

Also grundsätzlich werden die Fehlmengen von jener Filiale mit dem höchsten Überbestand aufgefüllt. Und wenn diese Filiale nichts mehr hat, dann wird jene mit dem nächsthöheren Überbestand herangezogen. Selbes Schema gilt bei den Fehlmengen. Es werden zuerst jene Filialen abgefertigt, welche die größten Fehlmengen aufweisen.

LG

 

[Palomino]

Der Vorschlag von bieneneber ist ganz gut, nur würde ich einen etwas anderen Aufbau wählen:


[table="width: 500, class: grid"]
[tr]
[td]Artikel[/td]
[td]Filiale A
Über-/Unterbestand[/td]
[td]Filiale B
Über-/Unterbestand[/td]
[td]Filiale C
Über-/Unterbestand[/td]
[td]Summe alle Bestände[/td]
[/tr]
[tr]
[td]001[/td]
[td]200[/td]
[td]-50[/td]
[td]-30[/td]
[td]120[/td]
[/tr]
[tr]
[td]002[/td]
[td]50[/td]
[td]-80[/td]
[td]0[/td]
[td]-30[/td]
[/tr]
[tr]
[td]003[/td]
[td]-25[/td]
[td]40[/td]
[td]-15[/td]
[td]0[/td]
[/tr]
[/table]

Für jeden Artikel hast Du in jeder Filiale entweder einen Über-, oder einen Unterbestand. So könntest Du übersichtlich für jeden Artikel sehen, wo sich diese Bestände befinden. Die letzte Spalte zeigt Dir an, ob Du genug Überbestand hast, um alle Filiale auszugleichen, oder nicht.

Die Entscheidung, von welcher Filiale wohin umlagert, wirst Du wohl manuell treffen müssen.

 

[NYC]

Gute Idee Palomino, danke

Wenn ich nun sowohl ein Blatt mit genau diesem Aufbau für die Überbestände und eines für die Fehlmenge habe, wie schaffe ich es dann, diese zu Verbinden, damit ich weiß, welche Filiale (Überbestand) ihre Menge in welche andere Filiale (Fehlmenge) umlagern soll?

Vielen Dank Palomino, werd ich gleich mal ausprobieren und mit der Chefin abklären wenn ichs geschafft habe

 

[Palomino]

Ich würde den gesa,ten Aufbau in einem Blatt vornehmen. Du hast ja bereits 2 Blätter mit den Über- unf den UNterbeständen.
Um die Daten zu ziehen bräuchtest Du eine Formel mit 2 Bedingungen, Artikel uhd Filiale, damit Du den richtigen Wert erhälst. Hier ist das ganz gut beschrieben: http://www.excelformeln.de/formeln.html?welcher=30

Ich würde dann unter jeder Filiale 2 Spalten einbauen, eine für die Abfrage Überbestand, und eine für Unterbestand. Der Wert, der nicht zutrifft, ist dann logischerweise Null.

 

[ms007]

Wenn da nur die auszugleichenden Über- bzw. Fehlmengen in der Tabellenstruktur wie von Palomino vorgeschlagen drin stehen, erübrigt sich das doch. Das steht doch direkt in dem Blatt drin.

Was ist den die Quelle deiner aktuellen zwei Tabellenblätter?

 

[NYC]

Ich beziehe die Daten aus 2 verschiedenen Data-Warehouse Berichten

 

[ms007]

Kannst du aus diesen Berichten das EINE Tabellenblatt wie von Palomino vorgeschlagen erzeugen?

 

[NYC]

Ja, das müsste eigentlich kein Problem sein, sowohl für den Überbestand als auch für die Fehlmengen, da muss ich nur ein Attribut von der Zeile in die Spalte verschieben

 

[NYC]

Hallo
Nur für jene, die wissen möchten, wie ich mein Problem nun gelöst habe:

Nachdem ich schon fleißig im Excel gearbeitet habe (mit der Lösung von Palomino) hat sich die Datei leider letzten Freitag nicht mehr öffnen lassen. Die komplette Datei war kaputt und nicht mal die IT konnte eine Rücksicherung machen. Also war alles weg und ich komplett verzweifelt. In meiner Verzweiflung kam mir jedoch die Idee Ich hab die komplette Geschichte vom Excel in unserem Data Warehouse nachgebaut und nach einem langen Meeting mit meinem Arbeitskollegen konnte der perfekte Bericht gebaut werden.
Es gibt nun nur mehr 1 Metrik, die mir angibt ob ich einen Überbestand oder eine Fehlmenge von einem Artikel in einer Filiale habe. Dadurch konnte ich schon viele Daten reduzieren.
Der Bericht muss jetzt nur mehr ins Excel exportiert werden, um besser damit weiterarbeiten zu können! Aber in meinen Augen einfach die beste Lösung!

Möchte mich nochmal bei allen bedanken, die mir bei diesem Problem geholfen haben!!!