我的天

谈Excel，索引肯定是离不开的话题。从经典的lookup到用得很多的vlookup，到index+match，再到vlookup的升级版xlookup，所有的这些都是为了让搜索更方便。xlookup相对vlookup来说的确已经进步了不少，但无论是这些搜索的函数也好，其它按条件汇总的函数也好，总是有一些支持选择列，有一些不支持。之所以有支持和不支持之分，其中一个很重要的原因是支持选择列对写公式的那个人来说很方便，但关键是选择列可能严重影响搜索的性能。感觉上明明很简单的东西却要加载很长一段时间才能出结果，那个加载时间，甚至让你觉得是不可接受的慢，所以在使用这些索引函数的时候，绝大多数的教程都会提醒你数据源得用绝对引用。引用一个确切的范围，即便你把那个范围搞得很宽也行，但是引用一些空白行，又会导致一些意想不到的事情发生，尤其是对某些经典函数来说就会出错。当然，出现这种问题可能是因为我道行不够，如果是高手操作，什么问题都不是问题。

为什么搜索出来的结果可以动态显示，可以显示多行，但是被搜索的内容必须得用绝对引用呢？为什么教程里除了整列选择就没有一些动态确定索引范围的方式呢？隐隐之中我觉得新出现的公式应该可以做到，因为新出的那些高级函数出来的结果都是很动态的。虽然实际上近几年我已经很少关注学习那些新出的东西了，但是我还是有那么一点印象的。

在超级表里面使用公式，引用的单元格不再是经典的单元格名字，而是超级表的列名，前提是你用的那种公式不涉及跨行，如果跨行了，我好像没发现超级表能有什么超级功能，但是如果用超级表的偏移定位函数，的确能实现到达上下行。

xlookup是个比较新的函数，所以它能不能把索引的范围定为超级表的某一列呢？我发现的确是可以这么干的。如果有两个超级表，我要用超级表A作为索引，超级表B的某些数据作为查询条件。那么我在写xlookup公式的时候，我就可以完全用超级表的快捷引用方式。暂时我只尝试过一些比较简单的数据，出来的效果非常好，比我整列引用速度快很多。虽然xlookup是支持整列引用的，但那样的话很慢，而之所以很慢，我估计一个很重要的因素是xlookup只在高级的Excel里面使用，而高级的Excel文件又比低级的Excel文件行数多很多。其实我一直搞不懂为什么同样是Excel的公式，有些函数可以整列引用，有些却不行。有些虽然可以整列引用，但实际上效率不高。用同样的公式，一个引用的是超级表，一个引用的是整列，同样是xlookup，出来的结果就很有差别。如果你在超级表B更新了数据要索引超级表A的内容。引用整列的那个在你把数据粘贴到超级表B的时候，就已经会把你卡得痛不欲生。也正是因为有这种痛不欲生，所以以前当我要引用很多数据的时候，我选择的是用Power Query做一个后台匹配。因为如果用前台做这种整列的索引，整个文件哪怕不是互相影响的那些表，也会变得奇慢无比。在这里我巧妙的地方是利用了超级表的动态性。以前，如果要动态应用可能会增减数据表格内容的时候，好像需要套用offset。

当我要指定某片数据区域的时候，Excel其实是能感知其连片区域范围的，但只是在用公式索引的时候没有给用户一个直截了当的方案而已。

要实现某个功能，有非常多的做法，到底要怎么做才能避免出错呢？Excel的函数非常牛逼，把那些公式弄好了，简直就是天下无敌的节奏。公司的原理很简单，但是套用起来做着做着就傻瓜了。有可能是手贱，标点符号按错了，更大的可能性是单元格引用出错。我已经不记得用Office 2003的时候是个什么状态了。反正在Office 365下面，如果在一个工作簿里面应用了别的工作表，把带公式的工作表复制到另外一个工作簿里的时候，那些引用的单元格会以绝对地址的形式继续指向原来工作簿的某个表。显然，如果只是指向本工作簿的话，找不到地址，顶多显示错误，但是如果那东西继续指向原表，会引发很多问题。因为那是一个绝对地址，即便你把工作放在同一个文件夹下面，还是不能解决问题。恐怖的绝对地址会导致云同步、在多人协作的时候，发生状况。为什么Office 2003下就没有绝对地址和相对地址这个烦恼呢？在Office 365下，数据透视表的绝对地址算是终于改正过来了，但我想不到公式居然也这样。这是一个令人非常崩溃的事情！如果工作表里面的公式是大量的，这将是一个灭门灾难。如果某一个工作表里面的公式是大量的，而另外一个工作表里面的源数据也是大量的，外加还有一个数据透视表指向了源数据。无论是动哪一个，都会导致另外的那个上的工作推倒重来。为什么就不能设置某个工作簿里面的链接采用相对工作簿内的连接，而不使用外联呢？停止了外联，就意味着那个单元格的数据从一个动态的东西变成了静态的。绝对地址这个问题，在云同步、在多设备协作的情况之下，根本是无法操作的，为什么微软会犯如此低级的错误呢？数据透视表的傻逼是发生在Office 2010版之后的。公式上的傻逼，之前貌似我还没遇到过。可能并不是因为不存在，而纯粹是因为我没有用到这种功能。

