Office: (Office 2016) Summenprodukt mit mehrdimensionalem Ergebnis

Hallo zusammen,

ich habe eine Monatstabelle, in der ich die tatsächlich geleisteten Arbeitsstunden in einer Summe berechnen möchte. Hier werden die Urlaubstage in Zahlen eingegeben (1=1 Tag). In einer zweiten Tabelle ist angegeben, wie viele Stunden der Mitarbeiter pro Tag arbeitet. Ich möchte nun automatisch berechnen, wie viele Arbeitsstunden auf Basis der eingetragenen Urlaubstage geleistet wurden. Ich hoffe, das ist verständlich. Ich denke, dass es irgendwie mit der SUMMENPRODUKT Funktion berechnet werden kann. Also die Tage in denen keine Zahl eingetragen wurde (das sind die tatsächlichen Arbeitstage) x die für den Mitarbeiter geltenden Arbeitsstunden an dem entsprechenden Tag = tatsächliche Arbeitsstunden in dem entsprechenden Monat.

Ich hoffe, das ist irgendwie verständlich

LG Aliena

 

Hallo,

wenn du in Zelle "Tabelle1!AG3" folgende Formel eingibst:
{=SUMME((B3:AF3)*SVERWEIS($A3;'Stunden pro Woche'!$A$1:$I$9;VERGLEICH($B$2:$AF$2;'Stunden pro Woche'!$A$1:$I$1;0);0))}
dann kommt dein gewünschtes Resultat (=34,5) heraus. Diese Formel ist dann hinunterzuziehen (zu kopieren) in die Zellen AG4:AG10.

Falls du die Tabelle in "Stunden pro Woche!A1:I9" umwandelst in eine strukturierte Tabelle (über Menüband > Tabulator "Einfügen" > Gruppe "Tabellen" > Button "Tabelle") und dieser den Namen "TabWoStunden" gibst, dann kann die Formel auch so geschrieben werden:
{=SUMME((B3:AF3)*SVERWEIS($A3;TabWoStunden; VERGLEICH($B$2:$AF$2;TabWoStunden[#Kopfzeilen];0);0))}
was leichter zu lesen ist und den großen Vorteil hat, dass die Tabelle "TabWoStunden" dynamisch nach unten erweiterbar ist für weitere Namenseinträge, ohne dass deswegen die Formeln im Arbeitsblatt "Tabelle1" angepasst werden müssen.

 

Danke, Anton! Es funktioniert! Und doch wesentlich einfacher als ich dachte. Vielen Dank!!!!

LG Aliena

 

Hallo nochmal. Nachdem die erste Formel so super funktioniert hat, wollte ich eine ähnliche Berechnung in einer zweiten Datei durchführen, jedoch mit dem Unterschied, dass anstatt von Zahlen Buchstaben zusammengezählt werden müssen. Mit Zählenwenn komme ich leider auf falsche Werte, vielleicht hat noch jemand eine Idee?!


Gesendet von iPhone mit Tapatalk

 

Hallo Aliena,

mir ist nicht ganz klar, was du mit "Buchstaben zusammenzählen" meinst. Falls du aber damit meinst, dass in der Monatstabelle alle Tage, die einen bestimmten Buchstaben enthalten, mit ihren jeweiligen Wochentagstunden gezählt werden sollen, dann müssten statt der obigen 2 Formeln folgende Formeln funktionieren:

Es werden hier alle Tage, die ein "a" enthalten, berücksichtigt (statt wie bisher alle Tage, einen Einser enthalten):
{=SUMME((B3:AF3="a")*SVERWEIS($A3;'Stunden pro Woche'!$A$1:$I$9;VERGLEICH($B$2:$AF$2;'Stunden pro Woche'!$A$1:$I$1;0);0))}
bzw.
{=SUMME((B3:AF3="a")*SVERWEIS($A3;TabWoStunden; VERGLEICH($B$2:$AF$2;TabWoStunden[#Kopfzeilen];0);0))}
Alle Zellen, die etwas anderes als ein "a" enthalten (oder leer sind), werden dann nicht berücksichtigt.

 

Jaa, genau so! Wow. Du bist ja genial! Danke!


Gesendet von iPhone mit Tapatalk