Microsoft Excel

Herbers Excel/VBA-Archiv

Hilfe bei Stückliste | Herbers Excel-Forum


Betrifft: Hilfe bei Stückliste von: Benny
Geschrieben am: 29.01.2012 17:11:41

Hi!

Bin neu hier und habe nur Grundlegende Kenntnisse im Bereich Excel...bin jedoch ehrgeizig wenn's um das Lösen von Problemen geht.
Das bringt mich zu einer kleinen Frage an die Excel Experten:

Ich habe im Zuge meiner Weiterbildung zum Maschinenbautechniker und dem momentan laufenden Projekt zur Realisierung einer Fertigung von Fahrrädern folgende Aufgabe bekommen:

Es existieren Stücklisten, die in einzelne Baugruppen unterteilt sind. Nun habe ich mir zu Ziel gesetzt eine Exceltabelle anzulegen, die mir ausgibt in welcher Menge ich die einzelnen Bauteile bestellten muss.

Das Problem liegt nun darin, dass manche Einzelteile z.B. "Sechkantmutter DIN ... M6" sowohl in der Baugruppe "Vormontage Bremssattel vorne" als auch in Baugruppe "Vormontage Bremssattel hinten" vorkommen.

Ich möchte aber natürlich nur die gesamte zu bestellenden Anzahl ausgegeben bekommen.
Habe mich da schon einige Zeit mit befasst und mich durch einige Formeln durchgehangelt...zum gewünschten Ergebnis bin ich jedoch nicht gekommen.

Vielleicht kann mir ja jemand auf die Sprünge helfen...am besten ohne VBA...da hab ich mich noch nie mit beschäftigt.

Lieben Gruß und schonmal danke für eure Mühe!

Benny

Hier die Datei:

https://www.herber.de/bbs/user/78644.xlsx

  

Betrifft: AW: Hilfe bei Stückliste von: Hajo_Zi
Geschrieben am: 29.01.2012 17:34:27

Hallo Benny,

Du solltest auf verbunden Zellen Verzichten und gleichen Artikeln die gleiche Nummer zuweisen.

Bestellung Baugruppen

 I
670
7 
870
970
10140
11140
1270
1335
1435
15140
16210
17140
1870
19 
20 
21 
2235
2335
24 
25 
26 
27 
2835
2935
3035
31 
32 
33 
34 

