Werte nach Monat und Ausgabenkategorie zuordnen

Question

Ich habe die folgende erste Tabelle als Daten. Gibt es eine Möglichkeit, das Ergebnis wie in der zweiten Tabelle zu erhalten?

Bitte beachten Sie, dass Start- und Endmonat als mm-jjjj angezeigt werden.

Zur Verdeutlichung der Berechnungen:

• Die zweite Tabelle enthält eine Spalte für jeden Monat, der in einem der Ereignisintervalle enthalten ist.
• Die Kosten für jede Veranstaltung werden gleichmäßig auf die Monate dieser Veranstaltung aufgeteilt.

• Obwohl im Beispiel keine Kostenkategorie für mehr als ein Ereignis verwendet wird, kann das vorkommen. In diesem Fall würden die Ausgaben für eine bestimmte Kostenkategorie jeden Monat zusammengefasst.

  Nehmen wir beispielsweise an, dass für beide Veranstaltungen Werbekosten anfallen und dass die Kosten für Veranstaltung A 600 USD betragen. Die monatliche Zuweisung für Veranstaltung A würde 200 USD betragen, da es sich um eine dreimonatige Veranstaltung handelt. Der Werbebetrag für April in Tabelle 2 würde also 200 USD für Veranstaltung A und 500 USD für Veranstaltung B betragen, also 700 USD.

Answer 1

Offen gesagt ist die erste Tabelle für Menschen vielleicht lesbar, aber nicht für Computer zum Berechnen oder Erstellen von Statistiken. Um Tabelle 2 zu erhalten, ist das Ideal einfach: Man erhält die geteiltefür jeden Monat für jede Veranstaltung für jede Kategorie Kosten(und sie in einigen Hilfszellen speichern), um sie dann entsprechend zu summieren. Um die geteilten Ausgaben zu erhalten, müssen wir zuerst eine Markierung setzen, wenn der jeweilige Monat in den Zeitraum fällt.

Ich gehe davon aus, dass Sie zwölf Monate benötigen. Ich gehe außerdem davon aus, dass die Zelle für den Startmonat das Datum des ersten Tages dieses Monats hat (z. B. 2017-04-01 für B2, 04/2017) und dementsprechend die Zelle für den Endmonat das Datum des letzten Tages dieses Monats hat (z. B. 2017-06-30 für C2, 06/2017).

1. Flagge: Verwendet H1bis S1als Datum für jeden Monat, also 01/2017 (2017-01-01), 02/2017 (2017-02-01), ..., 12/2017 (2017-12-01), und verwendet die linken Zellen der Spalte Hbis Sals Kennzeichen, ob dieser Monat im Bereich liegt. H2Verwendet in die Formel =AND($B2<=H$1,H$1<=$C2).Vorbehalt: Bitte beachten Sie die unterschiedliche Verwendung von $in der Formel, um den absoluten Bezug auf Zellen zu haben und so das Kopieren zu erleichtern.Das bedeutet, wenn der aktuelle Monat (in der obersten Zeile der aktuellen Spalte) zwischen Anfangs- und Endmonat liegt, wird „true“ zurückgegeben, andernfalls „false“. Kopieren Sie diese Formel dann in die rechte Spalte Sund kopieren Sie sie nach unten bis zum letzten Ereignis.

2. Zählen: Verwendet die Spalte Tals Zähler für die Monate, auf die die Ausgaben aufgeteilt werden sollen. Die Formel T2lautet =COUNTIF(H2:S2,TRUE), kopieren Sie dann die Formel nach unten.

3. Aufgeteilte Kosten für jede Kategorie:Beachten Sie bitte nochmals die Verwendung von $in der folgenden Formel.

   3.1 1. Kategorie: Verwendet die Spalte Ubis AF(1. Gruppe von 12 Spalten) als geteilte Ausgabe für die 1. Kategorie, d. h. „Werbung“; die Formel von U2ist =IF(H2,$E2/$T2,0), d. h., wenn die Flagge dieses Monats wahr ist (d. h. dieser Monat liegt im Bereich), gibt sie die geteilte Ausgabe zurück (Werbungskosten/Monatszahl), andernfalls Null. Dann nach rechts kopieren (bis Spalte AF), nach unten kopieren.

   3.2 2. Kategorie: Verwendet die Spalte AGbis AR(2. Gruppe von 12 Spalten) als geteilte Ausgabe für die 2. Kategorie, also „Messen & Seminare“; die Formel von AG2lautet =IF(H2,$F2/$T2,0). Dann nach rechts kopieren (bis AR) und nach unten kopieren.

   3.3 3. Kategorie: Verwendet die Spalte ASbis BD(3. Gruppe von 12 Spalten) als geteilte Ausgabe für die 3. Kategorie, also „Unterkunft“; die Formel von AS2lautet =IF(H2,$G2/$T2,0). Dann nach rechts kopieren (bis BD) und nach unten kopieren.

4. Fassen Sie sie zusammen: Addieren Sie in Tabelle zwei für jeden Monat die entsprechenden Ausgaben aller Veranstaltungen.