超簡単!エクセルのVLOOKUP関数で成績一覧から個人の成績票を作成 VLOOKUP関数の ちょっとした裏技も! 365・2019・2016・2013・2010・2007対応

スポンサーリンク

今回は、成績一覧データから、個人の成績票を「VLOOKUP関数」で作成する方法です。

下記の成績一覧データから、個人の成績票を作成します(ちなみに名前は架空の人物です)。

各教科の点数が入っているシート

このように、氏名を選択すると個人の成績票が表示されます。

個人の成績票が簡単に作成可能
スポンサーリンク

まずはドロップダウンリストで氏名を選択する設定から

まずは、成績票で氏名をドロップダウンリストから選択できるように設定します。

氏名の入っているセルA2~A8セルを選択し、名前ボックスに「名前」と入力します。

氏名のセル範囲に「名前」をつける

これで氏名のリスト範囲に「名前」という名前を付けることができました。

それでは、個票の氏名欄を「データの入力規則」でドロップダウンリスト形式で選択できるようにしましょう。

「個票」シートの氏名欄、F2セルをクリックします。

氏名をドロップダウンリストから選択するように設定

「データ」「データの入力規則」をクリックします。

「データ」「データの入力規則」

「入力値の種類」を「リスト」に、「元の値」を「=名前」(先ほど定義した名前)と設定します。

条件の設定をリストに

これで氏名をドロップダウンリスト(プルダウンリスト)から選択できるようになりました。

氏名をドロップダウンリストから選択
Excel(エクセル)プルダウンリスト(ドロップダウンリスト)のリストを可変式リストにすればリストの項目が増えても再設定不要! リストを名前の定義とテーブルにする2つの方法 365・2019・2016・2013・2010・2007対応
データの入力規則で、「元の値」をリストにして、プルダウンリスト(ドロップダウンリスト)を利用している方は多いと思います。「元の値」のリストは、Excel2007以前では、セル範囲での別シートを参照することができません。ですから参照...

平均点はリンク貼り付けで

平均点を個票に貼り付けます。「成績」シートの平均点が入っている(B9~G9)セルを選択し、コピーします。

平均点をコピー

そして、個票のB6セルを選択し、右クリックメニューから「リンク貼り付け」をクリックします。

リンク貼り付け

これで平均点を「個票」に貼り付けることができました。

平均点を個票に貼り付け

「リンク貼り付け」なら、元のデータが変わってもデータが連動して表示されます。

エクセルの右クリックメニューで「ドロップダウンリストから選択」が表示されない! 見落としがちなビューの表示が原因だった 互換モードにもご注意を 解決済! 365・2019・2016・2013・2010・2007対応
普段何気なくエクセルのファイルを編集していると、あることができなくなっていました。それは「右クリックメニュー」による「ドロップダウンリストから選択」が表示されなくなっていたのです。下図は原因を再現するために、簡単なリストを...

ここからが本番 VLOOKUP関数で氏名と成績を連動させる

いよいよここからが本番です。「VLOOKUP関数」はなかなか難しいですが、一度設定すれば理解することは簡単ですので、チャレンジしてみましょう。

まず、「VLOOKUP関数」で参照するデータ範囲に名前をつけます。ここではA2セルからG8セルまでが氏名と各教科の得点が入っていますので、選択後「名前ボックス」に「データ」と入力して「VLOOKUP関数」で参照する範囲に名前をつけます。

先に「VLOOKUP関数」で参照する範囲に名前をつける

それでは、いよいよ「VLOOKUP関数」を利用して「国語」の得点欄に表示させる設定をしましょう。B5セルを選択し、「関数の挿入」をクリックします。

「関数の挿入」をクリック

「関数の挿入」ダイアログボックスで、「関数の検索」欄に「vlookup」と入力して「検索開始」をクリックすると、下記のように「VLOOKUP」が表示されますので、「OK」をクリックします。

「VLOOKUP関数」の検索

「VLOOKUP関数」は4つの引数を必要とします。

「=VLOOKUP(検索する値,一致するデータを探す範囲,範囲の中で表示させたい列番号,完全に一致しているセルを検索するかしないか)」です。

上記の通り、「検索値」は「氏名」表示させるセルF2にしています。「氏名」を検索して「成績」と連動させるためです。この後、数式をコピーして利用しますので、「絶対参照」にしています。

