[[20031122132929]]　『VLOOKUPの応用偏』（千）　ページの最後に飛ぶ

『VLOOKUPの応用偏』（千）

シニア様
昨日教えていただいたVLOOKUPを応用して使用したいのですがどう表記したら良いでしょうか
宜しくお願い致します。

        A       　　B　　　　　C        D        E
1　　客先コード　　　客先名   　住所　　　TEL　　　〒番号　 
2       AA      ○○○商事　　　***　　　****　　　***-*****
3       BB      ＸＸＸ会社　　　123　　　1111　　　***-00000　
4       CC      □□□店　　　　223　　　2222　　　***-11111　
B6セルに　=Vlookup(A6,A1:B4,2,FALSE) と入力すると結果はA6,B6に　AA　○○○商事　と表示されますが、今回はAAと入力するとその行一列を表示させたいのです。
例えば
　　AA　の入力で　　○○○商事　　　***　　　****　　　***-*****　と表したいのです。
=VLOOLUP(A6,A1:E4,ここから先はどう表記したらよいでしょうか？
宜しくお願いします。（千）

 =VLOOLUP(A6,A1:E4,　此処の数字 A列から数えて何番目？　,FALSE)
    　　　　　　　　　　　　　　　↑出力させたい番数字を入れて　挑戦してみて下さい　　　（jun53）

 衝突しました。
 (jun53さんのヒントを参考にまず挑戦してみてから見てもらえたらよいかもしれません。）

 まず数式中の範囲を次のように絶対参照にします。

 =VLOOKUP($A$6,$A$1:$E$4,2,FALSE)

 セルアドレスに「＄」をつけることで数式を他のセルへコピーしても
 セルの範囲がずれなくなります。
 その上でこの数式をC6:E6へコピーし、それぞれ下記のように変更します。
 C6:
 =VLOOKUP($A$6,$A$1:$E$4,3,FALSE)

 D6:
 =VLOOKUP($A$6,$A$1:$E$4,4,FALSE)

 E6:
 =VLOOKUP($A$6,$A$1:$E$4,5,FALSE)

 参考
 さらに下記のようにすると同じ式でご希望のことが実現できます。
 =VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE)

 （KAMIYA)

 COLUMN関数を使った方法が衝突してしまいましたので、COLUMN関数の説明だけします。
=COLUMN()とすると、その数式が入っている列番号を返してくれます。
A列なら１、B列なら２というようにです。ですからKAMIYAさん、ご提案の様にすると
数式をいちいち打ち込まなくてもコピーして使う事が出来ます。覚えておくと、重宝します。
（ケン）

 『便利さの確認』
 KAMIYAさんの案内された式の参照値$A$6を$A6と列固定だけにして下の式にします。
=VLOOKUP($A6,$A$1:$E$4,COLUMN(),FALSE)
上の式をコピーして、B6に貼り付けます。○○○商事　と表示されました。
ケンさんが補説されていますように列関数COLUMN()が検索表の列位置と一致するので、
A7に　CC,　A8に　BB　と入力して，B6:E8を選択します。
次に『Ｆ２』を押します。Ctrlキーを押しながらEnterキーを押すと一瞬に検索結果が表示されます。
 (シニア)

皆様たくさんの回答有難うございます。（千）

KAMIYA 様
C6:

 =VLOOKUP($A$6,$A$1:$E$4,3,FALSE)

 D6:
 =VLOOKUP($A$6,$A$1:$E$4,4,FALSE)

 E6:
 =VLOOKUP($A$6,$A$1:$E$4,5,FALSE)

 参考
 さらに下記のようにすると同じ式でご希望のことが実現できます。
 =VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE)

 参考前の式ではうまくできました。
でも参考数式でやってみるとできませんでした。数式をコピーして使用してもできないんですよね？
COLUMAN()の()の間に範囲か何かを指定しないといけないんですか？
（千）

 C列に数式が入っていればCOLUMN（）の戻り値は３になります。ですから
