Excelでのデータの連携について


以下の1と2の既存ファイル情報から、3を作成することは可能でしょうか。可能な場合、方法をご教示ください。

既存ファイル
1)学生一覧名簿(学生番号、クラス、名前、他の情報)
  ※学生番号は一人に一つで、重複はありません。
2)学生が受験した学校等の情報(学生番号、名前、受験校名、受験結果、その他の情報)
  ※受験日順に一人一校一行で入力されています。
   一人で複数校受験している場合、別の行に記入してあります。

出力したい情報
3)クラスごとの学生の受験状況一覧
  ※2のファイルに書かれていない学生も含めて学生番号順にソートしたい。
   →受験校「なし」または空白にしたい
  ※複数校受験している学生についてもすべての情報を反映させたい。
  ※具体的な学校名・受験結果など、2のファイルの内容はすべて反映させたい。
   (受験校の数だけの表示では役に立たない)

できれば具体的に方法をご教示いただけましたら幸いです。
登録直後でポイントがありませんが、有効な回答には後日500ポイントほどお送りしたいと思います。
よろしくお願いいたします。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2016/06/10 13:22:20
  • 終了:2016/06/17 13:25:03

ベストアンサー

id:a-kuma3 No.1

a-kuma3回答回数4325ベストアンサー獲得回数17732016/06/11 00:32:42

マクロでもできそうですが、シートの操作だけでもそれっぽくできます。
シートや列などの操作が分かっていれば、大丈夫だと思います。


f:id:a-kuma3:20160610235724p:image
f:id:a-kuma3:20160610235722p:image
まず、新しいブックを作成して、受験した情報のシートと、名簿のシートをコピーしましょう。
元のデータを壊したくないし。
一応、分かりやすいようにシートに名前を設定してます。

f:id:a-kuma3:20160610235719p:image
後の都合があるので、学生番号の列を最初の列にしておきます。

f:id:a-kuma3:20160610235716p:image
受験データのシートに、名簿の見出しをコピーして、2行目に式を入力します。

=VLOOKUP($A2,学生名簿!$A$1:$E$12,2)

VLOOKUP 関数には、三つのパラメータをとります。

  1. 同じ行の受験データの学生番号のセル
  2. 名簿シートの全範囲
  3. 名簿シートの「クラス」の列の順番

式をコピーするので、一番目のパラメータの列は絶対指定($付き)です。

f:id:a-kuma3:20160610235713p:image
横方向に式を設定していきます。
式をコピーして、三番目のパラメータだけ手で変更します。

f:id:a-kuma3:20160610235711p:image
f:id:a-kuma3:20160610235708p:image
名簿の列の分だけ式を設定したら、受験データの行にコピーします。

これで、受験をした生徒については、名簿のデータと対応が取れたことになります。


次は、受験データがない生徒の分です。

f:id:a-kuma3:20160610235705p:image
名簿のシートに受験日のデータを作ります。
見出しを入れて、次の行に式を設定します。

=IF(ISNA(VLOOKUP($A2,受験データ!$A$2:$H$24,4,FALSE)),"","あり")

VLOOKUP 関数には、実は四つ目のパラメータがあります。

  1. 同じ行の受験データの学生番号のセル
  2. 受験データシートの全範囲
  3. 名簿シートの「受験日」の列の順番
  4. FALSE を設定します

これを、ISNA 関数と IF 関数でくるんで、受験データシートに受験日が入っている生徒には「あり」、受験日のデータがない生徒にはブランクを表示します。

f:id:a-kuma3:20160610235702p:image
名簿シートの全ての行に式をコピーします。

f:id:a-kuma3:20160610235751p:image
名簿シートの膳は印をフィルタに指定して、「受験日」の列で(空白セル)だけを表示するようにします。

f:id:a-kuma3:20160610235749p:image
受験した記録がない生徒だけが抽出できます。

f:id:a-kuma3:20160610235746p:image
表示されているデータを選択してコピーして、

f:id:a-kuma3:20160610235743p:image
受験データのシートの方に貼り付けます。

これで、受験データシートの最後の方に、受験していない生徒のデータが入りました。

f:id:a-kuma3:20160610235740p:image
貼り付けた「学生番号」の列を、受験データの列の方に移動して、受験校のところに「(なし)」を入力します(行方向はコピー)。

f:id:a-kuma3:20160610235737p:image
余分な見出しの行を削除します。

f:id:a-kuma3:20160611002703p:image
全範囲を選択して、最初の学生番号で並び替えをします。

後は、欲しい形になるように、不要な列を消したり、希望する位置に列を移動してください。

id:skrk1969

大変詳細にありがとうございます。じっくりと読んでいたためお返事が遅くなり申し訳ありません。

大変よい方法を教えていただきました。これは使えると思います。
ただ、これは一度だけ出力するのであればよい方法だと思いますが、できれば随時、現時点での状況というのをもう少し手軽に見られればと思っています。そうなるとマクロなどを使う必要が出て来るでしょうか?

2016/06/13 10:29:28
id:a-kuma3

できれば随時、現時点での状況というのをもう少し手軽に見られればと思っています。

質問に書かれたまんまを実現するなら、マクロでやる必要があります。

「手軽に状況を確認する」という意味では、確認したい内容を少し減らすという手もあります。
学生名簿の方から、受験状況のシートに向けて vlookup で学生番号を拾って、受験のあり/なしだけを確認するとか。

2016/06/13 11:37:42

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません