☰

Excel：如果某支球队没有参加联赛会怎样

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
Excel：如果某支球队没有参加联赛会怎样

在此处输入图片描述

想象一下有四支球队参赛的锦标赛，如上图所示。（表 1 中的分数显示两支球队的分数；对于第 2 支球队，加/减号是倒置的。）

我如何进行以下分析：

如果 TeamC 没有参加比赛，最终排名会如何？（TeamC 的所有比赛都应被忽略/丢弃。）

（万一，示例文件在这里）

答案1

这是一个全面的解决方案，当通过下拉列表排除某个团队时，它使用自动刷新的数据透视表来动态显示替代的最终排名。

它会将排除的团队从数据透视表中Table2完全删除，并且表格会自动调整以删除任何空白。

对于Table1，它会使用条件格式使包含排除队伍的行变暗。此表的最佳做法是完全隐藏回合，留下空白行。

这是显示添加的修改的工作表：

工作表截图


公式：

请注意，以下所有公式均使用 Excel 2007 结构化引用，但也适用于所有更高版本的 Excel。


一级方程式赛车 - 参赛F1:F14：

=1+INT((ROW()-ROW(Table1[Round]))/2/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9))


公式 2 - 输入数组（Ctrl++ Shift）Enter并G3复制粘贴/填充G3:G14：

=IFERROR(INDEX(Table1[[Team1]:[Team2]],SMALL(IFERROR(1/(1/N(IF(1,(ROW(Table1[Round])-ROW(Table1[[#Headers],[Round]]))*(Table2[[#This Row],[Round]]=Table1[Round])*($K$9<>Table1[Team1])*($K$9<>Table1[Team2])))),FALSE),1+MOD(INT((ROW()-ROW(Table1[Round]))/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9)),COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9))),1+MOD(ROW()-ROW(Table2[[#Headers],[Round]])-1,2)),"")

美化后的公式2如下：

=
IFERROR(
  INDEX(
    Table1[[Team1]:[Team2]],
    SMALL(
      IFERROR(1/(1/N(IF(1,
         (ROW(Table1[Round])-ROW(Table1[[#Headers],[Round]]))
        *(Table2[[#This Row],[Round]]=Table1[Round])
        *($K$9<>Table1[Team1])
        *($K$9<>Table1[Team2])
      ))),FALSE),
      1+MOD(
        INT((ROW()-ROW(Table1[Round]))/COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9)),
        COUNTIFS(Table1[Round],1,Table1[Team1],"<>"&$K$9,Table1[Team2],"<>"&$K$9)
      )
    ),
    1+MOD(ROW()-ROW(Table2[[#Headers],[Round]])-1,2)
  ),
  ""
)


公式 3 - 列中的公式H保持不变，为了完整性在此重复：

=SUMPRODUCT(Table1[Score]*(Table2[[#This Row],[Round]]=Table1[Round])*(Table2[[#This Row],[Team]]=Table1[Team1])-Table1[Score]*(Table2[[#This Row],[Round]]=Table1[Round])*(Table2[[#This Row],[Team]]=Table1[Team2]))


数据透视表：

  • 数据透视表的数据源需要更改为Table2。
  • 该Team字段需要取消选择空白值。

数据透视表设置截图


表格：

Table1- 将以下条件格式公式应用于范围$A$3:$D$8：

=OR($B3=$K$9,$C3=$K$9)

Table1- 将以下数字格式应用到列Score：

0;-0;

Exclude- 将数据验证设置K9为下拉列表，其来源如下：

=$J$3:$J$6


VBA：

在工作表的 Sheet Module 中添加如下代码：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address <> "$K$9" Then Exit Sub

  With Application
    .EnableEvents = False
    Me.PivotTables(1).RefreshTable
    .EnableEvents = True
  End With

End Sub

此代码只是在排除的团队中的值发生变化时刷新数据透视表。解决方案的运行并不严格要求这样做，但在更改团队后，您必须手动刷新（例如，右键单击数据透视表并选择“刷新”）。

答案2

使用当前布局，您可以在常规的第三个表（而不是当前拥有的数据透视表）中显示调整后的分数，如下所示：

我用常规表而不是数据透视表复制了您的工作表，并添加了一个名为“ExcludedTeam”的命名单元格，以便我可以向SUMIFS函数添加一个条件以进入最后一张表。

=SUMIFS(Table1[Score],Table1[Team1],[@Team],Table1[Team2],"<>"&ExcludedTeam)-SUMIFS(Table1[Score],Table1[Team2],[@Team],Table1[Team1],"<>"&ExcludedTeam)

用普通英语来说，这句话的意思是“将表 1 中的分数总和（其中 Team1 与此 Team 匹配且 Team2 与排除的 Team 不匹配）添加到逆表 1 中的分数总和（其中 Team2 与该 Team 匹配并且 Team1 与被排除的 Team 不匹配。）”

当然，您也可以将排除的球队的名称硬编码到公式中，但这根本不起作用……

在此处输入图片描述

如果你希望 TeamC 的得分为 0，则必须指定 Team1 和 Team2 <> 排除团队公式的两半。

编辑：根据评论，需要更多功能，我在下面描述了如何实现这些功能。

要从所有表格中删除对排除的团队的任何提及，您必须将表格一个接一个地设置，这样隐藏表 1 中的列不会隐藏表 2 中的任何列，反之亦然，或者将它们放在单独的工作表上。

完成此操作后，创建一个宏，当您更改 中选择的选项时触发该宏，该宏ExcludedTeam将设置.EntireRow.Hidden任一表中任何单元格的属性，其.Value = ExcludedTeam.Value为true。剩下的就是更改数据透视表的规格以过滤掉该团队，您就成功了。

相关内容