☰

按月份和支出類別分配價值

tag-icon • tag-icon microsoft-excel
按月份和支出類別分配價值

我有以下第一個表格作為資料。有什麼方法可以得到第二個表中的結果。

螢幕截圖

請注意，開始月份和結束月份顯示為 mm-yyyy。

為了澄清計算：

  • 第二個表有一列代表任何事件間隔中包含的每個月。
  • 每項活動的費用均分在該活動的月份中。

  • 儘管此範例顯示沒有費用類別用於多個事件，但這種情況仍可能發生。在這種情況下，給定成本類別的費用將每月匯總。

    舉例來說，假設兩個活動都有廣告費用，活動 A 的廣告費用為 600 美元。活動 A 的每月撥款為 200 美元，因為它是一個為期三個月的活動。因此，表 2 中的 4 月份廣告金額對於事件 A 為 200 美元，對於事件 B 為 500 美元，即 700 美元。

答案1

坦白說，第一個表可能適合人類閱讀，但不適合電腦計算或做一些統計。要獲得表 2，理想的情況很簡單 - 得到除以表 2每個月每個事件每個類別費用（並將它們儲存到一些輔助單元中），然後對它們進行相應的總結。為了獲得劃分的費用，我們首先需要獲得一個標誌，如果給定的月份屬於時間範圍。

我想你需要十二個月。我還假設開始月份的單元格具有該月第一天的日期（例如 2017-04-01，04 B2/2017），因此，結束月份的單元格具有該月最後一天的日期（例如 2017- 06-30為C2, 06/2017)。

  1. 旗幟：使用H1toS1作為每月的日期，即 01/2017 (2017-01-01)、02/2017 (2017-02-01)、...、12/2017 (2017-12-01)，並使用 left列的單元格H作為S該月是否在範圍內的標誌。在 中H2，使用公式=AND($B2<=H$1,H$1<=$C2)。$警告：請注意公式中的不同用法，以對單元格進行絕對引用，以便輕鬆複製。這表示如果目前月份（在目前列的頂行）位於開始月和結束月之間，則傳回 true，否則傳回 false。然後將此公式複製到右直到列S並向下複製到最後一個事件。

  2. 數數：使用列T作為要分割費用的月份計數器。中的公式T2為=COUNTIF(H2:S2,TRUE)，然後將公式複製下來。

  3. 每個類別的分攤費用：$再次請注意以下公式中的用法。

    3.1 第一類：使用欄位U（AF第一組 12 欄）作為第一類別的分割費用，即「廣告」；的公式U2is =IF(H2,$E2/$T2,0)，表示如果該月的標誌為 true（即該月在範圍內），則返回除費用（高級​​費用/月數），否則返回零。然後向右複製（直到第 列AF），向下複製。

    3.2 第二類：使用列AG（AR第 2 組，共 12 列）作為第二類別的劃分費用，即「展覽會和研討會」；的公式AG2為=IF(H2,$F2/$T2,0).然後向右複製（直到AR）並向下複製。

    3.3 第三類：使用欄位AS（BD第 3 組，共 12 欄）作為第 3 類（即「住宿」）的分割費用；的公式AS2為=IF(H2,$G2/$T2,0).然後向右複製（直到BD）並向下複製。

  4. 總結一下：在表二中，將每個月所有事件的相應費用相加。

相關內容