☰

按月和支出类别分配价值

tag-icon • tag-icon microsoft-excel
按月和支出类别分配价值

我有以下第一个表作为数据。有什么方法可以得到第二个表的结果吗？

截屏

请注意，开始月份和结束月份显示为 mm-yyyy。

为了澄清计算：

  • 第二张表包含每个月的一列，该列包含在任意事件间隔内。
  • 每个活动的费用均等分摊到该活动的各个月份。

  • 虽然示例显示没有费用类别用于多个事件，但这种情况是可能发生的。在这种情况下，给定成本类别的费用将每月汇总。

    例如，假设两个活动都有广告费用，而活动 A 的费用为 600 美元。活动 A 的月度分配额为 200 美元，因为它是一个为期三个月的活动。因此，表 2 中的 4 月广告费用为活动 A 200 美元，活动 B 500 美元，即 700 美元。

答案1

坦白说，第一张表可能适合人类阅读，但不适合计算机计算或做一些统计。要得到第二张表，理想很简单——得到分割的每个月 每个活动 每个类别 费用（并将它们存储到一些辅助单元格中），然后相应地将它们相加。要获得分摊的费用，我们首先需要获得一个标志，以判断给定月份是否属于时间范围。

我假设您需要十二个月。我还假设起始月份的单元格的日期为该月的第一天（例如，B22017 年 4 月为 2017-04-01），因此，终止月份的单元格的日期为该月的最后一天（例如，2017 年 6 月为 2017-06-30 C2）。

  1. 旗帜：使用H1到S1作为每个月的日期，即 01/2017 (2017-01-01)、02/2017 (2017-02-01)、...、12/2017 (2017-12-01)，并使用列左侧单元格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（第 1 组 12 列）作为第 1 个类别（即“广告”）的分摊费用；公式为U2，=IF(H2,$E2/$T2,0)表示如果该月的标志为真（即该月在范围内），则返回分摊费用（广告费用/月数），否则为零。然后向右复制（直到 列AF），向下复制。

    3.2 第二类：使用列AG至AR（第 2 组 12 列）作为第 2 类，即“展览会及研讨会”的分项费用；公式为AG2。=IF(H2,$F2/$T2,0)然后向右复制（直到AR）并向下复制。

    3.3 第三类：使用列AS至BD（第 3 组 12 列）作为第 3 类，即“住宿”的分项费用；公式为AS2。=IF(H2,$G2/$T2,0)然后向右复制（直到BD）并向下复制。

  4. 总结一下：表二中，对每个月的所有事件，汇总相应的费用。

相关内容