Office: Ausbildungsstunden aufaddieren

Tach liebe EXCEL-Füchse,
folgendes Problem:
es gibt 2 Azubis (P und Q), die auf 3 Arbeitsplätzen (X,Y und Z) von verschiedenen Ausbildern (A, B und C) ausgebildet werden.
Die Azubis müssen täglich dokumentieren, wie lange sie von wem wo ausgebildet wurden. Das wird dann in einer Monatsliste zusammengefasst.
Es gibt also z.B. eine Arbeitsmappe „Mai 2009“ mit 2 Tabellenblättern für P und Q.
Die Ausbilder hingegen müssen tageweise dokumentieren, wie viele Stunden sie ausgebildet haben. Hierbei ist es jedoch unerheblich wen und wo. Es steht auf deren Formular also für den jeweiligen Tag nur eine Gesamtstundenzahl der Ausbildungsstunden. Das Ausbilderformular soll sich nach Eingabe des Ausbildernamens und des betreffenden Monats die Daten aus den Azubinachweisen holen.
Wie bringe ich jetzt diesem Formular bei
a) für welchen Monat es gelten soll (=auf welche Arbeitsmappe es zugreifen soll)
b) dass es nur für einen bestimmten Ausbilder gelten soll (den man oben im Formular eingibt)
c) dass es die Stunden, die der Ausbilder aus b) ausgebildet hat, aus beiden Tabellenblättern P und Q für den jeweiligen Tag des Monats addieren soll
Ich hoffe, mein Anliegen ist verständlich formuliert.
Also, lasst die Köpfe rauchen und überschüttet mich bitte mit Lösungsvorschlägen.
Es grüßt
Giraffe

 

Hallo,

da Du eine ganze Reihe von Dingen nicht (genau) angegeben hast, gehe ich von folgenden Voraussetzungen aus:

1) Du hast ein Dateiverzeichnis mit folgendem Pfad: 'C:\Excel\Abrechnung\'

2) In diesem Verzeichnis hast Du 13 Excel-Dateien:
2a) für jeden Monat 1 Datei mit folgenden Namen:
Ausbildung Januar 2009.xls, Ausbildung Februar 2009.xls, ..., Ausbildung Dezember 2009.xls
2b) und schließlich die Formular-Excel-Datei: Ausbildung Formular.xls

3) Jede der 12 Excel-Mappen von 2a) hat 5 Arbeitsblätter:
3a) Die Blätter "P" und "Q" für die Azubis mit je 4 Spalten:
Spalte A fürs Datum, Spalte B für den Ausbildner (A,B,C), Spalte C für den Arbeitsplatz (X,Y,Z), Spalte D für die Ausbildungszeit (als Dez.Zahl)
3b) Ferner die Blätter "A", "B", "C" für die Ausbildner mit je 2 Spalten:
Spalte A fürs Datum, Spalte B für die Ausbildungszeit (als Dez.Zahl).
3c) Jede der Spalten von 3a)+3b) hat in Zeile 1 eine passende Überschrift und kann max. 999 Zeilen enthalten (vgl. die Formeln unter 4)).

4) Unter diesen Voraussetzungen könnte dann das entsprechende Arbeitsblatt der Mappe aus 2b) 'Ausbildung Formular.xls' in etwa so aussehen:
Zelle B1: enthält das Datum des 1. des betreffenden Monats, also zB. 01.05.2009; dieses Datum wird benutzerdefiniert formatiert mit "MMMM JJJJ"; dadurch wird der 01.05.2009 angezeigt als "Mai 2009".
Zelle E1: enthält den Pfad+Dateinamen der 12 Monatsdateien:
Formel: ="C:\EXCEL\Abrechnung\[Ausbildung "&TEXT(B1;"MMMM JJJJ")&".xls]"
Zelle B2: enthält den Ausbildner (also A, B oder C)
Zelle B3: enthält den 1. Azubi (also P)
Zelle C3: enthält den 2. Azubi (also Q)
Die Zellen B5, C5, D5 enhalten die Pfade auf die Arbeitsblätter von Ausbildner (A,B,C) bzw. von den Azubis (P,Q):
Zelle B5: ="'"&$E$1&B6&"'!"
Zelle C5: ="'"&$E$1&C6&"'!"
Zelle D5: ="'"&$E$1&D6&"'!"
Die Zelle A6, B6, C6, D6, E6 sind Spaltenüberschriften, werden aber auch für die Formeln in B5:D5 benötigt:
Zelle A6: 'Datum'
Zelle B6: =B2
Zelle C6: =B3
Zelle D6: =C3
Zelle E6: 'Gesamt Azubis'
In den Zellen A7, B7, C7, D7, E7 folgen die entscheidenen Berechnungsformeln:
Zelle A7: =DATUM(JAHR($B$1);MONAT($B$1);1)
Zelle B7: =SUMMEWENN(INDIREKT(B$5&"A2:A999");$A7;INDIREKT(B$5&"B2:B999"))
Zelle C7: =SUMMENPRODUKT((INDIREKT(C$5&"A2:A999")=$A7)*(INDIREKT(C$5&"B2:B999")=$B$6)*INDIREKT(C$5&"D2:D999"))
Zelle D7: =SUMMENPRODUKT((INDIREKT(D$5&"A2:A999")=$A7)*(INDIREKT(D$5&"B2:B999")=$B$6)*INDIREKT(D$5&"D2:D999"))
Zelle E7: =SUMME(C7:D7)
In die Zelle A8 kommt die Formel für den nächsten Tag: =A7+1
Diese Formel in die Zellen A9 bis A37 kopieren für die restlichen Monatstage
Ferner die 4 Formeln in B7:E7 hinunterkopieren in den Bereich B8:E37.

