公式单元格排序原理解析

问题：如何对公式单元格进行排序？

背景：

由用户发现对一组单元格进行排序的时候，发现没有效果。

查看了用户提供的文件（附件demo.ssjson），发现Sheet2 中B2：B4单元格是跨sheet公式。

尝试对B2：B4单元格设置升序/降序，从结果来看确实没有发生变化，这是怎么回事呢？

步骤：

结合附件的示例文件，先理解两个概念：

1、相对引用 和 绝对引用

2、公式调整

下图说明 相对引用 和 绝对引用 的区别。

一、

在微软Excel中，

对于不跨sheet的公式（相对引用写法）进行排序时，单元格实际已经做了排序，只是因为"公式调整"原因，表现为结果未发生变化。

验证步骤：

打开1.xlsx，选择B4：B5区域，设置升序/降序（如弹出弹窗，勾选给当前选择区域排序），观察结果。

您会看到，此时排序结果并未发生变化。

但实际上，排序的逻辑已经被执行了，如下步骤测试：

打开1.xslx文件，选择B10：B12区域（B10为公式，B11 B12为值），设置升序，观察结果。

您会看到，此时的排序结果为 1 2 0，与预期的结果并不一致，反而值还改变了。

这是由于 相对引用+公式调整 的原因。

如何理解公式调整？如下步骤测试：

打开1.xslx文件，选择B18单元格，你会看到公式为 "=A18"。ctrl+c 复制，并粘贴到其他单元格如E18，然后ctrl+v粘贴。

你会看到，此时E18的值为2，公式为 "=D18"。

明明是做了复制粘贴操作，为什么E18的公式不是 "=A18"，值不是1呢？

这就是因为公式做了自动调整，在相对引用的影响下，公式会根据周围单元格的位置做出调整。

如果公式用了绝对引用，那么公式就不会调整了，筛选结果也就能达到预期了。

验证步骤：

打开1.xslx文件，选择B23:B5，设置降序。你会看到，此时筛选结果符合预期了。

（更多 绝对引用 相对引用的概念，您可以网上搜索 Excel绝对引用相对引用 学习了解）

二、

在Excel中，

对于跨sheet 相对引用 公式，Excel在筛选结果上，表现的像绝对引用了。即不会做公式调整，筛选结果符合预期。（也即是您帖中提供的ssjson文件使用场景）

但这样的设计其实是有问题的，举个例子来说明下。

打开附件的2.xlsx, 选择B4:B6区域，设置降序，观察排序结果。

你会看到，排序结果不仅不是 9 7 5，反而是 7 7 7这种看似毫无逻辑的值。

原因就是 不跨sheet的部分进行了公式调整，跨sheet部分没有做公式调整，二者一块用就出现这个问题了。

三、

SpreadJS不清楚微软Excel为什么这样设计，如果混合使用时容易出现难以判断的错误，它认为Excel的这种设计不合理。

所以 对于跨sheet 和 不跨sheet 两种场景下统一使用了相对引用方案，即表现为您问题中结果 - 筛选后结果并未变动。

对于解决方案：

将文件中公式相对引用修改为绝对引用即可，如下图。

文章中所用附件请参考该链接。

作者: Lynn.Dou | 审核：Ellia.Duan | 更新时间：2023.12.10