ここでの注意点は、「検索値」はリスト(ここでは「データ」と名付けた範囲)の左端である必要があります。

範囲は先ほど名前をつけた氏名・点数を含んでいる範囲「データ」、そして「国語」は「データ」の中で2列目ですので、「列番号」は「2」を、最後に検索方法は「検索値」と完全に一致するデータを選びますので「FALSE」としています。

下図は、氏名が「鈴木 一郎」を検索値とした場合です。

「鈴木 一郎」は「データ」の中で1行目(全体では表の2行目)です。

さらに、列番号を「2」にしましたので、クロスする「68」という値を表示するわけです。

検索した行と設定した列がクロスした部分の値を取得

ご覧のようにシート「個票」には、「鈴木 一郎」の「国語」のデータが表示されています。

国語の得点が表示された

もちろん別の氏名を選択すると、VLOOKUP関数」はその氏名に一致する行を検索し、その行の2列目の値を表示します。

別の氏名を選択すると、検索された行の2列目の数値が表示される

「VLOOKUP関数」の数式をコピーして編集

それでは、B5セルに入力したセルをG5セルまでコピーしてみましょう。

残念ながら「VLOOKUP関数の3つ目の引数である「列番号」は、オートフィルのように自動で変更されません。

「VLOOKUP関数」の引数「列番号」は自動で変化しない

そこで大変ですが、一つ一つ列番号を変更していく必要があります。下図の場合は列番号は「3」ですね。

VLOOKUP関数の列番号を変更していく

「合計」(7列目)まで変更すれば完了です。下図は完了した「成績票」となります。

7列目まで数式を変更

もちろん、「氏名」を変更すると、それぞれの「得点」が変動して表示されます。

「氏名」と連動して「成績」が変動する

「VLOOKUP関数」の便利なちょっとした裏技

今回は7列だけでしたが、列がもっとある場合は大変です。

ということで、「VLOOKUP関数」で、列も自動的に変化するようにする裏技の紹介です。

実は10行目に、フォントの色を白にして非表示にしていましたが、数字をオートフィルで1~7まで並べていました。これを「VLOOKUP関数」の3つ目の引数、「列数」に設定すると、数式をコピーして自動で列が変更されます。

「VLOOKUP関数」の「列」を別セルで参照させる

具体的には下図のようになります。「成績!」の「!」は、別シート「成績」を参照しているという意味です。下の例では、「成績!B10」は「2」になります。こちらは「絶対参照」ではなく「相対参照」($をつけない)にする必要があります。

「列番号」は別シートを参照して自動的に変更する

B5セルは下記のようになります。

「列番号」をセル参照に

B5セルの数式をオートフィルでコピーすると、自動的に列番号が変更されます。

これで、列数が多くなっても対応が可能になります。

「列番号」をセル参照にすると自動的に変化する

これは便利な裏技ですので、ぜひご利用ください。

個票を一括でプリントアウトするにはVBAが必要

今回紹介したエクセルの成績個票では、全員の個票を印刷する際には、氏名を選んで一人一人印刷する必要があります。

今回のように7人くらいならまだ大丈夫ですが、人数が増えたら大変です。

そのように、氏名を順に自動で変更し、印刷するにはエクセルのマクロであるVBA(ビジュアルペーシック・フォー・アプリケーションズ)を利用する必要があります。

また近いうちに「土日でマスター エクセルVBA講座」で紹介したいと思います。

土日でマスター エクセルVBA講座② 成績個票を自動でプリントアウト 繰り返し作業を実行する「For」「Next」構文+おまじない
今回は、成績データから個人の個票を順番に自動的に印刷していくマクロを作成したいと思います。繰り返しの作業を実行するには、「For」「Next」構文(ステートメント)を利用します。下記の成績一覧データから順に個票で名前を変更していき...
土日でマスター エクセルVBA講座① マクロの記録でエクセル操作の自動化と「With」「End With」構文
今回から、新しいカテゴリ「エクセルVBA」を追加し、エクセルのマクロであるVBA(ビジュアルペーシック・フォー・アプリケーションズ)の紹介をしていきたいと思います。エクセルのマクロには興味があるけれどなかなか時間が取れない、という...

コメント

タイトルとURLをコピーしました