Indirect的黑科技应用-跨表数据透视表
前几篇文章我们介绍了Excel中讨论度较少，但是我个人评价很高的几个函数，比如，indirect定位函数：
初识Excel被低估的数据操作神器 - indirect函数
又比如match查找函数：
Excel中的被vlookup光芒掩盖的函数-match
这些函数说实话，比起Vlookup之类的网红函数略显晦涩，而且使用起来多少有些不便。那我为什么还要花大量时间介绍这几个函数呢？
因为他们组合起来，可以称之为报表神器。
老规矩，介绍我们今天的进阶用法之前我们先来构造一个场景。今天介绍的用法比较繁琐和复杂，但是并没有新的知识。但是我们可以用在一个非常实际的场景。
现在还是假设我们集团有N多个事业部，这里的N可能非常大，为了演示方便，我们假设有2个事业部，BU1和BU2，他们主要是销售图书的。每个月都会上交到总部一个销售业绩表，显示每种书号每个月的销量。我们作为总部人员，需要汇总这些数据。
听起来很简单是不是。
但是大家工作的时候都会有一些各自的小习惯，比如BU1的小A是一个粗糙的人，他每个月的数据都是手工输入，输入完了就上缴，他报表长这样：
有时候写销量，有时候写销售，有时候直接写个月份，毕竟这个表就是统计销售量的，可能就懒得写了。
而BU2的小B是个很细心的人，并且会做好分类汇总：
不但每个月的表头规规矩矩，而且每个季度还有自己的汇总。
不但如此，两个事业部的销售范围还有一些不同。BU1的产品线更全，有10个产品，而BU2则只有8个产品。
现在我们作为总部对接的人员，每个月都会收上来这些报表，即便是三令五申大家一定要按照格式填报，还是会因为人员更替，或者就是个人性格问题，导致报表格式混乱。
抱怨不是办法，我们现在只能靠我们自己来汇总这些“脏数据”。
下面我们整理一下思路。在这个例子里面，数据虽然乱，但是还没有完全乱。通过观察我们可以发现，表头至少都在第一行，而书号都在第一列。那我们需要做的就是，通过某种方法定位到各张工作表中的“总计”单元格，将数字取出来，汇总在我们的工作表上。
假设我们最终的汇总表长这样：
那我们现在就需要根据这张表的行和列来寻找对应的数据，比如B2单元格就会根据它对应的表头寻找“BU1”工作表的”1月“的数据。
这听起来是不是可以适用indirect来定位？对的
比如我们看之前的例子，BU1的工作表中1月的汇总在B12格，所以我们要取到这个数可以简单的写下：
=indirect('BU1!B12')
看一下结果：
聪明的朋友肯定发现，这个公式里面的BU1其实可以固定到A列的单元格上，这样我们就可以根据A列的变动，在不同的事业部的表格中取数了，所以先把公式改成：
=indirect($A1&'!B12')
（记得A1可以把A绝对引用，这样自动填充时比较方便）
但是这个B12比较烦人，每张表的位置都不一样。怎么办呢？
我们在手工统计的时候时怎么找到对应的数字的，我们通过观察第一行和第一列，找到“1月”字样的那一列，比如这里是第二列，以及“总计“字样的那一行，比如这里是第12行，然后将2列12行翻译成B12，再取到数的。
那有没有办法不要翻译呢，因为英文和数字互相转换还挺麻烦的。
可以！
indirect有一个所谓的R1C1样式，就是我们只需要输入行列的数字一样能取到单元格，只需要添加相应的参数就行。我们来改造一下这个函数，改写成：
=indirect($A1&'!R12C2', FALSE)
效果如下：
数字没有问题。
那现在问题变成了，我们如何找到对应的行数和列数。
先解决行，通过观察我们发现，这个例子中虽然月度总销量的具体行数不一样，但至少大家都成为总计，所以我们只要找到”总计“这个词所在的位置，就能知道加总数在第几行。
这时候就想到了我们的match函数。我们可以通过：
=match('总计','BU1!A:A',0)
来知道对应的行数，甚至，BU1这个信息也可以通过indirect来跟着单元格来变化，进一步改成：
=match('总计',indirect($A2&'!A:A'), 0)
然后将match的结果嵌套到原来的indirect中：
=INDIRECT($A2&'!R'
&MATCH('总计',INDIRECT($A2&'!A:A'))&
'C2',0),FALSE)
（这里公式长的已经写不下了）
虽然很罗嗦啊，但是很好用，我们现在已经能在不同的工作表中找到对应的总计数了。
百尺竿头更进一步，我们希望对应的月份也能够自动定位，这里就要用到我们之前提到的通配符：
一招解决用Excel统计时公司名称不规范难题
通过利用“1月*”这样的形势来找到对应的列数，公式如下：
=match('1月*',BU1!1:1,0)
当然，我们这里要改造一下让这些参数随着单元格变化，进一步修改成：
=MATCH(B$1&'*',INDIRECT($A2&'!1:1'),0)
然后把这一坨整个塞进刚才那个很夸张的函数中：
=INDIRECT($A2
&'!R'&MATCH('总计',INDIRECT($A2&'!A:A')) （寻找总计行）
&'C'&MATCH(B$1&'*',INDIRECT($A2&'!1:1') （寻找月份列）
,0),FALSE)
这样取数逻辑就完成了。
我们放在工作表中看一下效果：
效果非常好！这样即使我们有个100个事业部，需要统计全年的销售情况，也可以通过简单的自动填充完成这张100*12的跨表取数的数据透视表，并且还是实时更新的。
最后说几句。
有朋友可能会问，这公式写成了这个鬼样子，然而并没有解决多大的问题。如果总计那一行的中文写的也乱七八糟怎么办？如果表头不都在第一行和第一列怎么办？学这么冷门的公式还不够累的。
我想说的是，每一个解决方案都有自己适用的场景。的确，这个函数写的啰嗦又抽象，但是是能解决一些平时工作中可能碰到的问题的。而且因为这个方案不需要任何运行环境，可以说是非常“轻量”的方案了，任何版本的Excel都能实现，需要的只是编写函数的人花一些精力而已。
如果我们的原始数据更脏，我们可能会上一些更“重”的解决方案，比如VBA，比如Python。这就需要我们来衡量问题场景和实现的复杂程度了。但是无论怎么样，我们作为操作数据的人，掌握更多的技术方案是没有任何坏处的。所以不要想太多，如果这个场景正好和你遇到的问题有相似的地方，那就作为一个参考吧！