みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルの関数 vlookup 関数で式の作り方&コピーの仕方

vlookup関数で、下のように式を入力しますよね。

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

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

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

投稿日時 - 2005-01-02 01:08:37

QNo.1149451

困ってます

質問者が選んだベストアンサー

> B2にの値からだったらColumn(b2)にして、

b2のような引数は不要です。
=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) と同じ事になります。

投稿日時 - 2005-01-02 02:32:26

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

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

投稿日時 - 2005-01-02 22:27:19

ANo.4

質問は2つの課題があります。
(1)前半
>単純に横方向にコピーすると、列番号は、変化しないのですね
そうです。変化させるにはCOLUMN()関数などを使います。
>横に長いデータを使用しているので
「データ」がVLOOKUPの第1引数(検索値)に関するのか、第2引数(検索範囲)に関するのかハッキリしません。
●このケースが良く判りません。
(例A)テーブルをE2:F6(例え)につくって
1a
2b
3c
4d
A1,B1、C1、D1・・・にデータを入れA2,B2、C2、D2・・
に式を入れるなら、
A2に=VLOOKUP(D1,$E$2:$F$6,2,FALSE)といれD2まで式を
複写すれば良い。
なにも疑問らしいことは起こらない。
(例B)E6:I9などに表を作って
1axvl
2bywm
3czsn
4duto
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の(例えば)A3に貼りつけると、=Sheet1!A3になります。
Sheet1の部分は変化しません。まして別ブックに持っていったりしても同じだし、(この例では指定してないが)ブック指定の部分も変化しません。
どのように変化させれば良いか、エクセルシステムにとって、表現し難いからです。質問者は自分の都合のよいあることを前提に考えているが、変化させたくないケースも多いので、変化させない記号なども導入しなければなりません。「表現が難しい」とは、シート名もSheet1、Sheet2は仮の名前で、東京シート、大阪シートと名古屋シートと名がついたら、どのように変化させる約束にするのでしょうか?表現できないでしょう。シートタブの左からの見える順番などその都度、如何様にも移動できますし。この様に難しい問題を抱えていますので、操作者の指定に任さざるを得ないのではないでしょうか。
(3)その他
第4引数を省略してますが、省略しない方が良いのでは。
あと2、3注意事項がありますが略。

投稿日時 - 2005-01-02 09:35:16

お礼

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

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

投稿日時 - 2005-01-03 11:28:37

ANo.2

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

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

投稿日時 - 2005-01-02 01:25:00

お礼

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

投稿日時 - 2005-01-02 01:42:43

ANo.1

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

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

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

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

投稿日時 - 2005-01-02 01:23:29

お礼

早速のご回答 ありがとうございます!
B2にの値からだったらColumn(b2)にして、横に伸ばせばよいのでしょうか??
そうしたらとても便利です!
ありがとうございます。

投稿日時 - 2005-01-02 01:38:07

あなたにオススメの質問