数式がC列に入っていれば=VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE) は
=VLOOKUP($A$6,$A$1:$E$4,3,FALSE) と同じ結果になります。
C列に入っていなければCOLUMN(C1)の様にする必要が有ります。（ケン）

 A列からE列以外の範囲に貼り付けられているのではないでしょうか
 貼り付ける範囲を書かなかったのも私の手落ちですが、
 式をB列からE列に貼り付けるようにするとうまくいくと思います。
 もし違っていたらどのようにコピーしたかを書いていただけると
 原因もはっきりすると思います。

 COLUMN関数
 シニアさんも書いているようにCOLUMN()とした場合はこの関数が入力されているセルの
 列番号（ワークシートの列の左から数えて何番目か）が返ってきます。
 たとえば次の二つの式がB列に入力されている場合は
 =VLOOKUP($A$6,$A$1:$E$4,2,FALSE) 
 =VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE) 
 この二つの数式の内容は同じものになります。

 ()の中にセル番地を入力した場合は、入力したセル番地の列番号が返ります。
 =COLUMN(C2) にするとそのセルには「3」が表示されます。
 (KAMIYA)

たとえばの資料を下記とし

 　 A　　　　B　 　　　　　　　C　　　　　D      E
 1 客先	 客先名	　　　　　場所	責任者
 2 AY	○○○商事	団地	山田
 3 AB	ＸＸＸ会社	団地	川端
 4 AC	□□□店	　　　　　団地	海坂
 5
 6
 7　　　　　　　　　　　　　　　　　　　　　　　
=VLOOKUP(　　　COLUMN()........
E7に参考数式をコピーすると言う事ですか？
やってみたんですけど値が0になるんです....。
(千）

 　 A　　　　　　　　B　 　　　　C　　　　　  D      E
 1 客先コード	 客先名	　　　場所	責任者
 2 AY	　　　　　○○○商事     団地	山田
 3 AB	　　　　　ＸＸＸ会社     団地	川端
 4 AC	　　　　　□□□店　 　  団地	海坂
 5
 6
 7　　　　　　　　　　　　　　　　　　　　　　　
 A6に客先コードを（AYとかABとかACの様に）
 B6に客先コードに該当する客先名を表示させるには
=VLOOKUP($A6,$A$1:$D$4,COLUMN(),FALSE)とします。ここまではOKでしょうか？
ここから客先コードに該当する場所、責任者を出すべき数式のコピーです。
まず。B6を選択状態にしておきます。マウスポインターをB6のセルの右下に合わせます。
マウスポインターが＋に変わると思います。そしたら左クリックしたままD6までドラック
します。で出来ると思います。（ケン）

        A       　　B　　　　　C        D        E
 1　　客先コード　　　客先名   　住所　　　TEL　　　〒番号　 
 2       AA      ○○○商事　　　***　　　****　　　***-*****
 3       BB      ＸＸＸ会社　　　123　　　1111　　　***-00000　
 4       CC      □□□店　　　　223　　　2222　　　***-11111

 こちらは最初のご相談のレイアウトで検証しています。
 ＞（A6に）AA　の入力で　　○○○商事　　　***　　　****　　　***-*****　と表したいのです。
 ということだったので、
 B6:E6へ
 =VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE) 
 を張り付けするとご希望の結果が得られます、という回答をいたしました。

 ＞やってみたんですけど値が0になるんです....。

 　 A　　　　B　 　　　　　　　C　　　　　D      E
 1 客先	 客先名	　　　　　場所	責任者
 2 AY	○○○商事	団地	山田
 3 AB	ＸＸＸ会社	団地	川端
 4 AC	□□□店	　　　　　団地	海坂
 5
 6
 7　　　　　　　　　　　　　　　　　　　　　　　
 このレイアウトでE7へ「=VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE) 」を貼り付けると、

 =VLOOKUP($A$6,$A$1:$E$4,COLUMN(),FALSE) 
                           ↑
　　　　　　　　このCOLUMN関数はセルE7の列番号「5」が返る

 つまり、$A$1:$E$4の範囲で5列目のE列を検索しに行きます。
 しかし、E列（E1:E4)には何も入力されていないので、｢0｣しか結果が返ってきません。
 また、検索する列A列は昇り順で並べ替えておく必要があります。
 (KAMIYA)

 検索条件がFALSEなので昇順でなくても良いのではないでしょうか？（ケン）

 ＞ケン様
 そのとおりでした・・・。
 並べ替えをするクセまで書いてしまったようです。（反省)
 (KAMIYA)

 千さん表検索のVLOOKUP(検索値、範囲、列番号、検索の型)について理解できましたか？
 　 A　　　　B　 　　　　　　　C　　　　　D     
 1 客先	 客先名	　　　　　場所	責任者
 2 AY	○○○商事	A団地	山田
 3 AB	ＸＸＸ会社	B団地	川端
 4 AC	□□□店	　　　　　C団地	海坂
 5
 6
 7　　　　　　　　　　　　　　　　　　　　　　　
 8 客先	 客先名	　　　　　場所	責任者 
 9  AB   =VLOOKUP(A9,A1:D4,2,FALSE)
         A9のABを検索して、表の範囲の2列目を検索値と完全一致のデータを返しなさい
