大家好,欢迎来到IT知识分享网。
Excel合并单元格将相同内容合并居中显示,从而使表格更加简单直观,但也带来一些麻烦,其中之一就是存在合并单元格的表格无法正常升降序。
如下图所示,如何实现组内的排序,将成绩由小到大排列。
如果我们直接筛选排序的话,Excel会报错,提示【若要执行此操作,所有合并单元格需大小相同】,直接筛选方法行不通。
如果仅筛选后两列(不筛选合并列),虽然可以正常升降序,但是会导致数据错位,此方法也不可取。
那么如何快速实现合并单元格小组内的排序?
组内排序
实现的方法需要借助辅助列。
第一步,D2单元格内输入公式,并填充(注意其中的相对引用与绝对引用):
=COUNTA($A$2:A2)*10^4+C2
第二步,选中后三列(除了合并单元格列,其它列都要选中),点击【筛选】—【升序】,搞定!
如果要组内降序,公式修改成:
= -COUNTA($A$2:A2)*10^4+C2
前面加个“-”负号即可。
原理讲解
首先要知道2个知识点:
1、counta是非空单元格计数函数;
2、合并单元格的内容其实是存放于最上方的单元格,其它单元格为空。
明白了以上两点,我们来看公式,D2:D4公式计算A2:A4非空单元格个数,由于A2:A4是个合并的单元格,其内容实际存放于A2单元格,所以D2:D4皆返回数字1;
同理D5:D7皆返回数字2;D8:D10皆返回数字3…..
这样所有合并单元格(小组)的数字是一样的。
接着我们再来理解为啥要乘以10的4次方(10000)?
要知道我们做的一切并没有修改Excel升降序的逻辑,最后一步升序的时候,Excel也是将D列的数字从小到大排列。
之所以要乘以10的4次方,是为了让三组之前的成绩差异扩大化!
下面的辅助列数据可以发现,A组与B组之间的数据相差了1万,B组与C组的数据相差了1万;
也就是说,无论如何升序,A组的数据不会跑到B组下面(因为没人家大)、B组的数据不会跑到C组下面,从而保证了各小组的排名只会在组内进行变动。
这样,即使最后一步升序的时候不带上A列(合并单元格列),其对应的姓名、成绩也不会发生错位。
下图是没乘10的4次方结果,如果升序出的话,数字130/132肯定会排列到底部C组,导致姓名、组别对应错误。
PS:如果数据本身比较大,比如成绩都是10W以上的数字,那么这里乘以10的4次方区分度就不够了,至少要乘以10的6次方。
相似场景
Excel中,很多场景也需要借助辅助列去完成,比如数据透视表的筛选。
我们知道,在数据透视表中是无法实现筛选功能的,如下图中,在透视表状态下,数据视图中的筛选按钮处于灰色状态,无法进行筛选。
那么如何实现透视表的筛选?
同样的是借助辅助列,鼠标点击 透视表 表头行 右侧的 空白单元格,就可以使用筛选功能。
小结
本篇文字有点多哈,看着可能枯燥了些,主要是将原理解释了下,这样有助于大家理解公式,理解的基础上进行记忆,下次才能快速使用。
以上就是今天的分享,希望对你有所帮助,觉得有用的话,记得点个赞+关注哟。
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/97172.html