PDA

Vollständige Version anzeigen : Erweiterte Summenprodukt-Funktion


vanruiten
06.07.2006, 13:33
Hallo,
ich möchte aus einer Tabelle für bestimmte Kennziffern (hier: Woche) Summenprodukte (hier: Menge Abgang x Prop.Kosten) ermitteln. Habe dies bereits mit den Funtionen Summewenn, Wenn und Sverweis versucht, bin aber zu keinem Ergebnis gekommen.
Kann mir jemand weiterhelfen ?
Nebenbei: kann man komplette Spalten nicht als Matrix definieren ?`

Hier ein paar Rohdaten:
Woche Jahr ArtikelNr ArtikelNrLang1 Artikelbezeichnung Mindestbestand Summe von Menge Abgang Prop_Kosten
1 06 51169966 0285-0637/RN01-0000 2-L-PE-DURCHG.-KL.35 QMM FV GRü/GELB 6000 33 173,689
1 06 51170431 0870-0107/RN01-0000 1L-1P-DSK-COMP-KL.,4xPE GRÜN/GELB 0 52 58,707
1 06 51173949 0400-0415/0415-0124/RN01-0000 DURCHGANGSKLEMME 35 QMM GRAU 6500 75 88
1 06 51174384 0284-0621/0999-0991/RN01-0000 FV-VERTEILER-KLEMME 35QMM/3X10QMM GR 45000 21 183,678
1 06 51175515 0859-0390/RN01-0000 RELAISKLEMME 16,8-30VDC 1U 3A 6000 33 288,43
1 06 51176350 0281-0611/0281-0541/RN01-0000 SI-KLEMME M.SCHWENKB.SI-HALTER 5X20 13000 120 80,438
10 06 01200244 0120-0244 SCHRAUBE M4X8 ZYL. ST.VZ. DIN 84 5000 60 0,31
10 06 01210244 0121-0244 U-SCHEIBE 4,3 DIN 125 10000 60 0,14
10 06 01310210 0131-0210 SCHRAUBE M6X16 FLACHR.ST.VZ. DIN 603 0 480 1,33
10 06 01320210 0132-0210 U-SCHEIBE DIN 125A 8,4MM ST.VERZ. 0 280 0,41
10 06 01540287 0154-0287 DRAHT H05 V-K 1 QMM SCHWARZ 0 0,5 3,7
10 06 01580308 0158-0308 WEIDM.-MONTAGEFUSS FM4 (068790) 3500 60 33,47

Hajo_Zi
06.07.2006, 13:35
Hallo Hans Bärbel <img src="http://smilie-land.de/t/i-l/lachen/lachen0048.gif">,

in diesem Forum können auch Dateien hochgeladen werden.
Versuche es mit Summenprodukt

<img src="http://home.media-n.de/ziplies/images/grusz1.gif" align="middle" height="30" alt="Grußformel"><a href="http://home.media-n.de/ziplies/" target="_blank"><img border="0" src="http://home.media-n.de/ziplies/images/logo_hajo.gif" align="middle" height="30" alt="Homepage"></a>
In Foren gilt allgemein das Du. Alle Leute, die ich duze, spreche ich mit dem Vornamen an. Ich habe jetzt mal meinen Antwortstil umgestellt: in Beiträgen, in denen kein Name steht, werden die Personen mit "Hans Bärbel" angeredet, da liege ich zumindest beim Geschlecht mit 50% richtig.

vanruiten
06.07.2006, 13:42
Hallo Hajo,

mein Name ist André :-)

Summenprodukt ist schon klar, aber die Datenmenge kann sich verändern, also möchte ich z.B. das Ergebnis für Woche "1". Das Ergebnis wird in einer anderen Tabelle dann komprimiert angezeigt und in eine Grafik verwandelt.
Kannst Du weiterhelfen ? danke.

Gruß
André

myfake
06.07.2006, 14:02
Hallo Andre,

mal schauen, ob ich Deine Tabelle richtig konvertiert habe ;)

<P><blockquote><table border=3 cellpadding=1><tr><td>&nbsp;</td><td align=center bgcolor=#C0C0C0>A</td><td align=center bgcolor=#C0C0C0>B</td><td align=center bgcolor=#C0C0C0>C</td><td align=center bgcolor=#C0C0C0>D</td><td align=center bgcolor=#C0C0C0>E</td><td align=center bgcolor=#C0C0C0>F</td><td align=center bgcolor=#C0C0C0>G</td><td align=center bgcolor=#C0C0C0>H</td><td align=center bgcolor=#C0C0C0>I</td><td align=center bgcolor=#C0C0C0>J</td><td align=center bgcolor=#C0C0C0>K</td></tr><tr><td align=center bgcolor=#C0C0C0>1</td><td>Woche</td><td>Jahr</td><td>ArtikelNr</td><td>ArtikelNrLang1</td><td>Artikelbezeichnung</td><td>Mindestbestand</td><td>Summe</td><td>von</td><td>Menge</td><td>Abgang</td><td>Prop_Kosten</td></tr><tr><td align=center bgcolor=#C0C0C0>2</td><td>1</td><td>6</td><td>51169966</td><td>0285-0637/RN01-0000</td><td>2-L-PE-DURCHG.-KL.35&nbsp;QMM&nbsp;FV&nbsp;Gr&uuml;n/Gelb</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>6.000</td><td>33</td><td>173,689</td></tr><tr><td align=center bgcolor=#C0C0C0>3</td><td>1</td><td>6</td><td>51170431</td><td>0870-0107/RN01-0000</td><td>1L-1P-DSK-COMP-KL.,4xPE&nbsp;Gr&uuml;n/Gelb</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>0</td><td>52</td><td>58,707</td></tr><tr><td align=center bgcolor=#C0C0C0>4</td><td>1</td><td>6</td><td>51173949</td><td>0400-0415/0415-0124/RN01-0000</td><td>DURCHGANGSKLEMME&nbsp;35&nbsp;QMM&nbsp;Grau</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>6.500</td><td>75</td><td>88</td></tr><tr><td align=center bgcolor=#C0C0C0>5</td><td>1</td><td>6</td><td>51174384</td><td>0284-0621/0999-0991/RN01-0000</td><td>FV-VERTEILER-KLEMME&nbsp;35QMM/3X10QMM&nbsp;GR</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>45.000</td><td>21</td><td>183,678</td></tr><tr><td align=center bgcolor=#C0C0C0>6</td><td>1</td><td>6</td><td>51175515</td><td>0859-0390/RN01-0000</td><td>RELAISKLEMME&nbsp;16,8-30VDC&nbsp;1U&nbsp;3A</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>6.000</td><td>33</td><td>288,43</td></tr><tr><td align=center bgcolor=#C0C0C0>7</td><td>1</td><td>6</td><td>51176350</td><td>0281-0611/0281-0541/RN01-0000</td><td>SI-KLEMME&nbsp;M.SCHWENKB.SI-HALTER&nbsp;5X20</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>13.000</td><td>120</td><td>80,438</td></tr><tr><td align=center bgcolor=#C0C0C0>8</td><td>10</td><td>6</td><td>1200244</td><td>0120-0244</td><td>SCHRAUBE&nbsp;M4X8&nbsp;Zyl.&nbsp;St.Vz&nbsp;Din&nbsp;84</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>5.000</td><td>60</td><td>0,31</td></tr><tr><td align=center bgcolor=#C0C0C0>9</td><td>10</td><td>6</td><td>1210244</td><td>0121-0244</td><td>U-SCHEIBE&nbsp;4,3&nbsp;Din&nbsp;125</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>10.000</td><td>60</td><td>0,14</td></tr><tr><td align=center bgcolor=#C0C0C0>10</td><td>10</td><td>6</td><td>1310210</td><td>0131-0210</td><td>SCHRAUBE&nbsp;M6X16&nbsp;FLACHR.ST.VZ.&nbsp;Din&nbsp;603</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>0</td><td>480</td><td>1,33</td></tr><tr><td align=center bgcolor=#C0C0C0>11</td><td>10</td><td>6</td><td>1320210</td><td>0132-0210</td><td>U-SCHEIBE</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>0</td><td>280</td><td>0,41</td></tr><tr><td align=center bgcolor=#C0C0C0>12</td><td>10</td><td>6</td><td>1540287</td><td>0154-0287</td><td>DRAHT&nbsp;H05&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>0</td><td>0,5</td><td>3,7</td></tr><tr><td align=center bgcolor=#C0C0C0>13</td><td>10</td><td>6</td><td>1580308</td><td>0158-0308</td><td>WEIDM.-MONTAGEFUSS&nbsp;FM4-68790</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>3.500</td><td>60</td><td>33,47</td></tr><tr><td align=center bgcolor=#C0C0C0>14</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td align=center bgcolor=#C0C0C0>15</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td align=center bgcolor=#C0C0C0>16</td><td>Summe&nbsp;Woche</td><td>&nbsp;</td><td>1</td><td>38.412,49</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr></td></tr></table></blockquote></P>

Formel in D16: =SUMMENPRODUKT((A2:A13=C16)*(J2:J13)*(K2:K13))
In C16 gibst Du die Woche an, die Du summieren willst.

cu
myfake

vanruiten
06.07.2006, 14:14
Hi myfake,

das sieht schon gut aus, aber...
die Matrix hat 10520 Datensätze zur Zeit und kann sich verändern (Import aus Access).
Und ich möchte die Berechnung in einer neuen Tabelle erstellen, da kommt bei mir immer als Ergebnis "#Wert!".

=SUMMENPRODUKT(('Materialabgang pro KW'!A1:A10520=A4*'Materialabgang pro KW'!G1:G10520*'Materialabgang pro KW'!H1:H10520))

André

myfake
06.07.2006, 14:17
Hallo Andre,

sollte kein Problem sein - wenn die Klammern gesetzt werden ;)

Versuche es mal so:
=SUMMENPRODUKT(('Materialabgang pro KW'!A1:A10520=A4)*('Materialabgang pro KW'!G1:G10520)*('Materialabgang pro KW'!H1:H10520))

cu
myfake

Hajo_Zi
06.07.2006, 14:18
Hallo André,

siehe Dir mal die vorgeschlagene Formel in Bezug auf die Klammern an und falls A4 in der Quelltabelle ist muß dort auch der Bezug hin.

<a href="http://home.media-n.de/ziplies/" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

vanruiten
06.07.2006, 14:24
Sorry, funktioniert bei mir nicht.
Bin noch nicht lange dabei: wie kriege ich die Tabelle eingefügt ?
Dann ist das ein wenig einfacher, denke ich.

Hajo_Zi
06.07.2006, 14:28
Hallo André,

für Tabelle im Beitrag gibt es einige Programme im INternet.
Falls Du eine Datei hochladen willst, benutze das erweiterte Antwortformular dort ist ein Punkt zum hochladen.

<a href="http://home.media-n.de/ziplies/" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>
Sieht ein Beitrag mit Anrede und Gruß nicht gleich viel persönlicher aus?

vanruiten
06.07.2006, 14:36
Hallo Hajo,

ich hoffe, es geht auch so...

Diese Daten möchte ich in der neuen Tabelle auswerten.

KW Summe
1 #WERT!
2 SUMMENPRODUKT(('Materialabgang pro KW'!A1:A10520=A4);('Materialabgang pro KW'!G1:G10520)*('Materialabgang pro KW'!H1:H10520))
3
4
5
6
7
8
9
10

Gruß
André

myfake
06.07.2006, 14:41
Hallo Andre,

auf meine Bsp.-Datei übertragen sieht das so aus:
<P><blockquote><table border=3 cellpadding=1><tr><td>&nbsp;</td><td align=center bgcolor=#C0C0C0>A</td><td align=center bgcolor=#C0C0C0>B</td></tr><tr><td align=center bgcolor=#C0C0C0>1</td><td>KW</td><td>Summe</td></tr><tr><td align=center bgcolor=#C0C0C0>2</td><td align=center>1</td><td>38.412,49</td></tr><tr><td align=center bgcolor=#C0C0C0>3</td><td align=center>2</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>4</td><td align=center>3</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>5</td><td align=center>4</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>6</td><td align=center>5</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>7</td><td align=center>6</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>8</td><td align=center>7</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>9</td><td align=center>8</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>10</td><td align=center>9</td><td>0,00</td></tr><tr><td align=center bgcolor=#C0C0C0>11</td><td>10</td><td>2.790,25</td></tr></td></tr></table></blockquote></P>

Formel in B2: =SUMMENPRODUKT((Tabelle2!$A$2:$A$13=A2)*(Tabelle2!$J$2:$J$13)*(Tabelle2!$K$2:$K$ 13))

Deine Formel in B2 sollte dann vermutlich so aussehen:
=SUMMENPRODUKT(('Materialabgang pro KW'!$A$1:$A$10520=A2)*('Materialabgang pro KW'!$G$1:$G$10520)*('Materialabgang pro KW'!$H$1:$H$10520))

cu
myfake

Hajo_Zi
06.07.2006, 14:43
Hallo André,

Deine Formel ist falsch, dort ist ein ; anstelle von *.

<a href="http://home.media-n.de/ziplies/" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

vanruiten
06.07.2006, 14:55
Hallo myfake,

hier scheint es ein echtes Problem zu geben: jetzt bekomme ich 0,00€ heraus.
Ich habe deine obige Formel kopiert und übertragen, so daß ich kein Fehler mit den Klammern oder Zeichen machen konnte.
Mal schauen wo das Problem liegt.

Gruß
André

myfake
06.07.2006, 14:57
Hallo Andre,

wo bekommst Du denn 0,00€ heraus? In allen (Ergebnis-)Zellen?

cu
myfake

vanruiten
06.07.2006, 14:59
Hallo myfake,

ja, in allen Zellen.

Gruß
André

myfake
06.07.2006, 15:09
Hallo Andre,

o.k. - in Ermangelung einer Datei kann ich halt nur raten....

Ist meine Übertragung Deiner Tabelle (Post #4) so korrekt? In Deiner Formel werden nämlich die Spalten G und H miteinander multipliziert und in denen steht nichts drin. Setz Deinen Cursor mal auf eine der Zellen, markiere dann "('Materialabgang pro KW'!$G$1:$G$10520)" und drücke F9 (das Selbe dann nochmal mit "('Materialabgang pro KW'!$H$1:$H$10520)". Was wird Dir dann angezeigt? <- es müssen Zahlen erscheinen!

Sollte es das nicht sein, wäre es doch sehr gut, wenn Du eine abgespeckte Datei hochlädst.

cu
myfake

vanruiten
06.07.2006, 15:11
Hallo myfake,

die Berechnung ohne den Verweis auf die Zellen der Spalte "KW" funktioniert.
Das wäre dann ja über alles.

Gruß
André

myfake
06.07.2006, 15:12
Hallo Andre,

ahhhh ....... dann wird eine der Spalten (entweder die auf dem Übersichtssheet oder die auf dem anderen) als Text formatiert sein.

Das muß natürlich angepaßt werden. ;)

cu
myfake

vanruiten
06.07.2006, 15:19
Hallo myfake,

es sind beide als "Standard" definiert. Kann es sein, daß das Format aus der Access-Tabelle übernommen wird und nicht geändert werden kann ?

Gruß
André

myfake
06.07.2006, 15:26
Hallo Andre,

d.h., die Daten auf dem Sheet 'Materialabgang pro KW' werden aus Access importiert? Kann schon sein, dass die dann als Text reinlaufen (weiß ich aber nicht!)

Schau doch mal, ob in einer der beiden Spalten ein " ' " vor der Zahl steht. Bzw. versuche einfach mal was passiert, wenn Du ein " ' " <- ohne " vor die Zahl auf dem Auswertungssheet schreibst.

cu
myfake

vanruiten
06.07.2006, 15:26
Hallo myfake,

ich glaube ich hab den Ursprungsfehler:
die Daten kommen aus einer OBDC-Datei und dort ist das Feld Datum als Text hinterlegt. Ich habe dieses zwar umgewandelt, so daß ich Kalenderwochen generiere, aber das Format wird dann wohl bleiben, oder ?!

Gruß
André

vanruiten
06.07.2006, 15:30
Hallo myfake,

das ist ja genial !!! :-)
Ich bekomme ein Ergebnis !!
MERCI VIELMALS !

Gruß
André

myfake
06.07.2006, 15:34
Hallo Andre,

na das war ja eine schwere Geburt ;) Freut mich aber sehr, dass es jetzt klappt! :five:

cu
myfake

jinx
06.07.2006, 17:48
<font size="2" face="Century Gothic">Moin, myfake,

und dann Ganze hätte man durch die Verwendung der Funktion Wert oder Multiplikation mit 1 verkürzen können...</font>

vanruiten
07.07.2006, 11:42
Hallo myfake,

das nächste Problem hat sich eingestellt und ich hoffe, du kannst wieder weiterhelfen:
Ich möchte aus den Werten "Mindestbestand" nur 5% dieses Wertes mit den Werten "Prop.Kosten" multiplizieren. Ich habe dazu einfach die vorangegangene Formel kopiert und die Spaltenangabe geändert, da hieß es schon wieder "#WERT!". Für "KW 1" und "5%" müßte ein Wert von ca. 87416 € herauskommen.

Leider gehen keine Anhänge durch, deshalb noch mal so:

x% vom Mindestbestand
5% 10% 15%
KW Summe Summe
1 € 32.437,21 #WERT!
2 € 43.111,90 SUMMENPRODUKT((Materialabgang!$A2:$A50000=$A6)*(Materialabgang!$F2:$F50000)*D3*( Materialabgang!$H2:$H50000)/100)
3 € 43.421,77

Spalte F sind die Mindestbestände (auch 0-Werte), Spalte H die prop.Kosten, D3 ist die Verlinkung zur %-Zelle.

Gruß
André

myfake
07.07.2006, 12:27
Hallo Andre,

die Frage nach Text/Wert hast Du sicher schon überprüft, oder? (<- denn danach hört es sich für mich gerade wieder an)

Kannst du einmal eine Bsp-Datei hochladen? Ich weiß nicht mit welchen Werten Du rechnest, um auf die 87416€ zu kommen. Wenn ich es in meiner Datei so einsetze, wird es wie erwartet berechnet.

Formel (Bsp. Tabelle siehe oben): =SUMMENPRODUKT(('Materialabgang pro KW'!$A$2:$A$13=A2)*('Materialabgang pro KW'!$I$2:$I$13)*5%*('Materialabgang pro KW'!$K$2:$K$13))
-> statt 5% fix einzutragen kann auch auf eine Zelle verwiesen werden

cu
myfake

vanruiten
07.07.2006, 12:39
Hallo myfake,

Formatierung habe ich geprüft, das paßt. Berechnungen in der Quelltabelle funktionieren auch; da habe ich den Wert berechnet, was rauskommen muß.

Interessant wird es wenn ich dem Verweis auf die Mindestbestände ein ">0" mitgebe,
dann wird berechnet, aber falsch:
1 € 32.437,21 € 13,05
SUMMENPRODUKT((Materialabgang!$A$2:$A$50000=$A6)*(Materialabgang!$F$2:$F$50000>0)*$D$3*(Materialabgang!$H$2:$H$50000)/100)


Meine Dateianhänge werden leider geblockt (als .zip versucht). Gibt es eine andere Möglichkeit ?

Gruß
André

myfake
07.07.2006, 12:47
Hallo Andre,

also ZIP-Dateien kann man hochladen: FAQ-Anhänge (http://www.ms-office-forum.net/forum/faq.php?s=&do=search&q=anhang&match=all&titlesonly=0)

Zur Größe/Alternative schaue bitte mal hier: Umfrage (http://www.ms-office-forum.net/forum/showthread.php?t=180067)

cu
myfake

vanruiten
07.07.2006, 12:58
Hallo myfake,

so nun aber, mit wenigen Daten drin.

Gruß
André

vanruiten
07.07.2006, 13:07
Hallo myfake,

potz blitz, mit den paar daten erhält man ein richtiges Ergebnis.
Wieso nicht in der kompletten Datei ?

Gruß
André

myfake
07.07.2006, 13:13
Hallo Andre,

sehr gut - jetzt also mit den selben Daten. :)

Wenn ich auf Tabelle1 / D5 diese Formel ...
=SUMMENPRODUKT((Materialabgang!$A$2:$A$38385=$A5)*(Materialabgang!$F$2:$F$38385) *D3*(Materialabgang!$H$2:$H$38385)/100)

... eingebe, erhalte ich 570,53€. Lasse ich die "/100" am Ende weg erhalte ich das selbe Ergebnis wie auf Sheet "Materialabgang" bei der manuellen Überprüfung:

<P><blockquote><table border=3 cellpadding=1><tr><td>&nbsp;</td><td align=center bgcolor=#C0C0C0>F</td><td align=center bgcolor=#C0C0C0>G</td><td align=center bgcolor=#C0C0C0>H</td><td align=center bgcolor=#C0C0C0>I</td></tr><tr><td align=center bgcolor=#C0C0C0>2</td><td align=right>0</td><td align=right>60</td><td align=right>2,22</td><td>0</td></tr><tr><td align=center bgcolor=#C0C0C0>3</td><td align=right>0</td><td align=right>35</td><td align=right>2,515</td><td>0</td></tr><tr><td align=center bgcolor=#C0C0C0>4</td><td align=right>200</td><td align=right>64</td><td align=right>162</td><td>1620</td></tr></td></tr></table></blockquote></P>
usw.
Formel in I2: =F2*5%*H2

Summe in I17 (Formel: =SUMME(I2:I16) ) 57.052,61

So habe ich die Aufgabenstellung verstanden und würde daher sagen, dass das Ergebnis korrekt ausgwisen wird. :confused:

Wo liegt der Fehler?


uups - ich hatte nicht gesehen, dass Du schon eine Antwort eingestellt hattest

Dann nehme ich mal an, dass eine Zelle (wahrscheinlich in Spalte A) noch als Text eingestellt ist


cu
myfake

vanruiten
07.07.2006, 13:17
Hallo myfake,

wie gesagt (post #31): die paar daten rechnet es bei mir auch richtig.
Kann dies mit der Datenmenge zu tun haben (kann ich mirzwar nicht vorstellen) ??

Gruß
André

sorry, ich hab den Fehler gefunden. Es waren nicht alle Zellen gefüllt.
Habe die Leerzellen gefiltert und dort eine 0 eingestellt. Jetzt klappt´s.
:)

Gruß
André