PDA

Vollständige Version anzeigen : Summeberechnung


slice1
22.02.2008, 14:02
Hallo,

hoffe auf eine kleine Hilfestellung:(
Bekomme einmal im Monat eine Excel-Tabelle mit folgenden Inhalten:
Spalte A1, A10, A20, A30... enthalten das Tagesdatum, Beginn A1 mit 01. und endet mit Enddatum d. Monats
Spalte B1, B10, B20, B30...enthalten Zahlen/Auswertungen die am jeweiligen Tagesdatum angefallen sind.

Möchte jetzt in einer Spalte C1-Cx die Kalenderwoche angezeigt bekommen, abhängig von SpalteA, in Spalte D die Summe pro KW berechnen, die in abhängigkeit ist von Spalte B, sowie in Spalte E1-Ex den jeweiligen Tag, abhängig von Spalte A angezeigt bekommen. Desweiteren will ich in Spalte F die Summe pro Tag im Monat berechnen (z.B. Summe aus jeden Montag im Monat).
Z.Zt. passe ich die Auswertungen Spalte C-F händisch an. Würde gerne diese gerne automatisch ausrechnen lassen. Komme aber irgendwie nicht weiter, wie ich diese Tabelle aufbaue, hinterlegt mit Formeln.

Hat hier jemand eine Idee/Tipp für mich?
Hoffe es gut erklärt zu haben, ansonsten lege ich ein Muster bei.

Vielen Dank für Beiträge.

Gruß
slice1

Tayler
22.02.2008, 14:10
Hallo slice1.
Kannst du bitte mal eine kleine Beispieldatei hochladen?

Gruß

Tayler

IngGi
22.02.2008, 14:11
Hallo slice1,

ein Muster ist immer gut. Dann muss man sich nicht selbst etwas zum testen nachbauen.

Gruß Ingolf

slice1
22.02.2008, 14:44
Hallo,

anbei die gesamte Tabelle, hoffe Ihr kommt klar damit.
Danke für Eure Unterstützung.

Gruß
slice1

IngGi
22.02.2008, 22:30
Hallo slice1,

die Formeln in C3, D3 und F3 kannst du einfach runterkopieren. In C3:C12 wird die Formel nur bis 10:00 Uhr funktionieren, da die Zeitangaben in A3:A12 ab 11:00 Uhr wohl einen Rundungsfehler aufweisen. Die sind wahrscheinlich über irgendwelche Formeln berechnet worden. Schreib dir also die Zeitangaben ab 11:00 Uhr (A6:A12) nochmal von Hand in deine Datei, dann sollte es auch bei dir funktionieren.

Die Formel in D3 enthält einen Algorithmus zur korrekten Berechnung der Kalenderwoche aus einem Datum nach DIN. Der Teil der Formel stammt nicht von mir, sondern von http://www.excelformeln.de/formeln.html?welcher=7.


<table><tr style="vertical-align:top; text-align:center; "><tr><td>Tabelle1</td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:ARIAL,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "> <style type = "text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold; "><col width=76.999998075 ><col width=64.999998375 ><col width=64.999998375 ><col width=57.99999855 ><col width=57.99999855 ><col width=72.999998175 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td></tr><tr height=51 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">T-Zeit</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">&nbsp;</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Summe <br>Zeit insg</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Summe <br>pro KW</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Tag</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Summe <br>W-tag/<br>Monat</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:left; ">08:00</td><td style="text-align:left; ">09:00</td><td style="text-align:left; ">8165</td><td style="text-align:left; ">7363</td><td style="text-align:left; ">Mo</td><td style="text-align:left; ">12781</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; ">09:00</td><td style="text-align:left; ">10:00</td><td style="text-align:left; ">11444</td><td style="text-align:left; ">14832</td><td style="text-align:left; ">Di</td><td style="text-align:left; ">13654</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; ">10:00</td><td style="text-align:left; ">11:00</td><td style="text-align:left; ">10401</td><td style="text-align:left; ">21480</td><td style="text-align:left; ">Mi</td><td style="text-align:left; ">14866</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; ">11:00</td><td style="text-align:left; ">12:00</td><td style="text-align:left; ">10616</td><td style="text-align:left; ">27707</td><td style="text-align:left; ">Do</td><td style="text-align:left; ">16785</td></tr><tr height=18 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; ">12:00</td><td style="text-align:left; ">13:00</td><td style="text-align:left; ">7322</td><td style="text-align:left; ">0</td><td style="text-align:left; ">Fr</td><td style="text-align:left; ">13296</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; ">13:00</td><td style="text-align:left; ">14:00</td><td style="text-align:left; ">6850</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=18 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:left; ">14:00</td><td style="text-align:left; ">15:00</td><td style="text-align:left; ">7157</td><td style="text-align:left; font-weight:bold; ">Summe</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; font-weight:bold; ">Summe</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:left; ">15:00</td><td style="text-align:left; ">16:00</td><td style="text-align:left; ">5983</td><td style="text-align:left; ">71382</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">71382</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:left; ">16:00</td><td style="text-align:left; ">17:00</td><td style="text-align:left; ">2853</td><td style="">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:left; ">17:00</td><td style="text-align:left; ">18:00</td><td style="text-align:left; ">591</td><td style="">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; font-weight:bold; ">Summe</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:left; ">&nbsp;</td><td style="">&nbsp;</td><td style="text-align:left; ">71382</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr></table><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#FFFCF9; "><tr><td><b>Formeln der Tabelle</b></td></tr><tr><td><table style="font-family:Arial; font-size:10pt;">C3 : =SUMMENPRODUKT(($B$18:$B$420=$A3)*($E$18:$E$420))<br>D3 : =SUMMENPRODUKT((K&Uuml;RZEN(($F$18:$F$36-DATUM(JAHR($F$18:$F$36+3-REST($F$18:$F$36-2;7));1;REST($F$18:$F$36-2;7)-9))/7)=K&Uuml;RZEN(($F$18-DATUM(JAHR($F$18+3-REST($F$18-2;7));1;REST($F$18-2;7)-9))/7)+ZEILE()-3)*($G$18:$G$36))<br>F3 : =SUMMENPRODUKT((LINKS(TEXT($F$18:$F$36;"TTT");2)=$E3)*($G$18:$G$36))<br></table></td></tr></table></td></tr><tr><td>&nbsp;</td></tr></tr></table> <br>

Gruß Ingolf

slice1
23.02.2008, 13:55
Super Ingolf, 1a Beitrag.
Gibt es auch eine Möglichkeit die KW zu berechnen?
Würde gerne mir vor der Spalte D (Summe/KW) eine Spalte einfügen, wo ich die Kalenderwoche angezeigt bekomme. In Spalte A18:A420 steht ja Datumsangaben.
Nochmals Danke.
Gruß
slice1

IngGi
23.02.2008, 16:29
Hallo slice1,

das Problem war ja nicht, die Kalenderwoche zu berechnen. Dafür habe ich dir ja in meiner letzten Antwort einen Link gepostet. Das Problem bestand vielmehr darin, für die Berechnung die richtige Zelle in Spalte F, ab F18 zu finden, denn der Monatserste liegt ja nicht immer auf demselben Wochentag, so dass sich die Anordnung der Wochen in der Matrix F18:F? von Monat zu Monat verschiebt. Ich habe das jetzt folgendermaßen gelöst:

D3 ist ja noch einfach. Hier kann ich einfach die Kalenderwoche von F18 berechnen. In D4 addiere ich dann 1 zu D3, zeige das Ergebnis aber nur dann an, wenn die Kalenderwoche in dem betreffenden Monat tatsächlich existiert. Dazu rechne ich in einer Matrixformel die Kalenderwoche aller Zellen von F18 bis F45 aus und prüfe mit der Funktion VERGLEICH, ob die hochgezählte Kalenderwoche in dieser Matrix vorkommt. Wenn ja, wird das Ergebnis in D4 angezeigt, wenn nein dann eben nicht.

Jetzt fällt mir allerdings gerade ein, dass man diese Prüfung auf D7, also die 5te Woche des Monats beschränken kann, denn es geht ja immer um genau einen Monat und jeder Monat hat mindestens 4 Wochen. Nur die 5te Woche muss geprüft werden. Und jetzt fällt mir noch ein, dass nur der Februar in einem Jahr, das kein Schaltjahr ist, keine 5te Woche hat. Man könnte also genausogut auf Februar und Schaltjahr prüfen, was sicher einfacher wäre. Ich lass es jetzt aber so, wie es ist.

Kopiere die Formel in D4 (nicht D3, die ist anders) runter bis D7. Die Formel in E3 habe ich übrigens auch ergänzt, so dass keine 0 angezeigt wird, wenn die 5te Kalenderwoche in dem betreffenden Monat nicht existiert.

Die Formel in D4 ist übrigens eine Matrixformel - Hinweis unten beachten !

<table><tr style="vertical-align:top; text-align:center; "><tr><td>Tabelle1</td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:ARIAL,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "> <style type = "text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold; "><col width=76.999998075 ><col width=64.999998375 ><col width=64.999998375 ><col width=57.99999855 ><col width=57.99999855 ><col width=72.999998175 ><col width=82.999997925 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td><td style="">&nbsp;</td></tr><tr height=51 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">T-Zeit</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">&nbsp;</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Summe <br>Zeit insg</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">KW</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Summe <br>pro KW</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Tag</td><td style="font-family:Arial; background-color:#ffffcc; text-align:left; ">Summe <br>W-tag/<br>Monat</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:left; ">08:00</td><td style="text-align:left; ">09:00</td><td style="text-align:left; ">8165</td><td style="text-align:right; ">1</td><td style="text-align:left; ">7363</td><td style="text-align:left; ">Mo</td><td style="text-align:left; ">12781</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; ">09:00</td><td style="text-align:left; ">10:00</td><td style="text-align:left; ">11444</td><td style="text-align:right; ">2</td><td style="text-align:left; ">14832</td><td style="text-align:left; ">Di</td><td style="text-align:left; ">13654</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; ">10:00</td><td style="text-align:left; ">11:00</td><td style="text-align:left; ">10401</td><td style="text-align:right; ">3</td><td style="text-align:left; ">21480</td><td style="text-align:left; ">Mi</td><td style="text-align:left; ">14866</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; ">11:00</td><td style="text-align:left; ">12:00</td><td style="text-align:left; ">10616</td><td style="text-align:right; ">4</td><td style="text-align:left; ">27707</td><td style="text-align:left; ">Do</td><td style="text-align:left; ">16785</td></tr><tr height=18 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; ">12:00</td><td style="text-align:left; ">13:00</td><td style="text-align:left; ">7322</td><td style="">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">Fr</td><td style="text-align:left; ">13296</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; ">13:00</td><td style="text-align:left; ">14:00</td><td style="text-align:left; ">6850</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=18 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:left; ">14:00</td><td style="text-align:left; ">15:00</td><td style="text-align:left; ">7157</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; font-weight:bold; ">Summe</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; font-weight:bold; ">Summe</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:left; ">15:00</td><td style="text-align:left; ">16:00</td><td style="text-align:left; ">5983</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">71382</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">71382</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:left; ">16:00</td><td style="text-align:left; ">17:00</td><td style="text-align:left; ">2853</td><td style="text-align:left; ">&nbsp;</td><td style="">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:left; ">17:00</td><td style="text-align:left; ">18:00</td><td style="text-align:left; ">591</td><td style="text-align:left; ">&nbsp;</td><td style="">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; font-weight:bold; ">Summe</td><td style="text-align:left; font-weight:bold; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:left; ">&nbsp;</td><td style="">&nbsp;</td><td style="text-align:left; ">71382</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td></tr></table><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#FFFCF9; "><tr><td><b>Formeln der Tabelle</b></td></tr><tr><td><table style="font-family:Arial; font-size:10pt;">C3 : =SUMMENPRODUKT(($B$18:$B$420=$A3)*($E$18:$E$420))<br>D3 : =K&Uuml;RZEN(($F$18-DATUM(JAHR($F$18+3-REST($F$18-2;7));1;REST($F$18-2;7)-9))/7)<br>E3 : =WENN(D3="";"";SUMMENPRODUKT((K&Uuml;RZEN(($F$18:$F$36-DATUM(JAHR($F$18:$F$36+3-REST($F$18:$F$36-2;7));1;REST($F$18:$F$36-2;7)-9))/7)=K&Uuml;RZEN(($F$18-DATUM(JAHR($F$18+3-REST($F$18-2;7));1;REST($F$18-2;7)-9))/7)+ZEILE()-3)*($G$18:$G$36)))<br>G3 : =SUMMENPRODUKT((LINKS(TEXT($F$18:$F$36;"TTT");2)=$F3)*($G$18:$G$36))<br>D4 : {=WENN(ISTFEHLER(VERGLEICH(D3+1;K&Uuml;RZEN(($F$18:$F$45-DATUM(JAHR($F$18:$F$45+3-REST($F$18:$F$45-2;7));1;REST($F$18:$F$45-2;7)-9))/7);0));"";D3+1)}<br><br><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></table></td></tr></table></td></tr><tr><td>&nbsp;</td></tr></tr></table>

Gruß Ingolf