但Excel不能一次性满足我所有想要的东西的时候，我就会想到要不要自己写个脚本解决一切难题。人想得到的逻辑，用脚本都能实现，前提是必须考虑出一种能够包容所有例外的规则。如果不能包含特殊情况，脚本写出来是毫无意义的，因为最终还是得人手去加工，这非常不科学。所以一定程度上，我更喜欢用数据透视表，自动处理那些东西，而不是手动设置公式。设置公式理论上是一个一劳永逸的过程，但实际上只要你手动在上面修改了一些东西以后，就像蝴蝶效应一样，后果不堪设想。某一次的修改，会导致往后半天都找不出原因。如果用数据透视表的话，双击单元格就会到达最基础的那个数据。查错是非常简单的，但是，用了公式以后，然后你又因为某次犯傻在某个你以为设置了公式的单元格里面输入了常数，那将导致一次令人绝望的debug。不要问我为什么会知道……

与其让我查找错误，还不如让我总结特点制定规则，让他们不犯错误。当然，不犯错误，是根本不可能的。

昨天我把平时都要更新的大型数据文件都从xls转变为xlsb。之前的那个文件没有删掉，而是把它记下最后的日期，放到某个地方，今天开始在新的文件里更新数据。到昨天为止，这个月坚果云的使用量是899MB。经过这样的数据转换以后，理论上，单位每天更新的数据应该不会超过5MB，除非有特殊情况，比如说要写一个比较大型的报告之类的，而里面又有很多图。如果Excel表格里面有很多图，估计就没办法缩小体积了吧。Excel可以在后面加个b缩小体积，那么Word可不可以也这么干呢？真的能把图片压缩吗？估计今天我得研究一下。格式转换的操作我是在Office 2003上面完成的，当然这必须得装一个转换插件才能正常运行，我顺便在WPS 2019上测试，打开xlsb文件没问题。我也把那个东西发给了在用WPS 2016的同事，她打开也没有问题。最后我在我的新笔记本电脑上也试着打开一下，同样没有问题，当我把xls文件转变为xlsx文件，在新的笔记本电脑上进行各种复制，结果发现数据透视表没有绝对相对引用的问题。也不知道是前天还是昨天我看到这么一条消息说Office 2016推荐的系统版本是Win10，几乎可以说，那个东西是Win10的定制版本，而Win7和Win8推荐的Office系统是2007或2010。于是这也就容易理解为什么我新笔记本电脑上的Office 2016没有数据透视表绝对引用的问题，但是在Win7上面哪一台机都有问题。不只是2016，2013也存在同样的问题。所以得出这么一个结论，如果要顺畅地使用2016，我的系统最好用Win10。估计这不是Office补丁补丁的问题，它们是配套使用的，虽然Win7和Win10都有32位和64位两种系统，但鬼知道Win10在Win7的基础上又改动了些什么。Office 2016之所以出现绝对引用地址的问题，是Windows的office模板那里有毛病。我不知道笔记本上的预装Windows以及office系统是怎么装上去的，估计不是人手一台一台来，但显然他们装上去的步骤是一个整套的流程，是一体的。现在卖的笔记本电脑基本上都是正版的家庭版Win10外加家庭版的Office2016。虽然现在最新的office应该是2019，但是微软最推荐你使用的是Office365。显然，买笔记本电脑的人估计不会愿意每年都给一笔费用使用Office365，所以2016就算是个比较稳定靠谱的选择。之前我已经听同学说过她的一些同事有些人用Office 2016没问题，但有些人有问题，我觉得估计不是软件版本的差异，而是他们Windows系统的差异，因为有些人用的Win7，而有些人用的Win10。

