Formel gesucht -> vermutlich über Index

Liebe Excelianer,
nach ewiger Zeit benötige ich wieder Köpfe, die klüger sind.
Ich muss eine Vorlage für entstehende Personalkosten erstellen. Wichtig ist hier die Monatsverteilung. Es betrifft nur das aktuelle Jahr.
Ein Muster liegt bei. Makros darf ich aus Sicherheitsgründen nicht verwenden. Eine Lösung muss daher Formelbasiert erfolgen.

Zur Tabelle. Die Spalten A bis G sind für spätere manuelle Eingaben gedacht und können noch angepasst werden. Beispielfälle sind hinterlegt. Der Rest sollte sich automatisch berechnen.
Ich finde keine Lösung, wie sich die Monate "ansprechen" lassen und entweder die vollen bzw. anteiligen Kostensätze hinterlegen lassen.

https://www.herber.de/bbs/user/158400.xlsx

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Andre,

da die Anstellungsverhältnise über mehrere Jahre gehen können, muss das Jahr angegeben werden!
Also habe ich in die Zelle "C1" das Jahr geschrieben (2023)

Dann habe ich in "H2" folgende Formel geschrieben:

="IST-Kosten " &C1

Damit zeigt der Text automatisch immer das Jahr an, welches in "C1" steht.

In die Zelle "I2" habe ich folgende Formel geschrieben:

=DATUM($C1;1;1)

Dort wird ein Datum erzeugt. es ist der 1.1. des Jahres aus "C1"
Dort habe ich die Formatierung als Benutzerdefiniert eingestellt und "MMM" als Typ angegeben. (ohne Anführungszeichen)
Der Typ "MMM" gibt den Monat als Text in Kurzform aus.

In die Zelle "J2" habe ich folgende Formel geschrieben:

=EDATUM(I2;1)

Dort wird das Datum aus "I2" um einen Monat erhöht.

Dann habe ich die Zelle "J2" selektiert und an der unteren rechten Ecke bis in Zelle "T2" gezogen.
Nun hab ich überall den Monat in Kurzform stehen. Jedoch weiß Excel, welches Datum dahinter steht.

Dann habe ich die Zelle "I4" folgende Formel geschrieben:

=$F4*(MAX(MIN(EDATUM(I$2;1)-1;$E4)-MAX(I$2;$D4)+1;0))/(EDATUM(I$2;1)-I$2)*$G4/100

Dann habe ich die Zelle "I4" selektiert und an der unteren rechten Ecke bis in Zelle "T4" gezogen.
Dann habe ich die untere rechte Ecke bis "T33" gezogen. (Dabei muss "I4" bis "T4" selektiert bleiben)
Nun ist die Formel mit den richtigen Verweisen in allen Zellen.

Falls du nicht überall die ganzen Nullen stehen haben möchtest, kannst du um die Formel noch ein WENN bauen.

Nun die Erklärung der Hauptformel:

MAX(MIN(EDATUM(I$2;1)-1;$E4)-MAX(I$2;$D4)+1;0)

Dieser Teil errechnet wie viele Tage von dem in "D"-"E" angegebenen Zeitraum im entsprechenden Monat lagen.
Der Rest ist ziemlich simpel:
Der Kostensatz wird mit den errechneten Tagen multipliziert und durch die Anzahl der Tage des Monats dividiert.
Somit haben wir den Anteil für die entsprechende Zeit.
Dann wird dies noch mit dem Anteil multipliziert und durch 100 dividiert.
Das ist simple Prozentrechnung und gibt dann halt das gewünschte Ergebnis an

Liebe Grüße
Mathias

Beliebteste Forumthreads (12 Monate)

Formel gesucht -> vermutlich über Index

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread