说明书大全网 - 海量说明书在线查询
手机版  |  网站地图

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)

今天的需求有点烧脑,但是学会合理设置辅助列,问题就变得非常容易解决。

整个年级的成绩表,表格的布局是以班级为单位,列出所有人的成绩,每个班级下面有个汇总成绩。

如果需要在不改变表格布局的情况下,给每个班级的成绩按降序或升序排序,难道要分别选中每个班级的区域,一次次排序?有没有办法一次性排序?

案例:

在下图 1 中,如何分别对每个班级的成绩按从高到低和从低到高排序?

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(1)

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(2)

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(3)

解决方案 1:按分类降序排序

1. 在“班级”前面增加辅助列,按如下方式构建数据:在每个分类的最后一行和分类汇总前面填写“1”

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(4)


2. 在 E2 单元格输入以下公式,下拉复制公式:

=SUM(A2:$A$18)

公式释义:

  • 这个公式是用于计算 A 列的当前行至末行区域的总和
  • 请注意:第一个参数需要相对引用,而第二个参数要绝对引用,这样向下拖动公式的时候,起始行会动态变化,而末行固定不变

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(5)

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(6)


3. 将 F 列作为排序辅助列,在 F2 中输入以下公式,向下拖动复制公式:

=E2*10^6+D2


公式释义:

  • E2*10^6:用 E 列的数值乘以一个数量级足够大的数字,此处为 10 的 6 次方,这样可以使得需要排序的区域处于同一个大的数据区间
  • =E2*10^6+D2:用上述数据加上分数,用于排序

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(7)

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(8)

4. 现在只要对 F 列执行降序排序,就可以在不改变班级列表及小计的情况下,按班级内的排名,将分数从高到低排序:


选中 B 至 F 列的表格区域 --> 选择菜单栏的“数据”-->“排序”

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(9)


在弹出的排序对话框中,按以下方式设置 --> 点击“确认”:

  • 勾选“数据包含标题”
  • 主要关键字:选择“排序列”
  • 排序依据:选择“数值”
  • 次序:选择“降序”

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(10)


这就是排序后的效果,在保持表格布局不变的前提下,实现了每个班级的降序排序。

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(11)

解决方案 2:按分类升序排序

1. 要按升序排序的话,最大的区别在于“班级”前面辅助列的数据构建上:在每个分类的第一行数据前面填写“1”

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(12)


2. 在 E2 单元格输入以下公式,下拉复制公式:

=SUM($A$2:A2)

公式释义:

  • 计算 A 列的起始行至当前行区域的总和
  • 请注意:第一个参数需要绝对引用,而第二个参数要相对引用,这样向下拖动公式的时候,起始行固定不变,而末行会动态变化

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(13)

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(14)


3. 将 F 列作为排序辅助列,在 F2 中输入以下公式,向下拖动复制公式:

=E2*10^6+D2

公式释义:

  • 跟降序的公式原理一样,给需要排序的区域设置不同的数据区间;将每个数据区间加上分数,用于排序

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(15)

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(16)

4. 现在只要对 F 列执行升序排序,就可以在不改变班级列表及小计的情况下,按班级内的排名,将分数从低到高排序:


选中 B 至 F 列的表格区域 --> 选择菜单栏的“数据”-->“排序”

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(17)

在弹出的排序对话框中,按以下方式设置 --> 点击“确认”:

  • 勾选“数据包含标题”
  • 主要关键字:选择“排序列”
  • 排序依据:选择“数值”
  • 次序:选择“升序”

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(18)

同样,在保持表格布局不变的前提下,实现了每个班级的升序排序。

excel分类汇总怎么操作步骤(excel排序后分类汇总的操作步骤)(19)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。