Excel合并单元格将相同内容合并居中显示,从而使表格更加简单直观,但也带来一些麻烦,其中之一就是存在合并单元格的表格无法正常升降序。

如下图所示,如何实现组内的排序,将成绩由小到大排列。

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

如果我们直接筛选排序的话,Excel会报错,提示【若要执行此操作,所有合并单元格需大小相同】,直接筛选方法行不通。

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

如果仅筛选后两列(不筛选合并列),虽然可以正常升降序,但是会导致数据错位,此方法也不可取。

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

那么如何快速实现合并单元格小组内的排序?

组内排序

实现的方法需要借助辅助列。

第一步,D2单元格内输入公式,并填充(注意其中的相对引用与绝对引用):

=COUNTA($A$2:A2)*10^4 C2

第二步,选中后三列(除了合并单元格列,其它列都要选中),点击【筛选】—【升序】,搞定!

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

如果要组内降序,公式修改成:

= -COUNTA($A$2:A2)*10^4 C2

前面加个“-”负号即可。

原理讲解

首先要知道2个知识点:

1、counta是非空单元格计数函数;

2、合并单元格的内容其实是存放于最上方的单元格,其它单元格为空

明白了以上两点,我们来看公式,D2:D4公式计算A2:A4非空单元格个数,由于A2:A4是个合并的单元格,其内容实际存放于A2单元格,所以D2:D4皆返回数字1

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

同理D5:D7皆返回数字2;D8:D10皆返回数字3…..

这样所有合并单元格(小组)的数字是一样的。

接着我们再来理解为啥要乘以10的4次方(10000)?

要知道我们做的一切并没有修改Excel升降序的逻辑,最后一步升序的时候,Excel也是将D列的数字从小到大排列。

之所以要乘以10的4次方,是为了让三组之前的成绩差异扩大化

下面的辅助列数据可以发现,A组与B组之间的数据相差了1万,B组与C组的数据相差了1万;

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

也就是说,无论如何升序,A组的数据不会跑到B组下面(因为没人家大)、B组的数据不会跑到C组下面,从而保证了各小组的排名只会在组内进行变动

这样,即使最后一步升序的时候不带上A列(合并单元格列),其对应的姓名、成绩也不会发生错位

下图是没乘10的4次方结果,如果升序出的话,数字130/132肯定会排列到底部C组,导致姓名、组别对应错误

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

PS:如果数据本身比较大,比如成绩都是10W以上的数字,那么这里乘以10的4次方区分度就不够了,至少要乘以10的6次方。

相似场景

Excel中,很多场景也需要借助辅助列去完成,比如数据透视表的筛选。

我们知道,在数据透视表中是无法实现筛选功能的,如下图中,在透视表状态下,数据视图中的筛选按钮处于灰色状态,无法进行筛选。

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

那么如何实现透视表的筛选?

同样的是借助辅助列,鼠标点击 透视表 表头行 右侧的 空白单元格,就可以使用筛选功能。

excel排序怎么排名次由小到大(excel按成绩高低排名次)-天天办公网

小结:

本篇文字有点多哈,看着可能枯燥了些,主要是将原理解释了下,这样有助于大家理解公式,理解的基础上进行记忆,下次才能快速使用。