Ich hoffe, dass das in etwa Deinen Vorstellungen entspricht. Das, was Du mit ziemlicher Sicherheit ändern musst, ist der Pfad aus 1) und somit auch die Formel in Zelle E1 (bis vor die 1. eckige Klammer) aus 'Ausbildung Formular.xls'.
Statt A,B,C, P,Q kann natürlich jeder beliebige Name verwendet werden; es muss natürlich darauf geachtet werden, dass sich diese 5 Namen alle unterscheiden und weiters muss diese Änderung nicht bloß in den Zellen B2, B3 und C3 in 'Ausbildung Formular.xls' durchgeführt werden, sondern auch die Arbeitsblätter der 12 Monatsdateien aus 2a) entsprechend umbenannt werden.

Achtung: Damit die Formeln in B6:D37 funktionieren, muss/müssen die jeweiligen Monatsdateien mindestens 1x gespeichert worden sein.
Die Formeln in Zelle E1, B5:D5 können auch mit dem benutzerdefinierten Format ";;;" weggeblendet werden.

 

Hallo Exl121150,
ganz großes Tennis!
Punkt 3b) ist überflüssig, dafür ist ja das Formular da.
Ansonsten: PEREKT! Genauso sollte es sein.
UND: Ich verstehe es sogar.
Vielen Dank und weiter so
Gruß
Giraffe

 

Hallo Exl121150,
ich habe da doch noch mal eine Frage:
wieso braucht man in den Zellen C7 und D7 das Summenprodukt? Kannst Du mir das noch mal aufdröseln.
Funktionieren tut alles genau so, wie ich es mir gewünscht habe.
Gruß
Giraffe

 

Hallo,

1) Du schreibst, dass Du Excel2000 besitzt; hättest Du angegeben, Besitzer von Excel2007 zu sein, wäre es sehr viel einfacher geworden durch die neuen Funktionen, die es dort gibt: =SUMMEWENNS(...), man kann damit eine bedingte Summierung mit mehreren Kriterien durchführen, bis Excel2003 ist in der SUMMEWENN()-Funktion nur 1 Kriterium möglich.

2) Zum Bilden der Tageswertes in Zelle C7 für Azubi P müssen 2 Bedingungen gleichzeitig zutreffen: das Tagesdatum (Zelle A7) und der Ausbildner (Zelle B6 bzw. B2). Da es mehrere Arbeitsplätze (X,Y,Z) gibt, kann es auch mehrere Eintragungszeilen in Arbeitsplatt P für Datum A7 und Ausbildner B6 geben. Daher müssen die Zeilen summiert werden, die den beiden Bedingungen genügen.

3a) Die SUMMENPRODUKT()-Funktion hat in diesem Fall einen einzigen Parameter, der sich aus 2 Multiplikationen von 3 Formelausdrücken zusammensetzt:

