Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1400to1404
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
Inhaltsverzeichnis

Bestimmte Zeilen aus Stückliste kopieren

Bestimmte Zeilen aus Stückliste kopieren
30.12.2014 11:42:38
wimkn
Hallo,
Ich habe eine Datei mit mehreren hundert Stücklisten untereinander. Eine Stückliste hat meistens mehrere Stufen, was die Unterbaugruppen darstellt.
Ich habe das Beispiel Skateboard genommen.
Ein Skateboard Typ x ist das Kopfteil. Es besteht aus einem Fahrwerk und Oberteil. Wiederum besteht das Fahrwerk aus Achse und Rand. Das Oberteil besteht aus Brett und Schrauben.
Skateboard Typ y, z usw. sind ähnlich aufgebaut, hat aber andere Materialien. Außer die Schrauben mit der Materialnummer 343 sind dieselben.Jetzt würde ich gerne alle Spalten kopiert haben, in der die Schrauben drin vorkommen. In meinem Beispiel sind es die Spalten mit der Schraube selbst und bei Skateboard x z.B. die Spalten mit Oberteil blau, da in dieser Baugruppe die Schraube verwendet wird. Das erkennt man daran, das Oberteil in Stufe 1 ist und die Schrauben direkt danach in Stufe 2 kommen.
Ein weiteres Merkmal ist der Arbeitsplatz. Spalten mit Schrauben bzw. Baugruppen wo Schrauben drin vorkommen haben immer Arbeitsplatz 2.
Zur Übersichtlichkeit habe ich die Stücklisten farblich unterschieden.
Ich hoffe das Beispiel ist halbwegs nachvollziehbar.
Mein Ansatz sieht so aus: Excel soll in allen Stücklisten die Materialnummer 343 suchen in Spalte C. Danach auf die nächst höhere Stufe in Spalte B gehen, die auch Arbeitsplatz 2 hat. Aber nur auf die nächst höhere!!!
Vielen herzlichen Dank im Voraus.
https://www.dropbox.com/s/of9zxxhjxupuijs/ST%C3%BCLi%20BSP.xlsx?dl=0

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bestimmte Zeilen aus Stückliste kopieren
30.12.2014 11:47:25
wimkn
Anbei noch ein Bild von einer vereinfachten Datei. Unter den Stücklisten sieht man die kopierten Zeilen.
Userbild

Matrixformelmix aus VERWEIS(), INDEX() und ...
30.12.2014 12:31:19
neopa
Hallo,
... ich geh davon aus, dass Du die Datensätze mit der Schraube nicht noch extra mit ausgeben werden muß und das beim SB Z die Arbeitsplatznummer in I16 nicht 5 sondern eine 2 sein soll. Oder? Anderenfalls stimmen Deine verbale Angaben nicht.
Dann folgende Formel Einfach nach unten und rechts kopieren:
 BCDEF
1Bauteile an den die Schraube 343 befestigt wird
2Bezeichnung GesamtbauteilStufePos.MaterialBezeichnung Material
3Skateboard x.16022Oberteil rot
4Skateboard y.140146Oberteil blau
5SB Z..230757Räder schwarz
6SB Z…2120444Bremse schwarz
7SB XV.120235Brett
8SB Mix.140146Oberteil blau
9SB HHH…2120444Bremse schwarz
10SBB.110653Brett
11     

