VVLOOKUP関数を使ったエクセルのシートを作るときに覚えておくと便利なテクニックを紹介します。
テクニック１
データベースシートを専用に作る
大きなデータベースを使って作業をする場合はデーターベース専用のシートを作ると良いです。
データーベースの隣に作業スペースを作っても良いですが、データーベースエリアは一度データをおいたらほとんど手を触れない場所になります。作業エリアで作業している間にうっかりとデーターベース部分のところに触れてしまったら時間の無駄になってしまいます。
テクニック２
列の絶対参照・行の絶対参照を使いこなす
列の絶対参照
下記の画像のようにC列の下に関数をコピペするときに使うときに便利です。
列の絶対参照が入っている部分はこの$の部分です。
式の中身
=$B2*$D$1
$B2→本体価格の18900円
$D$1→10%
これがあることで下にコピペしてもB列の下にコピペしてもB3やB4にも正しい結果が返ってくるようになります。
行の絶対参照
横に対して絶対参照をかける方法です。下記の画像のように右にある単価と本数をかけた場合に表を作成したものです。
E5の式の中身
=$C$5*E$2
$C$5→本数を固定
E$2→単価の１００円を指している
E$2の部分を行参照にしています。この状態で右のセルにコピーしていくと自動的にそれぞれの単価で本数をかけた結果が表示されます。
VLOOKUP関数の場合だと検索値の部分で列と行の絶対参照が有効です。
テクニック３
関数の一括置換して効率的に式を作る
下記の例で関数に対して一括置換をかけることで簡単に黄色のエリアに正しいVLOOKUP関数を作成することができます。
①検索値のセルを固定する
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
上記の赤丸の部分にこのような式を作成します。
②J5を右にセルコピーする
コピーすると自動でK列・L列に式が挿入されます。
③式を修正する
列番号が正しくなるように修正します。
K列（修正前）
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
K列（修正後）
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)
L列（修正前）
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
L列（修正後）
=VLOOKUP($I$5,$D$4:$G9,4,FALSE)
修正したあとはこのようになります。
④式に対して置換する
J・K・Lに入っている関数に対して一括で置換します。
キーボードのコントロールボタン、シフトボタン、アットマークボタンを同時に押します。押すと、式の中身が表示されます。
⑤一括置換する
黄色エリアにある式に対して一括置換します。
今、これらの式が入っています。
J5
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
K5
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)
L5
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)
検索値の$I$5が入っている為、下にドラッグしたら全てI5を参照してしまい５行目に入ってる式と全く同じ結果になってしまいます。ドラッグしたあとに１個ずつ直すことで正しい結果を出すこともできますが、これが１００個や１０００個になれば手で直す事は困難になります。ここで一括置換をすれば簡単にドラッグ可能な式を作成することができます。J５・K５・L５をドラッグで選択し、コントロールボタンとシフトボタンとアットマークボタンを同時に押します。このような感じでセルが式の中身を表示するようになります。
次に、コントロールボタンとHボタンを押して置換メニューを表示させます。
メニューを表示したら
検索する文字列→ $I$5
置換後の文字列→ I5
を指定しすべて置換をクリックします。
置換後は閉じるを押して下さい。
J５・K５・L５に入っていた式の一部が$I$5からI5になっているはずです。これを確認したら再度コントロールボタンとシフトボタンとアットマークボタンを同時に押します。これで元に戻ります。
⑥セルのコピー
J５・K５・L５を下のセルにコピーして下さい。
これで一括で式の作成が完了となります。
応用すれば他の関数でも活用できます。複数の式の置換があればこのテクニックを使ってみて下さい。
コメント