基于FILTER函数转换英超利物浦赛事数据表格需求

核心需求 #

基于原始赛事数据，通过FILTER及配套函数实现：

• 将利物浦的对手统一归集到单列，同时根据利物浦的原列位置标记主客场（H=主场，A=客场）
• 自动调整XG（预期进球）和实际比分的顺序：利物浦在原始数据B列时保持原比分，在C列时调换双方数值

一站式数组公式（推荐） #

在空白工作表的A1单元格输入以下公式，可直接生成符合要求的完整表格：

=ARRAYFORMULA(
  LET(
    筛选条件, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"),
    日期列, FILTER(Data!A:A, 筛选条件),
    原主场队, FILTER(Data!B:B, 筛选条件),
    原客场队, FILTER(Data!C:C, 筛选条件),
    主场XG, FILTER(Data!D:D, 筛选条件),
    客场XG, FILTER(Data!E:E, 筛选条件),
    主场得分, FILTER(Data!F:F, 筛选条件),
    客场得分, FILTER(Data!G:G, 筛选条件),
    对手列, IF(原主场队="Liverpool", 原客场队, 原主场队),
    主客场标记, IF(原主场队="Liverpool", "H", "A"),
    XG比分, IF(原主场队="Liverpool", 主场XG&"-"&客场XG, 客场XG&"-"&主场XG),
    实际比分, IF(原主场队="Liverpool", 主场得分&"-"&客场得分, 客场得分&"-"&主场得分),
    {日期列, 对手列, 主客场标记, XG比分, 实际比分}
  )
)

公式拆解说明 #

1. 筛选利物浦赛事：通过(Data!B:B="Liverpool")+(Data!C:C="Liverpool")筛选所有利物浦参与的比赛
2. 变量定义：用LET函数一次性提取所需原始数据列，避免重复计算，提升公式可读性
3. 对手列生成：判断利物浦是否在原始主场列（B列），是则取原客场队，反之取原主场队
4. 主客场标记：利物浦在B列时标记H（主场作战），在C列时标记A（客场作战）
5. 比分调整：XG和实际比分均按利物浦的位置调整顺序，确保利物浦的数值始终在前

分列单独公式（可选） #

如果需要逐列设置公式，可使用以下单列式：

• 日期列：=FILTER(Data!A:A, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))
• 对手列：=ARRAYFORMULA(IF(FILTER(Data!B:B, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))="Liverpool", FILTER(Data!C:C, (Data!B:B="Liverpool")+(Data!C:C="Liverpool")), FILTER(Data!B:B, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))))
• 主客场标记列：=ARRAYFORMULA(IF(FILTER(Data!B:B, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))="Liverpool", "H", "A"))
• XG比分列：=ARRAYFORMULA(IF(FILTER(Data!B:B, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))="Liverpool", FILTER(Data!D:D, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))&"-"&FILTER(Data!E:E, (Data!B:B="Liverpool")+(Data!C:C="Liverpool")), FILTER(Data!E:E, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))&"-"&FILTER(Data!D:D, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))))
• 实际比分列：=ARRAYFORMULA(IF(FILTER(Data!B:B, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))="Liverpool", FILTER(Data!F:F, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))&"-"&FILTER(Data!G:G, (Data!B:B="Liverpool")+(Data!C:C="Liverpool")), FILTER(Data!G:G, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))&"-"&FILTER(Data!F:F, (Data!B:B="Liverpool")+(Data!C:C="Liverpool"))))

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