其ノ12 vlookupの練習 その2
vlookup編の第2回です。下↓からダウンロードをお願いします。
開くとこんな↓です※1 ※2。
「取扱い品目データ」kiso12_hinmoku_data.xlsxの方、「価格」列と「在庫数」列が空っぽですね。
今回はココを、「在庫数データ」kiso12_data_zaiko.xlsxからひっぱってきて埋めたい、と思います。
例によって例のごとく、vlookupを使うのですが、vlookup関数の式のかたち、覚えています?
念のためおさらいしておきましょう。
=vlookup(検索値 , 検索範囲 , 欲しいのは左から何番目? , FALSE)
と、いうことで、やっていきましょう。
シートをコピーする
ハイ、では。
まず、vlookupを使う前に、下準備として、参照元となるデータをsheet2にコピーしてきましょう。
全選択してコピペ、でも良いんだけど、今回はシート移動を使いましょうか。
まず、2つのExcelを両方とも開いてください※3。
そして、「在庫数データ」のシートタブを右クリックします。
「移動またはコピー」ってありますよね。
それを選択してください※4。
移動先ブック名をもう一方のExcelにします。
移動先のExcelを開いていれば、下三角▼で選べるようになります。
挿入先は末尾でいいよね。
で、「コピーを作成する」チェックボックスにチェック入れてください。
入れ忘れるとデータが切り取られてしまいますので。※5 ※6
と、いうわけで、コピーができました。
ちょっとしたお話。
現在2つのExcelを開いていると思いますが、もしかしたら移動先ブック名の3つ目4つ目として、同じファイル名の「コピー ~ 」ってのが出てくるかもしれません。
Excelの設定によって出たり出なかったりするのですが、コイツらは、Excelがバックグラウンドで行っているバックアップのデータだ、と思います。
なので、基本は無視で大丈夫です。
移動先候補が見かけ上増えてやっかいですが、無視していただければ問題なく。
vlookupは検索値が一番左にいないとダメ
じゃ!さっそく関数入れちゃおうかな!
=vlookup(と入力して、検索したい値は1コ目の注文番号だから…※7
範囲は「在庫」シートを全部選んで…※8
「価格」列は左から4番目だから…※9
FALSEを入れて、入力完了!※10
…アレ?
*おおっと*
エラーが出た!
何で?
・
・
ちょっと試しに、1コ目の注文番号「D59465」を、「在庫」シートで検索してみましょうか※11。
ctrl+Fで検索すると、あるんですよ、コレが。
ということは、完全な、失敗、ですな。
うーん、どういうことなんでしょうか。
・
・
実は、vlookupの式のかたち、思い出してください。
vlookupは、1コ目の引数「検索値」を、2コ目の引数「検索範囲」の中から探す、という構造になっているんですが、実は、「検索値」が「検索範囲」の一番左にないとダメなんです。
今回の例だと、「注文番号」が一番左にないとダメ。
現状だと、sheet2「在庫」シートの「注文番号」は右端にあるから、これを左側にもってこないといけない※12。
加工が必要なワケですな。
ということで、sheet2の「在庫」シート、加工してやりましょう。
「注文番号」列を一番左にもってきてください※13。
こういった加工が必要だから、参照元となるデータはコピーを使ってる、というワケなんです。
下準備ができたところで、式を入れてあげましょう。
vlookupの式はもう長々と説明しなくても、大丈夫ですよね?
不安、という人は、画面見ながらついてきてくださいね※14 ※15。
1列移動したので、欲しい「価格」列は5番目になってますよ。 注意、注意。
ということで、できましたでしょうか。
vlookupは、一番左にキーとなる値(検索値)がないとダメ。
つまりね、vlookupはこういう↓挙動をしてるワケなんです※16。
vlookupは、検索範囲の一番左を探して、見つかったら、「左から何番目?」で指定したところにある値をもってくる、という動きをしてるんです。 だから、さっきみたいに右側に注文番号があるとエラーになる。 それで、一番左にもってこないといけない、というわけなんです。
絶対参照と相対参照
ハイ、ではこの勢いで、「在庫数」も埋めていきましょう。
式をもう一回手入力しても良いんだけど、めんどくさいから隣からコピっちゃおうかな!※17 ※18
…エラーになってしまいました。
うーん…左の「価格」列はうまくいっているのに…。
コピペした式、どこがうまくいってないのか、ちょっと分析してみましょうか。
・
・
まず、「検索値」がB2になってますね※19。
あと、2コ目の引数(検索範囲)もB:G列にずれちゃってます。 これではvlookupは機能してくれませんよね。
で、こうなってしまった時の対策なんですが、改めて式を手で入力し直せば良い※20 ※21。 つまり、コピペしない。
でも、コピペしたいケースもありますよね。
例えば、今回のケースは式を入れるのが「価格」と「在庫数」の2列だけなので入力し直してもそんなに手間ではないですが、もしこれが5列あったとしたら、けっこうな手間です。
なので、隣の列にコピペしてもずれない方法を紹介しましょう。
コピペしてもずれないようにするためには、1列目の「価格」のところの式の入れ方から工夫してやる必要があります。
なので、さきほど入れた式はいったん消してください、…もったいないですが。
で、いつもの通りvlookup式を入れてください。
「=vlookup(」として、A2セルを選択するんでしたね※22。
で、A2セルを選択したら、F4キーを押してください※23。 すると、「$」マークが付きますよね?
コレ、絶対参照ってヤツです。
参照セルの番地の前に「$」マークが付くと、その参照が固定化されるんです。
セル参照には相対参照($なし)と絶対参照($あり)があるのですが、相対参照と絶対参照のちがいは、…まあ解説書を読むと難しい説明が載ってますけど、ここでは簡単に、相対参照は「ずれる」、絶対参照は「ずれない」と理解しておいてください。
今回は、コピペしてもずれないように、絶対参照を使う、というわけです。
同様に、検索範囲の選択のときも、F4キーで$付きにしてください※24。
あとは同様です※25。 ではこの新たな式を「価格」列全部にコピーしましょう!※26 さあ! どうなるか?
あれ?
全部5600円になっちゃった…。
これはおかしいぞ…。
・
・
実は、1コ目の引数「検索値」のときに、固定化させすぎちゃったんです。
「A2」セルって、「A列の2行目のセル」って意味ですよね。
その「A」とか「2」の前に$マークが付くと、そこが固定化される。
で、「A」列が固定化されるのは良いんですよ。
だけど、「2」まで固定化されちゃうと、延々と注文番号「D59465」を検索しちゃうことになってしまう…。
だから、全部5600円になっちゃったんです。
「A2」の「2」のところは、「3、4、5…」とずれていってほしいですよね。
なので、もう一回。
「A2」の「A」のところだけ固定されるように、調節しましょう。
F4キーを何回か押せば、われわれが求めている状態「$A2」になりますので※27。
あとはさきほど同様です。
2コ目の引数「検索範囲」もF4一回($A:$F)でOKですよ。
これで、「価格」列はきっちり埋められたと思います※28。
ようやく本題に帰って来ることができました。
では、式を「在庫数」列の方にコピーしてみましょう。
「価格」列の式をコピって、「在庫数」列の1番上に貼りつけてください※29。
エラーは出なくなりましたね。
コピペ先でも、列がずれていないこと、確認しておいてください。
でね、もうひと手間必要なんですが、実はこのやり方、3番目の引数「左から何番目?」のところは手で直さないといけないんですよ。
現状左から5番目を拾ってきてるんですが、それを手作業で6番目に直してください※30。
そうすれば、「在庫」シートの左から6番目、つまり「在庫数」列の情報を引っ張ってこれます。
あとは、この式を「在庫数」列全部にコピーしてください※31。
完了です。
今日はこんなところですね。
え?絶対参照にしたけど、あんまり楽になってないって?
そうですよね、どうせなら手直しの部分ナシで、コピーしただけできっちり情報を拾えれば良いですよね。
つまり、
・「検索値」= 列は動かない、行は動く
・「検索範囲」= 動かない
・「左から何番目?」= 動く
とできれば、理想的なんですが…。
でもとりあえず今の段階では、この辺りで妥協しておきましょうよ。
もちろんやり方はあります、が、式が複雑になっちゃう。
なので、vlookupに慣れるまでは、とりあえず上のやり方で覚えていきましょうよ。
スマートなやり方は、後々、紹介できたら、と思っています。