itエクセルの関数で何を覚えたら良いと聞かれたら、Vlookup関数がまず頭に思い浮かぶ。使い勝手がいい関数であるが、エクセルに馴染みがない人にとっては、式がウニャウニャして分からない、そんな関数でもある。一意(ユニーク)なキーが無いよう。商品コードが一緒でも、販売する営業所によって単価が違うんです、どうしましょう利用シーンまずは、利用シーンを上げてみよう、このシーンが思い浮かぶ人は頑張ってVlookupを覚えるとエクセル作業が効率化するだろう。一意なキー(ユニークキー)があるか、又は作ることができるか作れなければVlookup関数は不要だ。このページは読み飛ばしてもらって大丈夫。必要ができたときに読んでほしい。1得意先コードと名称のデータがバラバラのシートにある。名称がぱっと見つからなくて不便。データベースから情報を参照させたい場合。2年度別のシートを時系列データにしたい。項目の名称は同じだけど、項目の並び順が年度ごとに違っていて、単純にコピペできないシーン3この情報は網羅的か調べたいとき。VlookupをAのシートに使えばA<B(AがBに含まれる)場合には全てに数値が入り、エラーが出ていればA>Bということ。Bのシートに使って、エラーが出なければAとBは同じ項目から構成されていることがわかる。関数の式=VLOOKUP($B5,$E$11:$F$13,2,0)見慣れないと呪文みたいである。説明しようと思ったが、基本的な説明は他のホームページにわかりやすく書いてあるのでそこを見てもらったほうがいい。このページとかがわかりやすい。私のページでは式の注意点に絞って記載しよう$マークはすごく重要な意味がある$は絶対参照と呼ばれるもので、$A1であればA列を固定し、A$1であれば1行目を固定する。$A$1であれば、A列と1行目を固定する。次のセルにコピーしたときに変えたくない項目について、$をつける。具体的には、検索キーがA列にあるとすると、セルをB1にコピーしても計算式のキーの列は変えたくない、このため$A1とする。すれば、B1にコピーしても$A1のままであり、2行目にコピーすれば$A2と変化してくれる。データベースの範囲は絶対に$A$1のように指定しよう、これは列や行が変わってもデータベースの範囲を固定させるためである。絶対参照は式の入力欄にマウスを持っていってから、ショートカットF4で変更するのが便利である。検索キーはデータベースの左側にないとだめ。最初は分かりづらかった項目。検索キーをとなる項目をデータベースの一番左においておくと覚えよう。検索キーの左の項目は検索できない。右にしかないときは、コピーで左に持ってくる対応をする。右から2番めはデータベースでの相対的な位置を示す検索キーから1番目の列、2番目の列と数える。Vlookup関数の実践的な使い方ここからが本番更にVlookupを便利に使おう。エラーが出てるのをなんとかしたいなんとかする前に、本当にエラーが出ていても良いのか考えよう。#N/AはDBに検索キーがないことを意味している。Vlookup式が正しいならば検索対象>データベースの状態になっている。ありがちなVlookupのエラーがでるケース1検索キーがよく見ると違うケース。半角・全角、スペース有無など。こんな場合に重要なのが入力形式の統一化なのです。この場合はフィルタを掛けてみて、関数を使って置き換え、スペース削除などしてデータのクレンジングを行う。2DBの範囲指定が違っている、$のつけ忘れなど。Vlookup式が間違って以内ならば、IFERROR関数を使ってエラーを消そう。=iferror(vlookup($B5,$E$11:$F$13,2,0),0)により、エラー値をゼロにすることができる。iferror関数はExcel2007以上のバージョンで使用できる関数なので注意。それ以前のバージョンでは、IF関数とISERROR関数によりエラーを消すことができる。vlookup($B5,$E$11:$F$13,2,0))よくカッコの数を間違えてしまうので、Iferrorが使えればそちらを使おう。データベースの範囲が変わるたびに指定しなおすのが面倒(対策1)データベースを別シートにするか列を変えて、そのDB以外の項目がその列にないようにする。することで、範囲の指定を$A:$Cのように列を固定する形式にできる。すれば、データベースの行が増えても自動で対応できる。データベースの項目を増やす(列を増やした)場合には、Vlookup式の列を拡張する必要がある。(対策2)DBの範囲をデータテーブルにする。すれば、Vlookup式の範囲指定をDBに追加しただけで拡張できる。以降でしか使えないので注意。テーブル機能は、範囲を指定して挿入タブの表から作成する。(対策3)OFFSET関数とCOUTIF関数で範囲を可変にする方法があるが、1、2対応したほうが簡単。Offset関数を使うとDB範囲がパット見でわからず、チェックがしづらい欠点がある。一意(ユニーク)なキーが無いよう。ない場合は自分で作ってしまおう。重複する名前の人がいたとする、そして住所は書いてある。名前で他のデータを持ってこようとすると重複してエラーになってしまう。この場合は、名前+住所をキーとして自分で作ってしまおう。作り方は、セルを&でつなぐだけ。D1セル神奈川バツバツ番地であったら、=C1&D1のようにすることで文字列が結合され、赤津神奈川バツバツ番地となる。ー(バー)を間にはさみたかったら、=C1&’-“&D1の用にしよう。文字列を入れたい場合はダブルクオテーションで囲むようにする。することで、一意っぽいキーが作成される。ホントに一意になっているかは、データ次第であるのでそこは忘れずに検討しておこう。この考えは複数条件でVlookup関数を使っていることと変わらないので、頭の片隅に覚えておこう。DBの相対的な位置を列ごとに手で入力するの面倒だよううむ。頑張りましょうではなくて、相対的列番号の指定はセルを指定した場合、そのセルの中身が入ることから、1箇所だけに記載すればあとはコピペで対応が可能だ。行を絶対参照にし、列を相対参照にしてコピペすれば、列が変化している特性を利用する。文章で説明するのは難しいが、まずはDBに検索キーの上に、例えばセルB1に1を入力、横のセル(C1)に=B1+1として、横にコピー。すると、相対的な列番号が入ったと思う。ここで、Vlookupを入力する表の外側の上に、該当する範囲の相対的列番号のセルとリンクを張っていく。そのセルの位置をvlookup式の相対的列番号の箇所にC$2のように記載、コピーすれば、D$2といった形で列がずれていき、参照する数値も自動でずれていく。商品コードが一緒でも、販売する営業所によって単価が違うんです、どうしましょうIf関数でDB範囲(テーブル)を切り替えてあげましょう。式はこんなイメージです。lookup編終わり、ここまで使えればきっと大丈夫。reserved.ページ上部へ戻る。