verwendete Formeln
Zelle Formel Bereich
I6 =WENN(B6<>"";WENN(ZÄHLENWENN($B$6:B6;B6)=1;SUMMENPRODUKT((B6:B34=B6)*(E6:E34));"");WENN(ZÄHLENWENN($C$6:C6;C6)=1;SUMMENPRODUKT((C6:C34=C6)*(E6:E34));"")) 
I7 =WENN(B7<>"";WENN(ZÄHLENWENN($B$6:B7;B7)=1;SUMMENPRODUKT((B7:B35=B7)*(E7:E35));"");WENN(ZÄHLENWENN($C$6:C7;C7)=1;SUMMENPRODUKT((C7:C35=C7)*(E7:E35));"")) 
I8 =WENN(B8<>"";WENN(ZÄHLENWENN($B$6:B8;B8)=1;SUMMENPRODUKT((B8:B36=B8)*(E8:E36));"");WENN(ZÄHLENWENN($C$6:C8;C8)=1;SUMMENPRODUKT((C8:C36=C8)*(E8:E36));"")) 
I9 =WENN(B9<>"";WENN(ZÄHLENWENN($B$6:B9;B9)=1;SUMMENPRODUKT((B9:B37=B9)*(E9:E37));"");WENN(ZÄHLENWENN($C$6:C9;C9)=1;SUMMENPRODUKT((C9:C37=C9)*(E9:E37));"")) 
I10 =WENN(B10<>"";WENN(ZÄHLENWENN($B$6:B10;B10)=1;SUMMENPRODUKT((B10:B38=B10)*(E10:E38));"");WENN(ZÄHLENWENN($C$6:C10;C10)=1;SUMMENPRODUKT((C10:C38=C10)*(E10:E38));"")) 
I11 =WENN(B11<>"";WENN(ZÄHLENWENN($B$6:B11;B11)=1;SUMMENPRODUKT((B11:B39=B11)*(E11:E39));"");WENN(ZÄHLENWENN($C$6:C11;C11)=1;SUMMENPRODUKT((C11:C39=C11)*(E11:E39));"")) 
I12 =WENN(B12<>"";WENN(ZÄHLENWENN($B$6:B12;B12)=1;SUMMENPRODUKT((B12:B40=B12)*(E12:E40));"");WENN(ZÄHLENWENN($C$6:C12;C12)=1;SUMMENPRODUKT((C12:C40=C12)*(E12:E40));"")) 
I13 =WENN(B13<>"";WENN(ZÄHLENWENN($B$6:B13;B13)=1;SUMMENPRODUKT((B13:B41=B13)*(E13:E41));"");WENN(ZÄHLENWENN($C$6:C13;C13)=1;SUMMENPRODUKT((C13:C41=C13)*(E13:E41));"")) 
I14 =WENN(B14<>"";WENN(ZÄHLENWENN($B$6:B14;B14)=1;SUMMENPRODUKT((B14:B42=B14)*(E14:E42));"");WENN(ZÄHLENWENN($C$6:C14;C14)=1;SUMMENPRODUKT((C14:C42=C14)*(E14:E42));"")) 
I15 =WENN(B15<>"";WENN(ZÄHLENWENN($B$6:B15;B15)=1;SUMMENPRODUKT((B15:B43=B15)*(E15:E43));"");WENN(ZÄHLENWENN($C$6:C15;C15)=1;SUMMENPRODUKT((C15:C43=C15)*(E15:E43));"")) 
I16 =WENN(B16<>"";WENN(ZÄHLENWENN($B$6:B16;B16)=1;SUMMENPRODUKT((B16:B44=B16)*(E16:E44));"");WENN(ZÄHLENWENN($C$6:C16;C16)=1;SUMMENPRODUKT((C16:C44=C16)*(E16:E44));"")) 
I17 =WENN(B17<>"";WENN(ZÄHLENWENN($B$6:B17;B17)=1;SUMMENPRODUKT((B17:B45=B17)*(E17:E45));"");WENN(ZÄHLENWENN($C$6:C17;C17)=1;SUMMENPRODUKT((C17:C45=C17)*(E17:E45));"")) 
I18 =WENN(B18<>"";WENN(ZÄHLENWENN($B$6:B18;B18)=1;SUMMENPRODUKT((B18:B46=B18)*(E18:E46));"");WENN(ZÄHLENWENN($C$6:C18;C18)=1;SUMMENPRODUKT((C18:C46=C18)*(E18:E46));"")) 
I19 =WENN(B19<>"";WENN(ZÄHLENWENN($B$6:B19;B19)=1;SUMMENPRODUKT((B19:B47=B19)*(E19:E47));"");WENN(ZÄHLENWENN($C$6:C19;C19)=1;SUMMENPRODUKT((C19:C47=C19)*(E19:E47));"")) 
I20 =WENN(B20<>"";WENN(ZÄHLENWENN($B$6:B20;B20)=1;SUMMENPRODUKT((B20:B48=B20)*(E20:E48));"");WENN(ZÄHLENWENN($C$6:C20;C20)=1;SUMMENPRODUKT((C20:C48=C20)*(E20:E48));"")) 
I21 =WENN(B21<>"";WENN(ZÄHLENWENN($B$6:B21;B21)=1;SUMMENPRODUKT((B21:B49=B21)*(E21:E49));"");WENN(ZÄHLENWENN($C$6:C21;C21)=1;SUMMENPRODUKT((C21:C49=C21)*(E21:E49));"")) 
I22 =WENN(B22<>"";WENN(ZÄHLENWENN($B$6:B22;B22)=1;SUMMENPRODUKT((B22:B50=B22)*(E22:E50));"");WENN(ZÄHLENWENN($C$6:C22;C22)=1;SUMMENPRODUKT((C22:C50=C22)*(E22:E50));"")) 
I23 =WENN(B23<>"";WENN(ZÄHLENWENN($B$6:B23;B23)=1;SUMMENPRODUKT((B23:B51=B23)*(E23:E51));"");WENN(ZÄHLENWENN($C$6:C23;C23)=1;SUMMENPRODUKT((C23:C51=C23)*(E23:E51));"")) 
I24 =WENN(B24<>"";WENN(ZÄHLENWENN($B$6:B24;B24)=1;SUMMENPRODUKT((B24:B52=B24)*(E24:E52));"");WENN(ZÄHLENWENN($C$6:C24;C24)=1;SUMMENPRODUKT((C24:C52=C24)*(E24:E52));"")) 
I25 =WENN(B25<>"";WENN(ZÄHLENWENN($B$6:B25;B25)=1;SUMMENPRODUKT((B25:B53=B25)*(E25:E53));"");WENN(ZÄHLENWENN($C$6:C25;C25)=1;SUMMENPRODUKT((C25:C53=C25)*(E25:E53));"")) 
I26 =WENN(B26<>"";WENN(ZÄHLENWENN($B$6:B26;B26)=1;SUMMENPRODUKT((B26:B54=B26)*(E26:E54));"");WENN(ZÄHLENWENN($C$6:C26;C26)=1;SUMMENPRODUKT((C26:C54=C26)*(E26:E54));"")) 
I27 =WENN(B27<>"";WENN(ZÄHLENWENN($B$6:B27;B27)=1;SUMMENPRODUKT((B27:B55=B27)*(E27:E55));"");WENN(ZÄHLENWENN($C$6:C27;C27)=1;SUMMENPRODUKT((C27:C55=C27)*(E27:E55));"")) 
I28 =WENN(B28<>"";WENN(ZÄHLENWENN($B$6:B28;B28)=1;SUMMENPRODUKT((B28:B56=B28)*(E28:E56));"");WENN(ZÄHLENWENN($C$6:C28;C28)=1;SUMMENPRODUKT((C28:C56=C28)*(E28:E56));"")) 
I29 =WENN(B29<>"";WENN(ZÄHLENWENN($B$6:B29;B29)=1;SUMMENPRODUKT((B29:B57=B29)*(E29:E57));"");WENN(ZÄHLENWENN($C$6:C29;C29)=1;SUMMENPRODUKT((C29:C57=C29)*(E29:E57));"")) 
I30 =WENN(B30<>"";WENN(ZÄHLENWENN($B$6:B30;B30)=1;SUMMENPRODUKT((B30:B58=B30)*(E30:E58));"");WENN(ZÄHLENWENN($C$6:C30;C30)=1;SUMMENPRODUKT((C30:C58=C30)*(E30:E58));"")) 
I31 =WENN(B31<>"";WENN(ZÄHLENWENN($B$6:B31;B31)=1;SUMMENPRODUKT((B31:B59=B31)*(E31:E59));"");WENN(ZÄHLENWENN($C$6:C31;C31)=1;SUMMENPRODUKT((C31:C59=C31)*(E31:E59));"")) 
I32 =WENN(B32<>"";WENN(ZÄHLENWENN($B$6:B32;B32)=1;SUMMENPRODUKT((B32:B60=B32)*(E32:E60));"");WENN(ZÄHLENWENN($C$6:C32;C32)=1;SUMMENPRODUKT((C32:C60=C32)*(E32:E60));"")) 
I33 =WENN(B33<>"";WENN(ZÄHLENWENN($B$6:B33;B33)=1;SUMMENPRODUKT((B33:B61=B33)*(E33:E61));"");WENN(ZÄHLENWENN($C$6:C33;C33)=1;SUMMENPRODUKT((C33:C61=C33)*(E33:E61));"")) 
I34 =WENN(B34<>"";WENN(ZÄHLENWENN($B$6:B34;B34)=1;SUMMENPRODUKT((B34:B62=B34)*(E34:E62));"");WENN(ZÄHLENWENN($C$6:C34;C34)=1;SUMMENPRODUKT((C34:C62=C34)*(E34:E62));"")) 

