PDA

Vollständige Version anzeigen : Schichten Stunden addieren


E.GO
27.06.2015, 17:41
Hallo,
Im Monatsblatt hier "Jan" habe ich die Schichten eingetragen.
Diesen Schichten sind im Blatt "Schichtzeiten" die Arbeitszeiten pro Schicht hinterlegt.
Nun möchte ich mittels VBA die Summe der Schichten in "Jan" einer Zeile erhalten.

Wer kann mir dabei helfen?
LG E.GO

RPP63neu
27.06.2015, 18:15
Hallo!
Eine Möglichkeit:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:28px;" /><col style="width:51px;" /><col style="width:60px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >Formel</td><td >Ergebnis</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >F</td><td >S</td><td >N</td><td >U</td><td >U</td><td >Frei</td><td >&nbsp;</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >F</td><td >Frei</td><td >K</td><td >U</td><td >U</td><td >S</td><td >&nbsp;</td><td style="text-align:right; ">33,5</td><td style="text-align:right; ">33,5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >F</td><td >S</td><td >Frei</td><td >F</td><td >F</td><td >F</td><td >&nbsp;</td><td style="text-align:right; ">35</td><td style="text-align:right; ">35</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >F</td><td >S</td><td >K</td><td >Frei</td><td >K</td><td >K</td><td >&nbsp;</td><td style="text-align:right; ">33,5</td><td style="text-align:right; ">33,5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Frei</td><td >S</td><td >S</td><td >S</td><td >U</td><td >F</td><td >&nbsp;</td><td style="text-align:right; ">34,5</td><td style="text-align:right; ">34,5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >J4</td><td >{=SUMME(<span style=' color:008000; '>WENN(C4:H4=Schichtzeiten!A$6:A$11;Schichtzeiten!B$6:B$11;0)</span>)}</td></tr><tr><td >J5</td><td >{=SUMME(<span style=' color:008000; '>WENN(C5:H5=Schichtzeiten!A$6:A$11;Schichtzeiten!B$6:B$11;0)</span>)}</td></tr><tr><td >J6</td><td >{=SUMME(<span style=' color:008000; '>WENN(C6:H6=Schichtzeiten!A$6:A$11;Schichtzeiten!B$6:B$11;0)</span>)}</td></tr><tr><td >J7</td><td >{=SUMME(<span style=' color:008000; '>WENN(C7:H7=Schichtzeiten!A$6:A$11;Schichtzeiten!B$6:B$11;0)</span>)}</td></tr><tr><td >J8</td><td >{=SUMME(<span style=' color:008000; '>WENN(C8:H8=Schichtzeiten!A$6:A$11;Schichtzeiten!B$6:B$11;0)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4.8 </a>

Beachte den Hinweis auf die Matrix-Formel!
Die Formel aus J4 kann nach unten gezogen werden.

Gruß Ralf

E.GO
27.06.2015, 18:53
Hallo Ralf,
das ist nett von dir danke.

Die Matrixformel würde mir schon weiter helfen, aber die Mappe ist durch die 12 Monate uvm. schon so groß und schwerfällig, dass ich eine VBA-Lösung brauche. Die Berechnungen benötige ich meist zum Monatsende.

Danke vielmals
E.GO

RPP63neu
27.06.2015, 19:09
Hi!
Prinzipiell gebe ich Dir ja Recht, was Matrixformeln in großen Tabellen betrifft.
Dies greift hier aber eher nicht.
Wenn Du Pro Zeile 31 Werte (einen Monat) mit der Suchmatrix vergleichst, dann ermittelst Du 31*6=186 Wahrheitswerte.
Dies zwingt keinen PC auch nur annähernd in die Knie!

Wenn Du es dennoch über VBA machen willst, wirst Du nicht eine Berechnung einsparen.
Ein Ansatz:
Laufe pro Zeile die Zellen durch, vergleiche per Worksheetfunktion.VLOOKUP() die einzelnen Zellen mit der Wertematrix und schreibe die ermittelten Std. in eine Variable (per Variable = Variable + Wert).
Nach Durchlauf der Zeile schreibe die Summe in den .Offset (die Summenzelle).
Aber ehrlich gesagt: dafür schreibe ich kein Makro.

Suche lieber in Deiner bisherigen Tabelle nach "Bremsen" oder stelle sie zwecks Optimierung zur Verfügung.

Gruß Ralf

RPP63neu
27.06.2015, 19:13
Einen habe ich noch:
In J4: =SUMMENPRODUKT((C4:I4=Schichtzeiten!A$6:A$11)*(Schichtzeiten!B$6:B$11))
Jetzt hast Du keine Matrixformel mehr,
... aber durchaus eine Matrixfunktion ... :p

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:28px;" /><col style="width:51px;" /><col style="width:60px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >Matrix</td><td >Soll</td><td >SumProd</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >F</td><td >S</td><td >N</td><td >U</td><td >U</td><td >Frei</td><td >&nbsp;</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >F</td><td >Frei</td><td >K</td><td >U</td><td >U</td><td >S</td><td >&nbsp;</td><td style="text-align:right; ">33,5</td><td style="text-align:right; ">33,5</td><td style="text-align:right; ">33,5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >F</td><td >S</td><td >Frei</td><td >F</td><td >F</td><td >F</td><td >&nbsp;</td><td style="text-align:right; ">35</td><td style="text-align:right; ">35</td><td style="text-align:right; ">35</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >F</td><td >S</td><td >K</td><td >Frei</td><td >K</td><td >K</td><td >&nbsp;</td><td style="text-align:right; ">33,5</td><td style="text-align:right; ">33,5</td><td style="text-align:right; ">33,5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Frei</td><td >S</td><td >S</td><td >S</td><td >U</td><td >F</td><td >&nbsp;</td><td style="text-align:right; ">34,5</td><td style="text-align:right; ">34,5</td><td style="text-align:right; ">34,5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >J4</td><td >{=SUMME(<span style=' color:008000; '>WENN(C4:I4=Schichtzeiten!A$6:A$11;Schichtzeiten!B$6:B$11;0)</span>)}</td></tr><tr><td >L4</td><td >=<span style=' color:008000; '>SUMMENPRODUKT((C4:I4=Schichtzeiten!A$6:A$11)</span><span style=' color:008000; '>*(Schichtzeiten!B$6:B$11)</span>)</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4.8 </a>

Gruß Ralf

E.GO
27.06.2015, 22:53
Hallo,
hab versucht mit diesem Makro die Zeiten zu berechnen.

Sub SchichtenAdd()
Dim h As Range
Dim var As Long
Dim z, sp As Integer

Set h = Sheets("Schichtzeiten").Range("A6:B11")
With ActiveSheet
For z = 4 To 8
For sp = 3 To 8

var = Application.WorksheetFunction.VLookup(.Cells(z, sp), h, 2, 0)


var = var + var '???????? + welcher Wert
Next sp

.Cells(z, 9) = var

Next z
End With

End Sub

Das Addieren mit var = var + var ist falsch.
wie muss das Richtig heißen?

LG E.GO

E.GO
28.06.2015, 13:44
Hallo,
habs mittlerweile selbst geschafft.
Der Code muss so sein, dann läuft er gut.

LG E.GO

Sub SchichtenAdd()
Dim h As Range
Dim var, r As Variant
Dim z, sp As Integer

Set h = Sheets("Schichtzeiten").Range("A6:B11")
With ActiveSheet
For z = 4 To 8
var = 0
For sp = 3 To 8

r = Application.WorksheetFunction.VLookup(.Cells(z, sp), h, 2, False)

var = var + r
Next sp

.Cells(z, 9) = var

Next z

End With
Set var = Nothing
End Sub