想象一下一个比赛,如上图所示,四支球队进入了比赛。(表1中的得分显示了两支球队的得分;对于第二支球队,其正负号是相反的。)
如何执行以下分析:
如果TeamC没有参加比赛,新的决赛成绩将是什么?(TeamC的所有比赛都将被忽略/丢弃。)
(以防万一,示例文件在这里)
这是一个综合解决方案,当通过下拉列表排除团队时,使用自动刷新的数据透视表动态显示备用最终排名。
它会从数据透视表中Table2
彻底删除被排除的团队,并通过表的自动调整来删除所有空白。
对于Table1
,它使用条件格式使包含排除的团队的行变暗。这张桌子最糟糕的是将回合完全隐藏起来,留出空白行。
这是您的工作表,其中显示了添加的修改:
公式:
请注意,以下所有公式均使用Excel 2007结构化引用,但也适用于所有更高版本的Excel。
公式1-输入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:
将以下代码添加到工作表的工作表模块:
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
每当排除的团队中的值发生更改时,此代码都将仅刷新数据透视表。严格来说,解决方案不是必需的,但是在更换团队之后,您将必须手动刷新(例如,通过右键单击数据透视表并选择“刷新”)。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句