Tabellendarstellung in Foren Version 5.48



Gruß Hajo


  

Betrifft: AW: Hilfe bei Stückliste von: Benny
Geschrieben am: 29.01.2012 18:52:56

Klappt super, danke!

Ich würd das Ganze nur gern nachvollziehen...hab's nur nicht ganz geschnallt.
Kannst du mir kurz erläutern wie du vorgegangen bist?!


  

Betrifft: AW: Hilfe bei Stückliste von: Hajo_Zi
Geschrieben am: 29.01.2012 19:13:13

Hallo Benny,

hebe die verbundenen Zellen auf, Krigiere die Nummern und kopiere die Formel´.

Gruß Hajo


  

Betrifft: AW: Hilfe bei Stückliste von: Benny
Geschrieben am: 29.01.2012 19:17:30

Hab ja schon alles in meine Tabelle übertragen, klappt wunderbar...

Komme nur nicht hinter das "Geheimnis" deiner Formel...versuche zu verstehen wie und warum das gewünschte Ergebnis erreicht wird...

Will mich ja weiterentwickeln und nicht nur kopieren! :-)


  

Betrifft: AW: Hilfe bei Stückliste von: Hajo_Zi
Geschrieben am: 29.01.2012 19:20:26

Hallo Benny,

es ist eine Summenproduktformel(). Zu erst wird geprüft ob die eine Zelle leer ist um festzustellen welche Spalte die Grundlage ist. Außen herum ist noch Zählenwenn, damitt die Summe nur beim ersten Artikel erscheint.


Gruß Hajo


  

Betrifft: AW: Hilfe bei Stückliste von: Benny
Geschrieben am: 29.01.2012 19:25:38

Mhhh, vielleicht muss ich mir das morgen nochmal anschauen....verstehs irgendwie nicht!

SUMMENPRODUKT((B12:B40=B12)*(E12:E40));"")

Kannst du mir den Teil der Formel noch etwas genauer erläutern?!

Danke für deine Geduld


  

Betrifft: AW: Hilfe bei Stückliste von: Hajo_Zi
Geschrieben am: 29.01.2012 19:30:10

Hallo Benny,

die Formel ist
SUMMENPRODUKT((B12:B40=B12)*(E12:E40))
Summenprodukt ist die Multiplikation von Wahrheitswerten. Schaue in Spalte B nach ob gleich B12 ((wahr oder Flasch) multipliziere dies mit dem Wert aus Spalte E und bilde die Summe. Der Wert mal Wahr(1) = der Wert, derr Wert mal Falsch (0) ist 0.

Gruß Hajo