PDA

Vollständige Version anzeigen : Zusammenstellung


MasterP84
12.04.2012, 11:31
Schönen guten Tag,
ich benutze leider noch Excel 2003. Nun hab ich folgendes Problem: Ich möchte gerne eine Materialzusammenstellung schaffen. Beispiel ich habe ein Tabellenblatt mit Muttern M4,M6,M8,M10,M12 darunter ne Zeile mit den entsprechenden Preisen pro Mutter. Noch eine Zeile darunter kann ich eine Stückzahl eingeben. Nun möchte ich das Excel auf einem neuen Tabellenblatt eine Materialliste macht mit einer Auflistung:

3 Muttern M6 Preis x
6 Muttern M8 Preis y
3 Schrauben M6 Preis w

Ist das so automatisch möglich??
Vielen Dank für eure Hilfe

Rudi Maintaire
13.04.2012, 12:01
Hallo,
so?
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='46,5pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='left' style='color:#0; ' >Was</td><td align='left' style='color:#0; ' >Preis</td><td align='left' style='color:#0; ' >Menge</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' >M6</td><td align='right' style='color:#0; ' >*</td><td align='left' style='color:#0; ' >M6</td><td align='right' style='color:#0; ' >1,15</td><td align='right' style='color:#0; ' >3</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' >1,15</td><td align='right' style='color:#0; ' >*</td><td align='left' style='color:#0; ' >M8</td><td align='right' style='color:#0; ' >1,25</td><td align='right' style='color:#0; ' >12</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='right' style='color:#0; ' >3</td><td align='right' style='color:#0; ' >*</td><td align='left' style='color:#0; ' >M10</td><td align='right' style='color:#0; ' >1,5</td><td align='right' style='color:#0; ' >20</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='left' style='color:#0; ' >M8</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >0</td><td align='right' style='color:#0; ' >0</td><td align='right' style='color:#0; ' >0</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='right' style='color:#0; ' >1,25</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >0</td><td align='right' style='color:#0; ' >0</td><td align='right' style='color:#0; ' >0</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='right' style='color:#0; ' >12</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='left' style='color:#0; ' >M10</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='right' style='color:#0; ' >1,5</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='right' style='color:#0; ' >20</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td><td align='right' style='color:#0; ' >*</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td>C2</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>($A:$A;</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>($A1)</Span><Span style='color:#222222'>-1)</Span><Span style='color:#0000DD'>*3+SPALTE</Span><Span style='color:#222222'>(B$1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr><tr><td>D2</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>($A:$A;</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>($A1)</Span><Span style='color:#222222'>-1)</Span><Span style='color:#0000DD'>*3+SPALTE</Span><Span style='color:#222222'>(C$1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr><tr><td>E2</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>($A:$A;</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>($A1)</Span><Span style='color:#222222'>-1)</Span><Span style='color:#0000DD'>*3+SPALTE</Span><Span style='color:#222222'>(D$1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr><tr><td>C3</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>($A:$A;</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>($A2)</Span><Span style='color:#222222'>-1)</Span><Span style='color:#0000DD'>*3+SPALTE</Span><Span style='color:#222222'>(B$1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr><tr><td>D3</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>($A:$A;</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>($A2)</Span><Span style='color:#222222'>-1)</Span><Span style='color:#0000DD'>*3+SPALTE</Span><Span style='color:#222222'>(C$1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr><tr><td>E3</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>($A:$A;</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>($A2)</Span><Span style='color:#222222'>-1)</Span><Span style='color:#0000DD'>*3+SPALTE</Span><Span style='color:#222222'>(D$1)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr></table>

Gruß
Rudi