基于多条件在Excel中按月份拆分项目收入金额的技术问询

核心逻辑 #

以项目在对应月度的实际天数占FY24总天数的比例来分摊T列金额，自动适配项目起止与财政年度的重叠情况。

通用公式（以Y4单元格为例，对应FY24的6月） #

=LET(
    fy_start, DATE(2023,6,1),
    fy_end, DATE(2024,5,31),
    month_start, Y$1,
    month_end, EOMONTH(Y$1,0),
    proj_start, $Q4,
    proj_end, $R4,
    effective_start, MAX(proj_start, month_start, fy_start),
    effective_end, MIN(proj_end, month_end, fy_end),
    IF(effective_start > effective_end, 0, $T4 * (effective_end - effective_start + 1)/$V4)
)

将公式向右拖拽即可覆盖FY24的所有月度（6月-次年5月）。

对应各条件的适配说明 #

1. 条件1：项目覆盖整个FY24（V列=365/366）
   公式自动取每个月度的完整天数（如6月30天、7月31天），分摊比例为「当月天数/365/366」，和你需求的T4*Y1逻辑完全匹配（若Y1为当月天数占全年比例，公式结果等价）。

2. 条件2：项目起始于月中且非6月
   比如第5行起始日2023年6月15日，公式计算6月的有效起始日为2023-06-15，有效结束日为2023-06-30，实际天数16天（30-15+1），分摊金额为T5*16/V5，完全贴合实际天数拆分要求。

3. 条件3：项目起始于2023年10月
   对于6-9月的列，有效起始日（MAX(2023-10-xx, 当月起始, 2023-06-01)）会大于有效结束日（当月月末），公式返回0自动留空；10月及以后的列则按实际天数计算分摊，符合从10月开始拆分的要求。

4. 条件4：项目起始日早于2023年6月1日，但FY24仍有天数
   公式的effective_start会取MAX(早于6月的起始日, 当月起始, 2023-06-01)，自动从2023年6月1日开始计算项目在FY24的天数，忽略6月1日之前的无效周期。

5. 条件5：项目结束日为非月末日期
   公式的effective_end会取MIN(非月末结束日, 当月月末, 2024-05-31)，比如结束日为2024年3月15日，3月的有效结束日就是2024-03-15，实际天数为15 - 当月起始日 +1，确保只按实际天数分摊。

额外优化建议 #

若你的V列（FY24总天数）公式需要简化，可替换为：

=MAX(0, MIN($R4, DATE(2024,5,31)) - MAX($Q4, DATE(2023,6,1)) + 1)

该公式比SEQUENCE写法更高效，直接计算项目在FY24的有效总天数。

内容的提问来源于stack exchange，提问作者raj kumar