ExcelのVLOOKUP関数をコピーしたらずれる場合

この記事では、ExcelのVLOOKUP関数をコピーしたらずれる場合の原因と対処法をご紹介しています。

VLOOKUP関数で数式をコピーする際は、適切な位置を固定することが重要です。数式の参照を固定していない場合、範囲などがずれてエラーが表示されることがあるので注意が必要です。

以下では、セル参照を固定しない「相対参照」とセル参照を固定する「絶対参照」についてご説明します。

VLOOKUP関数の数式をコピーしたらずれる場合は、数式が相対参照になっている可能性があります。

以下では、相対参照をしている数式をコピーする場合、どのように数式がずれてしまうのかをご説明します。

上記の画像は、上の表から必要なチームの情報を「エントリーNo.」を検索値として抽出するためにB9セルに「=VLOOKUP(A9,A2:D6,2,FALSE)」を入力しています。

B9セルに入力した数式のようにセル参照を「$」で固定していないことを相対参照といい、数式をコピーする際にセル参照の位置が相対的に変化します。

以下では、B9セルの数式を下の表に貼り付けた場合、コピーした数式がどのようにずれるのかを実際に試していきます。

「エントリーNo.」1、2、3に紐づく「チーム名」「代表者」「メンバー数」を抽出することができました。

一見、正しく抽出できているように見えますが、どのように数式がコピーされたのかを確認していきます。

上記の画像から、検索値が1セルずれる範囲も1セルずつずれていることが分かりました。

今回は、検索値が範囲内にあるため正しい結果が抽出されましたが、検索値を入れ替えた場合にエラーになる場合があります。

実際に、検索値を入れ替えていきます。

A9セルからA11セルの検索値を入れ替えると、B10セルからD11セルに#N/Aエラーが表示されました。

#N/Aエラーは、主に検索値が範囲内に存在しない場合に表示されるエラーです。

#N/Aエラーが表示されたB10セルを確認すると、検索値が範囲から漏れていることが分かります。

数式をコピーする場合は、相対参照ではセル参照がずれて正しい結果が得られないことがあるので、必ず「絶対参照」して適切な位置を固定しましょう。

VLOOKUP関数で数式をコピーしてもずれないようにする方法

VLOOKUP関数で数式をコピーしてもずれないようにするには「絶対参照」をします。

絶対参照とは、数式の引数にセル参照を指定する際に列番号や行番号を「$」で固定して、数式をコピーしてもセル参照がずれないようにすることです。

列番号はコピーしても値が変わらないので、自動で列番号が可変するように、引数の列番号となる数字をセル参照で指定します。

任意のセル（例：B7セル）に2を入力して、Ctrlを押しながらドラッグします。

抽出したい列が２列目以降のため、2から入力しています。

B7セルからD7セルに列番号に指定する数字が表示されました。

以下では、VLOOKUP関数をコピーしてもずれないように「$」で絶対参照する方法をご説明します。

B9セルに「=VLOOKUP($A9,$A$2:$D$6,B$7,FALSE)」を入力して、Enterを押します。

第1引数の検索値は、A9セル、A10セル、A11セルとA列を固定して行番号を可変させたいので、「$A9」のようにAの前に$を付けて列番号だけを固定します。

第2引数の範囲は、参照する表の位置が変わらないので、「$A$2:$D$6」のように行番号と列番号の前にそれぞれ$を付けてセル位置を固定します。

第3引数の列番号は、B7セル、C7セル、D7セルと7行目を固定して列番号を可変させたいので、「B$7」のように7の前に$を付けて行番号だけを固定します。

B9セルにAが表示されました。

B9セルをCtrl+Cでコピーします。

今回はCtrl+Cでコピーしますが、オートフィル機能でフィルハンドルをドラッグして数式をコピーしても同様の操作ができます。

B9セルからD11セルを選択して、Ctrl+Vで貼り付けます。

B9セルからD11セルに結果が表示されました。

数式をコピーした場合にセル参照がずれていないか確認します。

D11セルの数式を確認すると、適切な位置を固定しているのでコピーしても参照がずれていないことが分かりました。