☰

计算球队前六场比赛的轮转表现

tag-icon • tag-icon microsoft-excel worksheet-function
计算球队前六场比赛的轮转表现

我有一个包含大量足球比分的电子表格。我已将其附加到附件中，以便您了解我在说什么。

以下是其中一条记录：

+-----+--------+-----------+-----------+--------+--------+------------+--------+--------+----+----+----+----+------+----+------+----+------+----+----+
| Ref | Output | Home      | Away      | HTHome | HTAway |    Date    | FTHome | FTAway | HT | FT | HT | FT | Away | HT | Away | FT | Away | HT | FT |
+-----+--------+-----------+-----------+--------+--------+------------+--------+--------+----+----+----+----+------+----+------+----+------+----+----+
|  1  |    1   | Arsenal   | Leicester |    2   |    2   | 11/08/2017 |    4   |    3   |  D |  W | DW |  D |   L  | DL |      |    |      |    |    |
+-----+--------+-----------+-----------+--------+--------+------------+--------+--------+----+----+----+----+------+----+------+----+------+----+----+

由此我知道阿森纳以4-3赢得了比赛。

电子表格包含很多这样的行。我想要做的是在下一场比赛之前找出球队之前的表现。

例如，节礼日，利物浦对阵斯旺西：

+-----+--------+-----------+-----------+--------+--------+------------+--------+--------+----+----+----+----+------+----+------+----+------+----+----+
| Ref | Output | Home      | Away      | HTHome | HTAway |    Date    | FTHome | FTAway | HT | FT | HT | FT | Away | HT | Away | FT | Away | HT | FT |
+-----+--------+-----------+-----------+--------+--------+------------+--------+--------+----+----+----+----+------+----+------+----+------+----+----+
| 194 |   20   | Liverpool | Swansea   |    1   |    0   | 26/12/2017 |    5   |    0   |  W |  W | WW |  L |   L  | LL |      |    |      |    |    |
+-----+--------+-----------+-----------+--------+--------+------------+--------+--------+----+----+----+----+------+----+------+----+------+----+----+

利物浦之前六场比赛的表现为W W D D W D。我最终会将这些信息转换成一个数字，例如，对于3胜利、1平局和0失败，利物浦的“得分”为12。

比赛可以是主场或客场，我想知道前六场比赛的战绩，无论它们在哪里进行。我还希望能够将公式拖下来，这样我就可以输入下周的比赛安排，并正确生成之前的战绩。

我见过很多其他形式的状态表解决方案。我的要求不同，因为我想知道整个赛季中每场比赛的先前状态。

如果这些公式能够轻松地用于检查最近 3 场比赛的形式，那也会很方便。

预定义文件.xlsx

如果有任何问题，请告诉我。

以下是数据链接：

http://www.football-data.co.uk/mmz4281/1718/E0.csv

答案1

您的两个示例条目与链接的 CSV 文件中的数据不匹配，而且有点不清楚。您似乎正在将 CSV 文件导入工作簿并对其进行修改。（不过，您忘记将链接粘贴到工作簿中。）

因此，对于我的解决方案，我将使用未修改的 CSV 文件数据。您必须调整公式以适合您的实际表格。此外，由于相关前六场比赛的“得分”实际上比单独的胜/负/平局结果更容易计算，因此我的公式将返回：

工作表截图

输入以下公式BN2，然后按 ctrl-enter/copy-paste/fill-down&right/auto-fill 进入其余表格BN和BO列：

=IFERROR(MOD(SUMPRODUCT(LARGE(+($C$1:$C1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="H")+1*($G$1:$G1="D"))+($D$1:$D1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="A")+1*($G$1:$G1="D")),{6,5,4,3,2,1})),100),0)

解释：

该公式的美化版本如下：

=
IFERROR(
  MOD(
    SUMPRODUCT(
      LARGE(
        +($C$1:$C1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="H")+1*($G$1:$G1="D"))
        +($D$1:$D1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="A")+1*($G$1:$G1="D")),
        {6,5,4,3,2,1}
      )
    ),
    100
  ),
  0
)