3b) Der 1.Formelausdruck lautet: (INDIREKT(C$5&"A2:A999")=$A7)
Dabei steht in Zelle C5 der Pfad auf das Arbeitsblatt "P" des Azubi P für den betreffenden Monat und A2:A999 ist der Zellbereich in der Spalte A für 998 Datenzeilen (wenn man A1 weglässt, weil dort nur ein Spaltenkopftext steht). Der Gesamtpfad würde gemäß obigen Postings für 05/2009 lauten:
'C:\EXCEL\Abrechnung\[Ausbildung Mai 2009.xls]P'!A2:A999. Dabei stehen in A2:A999 wiederum Datumsangaben. Diese Datumsangaben im Azubi-Arbeitsblatt werden Zeile für Zeile verglichen mit dem Datum in Zelle A7 des Ausbildnerformulars: dort wo beide Datumsangben übereinstimmen, wird der logische Wert WAHR erzeugt, sonst der log. Wert FALSCH. Auf diese Weise werden 998 WAHR/FALSCH-Werte durch den Datumsvergleich erzeugt.

3c) Der 2.Formelausdruck lautet: (INDIREKT(C$5&"B2:B999")=$B$6)
Analog zu Punkt 3b) lautet der Gesamtpfad nun:
'C:\EXCEL\Abrechnung\[Ausbildung Mai 2009.xls]P'!B2:B999. Dabei stehen in B2:B999 die 3 Ausbildner (A,B oder C). Diese Ausbildnerangaben im Azubi-Arbeitsblatt werden Zeile für Zeile verglichen mit dem Ausbildner in Zelle B6 bzw. B2 des Ausbildnerformulars: dort wo beide Ausbildnerangaben übereinstimmen, wird der logische Wert WAHR erzeugt, sonst der log. Wert FALSCH. Auf diese Weise werden 998 WAHR/FALSCH-Werte durch den Ausbildnervergleich erzeugt.

3d) Der 3.Formelausdruck lautet: INDIREKT(C$5&"D2:D999")
Analog zu Punkt 3b) und 3c) lautet der Gesamtpfad nun:
'C:\EXCEL\Abrechnung\[Ausbildung Mai 2009.xls]P'!D2:D999. Dabei stehen in D2:D999 die jeweiligen Ausbildundsstunden. Diese Ausbildungsstunden im Azubi-Arbeitsblatt werden Zeile für Zeile ermittelt: 1 numerischer Wert je Zeile. Auf diese Weise werden 998 Stundenwerte erzeugt.

3e) Jetzt wird eine spezielle Eigenschaft von SUMMENPRODUKT ausgenutzt (=ArrayFormel-Eigenschaft): Diese 3x 998 Werte kannst Du Dir vorstellen wie in einer Tabelle, die 3 Spalten und 998 Zeilen hat. Dabei werden die 3 Werte, die in einer Tabellenzeile enthalten sind, miteinander multipliziert. Excel erkennt nun automatisch, dass es sich bei den ersten beiden Werten der Zeile nicht um Zahlen, sondern um log. Werte handelt, wandelt diese in Zahlen um (WAHR=1 bzw. FALSCH=0) und multipliziert. Wie Du schon bemerkt haben wirst, kommt nur dann wieder ein Stundenwert (nämlich der gleiche) heraus, wenn die beiden ersten Zahlen gleich 1 (d.h. WAHR) sind; also nur, wenn beide Bedingungen zutreffen, erhalten wir wieder den Stundenwert, der in der Zeile stand, sonst 0. Auf diese Weise erhalten wir 998 unseren Bedingungen angepasste Stundenwerte.

3f) Wie das Wort SUMMENPRODUKT erwarten lässt, könnte man diese 998 Werte noch mit weiteren Werten multiplizieren (was wir hier nicht tun) und zu guter Letzt werden die 998 Stundenwerte noch addiert.

3g) Im Formelausdruck wird außerdem 3x die INDIREKT()-Funktion verwendet: das ist deshalb nötig, weil es sich bei den Parametern dieser Funktion jeweils um einen Pfadausdruck auf externe Zellbereiche handelt. Ohne diese INDIREKT-Funktion käme man nur dann aus, wenn es sich 3x um eine Pfadkonstante handelte.

4) Die Erklärung der Formel in Zelle D7 ist völlig gleich zu der in C7, nur eben für Azubi Q.
Da in der Formel für Zelle B7 nur 1 Kriterium benötigt wird, wird dort die einfachere SUMMEWENN()-Funktion verwendet.

 

Hallo Exl121150,
das nenne ich mal eine ausführliche Antwort.
Jetzt habe ich es gerafft. Ein Zauberding, dieses Summenprodukt.
Ich kriege jedes Mal feuchte Augen, wenn ich mit der neuen Mappe arbeite.
Nochmals vielen Dank für Deine Mühe.
Super gemacht.
Gruß
Giraffe