コピー＆ペーストすると、VLOOKUPの “範囲” が、ずれてしまう

質問

こんにちは。いつもお世話になっております。とかく昨日からは幾度か質問させて頂いておりまして、都度ご丁寧な回答を承っており、感謝しております。

さて、早速ですが今度は下記の事で悩んでおります。

◆エクセルバージョン　→　エクセル2003

◆VLOOKUPの入ったセルをコピーして貼り付けすると “範囲”が自動的に “1”ずつプラスされて式が変わってしまう為、都度、手修正で直している。
　これを改善したい。

【具体例】
SHEET名　＝　顧客名一覧　に対して

A1に　お　と入力すると　B1に　小野　と表示される。
A2に　か　と入力すると　B2に　加藤　と表示される。
A3に　さ　と入力すると　B3に　斉藤　と表示される。

ように作成しております。
この　顧客名一覧Sheetの　B1　に
　=VLOOKUP(A1,VLOOKUP用シート!A1:B100,2,0)
という関数式を入れているので、これをコピーし、B2～B100まで貼り付けをしたのですが、そうすると

B2には　=VLOOKUP(A2,VLOOKUP用シート!A2:B101,2,0)　と入ってしまい、
B3には　=VLOOKUP(A3,VLOOKUP用シート!A3:B102,2,0)　と入ってしまいます。

何故、この数式の中の“範囲”が勝手にプラス１ずつされてしまうのか…これを防ぎたい。

現在は、100個ほどペーストしたものに対して、全て直接入力で“範囲”を正しい　VLOOKUP用シート!A1:B100　と修正している状態です。

どなたかお手隙の方いらっしゃいましたら、どうぞご教授の程、お願い致します。

回答No.7

Cupper · ベストアンサー

範囲は相対的な位置で記録されていると考えてください。
そんなわけで
　=VLOOKUP(A1,VLOOKUP用シート!A1:B100,2,0)
を下にコピーすると
　=VLOOKUP(A2,VLOOKUP用シート!A2:B101,2,0)
と下に範囲がずれてくるのです。
右にコピーすると
　=VLOOKUP(B1,VLOOKUP用シート!B1:C100,2,0)
となります。

同じ計算を同列の行や列で繰り返し行なう場合は入力の手間を省くことができるので、便利な機能です。
例：
　C1 =A1+B1
C1セルを下にコピーすると
　C2 =A2+B2
　C3 =A3+B3　

ずれないようにするには、絶対参照の形式で範囲を指定する必要があります。
列方向を固定するには列番号の前に "$" マークを付けます。
行方向を固定するには行番号の前に "$" マークを付けます。
　=VLOOKUP(A$1,VLOOKUP用シート!A$1:B$100,2,0)
これで下へコピーしてもずれることはなくなります。
ただし列番号に "$" マークを付けていないので、横へコピーすると列番号は変わります。

お礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が２３時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました！有難うございます。
そして、同じ計算を同列の行や列で繰り返し行なう場合の記載方法まで…嬉しかったです☆
早速試して全てやってみました。これで頭の中、スッキリです"^_^"

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々（本当に近いと本日中、再び会社からですが…汗）、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

回答No.8

Cupper

ああ、自分も失敗してますね。

×　=VLOOKUP(A$1,VLOOKUP用シート!A$1:B$100,2,0)
○　=VLOOKUP(A1,VLOOKUP用シート!A$1:B$100,2,0)

追記
範囲を固定するのであれば、範囲に名前を付けて、範囲名で指定する方法もあります。
この場合、シート名を省略することができるので数式を見やすくなります。
・A1からB100セルを選択
・左上の名前ボックスに範囲名を入力（例えば、参照範囲）
・=VLOOKUP(A1,参照範囲,2,0)

設定した範囲は
　挿入　→　名前　→　定義
で、あとから設定を変更できます。
ここで確認すると分かると思いますが、上記の方法で設定した範囲はシート名を含めた絶対参照として設定されています。

お礼

度々恐縮です＆有難うございます。了解です！大丈夫でした♪
無事、テスト終了してます<(_ _)>

回答No.6

mu2011

NO1です。
数式を間違えましたので訂正します。
=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)

お礼

たびたび有難うございます☆了解ですっ<(_ _)>

回答No.5

TT333

こんにちは。

えーと、的外れな回答をしてたら申し訳ないんですが、ＶＬＯＯＫＵＰ用に作ったＡ１：Ｂ１００のシート範囲を動かないようにしたいんですよね？

それには絶対値指定というのが必要です。

=VLOOKUP(A1,VLOOKUP用シート!A1:B100,2,0)
この式を
=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)
にしてみてください。＄マークはいわば「動かさない」という意味です。エクセルはこのような指定をしないかぎり、相対値になっていて自動的に範囲がずれるようになっています。

お礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が２３時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました！有難うございます。
＄マークはいわば「動かさない」という意味あい…時折、この＄マークを目にする事はあったものの、その意味すら
知りませんでした…。有難うございます！スッキリしました☆

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々（本当に近いと本日中、再び会社からですが…汗）、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

回答No.4

romuromu

=VLOOKUP(A2,VLOOKUP用シート!$A$1:$B$100,2,0)
これで範囲が固定されたと思います

絶対参照と相対参照
http://yokohama.cool.ne.jp/takesima1/excel/zetai/excelzetai.htm
http://www.nbcom.co.jp/PC-Support/FAQ/xls/ot/xls012002ot001.shtml
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub03_010.html

参照範囲を替えたくないセルの数値の所でＦ４キーを押して$を付けて下さい。

お礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が２３時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました！有難うございます。
F4キーを押す回数によって参照方法を繰り返し切り替えることが出来る…目からうろこです（笑）

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々（本当に近いと本日中、再び会社からですが…汗）、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

回答No.3

merlionXX

=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)

というように範囲を絶対参照に変えてみてください。

お礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が２３時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました！有難うございます。

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々（本当に近いと本日中、再び会社からですが…汗）、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

回答No.2

めとろいと（@naktak）

コピー元を
=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)
とすればいいです。
セル指定しているところにカーソルを合わせ、F4を押せば「$」がついて
コピーされても参照先が変化されることはありません。

お礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が２３時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました！有難うございます。
F4を押せば「$」がつく…初めて知りました☆何てラクチンなのでしょう(^.^)　助かりました。

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々（本当に近いと本日中、再び会社からですが…汗）、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

回答No.1

mu2011

数式コピーする場合、固定範囲とする表などは、絶対参照としますので、以下の様にセルに$を付加して下さい。
=VLOOKUP(A1,VLOOKUP用シート!$A$2:$B$100,2,0)

お礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が２３時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました！有難うございます。

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々（本当に近いと本日中、再び会社からですが…汗）、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。