对于包含目标球队的每一行，公式都会创建一个特殊值。（对于其他行，该值为零。）特殊值的最右边两位数字包含结果值（3获胜、1平局和失败0），而最左边的数字包含行号。

然后，该LARGE()函数选择最近六行匹配的特殊值（即最左边数字中六行数字最大的值）。使用SUMPRODUCT()这六个值将得到一个值，其中最右边两位数字是结果值的总和，即“分数”。该MOD()函数提取此分数。

该IFERROR()函数用于消除逐行#NUM!发生的错误（由于试图获取包含少于六个元素的数组的最大六个值）。26LARGE()

请注意，如果目标球队之前的比赛少于六场，则缺失比赛对应数组元素的值将为零。


逐步执行公式BO22应该会使上述内容更加清晰。请注意，由于TRUE和在乘法中使用时分别FALSE转换为1和，因此出于紧凑性原因，我将在以下评估中使用这些数字。0

  • 100*ROW($G$1:$G21)+3*($G$1:$G21="H")+1*($G$1:$G21="D")
    → 100*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}+3*({0;1;0;0;0;1;0;0;1;1;0;0;0;1;1;1;1;0;1;0;0})+1*({0;0;0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;1})
    → {100;203;300;400;500;603;701;801;903;1003;1100;1200;1300;1403;1503;1603;1703;1800;1903;2000;2101}，所有前几行的主队特殊值
  • ($C$1:$C21=D22)*({100;203;300;400;500;603;701;801;903;1003;1100;1200;1300;1403;1503;1603;1703;1800;1903;2000;2101})
    → ({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1})*({100;203;300;400;500;603;701;801;903;1003;1100;1200;1300;1403;1503;1603;1703;1800;1903;2000;2101})
    → {0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101}，如果主队与目标队 ( D22) 匹配，则为特殊值，且所有行均如此
  • 类似地，($D$1:$D21=D22)*(100*ROW($G$1:$G21)+3*($G$1:$G21="A")+1*($G$1:$G21="D"))
    → {0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}，特殊值，如果离开团队与所有前几行的目标团队匹配
  • +{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101}+{0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
    → {0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101}，如果比赛涉及目标球队，则所有先前行的特殊值。（请注意，由于这两个数组是“互斥的”，因此将它们相加不会造成任何干扰。）
  • LARGE({0;0;303;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;2101},{6,5,4,3,2,1})
    → {0,0,0,0,303,2101}，目标球队前六场比赛的特殊值
  • SUMPRODUCT({0,0,0,0,303,2101})
    → 2404，其中最右边的两位数字是“分数”，即结果值的总和，而无关的最左边的数字是行号的总和。（请注意，SUMPRODUCT()使用 而不是 ，SUM()因此内部子表达式将作为数组进行评估，从而避免了在公式中输入数组的需要。）
  • MOD(2404,100)
    → 4，目标球队前六场比赛的“比分”（或成绩）

笔记：

  • 美化的公式确实可以起作用。
  • 您需要保留原始 CSV 文件中的FTR列 ( G:G)，以使公式正常工作。（该列并非严格要求，但公式需要进行调整才能在没有它的情况下工作。我选择使用该列，因为它使公式更易于阅读。）
  • 修改公式以仅使用前三场相关比赛只需简单地更改{6,5,4,3,2,1}为即可{3,2,1}。
  • 行缩放因子只有在 时才保证有效[# relevant previous games]*[max outcome value] < [row scaling factor]。使用示例的值，6*3=18 < 100，因此我们可以看到这100是可以工作的最小缩放因子。（嗯，实际上是最小十的幂缩放因子，19就像绝对最低限度。）
  • 如果您确实需要前六场比赛的个别结果，我可以将其添加到答案中。已添加。

附录#1：

对于当前行主队前六场比赛的个别结果，多单元格阵列在行的六个连续单元格中输入（++）以下公式Ctrl，Shift然后复制粘贴/填充到表格的其余列中（不要忘记删除 and ）：Enter2{}

{=
CHOOSE(1+
  IFERROR(
    MOD(
      LARGE(
        +($C$1:$C1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="H")+2*($G$1:$G1="A")+1*($G$1:$G1="D"))
        +($D$1:$D1=C2)*(100*ROW($G$1:$G1)+3*($G$1:$G1="A")+2*($G$1:$G1="H")+1*($G$1:$G1="D")),
        {6,5,4,3,2,1}
      ),
      100
    ),
    0
  ),
  "-","D","L","W"
)}

对于当前行的个别结果离开球队前六场比赛，只需将其均=C2改为=D2。

要将结果作为字符串返回到单个单元格中，只需用 包裹整个公式即可TEXTJOIN("",TRUE,…)。（仅适用于 Excel 2016。早期版本的 Excel 需要 poly-fill UDF - 请参阅这个帖子为一个基本的。

解释：

该公式与上一个公式基本相同，仅进行了三处修改：

  1. 2为损失添加了结果值
  2. 结果值不再相加
  3. 相反，它们被用作函数的第一个参数（即索引）CHOOSE()来选择适当的结果字符。

最后，只是为了向你表明，我并不是在开玩笑，分数比单个结果更容易计算，以下是最简单的结果公式，它不首先计算单个分数值：

{=
IF(
  ISERROR(0/LARGE(ROW($G$1:$G1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})),
  "-",
IF(
  "D"=INDEX($G:$G,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))),
  "D",
IF(
  +("H"=INDEX($G:$G,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))))
  *(C2=INDEX($C:$C,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))))
  +("A"=INDEX($G:$G,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1})))))
  *(C2=INDEX($D:$D,N(IF(1,1+LARGE((ROW($G$1:$G1)-1)*(($C$1:$C1=C2)+($D$1:$D1=C2)),{6,5,4,3,2,1}))))),
  "W",