今天我之所以选择xlsb，而不选择更通用的xlsx是因为我知道xlsx文件在2016上用数据透视表，有可能出现绝对引用的问题，但现在显然在我唯一一台装了2016的电脑上没有问题。我需要把它换回来吗？其实没这个必要，因为xlsb文件的体积大概只有xlsx的一半。b和x的区别在于，b是二进制的工作簿，在一些软件上打不开，在没有安装office的前提下，x文件你还可以改它的后缀，改成zip文件，然后解压浏览里面的东西。x文件的通用性显然更强。但对我来说，我是不装office会死星人，所以这根本不成问题。b结尾的文件一般软件打不开，所以一定程度上是不是二进制的工作簿安全性更好呢？而之所以一个体积比另外一个大，是因为x文件里除了数据本身还有各种各样的xml格式。

微软这个博大精深的东西，即便我愿意花钱买2016，但只要我的Windows系统还是win7结果还是会死得很惨，所以想花钱也不能随便花。

这个月我突然发现一个很惊恐的现象，我的坚果云的1GB免费上传流量几乎爆掉了，为什么会这样呢？这一个月下来，米叔的东西是有点多，但是我整个文件夹都只有不到300MB，不可能会发生这种情况，而我每天都进行更新的就只有单位的统计数据的东西了。理论上坚果云是增量同步的，所以如果是同一个文件，我在里面新增了东西就同步那些，旧的东西不会再给我同步，但实际上，周末经过两天的观察，我发现情况不是这样的。每天我都会增加十几MB的上传数据，但显然我每天都加进去的东西不可能那么多，每天我加进去的东西顶多几MB而已。为什么会有几倍甚至十几倍的差别呢？昨天晚上我终于发现了原因！因为实际上它并没有传说中的那么神奇，坚果云直接把我更新过的文件重新上传覆盖上去了，不管那个文件里面之前有没有我已经存储过的内容。于是呢，这就让我比较惊恐了，因为我每天都要更新的文件里面有几个文件是MB级的，最大的那个甚至接近9MB，如果每天我都要更新数据，更新的文件达到15MB也就没什么奇怪的了。以每天15MB计算，一个月30天，即便我什么东西都不往里面添，450MB是跑走不掉。我该怎么解决这个问题呢？

这些数据文件绝大多数都是Excel数据表，我用的系统是Office 2003，所以文件是xls。以前我就发现用2007以上的xlsx更大大能节省空间，至于为什么我不知道。之所以仍然选择老版本，一定程度是因为我觉得用的习惯，而另一方面是因为我也曾经试过用Office 2013或者2016，结果发现他们的数据透视表有严重的绝对引用问题。我不知道那个东西在经过一轮又一轮的升级以后有没有得到改善，但显然我今年早几个月做测试的时候，那还相当糟糕，出来的东西完全不是我想要的。幸好那是年头，我只是改了几个数据表而已。据说这是因为xlsx看上去是一个Excel文件，但实际上是一个压缩文件，核心是xml所以它的体积要比传统的xls小起码一半，尤其是但文件里有缓存数据库的时候。xml不是微软自己的东西，有人曾经调侃，在2007版本以上出现的那个xml的绝对引用问题是临时工搞出来的，微软还没有完全吃透那个东西，就把它用在数据里面了。但是，据说如果用的是xlsb，就没有那个烦恼，虽然那个跟xlsx是同一代的产品，但是xlsb是微软自创的二进制的数据表，所以没那么多毛病。用xls保存文件大小是8.5MB，如果用x1sx，大小是二点多，如果用xlsb。文件就只需要一点多。这就意味着，那个东西能做到接近八倍的压缩，所以，如果平时我每天有15MB的数据量，经过这么一压缩，就会只剩下几个MB而已，一个月下来，那些东西顶多200MB就包住了。之前我一直觉得没什么问题是因为单位的数据量远远没有现在这么多，而另外一个情况是我一直很相信坚果云，我觉得xls是很普通的文件，增量同步应该没什么问题的，但显然，他们只是检测了文件有没有变化，如果有变化就整个换掉。到底什么文件上他们才可以做到增量同步呢？难道只能是txt文件吗？如果那样的话就实在太恐怖了。我还要测试一下在新版本的Office 2016环境下，是不是真的用xlsb就没有绝对引用的问题。xlsb对使用office软件的人一点问题都没有，但是如果对方使用的是WPS，非常有可能他们就打不开文件了。之所以这样是因为之前已经说过，那是微软自创的二进制模式，WPS没办法攻克这个也很正常。如果真的这样，我也就只能把只是我自己使用的文件转格式，而发给别人的文件还是用原来的样子。

