为Sunny定制Collusion Report Template之EXCEL技巧之一：INDIRECT函数

Sunny的Collusion Team需要BO提供相关的作弊检查信息。基本上就是基于一个可疑的玩家，其它玩家与它对局的相关数据：两人一共一起玩了多少局，一起玩时的胜率，一起玩时的收入等数据。她所希望得到的数据如下图所示：

这样的需求如果用BO在数据库来做的话，需要递归地做很多查询，性能很不理想。之前我们有试验性的做过尝试，查询的速度很慢。这样我希望通过把Game History里的数据eport成EXCEL文件的方式，在EXCEL中使用公式来计算（统计）出相应的数据。其实分析这个报表，Sunny想要的数据都是从基础数据中运算或者统计得出来的。而这个基础数据，就是Game History中的数据。

经过一段时间的尝试，这个template做好了，"PlayertoPlayer"这个sheet是结果试问页，"Raw0"是基础数据页，从Game History报表里Export出来的数据就粘贴到这里；"Raw1"到"Raw5"是复制数据页，它们从"Raw0"页中复制出的数据，同时针对A53到A56的这四位玩家，分别统计出相应的数据，最后再在"PlayertoPlayer"这个sheet上做汇总。

好了，基本情况交待得差不多了。来说一下今天的主角：INDIRECT函数。

这个INDIRECT函数在office的官方帮助文件上是这样写的：“返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数 INDIRECT。”。也有下面的例子来说明：

1 2 3 4 5 6 7 8 9 10

A B 数据 数据 B2 1.333 B3 45 George 10 5 62 公式 说明（结果） =INDIRECT($A$2) 单元格 A2 中的引用值；即先将($A$2)的值计算，得出B2，再取B2单元格中值 (1.333) =INDIRECT($A$3) 单元格 A3 中的引用值；即先将($A$3)的值计算，得出B3，再取B3单元格中值 (45) =INDIRECT($A$4) 如果单元格 B4 有定义名“George”，则返回定义名的值 (10) =INDIRECT("B"&$A$5) 单元格 A5 中的引用值； 即先将($A$5)的值计算，得出"5"，再把字符串"B"连接到得出的"5"上得到"B5"，最后取单元"B5"格中值 (62)

 

不过还是来看我们的例子会明白一些：从第二张图上我们可以看出：单元格B53中的公式是：=COUNTIF(INDIRECT("Raw"&ROW()-52&"!Q:Q"),">0")，这里是先把当前单元格对应的行数计算出来，得到53，然后减去52，得到1，再去字符串"Raw"和"!Q:Q"连接（记住"&"在这个函数里是字符串连接符），得到"Raw1!Q:Q"。 所对在这一个单元格里，这个公式最终被当作=COUNTIF(Raw1!Q:Q,">0") 来执行的。同样的公式，当定义在B54这个单元格里时，就会被运算成=COUNTIF(Raw2!Q:Q,">0") ；注意这是工作表的名字从“Raw1”变成了“Raw2”。这样通过INDIRECT函数，我们可以对于RAW1到RAW2里的相同列的数据进行相同的运算，因为每一个数据表里存放着针对一名玩家的数据，这样对于不同的玩家的数据的汇总工作就可以方便的完成了。