☰

1) コース名のヘッダー行を生成する

tag-icon • tag-icon microsoft-excel list
1) コース名のヘッダー行を生成する

最初のシートで、参加したいクラスを選択した生徒が数人います。2 番目のシートで、選択に基づいて、各クラスの生徒の名前が記載されたリストを生成したいと思います。これを行う方法はありますか?

ここに画像の説明を入力してください

答え1

1) コース名のヘッダー行を生成する

まず最初に、sheet2 でクラスの水平リストを生成する必要があります。これは手動で行うこともできますし、次の数式を使用して、クラス 1 ～ 3 の生徒の選択から、使用されている一意のクラス名の並べ替えられたリストを生成することもできます。空のクラス エントリを無視することもできます。私が思いつく唯一の 2 つの注意点は、リストの左側のセルがリスト内の名前のいずれかと等しい必要があることです。もう 1 つの注意点は、これは配列数式であり、数式を確認するときにCONTROL+ SHIFT+ENTERだけでなく+ も必要になることですENTER。数式の周囲に { } が表示されたら、正しく実行されたことがわかります。{ } は手動で追加できないことに注意してください。

この例では、次の数式を Sheet2!B2 に貼り付け、空白のセルが表示されるまで右にコピーしました。

=IFERROR(INDEX(Sheet1!$B$2:$D$9,SMALL(IF(SMALL(IF(COUNTIF($A$2:A2,Sheet1!$B$2:$D$9)+ISBLANK(Sheet1!$B$2:$D$9)=0,COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1,""),1)=IF(ISBLANK(Sheet1!$B$2:$D$9),"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1),ROW(Sheet1!$B$2:$D$9)-MIN(ROW(Sheet1!$B$2:$D$9))+1),1),MATCH(MIN(IF(COUNTIF($A$2:A2,Sheet1!$B$2:$D$9)+ISBLANK(Sheet1!$B$2:$D$9)>0,"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1)),INDEX(IF(ISBLANK(Sheet1!$B$2:$D$9),"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1),SMALL(IF(SMALL(IF(COUNTIF($A$2:A2,Sheet1!$B$2:$D$9)+ISBLANK(Sheet1!$B$2:$D$9)=0,COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1,""),1)=IF(ISBLANK(Sheet1!$B$2:$D$9),"",COUNTIF(Sheet1!$B$2:$D$9,"<"&Sheet1!$B$2:$D$9)+1),ROW(Sheet1!$B$2:$D$9)-MIN(ROW(Sheet1!$B$2:$D$9))+1),1),,1),0),1),"")

配列数式であるため、A:A や 3:3 のような完全な行/列参照を使用しないでください。過剰な計算が実行されることになります。

2) 名前のリストを生成する

ヘッダー行のコースを 3 つの選択肢の 1 つとして選択した名前の列を生成するには、次の数式を使用できます。以下の例では、この数式は Sheet2!B3 に配置され、コース名のリストと一致するように右側にコピーされ、空白行のみになるまで下にコピーされます。

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(14,6,ROW(Sheet1!$B$2:$D$9)/(Sheet1!$B$2:$D$9=B$2),ROW(A1))),"")

AGGREGATE 関数は、選択した数式番号に応じて配列のような操作を実行できます。最初のパラメータ番号が 14 または 15 の場合、配列のような操作が実行されるようです。2 番目の数値パラメータは、エラー結果、非表示の行などを無視するように AGGREGATE に指示すると思います。配列のような計算の結果として、AGGREGATE 関数内で完全な列参照を使用することは再度避けてください。

出典: シート1

ここに画像の説明を入力してください

出力: シート2

ここに画像の説明を入力してください

学生が同じコースを複数回選択した場合、その名前はリストに複数回表示されます。

関連情報