５．教員が知るべきExcelの便利機能⑤－vlookup関数－

2021年2月24日 19:33

今までの記事では、Excelを使って表計算をするところに焦点をあててきました。

どちらかというとデータベースを作るという側面が強かったと思います。

今回の記事は、その集大成として、データベース化したものをどう資料に落とし込むか、という話になります。

座席表や成績表を作るというアウトプットの話になってくるかと思います。

教員が知るべきExcelの便利機能－目次－

０．はじめに－目次＆基礎編－
１．教員が知るべきExcelの便利機能①－オートフィル－
２．教員が知るべきExcelの便利機能②－数値計算－
３．教員が知るべきExcelの便利機能③－フィルターと並び替え－
４．教員が知るべきExcelの便利機能④－初歩的な関数－
５．教員が知るべきExcelの便利機能⑤－vlookup関数－（本記事）
Tips①　－Tabを使った入力編－
Tips②　－置換を使った入力編－
Tips③　－ショートカットキー編－

以下、取り扱って欲しい項目は募集中です。

座席表Excelはこうやって作られている

世間には既にたくさん出回っていますよね。

作って無料で公開している方もたくさんいるように思います。

作られ方を知っておくと、使うときにも効率的な運用が出来ると思いますし、机の列の数が違うときには、元のデータを参考にしながら自分で作ったりすることもできると思います。

とりあえず、完成品を提示したいと思います。

各座席の左上のセルに出席番号を入力すると、その出席番号に該当する生徒の名前とその読みがなおよび役職が出てくるタイプです。

【Excelファイルの全貌】

01 完成品

【座席表の部分】

02 座席表のみ拡大

これがどうやって動いているのかと言われると、左側にある一覧表をデータベース元にしてデータを参照して表示させています。

【元のデータベース】
（名前は全て架空のものです。後半、雑な感じが出ているのはご愛嬌。実在する人物がいたとしても何の関連もありません。）

03 データベース元

あとは、今日のテーマであるvlookup関数だけわかれば座席表以外にも成績処理とか連絡網とかアウトプットの形式（様式）を変えるだけで、いろんなものに応用が利きます！

作業工程の例（関数の入力前まで）

いきなり完成形を見せられたり、無料で配布されているExcelファイルを入手しても、どこからどのように手を付けていいのかわからない場合も多いと思います。

また、自分で修正を加えたい場合にもどこを消したり付け加えたりしていいのかわからないことも多いと思いますので、最初から作る方法をご紹介しておきます。

【１】Excelを開きます

【２】元のデータベースを作ります
（実際には、年度の最初に作った名簿などをコピーペーストすることになるかと思います。）

【３】座席の枠を作っていきます
個々の座席になる部分を範囲選択して

罫線を設定します。

きちんと枠が太くなりました。

基本的には汎用性が低くなる（他のファイルへの流用がしにくくなったり使える機能が減ったりする）ので「セルの結合」は多用しない方がいいと思うのですが、今回の座席表のファイルについては「出席番号+漢字氏名」の幅と「よみがな」の幅がちょうどいいだろうということで結合していきます。

結合する範囲を選択して

この「セルを結合して中央揃え」を押すと結合されますね。

こんな感じになります

この下の段も結合しておきます。

【４】教室の配列に整えていきます

ひたすらコピー・ペーストを繰り返してもいいのですが、オートフィルでもいけることは大丈夫ですよね？

今回は、１行分の空白も含めて繰り返しコピーさせたいので、以下の範囲を選択します。

その上で以下のようにオートフィルしていきます

これを横にもオートフィルすると

席が２つくっつきました。

横方向も１行分の余白を含めて繰り返しをさせたいので（改めて範囲選択をしているところに注意してくださいね）

出席番号を入力する幅を狭くして
（列をctrlを押しながらクリックしていくと複数選択することができます）

同様に漢字氏名を表示させるセルを太くしていきます。

あとは、教室の前後をわかりやすくするために、教卓（や黒板など）を書き加えておしまいです。

本日のテーマ：VLOOKUP関数とは

関数のしくみは基本的には同じだと、前回の記事で書きました。

４．教員が知るべきExcelの便利機能④－初歩的な関数－
（こちらの記事をご参照ください）

