Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
26.04.2024 16:05:28
PivotChart-Stapeldiagramm erstellen
26.04.2024 15:34:42
Überlagernde Textbox-Ansicht
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1812to1816
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1812to1816
1812to1816
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Übersicht Lagerkoordinaten

Übersicht Lagerkoordinaten
15.02.2021 08:15:54
Toastie
Hallo Excel-Experten
Ich stehe vor einem Excelproblem (Excel 2010) bei dem ihr mir sicherlich weiterhelfen könnt. Es geht um folgende Arbeitsmappe und diese Bedingungen (Beispielmappe habe ich erstellt):
https://www.herber.de/bbs/user/143921.xlsx
Bedingungen:
1) In einer Arbeitsmappe gibt es mehrere Tabellenblätter pro Lager
2) Jedes Lager führt mehrere Materialien und ordnet einen Lagerplatz zu
3) Pro Material kann es in einem Lager nur einen Platz geben
4) Ein Material kann in mehreren Lagern vorkommen
Anforderung:
5) Im letzten Arbeitsblatt soll eine Übersicht aller Materialien entstehen
6) Dabei soll zusätzlich pro Material der Lagerplatz von allen Lagern ergänzt werden (ohne doppelte Lagerplätze) und kann maximal 9 Zeichen lang sein
Mir ist der Lösungsansatz schon nicht klar. Lässt sich das Problem mit einer intelligenten Excelformel lösen? Oder brauche ich ein Makro?

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: gemäß Deinen Vorgaben ...
15.02.2021 09:11:46
neopa
Hallo Toastie,
... geh ich davon aus, dass es nur 3 Lagerplätze sind und das Material durch Nummern repräsentiert ist.
Folgender Lösungsvorschlag dafür ergibt zwar eine etwas andere Reihenfolge des Ergebnislisting, die aber mE untergeordenet sein dürfte:
- ersetze in A1: "Material" durch 0 mit benutzerdefinierten Zahlenformat: ;;"Material"
- dann folgende Formel in A2:
=WENNFEHLER(WENNFEHLER(WENNFEHLER(AGGREGAT(15;6;'Lager A'!A$2:A$99/(ZÄHLENWENN(A$1:A1;'Lager A'!A$2:A$99)=0);1);AGGREGAT(15;6;'Lager B'!A$2:A$99/(ZÄHLENWENN(A$1:A1;'Lager B'!A$2:A$99)=0);1));AGGREGAT(15;6;'Lager C'!A$2:A$99/(ZÄHLENWENN(A$1:A1;'Lager C'!A$2:A$99)=0);1));"")
- abschließend folgende Formel in B2
=WENNFEHLER(SVERWEIS(A2;'Lager A'!A:B;2;0);"")&WENNFEHLER(SVERWEIS(A2;'Lager B'!A:B;2;0);"")&WENNFEHLER(SVERWEIS(A2;'Lager C'!A:B;2;0);"")
Formeln A2:B2 weit genug nach unten ziehend kopieren.
Sollten Deine Materialien anstelle durch Zahlen mit Textwerten bezeichnet sein, so wird in der Formel für A2 noch jeweils 3x INDEX() und ZEILE() notwendig.
Gruß Werner
.. , - ...
Anzeige
AW: gemäß Deinen Vorgaben ...
16.02.2021 12:38:53
Toastie
Ich sage jetzt einfach mal nur "WOW!!!". Die Formel ist Mega-Komplex und ohne deine Hilfe hätte ich das nie im Leben hinbekommen. Genau das löst mein Problem!
Die Original-Exceldatei ist natürlich um ein vielfaches komplexer und umfangreicher als die Beispieldatei. Daher hat jetzt meine Rückmeldung etwas gedauert weil ich das Gelernte erst verstehen musste. Aber mit deinen Erläuterungen war ich in der Lage diese Lösung nicht nur anzuwenden sondern auch auf eine komplexere Struktur anzuwenden.
Mit Deiner Vermutung lagst du übrigens absolut richtig. "Material" ist ein numerischer Wert, so dass ich mir zum Glück INDEX() und ZEILE() sparen konnte.
Einziger Schönheitsfehler - und auch das hast du bereits vorhergesehen - ist die unsortierte Reihenfolge des Ergebnislisting. Mal angenommen ich würde das sortieren wollen: Wie gehe ich am besten vor?
1) Das fertige Ergebnislisting in 3er Schritten zu zerhacken und neu zusammenzusetzen (eine Koordinate besteht immer nur aus 3 Zeichen)
2) Die Formel umstricken und das ganze komplett neu machen
Ich würde jetzt mit meinem laienhaften Verständnis eher 1) wählen.
Anzeige
AW: ich schlage 3.) vor ...
16.02.2021 13:04:05
neopa
Hallo Toastie,
... und zwar wie folgt. Verschiebe die Formel A1:A## nach einer freien Hilfsspalte, z.B. Spalte H
Dann kopiere danach H1 nach A1 und in A2 folgende Formel:
=WENNFEHLER(AGGREGAT(15;6;H$2:H$99/(H$2:H$99&gtA1);1);"")
und diese nach unten ziehend kopieren.
In der Formel in B2 ersetze H2 durch A2 und kopiere diese Formel nach unten.
Spalte H kannst Du ausblenden.
Gruß Werner
.. , - ...
AW: ich schlage 3.) vor ...
16.02.2021 13:49:23
Toastie
Funktioniert leider nicht. Aber ich bin selber schuld weil ich beim erweitern der Formel irgendwo einen Fehler gemacht habe den ich aber nicht entdecke.
Würdest du mir bei der ursprünglichen Formel nochmal helfen? Ich habe in der Originaldatei nämlich 5 Tabellenblätter (Lager A, Lager B, Lager C, Lager V, Lager W) mit jeweils maximal 500 Zeilen.

