【WPS表格案例】在混合数据中查找指定项目的倒数第二条数据

创作者俱乐部成员

📌	大家好，昨天是立春，代表着寒冬即将过去，终会迎来春暖花开之时。这篇帖子是年前的最后一篇了，准备进入休假模式了。这一次的案例来自菁培班，为了解决这个问题，我给出了两种方法，希望大家能够有所收获~

一、案例介绍

有这样一个表格，表中按照提交顺序记录了每个同学每次测验的成绩，现在我们想根据这个表，随机抽查指定同学的某一次成绩（在本案例中需要提取倒数第二次成绩，每个表中的倒数第二次已用黄色底色标出，方便核对结果），进一步地查看这次测试是否有作弊行为。在抽查姓名的E列已设置数据有效性，可以选择表中任意一位同学。这个问题该如何解决呢？且慢且慢，让我们接着往下看~

案例-混合成绩表

二、利用OFFSET函数解决

我们以A同学的倒数第二次成绩为例，给出函数公式和运行结果（绝对引用在这里不添加也可以，因为不涉及到下拉填充的操作）：

💡	=OFFSET($A$1,LARGE(IFERROR(FIND(E1,$A$2:$A$20),0)*SEQUENCE(COUNTA($A$2:$A$20)),2),1)

OFFSET方法实现结果

可以发现，我们在这里使用了OFFSET函数来确定每个同学的倒数第2个数据所在的位置，进而返回对应的成绩。接下来我来告诉大家这个公式是如何确定倒数第二次成绩所在位置的。

首先，我们利用FIND函数找到姓名"A"这个字符串是否存在，如果存在返回其在字符串中的位置，如果不存在返回#VALUE!错误值。为了方便我们提取A字符的位置，我们利用IFERROR函数将错误值返回为0，方便之后的运算。

接下来，我们利用SEQUENCE函数，生成长度为姓名列非空单元格个数，步长为1的序列，这样运算代表着每个单元格相对A1单元格需要偏移的位置。然后，我们将前面FIND函数、IFERROR函数复合运算得到的数组和SEQUENCE函数生成的数组进行乘积运算，得到这样的数组：

IFERROR、FIND × SEQUENCE函数得到的结果

此时我们经过观察可以发现，生成的动态数组中只有姓名为A的偏移量得到保留，其他的位置均为0。

下一步，我们就可以提取出倒数第二次乘积了。在这个动态数组的结果中，我们很容易知道，倒数第二次成绩的位置就是这个数组中第2大的数字。那么问题就简单了，我们只需用LARGE函数提取第二大的数字就行了。

最后，我们利用OFFSET函数，以A1为参照区域，行数即为我们刚刚提取的倒数第二个位置。因为我们要提取【成绩】列的数据，所以列数我们需要相对A列平移1列，因此填写1即可实现。

三、利用FILTER和LET函数解决

上面是利用OFFSET函数进行位置相对偏移得到的，比较好理解，而且公式整体没有特别困难的地方。我们先来展示一下用FILTER函数和LET函数解决的结果，会发现这个问题变得更简单了：

💡	=LET(t,FILTER(A2:B20,A2:A20=E1),INDEX(t,COUNTA(t)/2-1,2))

LET × FILTER函数运行结果

接下来我们来对上面的公式进行讲解。

首先，我们使用FILTER函数将姓名为A的数据全部筛选出来，得到一个新的表：

FILTER函数

之后的操作我们都在FILTER函数生成的表上进行。我们以A为例，我们可以使用COUNTA函数计算FILTER函数中非空单元格的个数，然后我们对这个个数除以2，再对这个结果减1，这样我们就可以得到倒数第二个数据的位置。然后使用INDEX函数对FILTER函数筛选的单元格区域提取倒数第二行的第2列（即成绩列）就可以了。