这是一个答疑问题,有公式的单元格为何排序后乱序?
如下图示例,在表格中,“去年”,“今年”,“同比”这三列都是使用公式的,接下来我们想要根据“产品定位”和“同比”进行升序排序。
为了方便观察,我们将“今年”,“去年”,“同比”这三列复制为值,作为对照组。接下来我们进行排序看看。
示例1:
排序之后,数值对应不上,完全不是我们想象的样子。这是为什么呢?
原因分析:
实际验证可以发现,是因为排序后,公式引用位置出现了错误,那么为什么会出现这个原因呢?
单元格中的公式和引用单元格是有相关性的,跨表引用的时候,Excel默认被引用的表格中单元格位置是固定的。比如对于“面粉B”它原始公式是SUMIFS(今年!$B:$B,今年!$A:$A,Sheet2!$A2)。公式不会随着单元格排序而自动更新,排序之后,表格中的公式没有变化。由此得出,公式中有跨表引用排序必出错。
我们在看另外一个示例。
示例2:
看如下示例。面包粉BCD分别是以面包粉A的数量为基数,分别乘以2、3、4,排序之后还是错误。
这里分析一下,比如面包粉B,它的公式在G12单元格,公式是G11*2。这实际是公式所在行和引用地址有个相对关系的,排序之后面包粉B公式在G13,公式是G12*2,这个公式和引用地址相对关系并没有改变,但是已经不符合当初的逻辑了。
公式不自动变化会出错,公式自动变化也会出错,那还有解决办法了吗?
解决思路:
1、公式保存为值之后在排序,这是万能保险法。缺点,对于经常排序可能有点麻烦。
2、将公式定义名称,根据实际情况选择。
以示例1为例。选中今年列的公式,然后打开---公式--定义名称。对公式指定一个名称。对去年列也是同样操作。
之后今年列中用名称替代公式,输入=,然后按F3,粘贴名称。下拉填充名称。和原始公式的效果一样,但是排序后结果不会错位。
总结:有公式的单元格排序后会错乱的原因,就在于公式所引用的位置是否符合表格的计算逻辑。注意是表格的计算逻辑,不是Excel的计算逻辑。
所以重点还是不能对知识一知半解,学习,不能有套路。就得扎扎实实打好基本功,没有一个方法可以应用于所有情况,只能说具体问题具体分析。
excel中如何让含公式的空白单元格不参加排序
选逗乱中e3到e13,点衡指大菜单的格式,条件格式,条件格式一,公式,=or(c3=d3,d3=e3,e3=c3),格式咐竖设置为字体白色,确定。Excel设置指定单元格数据不参与排序的方法
Excel 是微软办公套装软件的一个重要的组成部分,排序是其中比较重要的工具。现在要求我们规定其中几组数据不进行排序,只排序其余的部分,要怎么做呢?下面我就教你Excel设置指定单元格数据不参与排序的 方法 。希望滚昌对你有帮助!
Excel设置指定单元格数据不参与排序的方法①打开表格数据,选中数据区域,单击菜单栏--数据--筛选,此时标题行就会出现一个小三角箭头。
Excel设置指定单元格数据不参与排序的方法图1②单击最右边那一列的三角箭头,从弹出的下拉菜单判备让中取消勾选不参加排序,确定。
Excel设置指定单元格数据不参与排序的方法图2
③这样,规定的不参加排序的数据就被剔除了,然后我们选择需要排序的数据,升序排列。
Excel设置指定单元格数据不参与排序的方法图3
④在将筛选功能关闭,原数据又看得到了,我掘局们清清楚楚的发现这些数据并没有计入排序中。
Excel设置指定单元格数据不参与排序的方法图4 猜你喜欢:
1. 怎么设置Excel2010某些单元格数据不参与排序
2. 禁止修改excel指定单元格数据数据的设置方法
3. Excel2013怎么设置不可输入指定的内容
4. Excel2013怎么设置不显示指定单元格里的0
5. Excel怎么设置限制单元格内容
6. Excel2013怎么按条件自动引用指定数据
7. Excel怎么求出指定单元格的位置