Office: (Office 365) Gesamtkosten berechnen

Hallo zusammen,

ich habe mal eine Excel angehängt, die mein Problem versucht zu beschreiben.

In der linken Spalte finden Sich die Daten, an denen das Projekt jeweils durchgeführt wurde. Direkt daneben befindet sich das entsprechende Projekt. In den Spalten C-E befinden sich die aufgewendeten Materialien für das Projekt am jeweiligen Tag. Diese Materialien kosten Geld, welches in Zeile H definiert ist. Nun will ich die monatlichen Kosten des jeweiligen Projektes errechnen (rot markiert). Welche Funktion ist dafür empfehlenswert und wie muss diese aussehen?

Grüße
DerEliminator

 

Hola,
die Kosten für das billige Material:
=SUMMENPRODUKT((TEXT($A$2:$A$74;"MMJJJJ")=TEXT(K$1;"MMJJJJ"))*($B$2:$B$74=$J2)*($C$2:$C$74))*$H$2
Das machst du für die anderen beiden auch (natürlich den Bereich und H2 anpassen) und addierst das dann.
Gruß,
steve1da

 

Hallo,

da du ja Excel365 hast, kannst du, ausgehend von der Formel von steve1da, die Summation für die 3 Materialien vereinfachen zur folgenden Formel ab Zelle K2:
=LET(m;WENN(ISTLEER($C$2:$E$74);0;$C$2:$E$74);SUMME((TEXT($A$2:$A$74;"MMJJJJ")=TEXT(K$1;"MMJJJJ"))*($B$2:$B$74=$J2)*MMULT(m;$H$2:$H$4)))
kopierbar in die restlichen Zellen des Projekte/Monats-Bereiches.

Die Hilfsvariable m in der LET-Funktion wurde notwendig, da die Funktion MMULT(m;$H$2:$H$4) keine leeren Zellen duldet. Wenn du in den 3 Materialien-Spalten C:E die Leerzellen durch 0-Werte ersetzt, kannst du auf die Hilfsvariable m verzichten und ebenfalls auf die LET-Funktion:
=SUMME((TEXT($A$2:$A$74;"MMJJJJ")=TEXT(K$1;"MMJJJJ")) * ($B$2:$B$74=$J2) * MMULT($C$2:$E$74;$H$2:$H$4))

 

Hallo,

da du 365 benutzt, würde ich mir aus den Daten eine intelligente Tabelle machen und das dann mit der Filter Funktion lösen.
Das ganze könnte dann so aussehen.

Gruß,
Roger

 

Kann man die Formel auch auf folgendes Problem erweitern?
(Office 365) - Berechung monatl. Kosten

 

Hallo,

ich habe dir die vorgenannte Formel in eine benutzerdef. Arbeitsblatt-Formel eingefügt, die sich so ähnlich verhält, wie eine PivotTable mit Zeilen- und Spaltenkriterien. Die Formeln befinden sich im Namensmanager und ich habe sie dir in einer übersichtlicheren Form auch in das Arbeitsblatt "Formeln_Im_Namensmanager" gestellt.

Ich habe dir die Formel in die Zelle G11 eingefügt:
=PivotKosten($A$2:$E$74; $I$2:$I$4)
das ist die linke obere Zelle des Zellbereichs, in den die Formel ihre Daten ausgibt.

Sie benötigt 2 Parameter: der erste ist der Datenbereich (zB. A2:E74 in deinem Beispiel) mit den Spalten für Datum / Projekte / 3 Materialspalten.
der zweite Parameter ist der Spaltenbereich (I2:I4) für die normale Preisliste, die genauso viele Preise enthalten muss als es Materialspalten gibt.
Die Preise für die 0,5-Mengen werden ermittelt, indem statt der 0,5-Menge von der Formel die Menge 2/3 eingesetzt wird und dann mit der normalen Preisliste weitergerechnet wird.
Die als Zeilen ausgegebenen Projekte richten sich nach den Projekten, die in der 2.Spalte des Datenbereichs enthalten sind, während in den Spalten die Monate ausgegeben werden, die in den Datumsangaben der 1.Spalte des Datenbereichs vorkommen.
Die Zahlen sind die aufsummierten Kosten.

Ich habe dir noch 2 weitere Formelbeispiele eingefügt, die Teilmengen des Datenbereichs auswerten.

Datei liegt bei.

 

Hallo,

ich habe die Funktion dahin gehend geändert, dass jetzt beliebige 0,5-Mengen-Preise (Bereich H2:H4) verwendet werden können.
Die Formel in Zelle G11 lautet fast identisch wie bisher, nur der Preisbereich ist jetzt 2-spaltig:
=PivotKosten($A$2:$E$74; $H$2:$I$4)

Datei liegt bei.