☰

尋找具有特定值集的行中的最後一個非空白儲存格

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。

  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。

相關內容