エクセルの関数 ｖｌｏｏｋｕｐ 関数で式の作り方＆コピーの仕方

質問

ｖｌｏｏｋｕｐ関数で、下のように式を入力しますよね。

=vlookup(検索値,検索範囲,列番号）

で、列番号のところはその、検索したい列番号にしなければ、いけないのですが、単純に横方向にコピーすると、列番号は、変化しないのですね・・・横に長いデータを使用しているので、列番号を一つずつ書いていくのが、面倒なのですが、みなさん、そうされているのでしょうか？？

また、別件で、このように別のシートからデータを持ってくるように作った式のシートを、コピーして、別のファイルに貼り付けると、その検索先が、式を作った場所のままになってしまいますよね（コピー→貼り付けで、数式のみにしても・・・）
この解決方法はないのでしょうか？？当方、マクロやＶＢＡはわからいのですが・・・・
どなたか、良い方法がありましたら教えてください。よろしくお願いいたします。

回答No.3

papayuka · ベストアンサー

> B２にの値からだったらColumn（ｂ２）にして、

ｂ２のような引数は不要です。
=Column() は、関数が入っている列の列番号を返します。

A1に =Column() と入れると 1
B1に =Column() と入れると 2
　　　・
　　　・
Z1に =Column() と入れると 26

そこに入れたい数字と関数を入れたセルの列番号を差し引きして調整すれば、横にコピーして使えます。
つまり、D1に =Column() と入れて結果を 1 にしたければ、D1 に =Column()-3 としてやれば良い事になります。
これを横にコピーすると、E1は2、F1は3になります。
VLOOKUPに応用すれば、D1に =VLOOKUP($A1,検索範囲,Column()-3,0) だと、=VLOOKUP($A1,検索範囲,1,0) と同じ事になります。

回答No.5

tascany

前半の問題は、COLUMN関数を使えばそれでいいと思います。
後半はおそらく、次のようにすれば解決する問題ではないでしょうか。
１）とりあえず、ブックAの式をブックBにそのままコピー。
２）ブックB上で、編集＞リンクの設定＞リンク元の変更で、リンク元をブックAからブックBに変更。
※ブックAとブックBとで、シート名をそろえておいてください。

回答No.4

imogasi

質問は２つの課題があります。
（１）前半
＞単純に横方向にコピーすると、列番号は、変化しないのですね
そうです。変化させるにはCOLUMN()関数などを使います。
＞横に長いデータを使用しているので
「データ」がＶＬＯＯＫＵＰの第１引数（検索値）に関するのか、第２引数（検索範囲）に関するのかハッキリしません。
●このケースが良く判りません。
（例A）テーブルをE２：F6（例え）につくって
1 a
2 b
3 c
4 d
A1,B1、C1、D1・・・にデータを入れA2,B2、C2、D2・・
に式を入れるなら、
A2に=VLOOKUP(D1,$E$2:$F$6,2,FALSE)といれD2まで式を
複写すれば良い。
なにも疑問らしいことは起こらない。
（例B）E6:I9などに表を作って
1 a x v l
2 b y w m
3 c z s n
4 d u t o
A1に=VLOOKUP(B1,$E$6:$I$9,2,FALSE)
B2に=VLOOKUP(C1,$E$6:$I$9,3,FALSE)
C2に=VLOOKUP(D1,$E$6:$I$9,4,FALSE)
D2に=VLOOKUP(E1,$E$6:$I$9,5,FALSE)
・・・
のように式を変化させたいのでしょうか？
A2に
=VLOOKUP(A1,$E$6:$I$9,COLUMN(A1)+1,FALSE)
B2、C2・・に式を複写して、例えばC2には
=VLOOKUP(B1,$E$6:$I$9,COLUMN(B1)+1,FALSE)
にしたいのでしょうか？。
こんなケースってビジネスではあまり考えられませんが。
小生が誤解しているなら、例を上げて見てください。
(後半）
これも質問の意味がハッキリしない。具体例を上げるのが
一番良い。
コピーで式が変化するのは番地部分だけです。
それは（同一ブックの）別シート間でも（その制約は）始まっています。
Sheet2の（例えば）A1セルに=Sheet1!A1といれ、そのSheet2のA1をコピーして、同一ブックのSheet3の(例えば）A３に貼りつけると、=Sheet1!A3になります。
Sheet1の部分は変化しません。まして別ブックに持っていったりしても同じだし、（この例では指定してないが）ブック指定の部分も変化しません。
どのように変化させれば良いか、エクセルシステムにとって、表現し難いからです。質問者は自分の都合のよいあることを前提に考えているが、変化させたくないケースも多いので、変化させない記号なども導入しなければなりません。「表現が難しい」とは、シート名もＳｈｅｅｔ１、Ｓｈｅｅｔ２は仮の名前で、東京シート、大阪シートと名古屋シートと名がついたら、どのように変化させる約束にするのでしょうか？表現できないでしょう。シートタブの左からの見える順番などその都度、如何様にも移動できますし。この様に難しい問題を抱えていますので、操作者の指定に任さざるを得ないのではないでしょうか。
（３）その他
第４引数を省略してますが、省略しない方が良いのでは。
あと２、３注意事項がありますが略。

お礼

ご回答 ありがとうございます。

とりあえず、仕事上の作業ではないんです・・

回答No.2

sero

COLUMN関数を使えば良いのでは？
例えば
=vlookup(検索値,検索範囲,column(a1)）
これなら最初は1、横にコピーすれば2、3となる。

＞別件で、このように別のシートからデータを持ってくるように作った式のシートを、コピーして、別のファイルに貼り付けると、その検索先が、式を作った場所のままになってしまいますよね（コピー→貼り付けで、数式のみにしても・・・）
何が言いたいのかいまいち分かりませんが、別ブックではなく、自ブックを参照したいなら、別ブックに関する部分を置換で消せば良いでしょう。

お礼

ありがとうございます！
質問の仕方がわかりずらくてすみません。
例えば、ファイルAのシート１で、検索式をつくり、データをシート２から持ってくるというものを作って、
また別のファイルBでも同じようにするために、Aのシート１をコピーしてファイルＢに貼り付けると、式がそのまま、Ａのシート2からデータを持ってくるようになったままなので、Ｂのシート2からもってくるようにしたかったのです。

回答No.1

papayuka

=VLOOKUP($A1,検索範囲,Column()-1,0)

のようにします。
Column() は 関数を入力したセルがB列なら2、C列なら3が返るので上記の指定だと、1を指定した事になります。

> 別のシートからデータを持ってくるように作った式のシート

元ブックとコピー先ブックを並べて表示して、ドラック＆ドロップでデータシートをコピー先ブックに「移動」させてしまい、元ブックは保存しないで終了させると外部リンクは切れます。

お礼

早速のご回答 ありがとうございます！
B２にの値からだったらColumn（ｂ２）にして、横に伸ばせばよいのでしょうか？？
そうしたらとても便利です！
ありがとうございます。