　　　　　　　　検索結果はＸＸＸ会社になります。
表の3列目の場所を表示させたい場合は
 8 客先	 客先名	　　　　　場所	責任者 
 9  AB   　　　　　　　　　=VLOOKUP(A9,A1:D4,3,FALSE)････B団地
表の4列目の責任者を表示させたい場合は
 8 客先	 客先名	　　　　　場所	責任者 
 9  AB   　　　　　　　　　　　　　　=VLOOKUP(A9,A1:D4,4,FALSE)････川端
ここまでは出来たとのことですが、皆さんが案内されている問題はこれらの式を各セルに入力するのは大変だ！
もっと楽に入力する方法はないだろうか？なのです。
 8 客先	 客先名	　　　　　場所	責任者 
 9  AB   =VLOOKUP(A9,A1:D4,2,FALSE)この式をC9にコピーすると=VLOOKUP(B9,B1:E4,2,FALSE)になります。
これで検索値も表の範囲もずれてしまいます。
B9の式を検索値のセル番地を列固定の$B9に変更、表範囲も$A$1:$D$4に列行共に固定します。
式は　=VLOOKUP($A9,$A$1:$D$4,2,FALSE)　となります。この式をD9までコピーします。
C9,D9の式の列番号を変更するだけで入力が楽になります。
=column()は列番号を求めます。=ROW()は行番号を求めます。
試しに、B10に　=column()　と入力し、C10,D10にコピーして下さい。結果は2,3,4となりましたか？
この列関数を利用して列番号を式に組み込みます。
B9の式は　=VLOOKUP($A9,$A$1:$D$4,COLUMN(),FALSE)　となり、
この式をD9までコピーすると編集しなくてもよいことになります。
A10にACと入力し、B10:D10を選択して、CtrlキーとDを押してみて下さい。どのようになりましたか？
これはB9:D9を選択し、下方コピーしたと同じことになります。
Ctrl+DはDownCopyでCtrl+RはRightCopyのショートカットキーです。
このようにEXCELの便利機能を利用して効率よく作業を進めます。
皆さんの説明されている便利な機能について、少しでも理解頂けたでしょうか？
 (シニア)

 わたしも今回の場合
 最初は　数字　から説明をして
 VLOOKUP を理解してもらったほうが
 いいような気がしてますが。

 どんなもんでしょうかね。。　　　（jun53）

 横からこんな下らない質問をしていいのか分かりませんが、
 ＶＬＯＯＫＵＰの最後につけるＦＡＬＳＥとは？
 無知ですみません･･･（ＭＩＮ）

 検索の型です。
検索の型   検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、
論理値で指定します。TRUE を指定するか省略すると、検索値が見つからない場合に、
検索値未満で最も大きい値が使用されます。FALSE を指定すると、
検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が
返されます。
ヘルプより。私は面倒なので、FALSEは0としちゃいます。（ケン）

シニア　様
できました！！！
とても簡単にです！！！
column(),ROW()も試してみました。結果は2,3,4となりました。
別の関数も教えていただきありがとうございます。
いろいろな事に応用してみます。
もっとVLOOKUPを理解しないといけませんね。jun53のおっしゃる通りです。
皆様ありがとうございました。
(千)

 ？？？　千さん、前回までの書き込み消しました？？
 確か、、、続きですよね？　違う？ 　　　（jun53）

ケン　様
ありがとうございます。
できました。本当に助かりました。

それから...
jun53　様
すみません。つい、編集してしまいました。
用件は前回分は解決し今回分は応用だったので前回の関連で資料のみを使ってしまいました。
皆さんすみません。
今後気をつけます。
申し訳ございません。(千)

 こちら事務局です。
 一応消失した部分を修復しましたが、どこかおかしい部分があるかもしれません。
（kazu） 2003/11/26 21:45

コメント返信：

[ 一覧(最新更新順) ]

[[20031122132929]] 『VLOOKUPの応用偏』（千） ページの最後に飛ぶ

[[20031122132929]]　『VLOOKUPの応用偏』（千）　ページの最後に飛ぶ