☰

查找具有特定值集的行中的最后一个非空单元格

tag-icon • tag-icon worksheet-function spreadsheet iwork-numbers
查找具有特定值集的行中的最后一个非空单元格

我有一份学生数据电子表格，其中每行代表一名学生。对于任何给定的班级，都有多个 1-N 列，其中 N 是任何学生重修该课程的最多次数。因此，一些示例数据，其中课程最多重修 4 次，我想在 E 列中计算：

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | C         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | F         |
| 5 | EP    | C     |       |       | C         |
+---+-------+-------+-------+-------+-----------+

我已经发现了这个lookup函数，它似乎让我接近我想要的。例如，LOOKUP("X",A2:D2)它将为我获取给定学生的最高（即字母表中的最新）非空列。给定上述数据，我将获得如下输出：

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | F         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | W         |
| 5 | EP    | C     |       |       | EP        |
+---+-------+-------+-------+-------+-----------+

存在的问题如下：

  1. 我实际上想要的是最低值，而不是最高的非空列值。因此，获得 D、F、W、EP 或 EF 的学生可以替换班级，并且（希望）在未来的尝试中表现更好。我想捕获最近的非 W、EP 或 EF 的尝试（请参阅下文）。请注意，这并不总是更好的成绩 - 我们曾有学生获得 D，替换成绩，然后获得 F。我想捕获 F。

  2. 使情况复杂化的一个问题是，学生可能会获得 EP 或 EF 的成绩，而这些成绩不应该包括在内，根据我目前的方法，这些成绩最终会取代更高的字母等级，如果我可以逆转的话，这些成绩会取代 F lookup—— 这表明学生紧急退学，要么通过课程，要么不通过课程。我想我可能会用 WP 或 WF 替换所有 EP 和 EF 值以简化事情。如果我这样做会影响您的答案，请注意！

答案1

根据你的第一个矩阵，你似乎对该行中的最后一个非空单元格感兴趣。条件是它们既不能等于EP，EF也不能等于W。这是正确的假设吗？如果是这样，请使用以下内容：

在此处输入图片描述

公式E2：

=INDEX(A2:D2,,AGGREGATE(14,3,(A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2)),1))

拖累....

如果这不是您想要的，您能告诉我限制是什么吗？目前，有时您会解释最后一个值是您想要的，最后您会陈述限制。

编辑：

这是如何运作的？

AGGREGATE将获得从公式中返回的最大数字（根据 14 个参数）(A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2)，该公式将根据所有指定的规则返回一个由 1 和 0 组成的数组，并乘以它们各自的列号。最高列号结果将是合格值。

然后它将使用这个最大的数字作为函数中的列参数INDEX。

相关内容