之前我从来没试过坚果云单月超上传流量，但现在单位的数据真的把我逼上绝路了。

昨天我才非常不容易的地通过远程控制往家里的电脑安装了Office2013，但用了不到24小时，现在我又不得不把它卸载掉。熟悉上手2013需要一定的时间，而有些功能在过去的一天里我的确也已经喜欢上了，比如选择性粘贴的方式以及表格隔行不同的颜色格式，可是这种东西可以设置比较简单。选择性粘贴在2003里藏得比较深。让我决心要换2013的原因是数据透视表动态引用比2003简单，但同样，让我彻底摒弃2013的原因也是数据透视表。在没用过之前，你完全没想到居然会这样，但用过以后，你就觉得非常无语，即便2013再强大，这也不适合我，因为它的某些潜规则非常反人类。

昨天开始，我就发现Excel某些完全没有引用外部数据的表格里也给我弹出安全警示，说那个文档用了外部数据，不安全。按照我的思路，如果你有警示，理论上点开是不是应该说明外部数据所存在的页面，但没有，那只有一个启用按钮，你只能按下去，或者让黄色的警示长期存在，这简直折磨死强迫症患者啊啊啊。至于外部数据在哪里，你得自己找。第一次发生这种事是我把原来的xls文件另存为了xlsx，在打开文件的时候，我发现数据透视表虽然引用的是同一个文件簿里的数据，但仔细看那个引用源，前面居然加了一大串的绝对地址。那是我另存为之前的文件所在地址。昨天发现这个问题的时候，我以为这只是新旧格式转换的时候出现的毛病，但今天当我做完所有操作，用远程监控在家里打开这些表的时候，我傻眼了。今天新上传的文件也是这样，我完全没有对他们做奇怪的操作，在单位这台电脑打开，查看数据源是很正常，但是在家里打开，显示的数据源居然用的是单位这部电脑文件的绝对地址。发生这种事，我的第一反应是是不是自己在操作上有什么问题呢？但是当我把所有操作都重复一遍以后，还是没有发现问题。家里那台电脑同步更新数据的方式是坚果云。以前我一直都是这么干的，任何文件都没发生过这种问题。如果Excel数据表里引用的是其它数据表的内容，发生这种事还可以理解，但是，那明明就是同一个文件里的不同工作表而已。

为了这个我跟同学纠缠了一个上午，未果。她用2013已经好长时间了，一直觉得没有问题，当然也从来没有遇到我这种事情。单位的数据如果是有数据库，任何地方都能连上的，当然没有这个烦恼，但是显然我没有，我只能以我个人的方式，用云端的办法在不同电脑上同步数据。偶然的机会，我发现这不只是不同电脑的问题，即便同一个电脑，只要是2013生成的xlsx文件，用复制的方法建立副本，副本的数据透视表也是引用原文件的。也就是说，如果不是用剪切的方式，而是用复制的方式把一个文件放在电脑的不同位置，这几个文件，一样会存在引用错误。但是，如果打开源文件，使用另存为命令生成新文件就没有这个问题。如果同一个电脑复制有问题，任何云端同同步肯定都会不行。

之前我一直没找到跟我相似案例的搜索结果，但是当我搜索2013，以及复制文件以后数据透视表引用错误，结果一大堆，Office 2013是这样，2016继续是这样，只要xlsx，保存下来的文件都存在这个问题。想想都觉得很疯狂，这不过是非常基础的操作而已，但居然也会有这种问题。唯一能解决这个的大概是用微软自己的同步软件云端同步（不确定是否真行），用其他任何的备份软件，肯定都会导致非常恐怖的效果。如果数据透视表里面有很多引用关系而且很复杂，复制之后就存在这个问题，要重新把数据源都调一遍，那真的会把人搞疯。我觉得之所以这样大概是他们觉得从2013年开始，数据就不应该存在单机电脑上，我应该存在一个永远不挂的服务器上。如果是存在一个外部服务器上，用绝对引用当然很靠谱，但如果是存在外部服务器上，我做数据透视表的时候步骤就不是这样。数据透视表建立的时候，默认用的是同一个工作簿里面的东西，但现在这个最最最最最基础的功能，反而歇菜了。有点瑕疵，还能忍，但是这种关乎到我每天工作而且非常重要的事情，绝对忍不了。所以24小时之内，我在单位和家里的电脑里装了2013，但是又卸载掉了。

天知道2013存在这种致命的缺陷为什么居然还有人在用，而且还把严重bug继承给了2016！微软干这个项目的都是临时工吗？