このページで学べること｜VLOOKUP・HLOOKUPの使い方と違い
別の表から情報を探して表示したい。
そんな時に活躍するのがLOOKUPシリーズです。
全2回でお届けするLOOKUPシリーズ。
第1回目の今回はセルシティ不動産管理会社を舞台に、
・契約番号から契約者を探す
・月別データを取得する
このような場面で活躍する、別の場所にあるデータを探して表示する「参照」のお仕事を学んでいきます。
この物語の案内役MISATOです。
今日は「参照」の得意な関数たちの活躍を紹介していきます。
【VLOOKUP関数の使い方】契約者番号から情報を探す
ここでは契約番号から、契約者情報を調べているようですね。
契約番号さえ分かれば、必要な情報を探し出せる。
それが俺の仕事だ。
検索といえばまずは、VLOOKUP関数さんですね！今回はよろしくお願いします。
契約番号のような重複しない番号は検索に向いてる。式は以下の通りだ。
=VLOOKUP(検索値,範囲,列番号,FALSE)
まぁ、実践して見せた方が早いだろう。私を苦手とする人間は多いと聞くからな。
C2に入力した式：
=VLOOKUP(B2,B5:F9,2,FALSE)
これは何をしているかと言うとだな、
B2に契約者番号を入力すると、C2に自動で契約者が表示される、ということをしているのだ。
一つずつ解説してもらってもいいですか？
まずは、おさらいだ。
=VLOOKUP(検索値,範囲,列番号,FALSE)この検索値というのが、今回のB2。
つまり契約者番号（2026001）だな。
B2に入力された値(2026001)を、指定した範囲(B5:F9)から検索するのですね。
そうだ。
=VLOOKUP(検索値,範囲,列番号,FALSE)
B2に入力された契約者番号を、まずB5:F9から探す。
見つかったら左から2番目の列をここ（C2）に表示してくれ、という関数なんだ。
最後の FALSE は何ですか？
完全一致検索の指定だ。
契約番号みたいに、完全に一致するものを探したい時に使う。
似たものを間違って表示させないための策だな。
なるほど！
0でも同じ意味になるんでしたよね？
ああ。
FALSE も 0 も「完全一致」だ。
TRUE や 1 にすると近い値を探す検索になる。
私は「0使い」派でした！
現場では0を使う人もかなり多いぞ。
さて、C2以外にもD2,E2,F2にも同じ目的の関数を入れておこうか。
オートフィルを使って関数を反映させるから絶対参照を活用して一気にいくぞ。いいな？
オートフィルでずれないように、検索値や範囲に$マークをつけてから一気に横に関数をコピー。列番号は手入力でそれぞれ修正すれば超時短ですね！！
【確認】
F2に入力した式：
=VLOOKUP($B$2,$B$5:$F$9,5,FALSE)
※B5を含めて5列目の値を返したいので、列番号は5にします。
⚠️トラブル発生！列追加で情報がズレる
どうやら検索範囲の途中であるD列に電話番号を記載する列が追加されたようです！
やれやれ、列番号の指定が崩れてしまったではないか…
【解説】
E2に入力してある式：
=VLOOKUP($B$2,$B$5:$F$9,3,FALSE)
※B5を含めて3列目の値を返す式なので、エリアを返すべきセルに電話番号が反映されてしまいました。
関数の式に、左から何番目の値を返す、と指定しているから、途中に列が挿入されると結果が崩れてしまいますね。
左から何番目の列を返す、とすでに指示が入っているから、E〜G列はそれぞれ結果がずれてしまっているんだ。
表の構成が変わる可能性がある場合は、気をつけないといけませんね。
こればかりは仕方ないな。自分が作った表ならこういうトラブルは少ないだろうが、スプレッドシートのような共有ファイルだとこういった事故はよく起こるから気をつけておいてほしい。
（う〜ん。確かにこういうトラブルはよくありそう。）
【HLOOKUP関数の使い方】横方向の月別データを探す
こちらでは月別の空室率を調べているようですね。
横方向の月次資料なら、私の担当だ。
横検索と言えばHLOOKUPさん！
よろしくお願いします。
実務の世界では、VLOOKUPばかりで俺はほとんど出番無しなんだよな。
知っていてくれて嬉しいよ。
ちなみに、どうして俺がHで、あっちがVが頭文字についているか知っているかい？
えっと・・・。その〜・・・。
あはは！まぁいいさ！「そういうもんだ」とみんな覚えているからな！
気にするなって！
Vertical（バーティカル）：垂直の
Horizontal（ホリゾンタル）：横方向の
水平線はHorizon（ホライズン）といいますね！そのHですね！
水平線のように横に検索をかけていくのがHLOOKUPさん。より理解が深まりました。
お！いいね！
なかなかスポットライトが当たりにくい俺に「水平線のように」と言ってくれるなんてオシャレじゃないか！じゃあ、早速検索していくぞ！
これは月別の集計表ですね。
B2の検索窓に、検索したい「月」を入力すると、C2には売上額、D2には修繕費が反映されるんだ。
【確認】
=HLOOKUP(検索値,範囲,行番号,FALSE)
VLOOKUPの式と基本構造は一緒ですね。
C2に入力した式：
=HLOOKUP(B2,B4:E7,2,FALSE)
※上記イラストには絶対参照の＄マークが入っていますが同じ意味を表しています。
範囲であるB4:E7のうち、「月」は行番号①。
C2には「売上額」を返したいから、行番号は②。
「月」が1行目、「売上額」は2行目にありますからね。行番号は2ですね。
そういうことだな。
B4:E7から、B2と同じものを探して見つかったらその2行目を返してくれ、という式だ。
あら？VLOOKUPさんがこちらを見ていますよ。
あ、いや、えーっと、だな。
どうしましたか？
…いや、あれだ。
さっきHLOOKUPのHはHorizontal（ホリゾンタル）、私のVはVertical（バーティカル）のVと言っていただろう。
えぇ、そうですね。
私のことも読者にわかりやすい何か例を出してもらえないかと思って、だな。
そうですね！では、バーチカル手帳はどうでしょうか？これならだいぶ日本語として馴染んできていると思いますよ。
確かに。縦軸管理が好きな人が使うこの手帳なら皆が知っているだろう。
縦検索の私にピッタリな例だ。
まさか、こんなところでヤキモチ焼くだなんてな！笑
俺なんて実務では全く出番なしなんだからヤキモチやきっぱなしだぜ！
【実践編】データベースと書類を連動させる
実務では、エクセルやスプレッドシートにつくったフォーマットをテンプレートとして、顧客情報を逐次修正して印刷する場面が多くあります。
取引先にFAXを入れる、申し込みの控えとして印刷をする、ことが往々にしてあります。
ここでは「スポーツジム」での会員データベースと連動させたシートで学習していきましょう。
【前提】
スプレッドシートで会員一覧のデータベースがあり、それを参照元として1枚の出力書類をつくる
以下はスポーツクラブの顧客データ（左）と、コース別の月額料金表（右）です。
上記の表を参照元として、1枚の出力書類を作っていきましょう。
まず完成図をお見せします。
【式の確認】
=VLOOKUP(検索値,範囲,列番号,FALSE)
以下の緑のセルに関数を入力します。
通常、出力書類を作る場合は、別に専用シートを作りますが、ここでは説明の都合上、同じシート内で説明をします。
【検索値】C4
【範囲】B11:F15
D4に入力した式：=VLOOKUP(C4,B11:F15,2,FALSE)
C6に入力した式：=VLOOKUP(C4,B11:F15,5,FALSE)
会員番号「1001」を入れると、以下のようにVLOOKUP関数によって反映されました。
次に、C6に表示された「コース」を【検索値】としてHLOOKUP関数を入力して「月額」を表示させましょう。
【式の確認】
=HLOOKUP(検索値,範囲,行番号,FALSE)
【検索値】C6
【範囲】I10:K11
C7に入力した式：=HLOOKUP(C6,I10:K11,2,FALSE)
完成です！
会員番号のような唯一の値があれば、どんなに膨大なデータの中からでもデータを検索し反映することができる関数。
セルに関数を仕込んでおけば、1つのセルに入力するだけで、他の必要な情報が一気に表示されます。
何度も使いまわすようなテンプレートでは、とても便利ですね。
アイディア次第で活用の幅がグンと広がります。
実は「参照」の仲間には、まだLOOKUPさんとXLOOKUPさんが控えています。
次回はこの2人が登場します。
お楽しみに！
