专栏名称: Excel之家ExcelHome
excel技巧原创教程每日推送,excel表格职场模板干货仓库,图文/视频/动画等多种教学方式分享excel操作技巧教程/excel函数公式教程/excel数据透视表教程/excel图表教程/Word教程,助您轻松提高办公效率!
目录
相关文章推荐
Excel之家ExcelHome  ·  实例分享:按条件统计不重复数 ·  5 天前  
Excel之家ExcelHome  ·  这些函数作用大,加班再多也不怕 ·  5 天前  
Excel之家ExcelHome  ·  Excel 年终测试题 ·  4 天前  
Excel之家ExcelHome  ·  一组实用小技巧,高效办公离不了 ·  4 天前  
完美Excel  ·  使用VBA获取单元格公式 ·  6 天前  
51好读  ›  专栏  ›  Excel之家ExcelHome

这些函数作用大,加班再多也不怕

Excel之家ExcelHome  · 公众号  · Excel  · 2025-01-12 07:45

正文

今天和大家分享几个常用函数的典型用法。


1、数据转置
如下图所示,需要将A列中的姓名,转换为多行多列。
D6单元格输入以下公式,按回车:
=INDEX(A:A,SEQUENCE(E3,E4,2))&""
SEQUENCE函数的作用是按指定的行列数生成序号。
公式中的“SEQUENCE(E3,E4,2)”部分,用SEQUENCE函数根据E3和E4单元格中指定的行列数,得到一个从2开始的多行多列的序号。
最后用INDEX函数,以SEQUENCE函数得到的序号为索引值,返回A列对应位置的内容。
当INDEX函数引用了空白单元格时,会返回一个无意义的0,公式最后加上&“”, 作用就是屏蔽这个无意义的0值的。

2、销售业绩排序
如下图所示,要根据C列的销售套数,使用公式得到排序后的销售记录。
E2单元格输入以下公式,按回车。
=SORT(A2:C15,3,-1)
SORT函数的作用是对数据区域中指定的行列进行排序,常用写法是:
=SORT(数据区域,[对第几列/行排序],[升/降序],[按行/列排序])
本例中,第二参数使用3,表示对A2:C15区域中的第3列排序。
第3参数使用-1,表示按降序排序。
第4参数省略,表示按列方向排序。

3、多条件排序
如下图所示,要根据销售处和销售套数两个指标,使用公式得到排序后的销售记录。
E2输入以下公式,按回车。
=SORTBY(A2:C15,A2:A15,1,C2:C15,-1)
SORTBY的作用是对指定区域按多个条件进行排序,常用写法是:
=SORTBY(数据区域,要排序的列1,[升序/降序],[要排序的列2],[升序/降序]..)
本例中,第1参数指定要排序的A2:C15单元格区域。
第2参数指定排序主要优先级的列A2:A15,第3参数使用1,表示对该列使用升序。
4参数指定排序次要优先级的列C2:C15,第5参数使用-1,表示对该列使用降序

4、计算中式排名
使用RANK函数排序时,相同数值会占用名次。比如对 10、10、9进行排序,两个10具有相同的名次1,而9的名次为3。
在一些比较特殊的场景下,会要求使用中式排名方式,即相同数值不占用名次。比如对 1010、9进行排序,两个10具有相同的名次1,而9的名次为2。
如下图所示,需要以中式排名方式计算考试排名。
D2单元格输入以下公式,向下复制:
=SUM(N(UNIQUE(C$2:C$15)>C2))+1
UNIQUE函数的作用是在数据表中提取不重复值,工作方式类似于删除重复值功能。
“UNIQUE(C$2:C$15)”部分,先使用UNIQUE函数提取出C2:C15单元格区域中的不重复值。
接下来再用这些不重复值与C2进行比较,如果这些不重复值大于C2,则返回TRUE,否则返回FALSE:
{FALSE;FALSE;FALSE;……;FALSE;FALSE}
再使用N函数,将这些逻辑值转换为数值,FALSE转换后的结果为0,TRUE转换后的结果为1。
最后用SUM函数求和,得到比C2大的不重复值个数。再加上1,就是中式排名的名次。

图文制作:祝洪忠

1、点击或搜索微信公众号【 Excel之家ExcelHome】到公众号首页。

2、点击右上角【···】,在下拉菜单中点击【设为星标】。

设置星标后,小伙伴们就不用担心错过咱们的推送了!

设为星标,精彩内容不错过

推荐文章
Excel之家ExcelHome  ·  实例分享:按条件统计不重复数
5 天前
Excel之家ExcelHome  ·  这些函数作用大,加班再多也不怕
5 天前
Excel之家ExcelHome  ·  Excel 年终测试题
4 天前
Excel之家ExcelHome  ·  一组实用小技巧,高效办公离不了
4 天前
完美Excel  ·  使用VBA获取单元格公式
6 天前
同道大叔  ·  性对女人来说意味着什么?
7 年前
健康常识百科  ·  中国老家规 ! (这就是教养)
7 年前