☰

特定の値のセットを持つ行の最後の空でないセルを見つける

tag-icon • tag-icon worksheet-function spreadsheet iwork-numbers
特定の値のセットを持つ行の最後の空でないセルを見つける

学生データのスプレッドシートがあり、各行が 1 人の学生を表しています。どのクラスにも 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. これを複雑にする問題の 1 つは、学生が EP または EF の成績を受け取る可能性があることです。これは、現在のアプローチでは含めるべきではないため、最終的には上位の文字の成績に取って代わられ、元に戻すことができれば F に取って代わられることになります。lookupこれらは、学生がコースの合格または不合格のいずれかで緊急退学したことを示します。物事を単純化するために、すべての EP および EF 値を WP または WF に置き換えると思います。これを行うことで回答に影響する場合は、その点に注意してください。

答え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。

関連情報