PolicymenuホームExcel【Excel関数】vlookup関数の使い方<応用編>Excel2017.12.25【Excel関数】vlookup関数の使い方<応用編>vlookup関数は是非使いこなしてほしい関数の一つ。全4パターンの応用編を紹介しますが、4つ目はマニア度MAXなので3つ目まで読んでもらえれば十分と思って紹介します。「知識を技術に」をテーマに、Excel関数の使いこなし方法を紹介します。vlookup関数はデータサイエンティストとしてのデータ加工でかなり多用してきました。新入社員でデータを扱う仕事に就くこととなった人は、是非マスターしてください。目次1.引数の効率的な設定1-1.[範囲]は列指定1-2.[列番号]はセル参照2.複数列に設定する場合STEP1.vlookupの1列目作成STEP2.1列目をコピーし2列目作成STEP3.セル参照の列番号を指定3.複数シートを参照する場合STEP1.1列目を作成STEP2.1列目をコピーし2列目を作成STEP3.コピーした2列目を一斉置換STEP4.タイトルや列番号を修正4.[検索値]のキーを2つにする5.vlookup関連記事1.引数の効率的な設定vlookupを使う箇所が1列だけの場合には、それほど効果を発揮することはありませんが、次のセクションで紹介する複数列、複数シートを参照する場合に大きな効果となります。☆☆☆1-1.[範囲]は列指定2つ目の引数で指定する[範囲]の指定は列での指定がよいです。vlooup(C5,データ!A1:H31,7,false)通常は、範囲はデータを参照する範囲を行と列で指定すると思いますが、行を指定すると参照先のデータ件数が増えた場合に取り込みモレてしまいます。それを解消できるのが、範囲の指定を列指定にすることです。このケースの場合、A:Hと列指定しておくと、32行目以降にデータが追加された場合でもvlookupの参照範囲となるのです。1-2.[列番号]はセル参照3つ目の引数で指定する[列番号]は行固定でのセル参照にすることです。vlooup($C5,データ!$A:$H,8,false)3つ目の引数もセル参照で指定できます。以下の左表は、D列に目標額、E列に売上をそれぞれvlookup関数で右のデータシートから引用しています。このとき3つ目の引数を以下のようにセル参照にしておくと、vlookup関数の中身を操作せずにE2の値を設定するだけで足りてしまいます。2.複数列に設定する場合[1]で紹介したような引数の設定をすると、vlookup関数を複数列に設定する場合もとても効率的です。★☆☆STEP1.vlookupの1列目作成まずはvlooupの1列目を作成します。この時のポイントは前述の2つのポイントに加えて、1つ目の引数を列固定にしておくことです。することで、次のステップのコピーの際に、参照先がずれることが無く効率的です。STEP2.1列目をコピーし2列目作成いま作成した1列目をコピーします。前述の通りの設定ができていると、コピーして作成した新しい列(下の左表の矢印列)は、1列目と同じ値になっているこはずです。STEP3.セル参照の列番号を指定コピーしたそのままでは列番号が同じなので、表示したいデータシートの列番号をセルに入力します。あとは項目名など必要な個所を修正して1列追加できました。vlookup関数の引数を毎回設定しなおすのではなく、汎用的に使えるvlookup関数の設定方法を覚えておいて、必要なパラメーターの箇所だけを入力しなおすことで、効率的に使えるようになります。3.複数シートを参照する場合[2]で紹介した複数列にvlookup関数を使用する場合の発展形です。指定する範囲が別シートにあるデータを使用する場合、シート名の工夫と、一斉置換で飛躍的に効率アップできます。★★★STEP1.1列目を作成vlookupを使う1列目を作成します。この場合は、シート「属」の3列目を参照しています。複数列を参照する時と同じように、1列目をコピーし2列目を作成します。ここまでは、複数列に設定する場合と同じ。作成した2列目のデータ範囲を選択し、シート名を一斉置換します。置換する場合は、ショートカットキー[Ctrl]+[H]がおすすめ。この例の場合は、シート名[属]を、シート名[実]に一斉置換しています。【ポイント】シート名は短い漢字名称がおすすめ。シート名をアルファベット"A"、"AX"や英数"D1"などにしてしまうと、一斉置換する場合にに関数内部にセルの値を置換してしまう可能性があります。おすすめのシート名は【漢字】1文字です。名が体を表すような漢字がよいでしょう。上記の例の場合は、属性のデータシート[属]と、実績のデータシート[実]と表現しています。シート名を一斉置換できるとこのように参照先も一斉に変更できます。必要に応じてタイトルや列番号を修正したら完成です。以下は、目標と実績を追加して、それらを用いて達成率を計算したものです。4.[検索値]のキーを2つにするちょっと複雑な運用を行う場合、このやり方を知っておくと楽な場合があります。通常、検索値に2つ以上のデータを設定する場合、vlookupではなくsumifs関数を使うことが多いですが、sumifs関数は文字通りsum関数なので数値を合計する場合にのみ使用します。参照先の値が合計できない文字列などで、複数条件に該当するデータを参照する場合はvlookupの複数条件設定で対応できます。マニア度はMAっXです><;この使い方は本当にマニアックMAXなので、伝わる人にだけ伝わればいいので。わからない、興味がない人はスルーして最後にまで読み飛ばしてください。★★★★★vlookupの1つめの引数で複数条件としたい項目を&で結合します。以下の場合は、[社員番号]と[売上年月]を結合しています。同様に[範囲]として設定するデータも[社員番号]と[売上年月]を結合した項目を用意しておきます。前処理としてはこれだけ。vlookup関数の1つ目の引数を、[社員番号]&[販売年月]としているので、範囲の一番左の列も[社員番号]&[販売年月]を結合したデータとなるように指定します。上記のシート[実]のように年月の経過によってデータレコードが増えていくよう場合でも、予めシート[表]にあるような実績管理表を準備しておくと、シート[実]のデータを貼り付けるだけでデータが連動してくれます。集計表をメンテしなくてよい運用にできるでしょう。5.vlookup関連記事vlookupを覚えるにはまずこれ。◇【Excel関数】vlookup関数の基本。応用編の前にまずはコレ!vlookup関数は、上手に設定できずエラーになってしまうことが多い関数としても名が高いです。エラーとなってしまうケースと解消法もあわせて見ておいてください。実績と将来予測をグラフで示したい場合など、一本の折れ線グラフの中を実線...Excel新社会人が覚えておきたいエクセル機能(初級編)パソコンをこれから始めて使うという新社会人も増えてきていると聞きます。...Excel【Excel関数】応用の前にまずは基本!vlookupの使い方vlookup関数は、別シートや別表のデータを参照できる関数です。今回のビズ技は、基本中の基本。グラフを見やすくするコツを紹介します。基...Excel【Excel関数】住所情報から都道府県を抽出する方法"東京都千代田区1-1-1"のように連結した住所情報から都道府県だけを...Excel【Excel】折れ線グラフを塗りつぶして見やすくする作り方サムネイルのグラフのような、折れ線グラフを塗りつぶして効果的に魅せる方...スポンサーリンクこのブログを書いている人ゆうぞうサザエさんのような天然パーマの母に、くせ毛の父の血を足した"超絶"天然パーマ。企業の財産は"人財"と暑苦しい理念を持ち、人事部でもないのに独自に社内研修を実施し表彰され、その勢いでブログってます。【特集】"使い方"では足りない!「折れ線グラフ」の...折れ線グラフを使いこなすための記事5つを紹介します。どの記事も、机上の知識ではな...【特集】「働き方改革」を独り歩きさせない!「働き方...働き方改革って言葉が独り歩きしていたと思ったら、最近は一人で走り出しているところ...【特集】ショートカットキーで仕事効率化!働き方改革...たかがショートカットキー、されどショートカットキー。【Excel関数】vlookupがエラーで使えない...vlookup関数が#N/A、#NAME?などよくエラーになってしまいますよね(...【PowerPoint】プレゼン効果抜群!スライド...スライドショー開始、全スライド表示など、パワーポイントならではのショートカットキ...【Excel】実線の一部を点線に替える「折れ線グラ...実績と将来予測をグラフで示したい場合など、一本の折れ線グラフの中を実線と点線で区...【業務効率化】パソコンが使えない人こそ!時間を生み...パソコンが苦手な人は、たくさんあってどのショートカットで何ができるか覚えていられ...【PowerPoint】Excelだけ!?早くてき...Excel(エクセル)だとグラフの書式設定がわからんなぁ。付け方だけでこんなに違う、フォ...1年間にものを探す時間は150時間ともいわれています。探し物をする時間を一番減ら...スキルアップ系記事プレゼンのシナリオ作成に大活躍のフレームワーク重ね...プレゼンのシナリオ作成、簡単にできてる人ってうらやましいですよね。口頭ではなんと...どれからやればいい?...優先順位付けは「ハイ・ローマ...おつかれさまです!ゆうぞうです。今日のビズ技は、複数の選択肢の優先順位を付ける時...【PowerPoint】見やすいきれいな円グラフの...グラフの色使いって迷いますよね。初期状態の色使いは意図しないところが強調されてい...自分じゃ浮かばない...アイディアが沸いてくるフレーム...今回紹介するビジネス技は、アイディア創出のビジネスフレームワークの一つ「SCAM...企画も分析も多くのビジネスで使える!5W1Hの進化...生産性向上の手段としてビジネスフレームワークがあります。文字通りビジネスで使える...【PowerPoint】モノクロ表示で図や写真など...PowerPointで画像を挿入し特定の箇所を強調することは多いと思います。管理職とマネージャーは違う。言葉だけじゃない求めら...ふとした瞬間に、英語で表現されていることに違和感を覚えることがある。私の...上司の愚痴はこぼしてナンボ!愚痴のメカニズムと付き...先日、部下の愚痴を聞いていたところ、"愚痴ってすいません"っていわれ。思った...プレ金(プレミアムフライデー)が浸透しない理由を考...こんにちは。第一段では、プレミアムフライデーを1日の勤務時...有馬記念を例に競馬の「当選確率」を計算してみる件"人馬一体"という四字熟語を現実に見られる競馬。