专栏名称: Excel之家ExcelHome

excel技巧原创教程每日推送，excel表格职场模板干货仓库，图文/视频/动画等多种教学方式分享excel操作技巧教程/excel函数公式教程/excel数据透视表教程/excel图表教程/Word教程，助您轻松提高办公效率!

这些Excel公式，复杂计算变简单

Excel之家ExcelHome · 公众号 · Excel · 2025-01-02 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。

在一些比较特殊的场景下，会要求使用中式排名方式，即相同数值不占用名次。比如对 10、10、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，就是中式排名的名次。

图文制作：祝洪忠