基于多条件过滤双列表并关联第三列表的Excel公式优化需求

需求明确 #

• 基于U1（品牌）、U2（截止日期）过滤采购数据（List A）与销售数据（List B）
• 按采购日期升序排列结果
• 计算产品净数值：采购总和 - 销售总和
• 关联产品详情（List C）并匹配指定列顺序

可拆分公式方案（Excel 365/2021 动态数组版本） #

以下方案按功能拆分，便于调试和维护：

1. 过滤并聚合采购数据 #

提取符合品牌、日期条件的产品，保留最早采购日期（用于排序）并计算采购总和：

=LET(
    filtered_purchases, FILTER(ListA!A:D, (ListA!B:B=U1)*(ListA!C:C<=U2), ""),
    prod_list, INDEX(filtered_purchases,,1),
    purchase_dates, INDEX(filtered_purchases,,3),
    purchase_vals, INDEX(filtered_purchases,,4),
    agg_purchases, HSTACK(UNIQUE(prod_list), XLOOKUP(UNIQUE(prod_list), prod_list, purchase_dates, "", 0, 1), SUMIF(prod_list, UNIQUE(prod_list), purchase_vals))
)

注：假设List A列结构为[A:产品, B:品牌, C:日期, D:数值]，若列顺序不同需调整INDEX的列参数

2. 过滤并聚合销售数据 #

提取符合条件的产品并计算销售总和：

=LET(
    filtered_sales, FILTER(ListB!A:C, (ListB!B:B=U1)*(ListB!C:C<=U2), ""),
    prod_sales, INDEX(filtered_sales,,1),
    sales_vals, INDEX(filtered_sales,,3),
    agg_sales, HSTACK(UNIQUE(prod_sales), SUMIF(prod_sales, UNIQUE(prod_sales), sales_vals))
)

注：假设List B列结构为[A:产品, B:品牌, C:日期, D:数值]，按需调整列参数

3. 合并数据并排序 #

匹配采购/销售数据，计算净数值，按采购日期升序排列：

=LET(
    agg_p, [步骤1的公式],
    agg_s, [步骤2的公式],
    prods, INDEX(agg_p,,1),
    sort_dates, INDEX(agg_p,,2),
    sum_p, INDEX(agg_p,,3),
    sum_s, XLOOKUP(prods, INDEX(agg_s,,1), INDEX(agg_s,,2), 0),
    net_val, sum_p - sum_s,
    sorted_data, SORTBY(HSTACK(prods, sum_p, sum_s, net_val), sort_dates, 1)
)

4. 关联产品详情并匹配列顺序 #

假设List C列结构为[A:产品, B:类别, C:规格, D:备注]，最终结果列顺序为产品→类别→规格→采购总和→销售总和→净数值：

=LET(
    sorted_data, [步骤3的公式],
    prods, INDEX(sorted_data,,1),
    prod_details, XLOOKUP(prods, ListC!A:A, ListC!B:D, ""),
    final_result, HSTACK(prods, prod_details, INDEX(sorted_data,,2), INDEX(sorted_data,,3), INDEX(sorted_data,,4))
)

兼容旧版Excel（2019及更早）方案 #

若无法使用动态数组，可通过以下公式组合实现：

1. 提取唯一产品（数组输入，按Ctrl+Shift+Enter）：

   =INDEX(ListA!A:A, SMALL(IF((ListA!B:B=U1)*(ListA!C:C<=U2), ROW(ListA!A:A)-MIN(ROW(ListA!A:A))+1), ROW(A1)))
   

2. 计算采购总和：

   =SUMPRODUCT((ListA!A:A=E2)*(ListA!B:B=U1)*(ListA!C:C<=U2)*ListA!D:D)
   

3. 计算销售总和：

   =SUMPRODUCT((ListB!A:A=E2)*(ListB!B:B=U1)*(ListB!C:C<=U2)*ListB!D:D)
   

4. 关联产品详情：

   =INDEX(ListC!B:B, MATCH(E2, ListC!A:A, 0))
   

5. 排序：手动筛选采购日期列升序，或用RANK函数生成排序序号后重新排列

关键注意事项 #

• 所有公式需根据实际列结构调整索引位置
• 动态数组公式输入后自动溢出结果，无需下拉填充
• 若产品仅存在于采购/销售单一方，XLOOKUP返回0，净数值计算逻辑自动适配

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