Excel文件体积爆炸与解决方法

  前几天做数据分析弄了个Excel表格，数据不多：

• 4张原始数据表，其中2张各约1500行16列，1张为前2张的叠加，约3000行16列，另1张约1000行2列。
• 1张分析表，约200行40列；
• 一个数据透视表。

  200行的分析表格中用了很多复杂的公式从原始数据表中取数加以计算。

问题症状

  弄完了保存表格，转圈转半天，保存完体积居然达到了50m。我从未见过如此大的Excel文件，哪怕是几十个表格几万行的审计底稿，最大的也就不到10m。重新打开这表格超过20秒，还会出现加载过程的滚动百分比。

  原以为是数据透视表的缓存问题，改掉保存规则后于事无补，甚至删掉透视表也几无变化 - 少了十几k而已。

问题症结

  后来问AI，一下找出了问题所在：爆炸的“已使区域”。排查起来也简单：

  点表格的任意位置，按Ctrl+End，软件会定位到表格使用区域的最后一个单元格。正常情况下，这个单元格差不多就在数据的末尾，有可能会超出一点。

  我在原始数据表格操作后，末尾单元格在第1,048,576行第20列。也就是说，针对这个表格，Excel不是记录了1500行，而是1百万行。

  回想起来，问题应该出在某一次在分析表里写的公式，为了实现只要在原始数据表加入数据就自动引入分析而把取数范围写成了1,048,576行（一个不可能达到的行）。

解决办法

  知道症结后，解决起来也简单：

1. 拷贝原始数据的真正范围，新建表格（自动命名为“sheet”），粘贴进去；
2. 修改原表格名字（如改成“数据-1”，方便后面调整），此时任何引用这个表格的公式会自动更新为“数据-1”；
3. 把新表格改成原表格的名字（如“数据”）；
4. 更新所有涉及原表格的公式，把自动调整的名字改回去（如把“数据-1”改成“数据”）。
5. 删除原表格。

  调整完后顺手优化了下分析公式，去掉了原来的合并原始数据表单。重新保存，体积变成了580k，整整缩小了100倍！

最后

  这大概就是传说中的代码屎山吧。