SpreadJS解答你公式单元格排序的困惑

收藏

本帖最后由 Lynn.Dou 于 2023-6-20 14:05 编辑

背景：
前几天有个用户反馈了一个问题，对一组单元格排序，发现没有效果。
查看了用户提供的文件（附件demo.ssjson），发现Sheet2 中B2：B4单元格是跨sheet公式。
尝试对B2：B4单元格设置升序/降序，从结果来看确实没有发生变化，这是怎么回事呢？
image.png66255492.png

步骤：
结合附件的示例文件，先理解两个概念：
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 两种场景下统一使用了相对引用方案，即表现为您问题中结果 - 筛选后结果并未变动。

对于解决方案：
将文件中公式相对引用修改为绝对引用即可，如下图。

image.png829873962.png

		自动登录	找回密码
密码			立即注册

SpreadJS解答你公式单元格排序的困惑

0 个回复

SpreadJS 认证

SpreadJS 高级认证

讲师达人

悬赏达人