★簡単に復習すると関数のしくみは以下の通りです★
①=
②関数
③範囲指定（関数によっては桁数指定などの指定も個別に必要）

今回のVLOOKUP関数も基本的には同じなのですが、後半の条件付けの部分がちょっと多いので最初は困惑するかもしれません。

では、落ち着いて、ゆっくり、１つ１つ、見ていきましょう。

★VLOOKUP関数の仕組み

①=
②VLOOKUP
③（これについては４段階）
[1]探す番号，
[2]探す範囲（一番左側の列について），
[3]範囲の中の左から何番目のデータを表示するか，
[4]条件（とりあえず最初は「false」と入力するとおぼえておくでOKです）

となります。

③の条件のところが４箇所になっているので、ややこしそうに見えますが、１つ１つ解説していきますので、ここだけちょっと頑張ってください。

実際の例

それでは、実際に名前を表示させていきましょう。

G4のセルに漢字氏名を表示させるように指示を入力していきます。

セルを選択して、以下のように入力していきます。

=VLOOKUP(F4
↑このF4の部分はマウスでクリックするのでもOKです

これで、F4のセルに出席番号を入力すれば、そのF4に入力した数値についての情報を今から範囲指定する部分のデータベースから参照してくれるようになります。

続いて検索させるデータベースの範囲を指定します。

=VLOOKUP(F4,A1:D25
↑まず「,」を入力してF4（検索値）との間を区切った後で、A1からD25をマウスでドラッグして選択しましょう（キーボードだけでも選択できますがそれはまた別の機会に）。

このタイミングで忘れずにキーボードのファンクションキーの４番（キーボード最上部のF4キー）を押してください。

そうすると、範囲の部分が以下のように書き換わるはずです。

=VLOOKUP(F4,$A$1:$D$25

間に「$」マークが入ったと思いますが、これは絶対参照というものです。（今までは相対参照だった→詳しくは後述します）。

これで、この指定した値を指定した範囲のデータベースから検索するように指示ができました。

さて、このF4のセルには漢字の氏名（検索範囲の左から２列目に記載されている）を表示させたいので、以下のように「2」を追加で入力します。

=VLOOKUP(F4,$A$1:$D$25,2
↑範囲の後に「,」で区切って2を入力

あとは（とりあえずは）何も考えずに「,false)」と入力して関数を完結させます。

このままEnterを押すと、何やら不穏な文字列が表示されてしまいます。

大丈夫です。

とりあえず、F4のセルに1と入力してみてください。

出席番号1番の安藤くんの名前が表示されますよね。

試しにF4に16と入力してみると

今度は出席番号16番の別府くんの名前に変わりました。

ということで解説していきます。

=VLOOKUP(F4,$A$1:$D$25,2,false)

と入力したわけですが、以下のように動いています。

①=
②VLOOKUP
③（これについては４段階）
[1]探す番号，【←F4に入力した数値。1とか16とか臨機応変に変わる】
[2]探す範囲（一番左側の列について），【←指定したA1からD25の範囲のうち左端のA列についてF4に入力した値の列を参照する】
[3]範囲の中の左から何番目のデータを表示するか，【←２番目の漢字氏名】
[4]条件（とりあえず最初は「false」と入力するとおぼえておくでOKです）

ということになっています。

なので、例えば16を入力したときは（上の説明の①～③と[1]～[4]と対照させて翻訳します）

①～②VLOOKUP関数を使うよ！
③[1][2]A1からD25までについてのデータを考えるよ。まずは一番左端のA列から16って書かれている（別府くんについての）データに注目して！
[3]そのデータ（別府くんのデータ）の左から2番目のデータ（つまり漢字氏名）を表示して！
[4]（検索方法は完全一致だよ）

となっています。

あとは同じようによみがなと係についても表示させる指示を出していきます。

F5とG5を結合したセルのところに
=VLOOKUP(F4,$A$1:$D$25,3,false)
↑左から３番目のよみがなを表示してという指示

F6とG6を結合したセルのところに
=VLOOKUP(F4,$A$1:$D$25,4,false)
↑左から４番目の役職を表示してという指示

と入力すると以下のようになるはずです。