如下图,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相同的内容,公式返回“查无此人”。先将标题复制到右侧的空白单元格内,然后在第一个标题下方输入公式:=SORTBY(A2:B11,RANDARRAY(10),1)RANDARRAY也是Excel 2021版本中新增的函数,作用是生成随机数数组,本例公式使用RANDARRAY(10),表示生成10个随机数的数组。SORTBY函数的排序区域为A2:B11单元格中的数据,排序依据是按随机数数组升序排序。因为公式每次刷新所生成的随机数数组是不确定的,所以A2:B11单元格中的数据也会得到随机的排序效果。=SORTBY(SEQUENCE(12),RANDARRAY(12))先使用SEQUENCE(12)生成1~12的连续序号。再使用RANDARRAY(12)生成12个随机小数。最后使用SORTBY函数,以随机小数为排序依据,对序号进行排序。 如下图所示,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部门名称的个数,结果为:再使用MATCH函数在以上内存数组中查找1的位置。最后使用INDEX函数,在E$2:E$6单元格区域中,根据MATCH函数的位置信息,返回对应位置的内容。使用RANK函数排序时,相同数值会占用名次。比如对 10、10、9进行排序,两个10具有相同的名次1,而9的名次为3。在一些比较特殊的场景下,会要求使用中式排名方式,即相同数值不占用名次。比如对 10、10、9进行排序,两个10具有相同的名次1,而9的名次为2。 =SUM(N(UNIQUE(C$2:C$15)>C2))+1UNIQUE函数的作用是在数据表中提取不重复值,工作方式类似于删除重复值功能。“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,就是中式排名的名次。
|