"L"
)))}

附录#2：

所有前面的公式都利用了这样一个事实：列C、D和的标题G与这些列中的任何数据值都不匹配，并且列中的值仅用于比较。这意味着主队和客队数组的第一个元素（即对应于标题行）的值将为零。

然而，如果列中的值在公式中以数字形式使用，例如在计算每支球队在过去六场比赛中进球总数时，

=
IFERROR(
  MOD(
    SUMPRODUCT(
      LARGE(
        +($C$1:$C1=C2)*(100*ROW($E$1:$E1)+$E$1:$E1)
        +($D$1:$D1=C2)*(100*ROW($F$1:$F1)+$F$1:$F1),
        {6,5,4,3,2,1}
      )
    ),
    100
  ),
  0
)

那么数组第一个元素的值将是错误值#VALUE!，LARGE()函数将始终返回数组{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}，并且公式将始终返回零。

有几种方法可以解决这个问题。

一种方法是从行开始输入公式3，而不是从行开始2，然后将其更改为从行开始2而不是从行开始1（例如，$C$2:$C2=C3而不是$C$1:$C2=C3）。当然，行的值2始终为零，可以手动输入。


另一种方法是将数组的总和包装在一个IFERROR()函数中，以便在将其传递给函数之前将第一个元素中的错误转换为零LARGE()：

{=
IFERROR(
  MOD(
    SUMPRODUCT(
      LARGE(
        IFERROR(
          +($C$1:$C1=C2)*(100*ROW($E$1:$E1)+$E$1:$E1)
          +($D$1:$D1=C2)*(100*ROW($F$1:$F1)+$F$1:$F1),
          0
        ),
        {6,5,4,3,2,1}
      )
    ),
    100
  ),
  0
)}

笔记：

  • 此公式需要以数组形式输入。通常，SUMPRODUCT()函数内的表达式会作为数组进行求值，但嵌套内的表达式IFERROR()是例外之一。
  • 由于公式是数组输入的，因此SUM()可以使用 代替SUMPRODUCT()。（请记住，它最初仅用于强制数组评估没有要求以数组形式输入公式。）

相关内容