=WENNFEHLER(WENNFEHLER(WENNFEHLER(WENNFEHLER(WENNFEHLER(AGGREGAT(15;6; 'Lager A'!A$2:A$500/(ZÄHLENWENN(B$1:B1;'Lager A'!A$2:A$500)=0);1);AGGREGAT(15;6; 'Lager B'!A$2:A$500/(ZÄHLENWENN(B$1:B1;'Lager B'!A$2:A$500)=0);1));AGGREGAT(15;6; 'Lager C'!A$2:A$500/(ZÄHLENWENN(B$1:B1;'Lager C'!A$2:A$500)=0);1)*AGGREGAT(15;6; 'Lager V'!A$2:A$500/(ZÄHLENWENN(B$1:B1;'Lager V'!A$2:A$500)=0);1)*AGGREGAT(15;6; 'Lager W'!A$2:A$500/(ZÄHLENWENN(B$1:B1;'Lager W'!A$2:A$500)=0);1));"")

Anzeige
AW: ich schlage 3.) vor ...
16.02.2021 14:57:57
Toastie
Ups, Häkchen vergessen...
AW: wenn 5 Tabellenblätter ...
16.02.2021 15:11:39
neopa
Hallo Toasti,
... wäre eine Nicht-Formellösung geeigneter. Dir steht aber in Deiner XL-Version kein PowerQuery zur Verfügung, oder?
Dann in H2:
=WENNFEHLER(WENNFEHLER(WENNFEHLER(WENNFEHLER(WENNFEHLER(AGGREGAT(15;6;'Lager A'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager A'!A$2:A$500)=0);1);AGGREGAT(15;6;'Lager B'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager B'!A$2:A$500)=0);1));AGGREGAT(15;6;'Lager C'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager C'!A$2:A$500)=0);1));AGGREGAT(15;6;'Lager V'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager V'!A$2:A$500)=0);1));AGGREGAT(15;6;'Lager W'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager W'!A$2:A$500)=0);1));"")
und diese musste in de Beispieldatei bis Zeile 720 herunter kopiert werden.
Also vorsichtshalber solltest Du diese bis Zeile ca. 999 kopieren.
Dann in A2:

=WENNFEHLER(AGGREGAT(15;6;H$2:H$999/(H$2:H$999&gtA1);1);"")
und nach unten kopieren.
Die Formel in B2 entsprechend auch erweitern.
Gruß Werner
.. , - ...
Anzeige
AW: wenn 5 Tabellenblätter ...
16.02.2021 15:48:41
Toastie
Fehler dank DIR behoben. Aber ich glaube es gab ein Missverständnis. Denn ich wollte die Koordinate inhaltlich sortieren und nicht die Spalte nach Materialnummer.
Also z.B. P87D57M85 sollte heißen D57M85P87 (die Koordinaten werden aufsteigend angegeben). Ist aber wie gesagt nur ein Schönheitsfehler. Denn inhaltlich passt die Information. Drum war meine Überlegung falls ich das sortieren wollen würde vielleicht nach Variante 1 vorzugehen. Dabei habe ich wohl das falsche Wording verwendet. Ich meinte:
1) Das fertige Ergebnis der Zelle wieder in 3er Schritten zu zerhacken und sortiert neu zusammenzusetzen (eine Koordinate besteht immer nur aus 3 Zeichen). Anbei das verbesserte (aber noch unsortierte) Ergebnis.
https://www.herber.de/bbs/user/143969.xlsx
Anzeige
AW: ist auch möglich ...
16.02.2021 16:09:09
neopa
Hallo Toastie,
... in Deiner XL-Version würde ich so vorgehen, dass ich die 5 Koordinaten je Materialnummer in 5 Hilfsspalten übernehmen würde und diese dann mit INDEX() und wahrscheinlich auch AGGREGAT() und ZÄHLENWENN() sortiert in die Ergebniszelle übernehmen und da mit &(9 verketten würde.
Muss jetzt allerdings erst einmal offline gehen.
Gruß Werner
.. , - ...
AW: ist auch möglich ...
16.02.2021 16:20:14
Toastie
Ich probiere einfach mal rum. Besten Dank für deine ganze Hilfe. Du hast mir SEHR geholfen. Ich wünsche Dir einen schönen Abend!
AW: nur probieren wird da wohl kaum reichen ...
16.02.2021 18:34:47
neopa
Hallo Toastie,
... um so besser jedoch, wenn Du damit zum Erfolg kommst.
Ich hätte jedoch dazu noch folgende Frage:
Gilt Deine ursprünglich getroffene Festlegung auch hier, wonach max 3 "Koordinaten" sich je Materialnummer ergeben?
Gruß Werner
.. , - ...
Anzeige
AW: nur probieren wird da wohl kaum reichen ...
17.02.2021 08:05:26
Toastie
Hi Werner
ich muss mich mit AGGREGAT INDEX ZÄHLENWENN unbedingt mehr befassen. Weil was du damit hinbekommen hast, ist echt klasse!
Gestern Abend habe ich noch "rumprobiert" aber bin ziemlich schnell an meine Grenzen gestoßen. Ich habe nur den simplen Part mit den Hilfsspalten geschafft (siehe Anhang).
https://www.herber.de/bbs/user/143988.xlsx
Nun fehlt das "sortierte Zusammensetzen" der einzelnen Koordinaten. Und Ja, die ursprüngliche Festlegung gilt auch weiterhin: Pro Material kann es bedingt wegen einer maximalen Feldlänge (10 Zeichen) im ERP System nur max. 3 Koordinaten hintereinander geben. Das ist die Schwachstelle dieses ganzen Systems aber ist ein anderes Thema und wird vorerst auch so bleiben müssen.
Anzeige
AW: nun dann ...
17.02.2021 09:35:33
neopa
Hallo Toastie,
... nachfolgend mein Vorschlag hierzu. Alle Formeln (die für A und H hattest Du ja schon) weit genug nach unten (wahrscheinlich max bis Zeile 999) ziehend kopieren.
Aber Achtung hierbei kommt der PC beim ersten berechnen etwas ins schwitzen.
Wie ich bereits schrieb, wäre eine PowerQuery-Lösung hier effizienter.
Diese gab es (kostenfrei) von MS als AddIn auch mal für Excel2010, wird jetzt aber nicht mehr so ohne weiteres, weil ja XL2010 von MS kein Support mehr gibt.
Arbeitsblatt mit dem Namen 'Lagerplatzkoordinate final'
 ABHIJKLM
1MaterialKoordinatenMaterialABCVW
2180N32821    N32
3821M81P83822P83   M81
4822D57M85P87835P87D57  M85
5832N46836    N46
19981M43P741117P74   M43
201012A22M261118  A22 M26
451553D611843 D61   
4615861844  A13 N24
471627D36M471858 D36  M47

ZelleFormel
A2=WENNFEHLER(AGGREGAT(15;6;H$2:H$2500/(H$2:H$2500>A1);1);"")
B2=WENNFEHLER(INDEX(2:2;AGGREGAT(15;6;SPALTE(I2:M2)/(ZÄHLENWENN(I2:M2;"&gt="&I2:M2)=3);1));"")&WENNFEHLER(INDEX(2:2;AGGREGAT(15;6;SPALTE(I2:M2)/(ZÄHLENWENN(I2:M2;"&gt="&I2:M2)=2);1));"")&WENNFEHLER(INDEX(2:2;AGGREGAT(15;6;SPALTE(I2:M2)/(ZÄHLENWENN(I2:M2;"&gt="&I2:M2)=1);1));"")
H2=WENNFEHLER(WENNFEHLER(WENNFEHLER(WENNFEHLER(WENNFEHLER(AGGREGAT(15;6;'Lager A'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager A'!A$2:A$500)=0);1);AGGREGAT(15;6;'Lager B'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager B'!A$2:A$500)=0);1));AGGREGAT(15;6;'Lager C'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager C'!A$2:A$500)=0);1));AGGREGAT(15;6;'Lager V'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager V'!A$2:A$500)=0);1));AGGREGAT(15;6;'Lager W'!A$2:A$500/(ZÄHLENWENN(H$1:H1;'Lager W'!A$2:A$500)=0);1));"")
I2=WENN(A2="";"";WENNFEHLER(SVERWEIS(A2;'Lager A'!A:B;2;0);""))
J2=WENN(A2="";"";WENNFEHLER(SVERWEIS(A2;'Lager B'!A:B;2;0);""))
K2=WENN(A2="";"";WENNFEHLER(SVERWEIS(A2;'Lager C'!A:B;2;0);""))
L2=WENN(A2="";"";WENNFEHLER(SVERWEIS(A2;'Lager V'!A:B;2;0);""))
M2=WENN(A2="";"";WENNFEHLER(SVERWEIS(A2;'Lager W'!A:B;2;0);""))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ..
Anzeige
AW: nun dann ...
17.02.2021 10:32:52
Toastie
Hi Werner
Ich kann gar nicht so viel feiern wie du mir Gründe dafür lieferst! Also besser kann das Endresultat nicht aussehen. Lange Rede kurzer Sinn: Wie von "Geisterhand" habe ich jetzt sortierte Koordinaten.
Für jeden Bearbeitungsschritt oder bei einer neuen Beispieldatei hatte ich eine neue Dateiversion angelegt. Ich entferne bei temporären Dateien immer die Formeln und lass nur die Werte drin stehen. Das ist so eine Angewohnheit von mir weil ich Excel i.d.R. mehr als "Importmedium" für unser ERP System nutze. Die Darstellung der einzelnen Formeln in den jeweiligen Zellen hat mir jedesmal sehr geholfen um das Ergebnis neu aufzubauen. Nochmal vielen Dank dass du dir so viel Mühe gemacht hast um mir jedesmal einen korrekten Input zu geben!
PS: Das AddIn PowerQuery hatte ich mir mal heruntergeladen und importiert. Allerdings habe ich mich nie wirklich damit beschäftigt weil es bisher komplett ohne geklappt hat. Auch diesesmal waren die Laufzeiten noch in Ordnung.
Anzeige
AW: gern, bitteschön owT
17.02.2021 11:01:14
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige