这些公式用得好，年薪三万都嫌少

1、数据查询

如下图，F3单元格输入以下公式，向下复制到F4单元格，可以根据E列的姓名查找对应的领导姓名。

=XLOOKUP(E3,C$3:C$8,B$3:B$8,'查无此人')

XLOOKUP函数的作用是查找数据在一行或一列中所处的位置，并返回与之对应的另一行或另一列中的内容。常用写法为：

XLOOKUP(要找谁,在哪行或哪列找,返回哪行或哪列,找不到时返回什么)

公式中的E3是要查找的秘书姓名，C$3:C$8是查找的区域，B$3:B$8是要返回内容的区域。

如果C$3:C$8单元格区域中的某个单元格和E3中的内容相同，就返回B$3:B$8单元格区域对应位置的领导名称。如果C$3:C$8单元格区域中没有和E3相同的内容，公式返回“查无此人”。

2、随机排序

如下图，希望对A列的应聘人员随机安排面试顺序。

先将标题复制到右侧的空白单元格内，然后在第一个标题下方输入公式：

=SORTBY(A2:B11,RANDARRAY(10),1)

RANDARRAY也是Excel 2021版本中新增的函数，作用是生成随机数数组，本例公式使用RANDARRAY(10)，表示生成10个随机数的数组。

SORTBY函数的排序区域为A2:B11单元格中的数据，排序依据是按随机数数组升序排序。因为公式每次刷新所生成的随机数数组是不确定的，所以A2:B11单元格中的数据也会得到随机的排序效果。

3、指定范围的随机不重复数

如下图，要根据A列的姓名，生成随机面试顺序。

B2单元格输入以下公式：

=SORTBY(SEQUENCE(12),RANDARRAY(12))

先使用SEQUENCE(12)生成1~12的连续序号。

再使用RANDARRAY(12)生成12个随机小数。

最后使用SORTBY函数，以随机小数为排序依据，对序号进行排序。

4、判断所在部门

如下图所示，B列是一些带有部门名称的混合字符串，希望根据E列的对照表，从B列内容中提取出部门名称。

=INDEX(E$2:E$6,MATCH(1,COUNTIF(B2,'*'&E$2:E$6&'*'),))

COUNTIF第一参数为B2单元格，统计条件为'*'&E$2:E$6&'*'，统计条件中的星号表示通配符，也就是在B2单元格中，分别统计包含E$2:E$6部门名称的个数，结果为：

{1;0;0;0;0}

再使用MATCH函数在以上内存数组中查找1的位置。

最后使用INDEX函数，在E$2:E$6单元格区域中，根据MATCH函数的位置信息，返回对应位置的内容。

5、计算中式排名

使用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，就是中式排名的名次。

图文制作：祝洪忠