实用Excel技巧分享:按条件进行排名的公式套路

胖明大大_6315

胖明大大_6315

2023-04-14

5746人浏览

转载

说到将excel中的数据进行排名,大家首先想到就是rank函数,但如果说要按条件对数据进行排名呢?小伙伴们是不是一下子就蒙圈了,似乎还没有听说过按条件进行排名的函数。那么今天就给大家分享一个在excel中按条件进行排名的公式套路,一起来看看吧!

 

在Excel的函数中,有按条件求和的SUMIF,有按条件求平均值的AVERAGEIF,也有按条件计数的COUNTIF,最新版本中甚至有了按条件求最大值的MAXIFS函数和按条件求最小值的MINIFS函数。可是唯独没有可以按条件排名次的函数。

但是按条件排名次这类问题平时又的确会遇到,例如下面这个问题就是其中的一类典型代表:

我们都知道使用RANK函数可以得到一个数字在一组数字中的排名,在这个例子中的总排名就是用了公式=RANK(C2,$C$2:$C$19)得到的。

但是如果要得到每个门店在区域内的销售排名该怎么办,难道要在每一个区域中分别使用RANK函数进行排名吗?

虽然这也是一个思路,但是效率之低可想而知,其实在Excel的函数中,是有一个可以实现按条件排名次的函数,它就是SUMPRODUCT。

在正式介绍按条件排名次的公式套路之前,让我们先来理一理按条件排名的运算原理。

以10004这个门店为例,区域内排名是2,总排名是10,如图所示:

它的区域排名之所以是2,很容易理解,因为在同一个销售区域(条件)中,只有六个数,在这六个数字中,大于56.55的只有1个数就是79.72,因此它在区域内的排名就是2。

其他名次的计算原理也是一样的,这样想来,实现按条件排名其实包含了两个过程:条件的判断和大小的判断。

把这两个过程用公式写出来就是:$A$2:$A$19=A2和$C$2:$C$19>C2,可以结合实例来理解这两部分。

首先看第一个,$A$2:$A$19=A2会得到一组逻辑值:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

从这个结果中可以看出,与要统计的门店在同一个区域的数据都是TRUE。

$C$2:$C$19>C2同样也会得到一组逻辑值:

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

Excel公式工具(专业版)
Excel公式工具(专业版)

从自然语言描述生成Excel公式,诊断表格错误,支持VLOOKUP、条件求和等常用函数。

下载

这个结果表示销售额大于要统计门店时也会得到TRUE。

现在的问题是如何将这两个部分合并起来,因为这是对一个数据同时进行的两个判断,所以将两组逻辑值相乘,来看看得到了什么结果:

图中的这一组由0和1构成的数据,是($A$2:$A$19=A2)*($C$2:$C$19>C2)计算得到的结果,表示10001这个门店所在的区域中,销售额高于14.46的有4个门店(4个1),只需要对这个结果求和,基本上就实现了排名的目的,因此公式套路也就有了:

=SUMPRODUCT(($A$2:$A$19=A2)*($C$2:$C$19>C2))

不过这样得到的结果有个问题,名次是从0开始的,要解决也很简单,有两个方法。

方法1:直接在公式后加1,结果如图所示。

方法2::将大于号改成大于等于,结果如图所示。

这两个方法,通常情况下并没有什么区别,使用哪个公式都可以。

以上是针对一个条件进行排名的公式,如果条件是两个或者更多,将公式套路进行扩展就行:

=SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)* (数据区域>数据))

具体示例就不列举了,相信大家理解了公式的原理以后,结合具体问题去自己套用是完全没问题的。

相关学习推荐:excel教程

相关文章

WPS零基础入门到精通全套教程!
WPS零基础入门到精通全套教程!

全网最新最细最实用WPS零基础入门到精通全套教程!带你真正掌握WPS办公! 内含Excel基础操作、函数设计、数据透视表等

下载

相关标签:

excel

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Excel 教程
Excel 教程

共162课时 | 46万人学习

成为PHP架构师-自制PHP框架
成为PHP架构师-自制PHP框架

共28课时 | 3.7万人学习