Formeln der Tabelle
ZelleFormel
B3{=WENNFEHLER(VERWEIS(9;1/(Tabelle1!I$1:INDEX(Tabelle1!$I:$I;KKLEINSTE(WENN(Tabelle1!$E$1:$E$9999=343;ZEILE(Z$1:Z$9999)); ZEILE(Z1))-1)=2); Tabelle1!B:B); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: Matrixformelmix aus VERWEIS(), INDEX() und ...
31.12.2014 15:21:36
wimkn
Hallo Werner,
vielen Dank für die sehr schnelle Antwort. Fast genau so wollte ich es haben. Aber die Schraube an sich sollte auch kopiert werden.
Weshalb meinst du, dass bei SB Z die Arbeitsplatznummer in I16 nicht 5 sondern eine 2 sein sollte?
LG WIMKN

die Formel setzt Sortierung voraus ...
01.01.2015 12:05:20
neopa
Hallo,
... in allen anderen SB, außer eben "SB Z", ist die Pos. der Schraube der Pos. des übergeordneten Bauteils nachgeordnet und steht in der momentanen Liste dementsprechend vor der Schraube innerhalb des entsprechenden SB-Datenbereiches. Wenn Du das nicht garantieren kannst, wird die Formel etwas aufwendiger. Wie stellt sich das bei Dir nun wirklich dar?
Wenn das geklärt ist, kann man die Formel auch so konstruieren, dass auch immer die Schraube mit ausgegeben wird, nur wird die Formel dann noch etwas komplexer.
Gruß Werner
.. , - ...

Anzeige
AW: die Formel setzt Sortierung voraus ...
02.01.2015 13:46:27
wimkn
Hallo Werner,
sorry du hast natürlich recht. I16 muss auch Arbeitsplatz 2 sein. I14 sogar auch. In der Realität zieht sich das immer bis in Stufe 1. Habe die Beispielstückliste nicht richtig durchdacht. Danke für den Hinweis!
Und Schraube selbst sollte auch mit ausgegeben werden.
Vielen Dank nochmal!!
LG WIMKN

wenn dem so ist, dann ...
02.01.2015 19:29:11
neopa
Hallo WIMKN
... dann folgende Formel nach rechts und unten kopieren:
 ABCDEF
1 Bauteile an den die Schraube 343 befestigt wird
2 Bezeichnung GesamtbauteilStufePos.MaterialBezeichnung Material
3 Skateboard x.16022Oberteil rot
4 Skateboard x..2100343Schrauben
5 Skateboard y.140146Oberteil blau
6 Skateboard y..260343Schrauben
7 SB Z..230757Räder schwarz
8 SB Z…370343Schrauben
9 SB Z…2120444Bremse schwarz
10 SB Z…3140343Schrauben
11 SB XV.120235Brett
12 SB XV.130343Schrauben
13 SB Mix.140146Oberteil blau
14 SB Mix..260343Schrauben
15 SB HHH…2120444Bremse schwarz
16 SB HHH…3140343Schrauben
17 SBB.110653Brett
18 SBB..220343Schraube
19      

Formeln der Tabelle
ZelleFormel
B3{=WENN(ZEILE(Z1)>ZÄHLENWENN(Tabelle1!$E:$E;343)*2;"";WENN(REST(ZEILE(Z1); 2)=0;INDEX(Tabelle1!B:B;KKLEINSTE(WENN(Tabelle1!$E$1:$E$9999=343;ZEILE(Z$1:Z$9999)); KÜRZEN((ZEILE(Z1)-1)/2)+1)); WENNFEHLER(VERWEIS(9;1/(Tabelle1!I$1:INDEX(Tabelle1!$I:$I;KKLEINSTE(WENN(Tabelle1!$E$1:$E$9999=343;ZEILE(Y$1:Y$9999)); KÜRZEN((ZEILE(Z1)-1)/2)+1)-1)=2); Tabelle1!B:B); "")))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: wenn dem so ist, dann ...
03.01.2015 16:48:14
wimkn
Hallo Werner,
vielen dank.
Habe die Formel jetzt in B55 kopiert. NAch unten kopiren ist kein problem, nach rechts funktioniert es nicht richtig. Da kopiert er immer Skateboard x Anstatt Stufe, Positionsnummer usw.
Müsste man die Formal sher abändern, wenn man die bestimmten Zeilen in Tabellenbltt 2 ausgegeben haben möchte?
Ist es möglich das auch Spalte A, Material Gesamtbauteil, mit ausgegeben wird?

AW: wenn dem so ist, dann ...
04.01.2015 16:20:06
wimkn
Hallo,
also es funktioniert jetzt soweit. Habe die Formel so geändert, dass auch Spalte A mit ausgegeben wird.
Das einzigste, was nich klapp ist, dass die Spalten bis I mit ausgegeben werden. Es geht nur bis D. Bei E wird dann wieder der Inhalt von A kopiert.

Anzeige
kann ich so nicht nachvollziehen, denn ...
04.01.2015 17:34:38
neopa
Hallo wimkn,
... wenn Du nachfolgende Formel nach rechts und links sowie nach unten kopierst, bekommst Du alle Werte. Wobei in G und H in der Beispieldatei nichts stand und in Spalte I der Ergebniswerte ja sowieso immer die 2 erscheinen muss:
 ABCDEFI
53 Bauteile an den die Schraube 343 befestigt wird 
54MaterialBezeichnung GesamtbauteilStufePos.MaterialBezeichnung Material 
55100Skateboard x.16022Oberteil rot2
56100Skateboard x..2100343Schrauben2
57200Skateboard y.140146Oberteil blau2
58200Skateboard y..260343Schrauben2
59500SB Z..230757Räder schwarz2
60500SB Z…370343Schrauben2
61500SB Z…2120444Bremse schwarz2
62500SB Z…3140343Schrauben2
63600SB XV.120235Brett2
64600SB XV.130343Schrauben2
65800SB Mix.140146Oberteil blau2
66800SB Mix..260343Schrauben2
671000SB HHH…2120444Bremse schwarz2
681000SB HHH…3140343Schrauben2
692000SBB.110653Brett2
702000SBB..220343Schraube2

Formeln der Tabelle
ZelleFormel
B55{=WENN(ZEILE(Z1)>ZÄHLENWENN(Tabelle1!$E:$E;343)*2;"";WENN(REST(ZEILE(Z1); 2)=0;INDEX(Tabelle1!B:B;KKLEINSTE(WENN(Tabelle1!$E$1:$E$9999=343;ZEILE(Z$1:Z$9999)); KÜRZEN((ZEILE(Z1)-1)/2)+1)); WENNFEHLER(VERWEIS(9;1/(Tabelle1!$I$1:INDEX(Tabelle1!$I:$I;KKLEINSTE(WENN(Tabelle1!$E$1:$E$9999=343;ZEILE(Z$1:Z$9999)); KÜRZEN((ZEILE(Z1)-1)/2)+1)-1)=2); Tabelle1!B:B); "")))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: kann ich so nicht nachvollziehen, denn ...
04.01.2015 20:01:30
wimkn
Hallo,
entschuldigung dass ich nochmal nerve. Aber irgend etwas mach ich falsch. Es gibt mir in Spalte E nicht die Materialnummer aus, sondern nur Skateboard x.

AW: kann ich so nicht nachvollziehen, denn ...
05.01.2015 07:21:53
wimkn
Guten Morgen Werner,
habe jetzt die Formel in die originale Datei kopiert. Funktioniert soweit auch. Aber nach wie vor kann nur bis Spalte D. Bin echt ratlos, deshalb schreibe ich mal, wie ich vorgehe:
Ich kopiere die Formel in die Spalte B unterhalb des Stücklisten. Danach klicke ich die Zelle mit der Formel an, fahre auf die rechte untere Ecke bis die Maus zu einem schmalen Kreuz wird. Danach ziehe ich es nach rechts. Bei Spalte C wird dann die Stufe ausgegeben, bei D die Positionsnummer. Bei E wird dannn wieder die Bezeichnung des Gesamtbauteiles ausgegeben.
Vielleicht mache ich was grundlegendes falsch.
Vielen dank nochmal.

Anzeige
AW: kann ich so nicht nachvollziehen, denn ...
05.01.2015 14:45:06
wimkn
bzw. es kommt dann teilweise auch folgende Info:
Eine Formel kann von MS Office Excel nicht berechnet werden. Es gibt ein Zirkelverweis in einer geöffneten Arbeitsmappe, aber in diesem Fall können Bezüge, durch die der Zirkelverweis entsteht, nicht angezeigt werden. Versuchen Sie die letzt eingegebene Formel zu bearbeiten, oder entfernen Sie sie mit dem Befehl Rückgängig.

die Formel ist korrekt ...vielleicht ...
05.01.2015 14:57:26
neopa
Hallo,
... stellst Du einfach die Datei nochmal hier ein, die den Fehler (der offensichtlich eine andere Ursache hat). Notfalls kannst Du diese mir auch mal direkt zumailen.
Gruß Werner
.. , - ...

Anzeige
AW: die Formel ist korrekt ...vielleicht ...
05.01.2015 15:40:33
wimkn
Hallo,
anbei die Datei.
https://www.dropbox.com/s/of9zxxhjxupuijs/ST%C3%BCLi%20BSP.xlsx?dl=0

Du wolltest Die Auswertung doch in Tabelle 2 ...
05.01.2015 15:46:08
neopa
Hallo,
... wenn Du die in der gleichen Tabelle vornimmst, dann muss die Formel angepasst werden. Sonst gibt es natürlich den Zirkelbezug. Soll die Auswertung nun doch in Tabelle 1 vorgenommen werden?
Gruß Werner
.. , - ...

AW: Du wolltest Die Auswertung doch in Tabelle 2 ...
05.01.2015 15:49:52
wimkn
Achso ok im prinzip ist es egal. Dann Versuch ichs mal in Tabelle 2.
Danke

AW: Du wolltest Die Auswertung doch in Tabelle 2 ...
05.01.2015 16:07:18
wimkn
Also in Tabelle 1 funktioniert es. Sorry!!!
Vielen Dank nochmal!
Wäre es sehr aufwändig die einzelnen Teile der Formel ganz grob zu erklären? Also welcher Teil was macht? Kann nicht alles ganz nachvollziehen.

Anzeige
die gewünschte Formelerklärung ...
05.01.2015 16:45:33
neopa
Hallo,
... werde ich nicht vornehmen können und wollen, da der Aufwand dafür für mich viel zu groß wäre. Denn da sind sehr viel Erfahrungen eingeflossen, die sich nicht mit wenigen Worten vermitteln lassen.
Gruß Werner
.. , - ...

AW: die gewünschte Formelerklärung ...
05.01.2015 17:50:19
wimkn
Hallo Werner,
ja klar das ist verständlich. Du hast mir schon mehr als genug geholfen.
Wie kann man sich in solche komplexen Sachen reinfinden? Gibt es Eklärungen für zumindest einfache Ausgaben von Zeilen?
LG und Danke

Formelasuwertung in der gleichen Tabelle ...
05.01.2015 15:52:51
neopa
Hallo,
... dann für B56 so:
 ABCDEFGHI
502000SBB.14055256Achse  1
512000SBB.150256Rolle  8
52         
53         
54         
55         
56100Skateboard x.16022Oberteil rot002
57100Skateboard x..2100343Schrauben002
58200Skateboard y.140146Oberteil blau002
59200Skateboard y..260343Schrauben002
60200Skateboard y..260343Schrauben002
61500SB Z…370343Schrauben002
62500SB Z…2120444Bremse schwarz002
63500SB Z…3140343Schrauben002
64600SB XV.120235Brett002
65600SB XV.130343Schrauben002
66800SB Mix.140146Oberteil blau002
67800SB Mix..260343Schrauben002
681000SB HHH…2120444Bremse schwarz002
691000SB HHH…3140343Schrauben002
702000SBB.110653Brett002
712000SBB..220343Schraube002
72         

Formeln der Tabelle
ZelleFormel
B56{=WENN(ZEILE(Z1)>ZÄHLENWENN(Tabelle1!$E$1:$E$54;343)*2;"";WENN(REST(ZEILE(Z1); 2)=0;INDEX(Tabelle1!B:B;KKLEINSTE(WENN(Tabelle1!$E$1:$E$54=343;ZEILE(Z$1:Z$54)); KÜRZEN((ZEILE(Z1)-1)/2)+1)); WENNFEHLER(VERWEIS(9;1/(Tabelle1!$I$1:INDEX(Tabelle1!$I:$I;KKLEINSTE(WENN(Tabelle1!$E$1:$E$54=343;ZEILE(Z$1:Z$54)); KÜRZEN((ZEILE(Z1)-1)/2)+1)-1)=2); Tabelle1!B:B); "")))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: Formelasuwertung in der gleichen Tabelle ...
05.01.2015 16:08:07
wimkn
Sehr nett, danke für deine Mühe und Geduld!

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige